SELECT column1, column2, ...
FROM table_name;

-- Get everything from the employees table
SELECT * FROM employees;

-- Only get names and salaries
SELECT name, salary FROM employees;

SELECT
  name AS employee_name,
  salary AS annual_salary,
  salary / 12 AS monthly_salary
FROM employees;

-- Get unique department names only
SELECT DISTINCT department FROM employees;

SELECT * FROM employees
WHERE department = 'Engineering';

SELECT * FROM employees
WHERE department = 'Engineering'
  AND salary > 100000;

SELECT * FROM employees
WHERE department = 'Sales'
  OR department = 'Marketing';

SELECT * FROM employees
WHERE NOT department = 'Sales';

SELECT * FROM employees
WHERE salary BETWEEN 70000 AND 100000;

SELECT * FROM employees
WHERE department IN ('Engineering', 'Marketing');

-- Names starting with 'A'
SELECT * FROM employees
WHERE name LIKE 'A%';

-- Names containing 'ol'
SELECT * FROM employees
WHERE name LIKE '%ol%';

-- Find employees with no department assigned
SELECT * FROM employees
WHERE department IS NULL;

-- Sort by salary, highest first
SELECT * FROM employees
ORDER BY salary DESC;

-- Sort by name alphabetically (default is ASC)
SELECT * FROM employees
ORDER BY name ASC;

-- Sort by department first, then salary within each dept
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

-- Get the top 2 highest-paid employees
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 2;

-- Skip first 2, then get next 2 (page 2)
SELECT * FROM employees
ORDER BY id
LIMIT 2 OFFSET 2;

-- Count all employees
SELECT COUNT(*) AS total_employees FROM employees;

-- Average salary
SELECT AVG(salary) AS avg_salary FROM employees;

-- Highest salary
SELECT MAX(salary) AS top_salary FROM employees;

-- Average salary per department
SELECT
  department,
  COUNT(*) AS num_employees,
  AVG(salary) AS avg_salary,
  MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- Only departments with avg salary above 75000
SELECT
  department,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000;

SELECT
  e.name,
  d.dept_name
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.id;

-- All departments, even those with no employees
SELECT
  d.dept_name,
  e.name
FROM departments d
LEFT JOIN employees e
  ON d.id = e.dept_id;

-- Employees earning above average
SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

-- Employees in departments that have 'Eng' in the name
SELECT name
FROM employees
WHERE dept_id IN (
  SELECT id FROM departments
  WHERE dept_name LIKE '%Eng%'
);

-- Get departments with their employee count, then filter
SELECT *
FROM (
  SELECT department, COUNT(*) AS emp_count
  FROM employees
  GROUP BY department
) AS dept_stats
WHERE emp_count > 1;

-- Departments that have at least one employee
SELECT dept_name
FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e
  WHERE e.dept_id = d.id
);

-- Insert a single row
INSERT INTO employees (name, department, salary)
VALUES ('Eve', 'Engineering', 98000);

-- Insert multiple rows
INSERT INTO employees (name, department, salary)
VALUES
  ('Frank', 'Sales', 71000),
  ('Grace', 'Marketing', 78000);

-- Give Alice a raise
UPDATE employees
SET salary = 105000
WHERE name = 'Alice';

-- 10% raise for all engineers
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';

-- Delete a specific employee
DELETE FROM employees
WHERE name = 'Dave';

-- Delete all employees in Sales
DELETE FROM employees
WHERE department = 'Sales';

-- ⚠️ DANGER: This deletes EVERYTHING!
-- DELETE FROM employees;

-- Step 1: Preview what will be affected
SELECT * FROM employees
WHERE department = 'Sales';

-- Step 2: If looks good, then update inside a transaction
BEGIN;
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Sales';
-- Check the results
SELECT * FROM employees WHERE department = 'Sales';
-- If good:
COMMIT;
-- If bad:
-- ROLLBACK;

CREATE TABLE employees (
  id        INTEGER PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  email     VARCHAR(255) UNIQUE,
  dept_id   INTEGER,
  salary    DECIMAL(10,2) DEFAULT 0,
  hired_at  DATE DEFAULT CURRENT_DATE
);

CREATE TABLE departments (
  id        INTEGER PRIMARY KEY,
  dept_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  id        INTEGER PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  dept_id   INTEGER,
  FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- Add a column
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

-- Remove a column
ALTER TABLE employees
DROP COLUMN phone;

-- Drop (delete) an entire table
DROP TABLE employees;

-- Create a view for high earners
CREATE VIEW high_earners AS
SELECT name, salary, department
FROM employees
WHERE salary > 90000;

-- Now use it like a regular table
SELECT * FROM high_earners;

-- Create an index on the department column
CREATE INDEX idx_department
ON employees(department);

-- Queries filtering by department are now faster
SELECT * FROM employees
WHERE department = 'Engineering';

WITH dept_stats AS (
  SELECT
    department,
    AVG(salary) AS avg_salary,
    COUNT(*) AS emp_count
  FROM employees
  GROUP BY department
)
SELECT *
FROM dept_stats
WHERE avg_salary > 80000
ORDER BY avg_salary DESC;

-- Rank employees by salary within their department
SELECT
  name,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

SELECT
  name,
  salary,
  SUM(salary) OVER (
    ORDER BY salary
  ) AS running_total
FROM employees;

-- 1. Authors
CREATE TABLE authors (
  id     INTEGER PRIMARY KEY,
  name   VARCHAR(200) NOT NULL,
  country VARCHAR(100)
);

-- 2. Books
CREATE TABLE books (
  id         INTEGER PRIMARY KEY,
  title      VARCHAR(300) NOT NULL,
  author_id  INTEGER REFERENCES authors(id),
  price      DECIMAL(8,2),
  genre      VARCHAR(50),
  published  DATE
);

-- 3. Customers
CREATE TABLE customers (
  id     INTEGER PRIMARY KEY,
  name   VARCHAR(200) NOT NULL,
  email  VARCHAR(255) UNIQUE NOT NULL,
  city   VARCHAR(100)
);

-- 4. Orders
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  book_id     INTEGER REFERENCES books(id),
  quantity    INTEGER DEFAULT 1,
  order_date  DATE DEFAULT CURRENT_DATE
);