FIT2094 Lecture 10: FIT2094 Lec 10 Notes
31 May 2018
School
Department
Course
Professor
W10: SQL III!
Subquery (NESTED)"
• For each unit, find the students who obtained maximum mark in the unit !
Select studid, unitcode, mark"
from enrolment"
where (unitcode, mark) IN (select unitcode, max(mark) !
from enrolment group by unitcode); !
• the subquery is independent of the outer query and is executed only once. !
Subquery (CORRELATED) !
• For each unit, find the students who obtained maximum mark in the unit !
Select studid, unitcode, mark from enrolment e1"
where mark = (select max(mark) !
from enrolment e2"
where e2.unitcode = e1.unitcode) !
• the subquery is related to the outer query and is considered to be evaluated once for each row
of the outer query !
Subquery (INLINE)"
• For each unit, find the students who obtained maximum mark in the unit !
select studid, e.unitcode, mark"
from (select unitcode, max(mark) as max_mark !
from enrolment group by unitcode) !
temp join enrolment e on temp.unitcode = e.unitcode and e.mark = temp.max_mark; !
Subquery (INLINE) !
• For each grade, compute the percentage of !
the students who got that grade SELECT !
grade,"
count(grade) as grade_count,"
(SELECT count(*) from enrolment) as total_rows, 100*count(grade)/(SELECT count(*) FROM
enrolment) as percentage !
FROM enrolment"
where grade is NOT NULL GROUP BY grade; !
Views !
• Avirtualtablederivedfromoneormorebase tables. !
CREATE OR REPLACE VIEW [view_name] AS SELECT ... ; !
Create or replace view max_view as"
Select unitcode, max(mark) as max_mark from !
enrolment group by unitcode; Select * from max_view; !
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
Subquery (nested: for each unit, nd the students who obtained maximum mark in the unit. Select studid, unitcode, mark from enrolment where (unitcode, mark) in (select unitcode, max(mark) from enrolment group by unitcode): the subquery is independent of the outer query and is executed only once. Subquery (correlated: for each unit, nd the students who obtained maximum mark in the unit. Subquery (inline: for each grade, compute the percentage of the students who got that grade select grade, count(grade) as grade_count, (select count(*) from enrolment) as total_rows, 100*count(grade)/(select count(*) from enrolment) as percentage. From enrolment where grade is not null group by grade; Create or replace view [view_name] as select ; Select unitcode, max(mark) as max_mark from enrolment group by unitcode; select * from max_view; Using the set operators you can combine two or more sets to create new sets (relations)
