Chapter Seven (part 2) Multiple Row Functions:

Chapter Seven (part 2)Multiple Row Functions: Objectives: -Multiple row functions -Ordering -Grouping -Concept of JOIN

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

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

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

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

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

Distinct: SELECT DISTinct (dept) FROM Faculty;

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

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;

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;

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.

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

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

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

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;

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

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

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 ;

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

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

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

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;

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

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

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.

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

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

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

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;

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;

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

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;

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

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

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

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

Chapter Seven (part 2) Multiple Row Functions: