HP JBT: Database Concepts and SQL study guide

What this domain tests

Database concepts and SQL tests the relational model, keys, normalization, transactions, indexing, and your ability to write and debug queries.

Core rules

A primary key uniquely identifies a row and cannot be NULL; a foreign key references a candidate key and enforces referential integrity.
Normalization reduces redundancy: 1NF atomic values, 2NF no partial dependency on part of a composite key, 3NF no transitive dependency.
ACID means atomicity, consistency, isolation, and durability.
INNER JOIN returns matching rows; LEFT JOIN keeps all left rows and fills NULLs where there is no match.
WHERE filters rows before grouping; HAVING filters groups after GROUP BY.
COUNT(*) counts rows; COUNT(column) ignores NULLs in that column.
An index speeds reads but adds write and storage overhead; a leading-column rule applies to composite indexes.

Worked example

Find departments with more than two employees: SELECT dept_id, COUNT(*) AS n FROM employee GROUP BY dept_id HAVING COUNT(*) > 2; The GROUP BY dept_id forms one group per department; COUNT(*) counts rows in each group; HAVING removes groups that do not meet the condition. Using WHERE COUNT(*) > 2 is invalid because row filtering happens before aggregation.

Traps

NULL is not equal to anything, including itself; use IS NULL, and remember NULL = NULL yields unknown, not true. NOT IN with a NULL in the list can return no rows. WHERE versus HAVING is a frequent error. COUNT(*) versus COUNT(col) changes results when NULLs exist. Joining without a condition produces a Cartesian product. Implicit conversions can silently prevent index use.