SQL Interview Questions

SELECT name, salary FROM employees WHERE salary > 50000;

employees                          departments
┌────┬────────┬─────────┐         ┌────┬─────────────┐
│ id │ name   │ dept_id │── FK ──→│ id │ dept_name   │
├────┼────────┼─────────┤         ├────┼─────────────┤
│  1 │ Alice  │   10    │         │ 10 │ IT          │
│  2 │ Bob    │   20    │         │ 20 │ HR          │
└────┴────────┴─────────┘         └────┴─────────────┘

DDL: CREATE TABLE users (id INT PRIMARY KEY);
DML: INSERT INTO users VALUES (1, 'Alice');
DCL: GRANT SELECT ON users TO 'john';
TCL: COMMIT;

CREATE TABLE users (
  id   INT          PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL
);

-- Composite primary key
CREATE TABLE enrollments (
  student_id INT,
  course_id  INT,
  PRIMARY KEY (student_id, course_id)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id  INT,
  amount   DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- CHAR: always 2 bytes stored
country_code CHAR(2) -- 'US', 'IN', 'GB'

-- VARCHAR: only stores actual data
email VARCHAR(255)   -- 'alice@example.com' uses ~18 bytes

DELETE FROM employees WHERE id = 5;  -- one row
TRUNCATE TABLE employees;            -- all rows, keep structure
DROP TABLE employees;                -- entire table gone

salary DECIMAL(10,2)  -- 99999999.99 (exact)
rating FLOAT           -- approximate

CREATE TABLE events (
  event_date DATE,
  event_time TIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- TIMESTAMP is timezone-aware
-- If you insert '2024-04-06 14:30:00' with session timezone +05:30
-- It stores as UTC: '2024-04-06 09:00:00'
-- Retrieval converts back to session timezone

CREATE TABLE users (
  id    INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,  -- no two users can have the same email
  phone VARCHAR(15) UNIQUE    -- multiple UNIQUE columns allowed
);

-- Add UNIQUE later
ALTER TABLE users ADD UNIQUE (email);

-- Composite UNIQUE
ALTER TABLE users ADD UNIQUE (first_name, last_name);

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  department VARCHAR(50) DEFAULT 'General',
  salary     DECIMAL(10,2) DEFAULT 0.00,
  is_active  BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO employees (id, name) VALUES (1, 'Alice');
-- department = 'General', salary = 0.00, is_active = TRUE, created_at = NOW()

CREATE TABLE employees (
  id     INT PRIMARY KEY,
  name   VARCHAR(100),
  age    INT CHECK (age >= 18 AND age <= 100),
  salary DECIMAL(10,2) CHECK (salary >= 0),
  gender ENUM('M', 'F', 'Other')
);

-- Named check (better for error messages)
CREATE TABLE products (
  id    INT PRIMARY KEY,
  price DECIMAL(10,2),
  CONSTRAINT chk_price_positive CHECK (price > 0)
);

-- Check for NULL (cannot use = or !=)
SELECT * FROM employees WHERE phone IS NULL;
SELECT * FROM employees WHERE phone IS NOT NULL;

-- NULL in comparisons
NULL = NULL   -- NULL (not TRUE!)
NULL != NULL  -- NULL (not FALSE!)
NULL = 5      -- NULL

-- NULL in arithmetic
10 + NULL     -- NULL
CONCAT('Hi', NULL) -- NULL

-- NULL in aggregates
SELECT COUNT(*), COUNT(phone), AVG(salary)
FROM employees;
-- COUNT(*) counts all rows
-- COUNT(phone) excludes NULLs
-- AVG ignores NULLs

-- Comparison operators
SELECT * FROM employees WHERE salary >= 50000;
SELECT * FROM employees WHERE department = 'IT';

-- Multiple conditions
SELECT * FROM employees
WHERE department = 'IT' AND salary > 70000;

SELECT * FROM employees
WHERE department = 'IT' OR department = 'HR';

-- Range
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;

-- List match
SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance');

-- Pattern matching
SELECT * FROM employees WHERE name LIKE 'A%';     -- starts with A
SELECT * FROM employees WHERE name LIKE '%son';   -- ends with 'son'
SELECT * FROM employees WHERE name LIKE '%mith%'; -- contains 'mith'

-- NULL check
SELECT * FROM employees WHERE manager_id IS NULL;

-- Single column
SELECT * FROM employees ORDER BY salary DESC;

-- Multiple columns
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

-- By column position
SELECT name, salary FROM employees ORDER BY 2 DESC; -- order by salary

-- With expressions
SELECT name, salary * 12 AS annual FROM employees
ORDER BY annual DESC;

-- NULL ordering
SELECT name, manager_id FROM employees
ORDER BY manager_id ASC;  -- NULLs appear first (default in MySQL)

-- Top 5 highest paid
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Pagination: page 2 (rows 11-20)
SELECT * FROM employees
ORDER BY id
LIMIT 10 OFFSET 10;

-- Shorthand syntax
LIMIT 10 OFFSET 10  →  LIMIT 10, 10  (MySQL only)

-- Full syntax order
SELECT    column1, column2
FROM      table1
JOIN      table2 ON condition
WHERE     filter_condition
GROUP BY  columns
HAVING    group_filter
ORDER BY  columns
LIMIT     count OFFSET skip;

-- Execution order (important!)
-- FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

-- Basic examples
SELECT * FROM employees;                        -- all columns
SELECT DISTINCT department FROM employees;      -- unique values
SELECT name, salary * 12 AS annual FROM employees;  -- expression
SELECT NOW(), CURDATE();                        -- functions

-- Unique departments
SELECT DISTINCT department FROM employees;

-- Unique combinations
SELECT DISTINCT department, location FROM employees;

-- Count distinct values
SELECT COUNT(DISTINCT department) FROM employees;

-- DISTINCT vs GROUP BY
SELECT DISTINCT department FROM employees;        -- simpler
SELECT department FROM employees GROUP BY department;  -- more flexible (can add aggregates)

-- % (any sequence)
SELECT * FROM employees WHERE name LIKE 'A%';      -- starts with A
SELECT * FROM employees WHERE name LIKE '%son';     -- ends with 'son'
SELECT * FROM employees WHERE name LIKE '%mith%';   -- contains 'mith'
SELECT * FROM employees WHERE email LIKE '%@gmail.com';

-- _ (one character)
SELECT * FROM employees WHERE name LIKE 'A_';       -- exactly 2 chars, starts with A
SELECT * FROM employees WHERE name LIKE '____';     -- exactly 4 characters

-- Escape special chars
SELECT * FROM products WHERE code LIKE '100\%';     -- literal % (with ESCAPE)

-- IN: value in a list
SELECT * FROM employees WHERE department IN ('IT', 'Finance', 'HR');

-- IN with subquery
SELECT * FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE active = 1);

-- NOT IN
SELECT * FROM employees WHERE department NOT IN ('HR', 'Admin');

-- BETWEEN: inclusive range
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
-- Equivalent to: salary >= 40000 AND salary <= 80000

-- BETWEEN with dates
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- NOT BETWEEN
SELECT * FROM employees WHERE salary NOT BETWEEN 30000 AND 50000;

-- Column alias
SELECT name AS employee_name, salary * 12 AS annual_salary
FROM employees;

-- Table alias (essential for joins and self-joins)
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- Self-join alias
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- Alias with spaces (use quotes)
SELECT name AS "Employee Name" FROM employees;

-- Insert one row
INSERT INTO employees (name, email, department, salary)
VALUES ('Alice', 'alice@mail.com', 'IT', 75000);

-- Insert multiple rows (faster)
INSERT INTO employees (name, email, department, salary)
VALUES
  ('Bob', 'bob@mail.com', 'HR', 60000),
  ('Carol', 'carol@mail.com', 'Finance', 80000);

-- Insert from another query
INSERT INTO high_earners (name, salary)
SELECT name, salary FROM employees WHERE salary > 100000;

-- Insert with default values
INSERT INTO employees (name) VALUES ('David');
-- Other columns get their DEFAULT values

-- Insert ignoring duplicates
INSERT IGNORE INTO employees (id, name) VALUES (1, 'Alice');

-- Update single column
UPDATE employees SET salary = 85000 WHERE id = 1;

-- Update multiple columns
UPDATE employees
SET salary = 90000, department = 'Engineering'
WHERE id = 1;

-- Update with arithmetic
UPDATE employees SET salary = salary * 1.10 WHERE department = 'IT';

-- Update with subquery
UPDATE employees
SET manager_id = (SELECT id FROM employees WHERE name = 'Alice')
WHERE department = 'IT';

-- Update with JOIN (MySQL)
UPDATE employees e
JOIN departments d ON e.dept_id = d.id
SET e.salary = e.salary * 1.15
WHERE d.dept_name = 'Engineering';

-- First preview
SELECT * FROM employees WHERE department = 'HR';
-- Then update
UPDATE employees SET salary = salary * 1.05 WHERE department = 'HR';

-- Delete specific row
DELETE FROM employees WHERE id = 5;

-- Delete with condition
DELETE FROM employees WHERE department = 'HR' AND salary < 30000;

-- Delete with subquery
DELETE FROM employees
WHERE dept_id NOT IN (SELECT id FROM departments);

-- Delete with JOIN (MySQL)
DELETE e FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

-- Delete all rows (keep table)
DELETE FROM employees;
-- Faster alternative: TRUNCATE TABLE employees;

-- Check first
SELECT * FROM employees WHERE department = 'Temp';
-- Then delete
DELETE FROM employees WHERE department = 'Temp';

-- Basic table
CREATE TABLE employees (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) UNIQUE,
  department VARCHAR(50) DEFAULT 'General',
  salary     DECIMAL(10,2) CHECK (salary >= 0),
  hired_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE orders (
  order_id   INT PRIMARY KEY AUTO_INCREMENT,
  user_id    INT NOT NULL,
  amount     DECIMAL(10,2),
  order_date DATE,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
);

-- Temporary table (session-specific)
CREATE TEMPORARY TABLE temp_results (
  id INT,
  score INT
);

-- Create like another table (copies structure)
CREATE TABLE employees_backup LIKE employees;

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

-- Add column at specific position
ALTER TABLE employees ADD age INT AFTER name;

-- Drop a column
ALTER TABLE employees DROP COLUMN age;

-- Modify column data type
ALTER TABLE employees MODIFY salary FLOAT;

-- Rename a column
ALTER TABLE employees RENAME COLUMN phone TO mobile;

-- Change column name AND type (both)
ALTER TABLE employees CHANGE mobile contact_no VARCHAR(20);

-- Add constraint
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- Drop constraint
ALTER TABLE employees DROP CONSTRAINT chk_salary;

-- Rename table
ALTER TABLE employees RENAME TO staff;
-- or
RENAME TABLE employees TO staff;

-- Drop single table
DROP TABLE employees;

-- Drop multiple tables
DROP TABLE employees, departments;

-- Drop with IF EXISTS (prevents error if table doesn't exist)
DROP TABLE IF EXISTS employees;

-- Drop database
DROP DATABASE company_db;

-- Drop temporary table
DROP TEMPORARY TABLE temp_results;

TRUNCATE TABLE employees;

-- Case conversion
UPPER('hello')        -- 'HELLO'
LOWER('HELLO')        -- 'hello'

-- Trimming
TRIM('  hello  ')     -- 'hello'
LTRIM('  hello')      -- 'hello'
RTRIM('hello  ')      -- 'hello'

-- Substring
SUBSTRING('hello', 2, 3)   -- 'ell'
LEFT('hello', 2)           -- 'he'
RIGHT('hello', 2)          -- 'lo'

-- Length
LENGTH('hello')       -- 5 (bytes)
CHAR_LENGTH('hello')  -- 5 (characters - same for ASCII)

-- Concatenation
CONCAT('Hello', ' ', 'World')   -- 'Hello World'
CONCAT_WS('-', '2024', '04', '06')  -- '2024-04-06'

-- Search
LOCATE('ll', 'hello')   -- 3 (position)
INSTR('hello', 'll')    -- 3

-- Replace
REPLACE('hello world', 'world', 'SQL')  -- 'hello SQL'

-- Reverse
REVERSE('hello')  -- 'olleh'

-- Repeat
REPEAT('x', 3)    -- 'xxx'

-- Current date/time
NOW()         -- 2024-04-06 14:30:00
CURDATE()     -- 2024-04-06
CURTIME()     -- 14:30:00

-- Extract parts
YEAR(NOW())      -- 2024
MONTH(NOW())     -- 4
DAY(NOW())       -- 6
DAYOFWEEK(NOW()) -- 7 (Sunday=1)
DAYNAME(NOW())   -- 'Saturday'
MONTHNAME(NOW()) -- 'April'

-- Date arithmetic
DATE_ADD('2024-01-01', INTERVAL 1 MONTH)   -- 2024-02-01
DATE_SUB('2024-01-01', INTERVAL 7 DAY)     -- 2023-12-25
DATEDIFF('2024-03-01', '2024-01-01')       -- 60 (days)

-- Format
DATE_FORMAT(CURDATE(), '%M %d, %Y')  -- 'April 06, 2024'
DATE_FORMAT(CURDATE(), '%Y-%m-%d')   -- '2024-04-06'

-- Last day of month
LAST_DAY('2024-02-01')  -- '2024-02-29'

-- Extract
EXTRACT(YEAR FROM NOW())     -- 2024
EXTRACT(MONTH FROM NOW())    -- 4

-- Rounding
ROUND(3.14159, 2)   -- 3.14
ROUND(3.14159)      -- 3
CEIL(3.1)           -- 4 (ceiling)
FLOOR(3.9)          -- 3 (floor)
TRUNCATE(3.14159, 2) -- 3.14 (truncate, no rounding)

-- Absolute
ABS(-10)            -- 10

-- Power/Square root
POW(2, 3)           -- 8
SQRT(16)            -- 4

-- Random
RAND()              -- random number 0 to 1
FLOOR(RAND() * 100) -- 0 to 99

-- Modulo
MOD(10, 3)          -- 1
10 % 3              -- 1

-- Sign
SIGN(-5)            -- -1
SIGN(0)             -- 0
SIGN(5)             -- 1

-- Greatest/Least
GREATEST(3, 7, 1)   -- 7
LEAST(3, 7, 1)      -- 1

-- Simple CASE (equality)
SELECT name, salary,
  CASE department
    WHEN 'IT' THEN 'Technology'
    WHEN 'HR' THEN 'People'
    ELSE 'Other'
  END AS category
FROM employees;

-- Searched CASE (conditions)
SELECT name, salary,
  CASE
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 60000  THEN 'Mid-level'
    WHEN salary >= 30000  THEN 'Junior'
    ELSE 'Intern'
  END AS level
FROM employees;

-- CASE in ORDER BY
SELECT name, department, salary
FROM employees
ORDER BY
  CASE department
    WHEN 'IT' THEN 1
    WHEN 'Finance' THEN 2
    ELSE 3
  END;

-- CASE with aggregation (pivot)
SELECT
  SUM(CASE WHEN department = 'IT' THEN 1 ELSE 0 END) AS it_count,
  SUM(CASE WHEN department = 'HR' THEN 1 ELSE 0 END) AS hr_count
FROM employees;

-- COALESCE (multiple values)
SELECT
  name,
  COALESCE(phone, email, 'No contact') AS contact
FROM employees;
-- Returns first non-NULL: phone → email → 'No contact'

-- IFNULL (two values)
SELECT name, IFNULL(phone, 'No phone') AS phone FROM employees;

-- NULLIF (returns NULL if equal)
SELECT NULLIF(a, b);  -- if a = b, returns NULL; otherwise returns a

-- Practical use: division by zero prevention
SELECT
  total_sales,
  total_employees,
  total_sales / NULLIF(total_employees, 0) AS per_capita
FROM departments;

-- COUNT(*): total rows (includes NULLs)
SELECT COUNT(*) FROM employees;           -- 100 rows

-- COUNT(column): non-NULL values only
SELECT COUNT(phone) FROM employees;       -- 85 (15 NULLs excluded)

-- COUNT(DISTINCT): unique non-NULL values
SELECT COUNT(DISTINCT department) FROM employees;  -- 5 unique departments

-- Practical examples
SELECT
  COUNT(*)                    AS total_employees,
  COUNT(manager_id)           AS has_manager,
  COUNT(DISTINCT department)  AS unique_depts,
  COUNT(DISTINCT manager_id)  AS unique_managers
FROM employees;

SELECT
  COUNT(*)      AS total,         -- number of rows
  COUNT(salary) AS with_salary,   -- non-NULL values
  SUM(salary)   AS total_salary,  -- sum
  AVG(salary)   AS avg_salary,    -- average
  MAX(salary)   AS highest,       -- maximum
  MIN(salary)   AS lowest,        -- minimum
  STD(salary)   AS std_dev,       -- standard deviation
  VARIANCE(salary) AS variance,   -- variance
  GROUP_CONCAT(name) AS names     -- concatenate values (MySQL)
FROM employees;

-- Basic grouping
SELECT department, COUNT(*) AS total, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Multiple columns
SELECT department, location, COUNT(*) AS total
FROM employees
GROUP BY department, location;

-- With ORDER BY
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- Important: SELECT columns must be in GROUP BY or be aggregated
-- ✅ Correct
SELECT department, COUNT(*) FROM employees GROUP BY department;

-- ❌ Error (name is neither in GROUP BY nor aggregated)
SELECT department, name, COUNT(*) FROM employees GROUP BY department;

-- WHERE: filter rows BEFORE grouping
-- HAVING: filter groups AFTER grouping

SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
WHERE salary > 30000               -- exclude low earners
GROUP BY department
HAVING cnt > 5                      -- only departments with >5 employees
   AND avg_sal > 60000;             -- with average salary > 60000

-- WHERE can use columns, HAVING can use aggregates
-- ❌ WHERE cannot use aggregate functions
SELECT department FROM employees
WHERE COUNT(*) > 5                  -- ERROR!
GROUP BY department;

-- ✅ HAVING can use aggregates
SELECT department FROM employees
GROUP BY department
HAVING COUNT(*) > 5;                -- correct

-- Filter in WHERE: removes rows AFTER join (can turn LEFT JOIN into INNER)
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.dept_name = 'IT';
-- Only employees in IT — David (no dept) is excluded by WHERE

-- Filter in ON: applied BEFORE/during join (preserves all left rows)
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id AND d.dept_name = 'IT';
-- All employees — David appears with NULL for dept_name

-- Written order:
SELECT   column1, aggregate(col2)
FROM     table1
JOIN     table2 ON condition
WHERE    filter
GROUP BY column
HAVING   group_filter
ORDER BY column
LIMIT    n OFFSET m;

-- Execution order:
-- 1. FROM      → which table(s)?
-- 2. JOIN      → combine tables
-- 3. WHERE     → filter rows (no aggregates yet)
-- 4. GROUP BY  → group rows
-- 5. HAVING    → filter groups (aggregates available)
-- 6. SELECT    → compute columns/expressions
-- 7. ORDER BY  → sort results
-- 8. LIMIT     → restrict output

-- In MySQL, CREATE SCHEMA is a synonym for CREATE DATABASE
CREATE SCHEMA company_db;
CREATE DATABASE company_db;  -- same thing

-- View schema information
SHOW DATABASES;           -- list all schemas
SHOW TABLES;              -- tables in current schema
DESCRIBE employees;       -- column info for a table
SHOW CREATE TABLE employees;  -- full table definition

-- Information schema (metadata)
SELECT TABLE_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'company_db';

-- DDL: defines structure (auto-committed)
CREATE TABLE users (id INT PRIMARY KEY);

-- DML: manipulates data (can be rolled back)
START TRANSACTION;
  INSERT INTO users VALUES (1, 'Alice');
ROLLBACK;  -- undoes the insert

-- DCL: controls permissions
GRANT SELECT ON users TO 'john';

-- TCL: transaction control
SAVEPOINT sp1;
ROLLBACK TO sp1;

CREATE TABLE employees (
  id       INT PRIMARY KEY,
  name     VARCHAR(100),
  gender   ENUM('M', 'F', 'Other'),
  status   ENUM('active', 'inactive', 'suspended') DEFAULT 'active'
);

INSERT INTO employees VALUES (1, 'Alice', 'F', 'active');
INSERT INTO employees VALUES (2, 'Bob', 'M', DEFAULT);  -- 'active'

-- ENUM values are stored as integers internally (1, 2, 3...)
-- Can be compared by index position
SELECT * FROM employees WHERE gender > 1;  -- values after 'M'

-- UNION: removes duplicates (sorts results)
SELECT name FROM employees
UNION
SELECT name FROM contractors;
-- Returns unique names from both tables

-- UNION ALL: keeps duplicates (faster)
SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;
-- Returns ALL names, including duplicates

-- INTERSECT: rows that appear in both (MySQL 8.0.31+)
SELECT name FROM employees
INTERSECT
SELECT name FROM contractors;

-- EXCEPT: rows in first but not in second (MySQL 8.0.31+)
SELECT name FROM employees
EXCEPT
SELECT name FROM contractors;

-- Show columns in a table
DESCRIBE employees;
DESC employees;
EXPLAIN employees;  -- MySQL treats EXPLAIN and DESCRIBE as synonyms for tables

-- Output:
-- +----------+--------------+------+-----+---------+----------------+
-- | Field    | Type         | Null | Key | Default | Extra          |
-- +----------+--------------+------+-----+---------+----------------+
-- | id       | int          | NO   | PRI | NULL    | auto_increment |
-- | name     | varchar(100) | YES  |     | NULL    |                |
-- | email    | varchar(255) | YES  | UNI | NULL    |                |
-- +----------+--------------+------+-----+---------+----------------+

-- Show full table definition
SHOW CREATE TABLE employees;

-- Show table status (size, rows, engine)
SHOW TABLE STATUS LIKE 'employees';

-- List all tables
SHOW TABLES;

-- Show all databases
SHOW DATABASES;

-- Check engine
SHOW TABLE STATUS LIKE 'employees';

-- Specify engine
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
) ENGINE=InnoDB;

❌ Non-atomic                       ✅ 1NF
┌────┬────────┬──────────────┐      ┌────┬────────┬──────┐
│ id │ name   │ phones        │      │ id │ name   │ phone│
├────┼────────┼──────────────┤      ├────┼────────┼──────┤
│ 1  │ Alice  │ 111, 222     │  →   │ 1  │ Alice  │ 111  │
└────┴────────┴──────────────┘      │ 1  │ Alice  │ 222  │
                                    └────┴────────┴──────┘

-- Split into: students(student_id, name), courses(course_id, name)
-- enrollments(student_id, course_id, grade)

-- ❌ employee(id, name, dept_id, dept_name, dept_location)
-- dept_name depends on dept_id, not employee id

-- ✅ employees(id, name, dept_id)
--    departments(dept_id, dept_name, location)

-- Normalized (3NF)
SELECT o.order_id, u.name, o.amount
FROM orders o
JOIN users u ON o.user_id = u.id;  -- needs JOIN every time

-- Denormalized (redundant but faster reads)
CREATE TABLE orders_denormalized (
  order_id INT,
  user_name VARCHAR(100),  -- copied from users table
  amount DECIMAL(10,2)
);
SELECT order_id, user_name, amount FROM orders_denormalized;  -- no JOIN

Without Index:                With Index (B-Tree):
Scan all 1M rows  →           Jump directly to matching rows
     🐢 O(n)                          🚀 O(log n)

-- Create index
CREATE INDEX idx_department ON employees(department);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_email ON employees(email);

-- Composite index (for multi-column queries)
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- Drop index
DROP INDEX idx_department ON employees;

-- Check index usage
EXPLAIN SELECT * FROM employees WHERE department = 'IT';
-- Look for: key (index used), rows (scanned)

CREATE TABLE employees (
  -- PRIMARY KEY: unique identifier, NOT NULL, one per table
  id         INT PRIMARY KEY AUTO_INCREMENT,

  -- NOT NULL: cannot be empty
  name       VARCHAR(100) NOT NULL,

  -- UNIQUE: all values must be different
  email      VARCHAR(255) UNIQUE,

  -- DEFAULT: value used when none provided
  status     VARCHAR(20) DEFAULT 'active',

  -- CHECK: validates condition (MySQL 8.0.16+)
  salary     DECIMAL(10,2) CHECK (salary > 0),

  -- FOREIGN KEY: references another table
  dept_id    INT,
  FOREIGN KEY (dept_id) REFERENCES departments(id)
    ON DELETE SET NULL
);

-- Add constraints to existing table
ALTER TABLE employees
  ADD CONSTRAINT chk_age CHECK (age >= 18),
  ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id);