1. Judul Praktek : Overview pembelajaran lampau (Query dan Sub Query) 2. Jumlah Jam : 3 jam 3. Tujuan praktek Mahasiswa dapat mengingat kembali tentang query dan sub query yang sudah dipelajari pada semester lalu sebagai dasar untuk pembelajaran semester ini. 4. Teori Dasar Perintah atau instruksi dalam bahasa SQL dapat dikelompokkan berdasarkan jenis dan fungsinya. Terdapat 3 kelompok perintah dasar SQL: Data Definition Language, Data Manipulation Language dan Data Control Language. • Data Definition Language (DDL) adalah jenis instruksi SQL yang berkaitan dengan pembuatan struktur tabel dan database. Termasuk diantaranya CREATE, DROP, ALTER, dan RENAME. • Data Manipulation Language (DML) adalah jenis instruksi SQL yang berkaitan dengan data yang ada dalam tabel. Yakni bagaimana cara menginput data, menghapus data, update data serta membaca data yang tersimpan di dalam database. Contoh perintah SQL untuk DML adalah SELECT, INSERT, DELETE, dan UPDATE. • Data Control Language (DCL) adalah jenis instruksi SQL yang berkaitan dengan manajemen hak akses dan pengguna (user). Perintah ini digunakan untuk membatasi siapa saja yang dapat mengakses database dan tabel. Perintah SQL yang termasuk ke dalam kategori DCL adalah GRANT dan REVOKE. Selain ketiga jenis perintah SQL diatas, terdapat juga 2 kategori tambahan: Transaction Control Language, dan Programmatic SQL. • Transaction Control Language (TCL) adalah perintah SQL untuk proses transaksi. Proses transaksi ini pada dasarnya digunakan untuk membuat beberapa perintah query yang dianggap sebagai satu kesatuan. Jika ada masalah, seluruh proses bisa dibatalkan. Termasuk ke dalam TCL adalah query COMMIT, ROLLBACK, dan SET TRANSACTION. • Programmatic SQL berkaitan dengan sub program (stored procedure) maupun penjelasan mengenai struktur database (meta data table). Contoh perintah seperti adalah DECLARE, EXPLAIN, PREPARE, dan DESCRIBE. JOIN TABLE Adalah cara untuk menghubungkan data yang diambil dari tabel-tabel melalui sebuah kolom yang menghubungkan mereka (Biasanya adalah foreign key). JOIN table akan berguna ketika kita akan melakukan query dari lebih dari 1 tabel. STRUKTUR JOIN TABLE : select namatabel.namafield1, namatable2.namafield2, ... from tabel1,tabel2, ... where tabel1.fieldrelasi = tabel2.fieldrelasi; Contoh : Tampilkan tanggal transaksi, nama pelanggan, jumlah, harga. select ttrans.tgl, tpelanggan.nmlgn, ttrans.jml,ttrans.hrg from ttrans, tpelanggan where ttrans.kdlgn=tpelanggan.kdlgn; SUB QUERY SUB QUERY / NESTED QUERY / QUERY didalam QUERY adalah query yang ada didalam query lainnya. Seperti SELECT, INSERT, UPDATE dan DELETE. Sub query dapat berada didalam subquery lainnya. Jenis2 Sub Query : 1. Sub Query didalam klausa WHERE A. Sub Query dengan operator perbandingan Kita dapat menggunakan operator pembanding misal: =, !=, >=, >, <, <= Operator ini digunakan untuk membandingkan nilai tunggal yang dikembalikan oleh subquery dengan ekspresi dalam klausa WHERE. Contoh : Tampilkan data transaksi yang jumlah barangnya diatas rata-rata jumlah penjualan SELECT * FROM ttrans WHERE jml > (SELECT AVG(jml) from ttrans); Tampilkan kdbrg, harga untuk barang yang paling murah. SELECT kdbrg, hrg FROM ttrans WHERE hrg = (SELECT MIN(hrg) FROM ttrans); B. Sub Query dengan operator IN dan NOT IN Jika subquery mengembalikan lebih dari satu nilai, kita dapat menggunakan operator lain seperti operator IN atau NOT IN dalam klausa WHERE. Contoh : Tampilkan kode pelanggan, nama pelanggan yang pernah membeli barang. Soal diatas bisa dijawab menggunakan JOIN dan sub query kalau dengan JOIN : SELECT distinct kdlgn, nmlgn FROM tpelanggan join ttrans using(kdlgn); kalau dengan sub query : SELECT kdlgn, nmlgn FROM tpelanggan WHERE kdlgn IN (SELECT kdlgn FROM ttrans); 2. SUB QUERY didalam klausa FROM Saat menggunakan subquery dalam klausa FROM, hasil yang kembali dari subquery digunakan sebagai tabel sementara. Struktur : SELECT field1, field2, .... FROM (SELECT field_a, field_b FROM namatabel) AS nama_query WHERE [kondisi]; CONTOH : Tampilkan jumlah barang paling banyak, paling sedikit dan rata2 barang yang dibeli berdasarkan nonota. SELECT MAX(items), MIN(items), AVG(items) FROM (SELECT SUM(jml) AS items FROM ttrans GROUP BY nonota) as query1; Pada subquery diatas, yang dijalankan adalah subquery yang didalam FROM terlebih dahulu, yaitu : SELECT SUM(jml) AS items FROM ttrans GROUP BY nonota Pada sub query setelah FROM(query bagian dalam) harus memberi alias pada field, karena field alias itu nanti yg akan dipanggil pada query yang pertama(query bagian luar) 3. SUB QUERY dengan EXISTS dan NOT EXISTS Ketika subquery digunakan dengan operator EXISTS atau NOT EXISTS, subquery mengembalikan nilai Boolean dari TRUE atau FALSE. STRUKTUR : SELECT EXISTS (SELECT field1, field2, ... FROM table_name WHERE [kondisi]); Pada query di atas, jika subquery mengembalikan baris, maka subquery EXISTS mengembalikan TRUE, jika tidak, ia mengembalikan FALSE. Contoh : SELECT EXISTS(SELECT * from tpelanggan WHERE kota = 'Padang'); akan menghasilkan nilai 1, karena ada pelanggan yg tinggal dikota Padang. SELECT NOT EXISTS(SELECT * from tpelanggan WHERE kota = 'Padang'); akan menghasilkan nilai 0, karena kebalikan dari EXISTS Penulisan dengan cara lain : SELECT * FROM tpelanggan WHERE EXISTS (SELECT * from tpelanggan WHERE kota = 'Padang'); akan menampilkan semua data pelanggan. Karena ada data pelanggan yang tinggal dikota Padang maka akan tampil semua. Jika kondisi diganti dengan kota jakarta, maka query tidak akan menghasilkan baris apapun. SELECT * FROM tpelanggan WHERE EXISTS (SELECT * from tpelanggan WHERE kota = 'Jakarta'); 4. SUb Query dengan ANY, SOME, ALL Query ANY dan SOME dipakai untuk membandingkan salah satu data himpunan subquery. Sedangkan query ALL akan membandingkan seluruh data yang ada dihimpunan subquery. QUERY SOME/ANY: CONTOH : Tampilkan semua data yang ada ditabel tpelanggan untuk pelanggan yang kdlgn nya ada di tabel ttrans. SELECT * FROM tpelanggan WHERE kdlgn = ANY (SELECT kdlgn FROM ttrans); Query diatas akan mengecek satu persatu apakah kdlgn di tpelanggan sama dengan (ada di) ttrans. Jika ada maka akan ditampilkan. Pemakaian ANY sama dengan SOME. Hasilnya juga sama dengan IN, tapi hanya untuk = ANY atau = SOME. QUERY ALL : Membandingkan seluruh data yang ada dihimpunan sub query, bukan hanya salah satu saja seperti SOME/ANY. CONTOH : Tampilkan keseluruhan data yang ada pada tpelanggan dimana yang ditampilkan untuk data kdlgn yg tidak ada pada ttrans. SELECT * FROM tpelanggan WHERE kdlgn != ALL (SELECT kdlgn FROM ttrans); Jika dengan LEFT JOIN : SELECT * FROM tpelanggan LEFT JOIN ttrans USING(kdlgn) WHERE ttrans.kdlgn is null; Bisa juga dengan NOT IN : SELECT * FROM tpelanggan WHERE kdlgn NOT IN (SELECT kdlgn FROM ttrans); 5. Latihan Jawablah soal-soal dibawah ini, gunakan database classicmodels : SOAL QUERY : 1. Tampilkan nama lengkap karyawan, jobtitle, country, state dengan ketentuan CA= CAlifornia, NY=New York, MA = Massachusetts, kalau ada selain itu buat other untuk karyawan yang berada di negara USA. 2. Tampilkan nama customer, orderdate, lama waktu tunggu yang dibutuhkan dari waktu pengiriman (shippeddate) hingga waktu pemesanan dari table order (orderdate). untuk barang yg diorder pada Januari - Agustus 2004 dan masa tunggu diatas 5 hari, urutkan dari waktu tunggu terpendek. 3. Tampilkan nama kontak customer lengkap dengan huruf kapital, alamat lengkap (addressline1 + state), creditlimit dan nama product yang dibeli oleh customer tersebut, untuk pelanggan yang berada di state MA,MY, NV, CA dan NJ dan credit limit antara 50.000 – 100.000, hanya untuk productname adalah Harley Urutkan dari creditlimit tertinggi 4. Tampilkan nama pelanggan, kontak lengkap pelanggan yang membeli barang dengan nama harley 5. Tampilkan nama lengkap employee, di city mana employee tsb ditempatkan. Untuk state yang memiliki nilai null, ganti dengan nilai country. 6. nama product apa saja yang sudah dijual oleh employee yang bernama Leslie Thompson dan siapa nama customer yg membeli product tersebut. Tampilkan hanya untuk product yang dikirim (shippeddate) pada tahun 2003 7. Tampilkan semua data dari tabel payments tetapi hanya untuk pelanggan dengan customernumber = 124, untuk payments yang terjadi pada tahun 2004 saja dan amount diatas 50000. Tampilkan customername, paymentdate dengan format tanggal, nama bulan dan tahun. 8. Jika profit didapatkan dari selisih harga jual dan harga beli, maka keuntungan tiap2 barang adalah selisih harga dikalian dengan quantityorder. berapa kira-kira kerugian yang didapatkan oleh perusahaan untuk barang yang tidak jadi dibeli(cancelled). 9. Tampilkan tanggal order, nama produk (huruf kapital), quantityorder, price each, total (quantity * price) untuk produk berjenis classic cars yang diorder selama januari - juni 2003, yang ordernya antara 50-100. Tampilkan 10 data saja untuk total harga tertinggi dan total ditampilkan pembulatan dengan 2 angka dibelakang koma 10. Tampilkan nama lengkap karyawan, nama customer yang dilayani, alamat yang diambil dari addressline2 (jika tidak ada addressline2 (nilainya NULL), ganti dengan addressline1, country dan creditlimit untuk pelanggan yang country nya USA dan kreditlimitnya antara 50000 dan 75000. Urutkan dari kreditlimit tertinggi SOAL SUB QUERY : 1. Tampilkan nama lengkap employee yang sama-sama bekerja (satu office) dengan employee yang bernama Peter Marsh 2. Tampilkan nama lengkap employee, manager untuk employee yang memiliki manager yang sama dengan Mary Patterson 3. Tampilkan nama customer yang membeli product yang sama dengan customer yang bernama Toys, tampilkan 25 data saja. 4. Tampilkan siapa saja customer yg memiliki status order yang sama dengan customer yang bernama scandinavian 5. Siapakah nama customer yang melakukan pembayaran diatas pembayaran rata-rata ditahun 2004? Tampilkan nama customer, paymentdate dan amount. 6. Tampilkan jumlah product paling banyak, paling sedikit dan rata2 barang yang dibeli berdasarkan nama customer. 7. Adakah pelanggan yang membeli product dengan nama The Titanic? 8. Tampilkan tanggal order, nama customers, nama produk yang dibeli, country dan status untuk produk yang namanya ford dan customers yang tinggal di negara yang sama dengan customernumber 282 dan 496 dan statusnya selain 'shipped'. Urutkan berdasarkan tanggal order terkini.
