Question bank — Relational model — tables, rows, columns, NULL

NULL = NULL evaluates to TRUE. False — it evaluates to UNKNOWN. Both sides are "no clue," and you cannot prove two unknown values are equal, so the honest verdict is "can't tell," not TRUE. ::: False — it is UNKNOWN, because two unknown values can't be proven equal.

0 are interchangeable in a query. No. 0 is a known number (the person is zero years old); NULL is the absence of a known age. age > 5 is FALSE for age 0 but UNKNOWN for NULL, so the two behave completely differently in a WHERE clause. ::: False — 0 is a known value that compares normally; NULL yields UNKNOWN in comparisons.

False — degree counts columns (the shape of the table), which is fixed by the schema. Inserting rows changes the cardinality (row count), never the degree. ::: False — degree = number of columns, fixed by schema; inserting rows changes cardinality.

False — a relation is a set, and sets have no order. There is no "first row." Any order you see in output is incidental and not part of the data. ::: False — a relation is a set; order is not part of the data.

SELECT * FROM Student WHERE age = 20 OR age <> 20; returns every student. False — a student with NULL age gives UNKNOWN for both comparisons, and UNKNOWN OR UNKNOWN is UNKNOWN, so WHERE drops that row. The two branches only cover known ages. ::: False — rows with NULL age evaluate to UNKNOWN and are silently excluded.

COUNT(major) and COUNT(*) always return the same number. False. COUNT(*) counts all rows; COUNT(major) counts only rows where major is not NULL. They differ exactly when some major values are NULL. ::: False — COUNT(column) skips NULLs while COUNT(*) counts every row.

False — real tables are multisets (bags) and allow duplicate rows unless you add a PRIMARY KEY or UNIQUE constraint. The no-duplicates rule is a property of pure relations, not of raw SQL tables. ::: False — SQL tables allow duplicates unless a key constraint forbids them.

FALSE AND NULL is UNKNOWN because a NULL is involved. False — it is FALSE. AND is only TRUE when both sides are TRUE; one FALSE side already forces the whole thing FALSE no matter what the unknown turns out to be. ::: False — it is FALSE; one FALSE branch settles AND regardless of the unknown.

WHERE major = NULL." The bug: = NULL compares against an unknown and returns UNKNOWN for every row, so nothing is kept. Use WHERE major IS NULL, which returns real TRUE/FALSE. ::: = NULL always yields UNKNOWN; use IS NULL instead.

Swapped the two counts. Degree = number of columns; the number of rows is cardinality. ::: Confuses the terms — rows are cardinality; degree counts columns.

NULL + 5 returns 5, since the missing value acts like 0." Wrong — any arithmetic with NULL produces NULL. If a value is unknown, the sum is unknown too; NULL does not silently become 0. ::: NULL + 5 is NULL; unknown input makes the result unknown, not 0.

ORDER BY." Order is not part of a relation, so without ORDER BY the system may return rows in any order — insertion order is a coincidence, not a guarantee. ::: Row order is not guaranteed; you must add ORDER BY when order matters.

WHERE NOT (age > 21) gives me everyone with age <= 21, including the NULLs." The NULLs are excluded. NULL > 21 is UNKNOWN, and NOT UNKNOWN is still UNKNOWN, which WHERE drops. Negation never rescues a NULL. ::: NOT of UNKNOWN is UNKNOWN, so NULL-age rows are still dropped.

age can store text like 'twenty' whenever I want." No — a column is bound to a domain / data type, so an INTEGER age column only accepts integers (see Data Types and Domains). The domain is a promise about what may go in the slot. ::: A column is restricted to its declared domain; text can't go in an INTEGER column.

Because NULL means "unknown," and a claim like "is this unknown age > 21?" cannot honestly be TRUE or FALSE. UNKNOWN is the truthful third answer, giving three-valued logic. ::: Comparisons involving unknown values can't be TRUE or FALSE, so UNKNOWN is required.

WHERE's job is to keep rows the condition proves it wants. UNKNOWN is not a proof of TRUE, so those rows are excluded — "if in doubt, leave it out." ::: Only conditions proven TRUE keep a row; UNKNOWN is not a proof, so the row is dropped.

So you could query by what you want, not how to fetch it. Programs no longer break when storage is reorganised — this is the root of SQL being declarative. ::: To make queries independent of physical layout, enabling declarative access (see SQL SELECT and WHERE).

set of tuples rather than a list? A set gives two guarantees for free: no meaningful order and (in pure theory) no duplicate rows. Both flow from the mathematical set definition, not from any storage trick. ::: A set has no order and no duplicates by definition — those properties come from the math.

Because raw SQL tables are multisets and won't stop you. You need a PRIMARY KEY or UNIQUE constraint to have the system reject duplicates for you (see Primary Keys and Uniqueness). ::: Tables allow duplicates by default; only a key constraint enforces uniqueness.

COALESCE(NULL, NULL, 7) return, and why? It returns 7. COALESCE walks left to right and returns the first non-NULL argument; it skips the two NULLs and lands on 7 (see COALESCE and NULL handling functions). ::: 7 — COALESCE returns the first non-NULL argument, scanning left to right.

COALESCE(NULL, NULL) return when every argument is NULL? It returns NULL — with no non-NULL argument to find, there is nothing to substitute, so the "no clue" survives. ::: NULL — with no non-NULL argument, there is nothing to return but NULL.

Grouping treats NULLs as matching each other so they fall in the same group — even though NULL = NULL is UNKNOWN in a comparison. Grouping/DISTINCT use "not distinct" semantics, not ordinary =. ::: They group together — grouping uses not-distinct semantics, unlike the = operator.

Degree stays whatever the schema declares (columns still exist); cardinality is 0. An empty table still has a shape. ::: Degree = its column count (unchanged); cardinality = 0.

TRUE OR NULL — does the unknown matter? No. OR is TRUE if either side is TRUE, and the left side is already TRUE, so the result is TRUE regardless of the unknown. ::: TRUE — one TRUE branch settles OR no matter what the unknown is.

IS NULL itself ever return UNKNOWN? No — IS NULL and IS NOT NULL always return a real TRUE or FALSE. That is exactly why they are the correct tools for NULL, unlike =. ::: No — IS NULL always returns real TRUE/FALSE, which is why it is the safe NULL test.