Modul 3 - MySQL (Sintaks)

1.    Mengambil data dengan SELECT

SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy

4.      Memberikan alias hasil query pada SELECT

mysql> SELECT CONCAT(first_name," ",last_name) AS name,

description

-> FROM employee

-> WHERE description = "Detective";

5.      Query data bertipe teks dengan pattern matching

mysql> SELECT CONCAT(first_name," ", last_name)

-> FROM employee

-> WHERE first_name LIKE 'J____';

mysql> SELECT CONCAT(first_name," ", last_name)

-> FROM employee

-> WHERE first_name NOT LIKE '%n';

6.      Query data unik menggunakan DISTINCT

mysql> SELECT DISTINCT description FROM employee;

7.      Membatasi hasil query dengan LIMIT

mysql> SELECT *

-> FROM employee

-> LIMIT 5;

mysql> SELECT *

-> FROM employee

-> LIMIT 2,3;

mysql> SELECT * FROM employee LIMIT 0,3;

8.      Mengelompokkan hasil query menggunakan GROUP BY

mysql> SELECT * FROM employee GROUP BY city;

9.      Mendapatkan jumlah anggota setiap kelompok menggunakan COUNT()

mysql> SELECT city, COUNT(*) FROM employee GROUP BY city;

mysql> SELECT city, COUNT(*)

-> FROM employee

-> WHERE description = 'Tester'

-> GROUP BY city;

10.  Parameter kondisional dengan HAVING

mysql> SELECT first_name, last_name, salary

-> FROM employee

-> HAVING salary > 3000;

mysql> SELECT city, COUNT(*), salary FROM employee WHERE

salary > 3000 GROUP BY city;

mysql> SELECT city, COUNT(*), salary FROM employee GROUP BY

city HAVING salary > 3000;

11.  Mengurutkan hasil query menggunakan ORDER BY

mysql> SELECT CONCAT(first_name," ",last_name) AS name

-> FROM employee

-> ORDER BY name;

mysql> SELECT CONCAT(first_name," ",last_name) AS name

-> FROM employee

-> ORDER BY name DESC;

12.  Mengurutkan hasil query berdasarkan lebih dari satu kolom

mysql> SELECT first_name, last_name, city FROM employee ORDER

BY first_name, city;

mysql> SELECT first_name, last_name, city FROM employee ORDER

BY first_name, city DESC;

13.  Kombinasi ORDER BY dengan LIMIT

mysql> SELECT first_name, last_name, city FROM employee ORDER

BY city LIMIT 4;

14.  Operator BETWEEN

mysql> SELECT first_name, last_name, salary FROM employee

-> WHERE salary BETWEEN 1000 and 3000;

mysql> SELECT first_name, last_name, salary FROM employee WHERE

salary BETWEEN 1233.78 and 1235.56;

mysql> SELECT first_name, last_name, salary FROM employee WHERE

salary BETWEEN 1233.78 and 1235.57;

mysql> SELECT first_name,last_name FROM employee WHERE

first_name BETWEEN "Elvis" and "James";

mysql> SELECT first_name,last_name FROM employee WHERE

first_name NOT BETWEEN "Elvis" and "James";

15.  SUB QUERY

mysql> SELECT first_name, last_name, salary

-> FROM employee

-> WHERE salary = (SELECT MAX(salary) FROM employee);

16.  Sub query dengan ALL

mysql> SELECT first_name, last_name, salary

-> FROM employee

-> WHERE salary > ALL(SELECT salary FROM employee

WHERE salary <2000);

17.  Sub query dengan ANY

mysql> SELECT first_name, last_name, salary FROM employee WHERE

salary > ANY(SELECT salary FROM employee WHERE salary

<2000);

mysql> SELECT first_name, last_name, salary FROM employee WHERE

salary > SOME(SELECT salary FROM employee WHERE salary

<2000);

18.  Sub query dengan EXISTS

mysql> SELECT first_name, last_name, city FROM employee WHERE

EXISTS (SELECT * FROM employee WHERE city = "Toronto");

mysql> SELECT first_name, last_name, city FROM employee WHERE

NOT EXISTS (SELECT * FROM employee WHERE city =

"Toronto");

19.  Sub query dengan IN

mysql> CREATE TABLE job (

-> id INT AUTO_INCREMENT PRIMARY KEY,

-> title VARCHAR(20));

mysql> INSERT into job VALUES

-> (1, 'Tester'),

-> (2, 'Accountant') ,

-> (3, 'Programmer'),

-> (4, 'Professor');

mysql> SELECT first_name, last_name, description

-> FROM employee

-> WHERE description IN

-> (SELECT title FROM job

-> );

20.  Table Join

mysql> CREATE TABLE employee_join AS (SELECT * FROM

employee);

mysql> ALTER TABLE employee_join ADD job_id INT;

mysql> UPDATE employee_join,jobs SET employee_join.job_id =

jobs.job_id WHERE employee_join.description = jobs.title;

mysql> ALTER TABLE employee_join DROP description;

21.  Cross Join

mysql> SELECT *

-> FROM employee_join, jobs;

22.  Equi-Join atau Inner Join

mysql> SELECT * FROM

-> employee_join, jobs

-> WHERE employee_join.job_id = jobs.job_id;

mysql> SELECT * FROM employee_join, jobs WHERE

employee_join.job_id = jobs.job_id;

mysql> SELECT * FROM employee_join t1 INNER JOIN jobs t2 ON

t1.job_id = t2.job_id;

mysql> SELECT * FROM employee_join INNER JOIN jobs ON

employee_join.job_id = jobs.job_id;

mysql> SELECT * FROM employee_join INNER JOIN jobs USING

(job_id);

23.  Natural Join

mysql> SELECT * FROM employee_join NATURAL JOIN jobs;

24.  Left Join dan Right Join

mysql> INSERT INTO jobs VALUES (7, 'Developer');

mysql> SELECT * FROM jobs t1 LEFT JOIN employee_join t2 ON

t1.job_id = t2.job_id;

mysql> SELECT * FROM employee_join t1 RIGHT JOIN jobs t2 ON

t1.job_id = t2.job_id;

25.  Update menggunakan Join Table

UPDATE <table1>, <table2>

SET <column_name> = 'new_value'

WHERE <conditions>

mysql> SELECT first_name, last_name, title FROM employee_join

INNER JOIN jobs USING (job_id);

mysql> UPDATE employee_join, jobs

-> SET last_name = "Bono"

-> WHERE employee_join.job_id = jobs.job_id AND jobs.title =

'Spy';

26.  Delete menggunakan join table

mysql> DELETE employee_join FROM employee_join, jobs WHERE

employee_join.job_id = jobs.job_id AND jobs.title = 'Spy';

mysql> SELECT * FROM employee_join INNER JOIN jobs USING

(job_id);