Relational Database Fundamentals and SQL SELECT Basics
Domain 1 tests relational vocabulary and the logical order of a SELECT statement. You must distinguish tables, rows, columns, primary keys, foreign keys, and NULL, and know how Oracle evaluates a query even when the SELECT list is written first.
Core rules
- A relational table stores rows in no guaranteed order; ORDER BY is the only way to guarantee result order.
- Oracle SQL is not case-sensitive for keywords and unquoted identifiers, but string literals are case-sensitive. Unquoted names are stored uppercase; quoted names keep exact case.
- NULL means unknown or absent. It is not zero or a blank string for comparisons; test it with IS NULL and IS NOT NULL.
- Logical clause order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Column aliases defined in SELECT are available to ORDER BY, not to WHERE.
- DUAL is a one-row, one-column Oracle table used for expression tests such as SELECT 1+1 FROM DUAL.
Worked example
SELECT department_id, COUNT(*) AS emp_count FROM employees WHERE salary > 5000 GROUP BY department_id HAVING COUNT(*) > 2 ORDER BY emp_count DESC;
FROM reads employees. WHERE removes rows with salary at or below 5000. GROUP BY forms department groups. HAVING keeps groups with more than two remaining rows. SELECT projects department_id and the count. ORDER BY sorts by the alias emp_count. The alias works in ORDER BY because it is evaluated after SELECT.
Traps
Putting a group function in WHERE fails; use HAVING. Writing salary = NULL returns unknown, never true. Expecting SELECT-list aliases in WHERE fails. Assuming row order without ORDER BY is unsafe. Confusing COUNT(*) with COUNT(column) matters because COUNT(column) ignores NULLs.
Key points
- Know clause evaluation order.
- Use IS NULL, never = NULL.
- Aliases work in ORDER BY, not WHERE.
- DUAL supplies a dummy row.
- Unquoted identifiers are case-insensitive.