SQL DATA MANIPULASI 2

SQL Data Manipulation (2)
A. ANY dan ALL
- ANY dan ALL mungkin digunakan dengan subqueries yang menghasilkan
sebuah kolom angka
o ALL menghasilkan kondisi benar jika semua terdapat nilai
o ANY menghasilkan kondisi benar jika terdapat satu atau lebih nilai
- Jika subquery kosong, ALL bernilai benar dan ANY bernilai salah
- SOME mungkin digunakan menggantikan ANY
- Contoh SOME:
Menemukan staff dimana gajinya lebih besar dibandingkan dengan salah satu
staff yang bekerja di cabang B003
SELECT staffNo,fName,lName,position, salary
FROM Staff
WHERE salary>SOME
(SELECT salary
FROM Staff
WHERE branchNo=’B003’);
Output
- Contoh ALL
Mencari staff yang gajinya lebih tinggi daripada gaji semua staff yang bekerja di
cabang B003
SELECT staffNo,fName,lName, position, salary
FROM Staff
WHERE salary>ALL
(SELECT salary
FROM Staff
WHERE branchNo=’B003’);
Output
B. MULTI TABLE QUERIES
- Bisa menggunakan subqueries yang menyediakan kolom hasil yang berasal dari
tabel yang sama
Modul Jurusan Statistika Angkatan 57 Semester IV
Pengurus Angkatan STIS 57
Divisi Akademik
- Jika hasil kolom berasal dari lebih dari satu tabel, maka harus menggunakan join
- Untuk melakukan join, masukkan lebih dari satu tabel pada klausa FROM
- Gunakan koma sebagai pemisah dan khususnya masukkan klausa WHERE untuk
menspesifikasikan beberapa kolom join
- Kemungkinan juga menggunakan sebuah alias untuk sebuah nama tabel di
klausa
- Alias dipisahkan dari nama tabel dengan spasi
- Contoh penggabungan sederhana:
Daftar nama semua klien yang sudah melihat sebuah property dengan komentar
SELECT c.clientNo,c.fName,c.lName,v.propertyNo,v.comment
FROM Client c, Viewing v
WHERE c.clientNo=v.clientNo
Output
Penjelasan
o Hanya baris dari kedua tabel yang mempunyai nilai yang identik/sama
pada kolom clientNo(c.clientNo=v.clientNo) yang masuk dalam output
- SQL menyediakan cara alternative untuk menspesifikasikan join
o FROM Client c JOIN Viewing v ON c.clientNo=v.clientNo
o FROM Client JOIN Viewing USING clientNo
o FROM Client NATURAL JOIN Viewing
- Di setiap kasus, FROM menggantikan FROM yang asli dan WHERE
- Contoh mengurutkan Join:
Untuk setiap cabang, tampilkan daftar nomor dan nama staff yang mengatur
property dan property yang dia atur
SELECT s.branchNo, s.staffNo, s.fName, s.lName, propertyNo
FROM Staff s, PropertyForRent p
WHERE s.staffNo=p.staffNo
ORDER BY s.branchNo,s.staffNo,propertyNo;
Output
- Contoh Mengelompokkan Banyak Kolom
Mencari jumlah property yang dikendalikan oleh setiap staff
SELECT s.branchNo, s.staffNo, COUNT (*) AS myCount
FROM Staff s, PropertyForRent p
WHERE s.staffNo=p.staffNo
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;
Output
Modul Jurusan Statistika Angkatan 57 Semester IV
Pengurus Angkatan STIS 57
Divisi Akademik
C. COMPUTING A JOIN
- Prosedur untuk menghitung hasil dari sebuah join:
o Buat produk Cartesian dari nama tabel pada klausa FROM
o Jika terdapat klausa WHERE, gunakan kondisi pencarian pada setiap
baris tabel produk, sebagai penahan baris yang memuaskan kondisi
pencarian
o Untuk setiap baris tersisa, tentukan nilai untuk setiap item di daftar
SELECT untuk menghasil sebuah baris dalam tabel hasil
o Jika DISTINCT sudah dispesifikasikan, hapus baris duplikasi dari hasil
tabel
o Jika ada klausa ORDER BY, urutkan hasil tabel sesuai kebutuhan
- SQL menyediakan format khusus SELECT untuk produk Cartesian
SELECT [DISTINCT|ALL] {*|columnList}
FROM Table1 CROSSJOIN Table2
- Inner Joins
o Jika satu tabel dari tabel join tidak sama, baris dihilangkan dari tabel
hasil
o Operasi inner join menahan baris yang tidak sesuai dengan kondisi join
o Perhatikan tabel dibawah ini
(Inner) Join dari tabel di atas
SELECT b.*, p.*
FROM Branch1 b, PropertyForRent1 p
WHERE b.bCity=p.pCity;
OUTPUT
Penjelasan:
- Tabel hasil mempunyai dua baris dimana memiliki kota yang sama
- Tidak ada baris untuk kota Bristol dan Aberdeen
- Untuk memasukkan baris yang tidak sama kedalam tabel, gunakan Outer Join
- Contoh Left Outer Join
Daftar cabang dan property yang sama juga dengan cabang yang tidak sama
SELECT b.*, p.*
FROM Branch1 b LEFT JOIN PropertyForRent1 p On b.bCity=p.pCity;
Output
Penjelasan:
Termasuk baris pertama pada tabel pertama (kiri) yang tidak sama dengan baris
tabel kedua (kanan)
Kolom dari tabel kedua tidak diisi atau kosong
- Contoh Right Outer Join
Daftar cabang dan property yang sama juga dengan cabang yang tidak sama
SELECT b.*, p.*
FROM Branch1 b RIGHT JOIN PropertyForRent1 p ON b.bCity=p.pCity;
Modul Jurusan Statistika Angkatan 57 Semester IV
Pengurus Angkatan STIS 57
Divisi Akademik
Output
Penjelasan
Right Outer Join termasuk baris kedua (kanan) tabel yang tidak sama dengan
baris dari tabel pertama(kiri)
Tabel pertama tidak diisi atau kosong
- Contoh Full Outer Join
Daftar cabang dan property yang sama juga dengan cabang yang tidak sama
SELECT b.*,p.*
FROM Branch1 b LEFT JOIN PropertyForRent1 p ON b.bCIty=p.pCity
UNION
SELECT b.*,p.*
FROM Branch1 b RIGHT JOIN PropertyForRent1 p ON b.bCIty=p.pCity;
Output
Penjelasan
- Termasuk baris yang tidak sama di kedua tabel
- Kolom yang tidak sama tidak diisi atau kosong
D. EXISTS DAN NOT EXISTS
- EXISTS dan NOT EXISTS adalah untuk digunakan hanya dengan subqueries
- NOT EXISTS adalah lawan dari EXISTS
- AS (NOT) EXISTS hanya untuk mengecek ada atau tidaknya baris di tabel hasil
subqueries, subquery bisa termasuk banyak angka pada kolom.
- Umumnya untuk subqueri diikuti (NOT) EXISTS dengan bentuk
(SELECT *…)
- Contoh Menggunakan EXISTS
Temukan semua staff yang bekerja di cabang London
SELECT staffNo,fName,lName,position
FROM Staff s
WHERE EXISTS
(SELECT *
FROM Branch b
WHERE s.branchNo=b.branchNo AND b.city=’London’);
Output
Penjelasan
o Kondisi pencarian s.branchNo=b.branchNo dibutuhkan untuk
mempertimbangkan record cabang yang benar untuk setiap anggota staff
o Jika dihilangkan, akan menampilkan semua daftar staff karena
subquerinya adalah
SELECT * FROM Branch WHERE city=’London’
- Akan selalu bernilai benar dan queri akan menjadi
SELECT staffNo,fName,lName, position
FROM Staff
WHERE true;
Modul Jurusan Statistika Angkatan 57 Semester IV
Pengurus Angkatan STIS 57
Divisi Akademik
- Bisa juga queri ini ditulis menggunakan struktur Join
SELECT staffNo,fName,lName,position
FROM Staff s, Branch b
WHERE s.branchNo=b.branchNo AND city=’London’;
E. UNION, INTERSECT DAN DIFFERENCE(EXCEPT)
- Bisa menggunakan operasi UNION, INTERSECTION dan DIFFERENCE untuk
mengkombinasikan hasil dari dua atau lebih queries menjadi satu tabel hasil
o UNION dari dua tabel, A dan B, adalah tabel yang berisi semua baris
dari A atau B atau keduanya
o INTERSECTION adalah tabel terdiri dari semua baris yang berada di
kedua tabel A dan B
o DIFFERENCE adalah tabel terdiri dari semua baris di A tetapi tidak di B
- Dua tabel harus cocok untuk digabung; keduanya harus mempunyai struktur
yang sama
o Terdiri dari jumlah kolom yang sama dan
o Corresponding kolom mempunyai tipe data dan panjang yang sama
- Format klausa operator untuk setiap kasus adalah:
o Op[ALL][CORRESPONDING [BY{column1[,…]}]]
- Jika CORRESPONDING BY dispesifikasikan, set operasi melakukan pada nama
kolom
- Jika CORRESPONDING dispesifikasikan tetapi tidak menggunakan klausa BY,
operasi melakukan pada kolom biasa
- Jika ALL dispesifikasikan, hasil bisa termasuk baris duplikat
- Contoh menggunakan UNION
Daftar semua kota dimana terdapat sebuah kantor cabang atau sebuah property
(SELECT city
FROM Branch
WHERE city IS NOT NULL)
UNION
(SELECT city
FROM PropertyForRent
WHERE city IS NOT
NULL);
Atau
(SELECT *
FROM Branch
WHERE city IS NOT NULL)
UNION CORRESPONDING
BY city
(SELECT *
FROM PropertyForRent
WHERE city IS NOT
NULL);
Output
- Contoh menggunakan INTERSECT
Daftar semua kota dimana terdapat sebuah kantor cabang dan sebuah property
(SELECT city FROM
Branch)INTERSECT
(SELECT city FROM
PropertyForRent);
Atau
(SELECT * FROM Branch)
INTERSECT
CORRESPONDING BY city
(SELECT * FROM
PropertyForRent);
Output
Modul Jurusan Statistika Angkatan 57 Semester IV
Pengurus Angkatan STIS 57
Divisi Akademik
- Penggunaan EXCEPT
Daftar nama kota dimana terdapat kantor cabang tetapi tidak ada property
(SELECT city FROM
Branch)
EXCEPT
(SELECT city FROM
PropertyFromRent);
Atau
(SELECT * FROM Branch)
EXCEPT
CORRESPONDING BY city
(SELECT * FROM
PropertyForRent);
Output
- INSERT
Memasukkan data baru pada relation
Contoh
INSERT INTO Staff
VALUES (‘SG16’ , ‘Alan’, ‘Brown’, ‘Assisstant’, ‘M’, ‘1957-05-25’, 8300,
‘B003’);
- UPDATE
Memperbarui data yang telah ada
Contoh
UPDATE Staff
SET salary=salary*1.03;
- DELETE
Menghapus data yang sudah ada di tabel
Contoh
DELETE FROM Viewing
WHERE propertyNo= ‘PG14’;