20240815172307_LN 5.pdf

LECTURE NOTES Data and Information Management Week ke - 5 SQL: Data Manipulation Language  LEARNING OUTCOMES LO2 : Use SQL to access data in the database that will be processed as information LO2 : Mahasiswa diharapkan mampu menggunakan SQL untuk mengakses...

LECTURE NOTES Data and Information Management Week ke - 5 SQL: Data Manipulation Language  LEARNING OUTCOMES LO2 : Use SQL to access data in the database that will be processed as information LO2 : Mahasiswa diharapkan mampu menggunakan SQL untuk mengakses data di database yang diproses menjadi informasi. OUTLINE MATERI : 1. SELECT 2. Grouping 3. Subqueries 4. Combining Table 5. ANY and ALL 6. Exist and Not Exist 7. Update 8. Delete 9. Anomaly Avoidance Data and Information Management  SQL: Data Manipulation Language A. SELECT Tujuan dari pernyataan SELECT adalah untuk mengambil dan menampilkan data dari satu atau lebih tabel basis data. Perintah SELECT merupakan perintah yang kuat karena mampu melakukan operasi relasional aljabar seperti pilihan, proyeksi, penggabungan dalam sebuah kalimat. Bentuk utama dari perintah select : SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [,...]} FROM TableName [alias] [,...] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList] Urutan pemrosesan perintah SELECT yaitu : FROM adalah tabel yang dipilih atau yang digunakan. WHERE menyaring baris yang sesuai kondisi yang diinginkan. GROUP membentuk kelompok baris dengan nilai kolom yang sama. HAVING menyaring group yang sesuai dengan kondisi. ORDER menentukan urutan output. Contoh 1: SELECT* FROM Staff; Hasil yang didapat dari query diatas adalah : Data and Information Management  Gambar 5.1. Hasil query SELECT * from Staff Contoh 2, SELECT dengan kolom tertentu : SELECT staffNo, fName, IName, salary FROM Staff; Hasil yang didapat : Gambar 5.2. Hasil dari Query SELECT pada kolom tertentu Contoh 3, menggunakan DISTINCT : SELECT DISTINCT propertyNo FROM Viewing; Hasil yang didapatkan : Gambar 5.3. Hasil Query SELECT DISTINCT Data and Information Management WHERE Clause Pada contoh sebelumnya klausa menggunakan perintah SELECT untuk mengambil data pada setiap baris pada tabel. Namun kadang dibutuhkan pembatasan dari baris tersebut yaitu menggunakan WHERE yang diikuti kondisi yang diinginkan. 5 pembatas pencarian yaitu : 1. Comparison untuk membandingkan nilai antar ekspresi 2. Range untuk melihat hasil perbandingan sesuai dengan settingan. 3. Set membership untuk Test apakah nilai ekspresi sama dengan salah satu dari serangkaian nilai. 4. Pattern match melihat hasil perbandingan sesuai dengan settingan Contoh 1, pencarian dengan perbandingan: SELECT staffNo, fName, IName, position, salary FROM Staff WHERE salary > 10000; Hasil dari query diatas : Gambar 5.4. Hasil Query SELECT Comparison Contoh 2, pencarian dengan perbandingan dengan kondisi OR : SELECT * FROM Branch WHERE city = ‘London’ OR city = ‘Glasgow’; Hasilnya yaitu : Gambar 5.5. Hasil Query SELECT Comparison OR Data and Information Management Contoh 3, penggunaan Range(Between) : SELECT staffNo, fName, IName, position, salary FROM Staff WHERE salary BETWEEN 20000 AND 30000; Hasil yang didapat : Gambar 5.6. Hasil Query SELECT Range Between Contoh 4, Search Condition (NOT/ NOT IN) : SELECT staffNo, fName, IName, position FROM Staff WHERE position IN (‘Manager’, ‘Supervisor’); Hasil query diatas : Gambar 5.7. Hasil Query SELECT Condition IN Contoh 5, penggunaan Like/Not Like : SELECT ownerNo, fName, IName, address, telNo FROM PrivateOwner WHERE address LIKE ‘%Glasgow%’; Hari dari query tersebut : Gambar 5.8. Hasil Query SELECT Like Data and Information Management  Contoh 6, penggunaan IS NULL/NOT NULL : SELECT clientNo, viewDate FROM Viewing WHERE propertyNo = ‘PG4’ AND comment IS NULL; Gambar 5.9. Hasil Query SELECT IS NULL Contoh 7, penggunaan ORDER : SELECT staffNo, fName, IName, salary FROM Staff ORDER BY salary DESC; Hasil yang didapat : Gambar 5.10. Hasil Query SELECT ORDER BY B. Grouping Kueri yang menyertakan klausa GROUP BY disebut Grouped query (kueri yang dikelompokkan), karena kueri tersebut mengelompokkan data dari tabel SELECT dan menghasilkan satu baris ringkasan untuk setiap grup. Kolom yang dinamai dalam klausa GROUP BY disebut grouped column. Standar ISO membutuhkan klausa SELECT dan klausa GROUP BY untuk diintegrasikan secara erat. Ketika GROUP BY digunakan, setiap Data and Information Management item dalam daftar SELECT harus bernilai tunggal per grup. Selain itu, klausa SELECT mungkin hanya berisi: 1. Nama kolom; 2. Fungsi agregat; 3. Konstanta; 4. Ekspresi yang melibatkan kombinasi elemen-elemen ini. Contoh Grouping SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum FROM Staff GROUP BY branchNo ORDER BY branchNo; Query yang dihasilkan : Gambar 5.11. Hasil Query SELECT GROUP BY Grouping terbatas (HAVING) Klausa having dirancang untuk digunakan pada klausa GROUP BY untuk membatasi kolom yang akan muncul pad ahasil akhir. Mirip dengan WHERE. Tetapi memiliki perbedaan yaitu Klausa WHERE menyaring baris individu ke dalam tabel hasil akhir, sedangkan grup filter HAVING masuk ke dalam tabel hasil akhir. Contoh : SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum FROM Staff GROUP BY branchNo HAVING COUNT(staffNo) > 1 Data and Information Management  ORDER BY branchNo; Hasilnya adalah : Gambar 5.12. Hasil Query Menggunakan HAVING C. Subqueries Beberapa pernyataan SELECT dapat disisipkan pada pernyataan SELECT lainnya untuk membantu menentukan hasil akhir dengan lebih rinci, penyataan ini disebut SUBQUERIES. Subqueries dapat dibagi menjadi: 1. Subkueri skalar mengembalikan satu kolom dan satu baris, yaitu, nilai tunggal. Pada prinsipnya, subkueri skalar dapat digunakan kapan pun nilai tunggal diperlukan 2. Subkueri baris menghasilkan banyak kolom, tetapi hanya satu baris. Subkueri baris dapat digunakan setiap kali konstruktor nilai baris diperlukan, biasanya dalam predikat. 3. Subkueri tabel mengembalikan satu atau beberapa kolom dan beberapa baris. Sebuah subquery table dapat digunakan kapan saja sebuah tabel diperlukan, misalnya, sebagai operan untuk predikat IN. Contoh subquery : SELECT staffNo, fName, IName, position FROM Staff WHERE branchNo = (SELECT branchNo FROM Branch WHERE street = ‘163 Main St’) Hasil dari subquery tersebut adalah : Gambar 5.13. Hasil Subquery pegawai yang bekerja di jalan ‘163 Main St’ Data and Information Management  Penjelasan dari query tersebut adalah Pernyataan SELECT dalam (SELECT branchNo FROM Branch..) Menemukan nomor cabang yang sesuai dengan cabang dengan nama jalan '163 Main St' (hanya akan ada satu nomor cabang tersebut, jadi ini adalah contoh dari subkueri skalar). Setelah memperoleh nomor cabang ini, pernyataan SELECT eksternal kemudian mengambil rincian dari semua staf yang bekerja di cabang ini. Dengan kata lain, SELECT bagian dalam menghasilkan tabel hasil yang berisi satu nilai 'B003', sesuai dengan cabang di ‘163 Main St’. Contoh subquery menggunakan fungsi matematika SELECT staffNo, fName, IName, position, salary – (SELECT AVG(salary) FROM Staff) AS salDiff FROM Staff WHERE salary > (SELECT AVG(salary) FROM Staff); Hasil query diatas ialah : Gambar 5.14. Hasil Subquery dengan fungsi matematika D. Combining Table Semua contoh yang telah kami pertimbangkan sejauh ini memiliki batasan utama: kolom yang muncul di tabel hasil semuanya berasal dari satu tabel. Dalam banyak kasus, ini tidak cukup untuk menjawab pertanyaan umum yang akan dimiliki user. Untuk menggabungkan kolom dari beberapa tabel ke tabel hasil, kita perlu menggunakan operasi gabungan. Operasi gabungan SQL menggabungkan informasi dari dua tabel atau lebih dengan membentuk pasangan baris terkait dari dua tabel atau lebih. Pasangan baris yang membentuk tabel gabungan adalah tempat-tempat di mana kolom pencocokan di masing- masing dari dua tabel memiliki nilai yang sama. Data and Information Management  Jika kita perlu memperoleh informasi dari lebih dari satu tabel, pilihannya adalah antara menggunakan subquery dan menggunakan gabungan. Jika tabel hasil akhir mengandung kolom dari tabel yang berbeda, maka kita harus menggunakan gabungan. Untuk melakukan gabung, kami cukup memasukkan lebih dari satu nama tabel dalam klausa FROM, menggunakan koma sebagai pemisah, dan biasanya termasuk klausa WHERE untuk menentukan kolom gabung (s). Juga dimungkinkan untuk menggunakan alias untuk tabel yang disebutkan dalam klausa FROM. Dalam hal ini, alias dipisahkan dari nama tabel dengan spasi. Alias dapat digunakan untuk memenuhi syarat nama kolom setiap kali ada ambiguitas mengenai sumber nama kolom. Ini juga dapat digunakan sebagai notasi singkat untuk nama tabel. Jika alias disediakan, itu dapat digunakan di mana saja di tempat nama tabel. Contoh Join SELECT c.clientNo, fName, IName, propertyNo, comment FROM Client c, Viewing v WHERE c.clientNo = v.clientNo; Untuk mendapatkan baris yang diperlukan, kami menyertakan baris-baris dari kedua tabel yang memiliki nilai identik di kolom clientsNo, menggunakan kondisi pencarian (c.clientNo = v.clientNo). Kami menyebut dua kolom ini kolom yang cocok untuk dua tabel. Hasil query diatas : Gambar 5.15. Hasil Join Table Data and Information Management Contoh SORTING JOIN : SELECT s.branchNo, s.staffNo, fName, IName, propertyNo FROM Staff s, PropertyForRent p WHERE s.staffNo = p.staffNo ORDER BY s.branchNo, s.staffNo, propertyNo; Hasilnya adalah : Gambar 5.16. Hasil Sorting Join Table Contoh Three Table Join : SELECT b.branchNo, b.city, s.staffNo, fName, IName, propertyNo FROM Branch b, Staff s, PropertyForRent p WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo ORDER BY b.branchNo, s.staffNo, propertyNo; Hasilnya yaitu : Gambar 5.17. Hasil Join Tiga Tabel Data and Information Management  Gambar 5.18. Tabel yang akan digunakan selanjutnya Gambar 5.19 Prinsip Join Table (sumber www.w3school.com) 1. Inner join SELECT b.*, p.* FROM Branch1 b, PropertyForRent1 p WHERE b.bCity = p.pCity; Hasilnya : Gambar 5.20. Hasil Inner Join 2. Left Outer Join / Left Join SELECT b.*, p.* FROM Branch1 b LEFT JOIN PropertyForRent1 p ON b.bCity = p.pCity; Hasilnya: Data and Information Management  Gambar 5.21. Hasil Left Outer Join 3. Right Outer Join / Right Join SELECT b.*, p.* FROM Branch1 b RIGHT JOIN PropertyForRent1 p ON b.bCity = p.pCity; Hasilnya : Gambar 5.22. Hasil Right Outer Join 4. Full Outer Join (Full Join) SELECT b.*, p.* FROM Branch1 b FULL JOIN PropertyForRent1 p ON b.bCity = p.pCity; Hasilnya : Gambar 5.23. Hasil Full Outer Join E. ANY and ALL Kata kunci ANY dan ALL dapat digunakan dengan subkueri yang menghasilkan satu kolom angka. Jika subquery didahului oleh kata kunci ALL, kondisinya akan benar hanya jika dipenuhi oleh semua nilai yang dihasilkan oleh subquery. Jika subquery didahului oleh Data and Information Management kata kunci ANY, kondisinya akan benar jika dipenuhi oleh salah satu (satu atau lebih) nilai yang dihasilkan oleh subquery. Jika subquery kosong, kondisi ALL mengembalikan true, kondisi ANY mengembalikan false. Standar ISO juga memungkinkan kualifikasi SOME digunakan sebagai pengganti ANY. Contoh penggunaan ANY/SOME : SELECT staffNo, fName, IName, position, salary FROM Staff WHERE salary > SOME (SELECT salary FROM Staff WHERE branchNo = ‘B003’); Hasil query diatas : Gambar 5.24. Hasil ANY/SOME Contoh penggunaan ALL : SELECT staffNo, fName, IName, position, salary FROM Staff WHERE salary > ALL (SELECT salary FROM Staff WHERE branchNo = ‘B003’); Hasilnya yaitu : Gambar 5.25. Hasil penggunaan ALL Data and Information Management F. Exist and Not Exist Kata kunci EXIST dan NOT EXIST dirancang untuk digunakan hanya dengan subkueri. Mereka menghasilkan hasil benar/salah yang sederhana. EXISTS benar jika dan hanya jika ada setidaknya satu baris dalam tabel hasil yang dikembalikan oleh subquery; salah jika subquery mengembalikan tabel hasil kosong. NOT EXISTS adalah kebalikan dari EXISTS. Karena EXISTS dan NOT EXISTS hanya memeriksa ada atau tidaknya baris dalam tabel hasil subquery, subquery dapat berisi sejumlah kolom. Untuk kesederhanaan, subquery yang mengikuti salah satu kata kunci ini biasanya berbentuk: (SELECT * FROM...) Contoh query menggunakan EXIST : SELECTstaffNo, fName, IName, position FROM Staff s WHERE EXISTS (SELECT * FROM Branch b WHERE s.branchNo = b.branchNo AND city = ‘London’); Hasilnya : Gambar 5.26. Hasil query EXIST G. Update Pernyataan UPDATE memungkinkan isi baris yang ada dalam tabel menjadi berubah. Format perintahnya: UPDATE TableName SET columnName1 = dataValue1 [, columnName2 = dataValue2...] [WHERE searchCondition] TableName dapat berupa nama tabel dasar atau view yang dapat diupdate. Klausa SET menetapkan nama dari satu atau lebih kolom yang akan diperbarui. Klausa WHERE Data and Information Management  bersifat opsional; jika dihilangkan, setiap baris dari kolom yang dipilih dalam tabel akan diperbarui. Jika klausa WHERE ditentukan, hanya baris yang memenuhi pencarian yang diperbarui. DataValue baru (s) harus kompatibel dengan tipe data (s) untuk kolom yang sesuai (s). Contoh Update semua baris : Berikan kenaikan gaji sebesar 3% untuk semua staff UPDATE Staff SET salary = salary*1.03; Karena pembaruan berlaku untuk semua baris dalam tabel staff, klausa WHERE dihilangkan. Contoh Update pada baris yang specific : UPDATE Staff SET salary = salary*1.05 WHERE position = ‘Manager’; Klausa WHERE menemukan baris yang berisi data untuk Manajer dengan pembaruan gaji = gaji * 1,05 hanya diterapkan pada baris-baris khusus yang memiliki posisi Manager saja. Contoh Update multiple columns : Promosikan David Ford (staffNo = ‘SG14’) to Manager rubahlah gajinya menjadi £18,000 UPDATE Staff SET position = ‘Manager’, salary = 18000 WHERE staffNo = ‘SG14’; H. Delete Pernyataan DELETE memungkinkan baris dihapus dari tabel. Format perintahnya adalah: DELETE FROM TableName [WHERE searchCondition] Seperti halnya pernyataan INSERT dan UPDATE, TableName bisa menjadi nama tabel dasar atau tampilan yang dapat dihapus. Pencarian kondisi adalah opsional; jika dihilangkan, semua baris akan dihapus dari tabel. Tetapi tidak menghapus tabel itu sendiri - untuk menghapus isi tabel dan definisi tabel, pernyataan DROP TABLE harus digunakan Data and Information Management  sebagai gantinya. Jika kondisi pencarian ditentukan, hanya baris yang memenuhi syarat yang dihapus. Contoh Menghapus baris secara spesifik DELETE FROM Viewing WHERE propertyNo = ‘PG4’; Klausa WHERE menemukan baris untuk properti PG4 dan operasi penghapusan hanya diterapkan ke baris tertentu ini. Contoh Menghapus seluruh baris Hapus semua baris dari tabel Tampilan DELETE FROM Viewing; Tidak ada klausa WHERE yang telah ditentukan, sehingga operasi penghapusan berlaku untuk semua baris dalam tabel. Kueri ini menghapus semua baris dari tabel, hanya menyisakan definisi tabel, sehingga kami masih dapat memasukkan data ke dalam tabel pada tahap selanjutnya. I. Anomaly Avoidance Proses berikut ini lebih menekankan pada penghindari anomaly. Yaitu insertion anomaly, modification anomaly, dan deletion anomaly. Insertion anomaly adalah kesalahan atau penyimpangan data pada saat penambahan data di database. Modification anomaly adalah kesalahan atau penyimpangan data pada saat update data pada database. Deletion anomaly adalah kesalahan atau penyimpangan data pada saat menghapus data di database. Hal ini dilakukan untuk menghindari terjadinya kesalahan hasil atau informasi yang hasil yang salah. Informasi yang diharapkan dapat dilihat pada gambar 1, 2, dan 3. Gambar 5.27. Isi Tabel Roles Data and Information Management  Gambar 5.28. Isi Tabel User Gambar 5.29. Hasil Join Table Memulai menambahkan data Gambar 5.30. Menambahkan data pada tabel roles Gambar 5.31. Menambahkan data pada tabel User Tambahkan beberapa tabel user lainnya Menghapus data pada tabel user, hasilnya tidak mempengaruhi yang ada di tabel roles. Cobalah update data pada tabel user. hasilnya tidak mempengaruhi yang ada di tabel roles. Jika semuanya sesuai berarti design tabel anda sudah benar dan normal. Data and Information Management  KESIMPULAN 1. SELECT adalah untuk mengambil dan menampilkan data dari satu atau lebih tabel basis data. Perintah SELECT merupakan perintah yang kuat karena mampu melakukan operasi relasional aljabar seperti pilihan, proyeksi, penggabungan dalam sebuah kalimat. 2. Pernyataan UPDATE memungkinkan isi baris yang ada dalam tabel menjadi berubah. 3. Pernyataan DELETE memungkinkan baris dihapus dari tabel 4. Proses pengujian pada DBMS lebih digunakan untuk menghindari anomaly. Yaitu insertion anomaly, modification anomaly, dan deletion anomaly. Data and Information Management  DAFTAR PUSTAKA 1. Connolly, T., & Begg, C. (2015). Database System a Practical Approach to Design, Implementation, and Management 6th Edition. Pearson. Chapter 6 2. Satinder Bal Gupta & Aditya Mittal. (2017). Introduction to Database Management System 2nd Edition. University Science Press. Laxmi Production. Chapter 7. Data and Information Management