1. Where
Format :
SELECT column1, column2, ..... columnN FROM table_name WHERE [search_condition] ORDER BY sort_expression
1.1. sama dengan ( = )
SELECT first_name,last_name FROM employee WHERE last_name = 'Tan';
1.2. And
SELECT first_name, last_name FROM employee WHERE first_name = 'John' AND last_name = 'Tan';
1.3. OR
SELECT first_name, last_name FROM employee WHERE first_name = 'John' OR last_name = 'Tan';
1.4. Like
SELECT first_name,last_name FROM employee WHERE last_name LIKE 'Joh%';
1.5. IN
SELECT first_name,last_name FROM employee WHERE last_name IN ('John','Mike');
1.6. LENGTH
SELECT last_name, LENGTH(last_name) name_length FROM employee
WHERE last_name LIKE '%an' AND LENGTH(last_name) BETWEEN 2 AND 6
ORDER BY name_length;
1.7. Tidak sama dengan ( <> )
SELECT first_name, last_name, address FROM employee WHERE address LIKE 'New%' AND first_name <> 'Oli';
2. Order By
Format :
SELECT column-list FROM table_name [WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
2.1 Ascending
SELECT first_name, last_name FROM employee ORDER BY first_name ASC;
2.2. Desending
SELECT first_name, last_name FROM employee ORDER BY first_name desc ;
2.3. Null
ORDER BY sort_expresssion [ASC | DESC] [NULLS FIRST | NULLS LAST]
Contoh :
SELECT num FROM demo ORDER BY num NULLS LAST;
SELECT num FROM demo ORDER BY num NULLS FIRST;
3. Group By
Format :
SELECT column-list FROM table_name WHERE [conditions ]
GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN
Contoh :
SELECT first_name || ' ' || last_name as full_name, SUM (salary) salary
FROM employee INNER JOIN employee_details USING (emp_id)
GROUP BY full_name ORDER BY salary;
SELECT first_name, COUNT (emp_id) FROM employee GROUP BY first_name;
4. Having
Format :
SELECT column1, aggregate_function (column2) FROM table1, table2
WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ]
ORDER BY column1, column2
Contoh :
SELECT emp_id, first_name, SUM (salary) FROM employee
GROUP BY first_name, emp_id HAVING SUM (salary) > 20000 order by first_name DESC;
5. Distinct
Format : Select Distinct column1 FROM table_name;
Contoh :
SELECT DISTINCT column1, column2 FROM table_name;
SELECT DISTINCT ON (column1) column_alias, column2
FROM table_name ORDER BY column1, column2 ;
6. Limit
Format :
SELECT select_list FROM table_name ORDER BY sort_expression LIMIT row_count
6.1 skip :
SELECT select_list FROM table_name LIMIT row_count OFFSET row_to_skip;
6.2. Limit
SELECT Car_id, Car_name, Body_Style FROM CAR ORDER BY Car_id LIMIT 6;
6.3. OFFSET
SELECT Car_id, Car_name, Body_Style FROM CAR ORDER BY Car_id LIMIT 5 OFFSET 2;
7. Fetch
Format :
OFFSET start { ROW | ROWS } FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY
Contoh :
SELECT car_id, car_name, car_model FROM Car ORDER BY car_name FETCH FIRST ROW ONLY;
SELECT car_id, car_name, car_model FROM Car ORDER BY car_name FETCH first 1 ROW ONLY;
