372021 Bab IV SQL STRUCTURED QUERY LANGUAGE STMIK

3/7/2021 Bab IV SQL STRUCTURED QUERY LANGUAGE STMIK KHARISMA, PRESENTED BY WAHYUDI 252003@YAHOO. COM 1

3/7/2021 SEJARAH q Tahun 1986, ANSI (American National Standards Institute) dan ISO mengumumkan standard SQL, SQL-86 q IBM merilis Systems Application Architecture) SAA-SQL tahun 1987 q Berturut-turut ANSI merilis SQL-89, SQL- 92, dan SQL- 99 2

3/7/2021 SQL (STRUCTURED QUERY LANGUAGE) SQL adalah bahasa yang digunakan untuk mengelola database relasional SQL adalah bahasa standard untuk sistem manajemen database relasional Sistem database yang menggunakan SQL : 1. 2. 3. 4. 5. 6. Oracle DB 2 Sybase MS SQL MS Access My SQL 3

3/7/2021 TYPE DATA Dibedakan menjadi : 1. 2. 3. 4. Tipe data numerik semua data bilangan yang dapat diperhitungkan bukan angka yang bersifat keterangan, mis jumlah komputer jenis tipe data numerik : integer, float, single, double, currency. Tipe data karakter semua data huruf, angka dan tanda baca jenis tipe data karakter : char, string, text, memo. Tipe data tanggal mendefinisikan waktu jenis tipe data waktu : date, datetime, timestamp. Tipe data boolean ; tipe data khusus untuk menyatakan status benar atau salah, ya atau tidak. 4

3/7/2021 CONT’D. . . kebanyakan mereka memiliki perintah tambahan yang proprietary. Jenis perintah SQL : 1. DDL (Data Definition Language) 2. DML (Data Manipulation Language) 3. DCL (Data Control Language) 5

3/7/2021 SKEMA CONTOH 6

3/7/2021 STRUKTUR DASAR q Select, berkaitan dengan operasi proyeksi pada aljabar relasional. Digunakan untuk mendaftar atribut yang ingin dikeluarkan sebagai hasil query q From, berkaitan dengan operasi produk kartesian (relasi mana yang akan di-scan) q Where, berkaitan dengan predikat seleksi. 7

3/7/2021 OPERASI SELECT Operasi select digunakan untuk mengambil sebagian atau seluruh isi tabel dari suatu basisdata. Contoh : “Tentukan nama-nama dari semua cabang bank dalam relasi loan “ Query-nya : SELECT branch-name FROM loan 8

3/7/2021 OPERASI WHERE Klausa where menspesifikasi kondisi yang harus dipenuhi oleh hasil query – Berkaitan dengan predikat seleksi pada aljabar relasional. Contoh : “Temukan semua loan number untuk pinjaman-pinjaman yang dibuat pada cabang Perryridge dengan jumlah lebih besar dari $1200”. Query-nya ditulis sebagai berikut : SELECT loan-number FROM loan WHERE branch-name = “Perryridge” and amount >1200; 9

3/7/2021 CONT’D. . . q Perbandingan dapat dikombinasikan dengan menggunakan operasi logika and, or, dan not. q Operand hubungan logika dapat menggunakan operasi perbandingan <, <=, >, >=, =, dan <> q Contoh: SELECT loan-number FROM loan WHERE amount <=100000 and amount >=90000; 10

3/7/2021 CONT’D. . . q SQL juga memasukkan perintah between ü q untuk menentukan apakah suatu nilai lebih kecil daripada atau sama dengan suatu nilai lain dan lebih besar daripada atau sama dengan suatu nilai lain. Contoh : “jika diinginkan menemukan loan-number yang jumlah pinjamannya antara $90000 dan $100000” Query ditulis sebagai berikut : SELECT loan-number FROM loan WHERE amount between 90000 and 100000 11

3/7/2021 OPERASI FROM q Klausa from menunjukkan daftar relasi yang dilibatkan dalam query – Berkaitan dengan operasi produk kartesian pada aljabar relasional q Contoh: Produk Kartesian dari borrower x loan select * from borrower, loan 12

3/7/2021 Contoh query : “Untuk semua customer yang mempunyai sebuah pinjaman dari bank, temukan nama dan loan number mereka”. Dalam SQL ditulis : SELECT distinct customer-name, borrower. loan-number FROM borrower, loan WHERE borrower. loan-number = loan-number Contoh: “Tampilkan nama, loan number and loan amount dari semua customer yang memiliki pinjaman di cabang Perryridge” select customer-name, borrower. loan-number, amount from borrower, loan where borrower. loan-number = loan-number and branch-name = ‘Perryridge’ 13

3/7/2021 DDL (DATA DEFINITION LANGUAGE) Merupakan kelompok perintah yang digunakan untuk melakukan pendefinisian tabel. Kelompok perintah DDL dapat membuat : 1. 2. 3. 4. Tabel Mengubah struktur Menghapus tabel Membuat index 14

3/7/2021 DML (DATA MANIPULATION LANGUAGE) Digunakan untuk melakukan manipulasi data dalam database, menambahkan (insert), mengubah (update), menghapus (delete), mengambil dan mencari data (query). Perintah SQL standar tsb dapat digunakan untuk menyelesaikan tugas yang diberikan berhubungan dengan data suatu database. 15

3/7/2021 DCL (DATA CONTROL LANGUAGE) Perintah untuk melakukan pendefinisian pemakai yang boleh mengakses database dan apa saja privilegenya. Fasilitas ini tersedia pada sistem manajemen database yang memiliki fasilitas keamanan dengan membatasi pemakai dan kewenangannya. 16

3/7/2021 DATA DEFINISION LANGUAGE (DDL) digunakan untuk melakukan pembuatan struktur database, mulai dari mendefinisikan database, tabel-tabel dan indexnya, view, dan perintah-perintah berkenaan dengan maintenance dari strukture database itu sendiri. 17

3/7/2021 MEMBUAT DATABASE Perintah CREATE DATABASE namadatabase Perintah ini digunakan pertama kali sebelum membuat tabel, view, fungsi, prosedur atau pun komponen lain suatu sistem database. Contoh : create database datamahasiswa; 18

3/7/2021 MEMBUAT TABEL Perintah CREATE TABLE namatabel ( Field 1 Tipe. Data 1 [, field 2 tipedata 2[, . . . ] ] ); perintah ini diberikan untuk membuat tabel dalam suatu database 19

3/7/2021 CONT’D. . . contoh : create table kota ( kodekota char(3) namakota varchar(35) primary key (kodekota) ); not null, 20

3/7/2021 MENAMBAH FIELD BARU TABEL Perintah ALTER TABLE namatable ADD fieldbaru tipenya; namatabel adalah nama dari tabel yang akan ditambah fieldnya fieldbaru adalah nama field yang akan ditambahkan. 21

3/7/2021 CONT’D. . . Contoh : alter table bukualamat add foreign key (kodekota) reference kota (kodekota); 22

3/7/2021 MENGUBAH LEBAR FIELD TABEL Perintah ALTER TABLE namatabel MODIFY fieldnya tipenya panjangbaru namatabel adalah nama dari table yang akan diubah salah satu fieldnya adalah nama field yang akan diubah lebar fieldnya. tipenya dan panjangbaru merupakan berubahan yang akan diterapkan kepada tabel tsb. 23

3/7/2021 contoh : alter table dbmahasiswa modify (nama_mahasiswa char(45)); 24

3/7/2021 MENGHAPUS TABEL Perintah DROP TABLE namatabel adalah nama dari tabel yang akan dihapus secara fisik. penghapusan menyebabkan struktur dan data yang dibuat akan hilang 25

3/7/2021 MENGHAPUS DATABASE Perintah DROP DATABASE namadatabase; namadatabase adalah nama dari database yang akan dihapus. penghapusan database akan menyebabakan seluruh struktur dan data yang ada didalamnya menjadi hilang 26

3/7/2021 MEMBUAT INDEX Perintah CREATE INDEX namaindeks ON namatabel (namakolom 1[, namakolom 2, . . . ]) namaindeks adalah nama yang diacu untuk mendapatkan data index dari suatu kolom dalam tabel. namatabel adalah nama dari tabel yang kolom-kolomnya akan dibuatkan indexnya. 27

3/7/2021 CONT’D contoh : create index kotaonbukualamat on bukualamat (kodekota); 28

3/7/2021 MENGHAPUS INDEX Perintah DROP INDEX namaindex ON namatabel penghapusan index tidak menyebabkan terhapusnya tabel. penghapusan index tabel suatu kolom hanya menyebabkan prosees pencarian data pada kolom tersebut bisa lebih lambat. 29

3/7/2021 DATA MANIPULATION LANGUAGE (DML) Merupakan bagian dari SQL yang digunakan untuk melakukan manipulasi dalam database (tambah, ubah, hapus, cari) 30

3/7/2021 CONTOH DATASET Loan loan-number branch-name amount L-170 Downtown 3000 L-230 Redwood 4000 L-260 Perryridge 1700 Borrower customer-name loan-number Jones L-170 Smith L-230 Hayes L-155 31

3/7/2021 INSERT Perintah INSERT INTO namatabel (field 1 [, field 2 [, . . . ]]) VALUE (nilai 1 [, nilai 2 [, . . . ]]; atau INSERT INTO namatabel VALUES (nilai 1 [, nilai 2[, . . . ]]); namatabel adalah tabel yang akan diisi data. field 1, field 2. . . Adalah field-field (kolom) dari tabel yang akan diisi nilai 1, nilai 2. . . Adalah data yang akan dimasukkan dalam tiap kolom yang disebutkan pada bagian field. 32

3/7/2021 CONTOH Untuk menambahkan satu tuple dalam relasi digunakan statement insert. Contoh : INSERT INTO account values (“Perryridge”, ”A-9732”, 1200) Query ini identik dengan INSERT INTO account (branch-name, account-number, balance) values (“Perryridge”, ”A-9732”, 1200) Insert juga dapat dilakukan untuk suatu hasil dari query yang lain. Contoh : INSERT INTO account SELECT branch-name, loan-number, 200 FROM loan WHERE branch-name = “Perryridge” 33

3/7/2021 UPDATE Perintah UPDATE namatabel SET field 1 = nilai 1 [, field 2= nilai 2 [, . . . ]] [WHERE kondisi]; namatabel adalah nama dari tabel yang akan diperbaiki datanya field 1 adalah nama field dalam tabel yang akan diubah. nilai 1 adalah data yang akan dimasukan ke dalam field 1 field 2 dan nilai 2 adalah nama field dan datanya dst. kondisi adalah kriteria data dalam tabel yang akan diperbaiki 34

3/7/2021 CONT’D perintah update digunakan untuk memperbaiki data dalam suatu record (baris) dalam suatu tabel, perbaikan dapat dilakukan untuk satu record, beberapa atau seluruh record. Contoh : untuk menaikkan saldo para nasabah sebesar 5% ditulis query sebagai berikut : UPDATE account SET balance = balance * 1. 05 35

3/7/2021 Untuk menaikkan saldo nasabah sebesar 6% bagi nasabah yang saldonya lebih dari $10000 UPDATE account SET balance = balance *1. 06 WHERE balance >10000 question : Apa yang akan terjadi jika dalam pengupdate-an suatu record apabila lupa menulis kondisinya ? 36

3/7/2021 Query yang sama dengan sebelumnya: Naikkan semua account dengan saldo di atas $10, 000 sebesar 6%, account yang lain sebesar 5%. update account set balance = case when balance <= 10000 then balance *1. 05 else balance * 1. 06 end 37

3/7/2021 DELETE Perintah DELETE FROM namatabel [WHERE kondisi]; namatabel adalah nama dari tabel yang akan dihapus datanya. kondisi adalah kriteria data dalam tabel yang akan dihapus. perintah DELETE digunakan untuk melakukan penghapusan record dari suatu tabel yang memiliki kondisi yang dinyatakan dalam pernyataan kondisi. 38

3/7/2021 CONTOH “Hapus semua account pada cabang Perryridge” delete from account where branch-name = ‘Perryridge’ “Hapus semua account di setiap cabang yang berlokasi di Needham city” delete from account where branch-name in (select branch-name from branch where branch-city = ‘Needham’) delete from depositor where account-number in (select account-number from branch, account where branch-city = ‘Needham’ and branch-name = account. branch-name) 39

3/7/2021 CONT’D Apa yang akan terjadi jika dalam DELETE suatu record apabila lupa menulis kondisinya ? 40

3/7/2021 CONTOH Data mahasiswa NIM Nama Jurusan 2901 Anjar Sipil 2902 Jasmin Hukum 2903 Bayu Hukum Ingin menghapus data Mahasiswa dengan NIM = 2902 : DELETE from Mahasiswa Where NIM = 2902; Hasil NIM Nama Jurusan 2901 Anjar Sipil 2903 Bayu Hukum 41

3/7/2021 CONTOH Jika ingin menghapus record mahasiswa bernama anjar dan Bayu bersamaan, dapat digunakan perintah : DELETE form Mahasiswa where Nama =‘Anjar’ or Nama=‘Bayu’; Beberapa hal yang patut diperhatikan dalam penulisan perintah SQL adalah: 1. 2. Perhatikan huruf besar - huruf kecil. Agus tidak sama dengan agus. Gaji_Pegawai tidak sama dengan Gaji. Pegawai. Jangan lupa untuk membubuhi tanda titik koma ( ; ) di setiap akhir penulisan perintah. 42

3/7/2021 SELECT Perintah SELECT (* | field 1 [, field 2 [, . . . ]]) FROM namatabel [WHERE kondisi] namatabel adalah nama dari tabel yang akan ditampilkan datanya field 1, field 2. . . Adalah nama field yang akan ditampilkan datanya. * digunakan untuk menampilkan seluruh field dari tabel kondisi adalah kriteria data dalam table yang akan ditampilkan Perintah SELECT digunakan untuk menampilkan isi dari suatu tabel 43

3/7/2021 KONDISI LIKE merupakan kata kunci dalam SQL yang digunakan untuk mendefinisikan suatu kriterisa yang lebih fleksibel. Kondisi yang dinyatakan dengan menggunakan LIKE dapat memfilter data sehingga dapat menampilkan suatu kriteria seolah dengan menggunakan bahasa inggris. 44

3/7/2021 CONT’D. . . Perintah SELECT * FROM namatabel WHERE namafield LIKE ‘datadicari’; perintah ini akan menampilkan seluruh record dalam tabel yang memiliki data dalam nama field yang disebutkan dengan “datadicari” 45

3/7/2021 Perintah SELECT * FROM namatabel WHERE namafield LIKE ‘datadicari%’; perintah ini akan menampilkan seluruh record dalam tabel yang memiliki data dalam nama field yang disebutkan diawali dengan “datadicari” 46

3/7/2021 Perintah SELECT * FROM namatabel WHERE namafield LIKE ‘%datadicari’; perintah ini akan menampilkan seluruh record dalam tabel yang memiliki data dalam nama field yang disebutkan pada bagian akhirnya ada “datadicari” 47

3/7/2021 Perintah SELECT * FROM namatabel WHERE namafield LIKE ‘%datadicari%’; perintah ini akan menampilkan seluruh record dalam tabel yang memiliki data dalam nama field yang disebutkan mengandung “datadicari” 48

3/7/2021 VIEW Perintah CREATE VIEW namaview AS ekspresi. Query namaview adalah nama dari view yang akan dibuat ekspresi. Query adalah perintah select dan kondisi query yang ditentukan sama seperti halnya pada saat melakukan perintah select dengan menggunakan kondisi. 49

3/7/2021 DATA CONTROL LANGUAGE (DCL) Terdiri atas sekelompok perintah SQL untuk memberikan hak otorisasi mengakses database, mengalokasikan space, pendefinisian space, pengauditan penggunaan database. Secara umum DCL merupakan bahasa yang digunakan untuk melakukan pengelolahan pemakai yang dapat melakukan akses dan manipulasi database terutama perintah GRANT dan REVOKE 50

3/7/2021 CONT’D Perintah COMMIT dan ROLLBACK merupakan kelengkapan fasilitas dalam pembuatan aplikasi yang memungkinkan suatu transaksi yang terjadi untuk dapat segera disimpan atau dibatalkan transaksinya. 51

3/7/2021 FUNGSI AGREGAT Fungsi yang disediakan oleh SQL untuk melakukan ringkasan (summary) data, bukan menampilkan data baris per baris. Fungsi Agregat di SQL : 1. 2. 3. 4. 5. Sum() Avg() Max() Min() Count() 52

3/7/2021 Fungsi agregat dapat disisipkan pada perintah SELECT, yang digunakan untuk melakukan manipulasi sederhana ataupun untuk mendapatkan informasi dari suatu tabel. 53

3/7/2021 SUM sum(namafield) merupakan fungsi agregat yang digunakan untuk melakukan penjumlahan isi field yang bertipe numerik yang namanya disebutkan padan namafield yang dijadikan parameter pada fungsi sum() 54

3/7/2021 CONTOH Tabel Karyawan Kode Nama Gaji KP 01 Amrin 200000 KP 02 Camelia 300000 KP 03 Bembi 100000 SELECT SUM(gaji) From Karyawan Hasil Gaji 600000 55

3/7/2021 AVG avg(namafield) fungsi ini digunakan untuk mendapatkan nilai rata-rata suatu field yang bertipe numerik yang namanya disebutkan sebagai parameter pada fungsi avg(). 56

3/7/2021 CONTOH SELECT AVG(gaji) From Karyawan Hasil Gaji 200000 57

3/7/2021 MAX max(namafield) fungsi ini digunakan untuk mendapatkan nilai terbesar(maximum) dari field bertipe numerik yang nama fieldnya dijadikan parameter pada fungsi min 58

3/7/2021 CONTOH Tabel Karyawan Kode Nama Gaji KP 01 Amrin 200000 KP 02 Camelia 300000 KP 03 Bembi 100000 select MAX(gaji) from Karyawan; Hasil : Gaji 300000 59

3/7/2021 MIN min(namafield) fungsi ini digunakan untuk mendapatkan nilai terkecil (minimum) dari field bertipe numerik yang nama fieldnya dijadikan parameter pada fungsi min 60

3/7/2021 CONTOH select MIN(gaji) from Karyawan; Gaji 100000 61

3/7/2021 COUNT count(namafield) digunakan untuk mengetahui jumlah record dari suatu tabel. Jumlah record yang ditampilkan adalah jumlah record berdasarkan perintah SELECT 62

3/7/2021 CONTOH SELECT COUNT(*) From Karyawan 3 63

3/7/2021 CONTOH FUNGSI AGREGAT sql> use dbmahasiswa; database changed sql>select count(*) from bukumahasiswa +--------+ | count(*) | |---------| | 2 | +---------+ 64

3/7/2021 GROUP BY Perintah Group By memiliki kegunaan untuk melakukan perhitungan berdasarkan kriteria tertentu. 65

3/7/2021 CONTOH Pegawai_baru Kode Nama Asal Pendidikan Gaji PB 01 Ronald Jakarta S 1 400000 PB 02 Made Bali S 1 300000 PB 03 Aziz Semarang S 1 300000 PB 04 Mustofa Semarang D 3 250000 PB 05 Eka Jakarta S 1 275000 PB 06 Gozali Yogya D 3 200000 PB 07 Dani Jakarta S 1 350000 66

3/7/2021 Dari tabel Pegawai_baru, kita ingin menampilkan gaji tertinggi / maksimum yang diperoleh pegawai berdasarkan pendidikannya. select Pendidikan, max(Gaji) from Pegawai_baru GROUP BY Pendidikan; Hasil D 3 250000 S 1 400000 67

3/7/2021 select Asal, count(Asal) from Pegawai_baru GROUP BY Asal; Bali 1 Jakarta 3 Semarang 2 Yogya 1 68

3/7/2021 select Pendidikan, count(Pendidikan), sum(Gaji) from Pegawai_baru GROUP BY Pendidikan; D 3 2 162500 S 1 5 450000 69

3/7/2021 REFERENCE http: //www. 1 keydata. com/sql/ 70

3/7/2021 Terimakasih STMIK Kharisma yudi@attglobal. net 71