Chapter Ten Multiple Row Functions/Join:

1. Chapter TenMultiple Row Functions/Join: Objectives: -Multiple row functions -Ordering -Grouping -Concept of JOIN

2. Aggregate Functions: • MAX (DISTINCT | ALL) (value) • MIN (DISTINCT | ALL) (value) • AVG (DISTINCT | ALL) (value) • SUM (DISTINCT | ALL) (value) • COUNT (DISTINCT | ALL) (value)* • STDDEV (DISTINCT | ALL) (value) • VARIANCE (DISTINCT | ALL) (value)

3. Aggregate Functions: • COUNT (*) • COUNT (id) • COUNT (DISTINCT ID) • MAX (Birth_date)

4. SUM (exp) • SUM (exp) [ OVER (analytic_clause) ] SELECT ID, name, salary,SUM (Salary) OVER (ORDER BY salary) “Cumulating sum” FROM Faculty; • NAME SALARY DEPT Cumulating sum • ------------------------------------------------------------- • Hook 25000 MATH 25000 • Johnson 30000 MATH 55000 • Williams 34000 COSC 89000 • Jackson 45000 COSC 134000

5. SUM (exp) SELECT ID, name, salary, SUM (Salary) OVER (PARTITION BY Dept ORDER BY salary) “Cumulating sum” FROM Faculty; • NAME SALARY DEPT Cumulating sum • ------------------------------------------------------------------ • William 34000 COSC 34000 • Jackson 45000 COSC 79000 • Hook 25000 MATH 25000 • Johnson 30000 MATH 55000

6. Aggregate Functions: • List the highest GPA SELECT MAX (GPA) FROM Student;

7. Aggregate Functions: • List average, max, min, and total salary of cosc faculty SELECT AVG(salary), MIN(salary), MAX(salary), SUM(salary) FROM faculty WHERE dept = ‘COSC’;

8. Aggregate Functions: • List average salary of cosc faculty: SELECT AVG(NVL(salary,0)), AVG (salary), COUNT(*) FROM faculty WHERE dept = ‘COSC’;

9. Aggregate Functions: • LEAD: Access to more than one row • List faculty with the hired date and the next hired date after each one • SELECT name, hired_date, LEAD(hired_date,1) OVER(ORDER BY hired_date) AS “next hired” FROM faculty;

10. Rank: • RANK (exp, exp, …) WITHIN GROUP • RANK ( ) OVER ( [partition] ORDER BY) SELECT RANK(24000) WITHIN GROUP (ORDER BY Salary DESC) “Rank of $24,000 Salary” FROM Faculty; SELECT RANK(24000, ‘PROFESSOR’) WITHIN GROUP (ORDER BY Salary, P_Rank) “Rank” FROM Faculty; SELECT Name, Salary, RANK() OVER (PARTITION BY Dept_Name ORDER BY Salary, P_Rank) “Rank” FROM Faculty WHERE Dept = ‘COSC’;

11. Distinct: SELECT DISTINCT (dept) FROM Faculty;

12. User: SELECT USER, UID FROM DUAL;

13. USERENV: • USERENV (‘Parameter’) parameters: ENTRYID -- current audit entry number ISDBA -- True if s/he in DBA privilege LANG -- Language used SESSIONID --Session ID TERMINAL --Operating System ID --for the Terminal of current session SELECT USERENV(‘LANG’) ‘Language’ FROM DUAL; LANGUAGE ---------------------------- AMERICAN_AMERICA.WE8ISO8859P1

14. Ordering • ORDERING: (Default is Ascending ASC) • List students name in an alphabetic order SELECT name FROM student ORDER BY name; ORDER BY Name , GPA DESC, Major;

15. Ordering • List of the faculty salary for the next year with 5% increase order by new salary. SELECT name, salary pay, salary+salary*0.05 AS new_salary FROM faculty ORDER BY new_salary;

16. Grouping SELECT FROM [WHERE] [GROUP BY] [ORDER BY]

17. Grouping • Average Salary of faculty members by department SELECT dept, AVG(Salary) FROM Faculty GROUP BY dept;

18. Grouping • List number of courses taken by each student SELECT ID, COUNT(*) FROM Student_Course GROUP BY ID;

19. Grouping by multiple attributes • List total number of credits taken by each student SELECT ID, SUM(Cr) FROM Student_Course GROUP BY ID; SELECT ID, semester, SUM(Cr) FROM Student_Course GROUP BY ID, semester; SELECT dept, count(name) FROM faculty GROUP BY dept;

20. Having • Condition on Group: SELECT FROM [WHERE] [GROUP BY] [HAVING] [ORDER BY]

21. Having List ID of students who have more than 20 credits and majoring in COSC. SELECT ID, SUM (Cr) FROM Student_Course WHERE Major = 'COSC' GROUP BY ID HAVING SUM(Cr) > 20 ;

22. Having • SELECT dept, MAX(salary) FROM faculty GROUP BY Department HAVING MAX(salary)>50000;

23. Having • SELECT dept, MAX(salary) a FROM faculty GROUP BY Dept HAVING a >50000 ORDER BY MAX(salary);

24. Having • SELECT dept, MAX(SUM(salary)) FROM faculty GROUP BY dept;

25. Illegal Queries: • SELECT name, count(*) FROM Department; • SELECT name, count(*) FROM Department GROUP BY name;

26. Illegal Queries: • SELECT name, AVG(salary) FROM Department WHERE AVG(salary) >5000; • SELECT name, AVG(salary) • FROM Department • GROUP BY name • HAVING AVG(salary) >5000;

27. JOIN: • Definition • General Format: • SELECT col1,col2,…. FROM table1, table2,… WHERE conditions;

28. JOIN: • List of students’ name with the grade = 'A' • SELECT Name FROM Student_Course, Student WHERE Student.ID = Student_Course.ID AND Grade =‘A’;

29. JOIN: • Aliases:   SELECT Name FROM Student_Course sc, Student s WHERE s.ID = sc.ID AND Grade =‘A’;

30. CARTESIAN PRODUCT: • Join condition is omitted • Join condition is invalid • All rows in table one are joined to all rows in table two SELECT * FROM Student, faculty;

31. JOIN • Equijoin/Natural: SELECT Name FROM Student_Course, Student WHERE Student.ID = Student_Course.ID ; SELECT Name FROM Student_Course NATURAL JOIN Student ;

32. JOIN • Equijoin/Natural: SELECT Name FROM Student_Course INNER JOIN Student ;

33. JOIN • Inner Join: SELECT a.Name FROM Student_Course b INNER JOIN Student a ON a.ID = b.ID ;

34. JOIN • Equijoin: SELECT department.num_faculty, faculty.name FROM department, faculty WHERE department.name=faculty.dept;

35. JOIN •  Non-Equijoin: Faculty (name, salary) Status (rank, low_salry, high_salary) • Get the name, salary and rank of faculty members • SELECT name, salary, rank FROM faculty, status WHERE salary BETWEEN low_salary AND high_salary;

36. JOIN • Outer Join: List of books that have been checked out: SELECT DISTINCT title FROM CheckedBook ;

37. JOIN • Outer Join: List of books that have not been checked out: SELECT DISTINCT title FROM ShelfBook ;

38. JOIN • Outer Join: List of all books:?? SELECT DISTINCT title FROM ShelfBook a, CheckedBook b WHERE a.ID=b.ID ;

39. JOIN • Outer Join: List of all books: SELECT title, MAX(b.R_date-b.C_date) FROM ShelfBook a, CheckedBook b WHERE a.ID (+) = b.ID ;

40. JOIN • ANSI SQL Outer Join: List of all books: SELECT DISTINCT title FROM ShelfBook b RIGHT OUTER JOIN CheckedBook a ON a.ID = b.ID ;

41. JOIN • ANSI SQL Outer Join: List of all books: SELECT DISTINCT title FROM CheckedBook a LEFT OUTER JOIN ShelfBook b ON a.ID = b.ID ;

42. JOIN • ANSI SQL Outer Join: List of all books: SELECT DISTINCT title FROM CheckedBook a FULL OUTER JOIN ShelfBook b ON a.ID = b.ID ;

43. JOIN • Outer Join: • SELECT department.num_faculty, faculty.name FROM department, faculty WHERE department.name(+)=faculty.dept

44. JOIN • Outer Join: • SELECT department.num_faculty, faculty.name FROM department LEFT OUTER JOIN faculty ON department.name=faculty.dept;

45. JOIN • Self Join SELECT a.Name FROM Student a, Student b WHERE a.ID > b.ID AND b.Name = 'SMITH'; • What is the output of this query

46. JOIN • Self Join • List of Faculty member with salary higher than salary of Mary and less than salary of John SELECT a.Name FROM Faculty a, Faculty b, Faculty c WHERE a.Salary > b.Salary AND a.Salary < c.Salary AND b.Name = 'MARY' AND c.Name = 'JOHN’;