Lecture_10
SQL (Structured Query Language)
(Multi-table queries)
To combine columns from several tables into a result table, we need
to use a join operation
JOIN Two Tables
The SQL join operation combines information from two tables by forming pairs of related rows
from the two tables.
The row pairs that make up the joined table are those where the matching columns in each of the
two tables have the same value
INNER JOIN
SELECT catalogNo, title, category, v.directorNo, directorName
FROM Video v, Director d
WHERE v.directorNo = d.directorNo;
SELECT catalogNo, title, category, v.directorNo, directorName
FROM Video v, Director d
WHERE (Selection Condition) AND v.directorNo = d.directorNo;
INNER JOIN
SELECT catalogNo, title, category, v.directorNo, directorName
FROM Video v JOIN Director d
ON v.directorNo = d.directorNo
WHERE (Selection Condition);
SELECT catalogNo, title, category, v.directorNo, directorName
FROM Video v INNER JOIN Director d
ON v.directorNo = d.directorNo;
OUTER JOIN
Left OUTER join
Selects data starting from the left table and matching rows in
the right table.
The left join returns all rows from the left table and the matching rows from
the right table.
If a row in the left table does not have a matching row in the right
table, the columns of the right table will have null
OUTER JOIN
Right OUTER join
Selects data starting from the right table and matching rows in
the left table.
The right join returns all rows from the right table and the matching rows
from the left table.
If a row in the right table does not have a matching row in the left
table, the columns of the left table will have null
OUTER JOIN
FULL OUTER join
The full outer join or full join returns a result set that contains all
rows from both left and right tables, with the matching rows from
both sides where available. In case there is no match, the missing
side will have NULL values.
OUTER JOIN
SELECT catalogNo, title, category, v.directorNo, directorName
FROM Video v FULL OUTER JOIN Director d
ON v.directorNo = d.directorNo;
SELECT catalogNo, title, category, v.directorNo, directorName
FROM Video v LEFT OUTER JOIN Director d
ON v.directorNo = d.directorNo;
SELECT catalogNo, title, category, v.directorNo, directorName
FROM Video v RIGHT OUTER JOIN Director d
ON v.directorNo = d.directorNo;
JOIN four Tables
JOIN
SELECT v.catalogNo, title, category, directorName, actorName, character
FROM Video v, Director d, Actor a, Role r
WHERE d.directorNo = v.directorNo
AND v.catalogNo = r.catalogNo
AND r.actorNo = a.actorNo;
Implementation of different types of functions
Aggregate Functions
SQL (Structured Query Language)
(SELECT) Statement
Aggregate Functions
These functions operate on a single column of a table and return a single value
COUNT Returns the number of values in a specified column.
MIN Returns the minimum value in a specified column.
MAX Returns the maximum value in a specified column.
SUM Returns the sum of the values in a specified column.
AVG Returns the average of the values in a specified column.
SQL (Structured Query Language)
(SELECT) Statement
Aggregate Functions
Each function eliminates nulls first and operates only on the remaining nonnull values.
COUNT(*) is a special use of COUNT, which counts all the rows of a table, regardless of
whether nulls or duplicate values occur.
we want to eliminate duplicates before the function is applied, we use the keyword
DISTINCT before the column name in the function. DISTINCT has no effect with the MIN and
MAX functions. However, it may have an effect on the result of SUM or AVG,
SQL (Structured Query Language)
(SELECT) Statement
Aggregate Functions (COUNT and SUM)
We apply the COUNT function to count the number of rows satisfying the WHERE clause
and we apply the SUM function to add together the salaries in these rows
SELECT COUNT(staffNo) AS totalStaff, SUM(salary) AS totalSalary
FROM Staff
WHERE salary > 40000;
SQL (Structured Query Language)
(SELECT) Statement
Aggregate Functions(MIN , MAX and AVG)
SELECT MIN(salary) AS minSalary, MAX(salary) AS maxSalary, AVG(salary) AS avgSalary
FROM Staff;
SQL (Structured Query Language)
Aggregate Functions
SELECT staffNo, COUNT(salary)
FROM Staff;
The following query is illegal:
1- the query does not have a GROUP BY clause
2- the column staffNo in the SELECT list is used outside an aggregate
function.
SQL (Structured Query Language)
(Aggregate Functions)
GROUP BY
A query that includes the
GROUP BY clause is called a grouped query, because it groups the data
from the SELECT table(s) and produces a single summary row for each
group. The columns named in the GROUP BY clause are called the
grouping columns
SQL (Structured Query Language)
(Aggregate Functions)
GROUP BY
For each branch:
1- Computes the number of staff members and calculates the sum of the
values in the salary column to get the total of their salaries.
2- SQL generates a single summary row in the query result for each group.
3- Finally, the result is sorted in ascending order of branch number,
branchNo.
SQL (Structured Query Language)
(Aggregate Functions)
GROUP BY
SELECT branchNo, COUNT(staffNo) AS totalStaff, SUM(salary) AS totalSalary
FROM Staff
GROUP BY branchNo
ORDER BY branchNo
Final Results
SQL (Structured Query Language)
(Aggregate Functions)
GROUP BY /Having
The HAVING clause is designed for use with the GROUP BY clause to restrict
the groups that appear in the final result table. HAVING and WHERE serve
different purposes
1- The WHERE clause filters individual rows going into the final result table.
2- HAVING filters groups going into the final result table.
3- It requires that column names used in the HAVING clause must also
appear in the GROUP BY list or be contained within an aggregate function.
SQL (Structured Query Language)
(Aggregate Functions)
GROUP BY /Having
SELECT branchNo, COUNT(staffNo) AS totalStaff, SUM(salary) AS totalSalary
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;
Final Results
SQL (Structured Query Language)
(Subqueries)
The use of a complete SELECT statement embedded within another SELECT
statement. The results of this inner SELECT statement (or subselect) are used
in the outer statement to help determine the contents of the final result.
A subselect can be used in the WHERE and HAVING clauses of an outer SELECT
statement, where it is called a subquery or nested query. Subselects may also
appear in INSERT, UPDATE, and DELETE statements.
