it

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 
employee

Partition 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;

Dhanny Ardiansyah

Dhanny Ardiansyah adalah senior digital marketing di PT. Toekang Digital Indonesia. Dhanny menulis tentang sosial media marketing, SEO, SEM, software enginering, server dan teknologi informasi

toekangdigital.com