Multiple Row Functions:

1
Multiple Row Functions
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(salary NVL(salary,0)),
• COUNT()
• FROM faculty
• WHERE dept COSC

6
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

7
Distinct
SELECT DISTINCT (dept) FROM Faculty
8
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

9
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

10
Grouping

• SELECT
• FROM
• WHERE
• GROUP BY
• ORDER BY

11
Grouping

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

12
Grouping

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

13
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

14
Having

• Condition on Group
• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY

15
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

16
Having

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

17
Having

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

18
Having

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

19
Illegal Queries

• SELECT name, count() FROM dep

• SELECT name, count()
• FROM dep
• GROUP BY name

• SELECT name, AVG(salary)
• FROM dep
• WHERE AVG(salary) 5000
• GROUP BY name

20
JOIN

• Definition
• General Format
• SELECT col1,col2,.
• FROM table1, table2,
• WHERE conditions
•  

21
JOIN

• List of students name with the grade 'A'
•  
• SELECT Name
• FROM Student_Course, Student
• WHERE Student.ID
• Student_Course.ID and Grade A

22
JOIN

• Aliases
•  
• FROM Student a, Student b
• WHERE a.ID b.ID

23
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

24
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

25
JOIN

• Inner Join
• SELECT a.Name
• FROM Student_Course
• INNER JOIN Student a
• ON a.ID
• Student_Course.ID

26
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

27
JOIN

• Outer Join
• 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()

28
JOIN

• Outer Join
• SELECT a.Name
• FROM Student_Course
• LEFT OUTER JOIN Student a
• ON a.ID
• Student_Course.ID

29
JOIN

• Outer Join
• SELECT a.Name
• FROM Student_Course
• RIGHT OUTER JOIN Student a
• ON a.ID
• Student_Course.ID

30
JOIN

• Outer Join
• SELECT a.Name
• FROM Student_Course
• FULL OUTER JOIN Student a
• ON a.ID
• Student_Course.ID

31
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

32
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
• b.Name 'Mary' AND
• c.Name 'John