C H A P T E R 4
SQL
Chapter 4 covers the relational language SQL. The discussion is based on SQL-92,
since the more recent SQL:1999 is not widely supported yet. Extensions provided by
SQL:1999 arecoveredlaterin [Link] andauthorization
features of SQL-92 are described in Chapter 6. SQL being a large language, many of
its
features are not covered here, and are not appropriate for an introductory course
on
databases. Standard books on SQL, such as Date and Darwen [1993] and Melton and
Simon [1993], or the system manuals of the database system you use can be used as
supplements for students who want to delve deeper into the intricacies of SQL.
Although it is possible to cover this chapter using only handwritten exercises, we
strongly recommend providing access to an actual database system that supports
SQL. A style of exercise we have used is to create a moderately large database and
give students a list of queries in English to write and run using SQL. We publish
the
actual answers (that is the result relations they should get, not the SQL they
must en
ter). By using a moderately large database, the probability that a “wrong” SQL
query
will just happen to return the “right” result relation can be made very small.
This
approach allows students to check their own answers for correctness immediately
rather than wait for grading and thereby it speeds up the learning process. A few
such example databases are available on the Web home page of this book.
Exercises that pertain to database design are best deferred until after Chapter 7.
Given the fact that the ODBC and JDBC protocols are fast becoming a primary
means of accessing databases, we have significantly extended our coverage of these
two protocols, including some examples. However, our coverage is only introduc
tory, and omits many details that are useful in practise. Online tutorials/manuals
or
textbooks covering these protocols should be used as supplements, to help students
make full use of the protocols.
Changes from 3rd edition:
Ourcoverage of SQL hasbeenexpandedto include the withclause,ODBC, JDBC,and
schemas, catalogs and environments (Section 4.14).
41
42
Chapter 4
SQL
Exercises
4.1 Consider the insurance database of Figure 4.12, where the primary keys are un
derlined. Construct the following SQL queries for this relational database.
a. Find the total number of people who owned cars that were involved in ac
cidents in 1989.
b. Find the number of accidents in which the cars belonging to “John Smith”
were involved.
c. Add a new accident to the database; assume any values for required at
tributes.
d. Delete the Mazdabelonging to “John Smith”.
e. Updatethe damageamountfor thecarwithlicense number“AABB2000”in
the accident with report number “AR2197” to $3000.
Answer: Note: The participated relation relates drivers, cars, and accidents.
a. Find the total number of people who owned cars that were involved in ac
cidents in 1989.
Note: this is not the same as the total number of accidents in 1989. We
must count people with several accidents only once.
select
from
count (distinct name)
accident, participated, person
where [Link]-number = [Link]-number
and
[Link]-id = [Link]-id
and
date between date ’1989-00-00’and date ’1989-12-31’
b. Find the number of accidents in which the cars belonging to “John Smith”
were involved.
select
from
count (distinct *)
accident
where exists
(select *
from participated, person
where [Link]-id = [Link]-id
and [Link] = ’John Smith’
and [Link]-number = [Link]-number)
c. Add a new accident to the database; assume any values for required at
tributes.
We assume the driver was “Jones,” although it could be someone else.
Also, we assume “Jones” owns one Toyota. First we must find the license of
the given car. Then the participated and accident relations must be updated
in order to both record the accident and tie it to the given car. We assume
values “Berkeley” for location, ’2001-09-01’ for date and date, 4007 for report
number and 3000 for damageamount.
Exercises
43
person (driver-id, name, address)
car (license, model, year)
accident (report-number, date, location)
owns (driver-id, license)
participated (driver-id, car, report-number, damage-amount)
Figure [Link].
insert into accident
values (4007, ’2001-09-01’, ’Berkeley’)
insert into participated
select [Link]-id, [Link], 4007, 3000
from person p, owns o, car c
where [Link] = ’Jones’ and [Link]-id = [Link]-id and
[Link] = [Link] and [Link] = ’Toyota’
d. Delete the Mazdabelonging to “John Smith”.
Since model is not a key of the car relation, we can either assume that only
one of John Smith’s cars is a Mazda, or delete all of John Smith’s Mazdas
(the query is the same). Again assume name is a key for person.
delete car
where model = ’Mazda’ and license in
(select license
from person p, owns o
where [Link] = ’John Smith’ and [Link]-id = [Link]-id)
Note: The owns, accident and participated records associated with the Mazda
still exist.
e. Updatethe damageamountforthe carwithlicense number“AABB2000”in
the accident with report number “AR2197” to $3000.
update participated
set damage-amount = 3000
where report-number = “AR2197” and driver-id in
(select driver-id
from owns
where license = “AABB2000”)
4.2 Consider the employee database of Figure 4.13, where the primary keys are un
derlined. Give an expression in SQL for each of the following queries.
a. Find the names of all employees who work for First Bank Corporation.
b. Find the names and cities of residence of all employees who work for First
Bank Corporation.
c. Find the names, street addresses, and cities of residence of all employees
whoworkfor First Bank Corporation and earn more than $10,000.
44
Chapter 4
SQL
d. Find all employees in the database who live in the same cities as the com
panies for which they work.
e. Find all employees in the database who live in the same cities and on the
same streets as do their managers.
f. Find all employees in the database who do not work for First Bank Corpo
ration.
g. Find all employees in the database who earn more than each employee of
Small Bank Corporation.
h. Assume that the companies may be located in several cities. Find all com
panies located in every city in which Small Bank Corporation is located.
i. Find all employees who earn more than the averagesalary of all employees
of their company.
j. Find the company that has the most employees.
k. Find the company that has the smallest payroll.
l. Find those companies whose employees earn a higher salary, on average,
than the average salary at First Bank Corporation.