Chapter Seven (part 2) Multiple Row Functions:

1. Chapter Seven (part 2)Multiple Row Functions: 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: • List the highest GPA SELECT MAX (GPA) FROM Student;

4. 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’;

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

6. Practice: • Find Average, and sum of quoted price from order form. Also the number of orders.

7. Practice: • Find the highest quoted price from order form. Order number must be in this range :2000 to 3000.

8. Distinct: SELECT DISTinct (dept) FROM Faculty;

9. Practice: • How many customers have an order on March, 10 2001?

10. 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;

11. 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;

12. Practice: • Find the list of customers: last name, first name, balance, credit line and birth date; order the data by birth date from older to younger and balance from lowest to highest.

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

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

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

16. 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;

17. Practice: • List of customer last names and first names for each customer state.

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

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

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

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

22. Having • SELECT dept, AVG(MAX(salary)-MIN(salary)) FROM faculty GROUP BY dept;

23. Illegal Queries: • SELECT name, count(*) FROM department; • SELECT name, count(*) FROM department GROUP BY name; • SELECT name, AVG(salary) FROM department WHERE AVG(salary) >5000; • SELECT name, AVG(salary) FROM department GROUP BY name HAVING AVG(salary) >5000;

24. Practice: • List each SalesRepNumber with their total customer’s balance.

25. Practice: • List each SalesRepNumber with their total customer’s balance, only for customers with balance of $1000 or more.

26. Practice: • List each SalesRepNumber with their total customer’s balance, only if the total SalesRepNumber balance is less than $100,000, for customers with balance of $1000 or more.

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

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

29. JOIN: • Aliases: • FROM Student a, Student b • WHERE a.ID > b.ID

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: SELECT Name FROM Student_Course, Student WHERE Student.ID = Student_Course.ID ; SELECT department.num_faculty, faculty.name FROM department, faculty WHERE department.name=faculty.dept;

32. Practice: • Find the list of customers: Last name, first name, balance and the city where their sales person lives.

33. 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;

34. JOIN • Outer Join: List of students who did take courses. • SELECT name FROM student, student_course WHERE student.id =student_course.id (+); • SELECT department.num_faculty, faculty.name FROM department, faculty WHERE department.name=faculty.dept(+)

35. Practice: • Find the list of customers: Last name, first name, who did not ordered some items in year 2004; • Assume there is a table called customer_order with attributes: c_number, o_number

36. 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

37. 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’;

38. Practice: • Find the list of customers: Last name, first name, who are reside in the same city as Ms joy Smith