SQL Advance Query & Table Management
Outline Workshop part 2
-
WHERE IN operator
-
LIKE operator
-
HAVING clause
-
LIMIT clause
-
DISTINCT operator
-
UNION operator
-
SQL Functions
-
UPDATE function
-
DELETE statement
-
ALTER table
-
Table Relation
-
COLUMN INDEX
-
JOIN table
-
SUBQUERY
Contoh Data Analytics
![](https://i.stack.imgur.com/kOnzy.gif)
![](https://i.stack.imgur.com/kOnzy.gif)
data.ssbrn.com
analytics.ssbrn.com
START XAMPP
![](https://i.stack.imgur.com/kOnzy.gif)
IMPORT DATABASE
![](https://s3.amazonaws.com/media-p.slid.es/uploads/2217191/images/11213810/pasted-from-clipboard.png)
WHERE IN OPERATOR
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
-- 'IN OPERATOR', MERUPAKAN SINGKATAN DARI 'OR OPERATOR'
SELECT column_name(s)
FROM table_name
WHERE column_name = value1 OR column_name = value2 OR ...;
LIKE Operator
SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;
Digunakan untuk mencari data menggunakan pattern (pola)
LIKE Operator
- The percent sign (%) represents zero, one, or multiple characters
- The underscore sign (_) represents one, single character
![](https://s3.amazonaws.com/media-p.slid.es/uploads/2217191/images/11213505/pasted-from-clipboard.png)
LIKE Operator
Buat query pada tabel `data_symbol` yang menampilkan perusahaan berjenis bank
LATIHAN:
SELECT * FROM `data_symbol`
WHERE symbol_name LIKE 'bank%';
HAVING Clause
SELECT kolom FROM tabel GROUP BY kolom
HAVING condition;
-
Untuk melakukan filter query berdasarkan functions dan groupings, dimana tidak bisa dilakukan menggunakan query `WHERE`
-
`WHERE` digunakan untuk filter data berdasarkan baris data / per baris
HAVING Clause
SELECT provinsi, COUNT(kota) AS jumlah_kota FROM wilayah_indonesia
GROUP BY provinsi HAVING COUNT(kota) < 50;
-
Query diatas digunakan untuk menghitung jumlah kota / kabupaten per provinsi dan hanya menampilkan provinsi yang memiliki jumlah kota < 50
HAVING Clause
Buat query pada tabel `penjualan_mobil` yang menampilkan merk, warna, total penjualan. Dikelompokan berdasarkan merk & warna, filter total penjualan yang lebih dari (>) 10
LATIHAN:
HAVING Clause
SELECT merk, warna, SUM(jumlah)
FROM penjualan_mobil
GROUP BY merk, warna
HAVING SUM(jumlah) > 10;
-
Query diatas digunakan untuk menghitung jumlah kota / kabupaten per provinsi dan hanya menampilkan provinsi yang memiliki jumlah kota < 50
LIMIT Clause
SELECT kolom FROM tabel LIMIT 10;
SELECT kolom FROM tabel LIMIT 10 OFFSET 5;
SELECT kolom FROM tabel LIMIT 5, 10;
`LIMIT` membatasi data berdasarkan hasil query
(bukan berdasar urutan `PRIMARY KEY` [ex: id] )
LIMIT Clause
SELECT kolom FROM tabel LIMIT 10;
SELECT kolom FROM tabel LIMIT 10 OFFSET 5;
SELECT kolom FROM tabel LIMIT 5, 10;
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
---|
0 |
---|
start = 1
finish = 10
offset = 0 total = 10
start = offset + 1
finish = offset + total
LIMIT Clause
SELECT kolom FROM tabel LIMIT 10;
SELECT kolom FROM tabel LIMIT 10 OFFSET 5;
SELECT kolom FROM tabel LIMIT 5, 10;
start = 6
finish = 15
0 |
---|
... | 5 | 6 | 7 | 8 | 9 | 10 | 11 | ... | n |
---|
start = offset + 1
finish = offset + total
offset = 0 total = 10
LIMIT Clause
SELECT kolom FROM tabel LIMIT 10;
SELECT kolom FROM tabel LIMIT 10 OFFSET 5;
SELECT kolom FROM tabel LIMIT 5, 10;
start = 6
finish = 15
0 |
---|
... | 5 | 6 | 7 | 8 | 9 | 10 | 11 | ... | n |
---|
start = offset + 1
finish = offset + total
LIMIT offset, total
offset = 0 total = 10
SELECT DISTINCT
SELECT DISTINCT kolom FROM tabel
distinct ( different )
Data pada kolom biasanya memiliki value yang sama (duplikat), query ini digunakan untuk filter data duplikat sehingga menghasilkan data yang unique
SELECT DISTINCT
Buat query pada tabel `wilayah_indonesia` untuk menampilkan nama provinsi
LATIHAN:
SELECT DISTINCT
SELECT DISTINCT provinsi FROM wilayah_indonesia;
![](https://s3.amazonaws.com/media-p.slid.es/uploads/2217191/images/11209508/pasted-from-clipboard.png)
UNION Operator
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
UNION operator digunakan untuk menggabungkan hasil dari dua query atau lebih.
-
Setiap `SELECT statement` dalam UNION harus memiliki jumlah kolom yang sama
-
Kolom harus memiliki tipe data yang similar
-
Kolom pada setiap `statement` harus memiliki urutan yang sama
UNION ALL Operator
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
UNION ALL tidak melakukan filter pada data duplikat
UNION ALL Operator
Buat query union (gabungan) antar tabel `penjualan_mobil` dan `penjualan_mobil_cabang`, filter tanggal agar menampilkan data / tanggal yang sama
LATIHAN:
SQL Functions
![](https://i.stack.imgur.com/kOnzy.gif)
SQL Functions
/* CONTOH PENGGUNAAN FUNCTION */
SELECT MAX(kolom) FROM tabel;
SELECT AVG(kolom) FROM tabel;
SELECT SUM(kolom) FROM tabel;
SELECT kolom FROM table WHERE YEAR(kolom_tanggal) = 2024;
CREATE TABLE name_tabel (
kolom_tanggal date NOT NULL DEFAULT CURRENT_TIMESTAMP,
...
);
SQL Functions
Latihan:
Pada tabel `penjualan_mobil`, hitung rata-rata penjualan & total penjualan untuk bulan Januari tahun 2024
SELECT AVG(jumlah), SUM(jumlah) FROM `penjualan_mobil`
WHERE YEAR(tanggal) = 2024 AND MONTH(tanggal) = 01
UPDATE Statement
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
NOTICE
Apabila tidak ada statement `WHERE`, maka semua data pada tabel akan ikut ter-update
Data yang telah dirubah tidak dapat di-undo
DELETE Statement
DELETE FROM table_name WHERE condition;
NOTICE
Apabila tidak ada statement `WHERE`, maka semua data pada tabel akan ikut ter-update
Data yang telah dihapus tidak dapat di-undo
TRUNCATE Statement
TRUNCATE TABLE table_name;
NOTICE
Data yang telah dihapus tidak dapat di-undo
Digunakan untuk reset data pada tabel
DROP TABLE Statement
DROP TABLE table_name;
NOTICE
Data yang telah dihapus tidak dapat di-undo
Digunakan menghapus tabel
ALTER TABLE
ALTER TABLE digunakan untuk menambahkan, menghapus, atau memodifikasi kolom pada tabel. ALTER TABLE juga digunakan untuk menambahkan dan menghapus relasi pada tabel.
ALTER TABLE
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
TABLE RELATION
![](https://s3.amazonaws.com/media-p.slid.es/uploads/2217191/images/11213047/pasted-from-clipboard.png)
TABLE RELATION
ALTER TABLE `distrik`
ADD FOREIGN KEY (`kota`)
REFERENCES `kota`(`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;
![](https://s3.amazonaws.com/media-p.slid.es/uploads/2217191/images/11213047/pasted-from-clipboard.png)
TABLE RELATION
ALTER TABLE `distrik`
ADD FOREIGN KEY (`kota`)
REFERENCES `kota`(`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;
Opsi `ON DELETE` & `ON UPDATE`
CASCADE | Apabila data pada 'parent' dihapus / diupdate maka data yang terhubung akan ikut terhapus |
RESTRICT | Data pada 'parent' tidak dapat dihapus / diupdate apabila masih terdapat data pada 'child' yang terhubung |
SET NULL | Apabila data pada 'parent' dihapus / diupdate maka data yang terhubung akan menjadi NULL |
TABLE RELATION
ALTER TABLE `kota`
ADD FOREIGN KEY (`provinsi`)
REFERENCES `provinsi`(`id`)
ON DELETE CASCADE ON UPDATE CASCADE;
Latihan:
Buat relasi tabel pada tabel `kota` - `provinsi` pada kolom provinsi
COLUMN INDEX
INDEX pada kolom digunakan untuk mempercepat pencarian atau query.
Fungsi:
![](https://static01.nyt.com/images/2021/08/10/dining/10ethnic-aisle1/09ethnic-aisle1-videoSixteenByNineJumbo1600.jpg)
![](https://frylibrary.com.au/wp-content/uploads/2022/03/category-sign-3.jpg)
COLUMN INDEX
INDEX pada kolom digunakan untuk mempercepat pencarian atau query.
Saat melakukan update data pada tabel yang menggunakan INDEX, prosesnya akan lebih lama ( dikarenakan indeks pada database juga memerlukan update )
Fungsi:
Kelemahan:
Jadi, buat INDEX untuk kolom yang akan sering digunakan untuk pencarian saja
COLUMN INDEX
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
-- Menghapus INDEX
ALTER TABLE table_name
DROP INDEX index_name;
Jika menggunakan `UNIQUE INDEX`, maka data pada kolom yang ter-indeks tidak boleh ada yang duplikat / sama
INNER JOIN
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
![](https://s3.amazonaws.com/media-p.slid.es/uploads/2217191/images/11206041/pasted-from-clipboard.png)
LEFT JOIN
![](https://s3.amazonaws.com/media-p.slid.es/uploads/2217191/images/11206042/pasted-from-clipboard.png)
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
RIGHT JOIN
![](https://s3.amazonaws.com/media-p.slid.es/uploads/2217191/images/11206045/pasted-from-clipboard.png)
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
SUBQUERY
SELECT * FROM penjualan_mobil
WHERE jumlah >= (
SELECT AVG(jumlah) FROM penjualan_mobil
)
Query tersebut akan menampilkan data penjualan mobil yang berada di atas rata-rata
Fin.
SQL Basic - Part 2
By Dimas Mahendra
SQL Basic - Part 2
- 48