SQL Basics and Advanced Examples
This document provides a summary of SQL concepts, rules, and practical examples
that we've covered over the last two days. It includes basic SQL queries,
aggregation functions, joins, subqueries, and much more.
We also provide examples and explanations for different SQL scenarios and queries.
Key Topics Covered:
1. Basic SQL Queries
2. Aggregate Functions
3. Joins
4. Subqueries
5. Sorting and Filtering Data
6. Advanced SQL Techniques
1. Basic SQL Queries
Examples:
- Select all data from a table:
SELECT * FROM employees;
- Select specific columns:
SELECT employee_id, name FROM employees;
- Use WHERE clause for filtering:
SELECT * FROM employees WHERE salary > 50000;
2. Aggregate Functions
Examples:
- Calculate the sum of salaries in each department:
SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;
- Find the average salary:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
- Count the number of employees in each department:
SELECT department_id, COUNT(employee_id) FROM employees GROUP BY department_id;
3. Joins
Examples:
- Inner Join between two tables (employees and departments):
SELECT employee_id, name, department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
- Left Join:
SELECT employee_id, name, department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
4. Subqueries
Examples:
- Subquery to find employees earning above average salary:
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
- Subquery to find employees with the highest salary in each department:
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees
WHERE department_id = employees.department_id);
5. Sorting and Filtering Data
Examples:
- Order employees by salary in descending order:
SELECT * FROM employees ORDER BY salary DESC;
- Use LIMIT to get top N results:
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;
- Use OFFSET to skip a number of results:
SELECT * FROM employees ORDER BY salary DESC OFFSET 2 LIMIT 3;
6. Advanced SQL Techniques
Examples:
- Using HAVING clause with GROUP BY:
SELECT department_id, COUNT(employee_id) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 50;
- Using complex subqueries to filter data:
SELECT * FROM employees WHERE salary > (SELECT MAX(salary) FROM employees
WHERE department_id = employees.department_id);
