Transcript – Advanced SQL CS263 : Lecture 7

CS263 : Lecture 7 – Advanced SQL





Joins: The WHERE clause of SELECT command can be
used for multiple table operations (2, 3 or more tables)
Two ways to use SELECT for combining data from related
tables - Most frequently used is called a Join
SQL implicitly specifies a join by referring in a WHERE
clause to the matching of common columns over which
tables are joined
Result of the join operation is a single table - selected
columns from all the tables are included
Join = a relational operation that causes two or more tables
with a common domain to be combined into a single table
or view
Joins



Each row returned contains data from rows in the different
input tables where values for the common columns match
An important rule of thumb: there should be one condition
within the WHERE clause for each pair of tables being
joined. If two tables are combined, one condition would be
necessary, but if three tables (A, B, C) are to be combined,
then two conditions would be necessary because there are
two pairs of tables (A-B and B-C)
There are several types of joins, the most commonly used
are the following 4:
Equi-join





Equi-join – a join in which the joining condition is based on
equality between values in the common columns; common
columns appear redundantly in the result table
e.g. if we want to know the names of customers who have
placed orders, that information is kept in 2 tables
CUSTOMER_T and ORDER_T. If we want to find the
names of customers who have placed orders:
SELECT CUSTOMER_T.CUSTOMER_ID,
ORDER_T.CUSTOMER_ID, CUSTOMER_NAME,
ORDER_ID
FROM CUSTOMER_T, ORDER_T
WHERE CUSTOMER_T.CUSTOMER_ID =
ORDER_T.CUSTOMER_ID;
Natural join

An equi-join where one of duplicate columns is eliminated
in result table (most commonly used form of join operation)
 From the previous example, one CUSTOMER_ID would
be left out
 Notice that CUSTOMER_ID must still be qualified as it
exists in both CUSTOMER_T and ORDER_T
 e.g. for each customer who placed an order, what is the
customer’s name and order number?
 SELECT CUSTOMER_T.CUSTOMER_ID,
CUSTOMER_NAME, ORDER_ID
 FROM CUSTOMER_T, ORDER_T
 WHERE CUSTOMER_T.CUSTOMER_ID =
ORDER_T.CUSTOMER_ID
Outer join

Often find that row in one table does not have a matching row in the
other table
 e.g., several CUSTOMER_ID numbers may not appear in the
ORDER_T table (maybe they have not ordered for a long time)
 As a result, the equi-join and natural join do not include all of the
customers in CUSTOMER_T
 Using an outer join rows that do not have matching values in common
columns are also included in the result table. Null values appear in
columns where there is not a match between the tables
 (as opposed to inner join, in which rows must have matching values in
order to appear in the result table)







e.g. List the customer name, ID number, and order number
for all customers. Include customer information even for
customers that do have an order:
SELECT CUSTOMER_T.CUSTOMER_ID,
CUSTOMER_NAME, ORDER_ID
FROM CUSTOMER_T, LEFT OUTER JOIN ORDER_T
WHERE CUSTOMER_T.CUSTOMER_ID =
ORDER_T.CUSTOMER_ID
Syntax LEFT OUTER JOIN selected as CUSTOMER_T
named first, and is table from which we want all rows
(regardless of whether there is matching order in the
ORDER_T table)
If we reversed order tables were listed, same results could
be obtained using a RIGHT OUTER JOIN
Also possible to request a FULL OUTER JOIN - all rows
matched and returned, including any that do not have a
match in the other table
Union join




Includes all columns from each table in the join, and an
instance for each row of each table, i.e. the result of a union
join is a table that includes all of the data from each table
that is joined
So a union join of the CUSTOMER_T table (15 customers
and 6 attributes) and the ORDER_T table (10 orders and 3
attributes) will return a results table of 25 rows and 9
columns
Assuming that each original table contained no nulls, each
customer row in the results table will contain 3 attributes
with assigned null values and each order row will contain 6
attributes with assigned null values
Do not confuse this command with the UNION command
for joining select STATEMENTS (discussed later)
Sample multiple join with 4 tables





This query on the next page produces a result table that
includes all the information needed to create an invoice for
order no. 1006.
We want the customer information, the order information,
the order line information and the product information (4
tables)
Since the join involves 4 tables, there will be 3 column join
conditions
Each pair of tables requires an equality-check condition in
the WHERE clause, matching primary keys against foreign
keys
Joining useful when data from several relations are to be
retrieved, and the relationships are not necessarily nested
4 table join



SELECT CUSTOMER_T.CUSTOMER_ID,
CUSTOMER_NAME, CUSTOMER_ADDRESS, CITY,
SATE, POSTAL_CODE, ORDER_T.ORDER_ID,
ORDER_DATE, QUANTITY, PRODUCT_NAME,
UNIT_PRICE, (QUANTITY * UNIT_PRICE)
FROM CUSTOMER_T, ORDER_T, ORDER_LINE_T,
PRODUCT_T
WHERE CUSTOMER_T.CUSTOMER_ID =
ORDER_LINE.CUSTOMER_ID AND
ORDER_T.ORDER_ID = ORDER_LINE_T.ORDER_ID
AND ORDER_LINE_T.PROUCT_ID =
PRODUCT_PRODUCT_ID
AND ORDER_T.ORDER_ID = 1006;
Results from a four-table join
From CUSTOMER_T table
From ORDER_T table
From PRODUCT_T table
Subqueries (nested subqueries)

Placing an inner query (SELECT, FROM, WHERE) within
a WHERE or HAVING clause of another (outer) query
 The inner query provides values for the search condition of
the outer query
 There can be multiple levels of nesting
 Useful alternative to joining when there is nesting of
relationships
 The following queries both answer the question ‘what is the
name and address of the customer who placed order
number 1008?’
 The second version uses the subquery technique
Subqueries

SELECT CUSTOMER_NAME, CUSTOMER_ADDRESS,
CITY, STATE, POSTAL_CODE
 FROM CUSTOMER_T, ORDER_T

WHERE CUSTOMER_T.CUSTOMER_ID =
ORDER_T.CUSTOMER_ID AND ORDER_ID = 1008;
 ------------------------------------------------------------------------
Subqueries

SELECT CUSTOMER_NAME, CUSTOMER_ADDRESS,
CITY, STATE, POSTAL_CODE
 FROM CUSTOMER_T

WHERE CUSTOMER_T.CUSTOMER_ID =

(SELECT ORDER_T.CUSTOMER_ID

FROM ORDER_T WHERE ORDER_ID = 1008);
Subqueries



The subquery approach may be used for this query because
we only need to display data from the table in the outer
query
The value for ORDER_ID does not appear in the query
result - it is used as a selection criterion in the outer query
To include data from the subquery in the result, we should
use a join technique (since data from a subquery cannot be
included in the final results)
Subqueries





Another example - ‘which customers have placed orders’
The IN operator will test to see if the CUSTOMER_ID
value of a row is included in the list returned from the
subquery
Subquery is embedded in parentheses. In this case it returns
a list that will be used in the WHERE clause of the outer
query. DISTINCT is used because we do not care how
many orders a customer has placed as long as they have
placed an order
SELECT CUSTOMER_NAME FROM CUSTOMER_T
WHERE CUSTOMER_ID IN
(SELECT DISTINCT CUSTOMER_ID FROM
ORDER_T);
Subqueries

Following example shows use of NOT and demonstrates
using a join in an inner query
 ‘Which customers have not placed any orders for computer
desks?’
 SELECT CUSTOMER_NAME FROM CUSTOMER_T
 WHERE CUSTOMER_ID NOT IN
 (SELECT CUSTOMER_ID FROM ORDER_T,
ORDER_LINE_T, PRODUCT_T
 WHERE ORDER_T.ORDER_ID =
ORDER_LINE_T.ORDER_ID AND
ORDER_LINE_T.PRODUCT_ID =
PRODUCT_T.PRODUCT_ID AND PRODUCT_NAME =
‘Computer Desk’);
Subqueries




So here the inner query returned a list of all customers who
had ordered computer desks
The outer query listed the names of those customers who
were not in the list returned by the inner query
EXISTS and NOT EXISTS can be used in the same
location where IN would be (just prior to the beginning of
the subquery)
EXISTS will take a value of ‘true’ if the subquery returns
an intermediate results table which contains one or more
values, and ‘false’ if no rows are returned (the opposite for
NOT EXISTS)
Subqueries
e.g. ‘what are the order numbers for all orders that have
included furniture finished in natural ash?’
 SELECT DISTINCT ORDER_ID FROM
ORDER_LINE_T
 WHERE EXISTS
 (SELECT * FROM PRODUCT_T
 WHERE PRODUCT_ID =
ORDER_LINE_T.PRODUCT_ID
 AND PRODUCT_FINISH = ‘Natural Ash’);


Here subquery checks to see if finish for a product on an
order line is natural ash
 Main query picks out order numbers for all orders that have
included ‘Natural Ash’ finish
 Where EXISTS or NOT exists are used , select will usually
select all the columns (SELECT*) as a placeholder (doesn’t
matter which columns are returned - purpose of the
subquery is testing to see if any rows fit condition, not to
return values from particular columns)
 Columns displayed determined by the outer query
 Summary - use subquery approach when qualifications are
nested or more easily understood in a nested way
 Such subqueries are processed ‘inside out’, whilst another
type of subquery, the correlated subquery, is processed
‘outside in’
Correlated vs. noncorrelated subqueries




Non-correlated subqueries:
– Do not depend on data from the outer query
– Execute only once for all the rows processed in the
entire outer query
Correlated subqueries
Make use of the result of the outer query to determine the
processing of the inner query
The inner query is different for each row referenced in the
outer query – i.e. executes once for each row of the outer
query
Processing a
noncorrelated
subquery
No reference to data
in outer query, so
subquery executes
once only










e.g. – list all details about product with highest unit price
Here compare table to itself using two aliases, PA and PB
Firstly, PRODUCT_ID 1 (the end table) will be considered
When subquery is executed, will return prices of every
product except the one being considered in outer query
Then outer query checks if unit price for product being
considered is > all of unit prices returned by subquery
If so - returned as result, if not, next value in outer query
considered, and inner query returns list of all unit prices for
other products
List returned by inner query changes as each product in
outer query changes, this makes it a correlated subquery
SELECT PRODUCT_NAME, PRODUCT_FINISH,
UNIT_PRICE FROM PRODUCT_T PA
WHERE UNIT_PRICE > ALL
(SELECT UNIT_PRICE FROM PRODUCT_T PB
WHERE PB.PRODUCT_ID != PA.PRODUCT_ID);
Correlated subquery example
The EXISTS operator will return a TRUE value if the
subquery resulted in a non-empty set, otherwise it returns a
FALSE
 e.g. show all orders that include furniture finished in natural
ash

SELECT DISTINCT ORDER_ID FROM ORDER_LINE_T
 WHERE EXISTS
(SELECT * FROM PRODUCT_T
WHERE PRODUCT_ID = ORDER_LINE_T.PRODUCT_ID
AND PRODUCT_FINISH = ‘Natural ash’);
See following Fig.
Processing a
correlated
subquery Subquery refers to outer-query data, so executes
once for each row of outer query
Using derived tables

Subqueries are not limited to inclusion in the WHERE
clause, they may also be used in the FROM clause
 Here they create a temporary derived table that is used in
the query
 Creating a derived table that has an aggregate value in it
(such as MAX or AVG) allows the aggregate to be used in
the WHERE clause
 e.g. which products have a standard price that is higher than
the average standard price?
One column of the subquery is an aggregate function that has
an alias name. That alias can then be referred to in the outer
query
Using derived tables
Subquery forms the derived table used in the FROM clause of
the outer query
 SELECT PRODUCT_DESCRIPTION,
STANDARD_PRICE, AVGPRICE
 FROM
(SELECT AVG(STANDARD_PRICE) AVGPRICE
FROM PRODUCT_T),
PRODUCT_T
WHERE STANDARD_PRICE > AVG_PRICE;
The WHERE clause normally cannot include aggregate
functions, but because the aggregate is performed in the
subquery its result can be used in the outer query’s
WHERE clause
Combining queries






The UNION clause is used to combine the output from
multiple queries together into a single result table
To use UNION, each query must output the same number
of rows
They must also be union-compatible, i.e. the output from
each query for each column should be of Compatible
datatypes (compatibility varies among products)
Can use the CAST command to control datatype conversion
e.g., the DATE datatype in ORDER_T may need to
converted to a text datatype:
SELECT CAST(ORDER_DATE AS CHAR) FROM
ORDER_T
Combining queries


Following query (over 2 pages) determines the customer(s)
who have purchased the largest and smallest quantities of
any product, and returns the results in one table
Notice that the expression QUANTITY is created in which
the strings ‘Smallest Quantity’ and ‘Largest Quantity’ have
been inserted for readability. The ORDER BY clause has
been used to organise the order in which the rows of output
are listed
Combining queries







SELECT C1.CUTOMER_ID, CUSTOMER_NAME,
ORDERED_QUANTITY,’LARGEST QUANTITY’
QUANTITY
FROM CUSTOMER_T C1, ORDER_T O1,
ORDER_LINE_T Q1
WHERE C1.CUSTOMER_ID = O1.CUSTOMER_ID
AND O1.ORDER_ID = Q1.ORDER_ID
AND ORDERED_QUANTITY =
(SELECT MAX(ORDERED_QUANTITY) FROM
ORDER_LINE_T)
UNION {query on next page}
Combining queries

SELECT C1.CUSTOMER_ID, CUSTOMER_NAME,
ORDERED_QUANTITY, ‘SMALLEST QUANTITY’
 FROM CUSTOMER_T C1, ORDER_T O1,
ORDER_LINE_T Q1
 WHERE C1.CUSTOMER_ID = O1.CUSTOMER_ID
 AND O1.ORDER_ID = Q1.ORDER_ID
 AND ORDERED_QUANTITY =
 (SELECT MIN(ORDERED_QUANTITY) FROM
ORDER_LINE_T)
 ORDER_BY ORDERED_QUANTITY
CS263 Lec. 8: Advanced SQL and SQL in practice




Data dictionary = system tables that store metadata, e.g:
– DBA_TABLES – descriptions of all tables in database
– DBA_CONSTRAINTS – description of constraints
– DBA_USERS – information about the users of the system
– DBA_TAB_PRIVS – descriptions of grants on objects in the
database
Users usually can view some of tables (begin with USER or ALL tables only Database Administrator can use begin with DBA )
Users restricted from updating them, since DBMS maintains them and
depends on them for its processing
In Oracle there are over 100 data dictionary views
SQL-99 enhancements/extensions






SQL-99 Standard not widely adopted yet - Oracle has propriety
version called PL/SQL
User-defined data types (UDT) - subclasses of standard types or an
object type
Analytical functions (for OLAP/Data visualisation) - many
mathematical/statistical and related functions
Persistent Stored Modules (SQL/PSM) - capability to create, store
(across user sessions) and drop code modules.
New statements such as CASE, IF, LOOP, FOR, WHILE, REPEAT
Introduce procedurality into SQL (statements are processed
sequentially) - base SQL is a non-procedural language (no statement
execution sequence implied)
Routines and triggers






Stored in the database and controlled by the DBMS
Promote stronger data integrity and consistency of use within database
Since they are stored once, code maintenance is simplified
Both consist of blocks of procedural code
Trigger code stored in database - runs automatically whenever the
triggering event (such as an UPDATE) occurs
Routines do not run automatically - have to be called in to operate






Since triggers are stored and executed in the database, they execute against all
applications that access the database
Triggers can also cascade, causing other triggers to fire
They can be used to ensure referential integrity, enforce business rules, create
audit trails etc.
Constraints can be thought of as a special case of triggers, as they are applied
automatically as a result of data modification commands (though they are not
as flexible as triggers)
Triggers have 3 parts, the event, the condition and the action
The following trigger will automatically insert the order number whenever a
new order is added
Triggers









BIR stands for Before Insert Row, also requires that a sequence
ID_SEQUENCE has been previously defined
CREATE TRIGGER ORDER_ID_BIR
BEFORE INSERT ON ORDER_T
FOR EACH ROW
BEGIN
SELECT ID_SEQUENCE.NEXTVAL
INTO: NEW.ORDER_ID
FROM DUAL;
END ORDER_ID_BIR;
Triggers





Triggers may occur either before or after the statement that aroused the trigger
is executed
They may occur on INSERT, UPDATE or DELETE commands
They may fire once for each time a row is affected, or they may fire once per
statement
Care should be taken when using them, since they fire automatically the user
will be unaware of them
One trigger can cause another to fire, can easily end up with an endless loop of
triggers

Routines can be Functions – (return values and take input parameters) or
Procedures – (do not return values and can take input or output parameters) –
example procedure =
CREATE OR REPLACE PROCEDURE PRODUCT_LINE_SALE
AS BEGIN
UPDATE PRODUCT_T
SET SALE_PRICE = 0.90*STANDARD_PRICE
WHERE STANDARD_PRICE >= 400;
UPDATE PRODUCT_T
SET SALE_PRICE = 0.85*STANDARD_PRICE
WHERE STANDARD_PRICE < 400;

END (To run this procedure we would use: EXEC PRODUCT_LINE_SALE)








Triggers contrasted with routines
Procedures are called explicitly
Triggers are event-driven
Using SQL: Identify entities and attributes
EmpNo
Emp
Job
Name
Hire Date
Salary
Dept
Location
Name
DeptNo
Comm
Identify relationships
Managed by
Manage
Emp
Works in
Assigned
Dept
Integrity Rules – derived from ER Diagram:
Each employee may be managed by one other employee
Each employee may manage one or more other employees
Each employee must work in a single department
Each department may be assigned one or more employees
Create a relational schema
Dept (DeptNo, Name, Location)
Emp (EmpNo, Name, Job, Sal, Comm, HireDate, Mgr, DeptNo)
Dept
DeptNo
Name
Location
Number(2)
Varchar2(14)
Varchar2(13)
Emp
EmpNo
Name
Job
Sal
Comm
HireDate
Mgr
DeptNo
Number(4)
Varchar2(10)
Varchar2(9)
Number(7,2)
Number(7,2)
Date
Number(4)
Number(2)
Create relational tables
To create a relation in SQL the following ‘Create Table’ command is required:
create table R (A1 D1, A2 D2, … An Dn,
integrity constraint1, integrity constarint2)
Where: R = Relation (table) name
A = Attribute name
D = Attribute domain
create table Dept (deptno number(2), name varchar2(14), location varchar2(13),
constraint DeptPK primary key (deptno));
create table Emp (empno number(4), name varchar2(10), job varchar2(9),
sal number(7,2), comm number(7,2), hiredate date, mgr number(4),
deptno number(2),
constraint EmpPK primary key (empno),
constraint EmpFK1 foreign key (mgr) references Emp,
constraint EmpFK2 foreign key (deptno) references Dept);
Populate relational tables
To create a tuple in SQL the following ‘Insert’ command is required:
insert into R (attribute1, attribute2, … attributen )
values (value1, value2, … valuen)
insert into Dept (deptno, name, location )
values (10, ‘Accounting’, ‘New York’)
insert into Dept (deptno, name, location )
values (30, ‘Sales’, ‘Chicago)
The insert order matters in
terms of referential integrity
constraints!
insert into Emp (empno, name, job, sal, comm, hiredate, mgr, deptno )
values (7839, ‘King’, ‘President’, 5000, NULL, ‘17-Nov-81’, NULL, 10)
insert into Emp (empno, name, job, sal, comm, hiredate, mgr, deptno )
values (7698, ‘Blake’, ‘Manager’, 1600, NULL, ’01-May-81’, 7839, 30)
Query relational tables
To query a relation in SQL the following ‘Select’ command is required:
SELECT [ALL | DISTINCT] attribute1, attribute2, … attributen
FROM relation1, relation2, … relationn
[WHERE condition-expression]
[GROUP BY attribute1, attribute2, … attributen ]
[HAVING condition-expression]
[ORDER BY attribute1, attribute2, … attributen ]
Simple Example: list all Employees and the departments they work in
select empno, name, deptno
from Emp;
7839
7698
King
Blake
10
30
Query relational tables
Simple Example: list all Employees that work in department 30
select empno, name
from Emp
where deptno = 30;
7698
Blake
Simple Example: list all Employees that work in either department 10 or 30
select empno, name
from Emp
where deptno = 10 or deptno = 30;
7839
7698
King
Blake
Query relational tables - Join
Example: list Employee and Department names of all employees that work
in either department 10 or 30
select emp.name, dept.name
from Emp, Dept
where (emp.deptno = 10
or emp.deptno = 30);
King
King
Blake
Blake
Accounting
Sales
Accounting
Sales
select emp.name, dept.name
from Emp, Dept
where (emp.deptno = dept.deptno)
and (emp.deptno = 10 or emp.deptno = 30)
King
Blake
Accounting
Sales
Query relational tables – Order by
select emp.name, dept.name
from Emp, Dept
where (emp.deptno = dept.deptno)
and (emp.deptno = 10 or emp.deptno = 30)
order by emp.name asc;
select emp.name, dept.name
from Emp, Dept
where (emp.deptno = dept.deptno)
and (emp.deptno = 10 or emp.deptno = 30)
order by dept.name desc;
select name
from Dept
order by name;
Accounting
Sales
Blake
King
Sales
Accounting
Blake
King
Sales
Accounting
Remember in relations
neither tuples nor attributes
have any intrinsic order!
Example relations
Emp
Dept
EMPNO
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
DEPTNO
10
20
30
40
NAME
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
JOB
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
NAME
ACCOUNTING
RESEARCH
SALES
OPERATIONS
MGR
7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
HIREDATE
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82
LOCATION
NEW YORK
DALLAS
CHICAGO
BOSTON
SAL
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
950
3000
1300
COMM DEPTNO
20
300
30
500
30
20
1400 30
30
10
20
10
0
30
20
30
20
10
Query relational tables - Outer Join
Example: list all departments and the names of staff that work in them.
select dept.name, emp.name
from Emp, Dept
where (emp.deptno (+) = dept.deptno)
order by dept.name, emp.name;
Accounting
Accounting
Accounting
Operations
Research
Research
Research
Research
Research
Sales
Sales
Sales
Sales
Sales
Sales
Clark
King
Miller
Adams
Ford
Jones
Scott
Smith
Allen
Blake
James
Martin
Turner
Ward
Query relational tables – Group by
Example: List employee’s departments giving a count of employees in each.
select deptno
from Emp
order by deptno;
10
10
10
20
20
20
20
20
30
30
30
30
30
30
select deptno
from Emp
group by deptno;
10
20
30
Aggregate Function!
select deptno, count(*)
from Emp
group by deptno;
10
20
30
3
5
6
Select queries can contain functions and
calculations as well as attribute names in
the select condition!
Query relational tables – Group by
select deptno, sum(sal), sum(sal)/count(*)
from Emp
group by deptno;
10
20
30
8750 2916.6667
10875 2175
9400 1566.6667
As we are dealing with groups of tuples, rather than individual tuples, there
are only certain types of data that can be selected: 1) attributes named in the
group by clause; 2) group aggregate functions; 3) expressions involving
combinations of 1) and 2).
Example: List employee’s departments giving a count of employees in each,
provided that there are over four employees in the department.
select deptno, count(*)
from Emp
group by deptno
having count(*) > 4;
20
30
5
6
The having clause is used in Group
Selections in the same way that the
where clause is used in standard
tuple selections.
Query relational tables – Group by
Combined Example: List, in reverse order of average department salary, the
total salary and average salary for each department that has employees, and
where the average salary of each department is over £2000
select deptno, sum(sal), sum(sal)/count(*)
from Emp
group by deptno
having sum(sal)/count(*) > 2000
order by sum(sal)/count(*) desc;
10 8750 2916.6667
20 10875 2175
The result of an SQL Select is, as shown, a new (unnamed) relation. The
attributes of this new relation are named on the basis of the select statement.
DEPTNO
10
20
SUM(SAL)
8750
10875
SUM(SAL)/COUNT(*)
2916.6667
2175
ALIAS:- select deptno, sum(sal) “Total Salary”, sum(sal)/count(*) “Avg Salary”
DEPTNO
10
20
Total Salary
8750
10875
Avg Salary
2916.6667
2175
Query relational tables – Inner Selects
As the result of an SQL Select on a relation(s) is itself a relation, it follows
that, as with Relational Algebra, the result of one select can be used as the
input into another SQL Select!
However, rather than create, and name, a separate relation to contain the
output of the first select and then use this relation in the second select, it is
possible to ‘pipe’ the output of the first select directly into the second select!
Example: List employee’s who earn more than the average company salary.
2
select name, sal
from Emp
where sal > (select AVG(sal) from Emp)
order by name;
1 2073.2143, is substituted
for this select statement
NAME SAL
BLAKE 2850
CLARK 2450
FORD
3000
JONES 2975
KING
5000
SCOTT 3000
Query relational tables – Inner Selects
Remember: Inner Selects (also called sub-selects or sub-queries) are fullbodied SQL Select statements: Therefore, they can, when required to do so,
return more than a single value (one tuple, one attribute) relation.
Example: List employees who earn the maximum salary in each of the
departments.
select name, deptno, sal
from Emp
where (deptno, sal) in (select deptno, max(sal)
from emp
group by deptno)
order by deptno;
This relation is substituted for
the inner select statement
DEPT
10
20
30
NAME DEPT SAL
KING
10
5000
SCOTT 20
3000
FORD
20
3000
BLAKE 30
2850
MAX(SAL)
5000
3000
2850
Query relational tables – Inner Selects
Note: It is sometimes necessary to make reference to an attribute value from
the outer select, within the Where Clause of the inner select. This can be
achieved by using a Relation Alias!
Example: List employee’s who earn more than the average salary in the
department they work in.
select name, deptno, sal
Relation Alias
from Emp E1
where sal > (select AVG(sal) from Emp
where deptno = E1.deptno
group by deptno)
order by deptno;
NAME DEPT SAL
KING
10
5000
JONES 20
2975
SCOTT 20
3000
FORD
20
3000
ALLEN 30
1600
BLAKE 30
2850
The deptno attribute value of the current tuple will be substituted here! If
there are 14 employee tuples there will be 14 separate substitutions (even
though there are only three departments)!
SQL set operations – Union
Example: list all staff that work in either of two departments (each dept. has
a separate database), showing their staff number, and date of birth.
DepB
DepA
staffno
SL10
SA51
DS40
dob
14-02-64
21-11-82
01-01-40
select staffno, dob
from DepA
UNION
select staffno, dob
from DepB;

staffno dob
CC15 11-03-66
SA51 21-11-82
staffno
SL10
SA51
DS40
CC15
dob
14-02-64
21-11-82
01-01-40
11-03-66
SQL set operations – Intersection
Example: list all staff that work in both departments (each dept. has a
separate database), showing their staff number, and date of birth.
DepB
DepA
staffno
SL10
SA51
DS40
dob
14-02-64
21-11-82
01-01-40
select staffno, dob
from DepA
INTERSECT
select staffno, dob
from DepB;

staffno dob
CC15 11-03-66
SA51 21-11-82
staffno dob
SA51 21-11-82
SQL set operations – difference
Example: list all staff that only work in department A (each dept. has a
separate database), showing their staff number, and date of birth.
DepB
DepA
staffno
SL10
SA51
DS40
dob
14-02-64
21-11-82
01-01-40
select staffno, dob
from DepA
MINUS
select staffno, dob
from DepB;
staffno dob
CC15 11-03-66
SA51 21-11-82
staffno dob
SL10 14-02-64
DS40 01-01-40
SQL - Group Insert statement
To create a set of tuples in SQL the following ‘Insert’ command can be used:
insert into R (attribute1, attribute2, … attributen )
select (attribute1, attribute2, … attributen)
from relation1, relation2, … relationn
[where condition-expression]
[group by attribute1, attribute2, … attributen ]
[having condition-expression]
[order by attribute1, attribute2, … attributen ]
Example: copy details of all employees that work in department 10 from
the Emp relation into the DepA relation.
insert into DepA (staffno, name, job, hiredate)
select empno, name, job, hiredate
from Emp
where deptno = 10;
corresponding
attributes have to
be of the same type
Each tuple to be inserted has to be unique!
SQL - Delete statement: Be Careful!
To delete a set of tuples in SQL the following ‘Delete’ command is used:
delete from R
[where condition-expression]
Example: remove details of all employees that work in department 10
from the Emp relation.
Delete from Emp
where deptno = 10;
If the where clause is omitted then all
tuples in the relation will be removed!