程序代写代做代考 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; 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 Employee Q1. 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) ID 1 2 3 Name Alice Bob Chris Manager 2 3 26 26 SELECT * FROM employee; SELECT e1.name AS name, e2.name AS manager FROM employee e1 RIGHT OUTER JOIN employee e2 ON e1.manager = e2.id; Employee Manager e2 e1 27 27 ID Name Salary 1 Alice 100,000 2 Bob 150,000 3 Chris 200,000 Project Cost EmpID Alpha 4000 1 Beta 3000 2 Gamma 5000 2 Employee Project Q2. 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 attributes B. the same degree and attributes’ name C. the same degree and cardinality. D. the same cardinality. 29 29 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 Q4. Given the following oracle syntax for round function: ROUND(n [,integer]) where n is a number and integer determines the decimal 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