Structured Query Language

Structured Query Language

Advanced DDL

31 Mei 2006

© 2006, M. Ramadhan

1

Pendahuluan Yang akan dibahas: Cascading foreign key View lanjut • • • •

Kondisi gabungan Mengubah urutan & nama kolom Kolom turunan Join beberapa tabel

Join sebuah tabel ke dirinya sendiri

31 Mei 2006


2

Cascading Foreign Key (1) Mendefinisikan tabel Mhs dengan atribut NRP_PA sebagai foreign key yang mengacu ke atribut NRP pada tabel Dosen: Create Table Mhs ( NIM Char(10) Not Null, Nama VarChar(20) Not Null, .... Costraint PkMhs Primary key (NIM) Costraint FkMhsDosen Foreign Key (NRP_PA) References Dosen (NRP)) 31 Mei 2006


3

Cascading Foreign Key (2) Ke dalam pernyataan SQL dpt ditambahkan klausa On Delete No Action (baris Dosen tak dpt dihapus), On Update No Action (NRP Dosen tak dpt diubah): Create Table Mhs ( NIM Char(10) Not Null, Nama VarChar(20) Not Null, .... Costraint PkMhs Primary key (NIM) Costraint FkMhsDosen Foreign Key (NRP_PA) References Dosen (NRP) On Delete No Action On Update No Action ) 31 Mei 2006


4

Cascading Foreign Key (3) Klausa On Delete Cascade mengakibatkan bila satu baris data Dosen dihapus maka semua baris data Mhs yang NRP_PA = NRP Dosen, juga dihapus: Create Table Mhs ( NIM Char(10) Not Null, Nama VarChar(20) Not Null, .... Costraint PkMhs Primary key (NIM) Costraint FkMhsDosen Foreign Key (NRP_PA) References Dosen (NRP) On Delete Cascade On Update No Action ) 31 Mei 2006


5

Cascading Foreign Key (4) Klausa On Update Cascade mengakibatkan bila NRP Dosen diubah maka semua baris data Mhs yang NRP_PA = NRP Dosen, juga ikut diubah: Create Table Mhs ( NIM Char(10) Not Null, Nama VarChar(20) Not Null, .... Costraint PkMhs Primary key (NIM) Costraint FkMhsDosen Foreign Key (NRP_PA) References Dosen (NRP) On Delete No Action On Update Cascade ) 31 Mei 2006


6

View : Kondisi/Kriteria Gabungan Gunakan operator And, Or Misalnya membuat view MhsUnggul dari

tabel Mhs yang IPK-nya lebih besar dari 3,5 dan KodeJurusan = 11: Create View MhsUnggul As Select * From Mhs Where IPK > 3.5 And KodeJurusan = 11 31 Mei 2006


7

View : Mengubah Urutan dan Nama Kolom Membuat view Student dengan urutan kolom Nama, NRP, IPK dan nama kolom diubah dg nama lain: Create View Student ( Name, Nama baru IdNumber, kolom GPA ) As Select Nama, Nama NRP, kolom asli IPK From Mhs 31 Mei 2006


8

View : Kolom Turunan Pada view berikut, NamaLengkap merupakan gabungan dari Nama dan Gelar, sedangkan Gaji hasil perhitungan GajiPokok + Tunjangan : Create View DosenJunior ( NomorPokok, NamaLengkap, Gaji ) As Select NRP, Nama + ‘, ‘ + Gelar, GajiPokok + Tunjangan From Dosen 31 Mei 2006


9

View : Join Dua Tabel (1) Tabel Film

Tabel Aktor

Setelah di-join-kan menjadi:

31 Mei 2006


10

View : Join Dua Tabel (2) Tabel Film

Tabel Aktor

Setelah di-join-kan menjadi:

31 Mei 2006


11

View : Inner Join (1) Paling sering digunakan Hanya memberikan baris yg harga foreign key

sebuah tabel cocok dengan harga primary key atau candidate key tabel yang terhubung. Create View NamaView As Select DaftarKolom From Tabel1 Inner Join Tabel2 On KriteriaJoin 31 Mei 2006


12

View : Inner Join (2) Tabel Film

Tabel Aktor

31 Mei 2006


13

View : Inner Join (3) Setelah di-join-kan menjadi:

Create View FilmAktor As Select Film.*, Aktor.FirstName, Aktor,LastName From Film Inner Join Aktor On Film.FilmId = Aktor.FilmId 31 Mei 2006


14

View : Inner Join (4) Tabel Dosen

Tabel Mhs

Hasil inner join:

31 Mei 2006


15

View : Inner Join (5)

Create View PA_Mhs As Select Mhs.*, Dosen.Nama As NamaPA From Dosen Inner Join Mhs On Dosen.Inisial = Mhs.InisialPA 31 Mei 2006


16

View : Outer Join Left Outer Join: Memberikan semua baris tabel

kiri walaupun harga kolom yg terhubung dengan kolom tabel di kanannya tidak cocok. Right Outer Join: Memberikan semua baris

tabel kanan walau pun harga kolom yang terhubung dg kolom tabel di kirinya tidak cocok. Full Outer Join: Memberikan semua baris tabel

kiri dan kanan walau pun harga kolom yang terhubung tidak cocok satu sama lain. 31 Mei 2006


17

View : Left Outer Join (1)

Create View PA_Mhs As Select Dosen.*, Mhs.NIM, Mhs.Nama As NamaMhs From Dosen Left Outer Join Mhs On Dosen.Inisial = Mhs.InisialPA 31 Mei 2006


18

View : Left Outer Join (2) Memberikan hasil:

31 Mei 2006


19

View : Right Outer Join (1)

Create View PA_Mhs As Select Mhs.NIM, Mhs.Nama As NamaMhs, Dosen.* From Dosen Right Outer Join Mhs On Dosen.Inisial = Mhs.InisialPA 31 Mei 2006


20

View : Right Outer Join (2) Memberikan hasil:

31 Mei 2006


21

View : Full Outer Join (1)

Create View PA_Mhs As Select Dosen.*, Mhs.NIM, Mhs.Nama As NamaMhs From Dosen Full Outer Join Mhs On Dosen.Inisial = Mhs.InisialPA 31 Mei 2006


22

View : Full Outer Join (2) Memberikan hasil:

31 Mei 2006


23

Join Tiga Tabel atau Lebih (1)

SELECT tBuku.*, tPenerbit.Nama, tBidangIlmu.Nama AS BidangIlmu FROM tPenerbit INNER JOIN tBuku ON tPenerbit.IdPenerbit = tBuku.IdPenerbit INNER JOIN tBidangIlmu ON tBuku.IdBidangIlmu = tBidangIlmu.IdBidangIlmu 31 Mei 2006


24

Join Tiga Tabel atau Lebih (2) SELECT tBuku.*, tPenerbit.Nama, tBidangIlmu.Nama AS BidangIlmu FROM tPenerbit INNER JOIN tBuku ON tPenerbit.IdPenerbit = tBuku.IdPenerbit INNER JOIN tBidangIlmu ON tBuku.IdBidangIlmu = tBidangIlmu.IdBidangIlmu

Memberikan hasil yang sama dengan: SELECT tBuku.*, tPenerbit.Nama, tBidangIlmu.Nama AS BidangIlmu FROM tPenerbit WHERE tPenerbit.IdPenerbit = tBuku.IdPenerbit AND tBuku.IdBidangIlmu = tBidangIlmu.IdBidangIlmu 31 Mei 2006


25

Join tabel ke dirinya sendiri (1)

31 Mei 2006


26

Join tabel ke dirinya sendiri (2)

CREATE AtasanPegawai AS SELECT Pegawai.* FROM Pegawai INNER JOIN Pegawai AS Pegawai_1 ON Pegawai.NRPAtasan = Pegawai_1.NRP 31 Mei 2006


27

Join tabel ke dirinya sendiri (3) SELECT Pegawai.*, Peg.Nama AS NamaAtasan FROM Pegawai INNER JOIN Pegawai AS Peg ON Pegawai.NRPAtasan = Peg.NRP

Hasilnya:

31 Mei 2006


28

Yang Sudah Dibahas Cascading foreign key View lanjut

• Kondisi gabungan • Mengubah urutan & nama kolom • Kolom turunan • Join beberapa tabel Join sebuah tabel ke dirinya sendiri 31 Mei 2006


29

Daftar Pustaka Connolly, Thomas., et.al., 2002. Database System. Wokingham England, AddisonWesley Publishing Company. Vieira, Robert, 2006. Beginning SQL Server 2005 Programming. Indianapolis, Wiley Publishing, Inc. Otey, Michael dan Conte, Paul, 2001. SQL Server Developer's Guide, Berkeley, Osborne / McGraw-Hill. 31 Mei 2006


30

TAMAT © 2006, M. Ramadhan

31 Mei 2006


31

Structured Query Language

Recommend Documents