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

data.ssbrn.com

analytics.ssbrn.com

START XAMPP

IMPORT DATABASE

https://1drv.ms/f/s!Ami0qc6UPz-sujF1paE2CVZUekCb?e=qoWVhY

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

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;

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

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

TABLE RELATION 

ALTER TABLE `distrik`
ADD FOREIGN KEY (`kota`)
REFERENCES `kota`(`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;

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:

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;

LEFT JOIN

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN

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.

Made with Slides.com