SQL Concepts

Introduction to Databases and SQL

Schema: named namespace for DB objects; also the logical design.
DBMS vs RDBMS:
- DBMS: generic database system (may be non-relational).
- RDBMS: relational, uses relations (tables), keys, constraints, and SQL.
SQL basics:
- Data languages:
  - DDL: CREATE, ALTER, DROP, TRUNCATE.
  - DML: INSERT, UPDATE, DELETE.
  - DQL: SELECT.
  - DCL: GRANT, REVOKE.
  - TCL: BEGIN/COMMIT/ROLLBACK/SAVEPOINT.
OLTP vs OLAP:
- OLTP: transactional workloads (short queries, normalized).
- OLAP: analytical workloads (aggregations, denormalization, star schemas).

Data Modeling: ER & Normalization

ER modeling:
- Entity (e.g., Customer), Attribute (e.g., email), Relationship (1:1, 1:N, M:N).
- Cardinality and optionality define relationship rules.

Data Types

Numeric: SMALLINT, INTEGER/INT, BIGINT, DECIMAL/NUMERIC(p,s), REAL/DOUBLE/FLOAT.
Strings: CHAR(n), VARCHAR(n), TEXT.
Date/Time: DATE, TIME, TIMESTAMP.
Boolean: TINYINT(1) (MySQL), BIT (SQL Server).
Binary: VARBINARY, BLOB.
UUID/GUID: UNIQUEIDENTIFIER (SQL Server), CHAR(36) (MySQL).
JSON: JSON (MySQL, SQL Server).

DDL: Create/Alter/Drop/Truncate/Partitioning

-- Create database (engine-specific)
CREATE DATABASE shop;

-- Switch database
-- MySQL: USE shop;   SQL Server: USE shop;

-- Create table with constraints
CREATE TABLE customers (
  id            INT AUTO_INCREMENT PRIMARY KEY,           -- MySQL
  -- SQL Server: id INT IDENTITY(1,1) PRIMARY KEY
  name          VARCHAR(100) NOT NULL,
  email         VARCHAR(255) UNIQUE,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Alter table
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
ALTER TABLE customers MODIFY COLUMN phone VARCHAR(30);       -- MySQL
-- SQL Server: ALTER TABLE customers ALTER COLUMN phone VARCHAR(30);
ALTER TABLE customers RENAME COLUMN phone TO mobile;         -- MySQL 8.0+
-- SQL Server: sp_rename 'customers.phone', 'mobile', 'COLUMN';
ALTER TABLE customers DROP COLUMN mobile;

-- Drop vs Truncate
DROP TABLE customers;         -- remove definition + data
TRUNCATE TABLE customers;     -- fast delete all rows (resets identities)

-- MySQL RANGE partitioning (by year)
CREATE TABLE metrics (
  ts DATETIME NOT NULL,
  value DOUBLE
) PARTITION BY RANGE (YEAR(ts)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pMAX  VALUES LESS THAN MAXVALUE
);

Constraints & Referential Integrity

Types:
- PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY.
Foreign keys (RI):

CREATE TABLE orders (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  customer_id  INT NOT NULL,
  order_date   DATE NOT NULL,
  status       VARCHAR(20) CHECK (status IN ('pending','paid','shipped','cancelled')),
  total_amount NUMERIC(10,2) NOT NULL CHECK (total_amount >= 0),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);

DML & Querying

-- SELECT
SELECT id, name, email FROM customers
WHERE email IS NOT NULL
  AND name LIKE 'A%'
ORDER BY created_at DESC, name ASC
LIMIT 10 OFFSET 20;

-- INSERT
INSERT INTO customers (name, email) VALUES ('Alice','alice@example.com');
INSERT INTO customers (name, email) VALUES
  ('Bob','bob@example.com'), ('Carol',NULL);

-- INSERT ... SELECT
INSERT INTO orders (customer_id, order_date, status, total_amount)
SELECT id, CURDATE(), 'pending', 0
FROM customers WHERE email IS NULL;

-- UPDATE / DELETE
UPDATE orders
SET status = CASE WHEN total_amount > 0 THEN 'paid' ELSE 'pending' END
WHERE order_date < CURDATE();

DELETE FROM orders WHERE customer_id = 123;

Upsert:

MySQL: INSERT ... ON DUPLICATE KEY UPDATE ...
SQL Server: MERGE (use carefully) or pattern with TRY/UPDATE ELSE INSERT.

Joins (INNER/OUTER/CROSS)

-- INNER
SELECT c.id, c.name, o.id AS order_id, o.total_amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

-- LEFT/RIGHT
SELECT c.id, c.name, o.id AS order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

-- FULL OUTER (SQL Server only)
SELECT c.id, c.name, o.id
FROM customers c FULL OUTER JOIN orders o ON o.customer_id = c.id;

-- CROSS
SELECT c.name, s.status
FROM customers c
CROSS JOIN (SELECT DISTINCT status FROM orders) s;

Aggregation & Grouping

-- Aggregates
SELECT
  COUNT(*)          AS order_count,
  SUM(total_amount) AS revenue,
  AVG(total_amount) AS avg_order,
  MIN(total_amount) AS min_order,
  MAX(total_amount) AS max_order
FROM orders
WHERE status = 'paid';

-- GROUP BY & HAVING
SELECT customer_id, COUNT(*) AS orders, SUM(total_amount) AS spend
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000
ORDER BY spend DESC;

-- GROUPING SETS / ROLLUP / CUBE (SQL Server)
SELECT region, product, SUM(amount) AS amt
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ());

Window Functions

SELECT
  customer_id,
  order_date,
  total_amount,
  ROW_NUMBER()  OVER (PARTITION BY customer_id ORDER BY order_date) AS rn,
  RANK()        OVER (ORDER BY total_amount DESC)                    AS rnk,
  DENSE_RANK()  OVER (ORDER BY total_amount DESC)                    AS drnk,
  SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)               AS running_spend,
  LAG(total_amount)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
  LEAD(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_amount
FROM orders;

Advanced:

NTILE(n), PERCENT_RANK, CUME_DIST, FIRST_VALUE/LAST_VALUE.
Frame types: ROWS, RANGE (engine nuances).

Subqueries & CTEs

-- Subquery
SELECT * FROM customers
WHERE id IN (
  SELECT customer_id FROM orders
  GROUP BY customer_id
  HAVING SUM(total_amount) > 500
);

-- CTE
WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT customer_id, COUNT(*) FROM paid_orders GROUP BY customer_id;

-- Recursive CTE (SQL Server)
WITH org AS (
  SELECT id, manager_id, name, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

Conditional & Null Handling

-- CASE
SELECT id,
  CASE
    WHEN total_amount = 0 THEN 'free'
    WHEN total_amount < 50 THEN 'small'
    ELSE 'regular'
  END AS order_size
FROM orders;

-- COALESCE / NULLIF
SELECT COALESCE(email, 'unknown@example.com') FROM customers;
SELECT NULLIF(status, 'pending') FROM orders;

-- Vendor equivalents:
-- MySQL: IFNULL(expr, alt)
-- SQL Server: ISNULL(expr, alt)

Watch-outs:

NULL makes predicates 3-valued (TRUE/FALSE/UNKNOWN). Use IS NULL/IS NOT NULL.
NOT IN with NULLs returns UNKNOWN (use NOT EXISTS).

Set Operations

SELECT email FROM customers
UNION
SELECT contact_email FROM partners;

SELECT email FROM customers
UNION ALL
SELECT contact_email FROM partners;

-- INTERSECT/EXCEPT (SQL Server)
SELECT email FROM customers
INTERSECT
SELECT contact_email FROM partners;

SELECT email FROM customers
EXCEPT
SELECT contact_email FROM partners;

Functions (String/Date/Math/JSON)

Examples (dialect varies):

Strings: LOWER/UPPER, TRIM/LTRIM/RTRIM, SUBSTRING, REPLACE, CONCAT.
Date/Time: CURDATE(), NOW(), DATE_ADD (MySQL), DATEADD (SQL Server).
Math: ABS, CEIL/FLOOR, ROUND, POWER.
JSON:
- MySQL: JSON_EXTRACT, JSON_SET, JSON_UNQUOTE, JSON_TABLE.
- SQL Server: JSON_VALUE, JSON_QUERY, OPENJSON.

-- MySQL JSON_TABLE example
SELECT jt.product_id, jt.qty
FROM orders o,
JSON_TABLE(o.items, '$[*]' COLUMNS (product_id INT PATH '$.id', qty INT PATH '$.qty')) AS jt;

-- SQL Server JSON example
SELECT JSON_VALUE(data, '$.email') AS email
FROM users_json
WHERE JSON_VALUE(data, '$.active') = 'true';

Database Objects: Views

CREATE VIEW customer_summary AS
SELECT c.id, c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total_amount),0) AS spend
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Programmability: Procedures, Functions, Triggers

-- MySQL procedure
DELIMITER $$
CREATE PROCEDURE set_order_paid(IN p_order_id INT)
BEGIN
  UPDATE orders SET status = 'paid' WHERE id = p_order_id;
END$$
DELIMITER ;

-- SQL Server procedure
CREATE PROCEDURE set_order_paid @p_order_id INT
AS
BEGIN
  UPDATE orders SET status = 'paid' WHERE id = @p_order_id;
END;

-- Trigger (MySQL)
CREATE TRIGGER trg_order_status
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO order_audit(order_id, old_status, new_status)
  VALUES (OLD.id, OLD.status, NEW.status);
END;

-- Trigger (SQL Server)
CREATE TRIGGER trg_order_status
ON orders
AFTER UPDATE
AS
BEGIN
  INSERT INTO order_audit(order_id, old_status, new_status)
  SELECT i.id, d.status, i.status FROM inserted i JOIN deleted d ON i.id = d.id;
END;

Events/Scheduling:
- MySQL EVENT, SQL Server Agent Jobs.

Transactions, ACID, Isolation, Locking

ACID:
- Atomicity, Consistency, Isolation, Durability.
Transaction control:

BEGIN;         -- MySQL: START TRANSACTION;  SQL Server: BEGIN TRANSACTION;
-- work ...
SAVEPOINT sp1;
-- more ...
ROLLBACK TO sp1;  -- partial undo
COMMIT;           -- or ROLLBACK;

Isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable.
- Phenomena: dirty reads, non-repeatable reads, phantom reads.
- Defaults: MySQL/InnoDB: Repeatable Read; SQL Server: Read Committed.
Concurrency:
- InnoDB uses MVCC; readers don't block writers.
- Locks: row/page/table, intent locks, lock escalation (SQL Server).
- Deadlocks: detect & retry, keep transactions short and ordered.

Indexing & Performance

Index types:
- B-tree: general purpose, lookup O(log n).
- Hash: equality O(1) average.
- Clustered (SQL Server) vs Nonclustered; MySQL primary key clustered for InnoDB.
Best practices:
- Create selective indexes on predicates and joins.
- Composite index order matters; support leading columns and sort orders.
- Covering indexes (include columns) reduce lookups (SQL Server INCLUDE).
- Filtered indexes (SQL Server).
- SARGability: avoid wrapping indexed columns in functions in WHERE/JOIN.
Plans:

EXPLAIN
SELECT c.id, COUNT(o.id)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Pagination:
- Keyset (WHERE id > :last_id ORDER BY id LIMIT n) is faster than OFFSET for large offsets.

Full-text Search & JSON

Full-text:
- MySQL: FULLTEXT indexes with MATCH ... AGAINST.
- SQL Server: CONTAINS/CONTAINSTABLE.
JSON:
- Store semi-structured data; index with functional indexes (MySQL/SQL Server).

Security & Permissions

Users, Roles, Grants; least privilege.
Schemas/namespaces.
Example grants (MySQL):

CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'strongpass';
GRANT SELECT ON shop.* TO 'analyst'@'localhost';
REVOKE SELECT ON shop.* FROM 'analyst'@'localhost';

Example grants (SQL Server):

CREATE LOGIN analyst WITH PASSWORD = 'strongpass';
CREATE USER analyst FOR LOGIN analyst;
GRANT SELECT ON dbo.customers TO analyst;