Perangkat Lunak
Pengembangan Web
Pertemuan 6 – Database II
Program Diploma IPB - Aditya Wicaksono, SKomp 1
Klausa GROUP BY
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 2
Klausa HAVING
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
Program Diploma IPB - Aditya Wicaksono, SKomp 3
AGGREGATE FUNCTION
Nama Fungsi Deskripsi
AVG() Mengembalikan nilai rata-rata
COUNT() Mengembalikan jumlah baris
MAX() Mengembalikan nilai terbesar
MIN() Mengembalikan nilai terkecil
SUM() Mengembalikan jumlah
Program Diploma IPB - Aditya Wicaksono, SKomp 4
AVG()
SELECT AVG(column_name) FROM table_name
Program Diploma IPB - Aditya Wicaksono, SKomp 5
COUNT()
SELECT COUNT(column_name) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 6
COUNT(DISTINCT)
SELECT COUNT(DISTINCT column_name) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 7
MAX()
SELECT MAX(column_name) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 8
MIN()
SELECT MIN(column_name) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 9
SUM()
SELECT SUM(column_name) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 10
SCALAR FUNCTION
Nama Fungsi Deskripsi
UCASE() Mengkonversi field ke upper case(huruf besar)
LCASE() Mengkonversi field ke lower case (huruf kecil)
MID() Ekstraksi karakter dari text field
LEN() Mengembalikan panjang dari text field
ROUND() Pembulatan angka kepada jumlah desimal yang ditentukan
NOW() Mengembalkan tanggal dan waktu sistem saat ini
FORMAT() Format bagaimana field akan ditampilkan
Program Diploma IPB - Aditya Wicaksono, SKomp 11
UCASE()
SELECT UCASE(column_name) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 12
LCASE()
SELECT LCASE(column_name) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 13
MID()
SELECT MID(column_name,start[,length]) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 14
LEN()
SELECT LEN(column_name) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 15
ROUND()
SELECT ROUND(column_name,decimals) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 16
NOW()
SELECT NOW() FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 17
FORMAT()
SELECT FORMAT(column_name,format) FROM table_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 18
Klausa LIMIT
SELECT column_name(s)
FROM table_name
LIMIT number;
Program Diploma IPB - Aditya Wicaksono, SKomp 19
JOIN
FULL JOIN RIGHT JOIN LEFT JOIN
INNER JOIN
Program Diploma IPB - Aditya Wicaksono, SKomp 20
JOIN
Program Diploma IPB - Aditya Wicaksono, SKomp 21
JOIN
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 22
INNER JOIN = JOIN
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 23
LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 24
LEFT OUTER JOIN = LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 25
RIGHT JOIN
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 26
RIGHT OUTER JOIN = RIGHT JOIN
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 27
FULL OUTER JOIN
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
Program Diploma IPB - Aditya Wicaksono, SKomp 28
UNION
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
Program Diploma IPB - Aditya Wicaksono, SKomp 29
INSERT INTO
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
Program Diploma IPB - Aditya Wicaksono, SKomp 30
UPDATE
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
Program Diploma IPB - Aditya Wicaksono, SKomp 31
DELETE
DELETE FROM table_name
WHERE some_column=some_value;
Program Diploma IPB - Aditya Wicaksono, SKomp 32
Terima Kasih
Program Diploma IPB - Aditya Wicaksono, SKomp 33