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.
🗺️ Task Index — “I need to…”
I need to…
Reach for
Section
copy a lecturer’s table into my account
create table t as select * from dtaniar.t (rows only, no PK/FK)
DDL
insert several rows in one statement
insert all into … into … select * from dual
DML
compare or insert a date
to_date('01-Mar-2021','DD-MON-YYYY')
Row Functions
get a year / month out of a date
to_char(d,'YYYY') · extract(year from d)
Row Functions · Advanced
show something instead of NULL
nvl(col, x) — same type both sides
Row Functions
filter groups, not rows
having
Aggregates
keep unmatched rows
left outer join · old-style a.k = b.k(+)
Joins · W5–6
find rows with no partner
not in (subquery) ⛔ a NULL in the subquery returns nothing
Subqueries · W3
per-group maximum row
where (g, x) in (select g, max(x) … group by g)
Subqueries
if / else inside a query
case when … then … end · decode(a, b, c, d)
Advanced
build a dimension
select distinct … — band derived attributes with alter add+update
W2
build a fact
select keys, agg … group by keys from operational tables or a TempFact
W2
count two populations
left outer join+count(col)
W2
keep only the latest row per entity
rank() over (partition by … order by … desc)=1
W2
find duplicate keys
group by k having count(*) > 1 on both join sides
W3
weight factor / group list for a bridge
1.0/count(*) · listagg(c, '_') within group (order by c)
W4
key a dimension
create sequence+.nextval
W5–6
fill an FK from a dimension
correlated update … set fk = (select … where …)
W5–6
build a pivoted fact
AllDimensions product +(+)+nvl(…, 0)
W5–6
merge facts of the same shape
union
W7 · W8
merge sources owning different measures
zero-padded union+ outer sum
W8
top-N
inline view +rank() … <= N (or rownum after an inner order by)
W7 · W9
subtotals and a grand total
group by cube / rollup+decode(grouping(c), 1, 'All', c)
W9
rank with ties handled
rank / dense_rank / row_number
W9
running total / moving average
sum(sum(m)) over (… rows unbounded preceding) · avg(sum(m)) over (… rows k-1 preceding)
W9
put the grand mean on every row
avg(x) over ()
W10
slope and intercept
means → slope → intercept, three layers
W10
auto-fill an ID on insert
before insert … for each row+select seq.nextval into :new.ID from dual
W11
keep a fact count live
find-or-insert trigger: select count(*) into IsFound …
W11
🧩 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.
drop sequence Seq_ID; then create sequence Seq_ID start with 1 increment by 1 maxvalue 99999999 minvalue 1 nocycle;
the warehouse way to key a dimension; drop first or a re-run raises ORA-00955
consume the sequence
update JunkDim set JunkID = Seq_ID.nextval;
one statement keys every row; .nextval fires once per row, .currval never advances
junk dimension
create table JunkDim as select distinct Ensuite, Pool, Aspect_Facing, Spa from Property order by …;
the Cartesian product of low-cardinality unrelated columns ➔ Junk Dimensions
deliberate Cartesian product
create table AllDimensions as select … from Dim1, Dim2, Dim3, Dim4;
no where on purpose — the only place a product is correct; it is the row grid a pivoted fact must fill
Oracle old-style outer join
where A.Year = O.Year(+)
(+) marks the side that may be missing; ANSI equivalent is A left outer join O
zero-fill a pivot measure
nvl(O.Total_Students_Overall, 0) as Total_Students_Overall
the inner-joined fact has no row for an absent combination; (+) keeps it and nvl makes it 0
band with CASE in DML
set Band = (case when s >= 30 and s <= 35 then '4.5' … end)
W6’s banding idiom — one statement replaces W2’s one-update-per-band; unmatched rows get NULL, not an error
fill an FK from a dimension
update TempFact TF set TF.SuburbID = (select S.SuburbID from SuburbDim S where S.Suburb = TF.Suburb);
correlated update — the loop-free alternative to n hand-typed update … where statements
fill an FK, multi-column
set TF.JunkID = (select J.JunkID from JunkDim J where J.Ensuite = TF.Ensuite and J.Pool = TF.Pool and …)
every junk column must appear, or the subquery returns many rows ⟹ ORA-01427
cursor loop
declare cursor JunkCursor is select * from JunkDim; begin for JunkCursorRec in JunkCursor loop update … end loop; end;
PL/SQL route to the same fill; prefer the correlated update unless per-row logic is needed
stack pivot components
create table FinalFact as select … from OverallFact union select * from ListeningFact union …;
breaks one wide source record into one fact row per component; union-compatible column lists required
join the components back
from OverallFactNew O, ListeningFactNew L, … where O.Year = L.Year and L.Year = R.Year and …
the pivot’s last step: k facts need k−1 conditions per key column
trim the padded grid
delete from F where M1 = 0 and M2 = 0 and …;
the pivot’s final step — deletes only rows that are 0 in every measure (60→11); a row zero in one component is the zero the pivot exists to record
one dimension, many roles
from PrivateTaxiFact2 F, CarDim D1, CarDim D2, …, CarDim D5 where F.CarNo1 = D1.CarNo and …
a shifted dimension re-entering as n aliases — the query cost the pivot buys ➔ Pivoted Fact Tables
open-ended SCD range
EndDate = 'Dec9999' as the current-version sentinel
Type 2/4 history; find “now” with a range test or CurrentFlag, never max(EndDate)
SCD Type 4 report — price at sale time
where F.BookID = P.BookID and to_date(F.TimeID,'MonYYYY') >= to_date(P.StartDate,'MonYYYY') and to_date(F.TimeID,'MonYYYY') <= to_date(P.EndDate,'MonYYYY')+listagg(P.Price, ';') within group (order by P.Price)
range join, not equality · a month overlapping two price periods returns both — listagg keeps one row ($23.00;$45.95) · ⛔ compare MonYYYY only through to_date ➔ Slowly Changing Dimensions (SCD)
inside a before trigger, cancels the triggering operation
view DML
instead of delete on room_summary for each row begin delete from rooms where building = :old.building; end;
makes an aggregate view deletable
self-maintaining DW object
create or replace view StudentDim as select …
dimension, measure-less fact, upper level that only re-sums — no trigger needed
fact increment
if IsFound <> 0 then update F set Num = Num + 1 where <keys = :new.keys>; else insert into F values (…, 1); end if;
update-only loses the first row of every new key combination
fact decrement
after delete on TempFact for each row … set Num = Num - 1 where <keys = :old.keys>
expiry: delete from the TempFact, which still holds the date
expiry filter
and to_char(LoginDate,'YYYY') >= '2015' in the Level-0 build or view
decides which source rows may enter the star
derive-then-insert
if to_char(:new.LoginDate,'YYYY') >= '2021' then … '06/30' … else … '07/15' … end if;
a rule change edits only the TempFact trigger
commit in a trigger
slides end every trigger body with commit;
🔭 live Oracle raises ORA-04092 — the firing statement’s transaction owns the commit
✍️ 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.
Practice 5 (FIT3003 Ch8-style): the Property table has four low-cardinality columns — Ensuite/Pool/Spa (yes/no) and Aspect_Facing (East/North/South/West). Build the junk-dimension star and answer "what are the features of the most expensive property (by average price)?"
Reference solution
create table JunkDim as -- 1. Cartesian product = 32 rowsselect distinct Ensuite, Pool, Aspect_Facing, Spa from Propertyorder by Ensuite, Pool, Aspect_Facing, Spa;alter table JunkDim add (JunkID number(2));drop sequence Seq_ID; -- 2. surrogate-key itcreate sequence Seq_ID start with 1 increment by 1 maxvalue 99999999 minvalue 1 nocycle;update JunkDim set JunkID = Seq_ID.nextval;create table PropertyTempFact2 as -- 3. stage + re-key the factselect Ensuite, Pool, Aspect_Facing, Spa, Houseprice from Property;alter table PropertyTempFact2 add (JunkID number(2));update PropertyTempFact2 TF set TF.JunkID = (select J.JunkID from JunkDim J where J.Ensuite = TF.Ensuite and J.Pool = TF.Pool and J.Aspect_Facing = TF.Aspect_Facing and J.Spa = TF.Spa);create table PropertyFact2 as -- 4. aggregateselect JunkID, count(*) as Num_of_Property, sum(Houseprice) as Total_Pricefrom PropertyTempFact2 group by JunkID;select J.Ensuite, J.Pool, J.Aspect_Facing, J.Spa -- 5. the nested questionfrom PropertyFact2 PF, JunkDim Jwhere Total_Price/Num_of_Property = (select max(P.Total_Price/P.Num_of_Property) from PropertyFact2 P)and PF.JunkID = J.JunkID;
Key moves:select distinct over four columns builds the product · Seq_ID.nextval keys it in one statement · a correlated update fills the fact’s FK · the final query needs only 2 tables and 1 join condition, against 5 tables and 4 conditions in the non-junk version.
⚠️ Never store avg ➔ the fact holds Total_Price and Num_of_Property; the average is computed at query time ➔ Fact Measure Aggregation Rules.
Practice 6 (FIT3003 Ch19-style): for MEL in 2019, list each month's total sales, its running year-to-date total, and its rank among the 12 months (best month =1); show only the top 3 months.
Reference solution
select * from (select S.TimeID, sum(Total_Sales) as Total_Sales, sum(sum(Total_Sales)) over (order by S.TimeID rows unbounded preceding) as YTD_Sales, rank() over (order by sum(Total_Sales) desc) as Month_Rank from SalesFact S, TimeDim T where S.TimeID = T.TimeID and Year = 2019 and S.LocationID = 'MEL' group by S.TimeID)where Month_Rank <= 3order by Month_Rank;
Key moves: two windows with differentorder bys over the same grouped rows · the filter lives outside the inline view, so YTD_Sales was computed over all 12 months before the cut — filtering first would shrink the running total.
Practice 7 (FIT3003 Ch19 + Ch21): fit a least-squares trend line to MEL's monthly total sales — return the slope and intercept.
Reference solution
create table monthly asselect row_number() over (order by S.TimeID) as x, sum(Total_Sales) as yfrom SalesFact Swhere S.LocationID = 'MEL'group by S.TimeID;select slope, y_bar_max - (slope * x_bar_max) as interceptfrom ( select sum((x - x_bar) * (y - y_bar)) / sum((x - x_bar) * (x - x_bar)) as slope, max(x_bar) as x_bar_max, max(y_bar) as y_bar_max from (select avg(x) as x_bar, avg(y) as y_bar from monthly) av, monthly);
Key moves: aggregate the fact to one row per month, index the months with a window over the grouped rows, then the three-layer regression · slope reads as average sales growth per month.
Practice 8 (FIT3003 Ch18-style): a new Sub-Level-1 star needs MonthDim(MonthID, Month, Year) with MonthID like '202104'. Write the trigger that keeps it complete as logins arrive in LabActivities(LoginNo, StudentNo, LoginDate, LoginTime, ComputerID).
Reference solution
create or replace trigger AddMonthYear after insert on LabActivities for each rowdeclare TempMonthID MonthDim.MonthID%type; TempYear MonthDim.Year%type; TempMonth MonthDim.Month%type; IsFound number;begin TempMonthID := to_char(:new.LoginDate,'YYYY') || to_char(:new.LoginDate,'MM'); TempYear := to_char(:new.LoginDate,'YYYY'); TempMonth := to_char(:new.LoginDate,'MON'); select count(*) into IsFound from MonthDim where MonthID = TempMonthID; if IsFound = 0 then insert into MonthDim values (TempMonthID, TempMonth, TempYear); end if;end AddMonthYear;
Key moves: row-level after insert on the source · %type anchors · the key built in the sameYYYY || MM order as the build · count(*) into as the existence test, so a month already present is skipped, not duplicated · the star’s fact is a view, so no fact trigger ➔ Active Data Warehouse Evolution.
⚠️ The slide version ends the if with commit; — see the toolkit’s commit row.
⚠️ Common Mistakes (only traps no table row already states)
💡 Word’s curly quotes ➔ SQL pasted from a .docx lab sheet carries ' instead of '; Oracle rejects it — retype the quote.
💡 select … as X while group by still names the old column ➔ ORA-00979; change the projection and the grouping list together.
💡 Pivoting a fact without AllDimensions ➔ the source fact was built with an inner join, so absent combinations have no row at all; outer-joining the component facts to each other cannot invent them ➔ Pivoted Fact Tables.
💡 Summing a determinant fact without pinning the determinant attribute ➔ where Year = '2017' over a 5-component fact returns 55 for 11 candidates — no error, one tidy row, silently 5× wrong; pin it in the where or split it out in the group by ➔ Determinant Dimensions.
💡 Unqualified shared key in an OLAP query ➔ LocationID / TimeID exist in the fact and its dimension; unqualified they raise ORA-00918 (two Chapter 19 slides do this) ➔ always S.LocationID, S.TimeID.
💡 Row counts on sparse data ➔ a cube/rollup grouping set yields one row per combination that occurs, not the product of distinct counts — Lab 9a’s 3 months ×3 pilots gives 7 rows, not 9 ➔ OLAP Cube and Rollup.
💡 Two spellings of one manufactured key ➔ a dimension built on Year || Semester and a trigger looking up Semester || Year never match, so every member is re-inserted under a key no fact row uses (Ch18 slide 68) ➔ Active Data Warehouse Evolution.