Tutorial Bassic Mysql Bagian 5 - Operator dan Clausa

Publish on Dec 25, 2022 by Alfin F

Di tutorial sebelumnya kita telah belajar tentang grouping data menggunakan database Mysql : Tutorial bassic mysql bagian 4 - grouping data.

Di tutorial bassic Mysql bagian ke-5 ini kita akan belajar tentang operator dan clausa mengunakan perintah : ANY, ALL, GROUP BY dan HAVING

1) GROUP BY

Gunakan klausa GROUP BY dalam pernyataan SELECT untuk mengelompokkan baris dalam tabel berdasarkan kolom tertentu ke dalam baris ringkasan atau subgrup.

GROUP BY ditempatkan setelah klausa FROM. Jika ada klausa WHERE dalam pernyataan SELECT, GROUP BY harus ditempatkan setelah klausa WHERE.

Setelah kata kunci GROUP BY, tempatkan daftar nama kolom yang dipisahkan koma yang ingin Anda gunakan untuk mengelompokkan data.

2) Klausa HAVING

Jika Anda juga ingin memfilter data yang dikelompokkan, gunakan klausa HAVING.

Anda harus menyadari bahwa klausa WHERE tidak dapat memfilter data yang dikelompokkan.

Klausa HAVING akan muncul setelah klausa GROUP BY. Dalam klausa HAVING, Anda dapat menentukan kondisi filter yang perlu diterapkan ke data yang dikelompokkan.

3) Operator ANY

Operator ANY memungkinkan Anda melakukan perbandingan antara nilai kolom tunggal dan rentang nilai lainnya. Kisaran nilai berasal dari eksekusi subquery.

Berikut ini syntax dasar yang menggunakan operator ANY:

SELECT column_name(s) 
FROM table_name 
WHERE column_name comparison operator ANY 
  (SELECT column_name 
  FROM table_name 
  WHERE condition);

Operator ANY mengembalikan nilai boolean setelah operasi perbandingan. Nilai TRUE jika nilai subquery ANY memenuhi kondisi yang diberikan.

Dengan kata lain, kondisinya akan TRUE jika operasinya benar untuk salah satu nilai yang di kembalikan dalam query pencarian data.

Dalam sintaks ini, operator ANY harus didahului dengan nama kolom dan operator pembanding.

Operator perbandingan standar berikut yang bisa digunakan

 =, <>, !=, >, >=, <, atau <=

4) Operator ALL

Gunakan operator ALL untuk tujuan yang sama dengan operator ANY. Namun, cara kerjanya sedikit berbeda.

Operator ALL mengembalikan nilai boolean sebagai hasil dari melakukan operasi perbandingan.

Operator ALL mengembalikan TRUE hanya jika semua nilai subquery memenuhi kondisi yang diberikan.

Dengan kata lain, kondisi hanya TRUE jika operasi benar untuk semua nilai yang dikembalikan dalam query pencarian data.

Berikut ini struktur syntax dasar dari operator ALL

SELECT column_name(s) 
FROM table_name 
WHERE column_name operator ALL 
  (SELECT column_name FROM table_name WHERE condition);

Untuk mengikuti tutorial ini persiapkan database dan table-nya terlebih dahulu

A) Persiapkan Data

Buat database dengan nama "halovinaaja_db" dan table dengan nama "employees, orders, employee_orders", perhatikan kode dibawah ini

CREATE DATABASE halovinaaja_db; 
USE halovinaaja_db;

CREATE TABLE employees (
  EmployeeID int NOT NULL,
  EmployeeName varchar(150) DEFAULT NULL,
  Department varchar(150) DEFAULT NULL,
  ContactNo varchar(12) DEFAULT NULL,
  Email varchar(100) DEFAULT NULL,
  AnnualSalary int DEFAULT NULL,
  PRIMARY KEY (EmployeeID)
);

CREATE TABLE orders (
  OrderID int NOT NULL,
  Department varchar(100) DEFAULT NULL,
  OrderDate date DEFAULT NULL,
  OrderQty int DEFAULT NULL,
  OrderTotal int DEFAULT NULL,
  PRIMARY KEY (OrderID)
);

CREATE TABLE employee_orders (
 OrderID int NOT NULL,
 EmployeeID int NOT NULL,
 Status VARCHAR(150),
 HandlingCost int DEFAULT NULL,
 PRIMARY KEY (EmployeeID,OrderID),
 FOREIGN KEY (EmployeeID) REFERENCES employees(EmployeeID),
 FOREIGN KEY (OrderID) REFERENCES orders(OrderID)
);

INSERT INTO employees VALUES 
(1,'Seamus Hogan', 'Recruitment', '351478025', 'Seamus.h@luckyshrub.com',50000), 
(2,'Thomas Eriksson', 'Legal', '351475058', 'Thomas.e@luckyshrub.com',75000), 
(3,'Simon Tolo', 'Marketing', '351930582','Simon.t@luckyshrub.com',40000), 
(4,'Francesca Soffia', 'Finance', '351258569','Francesca.s@luckyshrub.com',45000), 
(5,'Emily Sierra', 'Customer Service', '351083098','Emily.s@luckyshrub.com',35000), 
(6,'Maria Carter', 'Human Resources', '351022508','Maria.c@luckyshrub.com',55000),
(7,'Rick Griffin', 'Marketing', '351478458','Rick.G@luckyshrub.com',50000);

INSERT INTO orders VALUES(1,'Lawn Care','2022-05-05',12,500),
(2,'Decking','2022-05-22',150,1450),
(3,'Compost and Stones','2022-05-27',20,780),
(4,'Trees and Shrubs','2022-06-01',15,400),
(5,'Garden Decor','2022-06-10',2,1250),
(6,'Lawn Care','2022-06-10',12,500),
(7,'Decking','2022-06-25',150,1450),
(8,'Compost and Stones','2022-05-29',20,780),
(9,'Trees and Shrubs','2022-06-10',15,400),
(10,'Garden Decor','2022-06-10',2,1250),
(11,'Lawn Care','2022-06-25',10,400), 
(12,'Decking','2022-06-25',100,1400),
(13,'Compost and Stones','2022-05-30',15,700), 
(14,'Trees and Shrubs','2022-06-15',10,300), 
(15,'Garden Decor','2022-06-11',2,1250),
(16,'Lawn Care','2022-06-10',12,500), 
(17,'Decking','2022-06-25',150,1450), 
(18,'Trees and Shrubs','2022-06-10',15,400), 
(19,'Lawn Care','2022-06-10',12,500), 
(20,'Decking','2022-06-25',150,1450), 
(21,'Decking','2022-06-25',150,1450); 

INSERT INTO employee_orders 
VALUES(1,3,"In Progress",200), 
(1,5,"Not Recieved",300), 
(1,4,"Not Recieved",250), 
(2,3,"Completed",200), 
(2,5,"Completed",300), 
(2,4,"In Progress",250), 
(3,3,"In Progress",200), 
(3,5,"Not Recieved",300), 
(3,4,"Not Recieved",250), 
(4,3,"Completed",200), 
(4,5,"In Progress",300), 
(4,4,"In Progress",250), 
(5,3,"Completed",200), 
(5,5,"In Progress",300), 
(5,4,"Not Recieved",250), 
(11,3,"Completed",200), 
(11,5,"Completed",300), 
(11,4,"Not Recieved",250), 
(14,3,"Completed",200), 
(14,5,"Not Recieved",300), 
(14,4,"Not Recieved",250);

B) Studi Kasus

Gunakan operator ANY untuk mengidentifikasi semua karyawan dengan status orders 'Completed'.

Gunakan operator ALL untuk mengidentifikasi ID karyawan yang memperoleh biaya penanganan "lebih dari 20% dari nilai pesanan" dari semua pesanan yang telah mereka tangani.

Gunakan klausa GROUP BY untuk meringkas rekaman duplikat dengan nilai kolom yang sama menjadi satu record dengan mengelompokkannya berdasarkan kolom tersebut.

Gunakan klausa HAVING untuk memfilter data yang dikelompokkan dalam subkueri yang Anda tulis di tugas 3 untuk memfilter 20% nilai OrderTotal agar hanya mengambil nilai yang lebih dari $100.