SQL

WHERE filters individual rows before grouping and aggregation. HAVING filters groups after aggregation, so it can reference aggregate functions that WHERE cannot.

SELECT dept, COUNT(*) AS n
FROM   employees
WHERE  active = true      -- row filter, before grouping
GROUP  BY dept
HAVING COUNT(*) > 5;      -- group filter, after aggregation

A SELF join is just a table joined to itself (aliased twice) — used for hierarchies like employee→manager.

Not the order you write it. The engine logically evaluates: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.

This one fact explains most beginner confusion: why an alias defined in SELECT can't be used in WHERE (WHERE runs first), but can be used in ORDER BY (which runs last).

NULL means "unknown", not zero or empty. Any comparison with it yields unknown, so x = NULL is never true — you must use IS NULL / IS NOT NULL.

• COALESCE(a, b, 0) returns the first non-null value.
• Aggregates like SUM/AVG ignore NULLs; COUNT(col) ignores them but COUNT(*) does not.

UNION removes duplicate rows, which forces a de-duplication sort/hash over the whole result — real cost on large sets. UNION ALL simply concatenates and is much cheaper.

Default to UNION ALL unless you specifically need duplicates removed. Interviewers listen for this — reaching for UNION by habit is a common performance smell.

• COUNT(*) — all rows, including those with NULLs.
• COUNT(col) — rows where col is not NULL.
• COUNT(DISTINCT col) — distinct non-NULL values.

A quick way to detect duplicates on a key: compare COUNT(*) with COUNT(DISTINCT key).

• Primary key — uniquely identifies a row; not null, one per table.
• Unique — enforces uniqueness on a column/set, but allows (typically one) NULL.
• Foreign key — references a key in another table, enforcing referential integrity.

DISTINCT removes duplicate rows from the result — the whole row, not one column. It costs a sort or hash over the full result set, so on large data it's expensive.

Rule of thumb: if you find yourself adding DISTINCT to "fix" duplicates, first ask why duplicates exist — it's often a join problem being masked.

SELECT * FROM products
ORDER BY price DESC, product_id   -- tie-breaker!
LIMIT 10 OFFSET 20;               -- rows 21–30

Always add a deterministic tie-breaker column: without it, rows with equal price can swap between pages and paging becomes unstable.

% matches any sequence of characters (including none); _ matches exactly one. 'A%' = starts with A; '%son' = ends with son; '_a%' = second letter is a.

Performance note: a leading wildcard ('%thing') defeats index/pruning use — the engine must scan.

IN (a,b,c) is shorthand for chained ORs. BETWEEN x AND y is inclusive on both ends.

The trap is timestamps: BETWEEN '2026-01-01' AND '2026-01-31' misses everything after midnight on the 31st. Safer pattern for dates with time:

WHERE dt >= '2026-01-01' AND dt < '2026-02-01'

Every column in the SELECT list must be either (a) in the GROUP BY, or (b) inside an aggregate function. That's the whole contract — one output row per distinct combination of the grouped columns.

SELECT dept, job, AVG(salary)      -- dept, job grouped; salary aggregated
FROM employees
GROUP BY dept, job;

-- 1) bucketing
SELECT CASE WHEN amount >= 1000 THEN 'large'
            WHEN amount >= 100  THEN 'medium'
            ELSE 'small' END AS size_band, COUNT(*)
FROM orders GROUP BY 1;

-- 2) conditional aggregation (mini-pivot)
SELECT SUM(CASE WHEN status='paid' THEN amount ELSE 0 END) AS paid,
       SUM(CASE WHEN status='open' THEN amount ELSE 0 END) AS open_amt
FROM invoices;

Conditions are evaluated top-down; the first match wins. With no ELSE, non-matches return NULL.

• CONCAT / || — join strings (mind NULL: in standard SQL, NULL || x = NULL).
• SUBSTRING(s, start, len), LEFT, RIGHT — slicing.
• TRIM / LTRIM / RTRIM — strip spaces (data cleaning staple).
• UPPER / LOWER — case normalisation before comparing.
• REPLACE(s, from, to), SPLIT_PART(s, ',', 2) — cleanup and parsing.
• LENGTH, POSITION/CHARINDEX — measuring and locating.

• CURRENT_DATE / CURRENT_TIMESTAMP — now.
• DATE_TRUNC('month', dt) — snap to period start; the backbone of time-series grouping.
• DATEADD / DATEDIFF (or interval arithmetic) — shifting and gaps.
• EXTRACT(DOW/YEAR/… FROM dt) — pulling parts.
• TO_DATE / TO_CHAR — parsing and formatting.

Grouping by DATE_TRUNC('month', dt) beats grouping by YEAR(dt), MONTH(dt) — one sortable column instead of two.

CHAR(n) pads to fixed length (rarely what you want); VARCHAR(n) stores up to n; TEXT is unbounded. For numbers: INT/BIGINT for counts and keys, DECIMAL(p,s) for money — never FLOAT for money, because binary floats can't represent 0.1 exactly and pennies drift.

A view is a saved query that behaves like a table. Uses: hide complexity behind a clean interface, enforce security (grant on the view, not the tables), and centralise a definition so "active customer" is written once.

A plain view stores no data — it re-runs on every query. A materialized view stores results and must be refreshed; faster reads, staleness and maintenance cost.

A query nested inside another. Three placements:

• WHERE — filtering: WHERE dept_id IN (SELECT id FROM depts WHERE region='EU')
• FROM — a derived table you then join or aggregate.
• SELECT — a scalar per row (use sparingly; often better as a join or window).

INSERT INTO t (a, b) VALUES (1, 'x');          -- name columns, always
UPDATE t SET b = 'y' WHERE id = 7;             -- WHERE first, then SET
DELETE FROM t WHERE created < '2020-01-01';

The habit that saves careers: run the WHERE as a SELECT first to see exactly which rows you're about to change, and wrap risky changes in a transaction so you can roll back.

SQL (Structured Query Language) is the language — a standard for defining, querying and manipulating relational data. MySQL is a database product that speaks SQL, just like PostgreSQL, SQL Server, Oracle and Snowflake do.

Every product implements the standard plus its own extensions — which is why LIMIT works in MySQL but SQL Server uses TOP. In an interview, saying "the concept is standard, the syntax detail varies by engine" is exactly the right frame.

A detail that impresses: DDL is auto-committing in most engines — you usually cannot roll back a DROP TABLE, which is why it deserves more fear than a DELETE.

CREATE TABLE employees (
  emp_id     INT          PRIMARY KEY,
  email      VARCHAR(255) UNIQUE NOT NULL,
  dept_id    INT          REFERENCES departments(dept_id),  -- FOREIGN KEY
  salary     DECIMAL(10,2) CHECK (salary > 0),
  hire_date  DATE         DEFAULT CURRENT_DATE,
  status     VARCHAR(10)  NOT NULL DEFAULT 'active'
);

• NOT NULL — value required.
• UNIQUE — no duplicates (NULLs usually allowed).
• PRIMARY KEY — UNIQUE + NOT NULL, one per table; the row's identity.
• FOREIGN KEY — value must exist in the referenced table.
• CHECK — an arbitrary condition every row must satisfy.
• DEFAULT — the value used when none is supplied.

Constraints are your first line of data quality — they stop bad data at the door instead of finding it in a report.

A primary key made of two or more columns whose combination must be unique:

CREATE TABLE order_items (
  order_id   INT,
  product_id INT,
  quantity   INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Neither column alone is unique — one order has many products, one product appears in many orders — but the pair identifies exactly one line. Natural fit for junction/bridge tables in many-to-many relationships.

They define what happens to child rows when the parent is deleted or its key updated:

FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
  ON DELETE CASCADE      -- delete the children too
  ON UPDATE CASCADE;

• CASCADE — propagate the delete/update to children. Convenient, dangerous: one parent delete can silently remove thousands of rows.
• SET NULL — orphan the children by nulling the FK (column must be nullable).
• RESTRICT / NO ACTION — refuse the delete while children exist. The safe default.

Production advice worth saying: prefer RESTRICT and delete deliberately — accidental cascades are a classic data-loss story.

-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY
-- SQL Server
id INT IDENTITY(1,1) PRIMARY KEY
-- Postgres
id BIGINT GENERATED ALWAYS AS IDENTITY
-- Oracle / standard
id NUMBER DEFAULT seq.NEXTVAL

Two behaviours to know: the counter does not reset on DELETE (only TRUNCATE typically resets it), and gaps are normal — rolled-back inserts consume numbers. Never build logic that assumes IDs are consecutive.

ALTER TABLE emp ADD COLUMN phone VARCHAR(20);
ALTER TABLE emp DROP COLUMN fax;
ALTER TABLE emp RENAME COLUMN nm TO name;
ALTER TABLE emp ALTER COLUMN salary TYPE DECIMAL(12,2);  -- Postgres syntax
ALTER TABLE emp ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE emp RENAME TO employees;

The production caveat: on huge tables some ALTERs rewrite the whole table and lock it. Adding a nullable column is usually instant; adding NOT NULL with a default, or changing a type, may not be. Always check before running one on a big production table.

NOT binds tightest, then AND, then OR. This query does not do what it looks like:

WHERE region = 'EU' OR region = 'UK' AND status = 'active'
-- actually means:  region='EU'  OR  (region='UK' AND status='active')
-- EU rows of ANY status leak through!

Always parenthesise mixed AND/OR. This is one of the most common real-world bug patterns in report SQL, and interviewers love it as a spot-the-bug question.

No — three different things:

• NULL = value unknown/absent. col = '' will NOT find it.
• '' = a known value that happens to be empty text.
• 0 = a known numeric value.

-- find both empty-ish states explicitly:
WHERE col IS NULL OR TRIM(col) = ''

One famous exception to name: Oracle historically treats '' as NULL — a portability trap.

The trap this creates: AVG(col) averages only the non-null rows. If half your rows are NULL, AVG(col) and SUM(col)/COUNT(*) give different answers — decide which the business actually means, and use AVG(COALESCE(col,0)) when NULL should count as zero.

SELECT region, product, SUM(revenue)
FROM sales
GROUP BY region, product;

One row per distinct combination — (EU, Widget), (EU, Gadget), (US, Widget)… The mental model that prevents mistakes: GROUP BY defines the grain of your output. Say the grain out loud — "one row per region per product" — and questions like "can I add customer to the SELECT?" answer themselves (no, unless you add it to the grain or aggregate it).

-- old style (avoid)
SELECT * FROM orders o, customers c WHERE o.cust_id = c.id;
-- modern
SELECT * FROM orders o JOIN customers c ON o.cust_id = c.id;

Same result — but the explicit form is strictly better: forget the WHERE in the old style and you get a silent Cartesian product (every order × every customer); forget the ON in the new style and you get an error. It also separates join conditions from filters, which matters once you use LEFT JOIN. There is no reason to write the comma form today.

Deliberately generating all combinations:

-- every store × every date = the complete reporting skeleton
SELECT s.store_id, d.dt
FROM stores s CROSS JOIN calendar d
WHERE d.dt BETWEEN '2026-01-01' AND '2026-12-31';

Left-join actuals onto that skeleton and days with zero sales appear as zero instead of vanishing. Other uses: parameter grids for testing, size × colour product variants. Accidental cross joins are a bug; intentional ones are a reporting technique.

JOIN dept d ON e.dept_id = d.dept_id   -- explicit, any condition
JOIN dept d USING (dept_id)            -- shorthand when names match
NATURAL JOIN dept                      -- joins on ALL same-named columns

USING is fine shorthand. NATURAL JOIN is a trap to name and avoid: it silently joins on every column with a matching name — add a harmless created_at to both tables later and the join quietly breaks. Explicit is durable.

• Same number of columns in each SELECT.
• Corresponding columns must be type-compatible (INT with INT, or castable).
• Column names come from the first SELECT.
• ORDER BY applies to the combined result and goes at the end.

SELECT id, name, 'customer' AS src FROM customers
UNION ALL
SELECT id, name, 'supplier'  FROM suppliers
ORDER BY name;

That literal src column is the classic pattern for tagging which table each row came from.

A separate, sorted lookup structure that lets the database find rows without scanning the whole table — like a book's index: instead of reading 500 pages to find "Snowflake", you check the index and jump to page 214.

The trade everyone must be able to state: indexes make reads faster and writes slower — every INSERT/UPDATE/DELETE must also maintain each index, and each index consumes storage. So you index the columns you search and join on, not everything.

• 1NF — atomic values, no lists in a cell. A phones column holding "555-1234, 555-9999" violates it; split into rows or a child table.
• 2NF — 1NF, plus no attribute depending on part of a composite key. In order_items(order_id, product_id, product_name…), product_name depends only on product_id → move it to products.
• 3NF — 2NF, plus no attribute depending on another non-key attribute. employees(…, dept_id, dept_name) — dept_name depends on dept_id, not on the employee → move it to departments.

The one-line purpose: store each fact once, so it can never disagree with itself.

Because the workload flips. OLTP systems write constantly and must avoid update anomalies → normalize. Warehouses are written once by a controlled pipeline and read thousands of times by humans → duplicate the data to eliminate joins, and manage the redundancy in the pipeline instead of the schema.

It's the same trade-off answered twice for two different workloads — framing it that way, rather than "normalization good/bad", is what earns the point.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK; to undo both

• Atomic — all statements succeed or none do.
• Consistent — constraints hold before and after.
• Isolated — concurrent transactions don't see each other's half-done work.
• Durable — once committed, it survives a crash.

The transfer example above is the answer to "why transactions": without atomicity, a crash between the two updates loses money.

server / account
 └── database        (e.g. ANALYTICS)
      └── schema     (e.g. SALES, HR — namespaces)
           └── table / view / function
-- fully qualified:  ANALYTICS.SALES.ORDERS

Schemas exist for organisation and permissioning — grant a team access to one schema, not table-by-table. Note the vocabulary clash: MySQL uses "database" and "schema" interchangeably, while Postgres/Snowflake/SQL Server keep them as distinct levels.

COALESCE(a, b, c, 0)   -- first non-null of the list (standard, use this)
IFNULL(a, 0)           -- MySQL two-argument version
NVL(a, 0)              -- Oracle's version
NULLIF(a, b)           -- returns NULL when a = b  (the reverse direction!)

NULLIF is the odd one out — it creates NULL. Its killer use: amount / NULLIF(qty, 0) turns divide-by-zero into NULL instead of an error.

CAST('123' AS INT)
CAST(amount AS DECIMAL(10,2))
'2026-07-31'::date               -- Postgres/Snowflake shorthand
TRY_CAST('abc' AS INT)           -- NULL instead of error (SQL Server/Snowflake)

Two rules: prefer explicit casts over relying on implicit conversion (implicit casts on a filtered column can disable index/partition use), and in pipelines prefer TRY_CAST so one malformed value nulls out instead of failing the whole load — then count the NULLs as a data-quality check.

SELECT * FROM t ORDER BY x LIMIT 10;                      -- MySQL/Postgres/Snowflake
SELECT TOP 10 * FROM t ORDER BY x;                        -- SQL Server
SELECT * FROM t ORDER BY x FETCH FIRST 10 ROWS ONLY;      -- standard/Oracle 12c+

Same idea, three spellings. The part that matters more than syntax: a row limiter without ORDER BY returns an arbitrary 10 rows — and a different 10 tomorrow.

SELECT email, COUNT(*) AS n
FROM   users
GROUP  BY email
HAVING COUNT(*) > 1
ORDER  BY n DESC;

The single most useful data-quality query in existence. Extend it to multi-column duplicates by grouping the full business key — and if this ever returns rows on a column you believed unique, you've found a real defect, not a curiosity.

-- 1) keep the lowest id (self-join delete)
DELETE FROM users u
WHERE  u.id NOT IN (SELECT MIN(id) FROM users GROUP BY email);

-- 2) window-function way (engines supporting DELETE from CTE)
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn
  FROM users)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

In analytical warehouses the safer pattern is create-clean-and-swap: build a deduplicated copy, verify counts, then swap tables — reversible until the final step.

-- max below the max
SELECT MAX(salary) FROM employees
WHERE  salary < (SELECT MAX(salary) FROM employees);

-- or: OFFSET
SELECT DISTINCT salary FROM employees
ORDER  BY salary DESC LIMIT 1 OFFSET 1;

Asked constantly because it tests thinking, not memory. Know both, plus the window version (in Intermediate) — and mention the edge case: with only one distinct salary, the first form returns NULL, which is usually the right answer.

CREATE TABLE emp_backup AS SELECT * FROM emp;        -- structure + data (CTAS)
CREATE TABLE emp_empty  AS SELECT * FROM emp WHERE 1=0;  -- structure only
INSERT INTO existing_tbl SELECT * FROM emp;          -- data into an existing table

Know the limitation: CTAS typically copies columns and types but not indexes, constraints or defaults. A "backup" made this way silently lacks the primary key. (SQL Server's spelling is SELECT * INTO emp_backup FROM emp.)

Rule of thumb: start with a CTE; graduate to a temp table when you reuse the result or it's expensive; promote to a view when other people need it.

The most practically important LEFT JOIN fact: a WHERE condition on the right table turns a LEFT JOIN back into an INNER JOIN.

-- WRONG: unmatched customers vanish (their o.status is NULL, NULL<>'x' fails)
SELECT c.*, o.total FROM customers c
LEFT JOIN orders o ON o.cust_id = c.id
WHERE o.status = 'complete';

-- RIGHT: keep the condition in the ON clause
SELECT c.*, o.total FROM customers c
LEFT JOIN orders o ON o.cust_id = c.id AND o.status = 'complete';

ON decides what matches; WHERE decides which result rows survive. Interviewers use this exact bug as a screening question.

Recap of the pieces you now know, assembled: LIKE 'abc%' can use an index (it's a range scan on the prefix); LIKE '%abc' or '%abc%' cannot — full scan. Options when you genuinely need contains-search at scale:

• A full-text index (MySQL FULLTEXT, Postgres tsvector/GIN, SQL Server FTS).
• A reversed column for suffix searches (LIKE 'cba%' on the reversed value).
• In warehouses: Snowflake's Search Optimization Service for point lookups.

Closing the Basic level with the theme that runs through everything above: the engine can only be fast when your predicate lets it skip data.

A cursor iterates over a query's rows one at a time so you can process each individually — like a for-loop over a result set. Declare, open, fetch in a loop, close.

The catch: SQL engines are optimised for set-based operations. A cursor updating 100k rows one-by-one can be hundreds of times slower than one UPDATE statement doing the same thing. The interview answer that lands: "I know how cursors work, and I treat them as a last resort — almost every cursor can be rewritten as a set-based statement or a window function."

An attack where malicious SQL is smuggled in through user input. If code builds a query by gluing strings, input like ' OR '1'='1 changes the query's meaning — the classic login bypass; '; DROP TABLE users;-- is the destructive version.

Prevention, in order of importance:

• Parameterised queries / prepared statements — the input is bound as data, never parsed as SQL. This alone defeats injection.
• Least-privilege accounts — the app's DB user shouldn't be able to DROP anything.
• Input validation — defence in depth, not the primary defence.

-- never:  "SELECT * FROM users WHERE name = '" + input + "'"
-- always: SELECT * FROM users WHERE name = ?   -- input bound as parameter

-- odd ids
SELECT * FROM employees WHERE id % 2 = 1;   -- MySQL/Postgres (MOD(id,2) in Oracle)
-- even ids
SELECT * FROM employees WHERE id % 2 = 0;

The id-based version assumes ids are dense. The version that always works — alternate by position, not id:

SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM employees e
) t WHERE rn % 2 = 1;

The cleanest framing: UNION stacks rows vertically; JOIN attaches columns horizontally.

• UNION — two result sets with the same columns, appended: January orders + February orders → one longer table.
• JOIN — two tables related by a key, matched: orders + customers → wider rows with data from both.

Choose by the question: "more of the same shape" → UNION; "enrich rows with related data" → JOIN.

Mirror images: LEFT keeps every row of the left table (NULL-padding unmatched right side); RIGHT keeps every row of the right table. Every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping table order:

SELECT * FROM a RIGHT JOIN b ON a.id = b.aid
-- identical to
SELECT * FROM b LEFT  JOIN a ON a.id = b.aid

Convention worth stating: most teams standardise on LEFT JOIN only — the "keep everything" table always goes first, and queries stay readable. RIGHT JOIN in review usually gets rewritten.

• Entity integrity — every row is uniquely identifiable: primary keys, no duplicate rows.
• Referential integrity — every foreign key points at a real parent row: no orphaned orders for deleted customers.
• Domain integrity — every value is legal for its column: right type, right range (CHECK (salary > 0), NOT NULL, DEFAULT).

Tie it together in one sentence: constraints are how the schema enforces integrity automatically, so correctness doesn't depend on every application remembering the rules.

A data warehouse is a central repository that consolidates data from many source systems for analysis and reporting. The contrast that structures the answer:

Snowflake, BigQuery and Redshift are warehouses; the full topic has its own course in the Engineering track.

SELECT CURRENT_DATE;                          -- today
SELECT MONTH(order_date) FROM orders;         -- MySQL; EXTRACT(MONTH FROM …) standard
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Small but asked constantly. Two notes that add polish: standard SQL's portable forms are EXTRACT(MONTH FROM d) and the || concat operator; and in strict engines CONCAT with a NULL argument returns NULL — wrap with COALESCE(middle_name,'') when fields are optional.

A precompiled group of SQL statements saved in the database and run with a single call.

• Accepts parameters and can contain logic (conditions, loops).
• Encapsulates repetitive or complex tasks in one place.
• Gives applications one safe entry point instead of raw table access.

A set of instructions that runs automatically in response to a table event.

• Fires on INSERT, UPDATE or DELETE, before or after the change.
• Common use: audit logging that no application can forget to write.
• NEW holds the inserted row; OLD holds prior values.

A database object that iterates over query results one row at a time.

• Allows row-by-row processing when set-based SQL isn't enough.
• Slow versus set-based operations — use as a last resort.
• Most cursors can be rewritten as a single statement or a window function.

A table that exists only for a session or transaction, then is dropped automatically.

• Stages intermediate results you don't need to keep.
• Can carry indexes and statistics (unlike a table variable).
• Lives in temporary space such as tempdb.

Returns the first non-NULL value from a list of expressions.

• Substitutes a default for missing values.
• Example: COALESCE(phone, mobile, 'N/A').
• Standard SQL — works across all major databases.

A subquery that references the outer query, so it depends on each outer row.

• Runs conceptually once per outer row.
• Example: employees earning above their own department's average.
• Often rewritable as a JOIN or window function for speed.

A join where a table is joined to itself using two aliases.

• Compares rows within the same table.
• Classic use: employee → manager in one employees table.
• Written by aliasing the table twice (e.g. e1, e2).

Two string types differing in how they use space.

• CHAR(n) — fixed length; pads short values with spaces.
• VARCHAR(n) — variable length; stores only characters used.
• CHAR for fixed-width codes, VARCHAR for names and free text.

They combine tables in opposite directions.

• UNION — stacks rows vertically (same columns).
• JOIN — attaches columns horizontally (matched by key).
• "More of the same" → UNION; "enrich rows" → JOIN.

Synchronises a target table from a source in one statement.

• Updates rows that match, inserts rows that are new.
• Optionally deletes rows no longer present in the source.
• The source must be unique on the join key, or it errors.

All remove data, at different levels.

• DELETE — removes selected rows; rollback-able; keeps the table.
• TRUNCATE — removes all rows fast; keeps the table structure.
• DROP — removes the entire table, structure included.

A temporary, readable name given to a column or table in a query.

• Column alias: SELECT price AS unit_price.
• Table alias: FROM employees e — shortens references.
• Exists only for the duration of the query.

SQL commands fall into four families.

• DDL — structure: CREATE, ALTER, DROP.
• DML — data: SELECT, INSERT, UPDATE, DELETE.
• DCL — permissions: GRANT, REVOKE.
• TCL — transactions: COMMIT, ROLLBACK.

Both are built-in functions, but operate on different inputs.

• Aggregate — works over many rows, returns one value: SUM, AVG, COUNT.
• Scalar — works on a single value per row: UPPER, ROUND, LEN.
• Aggregates usually pair with GROUP BY; scalars don't.

A primary key made of two or more columns whose combination is unique.

• No single column is unique on its own.
• Common in junction tables (e.g. order_id + product_id).
• Identifies exactly one row by the combination.

A rule ensuring relationships between tables stay valid.

• A foreign key must point to an existing primary key value.
• Prevents "orphan" rows (e.g. an order for a deleted customer).
• Enforced automatically by foreign key constraints.

Both test membership, but work differently.

• IN — compares a value against a list or subquery result.
• EXISTS — checks whether a subquery returns any row; stops at the first match.
• EXISTS is usually safer with NULLs than NOT IN.

They operate at different levels.

• DELETE — removes rows; the table and its structure remain.
• DROP — removes the whole table, including its structure.
• DELETE can be rolled back; DROP generally cannot.

Any column (or set) that could serve as the primary key.

• Uniquely identifies rows and contains no NULLs.
• A table can have several candidate keys.
• One is chosen as the primary key; the rest are "alternate keys".

Sorts the result set by one or more columns.

• ASC (default) ascending, DESC descending.
• Can sort by multiple columns and by a column alias.
• Runs last, so it can use aliases defined in SELECT.

They filter at different stages.

• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.
• Only HAVING can use aggregates like COUNT().

Groups rows sharing a value so aggregates can be applied per group.

• One output row per distinct combination of grouped columns.
• Every selected column must be grouped or aggregated.

Special characters used with LIKE for pattern matching.

• % — any number of characters.
• _ — exactly one character.

Selects values within a range, inclusive of both ends.

SELECT * FROM products WHERE price BETWEEN 10 AND 50;

Tests whether a value matches any value in a list or subquery.

SELECT * FROM orders WHERE status IN ('open','paid');

• NULL — unknown/absent value.
• 0 — a known numeric value.
• '' — a known empty text value.

• Numeric: INT, DECIMAL, FLOAT.
• String: CHAR, VARCHAR, TEXT.
• Date/time: DATE, TIMESTAMP.
• Boolean where supported.

Restricts the number of rows returned.

SELECT * FROM orders ORDER BY order_date DESC LIMIT 5;

SQL Server uses TOP; Oracle uses FETCH FIRST.

Case-conversion scalar functions.

• UPPER(s) — converts to uppercase.
• LOWER(s) — converts to lowercase.
• Used to normalise text before comparing.

Supplies a value automatically when none is provided on insert.

status VARCHAR(10) DEFAULT 'active'

Ensures values in a column satisfy a condition.

salary DECIMAL CHECK (salary > 0)

• Inner query — the subquery that runs to supply a value/set.
• Outer query — the main query that uses the subquery's result.