🔍 Module 5: SELECT Queries & JOINs

-- Simple SELECT statement
SELECT 
  employee_id,
  first_name,
  last_name,
  salary
FROM employees;

-- SELECT all columns
SELECT * FROM employees;

-- SELECT with aliases
SELECT 
  employee_id AS emp_id,
  first_name || ' ' || last_name AS full_name,
  salary AS monthly_salary
FROM employees;

-- SELECT with expressions
SELECT 
  employee_id,
  salary,
  salary * 12 AS annual_salary,
  salary * 1.1 AS salary_with_10_pct_raise
FROM employees;

-- Get unique departments
SELECT DISTINCT department_id
FROM employees;

-- ROWNUM - Get first N rows (Oracle)
SELECT *
FROM employees
WHERE ROWNUM <= 10;

-- FETCH NEXT - Modern approach (12c+)
SELECT *
FROM employees
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

-- OFFSET with FETCH
SELECT *
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS 
FETCH NEXT 10 ROWS ONLY;

-- Comparison operators
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE salary <> 50000;

-- IN operator
SELECT * FROM employees 
WHERE department_id IN (10, 20, 30);

-- BETWEEN operator
SELECT * FROM employees 
WHERE salary BETWEEN 50000 AND 100000;

-- LIKE operator (pattern matching)
SELECT * FROM employees 
WHERE first_name LIKE 'J%';  -- Starts with J
SELECT * FROM employees 
WHERE first_name LIKE '%n';  -- Ends with n
SELECT * FROM employees 
WHERE first_name LIKE 'J_hn';  -- Exactly 4 chars, J and hn fixed

-- IS NULL / IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE commission IS NOT NULL;

-- Logical operators (AND, OR, NOT)
SELECT * FROM employees 
WHERE salary > 50000 AND department_id = 20;

SELECT * FROM employees 
WHERE salary > 100000 OR department_id = 10;

SELECT * FROM employees 
WHERE NOT department_id = 20;

-- Sort ascending (default)
SELECT * FROM employees 
ORDER BY salary ASC;

-- Sort descending
SELECT * FROM employees 
ORDER BY salary DESC;

-- Multi-column sort
SELECT * FROM employees 
ORDER BY department_id ASC, salary DESC;

-- Sort by column position
SELECT department_id, COUNT(*), AVG(salary)
FROM employees 
GROUP BY department_id
ORDER BY 3 DESC;  -- Order by 3rd column (AVG salary)

-- Sort with NULLS FIRST/LAST
SELECT * FROM employees 
ORDER BY commission ASC NULLS LAST;

SELECT * FROM employees 
ORDER BY manager_id ASC NULLS FIRST;

-- INNER JOIN - explicit
SELECT 
  e.employee_id,
  e.first_name,
  e.salary,
  d.department_name
FROM employees e
INNER JOIN departments d 
  ON e.department_id = d.department_id;

-- INNER JOIN - implicit (old style, comma notation)
SELECT 
  e.employee_id,
  e.first_name,
  d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- INNER JOIN with multiple tables
SELECT 
  e.employee_id,
  e.first_name,
  d.department_name,
  l.city
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id;

-- LEFT OUTER JOIN
SELECT 
  e.employee_id,
  e.first_name,
  d.department_name
FROM employees e
LEFT OUTER JOIN departments d 
  ON e.department_id = d.department_id;

-- SHORT FORM: LEFT JOIN
SELECT 
  e.employee_id,
  e.first_name,
  d.department_name
FROM employees e
LEFT JOIN departments d 
  ON e.department_id = d.department_id;

-- RIGHT OUTER JOIN
SELECT 
  e.employee_id,
  e.first_name,
  d.department_name
FROM employees e
RIGHT OUTER JOIN departments d 
  ON e.department_id = d.department_id;

-- Get departments with no employees
SELECT 
  d.department_id,
  d.department_name,
  COUNT(e.employee_id) AS emp_count
FROM employees e
RIGHT JOIN departments d 
  ON e.department_id = d.department_id
WHERE e.employee_id IS NULL
GROUP BY d.department_id, d.department_name;

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

-- CROSS JOIN - all combinations
SELECT 
  e.employee_id,
  e.first_name,
  d.department_name
FROM employees e
CROSS JOIN departments d;

-- Use case: Generate all dates in range
SELECT d.report_date, s.shift_name
FROM (
  SELECT TRUNC(SYSDATE) + ROWNUM - 1 AS report_date
  FROM DUAL 
  CONNECT BY ROWNUM <= 30
) d
CROSS JOIN shifts s;

-- SELF JOIN - employee and their manager
SELECT 
  e.employee_id,
  e.first_name AS employee_name,
  m.first_name AS manager_name,
  m.employee_id AS manager_id
FROM employees e
LEFT JOIN employees m 
  ON e.manager_id = m.employee_id;

-- Complex multi-table JOIN with filtering
SELECT 
  e.employee_id,
  e.first_name,
  d.department_name,
  l.city,
  COUNT(p.project_id) AS project_count
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
LEFT JOIN projects p ON e.employee_id = p.lead_id
WHERE e.hire_date > '01-JAN-2020'
GROUP BY e.employee_id, e.first_name, d.department_name, l.city
ORDER BY project_count DESC;

-- Find employees without projects
SELECT 
  e.employee_id,
  e.first_name
FROM employees e
LEFT JOIN projects p ON e.employee_id = p.lead_id
WHERE p.project_id IS NULL;

-- Alternative using NOT IN
SELECT 
  e.employee_id,
  e.first_name
FROM employees e
WHERE e.employee_id NOT IN (
  SELECT DISTINCT lead_id FROM projects
);