Perintah Dasar di MySql

TUGAS PERTEMUAN 5

MANAJEMEN DATA / MANAJEMEN BASIS DATA

Nama               : Budi Raharjo

NIM                 : 1911601266

1.      Membuat database di MySql

CREATE DATABASE db_penjualan;

2.      Membuat tabel pelanggan

CREATE TABLE pelanggan (

    id_pelanggan VARCHAR (5) NOT NULL,

    nm_pelanggan VARCHAR (40) NOT NULL,

    alamat TEXT

);

3.      Menambahkan field

Tambahkan telpon dengan sytax di bawah ini

ALTER TABLE pelanggan ADD telpon varchar (50)AFTER alamat;

Kemudian untuk email tambahkan lagi sintax di bawah ini

ALTER TABLE pelanggan ADD email varchar(50)AFTER telpon;

4.      Merubah id_pelanggan menjadi primary

ALTER TABLE pelanggan ADD PRIMARY KEY (id_pelanggan);

5.      Membuat tabel pesan, detail_pesan, produk, faktur dan kuitansi

CREATE TABLE pesan (

    id_pesan INT (5) NOT NULL,

    id_pelanggan VARCHAR (5) NOT NULL,

    tgl_pesan date,

   PRIMARY KEY (id_pesan)

);

CREATE TABLE produk (

    id_produk varchar (5) NOT NULL,

    nm_produk VARCHAR (30) NOT NULL,

    satuan VARCHAR (10),

   harga DECIMAL(10,0),

   stok INT (3),

   PRIMARY KEY (id_produk)

);

CREATE TABLE detail_pesan (

    id_pesan INT (5) NOT NULL,

    id_produk VARCHAR (5) NOT NULL,

    jumlah INT (5),

    harga DECIMAL(10,0)

);

CREATE TABLE faktur (

    id_faktur INT (5) NOT NULL,

    id_pesan INT(5) NOT NULL,

    tgl_faktur date,

   PRIMARY KEY (id_faktur)

);

CREATE TABLE kuitansi (

    id_kuitansi INT (5) NOT NULL,

    id_faktur INT(5) NOT NULL,

    tgl_kuitansi date,

   PRIMARY KEY (id_kuitansi)

);

Kemudian tambahkan foreign key dua buah di tabel detail_pesan

Foreigen key pada id_pesan

ALTER TABLE detail_pesan ADD FOREIGN KEY (id_pesan) REFERENCES pesan(id_pesan);

Foreign key pada id_produk

ALTER TABLE detail_pesan ADD FOREIGN KEY (id_produk) REFERENCES produk(id_produk);

6.      Meginput data 

Meginput data ke tabel pelanggan

INSERT INTO pelanggan VALUES

    ('P0001', 'Achmad Solichin', 'Jakarta', '021-7437299', 'achmatim@gmail.com'),

    ('P0002', 'Budianto', 'Bandung', '021-349924', 'budi@luhur.com'),

    ('P0004', 'Amin Riyadi', 'Jakarta', '021-3239991', 'aminudin@plasa.com'),

    ('P0005 ', 'Anandini', 'Semarang', '021-93949992', 'anidia@plasa.com'),

    ('P0006', 'Lintang', 'Jakarta', '021-8320020', 'lintang@yahoo.com');

Menginput data ke tabel produk

INSERT INTO produk VALUES

('B0001', 'Buku Tulis', 'Buah', '2500', '40'),

('B0002', 'Pulpen', 'Buah', '2000', '30'),

('B0003 ', 'Penggaris', 'Buah', '1500', '15'),

('B0004', 'Pensil', 'Buah', '1300', '100'),

('B0005', 'Jangka', 'Buah', '1700', '0');

Menginput data ke tabel pesan

INSERT INTO pesan VALUES

('1','P0001','2008-02-02'),

('2','P0002','2008-02-05'),

('3','P0003','2008-02-10'),

('4','P0004','2008-02-20');

Menginput data ke detail_pesan

INSERT INTO detail_pesan VALUES

('1','B0001','2','2700'),

('1','B0003','3','3000'),

('1','B0004','1','2000'),

('2','B0001','1','2700'),

('2','B0002','5','2500'),

('2','B0004','1','1500'),

('3','B0002','5','2550'),

('4','B0001','10','2600');

7.      Update alamat Amin Riyadi

UPDATE pelanggan SET alamat='Bandung' where nm_pelanggan='Amin Riyadi';

8.      Menghapus baris yang stoknya 0

DELETE FROM produk where stok=0;

9.      Perintah menampilkan data

1.      Berdasarkan alamat di Jakarta

SELECT * FROM pelanggan where alamat='Jakarta';

2.      Semua produk yang harganya lebih dari 2500

SELECT * FROM produk WHERE harga > 2500;

3.      Semua produk yang pernah dibeli (dipesan) oleh pelanggan P0001.

SELECT produk.id_produk, produk.nm_produk, detail_pesan.jumlah, pesan.id_pelanggan

FROM produk INNER JOIN (detail_pesan INNER JOIN pesan

ON detail_pesan.id_pesan=pesan.id_pesan)

ON produk.id_produk=detail_pesan.id_produk WHERE id_pelanggan='P0001';

4.      Semua pelanggan beserta transaksi yang pernah dilakukannya.

SELECT produk.id_produk, produk.nm_produk, detail_pesan.jumlah, pesan.id_pelanggan, pelanggan.nm_pelanggan

FROM produk INNER JOIN

(detail_pesan INNER JOIN pesan ON detail_pesan.id_pesan=pesan.id_pesan

INNER JOIN pelanggan ON pelanggan.id_pelanggan=pesan.id_pelanggan)

ON produk.id_produk=detail_pesan.id_produk;

5.      Jumlah barang dan nilai transaksi untuk masing-masing transaksi pembelian (pemesanan).

SELECT  id_pesan, id_produk,

        SUM(jumlah) jumlah,

        SUM(harga) harga

FROM detail_pesan

LEFT JOIN pesan USING(id_pesan)

GROUP BY id_pesan;

6.      Total nilai transaksi yang pernah dilakukan untuk setiap pelanggan.

SELECT  pesan.id_pesan, pelanggan.nm_pelanggan, COUNT(id_pesan) total, SUM(jumlah) jumlah, SUM(harga) tagihan

FROM detail_pesan

LEFT JOIN pesan USING(id_pesan)

LEFT JOIN pelanggan ON pelanggan.id_pelanggan=pesan.id_pelanggan

GROUP BY id_pesan;

7.      Semua 5 produk dengan jumlah stock terbanyak.

SELECT * FROM produk ORDER BY stok DESC limit 5;

8.      Produk yang paling laku.

SELECT id_produk, SUM(jumlah) jumlah FROM detail_pesan

   GROUP BY id_produk ORDER BY jumlah DESC LIMIT 1;