SQL – Aggregate & Join

Source:Darcy Benoit, Acadia University

 Student database
Student ( number, street, city, province, postal_code,
name, date_of_birth)
Faculty (number, name, rank, phone, office, email,
dcode, salary)
Department (code, name, start_date, end_date, fnum)
Section (number, cnum, dcode, term, slot, faculty_num)
Course (number , dcode, title, description)
Enrolled (student_num, section_num, cnum, dcode)
Dept_phones (dcode, phone_number)
 DML - Data Manipulation
Language
4 main SQL statements:

– INSERT
– UPDATE
– DELETE
– SELECT
 Insert (using the enter key)

INSERT INTO course VALUES

(3753, ‘COMP’, ‘DBMS’, ‘Database Management Systems’)

INSERT INTO course (number, dcode, title)

VALUES (3753, ‘COMP’, ‘DBMS’)

INSERT INTO course (number, dcode, title)

VALUES (3753, ‘COMP’, ‘DBMS’),
(3713, ‘COMP’, ‘OS’)
 Update
UPDATE TABLE course
SET description = ‘A fun course!’
WHERE title = ‘DBMS’

UPDATE TABLE course

SET description = ‘A fun course!’
WHERE number = 3753 AND
dcode = ‘COMP’
 Delete

DELETE FROM course

•deletes the whole table

DELETE FROM course where dcode=‘HIST’

•deletes the tuples where dcode=‘HIST’
 Queries (SELECT)
• Retrieval of some information from the
database
• Result Set contains the “answer” for the
query.
• Result “Sets” can contain duplicate rows
• Single value Result Sets are considered to
be one row with one column
• Result Sets can be empty
 Simple Format

SELECT <columns>
FROM <tables>
WHERE <condition>

Where:

• Columns = list of attributes to be retrieved

• Tables = list of relations needed to process the query
• Condition = a boolean expression that identifies which
tuples are to be retrieved
 Example

SELECT <columns>
FROM <tables>
WHERE <condition>

Example:

SELECT title, description

FROM universe.course
WHERE dcode=‘COMP’
 Contd…
• SQL allows duplicate tuples in the result
set

• For example, retrieving “cnum” and

“dcode” from Section, we will get identical
tuples for courses with multiple sections.
 Contd…
• If you are trying to select on a field where
the data type is VARCHAR(), then this
select will do:
– SELECT * FROM foo WHERE name=‘BOB’
• If you are trying to select on a field where
the data type is CHAR(5), then this select
will do:
– SELECT * FROM foo WHERE name=‘BOB ’
 Contd…
• Remember that selects on CHAR and
VARCHAR types are case sensitive. The
SELECTS:
– SELECT * FROM foo WHERE name=‘BOB’
– SELECT * FROM foo WHERE name=‘Bob’
– SELECT * FROM foo WHERE name=‘BoB’
• are all different to the DBMS.
 Example 1
List course number, department code, title
and description of each course.
SELECT number, dcode, title, description
FROM course

OR
SELECT *
"select all attributes"
FROM course
 Example 2
Find the faculty number and name of each
faculty member in Computer Science with
the rank of “Assistant”

SELECT number, name

FROM faculty
WHERE dcode = ‘COMP’ and rank = ‘Assistant’
 Example 3

Which computer science courses are being

taught in the second term?

SELECT cnum
FROM section
WHERE dcode = ‘COMP’ and term = ‘X2’

The result has only one column called “cnum”

 Aggregate Functions
• Aggregate functions cannot be expressed
in regular relational algebra.
• Aggregate functions include simple
mathematical functions that are useful in a
DBMS environment such as:
– sum, average, maximum, minimum, count
 Aggregate Functions (cont’d)
Find the average salary of associate
professors.

SELECT AVG(salary) AS AverageSalary

FROM faculty
WHERE rank=‘Associate’

• The result has one column called

“AverageSalary” with only one tuple.
 Aggregate Functions (con’t)
How many professors make more than
60,000 INR?
SELECT COUNT(*) as NumProfs
FROM faculty
WHERE salary > 30000

• The result has one column called “NumProfs”

with only one tuple.
 Aggregate Functions (con’t)
How many different salary levels are paid to
assistant professors?

SELECT COUNT(DISTINCT salary)

FROM faculty
WHERE rank = ‘Assistant’

• The result will be one column (which is

nameless) with one tuple.
 Grouping
• Sometimes useful to group rows of a table
together then apply a function to each group
separately

SELECT rank, AVG(salary) AS AV,

MIN(salary)AS MinSalary, MAX(salary) AS MaxSalary
FROM faculty
GROUP BY rank

• The result has 4 columns (rank, AV, MinSalary,

MaxSalary) and one tuple for each rank.
 Expressions in SELECT
• What is the difference between the start
and end dates in the department table?
SELECT sdate, edate, (edate-sdate) AS TimeDiff
FROM department
• The result has 3 attributes (sdate, edate, timeDiff)
• timeDiff is returned in a 8 digit number where the
first 4 digits are the years, the next two are the
months, and the final two are the days.
 The “Like” Predicate
Retrieve the average salary of professors
with offices in Carnegie Hall.

SELECT AVG(salary) AS CarnegieSalary

FROM faculty
WHERE office LIKE ‘CAR%’
 Sorting
• rows of result relation can be sorted by
values in one or more columns

SELECT name, salary

FROM faculty
WHERE salary > 40000
ORDER BY salary
 Sorting
Show all salaries less than 40000. Sort the
output according to price.

SELECT * SELECT *
FROM faculty FROM faculty
WHERE salary < 40000 WHERE salary < 40000
ORDER BY salary ORDER BY salary DESC

Lowest to Highest Highest to Lowest

 Retrieval Using a Subquery
• Find Name & Salary of all department
heads

SELECT name, salary

FROM faculty How else can this
WHERE number query be expressed?
IN
(SELECT fnum
FROM department)
 “IN”
• The “IN” clause is useful as it allows us to
match up on a set of data rather than just
a single piece of data.
• Assuming that the subquery will return
more than one item, we can match on
anything in the subquery.
 “EXISTS”
• We can check to see if a row exists in a
subquery.
• The subquery does not return any values
– only the existence of the row is checked.
• We can also use “NOT EXISTS” when the
cases arises.
 Contd…
• List the name, number and salary of all
professors who are not department heads.

SELECT name, number, salary

FROM faculty
WHERE NOT EXISTS
(SELECT *
FROM department
WHERE fnum = faculty.number)
 Join

Find Name & Salary of all department

heads
SELECT name, salary
FROM faculty, department
WHERE faculty.number = department.fnum

For a join, you need to specify all the tables

which you will use in getting the information.
 Contd…
Find the course title, number and section for all
sections taught by department heads.

SELECT section.number, course.number,

course.dcode, faculty.name
FROM section, course,
department, faculty
WHERE section.cnum = course.number
AND section.dcode = department.dcode
AND department.fnum = faculty.number
 Renaming (Aliasing)

Find the course title, number and section for all

sections taught by department heads.

SELECT S.number, C.number,

C.dcode, F.name
FROM section as S, course as C,
department as D, faculty as F
WHERE S.cnum = C.number
AND S.dcode = D.dcode
AND D.fnum = F.number
 Left Outer Join
A B A F
R= S=
C D G H

• If we do a left outer join on R and S, and we

match on the first column, the result is:

A B F
C D -
 Right Outer Join
A B A F
R= S=
C D G H

• If we do a right outer join on R and S, and we

match on the first column, the result is:

A B F
- G H
 Full Outer Join
A B A F
R= S=
C D G H

• If we do a full outer join on R and S, and we

match on the first column, the result is:

A B F
C D -
- G H
 Contd…

Find the department heads and course

sections that they teach, if any.

SELECT D.fnum, S.number, S.cnum

FROM department AS D
left outer join section AS S
ON D.fnum = S.faculty_num
 Set Operations
• Set operations DO exist in SQL
– UNION
– INTERSECT
– EXCEPT (difference)
 Union
Select all of the courses listed in section and all
of those where students are enrolled.

SELECT cnum, dcode

FROM section
UNION
SELECT cnum, dcode
FROM enrolled
Note: there is no good example of why we
would use UNION in our database…