Quiz · Medium
Mixed questions on keys, NULL logic, joins, grouping, evaluation order and SQL safety.
Which statement about a relation in the relational model is correct?
Its rows have a fixed physical order
Its columns have a fixed order and may repeat
It is a set of tuples sharing the same attributes
It must contain at least one NULL
A relation is a set of tuples over the same attributes; neither tuple order nor duplicate tuples carry meaning.
Which expression returns rows where salary is unknown?
WHERE salary = NULL
WHERE salary IS NULL
WHERE salary == 0
WHERE NOT salary
Comparisons with NULL yield UNKNOWN, so equality never matches. IS NULL is the correct test.
A table has 100 rows. 10 have bonus NULL. What does SELECT COUNT(bonus) FROM t return?
100
90
10
NULL
COUNT(column) ignores NULLs, so only the 90 non-NULL values are counted. COUNT(*) would return 100.
Which query returns only departments with more than 5 employees?
SELECT dept FROM e WHERE COUNT(*) > 5 GROUP BY dept
SELECT dept FROM e GROUP BY dept HAVING COUNT(*) > 5
SELECT dept FROM e GROUP BY dept WHERE COUNT(*) > 5
SELECT dept FROM e HAVING dept > 5
Aggregates can be filtered only after grouping, which is what HAVING is for. WHERE cannot contain aggregate functions.
The shaded region in the diagram corresponds to which join of A with B?

INNER JOIN
LEFT JOIN
RIGHT JOIN
CROSS JOIN
Only the overlap, the rows that match in both tables, is returned by an inner join.
The shaded region in the diagram (all of A and the overlap) is produced by which join?

INNER JOIN
LEFT OUTER JOIN of A with B
FULL OUTER JOIN
A EXCEPT B
A left outer join keeps every row from the left table and fills B's columns with NULL where there is no match.
Which clause is evaluated first in the logical processing of a SELECT statement?
WHERE
SELECT
FROM
ORDER BY
FROM (and joins) comes first, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
Why does SELECT price * qty AS total FROM t WHERE total > 100 fail in standard SQL?
Aliases cannot contain spaces
WHERE is evaluated before SELECT, so the alias does not exist yet
total is a reserved word
Multiplication is not allowed in SELECT
WHERE runs before the SELECT list is computed, so the alias is not visible. Repeat the expression or use a subquery or CTE.
What does SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM b) return if b.a_id contains a NULL?
All rows of a that are not in b
No rows
Only rows where id is NULL
An error
NOT IN with a NULL in the list never evaluates to TRUE (it is UNKNOWN), so no rows qualify. NOT EXISTS avoids the problem.
Scores are 95, 95, 90. Which function assigns ranks 1, 1, 2?
ROW_NUMBER
RANK
DENSE_RANK
NTILE
DENSE_RANK leaves no gaps after ties. RANK would give 1, 1, 3 and ROW_NUMBER 1, 2, 3.
Which is the safest defence against SQL injection?
Escape quotes by hand
Hide the table names
Use parameterized queries (prepared statements)
Run the database on a non-default port
Parameters keep user input as data, never as SQL text, so it cannot change the query structure.
What is the main difference between UNION and UNION ALL?
UNION ALL removes duplicates
UNION removes duplicate rows, UNION ALL keeps them
UNION works on one table only
They are identical
UNION needs extra work to remove duplicates, so UNION ALL is faster when duplicates are acceptable or impossible.
Which statement removes all rows from a table but keeps the table definition?
DROP TABLE t
TRUNCATE TABLE t
ALTER TABLE t
REMOVE t
TRUNCATE empties the table quickly; DROP deletes the table itself.
A foreign key column should normally:
Be unique in the child table
Match an existing key in the parent table or be NULL
Always be the primary key of the child
Be indexed only in the parent table
That is referential integrity. Uniqueness in the child is only needed for one-to-one relationships.
How is a many-to-many relationship between students and courses implemented?
A foreign key in students pointing to courses
A comma-separated list of course ids in each student row
A junction table with foreign keys to both
Two separate databases
A junction table (for example enrollment) breaks M:N into two one-to-many relationships and keeps the data atomic.
Why should money columns use DECIMAL rather than FLOAT?
DECIMAL uses less storage
FLOAT cannot store numbers above 1000
FLOAT is binary and cannot represent many decimal fractions exactly
DECIMAL is faster for division
For example 0.1 has no exact binary representation, so rounding errors accumulate in FLOAT arithmetic.
Which statement correctly describes CHAR(10) and VARCHAR(10)?
CHAR pads values to 10 characters; VARCHAR stores only what is used
They are the same in all systems
VARCHAR is always padded
CHAR cannot be indexed
CHAR is fixed length with padding; VARCHAR is variable length up to the limit.
Which query finds customers who have never placed an order?
SELECT c.* FROM customers c INNER JOIN orders o ON o.cid = c.id
SELECT c.* FROM customers c LEFT JOIN orders o ON o.cid = c.id WHERE o.cid IS NULL
SELECT c.* FROM customers c CROSS JOIN orders o
SELECT c.* FROM customers c GROUP BY c.id HAVING o.cid IS NULL
A left join keeps customers without matches, and the IS NULL test on the right-hand key isolates them (an anti-join).
What happens with ON DELETE CASCADE when a parent row is deleted?
The delete is refused
Child rows referencing it are deleted too
Child foreign keys are set to 0
Nothing changes
CASCADE propagates the delete to dependent rows. RESTRICT would refuse it and SET NULL would clear the child keys.
In a relational table, an attribute is:
A row
A column
The whole table
A constraint
Attribute = column, tuple = row, relation = table.