KISI-KISI UTS

Kerjakan di dalam database Sistem_Pemesanan!

Langkah awal, masukkan data berikut ke tabel detil_pesan.


detil_pesan

id_pesan  id_produk  jumlah  harga 

1 MIE001  3 jumlah x dengan harga di ID PRODUK

2 ATK001 4 jumlah x dengan harga di ID PRODUK

3 ATK002 5 jumlah x dengan harga di ID PRODUK

4 ATK002  6 jumlah x dengan harga di ID PRODUK

Syntaks :

DROP TABLE IF EXISTS detil_pesan;
CREATE TABLE detil_pesan (
-> id_pesan int(5) NOT NULL,
-> id_produk varchar(5) NOT NULL,
-> jumlah int(5) NOT NULL default '0',
-> harga decimal(10,0) NOT NULL default '0',
-> PRIMARY KEY (id_pesan,id_produk),
-> KEY FK_pesan (id_produk),
-> KEY id_pesan (id_pesan),
-> CONSTRAINT FK_pesan FOREIGN KEY (id_produk)

-> REFERENCES produk (id_produk),
-> CONSTRAINT FK_pesan2 FOREIGN KEY (id_pesan)
-> REFERENCES pesan (id_pesan)
-> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Insert into detil_pesan values

-> ('1','MIE001','3','300'),

-> ('2','ATK001','4','20000'),

-> ('3','ATK002','5','37500'),

-> ('4','ATK002','6','30000');

Kesalahan pada kolom harga. Jumblah × dengan harga di Id produk :

• 3×2.000=6.000
• 4×5.000=20.000
• 5×7.500=37.500
• 6×7.500=45.000

SOAL!

Petunjuk: setiap soal discreenshootkan syntak dan hasil querynya.

1. Lakukan INNER JOIN dari semua tabel yang bisa dilakukkan inner join! (tidak ada ketentuan minimal, semakin banyak tentu semakin baik jika benar)

INNER JOIN :

• Menggunakan Where (SELECT pelanggan.id_pelanggan, pelanggan.nm_pelanggan, pesan.id_pesan, pesan.tgl_pesan FROM pelanggan, pesan WHERE pelanggan.id_pelanggan=pesan.id_pelanggan;)

• Menggunakan Inner Join (SELECT pelanggan.id_pelanggan, pelanggan.nm_pelanggan, pesan.id_pesan, pesan.tgl_pesan FROM pelanggan INNER JOIN pesan ON pelanggan.id_pelanggan=pesan.id_pelanggan;)

• Menggunakan WHERE (SELECT pesan.id_pesan, pesan.tgl_pesan, faktur.id_faktur FROM pesan, faktur WHERE pesan.id_pesan=faktur.id_pesan;)

• Menggunakan Inner Join (SELECT pesan.id_pesan, pesan.tgl_pesan, faktur.id_faktur FROM pesan INNER JOIN faktur ON pesan.id_pesan=faktur.id_pesan;)

• Menggunakan WHERE (SELECT pesan.id_pesan, pesan.tgl_pesan, detil_pesan.jumlah FROM pesan, detil_pesan WHERE pesan.id_pesan=detil_pesan.id_pesan;)

• Menggunakan Inner Join (SELECT pesan.id_pesan, pesan.tgl_pesan, detil_pesan.jumblah FROM pesan INNER JOIN detil_pesan ON pesan.id_pesan=detil_pesan.id_pesan;)

• Menggunakan WHERE (SELECT produk.id_produk, produk.nm_produk, detil_pesan.jumblah, detil_pesan.harga FROM produk, detil_pesan WHERE produk.id_produk=detil_pesan.id_produk;)

• Menggunakan Inner Join (SELECT produk.id_produk, produk.nm_produk, detil_pesan.jumblah, detil_pesan.harga FROM produk INNER JOIN detil_pesan ON produk.id_produk=detil_pesan.id_produk;)

• Menggunakan WHERE (SELECT detil_pesan.id_pesan, detil_pesan.jumblah, detil_pesan.harga, faktur.id_faktur FROM detil_pesan, faktur WHERE detil_pesan.id_pesan=faktur.id_pesan;)

• Menggunakan Inner Join (SELECT detil_pesan.id_pesan, detil_pesan.jumblah, detil_pesan.harga, faktur.id_faktur FROM detil_pesan INNER JOIN faktur ON detil_pesan.id_pesan=faktur.id_pesan;)

2. Lakukan LEFT JOIN untuk menampilkan id_pelanggan, nm_pelanggan, id_pesan, tgl_pesan

LEFT JOIN :

• Menggunakan Left (SELECT pelanggan.id_pelanggan, pelanggan.nm_pelanggan, pesan.id_pesan, pesan.tgl_pesan FROM pelanggan LEFT JOIN pesan ON pelanggan.id_pelanggan=pesan.id_pelanggan;)

3. Tampilkan semua field dari penggabungan 3 tabel yang dimungkinkan karena adanya relasi tabel! (tidak ada ketentuan minimal, semakin banyak tentu semakin baik jika benar)

PENGGABUNGAN 3 TABEL

• SELECT pelanggan.id_pelanggan, pelanggan.nm_pelanggan, pesan.id_pesan, pesan.tgl_pesan, faktur.id_faktur FROM pelanggan, pesan, faktur WHERE pelanggan.id_pelanggan=pesan.id_pelanggan AND pesan.id_pesan=faktur.id_pesan;

• SELECT pesan.id_pesan, pesan.tgl_pesan, detil_pesan.harga, detil_pesan.jumlah, produk.id_produk, produk.nm_produk FROM pesan, detil_pesan, produk WHERE pesan.id_pesan=detil_pesan.id_pesan AND detil_pesan.id_produk=produk.id_produk;

• SELECT pelanggan.id_pelanggan, pelanggan.nm_pelanggan, pesan.id_pesan, pesan.tgl_pesan, detil_pesan.jumblah, detil_pesan.harga FROM pelanggan, pesan, detil_pesan WHERE pelanggan.id_pelanggan=pesan.id_pelanggan AND pesan.id_pesan=detil_pesan.id_pesan;

• SELECT produk.id_produk, produk.nm_produk, detil_pesan.jumblah, detil_pesan.harga, faktur.id_faktur FROM produk, detil_pesan, faktur WHERE produk.id_produk=detil_pesan.id_produk AND detil_pesan.id_pesan=faktur.id_pesan;

4. Salinlah syntak berikut "SELECT pesan.id_pesan, pesan.tgl_pesan, SUM(detil_pesan.jumlah) as jumlah FROM pesan, detil_pesan WHERE pesan.id_pesan=detil_pesan.id_pesan GROUP BY id_pesan;" hasilnya apa dan jelaskan!

Jika hasil tampilanya masih per barang dengan jumblah barang yang terjadi untuk setiap transaksi, maka agar jumlah barang ditampilkan per transaksi (pemesanan), maka kita dapat menggunakan fungsi GROUP BY dan juga SUM untuk menjumlahkan jumlah barang. Hasil tampilannya seperti gambar diatas.

5. Salinlah syntak berikut "SELECT id_pelanggan, nm_pelanggan FROM pelanggan WHERE id_pelanggan IN (SELECT id_pelanggan FROM pesan);" hasilnya apa dan jelaskan, perhatikan dengan melihat tabel pelanggan.

biasanya jika melakukan query dari database, lalu urut di fungsi aplikasi dan untuk querynya adalah dengan menggunakan kondisi IN. Namun dengan query ini, MySQL akan memberikan baris yang tidak berurut sesuai urutan ID yang kita inginkan. Melainkan data yang diberikan berurut secara default ascending by ID. Dan pada tabel pelanggan terdapat 'id_pelanggan=UAA005', 'nm_pelanggan=Ekawalaya', sedangkan dengan menggunakan IN, pada kolom id_pelanggan, nm_pelanggan tidak terdapat 'UAA005' dan 'Ekawalaya', artinya pelanggan tersebut belum pernah bertransaksi dan pelanggan tersebut tidak ada. Hasil tampilanya seperti gambar diatas.