Mahasiswa memahami fungsi-fungsi lain di dalam SQL Mahasiswa memahami tentang operasi agregasi Mahasiswa mampu mengaplikasikan operasi agregasi

 Mahasiswa

memahami fungsi-fungsi lain di

dalam SQL  Mahasiswa memahami tentang operasi agregasi  Mahasiswa mampu mengaplikasikan operasi agregasi

2

Review Klausa Where Filter Perbandingan Like IN dan NOT IN Order By Fungsi Agregate Group By Having JOIN

5

 Integrity

constraint merupakan cara deklaratif untuk mendefinisikan rule pada kolom suatu table.

6

1.

2.

3.

NOT NULL constraints constraint ini digunakan untuk aturan yang berhubungan dengan apakah suatu kolom boleh null atau tidak UNIQUE key constraint  constraint ini digunakan untuk aturan yang berhubungan dengan nilai kolom yang bersifat unique. PRIMERY KEY constraint  constraint ini untuk aturan yang berhubungan dengan pengidentifikasian primary key.

7

4. FOREIGN KEY constraint  untuk rule yang berhubungan dengan referential integrity. Constraint ini disebut dengan referential integrity constraint 5. CHECK constraints untuk integrity rules yang bersifat complex

8

 Digunakan

untuk: menjaga konsistensi barisbaris data antara 2 buah tabel  Berkaitan dengan insert, update dan delete data Child Tabel

Tabel mahasiswa berelasi dengan tabel dosen Karenanya, referential integrity constraintnya harus diperhatikan dan dijaga Parent Tabel

9

Aturan untuk Update  berlaku pada proses modifikasi di parent table Cascade  Pembaruan sebuah baris data diikuti

dengan pembaruan baris data pada child table yang terelasikan. Restrict  mencegah proses pembaruan data jika terdapat baris data di child table yang terelasikan. Ignore  mengabaikan referensi. Boleh memperbarui data pada parent, tapi tidak memperbarui data yang berelasi pada child table.

10

Aturan untuk Insert  Tidak boleh menambah data pada child table, jika nilai yang dimasukkan pada kolom yang berelasi tidak terdapat pada parent tabelnya.  Ignore  mengabaikan referensi. Boleh menambah data pada child, walaupun nilai yang dimasukkan pada kolom yang berelasi tidak terdapat pada parent tabel.  Restrict

11

12

13

14

15

 Dependent

relationship dari entitas A terhadap entitas B didefinisikan dengan A sebagian atau seluruhnya diidentifikasi dengan identifier B.

16

CDM

PDM 17

Syntax pembuatan tabel

[aturann] berisi aturan untuk field/kolom ybs, bersifat opsional. Biasanya berupa:  NOT NULL  DEFAULT  [aturanTabel] berisi aturan-aturan yang berlaku untuk tabel tersebut. Jika aturan lebih dari satu, maka dibatasi oleh tanda koma. Aturan tabel biasanya: • primary key () • foreign key () references () [ON DELETE ] [ON UPDATE ]) •  [AturanTabel] bisa diberi nama. Jika diberi nama, maka syntaxnya sbb: CONSTRAINT BASIS DATA TEKNIK INFORMATIKA - ITS 

19

Contoh: Pembuatan tabel Mahasiswa dan set nrp menjadi primary key, nilai default pada jeniskel=”L”, nipdosen mereferensi ke tabel dosen field nip

CREATE TABLE Mahasiswa( NRP CHAR(10) not null, NIP CHAR(18) not null, MHSNAMA VARCHAR2(20), MHSALAMAT VARCHAR2(50), MHSJENISKEL CHAR(1) DEFAULT ‘L’, constraint PK_MAHASISWA primary key (NRP), constraint FK_MHS_DSN foreign key (NIP) REFERENCES Dosen(NIP) ); BASIS DATA TEKNIK INFORMATIKA - ITS

20

Contoh CREATE TABLE Mahasiswa (nrp CHAR(10), nama CHAR(20), nip CHAR(10), PRIMARY KEY (nrp), FOREIGN KEY (nip) REFERENCES Dosen ON DELETE CASCADE ON UPDATE CASCADE )

BASIS DATA TEKNIK INFORMATIKA - ITS

21

SELECT [DISTINCT] select_list FROM table_source [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ]

 Klausa

ini biasanya digunakan untuk:

1.Filter (penyaringan) data. 2.Merelasikan/menghubungkan query yang disusun lebih dari satu tabel. o

Penerapannya adalah dengan memasukkan suatu ekspresi kondisi setelah klausa WHERE: WHERE

o

Jika lebih dari satu, maka bisa ditambahkan dengan AND atau OR (sesuai dengan kebutuhan).

 Jika

kita mengambil informasi dari lebih dari sebuah tabel, maka kita perlu menambahkan kondisi untuk relasi antara kedua tabel tersebut.  Pada dasarnya mirip dengan filter, tapi fungsinya digunakan untuk merelasikan antara tabel-tabel yang diambil informasinya

Penyewaan Anggota No_anggota CHAR(5) Nama_Anggota VARCHAR(50) Alamat_anggota VARCHAR(100)

Kode_Sewa CHAR(5) No_anggota CHAR(5) FK_PENYEWAA_MELAKUKAN_ANGGOTA Tgl_Sewa DATE

Menampilkan Judul_VCD, Kategori_VCD, tgl_harusKembali

FK_PENYEWAA_TERDIRI_D_PENYEWAA

Penyewaan_detail Kode_Sewa KodeVCD biaya_sewa tgl_harusKembali tgl_pengembalian denda

CHAR(5) CHAR(5) CURRENCY DATE DATE CURRENCY

VCD KodeVCD Judul_VCD Kategori_VCD biaya_sewa

CHAR(5) FK_PENYEWAA_BERISI_VCD VARCHAR(50) CHAR(2) CURRENCY

Judul_VCD dan KategoriVCD bisa diambil dari tabel VCD, tetapi tgl_harusKembali tidak ada pada tabel VCD sehingga haris diambil dari tabel Penyewaan_detail. Kedua tabel tersebut berelasi pada Kode_VCD. Sehingga querynya: SELECT Judul_VCD, Kategori_VCD, tgl_harusKembali FROM VCD, Penyewaan_detail WHERE VCD.KodeVCD=Penyewaan_detail.KodeVCD

Jika ada nama field yang sama dari tabel yang disertakan, maka sebelum nama field, berikan nama tabel diikuti sebuah titik (.)  Misal untuk query sebelumnya dengan ditambah menampilkan KodeVCD: 

SELECT VCD.KodeVCD, VCD.Judul_VCD, VCD.Kategori_VCD, Penyewaan_detail.tgl_harusKembali FROM VCD, Penyewaan_detail WHERE VCD.KodeVCD=Penyewaan_detail.KodeVCD

Atau untuk mempersingkat query menggunakan alias SELECT V.KodeVCD, V.Judul_VCD, V.Kategori_VCD, P.tgl_harusKembali FROM VCD V, Penyewaan_detail P WHERE V.KodeVCD=P.KodeVCD

Contoh Menampilkan KodeVCD,Judul_VCD, tgl_harusKembali untuk VCD dengan kategori “kartun” diurutkan berdasarkan JudulVCD SELECT V.KodeVCD, V.Judul_VCD, P.tgl_harusKembali FROM VCD V, Penyewaan_detail P WHERE V.KodeVCD=P.KodeVCD AND V.Kategori_VCD=“kartun” ORDER BY V.Judul_VCD

Where untuk filter atau pencarian data di dalam SQL Server bisa dalam beberapa bentuk ekspresi, yakni:   

operasi pembandingan operasi pendekatan pola operasi pembandingan dengan sekumpulan nilai

Operasi: = < <= >= > <> is 



Khusus untuk pembanding IS, nilai yang diisikan adalah null, yakni sebuah nilai yang belum berisi nilai. nilai null tidak sama dengan nilai 0 pada tipe numerik ataupun string kosong pada tipe string

Basis Data Jurusan Teknik Informatika ITS

Contoh  menampilkan pegawai yang berjenis kelamin laki-laki: SELECT * FROM Employee WHERE Sex=“M”  menampilkan Pegawai yang tahun kelahirannya lebih dari 1965 SELECT * FROM Employee WHERE year(Bdate) > 1965  menampilkan Pegawai wanita yang lahir di bulan Juli:

SELECT * FROM Employee WHERE Sex=“F” AND month(Bdate)=6  menampilkan idKoleksi yang belum dikembalikan (TglKembali belum terisi)

SELECT * FROM Peminjaman WHERE TglKembali IS NULL

Basis Data Jurusan Teknik Informatika ITS

 Operasi

ini, hanya untuk pembandingan nilai bertipe string  Digunakan untuk mengenali string-string yang memiliki pola tertentu.  Di dalam operasi ini, digunakan simbol-simbol berikut: simbol % (Oracle) atau *(ms. Access) artinya mewakili 0 s/d tak terhingga dari sembarang karakter  simbol _ artinya mewakili 1 sembarang karakter 

Basis Data Jurusan Teknik Informatika ITS

Contoh  Menampilkan pegawai yang tinggal di Houston SELECT * FROM Employee WHERE Address LIKE “%houston&"  menampilkan Pegawai yang huruf ketiga namanya adalah “m”: SELECT SSN, Fname FROM Employee WHERE FName LIKE “__d%" “?” dan “_” mewakili satu karakter ? : access _ : oracle

Basis Data Jurusan Teknik Informatika ITS

 Ekspresi

ini digunakan untuk membandingkan dengan sebuah kumpulan nilai  Kumpulan nilai bisa berupa: nilai-nilai yang diisikan  query tunggal query yg hasilnya hanya terdapat sebuah kolom saja 

Basis Data Jurusan Teknik Informatika ITS



Nilai-nilai yang diisikan, maksudnya kita sendiri yang mengisikan nilai-nilai tersebut di dalam query kita.

Contoh  Menampilkan pegawai yang terlibat dalam proyek selain nomer 3 dan 10 SELECT W.ESSN, E.Fname, W.PNO FROM Employee AS E, Works_on AS W WHERE W.PNO NOT IN (3,10) and W.ESSN=E.SSN;

Basis Data Jurusan Teknik Informatika ITS



Query tunggal, yakni nilai-nilai yang digunakan sebagai pembanding diambil dari sebuah query tunggal yang hasilnya hanya satu kolom saja

Contoh

 Menampilkan data pegawai yang tidak pernah terlibat dalam proyek SELECT SSN, Fname FROM Employee WHERE SSN NOT IN (Select distinct ESSN From Works_On)  menampilkan koleksi yang sedang dipinjam: SELECT * FROM koleksi WHERE idKoleksi IN (SELECT idKoleksi FROM Peminjaman WHERE TglKembali is NULL) Basis Data Jurusan Teknik Informatika ITS

Query yang melibatkan fungsi penghitungan.  Fungsi-fungsi tersebut yakni: 

- COUNT(*)  mendapatkan jumlah baris - SUM(kolom)  mendapatkan hasil penjumlahan kolom - MAX(kolom)  mendapatkan nilai tertinggi - MIN(kolom)  mendapatkan nilai terendah - AVG(kolom)  mendapatkan nilai rata-rata

Basis Data Jurusan Teknik Informatika ITS

Tampilkan rata-rata harga sewa VCD koleksi kartun Tampilkan jumlah VCD

SELECT AVG (V.Biaya_Sewa) FROM VCD V WHERE V.Kategori_VCD=“kartun”

SELECT COUNT (*) FROM VCD V

Sintaks:

SELECT [DISTINCT] target-list relation-list FROM WHERE qualification GROUP BY grouping-list [HAVING group-qualification]



target-list terdiri dari: (a) attribute names, dan (b) “terms” berisikan operasi-operasi aggregasi (misal, MIN (S.age)).  “term” adalah atribut yang mengandung fungsi agregasi.  attribute name yang dituliskan dalam target-list harus juga menjadi subset dari grouping-list.



Klausa HAVING digunakan untuk menuliskan spesifikasi terhadapa GROUP

39

 Digunakan

untuk pengelompokan dari fungsi-fungsi

aggregate  Yang penting untuk diperhatikan, kolom-kolom yang disertakan setelah GROUP BY harus sama dengan kolom-kolom yang dipilih pada setelah klausa SELECT yg selain fungsi AGGREGATE

SELECT V.Kategori_VCD, AVG (V.Harga_Sewa) FROM VCD V

 Query

ini ilegal! (Mengapa?) SELECT V.Kategori_VCD, AVG (V.Harga_Sewa) FROM VCD V GROUP BY V.Kategori_VCD

 Jika

untuk filter query biasa kita menggunakan klause where, maka klausa having digunakan untuk filter fungsi-fungsi aggregate. Contoh

 Menampilkan departemen yang jumlah pegawainya lebih dari 2. SELECT DNO, COUNT(*)as [Jumlah Pegawai] FROM Employee GROUP BY DNO HAVING COUNT(*)> 2

 Perintah

order by digunakan untuk mengurutkan data  Order by Asc (ascending) : pengurutan dari kecil ke besar, dari A ke Z  Order by Desc (Descending): pengurutan dari besar ke kecil, dari Z ke A)  Contoh: Tampilkan gaji pegawai urut dari yang terbesar SELECT SSN, FName, Salary FROM Employee ORDER BY salary DESC;

Contoh: select NAMA,EXTRACT (YEAR FROM TO_DATE(TGL_LHR,'DD-MONRR')) AS TAHUN FROM MAHASISWA ORDER BY TAHUN ASC