SQL Cheatsheet

Basic Level

1. SELECT Statement

-- Returns columns 'column1' and 'column2' from the 'table_name'
SELECT column1, column2 FROM table_name;

2. WHERE Clause

-- Returns all columns from the 'employees' table where 'department' is 'IT'
SELECT * FROM employees WHERE department = 'IT';

3. ORDER BY Clause

-- Returns 'product_name' and 'price' from the 'products' table, ordered by 'price' in descending order
SELECT product_name, price FROM products ORDER BY price DESC;

4. LIMIT and OFFSET

-- Returns 10 rows from the 'orders' table, starting from the 21st row
SELECT * FROM orders LIMIT 10 OFFSET 20;

5. COUNT() Function

-- Returns the count of rows from the 'customers' table where 'country' is 'USA'
SELECT COUNT(*) FROM customers WHERE country = 'USA';

6. GROUP BY and HAVING

-- Returns the average salary per department from the 'employees' table, filtering departments with an average salary greater than 50000
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

7. INNER JOIN

-- Returns 'order_id' from 'orders' and 'customer_name' from 'customers' where 'customer_id' matches
SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;

8. INSERT Statement

-- Inserts a new employee with first name 'John', last name 'Doe', and salary 60000 into the 'employees' table
INSERT INTO employees (first_name, last_name, salary) VALUES ('John', 'Doe', 60000);

9. UPDATE Statement

-- Updates the 'stock_quantity' of the product with 'product_id' 101 in the 'products' table, subtracting 5
UPDATE products SET stock_quantity = stock_quantity - 5 WHERE product_id = 101;

10. DELETE Statement

-- Deletes the row from the 'suppliers' table where 'supplier_id' is 20
DELETE FROM suppliers WHERE supplier_id = 20;

These comments provide an overview of the expected outcomes for each SQL query.

Intermediate SQL Cheatsheet

11. DISTINCT Keyword

-- Returns unique values in the 'category' column from the 'products' table
SELECT DISTINCT category FROM products;

12. Subqueries

-- Returns the order_id and total amount from the 'orders' table where the total amount is greater than the average total amount
SELECT order_id, total_amount FROM orders WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

13. JOIN with WHERE Clause

-- Returns 'order_id' and 'customer_name' for orders made by customers from the 'USA'
SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id WHERE customers.country = 'USA';

14. CASE Statement

-- Returns a column indicating whether the product is 'In Stock' or 'Out of Stock' based on the 'stock_quantity'
SELECT product_name, stock_quantity, CASE WHEN stock_quantity > 0 THEN 'In Stock' ELSE 'Out of Stock' END AS stock_status FROM products;

15. Aggregate Functions with GROUP BY

-- Returns the number of orders and the average total amount per customer
SELECT customer_id, COUNT(order_id) AS order_count, AVG(total_amount) AS avg_total_amount FROM orders GROUP BY customer_id;

16. Self JOIN

-- Returns employees and their managers from the 'employees' table by performing a self JOIN
SELECT e.employee_name, m.employee_name AS manager_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id;

17. LIKE Operator

-- Returns product names that start with 'A'
SELECT product_name FROM products WHERE product_name LIKE 'A%';

18. BETWEEN Operator

-- Returns orders with a total amount between $100 and $500
SELECT * FROM orders WHERE total_amount BETWEEN 100 AND 500;

19. INDEX Creation

-- Creates an index on the 'customer_id' column of the 'orders' table
CREATE INDEX idx_customer_id ON orders(customer_id);

20. Triggers

-- Creates a trigger that automatically updates the 'last_updated' timestamp when a product is updated
CREATE TRIGGER update_last_updated BEFORE UPDATE ON products FOR EACH ROW SET NEW.last_updated = NOW();

These comments provide insights into the expected outcomes for each Intermediate level SQL query.

Advanced SQL Cheatsheet

21. Window Functions

-- Returns the rank and total sales for each product, ordered by total sales
SELECT product_name, SUM(sales) OVER (ORDER BY product_name) AS total_sales, RANK() OVER (ORDER BY total_sales DESC) AS sales_rank FROM sales_data;

22. Common Table Expressions (CTE)

-- Uses a CTE to calculate the average salary and then selects employees with a salary above the average
WITH AvgSalary AS (SELECT AVG(salary) AS avg_salary FROM employees) SELECT * FROM employees WHERE salary > (SELECT avg_salary FROM AvgSalary);

23. Pivot Table

-- Transforms rows into columns to display the total sales for each product category
SELECT * FROM sales_data PIVOT (SUM(sales) FOR product_category IN ('Electronics', 'Clothing', 'Furniture'));

24. MERGE Statement

-- Updates or inserts rows in the 'sales' table based on matching conditions
MERGE INTO sales USING new_sales ON sales.product_id = new_sales.product_id WHEN MATCHED THEN UPDATE SET sales_quantity = sales_quantity + new_sales.sales_quantity WHEN NOT MATCHED THEN INSERT (product_id, sales_quantity) VALUES (new_sales.product_id, new_sales.sales_quantity);

25. Recursive Queries

-- Retrieves the hierarchical structure of employees and their managers
WITH RECURSIVE EmployeeHierarchy AS (SELECT employee_id, employee_name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.employee_name, e.manager_id FROM employees e JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id) SELECT * FROM EmployeeHierarchy;

28. JSON Functions

-- Extracts values from a JSON column and returns the product names and prices
SELECT product_data->>'name' AS product_name, product_data->>'price' AS product_price FROM products WHERE product_data->>'category' = 'Electronics';

27. Temporal Tables

-- Retrieves historical data for a specific product using a temporal table
SELECT * FROM products FOR SYSTEM_TIME AS OF '2022-01-01 12:00:00' WHERE product_id = 101;

28. Materialized Views

-- Creates a materialized view to store aggregated sales data for faster query performance
CREATE MATERIALIZED VIEW mv_sales_summary AS SELECT product_id, SUM(sales_quantity) AS total_sales FROM sales GROUP BY product_id;

29. Advanced JOIN Techniques

-- Retrieves customers who have made purchases in the last month, including their latest purchase details
SELECT customers.customer_id, customers.customer_name, MAX(orders.order_date) AS last_purchase_date FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id AND orders.order_date >= CURRENT_DATE - INTERVAL '1' MONTH GROUP BY customers.customer_id;

30. Database Security

-- Grants specific privileges to a user on the 'employees' table
GRANT SELECT, INSERT, UPDATE ON employees TO user1;

These comments provide insights into the expected outcomes for each Advanced level SQL query.

Expert SQL Cheatsheet

31. Data Compression

-- Implements table compression to optimize storage space for the 'sales' table
ALTER TABLE sales COMPRESS FOR OLTP;

32. Query Optimization

-- Uses EXPLAIN ANALYZE to analyze and optimize the execution plan of a complex query
EXPLAIN ANALYZE SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.order_date >= '2023-01-01';

33. Database Sharding

-- Implements horizontal sharding to distribute data across multiple servers based on customer regions
CREATE TABLE orders_east AS SELECT * FROM orders WHERE customer_region = 'East';
CREATE TABLE orders_west AS SELECT * FROM orders WHERE customer_region = 'West';

34. Advanced Indexing

-- Creates a function-based index to index the result of a function for faster query performance
CREATE INDEX idx_upper_product_name ON products (UPPER(product_name));

35. Row-Level Security

-- Implements row-level security to restrict access to sensitive employee data based on user roles
CREATE POLICY hr_policy FOR SELECT USING (role = 'HR');
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

36. Query Rewriting

-- Uses a SQL query rewrite to replace a subquery with a join for improved performance
CREATE OR REPLACE RULE replace_subquery AS ON SELECT TO orders WHERE EXISTS (SELECT 1 FROM customers WHERE customers.customer_id = orders.customer_id AND customers.country = 'USA') DO INSTEAD SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE customers.country = 'USA';

37. Database Auditing

-- Enables auditing to track changes to sensitive data in the 'employees' table
ALTER TABLE employees ENABLE AUDIT;

38. Full-Text Search

-- Implements full-text search to find products containing specific keywords
SELECT * FROM products WHERE to_tsvector('english', product_name) @@ to_tsquery('english', 'electronics & sale');

39. Database Replication

-- Sets up replication for disaster recovery and load balancing purposes
CREATE PUBLICATION pub_sales FOR TABLE sales;
CREATE SUBSCRIPTION sub_sales CONNECTION 'host=replica1' PUBLICATION pub_sales;

40. Database Partitioning

-- Partitions the 'orders' table by date range for improved query performance
CREATE TABLE orders_part PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

These comments provide insights into the expected outcomes for each Expert level SQL query.