Multiple Row Functions/Join:

1

Objectives:

-Multiple row functions

-Ordering

-Grouping

-Concept of JOIN

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)

​

2

Aggregate Functions:

​

• COUNT (*)
• COUNT (id)
• COUNT (DISTINCT ID)

​

• MAX (Birth_date)

​

​

​

3

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

4

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

5

Aggregate Functions:

• List the highest GPA

​

SELECT MAX (GPA)

FROM Student;

6

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

7

Aggregate Functions:

• List average salary of cosc faculty:

​

SELECT AVG(NVL(salary,0)),

AVG (salary), COUNT(*)

FROM faculty

WHERE dept = ‘COSC’;

8

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;

9

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

​

​

10

Distinct:

11

SELECT DISTINCT (dept)

FROM Faculty;

​

​

​

​

User:

​

SELECT USER, UID

FROM DUAL;

12

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

​

13

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;

14

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;

15

Grouping

SELECT

FROM

[WHERE]

[GROUP BY]

[ORDER BY]

16

Grouping

• Average Salary of faculty members by department

​

SELECT dept, AVG(Salary)

FROM Faculty

GROUP BY dept;

​

17

Grouping

​

• List number of courses taken by each student

​

SELECT ID, COUNT(*)

FROM Student_Course

GROUP BY ID;

18

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;

19

Having

• Condition on Group:

​

SELECT

FROM

[WHERE]

[GROUP BY]

[HAVING]

[ORDER BY]

20

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 ;

21

Having

• SELECT dept, MAX(salary)

FROM faculty

GROUP BY Department

HAVING MAX(salary)>50000;

22

Having

• SELECT dept, MAX(salary) a

FROM faculty

GROUP BY Dept

HAVING a >50000

ORDER BY MAX(salary);

23

Having

• SELECT dept, MAX(SUM(salary))

FROM faculty

GROUP BY dept;

24

Illegal Queries:

• SELECT name, count(*) FROM Department;

25

• SELECT name, count(*)

FROM Department

GROUP BY name;

​

Illegal Queries:

26

• SELECT name, AVG(salary)

FROM Department

WHERE AVG(salary) >5000;

​

SELECT name, AVG(salary)

FROM Department

GROUP BY name

HAVING AVG(salary) >5000;

JOIN:

• Definition
• General Format:

​

• SELECT col1,col2,….

FROM table1, table2,…

WHERE conditions;

27

JOIN:

• List of students’ name with the grade = 'A'

• SELECT Name

FROM Student_Course, Student

WHERE Student.ID =

Student_Course.ID AND Grade =‘A’;

28

JOIN:

• Aliases:

​

  SELECT Name

FROM Student_Course sc,

Student s

WHERE s.ID = sc.ID AND Grade =‘A’;

​

29

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;

30

JOIN

• Equijoin/Natural:

​

SELECT Name

FROM Student_Course, Student

WHERE Student.ID = Student_Course.ID ;

​

​

SELECT Name

FROM Student_Course

NATURAL JOIN Student ;

31

JOIN

• Equijoin/Natural:

​

​

SELECT Name

FROM Student_Course

INNER JOIN Student ;

32

JOIN

• Inner Join:

​

SELECT a.Name

FROM Student_Course b

INNER JOIN Student a

ON a.ID = b.ID ;

​

​

33

JOIN

• Equijoin:

​

​

​

​

​

SELECT department.num_faculty, faculty.name

FROM department, faculty

WHERE department.name=faculty.dept;

34

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;

35

JOIN

• Outer Join:

​

List of books that have been checked out:

SELECT DISTINCT title

FROM CheckedBook ;

​

​

36

JOIN

• Outer Join:

​

List of books that have not been checked out:

SELECT DISTINCT title

FROM ShelfBook ;

​

​

37

JOIN

• Outer Join:

​

List of all books:??

SELECT DISTINCT title

FROM ShelfBook a,

CheckedBook b

WHERE a.ID=b.ID ;

​

​

38

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 ;

​

​

39

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 ;

​

​

40

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 ;

​

​

41

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 ;

​

​

42

JOIN

• Outer Join:

​

• SELECT department.num_faculty, faculty.name

FROM department, faculty

WHERE department.name(+)=faculty.dept

​

43

JOIN

• Outer Join:

​

• SELECT department.num_faculty, faculty.name

FROM

department LEFT OUTER JOIN faculty ON department.name=faculty.dept;

​

44

JOIN

• Self Join

​

SELECT a.Name

FROM Student a, Student b

WHERE a.ID > b.ID AND

b.Name = 'SMITH';

​

45

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

46