SQL & Databases Relational Database Fundamentals Syllabus

What referential actions can be specified for ON DELETE / ON UPDATE of a foreign key?

CASCADE (propagate the change), SET NULL (set FK to NULL), SET DEFAULT (set FK to its default), RESTRICT / NO ACTION (reject the operation if references exist).

List the main types of constraints in a relational database.

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY (referential), CHECK (domain/value condition), and DEFAULT (supplies a value when none given). PRIMARY KEY = UNIQUE + NOT NULL.

What is the difference between a UNIQUE constraint and a PRIMARY KEY constraint?

Both enforce uniqueness, but a table may have many UNIQUE constraints and only one PRIMARY KEY. UNIQUE columns may allow NULLs (typically one or many depending on the DBMS), whereas a PRIMARY KEY forbids NULLs.

What does a CHECK constraint do?

It enforces a Boolean condition that every row must satisfy (e.g., CHECK (salary > 0)). Rows violating the predicate are rejected. It is used to enforce domain/business rules at the column or table level.

Define entity integrity.

The rule that no attribute of a primary key may be NULL. This guarantees each tuple is uniquely identifiable, since a NULL primary key value could not reliably distinguish rows.

Define domain integrity.

The requirement that every attribute value belongs to its declared domain — the correct data type, format, and range — typically enforced by data types, NOT NULL, CHECK, and DEFAULT constraints.

What are the standard SQL exact-numeric and approximate-numeric types?

Exact: INTEGER (and SMALLINT, BIGINT), NUMERIC/DECIMAL(p,s) with fixed precision $p$ and scale $s$. Approximate (floating-point): REAL, FLOAT, DOUBLE PRECISION, which store binary approximations.

In DECIMAL(p, s), what do p and s mean?

$p$ (precision) is the total number of significant digits; $s$ (scale) is the number of digits to the right of the decimal point. For example DECIMAL(7,2) stores values up to $\pm 99999.99$.

Why should money be stored in DECIMAL/NUMERIC rather than FLOAT?

Floating-point types store binary approximations and cannot represent many decimal fractions exactly (e.g., $0.1$), causing rounding errors. DECIMAL/NUMERIC is exact, which is required for financial calculations.

What is the difference between CHAR(n) and VARCHAR(n)?

CHAR(n) is fixed-length: values are right-padded with spaces to exactly $n$ characters. VARCHAR(n) is variable-length: it stores only the actual characters up to a maximum of $n$, using less space for short values.

What type stores large character data, and how does the BOOLEAN type behave in SQL?

TEXT / CLOB stores large/unbounded character data. BOOLEAN holds the logical values TRUE and FALSE — and, because of SQL's three-valued logic, also UNKNOWN (represented by NULL).

What are the standard SQL date/time types?

DATE (calendar date), TIME (time of day), TIMESTAMP (date + time), and interval types (INTERVAL). Variants WITH TIME ZONE (TIMESTAMPTZ) store zone/offset information.

What is the difference between TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE?

WITHOUT TIME ZONE stores the literal date/time with no zone context. WITH TIME ZONE (TIMESTAMPTZ) normalizes the value to UTC on storage and converts to the session's time zone on retrieval, making it unambiguous across zones.

What is an INTERVAL type used for?

An INTERVAL represents a span/duration of time (e.g., '2 days 3 hours') rather than a point in time. Intervals can be added to or subtracted from dates and timestamps.

Name several specialized SQL data types beyond numbers, strings, and dates.

BLOB/BYTEA (binary data), JSON/JSONB (semi-structured documents), XML, UUID (128-bit unique identifiers), ARRAY, ENUM, and spatial/GEOMETRY types. PostgreSQL notably supports many of these natively.

What is the difference between JSON and JSONB in PostgreSQL?

JSON stores an exact textual copy (preserving whitespace/key order, reparsed each access). JSONB stores a decomposed binary form: faster to process and indexable (GIN), but does not preserve key order or duplicate keys.

What is a UUID and why is it used?

A UUID (Universally Unique Identifier) is a 128-bit value, often shown as 32 hex digits in 5 groups (8-4-4-4-12). It provides globally unique keys generated without central coordination, useful for distributed systems.

What does NULL represent in SQL?

NULL is a marker for the absence of a value — meaning 'unknown,' 'not applicable,' or 'missing.' It is NOT the same as zero, an empty string, or FALSE.

What is three-valued logic (3VL) in SQL?

SQL Boolean expressions can evaluate to TRUE, FALSE, or UNKNOWN. Any comparison involving NULL yields UNKNOWN, so predicates that are not definitely TRUE do not qualify rows in WHERE clauses.

Why does 'column = NULL' never return rows, and what should be used instead?

Comparing anything to NULL with = yields UNKNOWN (not TRUE), so no rows qualify. Use the predicates IS NULL and IS NOT NULL to test for NULLs.

How does NULL behave in arithmetic and aggregate functions?

Arithmetic with NULL yields NULL (e.g., $5 + \text{NULL} = \text{NULL}$). Aggregate functions like SUM, AVG, MIN, MAX ignore NULLs, but COUNT(*) counts all rows while COUNT(column) ignores NULLs.

Evaluate TRUE OR UNKNOWN and TRUE AND UNKNOWN in SQL three-valued logic.

TRUE OR UNKNOWN = TRUE (because OR is true if either operand is true). TRUE AND UNKNOWN = UNKNOWN (AND cannot be confirmed true while the other operand is unknown). FALSE AND UNKNOWN = FALSE.

What is DDL and which statements belong to it?

Data Definition Language defines and modifies schema objects. Statements: CREATE, ALTER, DROP, TRUNCATE, and RENAME. They act on structures like tables, indexes, views, and schemas.

What does TRUNCATE do and how does it differ from DELETE?

TRUNCATE quickly removes all rows from a table. It is DDL (typically auto-committed, minimal logging, resets identity, cannot use WHERE), whereas DELETE is DML that removes rows one-by-one, can be filtered with WHERE, and is transaction-controllable/rollbackable.

What is DML and which statements belong to it?

Data Manipulation Language changes the data within tables. Core statements: INSERT (add rows), UPDATE (modify rows), DELETE (remove rows), and MERGE (upsert). Some classifications include SELECT here as well.

What is DQL and its single statement?

Data Query Language is used to retrieve data; its statement is SELECT (with clauses like FROM, WHERE, GROUP BY, HAVING, ORDER BY). It is often grouped under DML in some taxonomies.

State the logical order of evaluation of the main SELECT clauses.

FROM (and JOINs) → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET. Note this differs from the written order, which starts with SELECT.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can reference aggregates (e.g., HAVING COUNT(*) > 5).

What is DCL and which statements belong to it?

Data Control Language manages privileges/permissions. Statements: GRANT (give privileges to users/roles) and REVOKE (remove them).

What is TCL and which statements belong to it?

Transaction Control Language manages transactions. Statements: COMMIT (make changes permanent), ROLLBACK (undo to a consistent state), SAVEPOINT (set a rollback marker), and SET TRANSACTION (configure properties).

What are the ACID properties of a database transaction?

Atomicity (all-or-nothing), Consistency (moves DB from one valid state to another respecting constraints), Isolation (concurrent transactions don't interfere), and Durability (committed changes survive failures).