SQL Basics Review

1. Key SQL Clauses

• SELECT: Specifies the columns to retrieve.

  SELECT column1, column2 FROM table_name;

• FROM: Specifies the table(s) to query data from.

  SELECT * FROM employees;

• WHERE: Filters rows based on specified conditions.

  SELECT * FROM orders WHERE order_date > '2023-01-01';

• GROUP BY: Groups rows with the same values in specified columns, often used with aggregate functions.

  SELECT department, COUNT(*) FROM employees GROUP BY department;

• HAVING: Filters groups based on aggregate function conditions (works after GROUP BY).

  SELECT department, COUNT(*) FROM employees 
  GROUP BY department 
  HAVING COUNT(*) > 10;

• ORDER BY: Sorts query results by one or more columns, in ascending (ASC) or descending (DESC) order.

  SELECT * FROM employees ORDER BY salary DESC;

---

2. Complex JOIN Operations

• INNER JOIN: Retrieves matching rows from both tables.

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

• LEFT JOIN: Retrieves all rows from the left table and matching rows from the right table.

  SELECT customers.name, orders.order_id 
  FROM customers 
  LEFT JOIN orders 
  ON customers.customer_id = orders.customer_id;

• RIGHT JOIN: Retrieves all rows from the right table and matching rows from the left table.

  SELECT customers.name, orders.order_id 
  FROM customers 
  RIGHT JOIN orders 
  ON customers.customer_id = orders.customer_id;

• FULL OUTER JOIN: Retrieves all rows from both tables, with NULL where there's no match.

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

• Self JOIN: Joins a table to itself to analyze hierarchical or relational data.

  SELECT e1.name AS Employee, e2.name AS Manager 
  FROM employees e1 
  INNER JOIN employees e2 
  ON e1.manager_id = e2.employee_id;

---

3. Subqueries and Derived Tables

• Subqueries: Queries nested inside another query.

  • Single-row subquery (used in WHERE or SELECT):

  SELECT name FROM employees 
  WHERE salary > (SELECT AVG(salary) FROM employees);
  

  • Multi-row subquery (used with IN, ANY, or ALL):

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

• Derived Tables: Temporary tables created using subqueries within the FROM clause.
  
  

  SELECT d.department_name, temp.avg_salary 
  FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS temp 
  INNER JOIN departments d 
  ON temp.department_id = d.department_id;

---