Skip to content

README.md

Latest commit

 

History

History
271 lines (241 loc) · 6.56 KB

README.md

File metadata and controls

271 lines (241 loc) · 6.56 KB

SQL Cheat Sheet with Examples (For Business Analysts)

1. Basic SQL Syntax

  • SELECT: Retrieve data from a table. 
    SELECT name, age FROM employees WHERE age > 30;
  • DISTINCT: Remove duplicates. 
    SELECT DISTINCT department FROM employees;
  • LIMIT/OFFSET: Limit the number of rows returned. 
    SELECT * FROM employees LIMIT 5 OFFSET 10;

2. Filtering Data

  • WHERE Clause: Filter rows. 
    SELECT * FROM employees WHERE salary > 50000 AND department = 'HR';
  • Operators:
    • Comparison: =, !=, <, >, <=, >= 
      SELECT * FROM employees WHERE age >= 30;
    • Logical: AND, OR, NOT 
      SELECT * FROM employees WHERE (age > 30 AND department = 'IT') OR salary > 60000;
    • Pattern Matching: LIKE, % (wildcard) 
      SELECT * FROM employees WHERE name LIKE 'A%'; -- Names starting with 'A'
    • NULL: IS NULL, IS NOT NULL 
      SELECT * FROM employees WHERE manager_id IS NULL;

3. Aggregate Functions

  • Functions applied to groups of data:
    • COUNT(): Number of rows. 
      SELECT COUNT(*) AS total_employees FROM employees;
    • SUM(): Sum of values. 
      SELECT SUM(salary) AS total_salary FROM employees;
    • AVG(): Average value. 
      SELECT AVG(salary) AS average_salary FROM employees WHERE department = 'IT';
    • MIN(), MAX(): Minimum and maximum values. 
      SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary FROM employees;

4. Grouping and Aggregation

  • GROUP BY: Group rows sharing a property. 
    SELECT department, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department;
  • HAVING: Filter groups (used with GROUP BY). 
    SELECT department, SUM(salary) AS total_salary 
FROM employees 
GROUP BY department
HAVING SUM(salary) > 200000;

5. Sorting Data

  • ORDER BY: Sort results. 
    SELECT * FROM employees ORDER BY salary DESC, age ASC;

6. Joining Tables

  • INNER JOIN: Matching rows in both tables. 
    SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
  • LEFT JOIN: All rows from the left, matching from the right. 
    SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;
  • RIGHT JOIN: All rows from the right, matching from the left.
  • FULL OUTER JOIN: All rows from both tables.

7. Subqueries

  • Subquery in SELECT: 
    SELECT name, (SELECT MAX(salary) FROM employees) AS highest_salary FROM employees;
  • Subquery in WHERE: 
    SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'NYC');

8. Common Table Expressions (CTEs)

  • Temporary result set for reuse. 
    WITH high_salary_employees AS (
  SELECT * FROM employees WHERE salary > 80000
)
SELECT * FROM high_salary_employees WHERE department = 'IT';

9. Window Functions

  • Perform calculations across a set of rows related to the current row. 
    SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
  • Common window functions:
    • ROW_NUMBER(), RANK(), DENSE_RANK()
    • SUM(), AVG(), COUNT() (used with OVER)

10. Modifying Data

  • INSERT: Add new rows. 
    INSERT INTO employees (name, age, department_id, salary)
VALUES ('John Doe', 35, 3, 75000);
  • UPDATE: Modify existing rows. 
    UPDATE employees 
SET salary = salary * 1.10 
WHERE department = 'HR';
  • DELETE: Remove rows. 
    DELETE FROM employees WHERE age < 25;

11. Advanced SQL

  • Case Statements: Conditional logic. 
    SELECT name, 
       CASE 
         WHEN salary > 80000 THEN 'High'
         WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
         ELSE 'Low'
       END AS salary_category
FROM employees;
  • UNION: Combine results of two queries. 
    SELECT name FROM employees WHERE department = 'HR'
UNION
SELECT name FROM employees WHERE salary > 60000;
  • EXISTS: Check for existence of rows in a subquery. 
    SELECT * 
FROM employees 
WHERE EXISTS (SELECT 1 FROM departments WHERE departments.department_id = employees.department_id AND location = 'NYC');
  • Recursive CTEs: Example for hierarchical data. 
    WITH RECURSIVE EmployeeHierarchy AS (
  SELECT employee_id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.name, e.manager_id
  FROM employees e
  INNER JOIN EmployeeHierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

12. Indexes and Performance

  • Use indexes to speed up queries. 
    CREATE INDEX idx_salary ON employees(salary);
  • Avoid using SELECT * in production.
  • Analyze query plans for optimization: EXPLAIN or EXPLAIN ANALYZE.

13. Practice Patterns for LeetCode SQL

  • Self-Joins: Compare rows in the same table. 
    SELECT a.id, b.id
FROM employees a, employees b
WHERE a.salary > b.salary AND a.department = b.department;
  • Ranking Problems: Use ROW_NUMBER() or RANK(). 
    SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
  • Top-N Queries: Use LIMIT with ORDER BY. 
    SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;
  • Gaps and Islands: Use window functions and GROUP BY. 
    SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

14. Business Analyst-Specific SQL

  • Revenue analysis: 
    SELECT product_category, SUM(revenue) AS total_revenue
FROM sales_data
GROUP BY product_category
ORDER BY total_revenue DESC;
  • Customer segmentation: 
    SELECT customer_id, COUNT(*) AS purchase_count
FROM purchases
GROUP BY customer_id
HAVING COUNT(*) > 5;
  • Monthly trend analysis: 
    SELECT DATE_TRUNC('month', sale_date) AS month, SUM(revenue) AS total_revenue
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month;

Tips for LeetCode SQL Challenges

  1. Read the problem carefully, focusing on constraints.
  2. Use CTEs for clarity.
  3. Test subqueries separately for debugging.
  4. Always check edge cases (e.g., NULL values).