Oracle SQL case studies
Oracle SQL · Put your knowledge to work in context.
The Oracle Database SQL Certified Associate credential - commonly referenced by its exam code, 1Z0-071 - is an entry-level certification that validates a candidate's ability to write and reason about SQL against an Oracle Database. It is aimed at developers, junior DBAs, data analysts, report writers, and students who need to prove they can retrieve, filter, transform, aggregate, and modify data using Oracle's SQL dialect rather than a generic one. The Oracle SQL case studies are 5 cases and about 2,857 words.
1Commission list: operator precedence, null ordering, and LIKE wildcards
The marketing team needs a list of commission-eligible sales staff. EMPLOYEES holds 107 rows; DEPARTMENT ID is null for unassigned staff and COMMISSION PCT is null for 72 rows.
508 words, about 3 min2Staged text data: ORA-01722, string sorting, and implicit date conversion
ORDER STAGE holds 4,812 rows loaded from a flat file: ORDER ID NUMBER, AMOUNT VARCHAR2(20) with values like '1250.00', ' 980 ', '$2,340.50', and 'N/A' for 37 unposted rows; ORDER DT VARCHAR2(10) holds values like '2024-03-07'. The report query SELECT order id, amount, NVL(amount, 0) AS amt2 FROM order stage WHERE amount 500 ORDER BY amount; fails with ORA-01722: invalid number.
587 words, about 3 min3Aggregates: WHERE vs HAVING, what COUNT and AVG skip, and comparing to a grand total
SALES holds 9,000 rows: SALE ID, REGION, AMOUNT NUMBER (null for 1,200 unposted rows), DISCOUNT NUMBER (null for 6,000 rows, where null means 'no discount given'). A developer writes: SELECT region, COUNT(amount), SUM(amount), AVG(discount) FROM sales WHERE COUNT( ) 100 GROUP BY region HAVING amount 0; It fails.
530 words, about 3 min4Outer join that silently became an inner join, and a total inflated by a fan-out
DEPARTMENTS has 27 rows; EMPLOYEES has 107, one of which has a null DEPARTMENT ID, and department 120 has no employees at all. Audit needs every department - including empty ones - with headcount and total salary for staff earning more than 5,000: SELECT d.department name, COUNT(e.employee id) AS headcount, SUM(e.salary) AS total FROM departments d LEFT JOIN employees e ON d.department id = e.department id WHERE e.salary 5000 GROUP BY d.department name; It returns 12 rows.
623 words, about 3 min5Transaction control: savepoints, DDL commits, and a blocked session
A nightly batch runs: UPDATE orders SET status = 'SHIPPED' WHERE order id = 1001; SAVEPOINT s1; DELETE FROM order lines WHERE order id = 1001; UPDATE orders SET status = 'CLOSED' WHERE order id = 1001; ROLLBACK TO s1; TRUNCATE TABLE order lines; COMMIT; The developer expected the delete to be undone but status to remain 'CLOSED', and expected to be able to ROLLBACK the truncate if the job failed. While the batch is mid-flight, a second session running SELECT status FROM orders WHERE order id = 1001 still sees the original value; when that session tries to update the same row, it hangs.
609 words, about 3 min