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,
• salarysalary0.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) gt 20

20
Having

• SELECT dept, MAX(salary)
• FROM faculty
• GROUP BY dept
• HAVING MAX(salary)gt50000

21
Having

• SELECT dept, MAX(salary)
• FROM faculty
• GROUP BY dept
• HAVING MAX(salary)gt50000
• 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) gt5000

• SELECT name, AVG(salary)
• FROM department
• GROUP BY name
• HAVING AVG(salary) gt5000

24
Practice

• List each SalesRepNumber with their total
  customers balance.

25
Practice

• List each SalesRepNumber with their total
  customers balance, only for customers with
  balance of 1000 or more.

26
Practice

• List each SalesRepNumber with their total
  customers 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 students 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 gt 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.namefaculty.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.namefaculty.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 gt 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 gt b.Salary AND
• a.Salary lt 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