SQL Advanced
Outline
▪ CASE
▪Subquery – nested, inline, correlated ▪ Views
▪Joins – self join, outer join
▪Set Operators
▪Oracle Functions
2
2
SQL CASE statement
The CASE statement used in the select list enables the query to evaluate an
attribute and output a particular value based on that evaluation
SELECT
unitcode,
to_char(ofyear,’YYYY’) as year,
semester,
case cltype
when ‘L’ then ‘Lecture’
when ‘T’ then ‘Tutorial’
end as Classtype,
case
when clduration < 2 then clduration || 'hr Short class'
when clduration = 2 then clduration || 'hr Standard class'
else clduration || 'hr Long class'
end as classduration
FROM uni.schedclass
ORDER BY unitcode, year, semester, classtype;
3
3
SELECT
unitcode,
to_char(ofyear,'YYYY') as year,
semester,
cltype,
clduration
FROM uni.schedclass
ORDER BY unitcode, year, semester, cltype;
SELECT
unitcode,
to_char(ofyear,'YYYY') as year,
semester,
case cltype
when 'L' then 'Lecture'
when 'T' then 'Tutorial'
end as Classtype,
case
when clduration < 2 then clduration || 'hr Short class'
when clduration = 2 then clduration || 'hr Standard class'
else clduration || 'hr Long class'
end as classduration
FROM uni.schedclass
ORDER BY unitcode, year, semester, classtype;
4
4
Outline
▪ Case
▪Subquery – nested, inline, correlated
▪ Views
▪Joins - self join, outer join ▪Set Operators
▪Oracle Functions
5
5
Query
For each unit, find the students(studid) who obtained the maximum mark in the unit
6
6
Subquery (NESTED)
▪For each unit, find the students who obtained the maximum mark in the unit
select studid, unitcode, mark
from uni.enrolment
where (unitcode, mark) IN (select unitcode, max(mark)
from uni.enrolment group by unitcode)
order by unitcode, studid;
▪the subquery is independent of the outer query and is executed only once.
7
7
Subquery (CORRELATED)
▪ For each unit, find the students who obtained the maximum mark in the unit
select studid, unitcode, mark from uni.enrolment e1
where mark = (select max(mark)
from uni.enrolment e2
where e1.unitcode = e2.unitcode)
order by unitcode, studid;
▪the subquery is related to the outer query and is evaluated once for each row of the outer query
▪correlated subqueries can also be used within update statements – outer update occurs based on value returned from subquery
8
8
SELECT
unitcode,
MAX(mark) AS max_mark
FROM
uni.enrolment
GROUP BY
unitcode;
Output is multi row multi column
9
9
Subquery (INLINE) – Derived table
▪For each unit, find the students who obtained the maximum mark in the unit
select studid, e.unitcode, mark from
(select unitcode, max(mark) as max_mark
from uni. enrolment
group by unitcode) max_table
join uni.enrolment e on e.unitcode = max_table.unitcode and
e.mark = max_table.max_mark
order by unitcode, studid;
10 10
▪For each grade, compute the percentage of the students who got that grade
SELECT
grade,
COUNT(*) AS grade_count
FROM
uni.enrolment
WHERE
grade IS NOT NULL
GROUP BY
grade
ORDER BY
grade;
SELECT
COUNT(*) AS total_rows
FROM uni.enrolment
WHERE
grade IS NOT NULL;
11 11
Subquery (INLINE)
▪ For each grade, compute the percentage of the students who got that grade
SELECT grade,
count(grade) as grade_count,
(SELECT count(grade) from uni.enrolment) as total_rows, 100*count(grade)/(SELECT count(grade) FROM uni.enrolment) as percentage
FROM uni.enrolment where grade is NOT NULL GROUP BY grade
order by grade;
12 12
Use of subquery in INSERT
Student
create table student2 (
stu_nbr number(8) not null,
stu_lname varchar2(20) not null,
stu_fname varchar2(20) not null);
alter table student2 add constraint pk_student2 primary key (stu_nbr);
-- insert to an existing table via select
insert into student2
(select stu_nbr, stu_lname, stu_fname from student);
13 13
Use of subquery in CREATE TABLE
Student
-- Create table from existing data
CREATE TABLE student3
AS
( SELECT
stu_lname
|| ' '
|| stu_fname AS studentname
FROM student
);
select * from student3;
14 14
Outline
▪ Case
▪Subquery – nested, inline, correlated
▪ Views
▪Joins - self join, outer join ▪Set Operators
▪Oracle Functions
15 15
Views
▪A virtual table derived from one or more base tables.
▪Sometimes used as "Access Control" to the database CREATE OR REPLACE VIEW [view_name] AS
SELECT ... ;
create or replace view max_view as
select unitcode, max(mark) as max_mark from uni.enrolment
group by unitcode;
select * from max_view order by unitcode;
▪What objects do I own?
select * from user_objects;
16 16
Using Views
▪For each unit, find the students who obtained the maximum mark in the unit
create or replace view max_view
as select unitcode, max(mark) as max_mark from uni.enrolment group by unitcode;
select e.studid, e.unitcode, e.mark
from max_view v join uni.enrolment e on e.unitcode = v.unitcode
and e.mark = v.max_mark order by e.unitcode;
Please note VIEWS MUST NOT be used for Assignment 2
17 17
Outline
▪ Case
▪Subquery – nested, inline, correlated
▪ Views
▪Joins - self join, outer join ▪Set Operators
▪Oracle Functions
18 18
Self Join
▪Show the name of the manager for each employee.
SELECT empno,
empname, empinit, mgrno
FROM emp.employee;
19 19
SELECT *
FROM emp.employee e1 JOIN emp.employee e2 ON e1.mgrno = e2.empno;
e1
e2
Joined rows 1,12
2,12
3,11
Note some columns have been hidden
Why now only 13 rows?
20 20
SELECT e1.empno, e1.empname, e1.empinit, e1.mgrno, e2.empname AS MANAGER
FROM emp.employee e1 JOIN emp.employee e2 ON e1.mgrno = e2.empno
ORDER BY e1.empname;
21 21
NATURAL JOIN
Student Mark
Natural Join gives no information for Chris and the student with ID 4
Select * from student s join mark m on s.id = m.id; Note that this is an EQUI JOIN (an inner join)
22
22
FULL OUTER JOIN
Student Mark
Get (incomplete) information of both Chris and student with ID 4
select * from
student s full outer join mark m on s.id = m.id;
23
23
LEFT OUTER JOIN
Student Mark
Get (incomplete) information of only Chris
select * from
student s left outer join mark m on s.id = m.id;
24
24
RIGHT OUTER JOIN
Student Mark
Get (incomplete) information of the student with ID 4
select * from
student s right outer join mark m on s.id = m.id;
25
25
Outline
▪ Case
▪Subquery – nested, inline, correlated
▪ Views
▪Joins - self join, outer join ▪Set Operators
▪Oracle Functions
30 30
Relational Set Operators
▪ Using the set operators you can combine two or more sets to create new sets (relations) ▪ Union All
–All rows selected by either query, including all duplicates
▪ Union
–All rows selected by either query, removing duplicates (e.g,, DISTINCT on Union
All)
▪ Intersect
–All distinct rows selected by both queries
▪ Minus
–All distinct rows selected by the first query but not by the second
▪ All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order.
▪ The two sets must be UNION COMPATIBLE (i.e., same number of attributes and similar data types)
31 31
MINUS
▪List the name of staff who are not a chief examiner in an offering.
select staffid, stafflname, stafffname from uni.staff
where staffid IN
(select staffid from uni.staff
minus
select chiefexam from uni.offering);
32 32
UNION
▪Create a list of units with its average mark. Give the label “Below distinction” to all units with the average less than 70 and “Distinction and Above” for those units with average greater or equal to 70.
1. Select units with average marks less than 70 and set status
2. Select units with average marks greater or equal to 70 and set status
3. Take a union of 1 and 2
33 33
SELECT unitcode, AVG(mark) AS Average, 'Below Distinction' AS Average_Status
FROM
uni.enrolment
GROUP BY
unitcode
HAVING
AVG(mark) < 70
UNION
SELECT unitcode, AVG(mark) AS Average, 'Distinction and Above' AS Average_Status
FROM
uni.enrolment
GROUP BY
unitcode
HAVING
AVG(mark) >= 70
ORDER BY
Average DESC ;
34 34
INTERSECTION
▪Find students who have the same surname as a staff member’s surname.
▪Find the common surnames in staff and student table. ▪Find students with the surname present in 1
35 35
SELECT studid, studfname, studlname
FROM
uni.student
WHERE
studlname IN
( SELECT DISTINCT studlname
FROM
uni.student
INTERSECT
SELECT DISTINCT stafflname
FROM
uni.staff)
ORDER BY studid;
36 36
Outline
▪ Case
▪Subquery – nested, inline, correlated
▪ Views
▪Joins – self join, outer join ▪Set Operators
▪Oracle Functions
37 37
See document on Moodle
38 38
SELECT
unitcode,
extract(year from ofyear) as year, semester,
decode (cltype, ‘L’, ‘Lecture’,
‘T’, ‘Tutorial’) as Classtype,
case
when clduration < 2 then clduration || 'hr Short class'
when clduration = 2 then clduration || 'hr Standard
class'
else clduration || 'hr Long class'
end as classduration
FROM uni.schedclass
ORDER BY unitcode, year, semester, classtype;
39 39
SELECT
unitcode,
lpad(extract(year from ofyear) || ' S' || semester,10,' ') as offering, decode (cltype, 'L', 'Lecture',
'T', 'Tutorial') as Classtype,
case
when clduration < 2 then clduration || 'hr Short class'
when clduration = 2 then clduration || 'hr Standard class'
else clduration || 'hr Long class'
end as classduration
FROM uni.schedclass
ORDER BY unitcode, offering, classtype;
40 40