SQL - SELECT
Overview
SELECT retrieves data. Start simple, then add filtering and ordering.
Examples
-- top 10 by salary in dept 1
SELECT first_name, salary
FROM employees
WHERE department_id = 1
ORDER BY salary DESC
LIMIT 10;
-- top 10 by salary in dept 1
SELECT TOP (10) first_name, salary
FROM employees
WHERE department_id = 1
ORDER BY salary DESC;
-- or with OFFSET/FETCH
SELECT first_name, salary
FROM employees
WHERE department_id = 1
ORDER BY salary DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
-- top 10 by salary in dept 1
SELECT first_name, salary
FROM employees
WHERE department_id = 1
ORDER BY salary DESC
LIMIT 10;
Prerequisite
Load the Standard Test Data. The examples below assume that dataset.
Expected Output
For the query shown (top 10 by salary in department_id = 1), expected rows are:
| first_name | salary |
|---|---|
| Eli | 130000 |
| Ada | 120000 |
| Bob | 90000 |
Sample Use Cases (5)
1) Select specific columns
SELECT id, first_name, salary FROM employees;SELECT id, first_name, salary FROM employees;SELECT id, first_name, salary FROM employees;| id | first_name | salary |
|---|---|---|
| 1 | Ada | 120000 |
| 2 | Bob | 90000 |
| 3 | Chen | 80000 |
| 4 | Dee | 110000 |
| 5 | Eli | 130000 |
2) Sort by recently hired
SELECT id, first_name, hired_at
FROM employees
ORDER BY hired_at DESC NULLS LAST;SELECT id, first_name, hired_at
FROM employees
ORDER BY hired_at DESC;SELECT id, first_name, hired_at
FROM employees
ORDER BY hired_at IS NULL, hired_at DESC;| id | first_name | hired_at |
|---|---|---|
| 4 | Dee | 2022-03-01 12:00:00 |
| 2 | Bob | 2021-05-05 09:30:00 |
| 1 | Ada | 2021-01-10 10:00:00 |
| 5 | Eli | 2020-11-20 15:15:00 |
| 3 | Chen |
3) Distinct department IDs
SELECT DISTINCT department_id FROM employees;SELECT DISTINCT department_id FROM employees;SELECT DISTINCT department_id FROM employees;| department_id |
|---|
| 1 |
| 2 |
| 3 |
4) Top N by salary
SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 3;SELECT TOP (3) first_name, salary FROM employees ORDER BY salary DESC;SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 3;| first_name | salary |
|---|---|
| Eli | 130000 |
| Ada | 120000 |
| Dee | 110000 |
5) Computed column (annual bonus @ 10%)
SELECT first_name, salary, ROUND(salary * 0.10, 2) AS bonus FROM employees;SELECT first_name, salary, CAST(salary * 0.10 AS DECIMAL(10,2)) AS bonus FROM employees;SELECT first_name, salary, ROUND(salary * 0.10, 2) AS bonus FROM employees;| first_name | salary | bonus |
|---|---|---|
| Eli | 130000 | 13000.0 |
| Ada | 120000 | 12000.0 |
| Dee | 110000 | 11000.0 |
| Bob | 90000 | 9000.0 |
| Chen | 80000 | 8000.0 |
Next, add filtering operators and predicates in the WHERE lesson.