SQL Beginner Notes

-- Simple SQL query asking the database for all employee records
SELECT *
FROM employees;

-- Output: Returns all 8 rows and all columns from the employees table

-- Query calculating total payroll across departments in milliseconds
SELECT department, SUM(salary) AS total_payroll
FROM employees
GROUP BY department;

-- Output: Instant summary of payroll costs grouped by department

-- 1. Create (Insert a new row)
INSERT INTO employees (id, name, department, salary, city)
VALUES (9, 'Ian Malcolm', 'IT', 74000, 'Austin');

-- 2. Read (Retrieve rows)
SELECT name, salary FROM employees WHERE department = 'IT';

-- 3. Update (Modify existing row)
UPDATE employees SET salary = 78000 WHERE id = 1;

-- 4. Delete (Remove a row)
DELETE FROM employees WHERE id = 9;

-- Chapter 1 Mastery Query: Reading structure step by step
SELECT name, department, salary   -- 1. Which columns to retrieve
FROM employees                    -- 2. Which table to inspect
WHERE city = 'New York';          -- 3. What filtering condition to apply

-- Output:
-- name           | department | salary
-- Charlie Brown  | Sales      | 61000
-- Alice Martin   | IT         | 75000

-- 1. View all databases on the current server
SHOW DATABASES;

-- 2. Select the specific database you want to work with
USE company_db;

-- 3. Confirm which database is currently active
SELECT DATABASE();

-- Inspect the column structure and data types of our employees table
DESCRIBE employees;

-- Output shows: Field, Type, Null, Key, Default, Extra
-- id         | int          | NO   | PRI | NULL    | auto_increment
-- name       | varchar(100) | NO   |     | NULL    |
-- department | varchar(50)  | NO   |     | NULL    |
-- salary     | decimal(10,2)| NO   |     | NULL    |
-- city       | varchar(50)  | NO   |     | NULL    |

-- Selecting vertical columns:
SELECT name, department FROM employees;

-- Filtering for a single horizontal row:
SELECT * FROM employees WHERE id = 1;

-- Output: Returns the complete row for employee #1 (Alice Martin)

-- Our Canonical 8-Row employees Dataset:
-- id | name             | department | salary   | city
-- 1  | Alice Martin     | IT         | 75000.00 | New York
-- 2  | Bob Smith        | HR         | 52000.00 | Chicago
-- 3  | Charlie Brown    | Sales      | 61000.00 | New York
-- 4  | Diana Prince     | IT         | 82000.00 | Austin
-- 5  | Evan Wright      | Finance    | 68000.00 | Chicago
-- 6  | Fiona Gallagher  | Sales      | 59000.00 | Miami
-- 7  | George Clark     | HR         | 48000.00 | Austin
-- 8  | Hannah Abbott    | IT         | 71000.00 | Miami

SELECT * FROM employees;

-- Demonstration of data types in the employees table schema:
-- id         -> INT             (e.g., 1, 2, 3)
-- name       -> VARCHAR(100)    (e.g., 'Alice Martin')
-- department -> VARCHAR(50)     (e.g., 'IT', 'Sales')
-- salary     -> DECIMAL(10, 2)  (e.g., 75000.00)
-- city       -> VARCHAR(50)     (e.g., 'New York')

SELECT id, name, salary
FROM employees
WHERE salary > 60000;

-- Looking up an employee record by their unique Primary Key ID
SELECT *
FROM employees
WHERE id = 4;

-- Output:
-- id: 4 | name: Diana Prince | department: IT | salary: 82000.00 | city: Austin

-- Recap Practice: Try finding employee #7
SELECT name, department FROM employees WHERE id = 7;
-- Answer: George Clark | HR

-- The most basic SELECT query
SELECT name
FROM employees;

-- Output: Returns the 8 employee names in order:
-- Alice Martin
-- Bob Smith
-- Charlie Brown
-- Diana Prince
-- Evan Wright
-- Fiona Gallagher
-- George Clark
-- Hannah Abbott

-- Correct Syntax Pattern:
SELECT
    id,
    name,
    salary
FROM
    employees;

-- Output: A clean 3-column table showing id, name, and salary for all 8 rows

-- Select all columns from our employees table
SELECT *
FROM employees;

-- Output: Returns all 5 columns (id, name, department, salary, city)
-- for all 8 rows in the table.

-- Requesting only the name and department columns
SELECT name, department
FROM employees;

-- Output:
-- name             | department
-- Alice Martin     | IT
-- Bob Smith        | HR
-- Charlie Brown    | Sales
-- Diana Prince     | IT
-- Evan Wright      | Finance
-- Fiona Gallagher  | Sales
-- George Clark     | HR
-- Hannah Abbott    | IT

-- Giving user-friendly labels to columns using AS
SELECT
    name AS employee_name,
    salary AS monthly_salary,
    city AS office_location
FROM employees;

-- Output headers:
-- employee_name | monthly_salary | office_location

-- Calculating annual pay and a projected 10% holiday bonus
SELECT
    name,
    salary,
    (salary * 12) AS annual_salary,
    (salary * 0.10) AS holiday_bonus
FROM employees;

-- Output sample:
-- Alice Martin | 75000 | 900000 | 7500.00
-- Bob Smith    | 52000 | 624000 | 5200.00

-- WRONG: Trailing comma before FROM causes syntax error
-- SELECT name, department, FROM employees;

-- FIXED: Clean comma placement
SELECT name, department
FROM employees;

-- Mini Practice:
-- Q1: Select only name and city with alias 'home_city'.
-- Answer: SELECT name, city AS home_city FROM employees;

-- Q2: Show each employee's salary after a $500 monthly raise.
-- Answer: SELECT name, (salary + 500) AS new_salary FROM employees;

-- Basic FROM clause pointing directly to the employees table
SELECT name, department, salary
FROM employees;

-- Output: Successfully retrieves rows because 'employees' exists
-- and contains the columns name, department, and salary.

-- 1. Standard FROM referencing current database
SELECT name, city
FROM employees;

-- 2. Fully qualified schema reference (database.table)
SELECT name, city
FROM company_db.employees;

-- Both queries return identical results when company_db is active.

-- Using table alias 'e' for the employees table
SELECT
    e.id,
    e.name,
    e.department,
    e.salary
FROM employees AS e;

-- Output: Returns the requested columns cleanly with alias prefixes

-- WRONG: Table name typo
-- SELECT name FROM employes;
-- Error: Table 'company_db.employes' doesn't exist

-- FIXED:
SELECT name FROM employees;

-- Mini Practice:
-- Q1: Write a query selecting name and salary using table alias 'emp'.
-- Answer: SELECT emp.name, emp.salary FROM employees AS emp;

-- Q2: Query all columns from employees using the schema prefix 'company_db'.
-- Answer: SELECT * FROM company_db.employees;

-- Retrieve only employees located in Chicago
SELECT name, department, salary, city
FROM employees
WHERE city = 'Chicago';

-- Output:
-- Bob Smith   | HR      | 52000 | Chicago
-- Evan Wright | Finance | 68000 | Chicago

-- Syntax Pattern Demonstration
SELECT
    name,
    salary,
    department
FROM
    employees
WHERE
    department = 'IT';

-- Output: Returns the 3 IT staff members:
-- Alice Martin (75000), Diana Prince (82000), Hannah Abbott (71000)

-- Filter employees with a salary strictly greater than $70,000
SELECT name, department, salary
FROM employees
WHERE salary > 70000;

-- Output:
-- Alice Martin | IT | 75000.00
-- Diana Prince | IT | 82000.00
-- Hannah Abbott| IT | 71000.00

-- Filtering by text string columns wrapped in single quotes
SELECT name, department, city
FROM employees
WHERE city = 'Miami';

-- Output:
-- Fiona Gallagher | Sales | Miami
-- Hannah Abbott   | IT    | Miami

-- Why column aliases fail in the WHERE clause:

-- WRONG (Causes error because 'annual_pay' does not exist yet):
-- SELECT name, (salary * 12) AS annual_pay
-- FROM employees
-- WHERE annual_pay > 800000;

-- CORRECT (Use the raw calculation in WHERE):
SELECT name, (salary * 12) AS annual_pay
FROM employees
WHERE (salary * 12) > 800000;

-- Output:
-- Alice Martin | 900000.00
-- Diana Prince | 984000.00
-- Hannah Abbott| 852000.00

-- WRONG: Missing quotes around string
-- SELECT * FROM employees WHERE department = HR;

-- FIXED:
SELECT * FROM employees WHERE department = 'HR';

-- Mini Practice:
-- Q1: Find all employees earning less than $55,000.
-- Answer: SELECT name, salary FROM employees WHERE salary < 55000;

-- Q2: Find all employees based in 'Austin'.
-- Answer: SELECT name, department FROM employees WHERE city = 'Austin';

-- Using multiple comparison operators in queries:
SELECT name, salary
FROM employees
WHERE salary = 75000;    -- Equal test

SELECT name, salary
FROM employees
WHERE salary != 75000;   -- Not equal test

-- 1. Equal to test (=)
SELECT name, department
FROM employees
WHERE department = 'Sales';
-- Returns: Charlie Brown, Fiona Gallagher

-- 2. Not equal to test (<> or !=)
SELECT name, department
FROM employees
WHERE department <> 'Sales';
-- Returns all 6 employees who do not work in Sales

-- 1. Strictly greater than $70,000
SELECT name, salary
FROM employees
WHERE salary > 70000;
-- Returns: Alice Martin (75k), Diana Prince (82k), Hannah Abbott (71k)

-- 2. Strictly less than $60,000
SELECT name, salary
FROM employees
WHERE salary < 60000;
-- Returns: Bob Smith (52k), Fiona Gallagher (59k), George Clark (48k)

-- Inclusive filtering: include employees earning exactly $61,000
SELECT name, department, salary
FROM employees
WHERE salary >= 61000;

-- Output:
-- Alice Martin   | IT      | 75000.00
-- Charlie Brown  | Sales   | 61000.00  <-- Boundary value included!
-- Diana Prince   | IT      | 82000.00
-- Evan Wright    | Finance | 68000.00
-- Hannah Abbott  | IT      | 71000.00

-- Checking for missing or present values

-- WRONG (Always returns 0 rows!):
-- SELECT * FROM employees WHERE department = NULL;

-- CORRECT:
SELECT name, department
FROM employees
WHERE department IS NOT NULL;

-- All 8 employees have a recorded department, so all 8 rows return.

-- Comparison Operator Practice Queries:

-- Q1: Find all employees who do NOT work in the 'IT' department.
SELECT name, department FROM employees WHERE department <> 'IT';
-- Answer: Bob, Charlie, Evan, Fiona, George (5 employees)

-- Q2: Find all employees who earn $68,000 or less.
SELECT name, salary FROM employees WHERE salary <= 68000;
-- Answer: Bob (52k), Charlie (61k), Evan (68k), Fiona (59k), George (48k)

-- Previewing combined filtering on the employees table
SELECT name, department, salary, city
FROM employees
WHERE department = 'IT' AND city = 'New York';

-- Output:
-- | name         | department | salary | city     |
-- | Alice Martin | IT         |  75000 | New York |

-- Find employees who work in IT AND earn more than 75000
SELECT id, name, department, salary, city
FROM employees
WHERE department = 'IT' AND salary > 75000;

-- Output:
-- | id | name         | department | salary | city   |
-- |  4 | Diana Prince | IT         |  82000 | Austin |

-- Find employees who work in HR OR live in Chicago
SELECT id, name, department, salary, city
FROM employees
WHERE department = 'HR' OR city = 'Chicago';

-- Output:
-- | id | name        | department | salary | city    |
-- |  2 | Bob Smith   | HR         |  52000 | Chicago |
-- |  5 | Evan Wright | Finance    |  68000 | Chicago |
-- |  7 | George Clark| HR         |  48000 | Austin  |

-- Find all employees who do NOT work in the IT department
SELECT id, name, department, salary, city
FROM employees
WHERE NOT department = 'IT';

-- Output:
-- | id | name            | department | salary | city     |
-- |  2 | Bob Smith       | HR         |  52000 | Chicago  |
-- |  3 | Charlie Brown   | Sales      |  61000 | New York |
-- |  5 | Evan Wright     | Finance    |  68000 | Chicago  |
-- |  6 | Fiona Gallagher | Sales      |  59000 | Miami    |
-- |  7 | George Clark    | HR         |  48000 | Austin   |

-- WRONG: Might return Sales employees from any city!
-- WHERE department = 'IT' OR department = 'Sales' AND city = 'Miami';

-- CORRECT: Parentheses guarantee department choice happens first
SELECT id, name, department, salary, city
FROM employees
WHERE (department = 'IT' OR department = 'Sales') AND city = 'Miami';

-- Output:
-- | id | name            | department | salary | city  |
-- |  6 | Fiona Gallagher | Sales      |  59000 | Miami |
-- |  8 | Hannah Abbott   | IT         |  71000 | Miami |

-- Truth Table Reference Demo:
-- Test 1 (department = 'IT') | Test 2 (city = 'Miami')
-- Alice:    TRUE  AND FALSE => FALSE  | TRUE  OR FALSE => TRUE
-- Bob:      FALSE AND FALSE => FALSE  | FALSE OR FALSE => FALSE
-- Hannah:   TRUE  AND TRUE  => TRUE   | TRUE  OR TRUE  => TRUE

SELECT name, department, city
FROM employees
WHERE (department = 'IT' AND city = 'Miami')
   OR (department = 'HR' AND city = 'Austin');

-- Output: George Clark (HR, Austin) and Hannah Abbott (IT, Miami)

-- Practice 1: Find IT workers earning more than 70000 in Miami
-- Answer:
SELECT * FROM employees WHERE department = 'IT' AND salary > 70000 AND city = 'Miami';

-- Practice 2: Find employees living in Chicago OR New York
-- Answer:
SELECT * FROM employees WHERE city = 'Chicago' OR city = 'New York';

-- Practice 3: Find non-HR employees who earn at least 60000
-- Answer:
SELECT * FROM employees WHERE NOT department = 'HR' AND salary >= 60000;

-- Finding employees located in specific target cities using IN
SELECT id, name, department, city
FROM employees
WHERE city IN ('New York', 'Austin', 'Miami');

-- Output: Returns 6 employees whose city is New York, Austin, or Miami
-- (Only Bob Smith and Evan Wright from Chicago are excluded)

-- Syntax: SELECT columns FROM table WHERE column IN (list);
SELECT id, name, department, salary
FROM employees
WHERE department IN ('HR', 'Finance');

-- Output:
-- | id | name         | department | salary |
-- |  2 | Bob Smith    | HR         |  52000 |
-- |  5 | Evan Wright  | Finance    |  68000 |
-- |  7 | George Clark | HR         |  48000 |

-- Find all employees who do NOT work in IT or Sales
SELECT id, name, department, salary, city
FROM employees
WHERE department NOT IN ('IT', 'Sales');

-- Output:
-- | id | name         | department | salary | city    |
-- |  2 | Bob Smith    | HR         |  52000 | Chicago |
-- |  5 | Evan Wright  | Finance    |  68000 | Chicago |
-- |  7 | George Clark | HR         |  48000 | Austin  |

-- CLUTTERED APPROACH (Repeated ORs):
-- WHERE city = 'New York' OR city = 'Chicago' OR city = 'Miami';

-- CLEAN PROFESSIONAL APPROACH (IN operator):
SELECT name, department, city
FROM employees
WHERE city IN ('New York', 'Chicago', 'Miami');

-- Output: Returns 6 employees residing in the three requested cities

-- Practice 1: Select employees whose ID is 1, 3, or 5
-- Answer:
SELECT * FROM employees WHERE id IN (1, 3, 5);

-- Practice 2: Select employees who live in Austin or Chicago
-- Answer:
SELECT * FROM employees WHERE city IN ('Austin', 'Chicago');

-- Practice 3: Select employees who are NOT in HR or Finance
-- Answer:
SELECT * FROM employees WHERE department NOT IN ('HR', 'Finance');

-- Finding employees with salaries between 50000 and 70000 (inclusive)
SELECT id, name, department, salary
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

-- Output:
-- | id | name            | department | salary |
-- |  2 | Bob Smith       | HR         |  52000 |
-- |  3 | Charlie Brown   | Sales      |  61000 |
-- |  5 | Evan Wright     | Finance    |  68000 |
-- |  6 | Fiona Gallagher | Sales      |  59000 |

-- Find employees earning between 60000 and 80000
SELECT id, name, department, salary
FROM employees
WHERE salary BETWEEN 60000 AND 80000;

-- Output:
-- | id | name          | department | salary |
-- |  1 | Alice Martin  | IT         |  75000 |
-- |  3 | Charlie Brown | Sales      |  61000 |
-- |  5 | Evan Wright   | Finance    |  68000 |
-- |  8 | Hannah Abbott | IT         |  71000 |

-- Demonstration: Filtering hire dates using BETWEEN
-- (Assuming an optional hire_date column formatted as YYYY-MM-DD)
-- SELECT name, hire_date
-- FROM employees
-- WHERE hire_date BETWEEN '2022-01-01' AND '2023-12-31';

-- Numeric range comparison on our standard employees table:
SELECT id, name, salary
FROM employees
WHERE salary BETWEEN 70000 AND 85000;

-- Output: Alice Martin (75000), Diana Prince (82000), Hannah Abbott (71000)

-- Find employees whose salary is NOT between 55000 and 75000
SELECT id, name, department, salary
FROM employees
WHERE salary NOT BETWEEN 55000 AND 75000;

-- Output:
-- | id | name         | department | salary |
-- |  2 | Bob Smith    | HR         |  52000 | (below 55000)
-- |  4 | Diana Prince | IT         |  82000 | (above 75000)
-- |  7 | George Clark | HR         |  48000 | (below 55000)

-- Practice 1: Find employees with ID between 2 and 5 (inclusive)
-- Answer:
SELECT * FROM employees WHERE id BETWEEN 2 AND 5;

-- Practice 2: Find employees earning between 45000 and 60000
-- Answer:
SELECT * FROM employees WHERE salary BETWEEN 45000 AND 60000;

-- Practice 3: Find employees whose salary is NOT between 60000 and 80000
-- Answer:
SELECT * FROM employees WHERE salary NOT BETWEEN 60000 AND 80000;

-- Finding employees whose name starts with the letter 'A'
SELECT id, name, department, city
FROM employees
WHERE name LIKE 'A%';

-- Output:
-- | id | name         | department | city     |
-- |  1 | Alice Martin | IT         | New York |

-- Find all employees whose city contains the letters 'am'
SELECT id, name, department, city
FROM employees
WHERE city LIKE '%am%';

-- Output:
-- | id | name            | department | city  |
-- |  6 | Fiona Gallagher | Sales      | Miami |
-- |  8 | Hannah Abbott   | IT         | Miami |

-- Find employees whose city has 'i' as the second letter and is 5 characters long
SELECT id, name, department, city
FROM employees
WHERE city LIKE '_i___';

-- Explanation:
-- Miami: M (1), i (2), a (3), m (4), i (5) => Matches exactly!
-- Output: Fiona Gallagher (Miami) and Hannah Abbott (Miami)

-- 1. Starts with 'Ch':
SELECT name, city FROM employees WHERE city LIKE 'Ch%';
-- Output: Bob Smith (Chicago), Evan Wright (Chicago)

-- 2. Ends with 'k':
SELECT name, city FROM employees WHERE city LIKE '%k';
-- Output: Alice Martin (New York), Charlie Brown (New York)

-- 3. Contains 'Prince':
SELECT name FROM employees WHERE name LIKE '%Prince%';
-- Output: Diana Prince

-- Find employees whose name does NOT start with 'A'
SELECT id, name, department
FROM employees
WHERE name NOT LIKE 'A%';

-- Universal case-insensitive technique (works on all SQL engines):
SELECT name, city
FROM employees
WHERE LOWER(city) LIKE '%york%';

-- Output: Alice Martin (New York), Charlie Brown (New York)

-- Practice 1: Find employees whose name starts with 'D'
-- Answer:
SELECT * FROM employees WHERE name LIKE 'D%';

-- Practice 2: Find employees whose name ends with 'n'
-- Answer:
SELECT * FROM employees WHERE name LIKE '%n';

-- Practice 3: Find employees whose department contains 'a'
-- Answer:
SELECT * FROM employees WHERE department LIKE '%a%';

-- Without DISTINCT: Shows duplicate departments
SELECT department
FROM employees;
-- Output: IT, HR, Sales, IT, Finance, Sales, HR, IT (8 rows, duplicates!)

-- With DISTINCT: Shows only unique departments
SELECT DISTINCT department
FROM employees;
-- Output: IT, HR, Sales, Finance (4 clean unique rows)

-- Find all unique cities where employees live
SELECT DISTINCT city
FROM employees;

-- Output:
-- | city     |
-- | New York |
-- | Chicago  |
-- | Austin   |
-- | Miami    |
-- (8 employees condensed into 4 unique cities)

-- Find all unique department and city combinations
SELECT DISTINCT department, city
FROM employees;

-- Output:
-- | department | city     |
-- | IT         | New York |
-- | HR         | Chicago  |
-- | Sales      | New York |
-- | IT         | Austin   |
-- | Finance    | Chicago  |
-- | Sales      | Miami    |
-- | HR         | Austin   |
-- | IT         | Miami    |

-- Count total rows vs unique departments
SELECT 
    COUNT(department) AS total_department_entries,
    COUNT(DISTINCT department) AS unique_departments,
    COUNT(DISTINCT city) AS unique_cities
FROM employees;

-- Output:
-- | total_department_entries | unique_departments | unique_cities |
-- |                        8 |                  4 |             4 |

-- Practice 1: List all distinct cities where employees live
-- Answer:
SELECT DISTINCT city FROM employees;

-- Practice 2: List all distinct departments in the company
-- Answer:
SELECT DISTINCT department FROM employees;

-- Practice 3: Count how many distinct cities employees reside in
-- Answer:
SELECT COUNT(DISTINCT city) FROM employees;

-- Sorting employees by salary from lowest to highest
SELECT id, name, department, salary
FROM employees
ORDER BY salary;

-- Output: George Clark (48000), Bob Smith (52000) ... up to Diana Prince (82000)

-- Explicitly sorting employees alphabetically by name in ascending order
SELECT id, name, department, city
FROM employees
ORDER BY name ASC;

-- Output:
-- | id | name            | department | city     |
-- |  1 | Alice Martin    | IT         | New York |
-- |  2 | Bob Smith       | HR         | Chicago  |
-- |  3 | Charlie Brown   | Sales      | New York |
-- |  4 | Diana Prince    | IT         | Austin   |
-- |  5 | Evan Wright     | Finance    | Chicago  |
-- |  6 | Fiona Gallagher | Sales      | Miami    |
-- |  7 | George Clark    | HR         | Austin   |
-- |  8 | Hannah Abbott   | IT         | Miami    |

-- Ranking employees from highest paid to lowest paid
SELECT id, name, department, salary
FROM employees
ORDER BY salary DESC;

-- Output:
-- | id | name            | department | salary |
-- |  4 | Diana Prince    | IT         |  82000 |
-- |  1 | Alice Martin    | IT         |  75000 |
-- |  8 | Hannah Abbott   | IT         |  71000 |
-- |  5 | Evan Wright     | Finance    |  68000 |
-- |  3 | Charlie Brown   | Sales      |  61000 |
-- |  6 | Fiona Gallagher | Sales      |  59000 |
-- |  2 | Bob Smith       | HR         |  52000 |
-- |  7 | George Clark    | HR         |  48000 |

-- Group by department alphabetically, then rank by salary descending
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

-- Output:
-- Finance: Evan Wright (68000)
-- HR:      Bob Smith (52000), George Clark (48000)
-- IT:      Diana Prince (82000), Alice Martin (75000), Hannah Abbott (71000)
-- Sales:   Charlie Brown (61000), Fiona Gallagher (59000)

-- Sorting text alphabetically by city, then by employee name
SELECT city, name, department
FROM employees
ORDER BY city ASC, name ASC;

-- Output:
-- Austin:   Diana Prince, George Clark
-- Chicago:  Bob Smith, Evan Wright
-- Miami:    Fiona Gallagher, Hannah Abbott
-- New York: Alice Martin, Charlie Brown

-- Practice 1: Sort all employees by salary from highest to lowest
-- Answer:
SELECT * FROM employees ORDER BY salary DESC;

-- Practice 2: Sort employees alphabetically by city
-- Answer:
SELECT * FROM employees ORDER BY city ASC;

-- Practice 3: Find IT employees and sort them by salary descending
-- Answer:
SELECT * FROM employees WHERE department = 'IT' ORDER BY salary DESC;

-- Returning only the first 3 rows from the employees table
SELECT id, name, department, salary
FROM employees
LIMIT 3;

-- Output:
-- | id | name          | department | salary |
-- |  1 | Alice Martin  | IT         |  75000 |
-- |  2 | Bob Smith     | HR         |  52000 |
-- |  3 | Charlie Brown | Sales      |  61000 |

-- Retrieve only 4 employees who work in IT or Sales
SELECT id, name, department, city
FROM employees
WHERE department IN ('IT', 'Sales')
LIMIT 4;

-- Output:
-- | id | name            | department | city     |
-- |  1 | Alice Martin    | IT         | New York |
-- |  3 | Charlie Brown   | Sales      | New York |
-- |  4 | Diana Prince    | IT         | Austin   |
-- |  6 | Fiona Gallagher | Sales      | Miami    |

-- Find the top 3 highest-earning employees in the company
SELECT id, name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

-- Output:
-- | id | name          | department | salary |
-- |  4 | Diana Prince  | IT         |  82000 |
-- |  1 | Alice Martin  | IT         |  75000 |
-- |  8 | Hannah Abbott | IT         |  71000 |

-- Page 1: Top 3 earners (skip 0)
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 0;
-- Diana Prince (82000), Alice Martin (75000), Hannah Abbott (71000)

-- Page 2: Next 3 earners (skip 3)
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 3;
-- Evan Wright (68000), Charlie Brown (61000), Fiona Gallagher (59000)

-- Page 3: Final 2 earners (skip 6)
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 6;
-- Bob Smith (52000), George Clark (48000)

-- Dialect Comparison Cheat Sheet:
-- MySQL / Postgres / SQLite:
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;

-- SQL Server (T-SQL):
-- SELECT TOP 5 name, salary FROM employees ORDER BY salary DESC;

-- Oracle (12c+ / ANSI Standard):
-- SELECT name, salary FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;

-- Practice: Find the single lowest paid employee
-- Answer: SELECT * FROM employees ORDER BY salary ASC LIMIT 1;

-- Running all 5 aggregate functions together on our employees table
SELECT 
    COUNT(*) AS total_staff,
    SUM(salary) AS total_payroll,
    AVG(salary) AS average_salary,
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary
FROM employees;

-- Output:
-- | total_staff | total_payroll | average_salary | lowest_salary | highest_salary |
-- |           8 |        516000 |       64500.00 |         48000 |          82000 |

-- Counting total employees vs employees in the IT department
SELECT 
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN department = 'IT' THEN 1 END) AS it_employees
FROM employees;

-- Output:
-- | total_employees | it_employees |
-- |               8 |            3 |

-- Simple direct count with WHERE:
SELECT COUNT(*) AS miami_staff FROM employees WHERE city = 'Miami';
-- Output: 2

-- Calculate the total monthly salary expense for the IT department
SELECT SUM(salary) AS total_it_payroll
FROM employees
WHERE department = 'IT';

-- Calculation:
-- Alice Martin (75000) + Diana Prince (82000) + Hannah Abbott (71000) = 228000
-- Output:
-- | total_it_payroll |
-- |           228000 |

-- Calculate the average salary across all employees, rounded to 2 decimal places
SELECT 
    AVG(salary) AS raw_average,
    ROUND(AVG(salary), 2) AS clean_average
FROM employees;

-- Calculation: Total salary 516000 / 8 employees = 64500.00
-- Output:
-- | raw_average | clean_average |
-- |    64500.00 |      64500.00 |

-- Finding the salary range and alphabetical extremes in the company
SELECT 
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary,
    MIN(name) AS first_name_alpha,
    MAX(name) AS last_name_alpha
FROM employees;

-- Output:
-- | lowest_salary | highest_salary | first_name_alpha | last_name_alpha |
-- |         48000 |          82000 | Alice Martin     | Hannah Abbott   |

-- Calculate salary statistics specifically for the Sales department
SELECT 
    COUNT(*) AS sales_headcount,
    SUM(salary) AS total_sales_payroll,
    AVG(salary) AS avg_sales_salary
FROM employees
WHERE department = 'Sales';

-- Output:
-- | sales_headcount | total_sales_payroll | avg_sales_salary |
-- |               2 |              120000 |         60000.00 |

-- WRONG: Mixing non-aggregated column with aggregate function!
-- SELECT name, AVG(salary) FROM employees; -- Error in standard SQL!

-- CORRECT: Use subquery or pure aggregate
SELECT AVG(salary) AS avg_salary FROM employees;

-- Practice 1: Find total headcount of employees in Chicago
-- Answer: SELECT COUNT(*) FROM employees WHERE city = 'Chicago';

-- Practice 2: Find the maximum salary in the HR department
-- Answer: SELECT MAX(salary) FROM employees WHERE department = 'HR';

-- Grouping employees by department to see headcount per department
SELECT department, COUNT(*) AS staff_count
FROM employees
GROUP BY department;

-- Output:
-- | department | staff_count |
-- | IT         |           3 |
-- | HR         |           2 |
-- | Sales      |           2 |
-- | Finance    |           1 |

-- Department-level salary summary with proper syntax
SELECT 
    department, 
    COUNT(*) AS total_employees,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;

-- Output:
-- | department | total_employees | average_salary |
-- | IT         |               3 |       76000.00 |
-- | Finance    |               1 |       68000.00 |
-- | Sales      |               2 |       60000.00 |
-- | HR         |               2 |       50000.00 |

-- Count how many employees reside in each city, sorted from most to least
SELECT city, COUNT(*) AS employee_count
FROM employees
GROUP BY city
ORDER BY employee_count DESC;

-- Output:
-- | city     | employee_count |
-- | New York |              2 |
-- | Chicago  |              2 |
-- | Austin   |              2 |
-- | Miami    |              2 |

-- Complete departmental financial breakdown
SELECT 
    department,
    COUNT(*) AS headcount,
    SUM(salary) AS total_department_payroll,
    ROUND(AVG(salary), 2) AS avg_department_salary
FROM employees
GROUP BY department;

-- Output:
-- | department | headcount | total_department_payroll | avg_department_salary |
-- | IT         |         3 |                   228000 |              76000.00 |
-- | HR         |         2 |                   100000 |              50000.00 |
-- | Sales      |         2 |                   120000 |              60000.00 |
-- | Finance    |         1 |                    68000 |              68000.00 |

-- Group by department AND city to see multi-level staffing
SELECT department, city, COUNT(*) AS staff_count
FROM employees
GROUP BY department, city
ORDER BY department, city;

-- Output:
-- | department | city     | staff_count |
-- | Finance    | Chicago  |           1 |
-- | HR         | Austin   |           1 |
-- | HR         | Chicago  |           1 |
-- | IT         | Austin   |           1 |
-- | IT         | Miami    |           1 |
-- | IT         | New York |           1 |
-- | Sales      | Miami    |           1 |
-- | Sales      | New York |           1 |

-- WRONG: 'name' is neither aggregated nor in GROUP BY!
-- SELECT department, name, AVG(salary) FROM employees GROUP BY department;

-- CORRECT: Either aggregate name or remove it
SELECT department, COUNT(name) AS staff_count, AVG(salary) AS avg_sal
FROM employees
GROUP BY department;

-- Practice 1: Find total salary per city
-- Answer: SELECT city, SUM(salary) FROM employees GROUP BY city;

-- Practice 2: Find average salary per department sorted highest to lowest
-- Answer: SELECT department, AVG(salary) FROM employees GROUP BY department ORDER BY AVG(salary) DESC;

-- WRONG: Cannot use aggregate function in WHERE!
-- SELECT department, COUNT(*) FROM employees WHERE COUNT(*) > 2 GROUP BY department;

-- CORRECT: Use HAVING to filter the aggregated group count
SELECT department, COUNT(*) AS staff_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

-- Output:
-- | department | staff_count |
-- | IT         |           3 |

-- Find departments whose average salary exceeds 60000
SELECT 
    department, 
    ROUND(AVG(salary), 2) AS average_salary,
    COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- Output:
-- | department | average_salary | headcount |
-- | IT         |       76000.00 |         3 |
-- | Finance    |       68000.00 |         1 |
-- (HR at 50000 and Sales at 60000 are excluded because they do not exceed 60000)

-- Using BOTH WHERE and HAVING together in the same query:
-- 1. WHERE filters individual employees earning at least 50000
-- 2. GROUP BY organizes remaining employees into departments
-- 3. HAVING keeps only departments with at least 2 such employees
SELECT department, COUNT(*) AS qualifying_staff, AVG(salary) AS avg_sal
FROM employees
WHERE salary >= 50000
GROUP BY department
HAVING COUNT(*) >= 2;

-- Output:
-- IT (3 staff, avg 76000) and Sales (2 staff, avg 60000) qualify!

-- The Ultimate Complete Beginner SQL Query
SELECT department, COUNT(*) AS staff_count, ROUND(AVG(salary), 2) AS avg_sal
FROM employees
WHERE salary >= 50000
GROUP BY department
HAVING COUNT(*) >= 2
ORDER BY avg_sal DESC
LIMIT 1;

-- Output:
-- | department | staff_count | avg_sal  |
-- | IT         |           3 | 76000.00 |

-- Practice 1: Find departments with total payroll greater than 150000
-- Answer:
SELECT department, SUM(salary) AS total_pay FROM employees GROUP BY department HAVING SUM(salary) > 150000;

-- Practice 2: Find cities having more than 1 employee
-- Answer:
SELECT city, COUNT(*) AS count FROM employees GROUP BY city HAVING COUNT(*) > 1;

-- Practice 3: Complete query - Cities with average salary > 65000 sorted descending
-- Answer:
SELECT city, AVG(salary) AS avg_sal FROM employees GROUP BY city HAVING AVG(salary) > 65000 ORDER BY avg_sal DESC;

-- Summary of the core DDL commands:
-- CREATE : Builds a new database, table, or index
-- ALTER  : Modifies the schema of an existing table
-- DROP   : Permanently destroys a table or database
-- TRUNCATE: Empties all records from a table while keeping the table structure

-- Checking database version and connection
SELECT VERSION() AS database_engine_version;

-- Step 1: Create a new company database safely
CREATE DATABASE IF NOT EXISTS company_db;

-- Step 2: View all databases on the server
SHOW DATABASES;

-- Step 3: Switch into our new database
USE company_db;

-- Output: Database changed. You are now working inside company_db.

-- Creating our canonical employees table from scratch
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary INT NOT NULL,
    city VARCHAR(50) NOT NULL
);

-- Inspect table structure in MySQL:
DESCRIBE employees;
-- Output: Displays column names, types, nullability, and primary key status

-- Creating a robust table with all 4 major constraints
CREATE TABLE employee_accounts (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL UNIQUE,
    full_name VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATE DEFAULT (CURRENT_DATE)
);

-- Inserting without is_active automatically uses default value TRUE:
INSERT INTO employee_accounts (email, full_name)
VALUES ('alice@company.com', 'Alice Martin');

-- Add an email column to our existing employees table
ALTER TABLE employees
ADD COLUMN email VARCHAR(100);

-- Add a status column with a safe default value
ALTER TABLE employees
ADD COLUMN status VARCHAR(20) DEFAULT 'Active';

-- Verify the new columns exist:
SELECT id, name, email, status FROM employees LIMIT 2;
-- Output: Shows existing employees with NULL email and 'Active' status

-- MySQL: Expanding department column size from VARCHAR(50) to VARCHAR(100)
ALTER TABLE employees
MODIFY COLUMN department VARCHAR(100) NOT NULL;

-- Dialect note: PostgreSQL and SQL Server equivalent:
-- ALTER TABLE employees ALTER COLUMN department TYPE VARCHAR(100);

-- Verify the change in MySQL:
DESCRIBE employees;
-- Output: department column now displays Type: varchar(100)

-- Rename 'city' column to 'office_location'
ALTER TABLE employees
RENAME COLUMN city TO office_location;

-- Rename the column back to 'city' to keep our standard schema intact
ALTER TABLE employees
RENAME COLUMN office_location TO city;

-- Rename an entire table:
-- ALTER TABLE employees RENAME TO staff_members;

-- Add a temporary scratch column for demonstration
ALTER TABLE employees ADD COLUMN temp_notes VARCHAR(100);

-- Permanently drop the column from the table
ALTER TABLE employees
DROP COLUMN temp_notes;

-- Verify the column has been completely removed:
DESCRIBE employees;
-- Output: temp_notes no longer appears in the table definition

-- Safe syntax using IF EXISTS:
DROP TABLE IF EXISTS temporary_logs;

-- Empty all rows without destroying the table structure:
-- TRUNCATE TABLE test_table;

-- Danger Zone: Completely deletes an entire database and all tables!
-- DROP DATABASE IF EXISTS test_sandbox_db;

-- Verification:
SHOW TABLES;

-- Final DDL Practice:
-- Practice 1: Create a projects table with id, project_name, and budget
CREATE TABLE IF NOT EXISTS projects (
    id INT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    budget DECIMAL(10,2) DEFAULT 0.00
);

-- Practice 2: Add a deadline column to projects
ALTER TABLE projects ADD COLUMN deadline DATE;

-- Practice 3: Clean up test table safely
DROP TABLE IF EXISTS projects;