[SOLVED] CS SQL database SQL Advanced

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;33 SELECTunitcode,to_char(ofyear,’YYYY’) as year,semester,cltype,cldurationFROM uni.schedclassORDER BY unitcode, year, semester, cltype;SELECTunitcode,to_char(ofyear,’YYYY’) as year,semester,case cltype when ‘L’ then ‘Lecture’ when ‘T’ then ‘Tutorial’end as Classtype,casewhen clduration < 2 then clduration || ‘hr Short class’when clduration = 2 then clduration || ‘hr Standard class’else clduration || ‘hr Long class’end as classdurationFROM uni.schedclassORDER BY unitcode, year, semester, classtype; 44Outline CaseSubquery nested, inline, correlated ViewsJoins – self join, outer join Set OperatorsOracle Functions55QueryFor each unit, find the students(studid) who obtained the maximum mark in the unit66Subquery (NESTED)For each unit, find the students who obtained the maximum mark in the unitselect studid, unitcode, markfrom uni.enrolmentwhere (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.77Subquery (CORRELATED) For each unit, find the students who obtained the maximum mark in the unitselect studid, unitcode, mark from uni.enrolment e1where mark = (select max(mark)from uni.enrolment e2where 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 querycorrelated subqueries can also be used within update statements outer update occurs based on value returned from subquery 88SELECTunitcode,MAX(mark) AS max_markFROMuni.enrolmentGROUP BYunitcode;Output is multi row multi column 99Subquery (INLINE) Derived tableFor each unit, find the students who obtained the maximum mark in the unitselect studid, e.unitcode, mark from(select unitcode, max(mark) as max_markfrom uni. enrolmentgroup by unitcode) max_tablejoin uni.enrolment e on e.unitcode = max_table.unitcode ande.mark = max_table.max_markorder by unitcode, studid; 10 10For each grade, compute the percentage of the students who got that grade SELECT grade, COUNT(*) AS grade_count FROMuni.enrolmentWHEREgrade IS NOT NULLGROUP BYgradeORDER BYgrade;SELECTCOUNT(*) AS total_rowsFROM uni.enrolmentWHEREgrade IS NOT NULL; 11 11Subquery (INLINE) For each grade, compute the percentage of the students who got that gradeSELECT 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 percentageFROM uni.enrolment where grade is NOT NULL GROUP BY gradeorder by grade; 12 12Use of subquery in INSERTStudent 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 13Use of subquery in CREATE TABLE Student — Create table from existing dataCREATE TABLE student3AS( SELECTstu_lname|| ‘ ‘|| stu_fname AS studentnameFROM student);select * from student3;14 14Outline CaseSubquery nested, inline, correlated ViewsJoins – self join, outer join Set OperatorsOracle Functions15 15ViewsA virtual table derived from one or more base tables.Sometimes used as “Access Control” to the database CREATE OR REPLACE VIEW [view_name] ASSELECT … ;create or replace view max_view asselect unitcode, max(mark) as max_mark from uni.enrolmentgroup by unitcode;select * from max_view order by unitcode;What objects do I own?select * from user_objects;16 16Using ViewsFor each unit, find the students who obtained the maximum mark in the unitcreate or replace view max_viewas select unitcode, max(mark) as max_mark from uni.enrolment group by unitcode;select e.studid, e.unitcode, e.markfrom 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 217 17Outline CaseSubquery nested, inline, correlated ViewsJoins – self join, outer join Set OperatorsOracle Functions18 18Self JoinShow the name of the manager for each employee.SELECT empno,empname, empinit, mgrnoFROM emp.employee; 19 19SELECT *FROM emp.employee e1 JOIN emp.employee e2 ON e1.mgrno = e2.empno;e1e2Joined rows 1,122,123,11 Note some columns have been hiddenWhy now only 13 rows? 20 20SELECT e1.empno, e1.empname, e1.empinit, e1.mgrno, e2.empname AS MANAGERFROM emp.employee e1 JOIN emp.employee e2 ON e1.mgrno = e2.empnoORDER BY e1.empname;21 21NATURAL JOINStudent MarkNatural Join gives no information for Chris and the student with ID 4Select * from student s join mark m on s.id = m.id; Note that this is an EQUI JOIN (an inner join)2222FULL OUTER JOINStudent MarkGet (incomplete) information of both Chris and student with ID 4select * fromstudent s full outer join mark m on s.id = m.id;2323LEFT OUTER JOINStudent MarkGet (incomplete) information of only Chrisselect * fromstudent s left outer join mark m on s.id = m.id; 2424RIGHT OUTER JOINStudent MarkGet (incomplete) information of the student with ID 4select * fromstudent s right outer join mark m on s.id = m.id; 2525 EmployeeQ1. What is the output from the following SQL:select e1.name as name, e2.name as manager from employee e1 right outer join employee e2 on e1.manager = e2.id;(A)(B)ID123NameAliceBobChrisManager 23 26 26 SELECT *FROMemployee;SELECTe1.nameAS name,e2.nameAS managerFROMemployeee1RIGHT OUTER JOIN employeee2 ONe1.manager = e2.id;Employee Manager e2 e127 27ID NameSalary1Alice100,0002Bob150,0003Chris200,000 Project CostEmpIDAlpha40001Beta30002Gamma50002 Employee ProjectQ2. Which of the following shows, for each employee, the total amount of projects they are assigned to? (E.g., Alice is assigned to Alpha with total cost 4000, Bob is assigned to Beta and Gamma with total cost 8000)A. select e.name, sum(cost) as total from employee e left outer join project p on e.id = p.empid group by e.name;B. select e.name, sum(cost) as total from employee e right outer join project p on e.id = p.empid group by e.name;C. select e.name, NVL(sum(cost),0) as total from employee e left outer join project p on e.id = p.empid group by e.name;D. None of the above 28 28 Q3. Two or more queries that are connected using a set operator have to be union compatible. When would two relations be union compatible? It is when the two relations have:A. the same degree and similar domain for the attributesB. the same degree and attributes nameC. the same degree and cardinality.D. the same cardinality.29 29Outline CaseSubquery nested, inline, correlated ViewsJoins – self join, outer join Set OperatorsOracle Functions30 30Relational Set Operators Using the set operators you can combine two or more sets to create new sets (relations) Union AllAll rows selected by either query, including all duplicates UnionAll rows selected by either query, removing duplicates (e.g,, DISTINCT on UnionAll) IntersectAll distinct rows selected by both queries MinusAll 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 31MINUSList the name of staff who are not a chief examiner in an offering.select staffid, stafflname, stafffname from uni.staffwhere staffid IN(select staffid from uni.staffminusselect chiefexam from uni.offering); 32 32UNIONCreate 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 status2. Select units with average marks greater or equal to 70 and set status3. Take a union of 1 and 233 33 SELECT unitcode, AVG(mark) AS Average, ‘Below Distinction’ AS Average_StatusFROMuni.enrolmentGROUP BYunitcodeHAVINGAVG(mark) < 70UNIONSELECT unitcode, AVG(mark) AS Average, ‘Distinction and Above’ AS Average_StatusFROMuni.enrolmentGROUP BYunitcodeHAVINGAVG(mark) >= 70
ORDER BY
Average DESC ;
34 34

INTERSECTION
Find students who have the same surname as a staff members 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 Standardclass’else clduration || ‘hr Long class’end as classdurationFROM uni.schedclassORDER BY unitcode, year, semester, classtype; 39 39SELECTunitcode,lpad(extract(year from ofyear) || ‘ S’ || semester,10,’ ‘) as offering, decode (cltype, ‘L’, ‘Lecture’,’T’, ‘Tutorial’) as Classtype,casewhen clduration < 2 then clduration || ‘hr Short class’when clduration = 2 then clduration || ‘hr Standard class’else clduration || ‘hr Long class’end as classdurationFROM uni.schedclassORDER BY unitcode, offering, classtype;40 40 Q4. Given the following oracle syntax for round function: ROUND(n [,integer]) where n is a number and integer determines thedecimal point;what would be the right SELECT clause for rounding the average mark of all marks in the enrolment (not including the NULL values) to the nearest 2 decimal point?A. SELECT avg(round(mark,2))B. SELECT round(avg(mark,2))C. SELECT round(avg(mark),2)D. SELECT avg(mark(round(2)))41 41