Что такое функция CASE?

-- Searched CASE (more flexible)
SELECT name,
    CASE 
        WHEN salary >= 100000 THEN 'Executive'
        WHEN salary >= 60000  THEN 'Senior'
        WHEN salary >= 40000  THEN 'Mid'
        ELSE 'Junior'
    END AS level
FROM employees;

-- Simple CASE
SELECT name,
    CASE dept
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Sales' THEN 'Revenue'
        ELSE 'Other'
    END AS dept_category
FROM employees;

-- Comprehensive CREATE TABLE
CREATE TABLE employees (
    id              INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Surrogate key',
    email           VARCHAR(255) NOT NULL UNIQUE,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    salary          DECIMAL(10,2) CHECK (salary >= 0),
    department_id   INT,
    hire_date       DATE DEFAULT (CURRENT_DATE),
    is_active       TINYINT(1) DEFAULT 1,
    metadata        JSON,
    
    -- Foreign key with ON DELETE/UPDATE actions
    CONSTRAINT fk_dept 
        FOREIGN KEY (department_id) 
        REFERENCES departments(id)
        ON DELETE SET NULL      -- Keep employee if dept deleted
        ON UPDATE CASCADE,      -- Update dept_id if departments.id changes
    
    -- Table-level constraint
    CONSTRAINT chk_names CHECK (first_name <> last_name)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COMMENT='Company employees';

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

-- Add column with position (MySQL)
ALTER TABLE employees ADD middle_name VARCHAR(50) AFTER first_name;

-- Modify column type
ALTER TABLE employees MODIFY phone VARCHAR(30);

-- Rename column (MySQL)
ALTER TABLE employees CHANGE phone mobile_phone VARCHAR(30);

-- Rename column (PostgreSQL / standard)
ALTER TABLE employees RENAME COLUMN phone TO mobile_phone;

-- Drop column
ALTER TABLE employees DROP COLUMN phone;

-- Add index
ALTER TABLE employees ADD INDEX idx_hire_date (hire_date);

-- Drop foreign key
ALTER TABLE employees DROP FOREIGN KEY fk_dept;

-- INSERT: Add data
INSERT INTO employees (email, first_name, last_name, salary)
VALUES ('alice@co.com', 'Alice', 'Smith', 75000);

-- Bulk insert
INSERT INTO employees (email, first_name, last_name, salary)
VALUES 
    ('bob@co.com', 'Bob', 'Jones', 80000),
    ('carol@co.com', 'Carol', 'White', 90000);

-- INSERT from SELECT
INSERT INTO employees_archive (id, email, name)
SELECT id, email, CONCAT(first_name, ' ', last_name)
FROM employees WHERE is_active = 0;

-- UPDATE: Modify data (ALWAYS use WHERE!)
UPDATE employees 
SET salary = salary * 1.1, last_raise = CURDATE()
WHERE performance_rating = 'Exceeds';

-- UPDATE with JOIN (MySQL)
UPDATE employees e
JOIN departments d ON e.department_id = d.id
SET e.salary = e.salary * 1.05
WHERE d.location = 'San Francisco';

-- DELETE: Remove rows (ALWAYS use WHERE!)
DELETE FROM employees WHERE id = 42;

-- DELETE with JOIN
DELETE e FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Dissolved Team';

┌──────────────────────────────────────────────────────────────────────────────┐
│              DELETE vs TRUNCATE vs DROP — THE SHOWDOWN                       │
├─────────────┬─────────────┬─────────────┬────────────────────────────────────┤
│ Aspect      │ DELETE      │ TRUNCATE    │ DROP                               │
├─────────────┼─────────────┼─────────────┼────────────────────────────────────┤
│ Type        │ DML         │ DDL         │ DDL                                │
│ WHERE       │ Yes         │ No          │ N/A (whole object)                 │
│ Speed       │ Slow (row)  │ Fast (page) │ Fast                               │
│ Logging     │ Row-level   │ Page-level  │ Minimal                            │
│ Rollback    │ Yes         │ Sometimes   │ No                                 │
│ Triggers    │ Fires       │ Doesn't fire│ N/A                                │
│ Identity    │ Preserved   │ Reset       │ N/A                                │
│ Structure   │ Kept        │ Kept        │ DESTROYED                          │
│ Use case    │ Remove some │ Empty table │ Remove table forever               │
└─────────────┴─────────────┴─────────────┴────────────────────────────────────┘

-- UNIQUE allows multiple NULLs (in most DBs)
CREATE TABLE users (
    email VARCHAR(255) UNIQUE  -- NULL ≠ NULL in SQL, so multiple NULLs OK
);

-- CHECK constraint
CREATE TABLE products (
    price DECIMAL(10,2) CHECK (price > 0),
    discount DECIMAL(3,2) CHECK (discount BETWEEN 0 AND 1)
);