Context:FIT2094_MOC, FIT3003_MOC · the ONE pre-lab/pre-exam re-read — every clause, predicate and function the two units teach, in one place · depth lives in the linked pattern notes
Read protocol: scan anatomy → scan tables → attempt all three practice items from a blank editor → follow links only where you failed.
Quick Revision
🎯 Objective: assemble any query from the fixed skeleton ➔ SELECT → FROM(+JOIN) → WHERE → GROUP BY → HAVING → ORDER BY.
⚡ Key Constraint:logical execution order ≠ writing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — explains why aliases work in ORDER BY but not GROUP BY/WHERE.
⚠ Unit divergence: FIT2094 bans implicit joins; FIT3003 lectures and labs use them. Everything else is shared.
🧩 Statement Anatomy (execution order)
SELECT dt_code, AVG(drone_flight_time) AS avg_flight -- 5. project + aliasFROM drone.drone -- 1. source (+ JOINs)WHERE to_char(drone_pur_date,'yyyy') = '2021' -- 2. filter ROWS (pre-group)GROUP BY dt_code -- 3. form groups (no aliases here!)HAVING AVG(drone_flight_time) > 50 -- 4. filter GROUPS (may use aggregates)ORDER BY dt_code; -- 6. sort (aliases OK, NULLS LAST OK)
Five aggregates ➔ COUNT / SUM / AVG / MIN / MAX; COUNT(*) counts rows incl. NULLs, COUNT(col) non-null only, COUNT(DISTINCT col) distinct non-null only.
GROUP BY rule ➔ every non-aggregate SELECT column MUST appear in GROUP BY; column aliases are illegal in GROUP BY (not yet computed) — repeat the expression: GROUP BY to_char(ct_date_start,'yyyy').
WHERE vs HAVING ➔ WHERE filters rows before grouping; HAVING filters groups after, and may contain aggregates: HAVING COUNT(DISTINCT model) > 1.
Aggregate over a join ➔ join condition goes in WHERE/ON and runs first; group on the surviving columns.
all common columns match — ⚠ no common column ⇒ Cartesian product
self join
FROM emp e1 JOIN emp e2 ON e1.mgrno = e2.empno
recursive FK (employee→manager); distinct aliases required
outer join
LEFT / RIGHT / FULL OUTER JOIN … ON …
keep unmatched rows (INNER drops them)
implicit / old-style
FROM customer ct, carsales cs, car c WHERE ct.customerID = cs.customerID AND c.carID = cs.carID
banned in FIT2094 (marked wrong) · ✅ standard in FIT3003 — n tables need n−1 conditions
Missing join condition = PRODUCT. FIT3003 warns that a comma-list FROM with no matching WHERE condition returns the Cartesian product, exhausts your Oracle quota, and locks your account. Count your ANDs before running.
run on every source table before building anything — the prediction all later numbers are judged against
duplicate PK probe
select PK, count(*) from t group by PK having count(*) > 1;
the standard dirty-data detector; empty result = that key is clean
duplicate row probe
group by log_time, log_date, student_id, act having count(*) > 1
when there is no single-column PK, group on the full composite
orphan FK probe
select * from t1 where FK not in (select PK from t2);
anti-join; a NULL inside the subquery makes not in return nothing at all
formatted sample
select log_date, to_char(log_time,'HH24:MI'), student_id from t order by …;
order by puts duplicates adjacent so they are visible in a big table
de-dup at the join
create table tempfact2 as select distinct U.…, S.… from … where …;
kills join fan-out and true source duplicates in one clause; only removes rows identical across the projected columns
de-dup a copy
create table student_clean as select distinct * from dw.student;
when the defect survives projection; CTAS brings no PK/FK
contradiction check
select * from contract where starttime > endtime;
inconsistent-value type — two attributes disagreeing
domain check
select * from charter where char_distance < 0;
incorrect-value type — outside the legal range
null check
select * from major where major_name is null;
= NULL never matches; only IS NULL
simplest repair
delete from t where <<the same predicate>>;
always select first — the count is the evidence the repair worked
merge two role facts
select … sum(x) from ( select * from f1 union select * from f2 ) group by emp_num;
union alone leaves two rows per member; the outer group by is what merges — legal only for individually-owned measures
label vs key
group by F.CourseCode, D1.CourseName
keep the key in the grouping list; grouping on the description alone merges members that share it
✍️ Integration Practice
Practice 1 (FIT2094 Topic 8, Q5-style): full name (one column, space-separated) and contact number of customers who completed a training course longer than 4 hours, ordered by name.
Reference solution
SELECT c.cust_fname || ' ' || c.cust_lname AS fullname, c.cust_phoneFROM drone.customer c JOIN drone.cust_train ct ON c.cust_id = ct.cust_id JOIN drone.training t ON ct.train_code = t.train_codeWHERE t.train_duration > 4ORDER BY fullname;
Key moves:\|\| concat + two ANSI ONs + alias reused in ORDER BY (legal — runs after SELECT).
Practice 2 (FIT2094 Topic 9, Q10-style): drones cheaper than the average price of all DJI-manufactured drones; show id, type code, price, purchase YEAR, manufacturer name; order by id.
Reference solution
SELECT drone_id, dt_code, drone_pur_price, to_char(drone_pur_date,'yyyy') AS yearpurchased, manuf_nameFROM drone.drone NATURAL JOIN drone.drone_type NATURAL JOIN drone.manufacturerWHERE drone_pur_price < (SELECT AVG(drone_pur_price) FROM drone.drone NATURAL JOIN drone.drone_type NATURAL JOIN drone.manufacturer WHERE UPPER(manuf_name) = UPPER('DJI Da-Jiang Innovations'))ORDER BY drone_id;
Key moves: scalar subquery with its own join chain + TO_CHAR year extraction + UPPER case-blind match.
Practice 3 (FIT3003 Lab 1, Q28-style): import a lecturer-owned table into your account, then cost the database labs per week — lab duration × the tutor's hourly rate — using FIT3003's old-style join syntax. Databases = subject codes CSE21DB, CSE31DB, CSE41FDB.
Reference solution
CREATE TABLE LAB AS SELECT * FROM dtaniar.LAB; -- rows only; PK/FK not copiedSELECT SUM(l.duration * t.salaryperhour) AS weekly_costFROM lab l, tutor tWHERE l.tutorno = t.tutornoAND l.subjectcode IN ('CSE21DB','CSE31DB','CSE41FDB');
Key moves: cross-account CTAS to seed the schema + exactly one join condition for two tables + IN for the set membership + SUM over a computed expression.
⚠ Column names unverified ➔ only SALARYPERHOUR is named in the lab sheet; duration, tutorno, subjectcode are inferred — confirm against the lab E/R diagram with DESC LAB; before relying on them.
Practice 4 (FIT3003 Lab 3-style): dw.uselog (108267 rows) joins 1–m to dw.student (37951 rows), yet the staged TempFact holds 170610. Diagnose it, clean it, and rebuild the fact banded by lab-time period.
Reference solution
select student_id, count(*) from dw.student -- 1. diagnosegroup by student_id having count(*) > 1; -- 14288 duplicated idscreate table tempfact_uselog2 as -- 2. clean at the joinselect distinct U.log_date, U.log_time, U.student_ID, S.class_id, S.major_codefrom dw.uselog U, dw.student Swhere U.student_id = S.student_id; -- 108261 rowsalter table tempfact_uselog2 add (timeid number); -- 3. band the derived keyupdate tempfact_uselog2 set timeid = 1where to_char(log_time,'HH24:MI') >= '06:01' and to_char(log_time,'HH24:MI') <= '12:00';update tempfact_uselog2 set timeid = 3where to_char(log_time,'HH24:MI') >= '18:01' or to_char(log_time,'HH24:MI') <= '06:00';create table fact_uselog2 as -- 4. aggregateselect semid, timeid, class_id, major_code, count(student_id) as total_usagefrom tempfact_uselog2group by semid, timeid, class_id, major_code;
Key moves: count-based diagnosis → select distinct CTAS → alter add + one update per band → single group by. The night band needs or, not and, because it wraps past midnight; and the fact’s row count is unchanged by cleaning — only total_usage moves.
⚠️ Common Mistakes
💡 = NULL never matches ➔ 3-valued logic makes it UNKNOWN; only IS NULL works.
💡 “not A or B” trap ➔ emp_no <> 3 OR emp_no <> 8 is TRUE for every row; exclusion needs AND.
💡 Alias in GROUP BY ➔ illegal (GROUP BY runs before SELECT); repeat the full expression.
💡 Forgetting COMMIT ➔ your inserts exist only in the session buffer; close the client badly and the work is gone.
💡 Word’s curly quotes ➔ SQL pasted from a .docx lab sheet carries ' instead of '; Oracle rejects it — retype the quote.
💡 Building a FIT3003 fact from the dimension tables ➔ dimensions were created with select distinct and hold no measures; aggregate from the operational (or Temp) tables.
💡 Staging a fact without counting first ➔ correct SQL over dirty sources produces a correctly-shaped fact with inflated measures; predict the join cardinality before trusting anything ➔ Data Exploration (Warehouse Validation).
💡 select … as X while group by still names the old column ➔ ORA-00979; change the projection and the grouping list together.