Dasar SQL Big Data – Pada artikel kali ini kita akan mengulas fundamental dari SQL (Structured Query Language). Dari apa itu SQL, apa itu basis data relasional, bagaimana benefitnya, apa perbedaan SQL dan No SQL, dan bagaimana perintah-perintah dasar dari SQL.
Apa itu SQL
SQL (Structured Query Language) adalah bahasa yang digunakan untuk mengelola dan memanipulasi data dalam basis data relasional. SQL memungkinkan pengguna untuk membuat, membaca, memperbarui, dan menghapus data di dalam basis data. SQL umumnya digunakan dalam sistem manajemen basis data relasional (RDBMS), seperti MySQL, PostgreSQL, Oracle Database, dan Microsoft SQL Server.
A.Perintah dasar SQL
1.Membuat Table Menggunakan Perintah SQL
CREATE TABLE employee(
id int(11) PRIMARY KEY,
nama varchar(100),
salary decimal(19,4)
);
CREATE TABLE penggajian(
id int(11) PRIMARY KEY,
employeeid int(11),
tanggal date,
bulan int(11),
tahun int(11),
takehomepay decimal(19,4)
);2.Menambahkan Data Di Table
INSERT INTO employee (id,nama,salary) VALUES (1,'GAZA','2500'), (2,'AKRAM','2250'), (3,'HAFIDZ','4500'), (4,'AFIK','2100'), (5,'YAHYA','5000'); INSERT INTO penggajian (id,employeeid,tanggal,bulan,tahun,takehomepay) VALUES (1,1,now(), month(now()),year(now()),2500), (2,1,now(), month(now())+1 ,year(now()),2500), (3,2,now(), month(now()),year(now()),2250), (4,2,now(), month(now())+1 ,year(now()),2250), (5,3,now(), month(now()),year(now()),4500), (6,3,now(), month(now())+1,year(now()),4500), (7,4,now(), month(now()),year(now()),2100), (8,4,now(), month(now())+1,year(now()),2100), (9,5,now(), month(now()),year(now()),5000), (10,5,now(), month(now())+1 ,year(now()),5000);
3.Menampilkan Data
SELECT * FROM employee;
4.Mengupdate Data
UPDATE employee SET salary = 4500 where id=1;
5.Menghapus Data
DELETE FROM Employee WHERE id=2;
B.Memfilter Data Dengan Perintah SQL
Menggunakan where
SELECT * FROM employee where id>2; SELECT * FROM employee where id BETWEEN 2 and 4; SELECT * FROM employee where id in (2,5); SELECT * FROM employee where salary BETWEEN 3000 AND 5000;
Menggunakan like untuk patern
SELECT * FROM employee where name like 'd%'
Fungsi Agregasi
SELECT count(*) as totalkaryawan from employee; SELECT AVG(salary) as ratagaji from employee; SELECT MAX(salary) as gajitertinggi from employee; SELECT MIN(salary) as gajiterendah from employee; SELECT SUM(salary) as totalgajikaryawan from employee;
Group By dan Having
/*ingin tahu total pembayaran gaji 2024*/ SELECT tahun,SUM(takehomepay) as gajidibayar from penggajian GROUP BY tahun; /*ingin tahu total pembayaran gaji per bulan tahun 2024*/ SELECT tahun,bulan,SUM(takehomepay) as gajidibayar from penggajian GROUP BY tahun,bulan; SELECT tahun,employeeid,AVG(takehomepay) as gajidibayar from penggajian GROUP BY tahun,employeeid; SELECT tahun,bulan,SUM(takehomepay) as gajidibayar from penggajian GROUP BY tahun,bulan HAVING bulan>11; SELECT tahun,bulan,sum(takehomepay) as gajidibayar FROM penggajian WHERE employeeid>2 GROUP BY tahun,bulan HAVING bulan > 11;
C.Menggabungkan Tabel Dengan Perintah SQL
INNER JOIN, LEFT JOIN, RIGHT JOIN
CREATE TABLE departemen(
id int(11) PRIMARY KEY,
departement varchar(25)
);
INSERT INTO departemen
VALUES
(1,'IT'),(2,'Marketing');
ALTER TABLE employee ADD COLUMN departemenid int(11);
UPDATE employee SET departemenid = 1 WHERE id<=2;
UPDATE employee SET departemenid = 2 WHERE id>2;
/*INNER JOIN*/
SELECT e.id, e.name, d.departement
FROM
employee e
INNER JOIN departemen d
ON e.departement_id = d.id
/*LEFT JOIN*/
SELECT
departemen.id,
departement,
nama,
salary
FROM
employee
LEFT JOIN
departemen
ON
departemen.id=employee.departemenid;
/*LEFT JOIN*/
SELECT
departemen.id,
departement,
nama,
salary
FROM
departemen
RIGHT JOIN
employee
ON
departemen.id=employee.departemenid;
D.SUB QUERY
SELECT
e.id,
departement,
nama,
tahun,
bulan,
takehomepay
FROM
(SELECT id,departement from departemen ) as departemen
JOIN
(SELECT id,departemenid,nama FROM employee) as employee
ON departemen.id = employee.departemenid
JOIN
(SELECT tahun,bulan,takehomepay,employeeid FROM penggajian) as penggajian
ON employee.id = penggajian.employeeid;E.Data Definition Language (DDL) Dengan Perintah SQL
/*Merubah struktur Table*/ ALTER TABLE employee ADD COLUMN departementid int(11); ALTER TABLE employee DROP COLUMN departementid; /*Menghapus table*/ DROP TABLE IF EXISTS employee;
F. WINDOW FUNCTION
Row number, rank, dense_rank
SELECT nama, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
RANK() OVER (ORDER BY salary DESC) as rank,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM
employeePartition By
SELECT name, salary, RANK() OVER (PARTITION BY departmentid ODER BY salaray DESC) AS dept_rank from employee;
G. Transaction
Starting Transaction
START TRANSACTION; UPDATE employee SET salary = salary*1.1 WHERE departmentid=1; COMMIT;
Rollback Transaction
START TRANSACTION; UPDATE employee SET salary = salary*1.1 WHERE departmentid=1; ROLLBACK;
