30 most common SQL queries with examples

Here are the 30 common SQL queries with examples. These cover a wide range of database operations from basic to more advanced.

Basic SELECT Queries

1. Select all columns from a table

SELECT * FROM employees;

2. Select specific columns

SELECT first_name, last_name, salary FROM employees;

3. Select with WHERE clause

SELECT * FROM employees WHERE department_id = 50;

4. Select with ORDER BY

SELECT * FROM employees ORDER BY salary DESC;

5. Select with LIMIT/TOP

-- MySQL/PostgreSQL
SELECT * FROM employees LIMIT 10;
-- SQL Server
SELECT TOP 10 * FROM employees;

Filtering and Conditions

6. WHERE with multiple conditions

SELECT * FROM employees 
WHERE salary > 50000 AND department_id = 20;

7. WHERE with IN operator

SELECT * FROM employees 
WHERE department_id IN (10, 20, 30);

8. WHERE with LIKE pattern matching

SELECT * FROM employees 
WHERE last_name LIKE 'S%';

9. WHERE with NULL values

SELECT * FROM employees 
WHERE manager_id IS NULL;

10. WHERE with BETWEEN range

SELECT * FROM employees 
WHERE salary BETWEEN 40000 AND 60000;

Aggregations and Grouping

11. COUNT function

SELECT COUNT(*) FROM employees;

12. SUM, AVG, MIN, MAX functions

SELECT 
    department_id,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

13. GROUP BY with HAVING

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

Joins

14. INNER JOIN

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

15. LEFT JOIN

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

16. RIGHT JOIN

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

17. FULL OUTER JOIN

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

18. Self JOIN

SELECT e.last_name AS employee, m.last_name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

Subqueries

19. Subquery in WHERE clause

SELECT * FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments
    WHERE location_id = 1700
);

20. Subquery with EXISTS

SELECT * FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.department_id
    AND e.salary > 10000
);

Data Modification

21. INSERT statement

INSERT INTO employees 
(employee_id, first_name, last_name, email, hire_date, job_id)
VALUES
(207, 'John', 'Smith', 'jsmith@example.com', '2023-01-15', 'IT_PROG');

22. UPDATE statement

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 90;

23. DELETE statement

DELETE FROM employees
WHERE hire_date < '2010-01-01';

Advanced Queries

24. Common Table Expressions (CTE)

WITH high_salary_employees AS (
    SELECT department_id, COUNT(*) AS count
    FROM employees
    WHERE salary > 50000
    GROUP BY department_id
)
SELECT d.department_name, h.count
FROM departments d
JOIN high_salary_employees h ON d.department_id = h.department_id;

25. UNION operator

SELECT first_name, last_name, 'Active' AS status
FROM current_employees
UNION
SELECT first_name, last_name, 'Inactive' AS status
FROM former_employees;

26. CASE statement

SELECT 
    employee_id,
    first_name,
    last_name,
    CASE 
        WHEN salary < 30000 THEN 'Low'
        WHEN salary BETWEEN 30000 AND 60000 THEN 'Medium'
        ELSE 'High'
    END AS salary_category
FROM employees;

27. Window functions

SELECT 
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

28. CREATE and ALTER TABLE

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(15,2),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

ALTER TABLE projects
ADD COLUMN status VARCHAR(20) DEFAULT 'Planning';

29. CREATE INDEX

CREATE INDEX idx_emp_dept ON employees(department_id);

30. CREATE VIEW

CREATE VIEW employee_details AS
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    j.job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id;

These queries cover the most common operations you’ll need when working with SQL databases. Each example can be modified to suit your specific database schema and requirements.

Thanks for reading!