I85PREP
⌘ K
Databricks Platform Admin study materials
Databricks Platform Admin study resources

Databricks Platform Admin: SQL retrieval, joins and transaction semantics

Databricks Platform Admin · Work through your exam, one topic at a time.

All guide sections

SQL retrieval, joins and transaction semantics

What the domain covers

This domain tests changing data and retrieving it from one table: INSERT, UPDATE, DELETE, SELECT with WHERE and ORDER BY, DISTINCT, and aggregate functions with GROUP BY and HAVING. Exam items ask you to predict row counts, handle NULLs, and decide whether a condition belongs in WHERE or HAVING.

Core rules

  • INSERT adds rows; a column list is safer than positional values. UPDATE changes existing rows and should always have a WHERE clause unless all rows are intended. DELETE removes rows; TRUNCATE is not a substitute when you need filtering.
  • WHERE filters rows before grouping. GROUP BY collapses rows into groups. HAVING filters groups after aggregation. SELECT projects columns and aggregates; ORDER BY sorts the final result and can use aliases.
  • Aggregate functions: COUNT, SUM, AVG, MIN, MAX. COUNT(*) counts rows; COUNT(column) counts non-NULL values; SUM and AVG ignore NULLs. AVG(column) divides by the non-NULL count, not the total row count.
  • DISTINCT applies to the whole selected row, not to one column in isolation.
  • Transactions: COMMIT makes changes permanent; ROLLBACK undoes uncommitted work; SAVEPOINT allows partial rollback.

Worked example

SELECT department_id, COUNT() AS rows, AVG(salary) AS avg_salary FROM employees WHERE salary > 0 GROUP BY department_id HAVING COUNT() >= 5 ORDER BY avg_salary DESC;

Reasoning: WHERE removes non-positive salaries before grouping. GROUP BY forms one group per department_id, including a NULL group if present. COUNT(*) counts rows per group; AVG(salary) ignores NULL salaries. HAVING keeps only groups with at least five rows. ORDER BY sorts by the alias. If the intent were “average salary above 5000,” that condition would go in HAVING, not WHERE, because it depends on an aggregate.

Traps that cost marks

  • UPDATE or DELETE without WHERE, changing every row.
  • Putting aggregate conditions in WHERE instead of HAVING.
  • Assuming COUNT(column) includes NULLs.
  • Expecting DISTINCT to deduplicate one column while returning other columns.
  • Using ORDER BY in a view or subquery where the DBMS does not preserve it.

What the domain covers

This domain tests multi-table retrieval and schema objects that support it: inner, outer and self joins, subqueries, set operators, views, indexes and sequences. Exam items ask you to choose the join that preserves unmatched rows, predict duplicate behavior, and explain when an index or view helps.

Core rules

  • INNER JOIN returns matching pairs. LEFT JOIN keeps all left rows and NULL-extends unmatched right columns; RIGHT JOIN is the mirror; FULL JOIN keeps both sides. CROSS JOIN is a Cartesian product. A self join uses two aliases of one table.
  • Join conditions belong in ON. Filters on the preserved side belong in WHERE, but filters on the optional side usually belong in ON to avoid turning an outer join into an inner join.
  • Subqueries can be scalar (one value), row, table or correlated. EXISTS and NOT EXISTS test presence and handle NULLs more safely than IN and NOT IN.
  • UNION combines and removes duplicates; UNION ALL keeps duplicates and avoids UNION’s duplicate-elimination requirement; actual performance depends on the plan and workload. INTERSECT returns common rows; EXCEPT (MINUS in Oracle) returns rows in the first query not in the second.
  • A view is a stored query; it can simplify access and restrict columns, but it is not a copy of data. An index speeds reads but adds write overhead and storage. A sequence generates numeric values, commonly for surrogate keys.

Worked example

SELECT d.department_name, e.last_name FROM department d LEFT JOIN employee e ON e.department_id = d.department_id WHERE e.last_name IS NULL;

Reasoning: the LEFT JOIN keeps every department, even those with no employees. Unmatched employee columns become NULL. This is a reliable unmatched-row test only if the tested employee column is guaranteed nonnull for a matched row. Otherwise a matched employee with NULL last_name also survives; test a nonnull employee key or use NOT EXISTS. If the predicate e.last_name = 'Smith' were placed in WHERE, departments without a Smith would be removed, effectively behaving like an inner join. NOT EXISTS is an alternative that avoids NULL pitfalls.

Traps that cost marks

  • Using NOT IN against a subquery that can return NULL; the result may be empty. Prefer NOT EXISTS.
  • Assuming UNION ALL removes duplicates; it does not.
  • Placing a filter on the outer-joined table in WHERE and losing unmatched rows.
  • Expecting an index to help every query; low-selectivity columns and functions on indexed columns can prevent its use.
  • Treating a view as materialized data; an ordinary view defines query semantics rather than a separately stored result; execution and optimization need not literally rerun it row by row.