SQL Fundamentals

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  description TEXT,
  price REAL NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL DEFAULT 0,
  category TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert a single row
INSERT INTO products (name, price, stock, category)
VALUES ('Mechanical Keyboard', 89.99, 50, 'electronics');

-- Insert multiple rows
INSERT INTO products (name, price, stock, category)
VALUES
  ('Wireless Mouse', 29.99, 100, 'electronics'),
  ('USB-C Hub', 49.99, 75, 'electronics'),
  ('Desk Lamp', 34.99, 40, 'office'),
  ('Notebook', 12.99, 200, 'office'),
  ('Monitor Stand', 59.99, 30, 'office');

-- All columns
SELECT * FROM products;

-- Specific columns
SELECT name, price, stock FROM products;

-- With aliases
SELECT name AS product_name, price AS unit_price FROM products;

-- Exact match
SELECT * FROM products WHERE category = 'electronics';

-- Comparison operators
SELECT * FROM products WHERE price > 40;
SELECT * FROM products WHERE stock <= 50;
SELECT * FROM products WHERE price BETWEEN 20 AND 60;

-- Pattern matching
SELECT * FROM products WHERE name LIKE '%Mouse%';
SELECT * FROM products WHERE name LIKE 'USB%';

-- Multiple conditions
SELECT * FROM products
WHERE category = 'electronics' AND price < 50;

SELECT * FROM products
WHERE category = 'electronics' OR category = 'office';

-- IN operator
SELECT * FROM products
WHERE category IN ('electronics', 'office');

-- NULL checks
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT NULL;

-- Ascending (default)
SELECT * FROM products ORDER BY price;

-- Descending
SELECT * FROM products ORDER BY price DESC;

-- Multiple columns
SELECT * FROM products ORDER BY category, price DESC;

-- First 5 results
SELECT * FROM products ORDER BY price LIMIT 5;

-- Pagination: skip 10, take 5
SELECT * FROM products ORDER BY id LIMIT 5 OFFSET 10;

-- Count
SELECT COUNT(*) AS total_products FROM products;

-- Sum
SELECT SUM(stock) AS total_stock FROM products;

-- Average
SELECT AVG(price) AS avg_price FROM products;

-- Min and Max
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive FROM products;

SELECT
  category,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price,
  SUM(stock) AS total_stock
FROM products
GROUP BY category;

| category    | product_count | avg_price | total_stock |
|-------------|---------------|-----------|-------------|
| electronics | 3             | 56.66     | 225         |
| office      | 3             | 35.99     | 270         |

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 40;

-- Update specific rows
UPDATE products
SET price = 79.99, stock = stock - 1
WHERE name = 'Mechanical Keyboard';

-- Update with a condition
UPDATE products
SET stock = 0
WHERE stock < 5;

-- Delete specific rows
DELETE FROM products WHERE id = 3;

-- Delete all out-of-stock products
DELETE FROM products WHERE stock = 0;

-- Delete everything (use with caution)
DELETE FROM products;

SELECT name, price FROM products WHERE category = 'electronics'
UNION
SELECT name, price FROM products WHERE price > 50;

-- Products priced above average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Categories with more than 2 products
SELECT * FROM products
WHERE category IN (
  SELECT category FROM products
  GROUP BY category
  HAVING COUNT(*) > 2
);

CREATE TABLE departments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  department_id INTEGER NOT NULL,
  salary REAL NOT NULL,
  hire_date DATE NOT NULL,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

INSERT INTO departments (name) VALUES ('Engineering'), ('Marketing'), ('Sales');

INSERT INTO employees (name, department_id, salary, hire_date) VALUES
  ('Alice', 1, 95000, '2023-01-15'),
  ('Bob', 1, 105000, '2022-06-01'),
  ('Charlie', 2, 72000, '2024-03-10'),
  ('Diana', 3, 68000, '2024-07-22'),
  ('Eve', 1, 115000, '2021-11-05'),
  ('Frank', 2, 78000, '2023-09-18');

-- Find the highest-paid employee in each department
SELECT d.name AS department, e.name AS employee, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary = (
  SELECT MAX(salary) FROM employees WHERE department_id = e.department_id
)
ORDER BY e.salary DESC;