SQL 101 | Summary of Most Used SQL Syntax for Data Analysis

This comprehensive summary sheet / cheat sheet covers from basic SELECT statements to advanced joins, subqueries, and query optimization.

Improve your readability in:

This comprehensive summary sheet / cheat sheet covers from basic `SELECT` statements to advanced joins, subqueries, and query optimization.

blog.ardenov.com

Understanding SQL and Databases Key Concepts CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(50) ); INSERT INTO employees (id, name, department) VALUES (1, ‘Alice’, ‘HR’); SELECT * FROM employees; Setting Up Your Environment Creating a Database CREATE DATABASE company_db; Retrieving Data with SELECT Statements SELECT Basics SELECT name, department FROM employees; Filtering Data with WHERE Clause SELECT * FROM employees WHERE department = ‘HR’; Sorting Results with ORDER BY SELECT * FROM employees ORDER BY name ASC; Limiting Results with LIMIT SELECT * FROM employees LIMIT 5; Aggregating Data with GROUP BY SELECT department, COUNT(*) FROM employees GROUP BY department; Joining Tables with INNER JOIN SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department = d.id; Advanced Filtering Complex WHERE Conditions SELECT * FROM employees WHERE department = ‘HR’ AND name LIKE ‘A%’; Pattern Matching with LIKE SELECT * FROM employees WHERE name LIKE ‘%son’; Range Filtering with BETWEEN SELECT * FROM employees WHERE id BETWEEN 1 AND 10; Null Checking SELECT * FROM employees WHERE department IS NULL; Advanced Joins and Subqueries LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department = d.id; Self-Joins SELECT e1.name, e2.name AS manager_name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id; Cross Joins SELECT e.name, p.project_name FROM employees e CROSS JOIN projects p; Subqueries SELECT name FROM employees WHERE department = (SELECT id FROM departments WHERE department_name = ‘IT’); Correlated Subqueries SELECT e1.name FROM employees e1 WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department = e2.department); Using EXISTS and IN with Subqueries SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE department_name = ‘IT’); Set Operations and Common Table Expressions (CTEs) UNION, UNION ALL SELECT name FROM employees WHERE department = ‘HR’ UNION SELECT name FROM employees WHERE department = ‘IT’; Using WITH to Define CTEs WITH dept_count AS ( SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department ) SELECT * FROM dept_count WHERE num_employees > 10; Window Functions and Advanced Aggregate Functions Using OVER and PARTITION BY SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank FROM employees; Ranking Functions SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank FROM employees; HAVING Clause for Filtering Groups SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; Full-Text Search and Data Manipulation Full-Text Search Queries SELECT * FROM documents WHERE MATCH(text) AGAINST(‘search query’); Date and Time Functions SELECT name, DATE_FORMAT(hire_date, ‘%Y-%m-%d’) FROM employees; Working with Views Creating and Managing Views CREATE VIEW hr_employees AS SELECT name, department FROM employees WHERE department = ‘HR’; Advanced Query Optimization Understanding Query Execution Plans EXPLAIN SELECT * FROM employees WHERE department = ‘HR’; Using Indexes for Performance CREATE INDEX idx_department ON employees(department);

Originally published at https://blog.ardenov.com.