数据库系统概念答案第六版（全）
第三章
C H A P T E R 3
Introduction to SQL
Exercis
3.1 Write the following queries in SQL, using the university schema. (We suggest you actually run the queries on a databa, using the sample data
that we provide on the Web site of the book, Instructions for
tting up a databa, and loading sample data, are provided on the above
Web site.)
a. Find the titles of cours in the Comp. Sci. department that have 3
credits.
b. Find the IDs of all students who were taught by an instructor named
Einstein; make sure there are no duplicates in the result.
c. Find the highest salary of any instructor.
d. Find all instructors earning the highest salary (there may be more
than one with the same salary).
e. Find the enrollment of each ction that was offered in Autumn 2009.
f. Find the maximum enrollment, across all ctions, in Autumn 2009.
g. Find the ctions that had the maximum enrollment in Autumn 2009.
Answer:
a. Find the titles of cours in the Comp. Sci. department that have 3
credits.
lect title
from cour
如何腌辣椒where dept name = ’Comp. Sci.’
and credits = 3
5
6 Chapter 3 Introduction to SQL
b. Find the IDs of all students who were taught by an instructor named
Einstein; make sure there are no duplicates in the result.
This query can be answered in veral different ways. One way is as
follows.
lect distinct student.ID
from (student join takes using(ID))
join (instructor join teaches using(ID))
using(cour id, c id, mester, year)
where instructor.name = ’Einstein’
As an alternative to th join … using syntax above the query can be
written by enumerating relations in the from clau, and adding the
corresponding join predicates on ID, cour id,ction id,mester, and
year to the where clau.
Note that using natural join in place of join … using would result in
equating student ID with instructor ID, which is incorrect.
c. Find the highest salary of any instructor.
古诗咏鹅lect max(salary)
from instructor
d. Find all instructors earning the highest salary (there may be more
than one with the same salary).
lect ID, name
from instructor
where salary = (lect max(salary) from instructor)
e. Find the enrollment of each ction that was offered in Autumn 2009.
One way of writing the query is as follows.
lect cour id, c id, count(ID)
from ction natural join takes
where mester = ’Autumn’
and year = 2009
group by cour id, c id
Note that if a ction does not have any students taking it, it would
not appear in the result. One way of ensuring such a ction appears
with a count of 0 is to replace natural join by the natural left outer
join operation, covered later in Chapter 4. Another way is to u a
subquery in the lect clau, as follows.
Exercis 7
lect cour id, c id, (lect count(ID)
from takes
ar = ar
ster = ster
ur id = ur id
ion id = ion id)
from ction
where mester = ’Autumn’
and year = 2009
Note that if the result of the subquery is empty, the aggregate function count returns a value of 0.
f. Find the maximum enrollment, across all ctions, in Autumn 2009.
One way of writing this query is as follows:
lect max(enrollment)
from (lect count(ID) as enrollment
from ction natural join takes
where mester = ’Autumn’
and year = 2009
group by cour id, c id)
As an alternative to using a nested subquery in the from clau, it is
小鸭迷你洗衣机possible to u a with clau, as illustrated in the answer to the next
part of this question.
A subtle issue in the above query is that if no ction had any enrollment, the answer would be empty, not 0. We can u the alternative
using a subquery, from the previous part of this question, to ensure
the count is 0 in this ca.
g. Find the ctions that had the maximum enrollment in Autumn 2009.
The following answer us a with clau to create a temporary view,
simplifying the query.
with c enrollment as (
lect cour id, c id, count(ID) as enrollment
from ction natural join takes
where mester = ’Autumn’
and year = 2009
group by cour id, c id)
lect cour id, c id
from c enrollment
where enrollment = (lect max(enrollment) from c enrollment)
It is also possible to write the query without the with clau, but the
subquery to find enrollment would get repeated twice in the query.
8 Chapter 3 Introduction to SQL
3.2 Suppo you are given a relation grade points(grade, points), which provides
a conversion from letter grades in the takes relation to numeric scores; for
新型无机非金属材料example an “A” grade could be specified to correspond to 4 points, an “A−”
example an “A” grade could be specified to correspond to 4 points, an “A−”to 3.7 points, a “B+” to 3.3 points, a “B” to 3 points, and so on. The grade points earned by a student for a cour offering (ction) is defined as the number of credits for the cour multiplied by the numeric points for the
grade that the student received.
Given the above relation, and our university schema, write each of the
following queries in SQL. You can assume for simplicity that no takes tuple
has the null value for grade.
a. Find the total grade-points earned by the student with ID 12345,
across all cours taken by the student.
b. Find the grade-point average (GPA) for the above student, that is,
the total grade-points divided by the total credits for the associated
cours.
c. Find the ID and the grade-point average of every student.
Answer:
a. Find the total grade-points earned by the student with ID 12345,
across all cours taken by the student.
lect sum(credits * points)
from (takes natural join cour) natural join grade points
whereID = ’12345’
One problem with the above query is that if the student has not
蔬菜沙拉怎么做
taken any cour, the result would not have any tuples, whereas we
would expect to get 0 as the answer. One way of fixing this problem
is to u the natural left outer join operation, which we study later
in Chapter 4. Another way to ensure that we get 0 as the answer, is
to the following query:
(lect sum(credits * points)
from (takes natural join cour) natural join grade points
where ID = ’12345’)
union
(lect 0
from student
where takes.ID = ’12345’ and
not exists ( lect * from takes where takes.ID = ’12345’))
As usual, specifying join conditions can be specified in the where
clau instead of using the natural join operation or the join … using
operation.
Exercis 9
b. Find the grade-point average (GPA) for the above student, that is,
the total grade-points divided by the total credits for the associated
cours.
lect sum(credits * points)/sum(credits) as GPA
from (takes natural join cour) natural join grade points
where ID = ’12345’
As before, a student who has not taken any cour would not appear
in the above result; we can ensure that such a student appears in the
result by using the modified query from the previous part of this
question. However, an additional issue in this ca is that the sum
of credits would also be 0, resulting in a divide by zero condition.
In fact, the only meaningful way of defining the GPA in this ca is
to define it as null. We can ensure that such a student appears in the
result with a null GPA by adding the following union clau to the
above query.
union
(lect null as GPA
from student
from student
where takes.ID = ’12345’ and
描写荷花诗句
not exists ( lect * from takes where takes.ID = ’12345’))
Other ways of ensuring the above are discusd later in the solution
to Exerci 4.5.
c. Find the ID and the grade-point average of every student.
lect ID, sum(credits * points)/sum(credits) as GPA
from (takes natural join cour) natural join grade points
group by ID
Again, to handle students who have not taken any cour, we would
have to add the following union clau:
union
(lect ID, null as GPA
from student
where not exists ( lect * from takes where takes.ID = student.ID))
3.3
3.4 Write the following inrts, deletes or updates in SQL, using the university schema.
a. Increa the salary of each instructor in the Comp. Sci. department
by 10%.
b. Delete all cours that have never been offered (that is, do not occur
in the ction relation).
10 Chapter 3 Introduction to SQL
c. Inrt every student who tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.
Answer:
a. Increa the salary of each instructor in the Comp. Sci. department
by 10%.
古文情书update instructor
t salary = salary * 1.10
where dept name = ’Comp. Sci.’
b. Delete all cours that have never been offered (that is, do not occur
in the ction relation).
delete from cour
where cour id not in
(lect cour id from ction)
c. Inrt every student who tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.
inrt into instructor
lect ID, name, dept name, 10000
from student
where tot cred > 100
3.5 Consider the insurance databa of Figure ??, where the primary keys are underlined. Construct the following SQL queries for this relational databa.
a. Find the total number of people who owned cars that were involved
in accidents in 1989.
b. Add a new accident to the databa; assume any values for required attributes.
c. Delete the Mazda belonging to “John Smith”.
Answer: Note: The participated relation relates drivers, cars, and accidents.
摄影师简历
a. Find the total number of people who owned cars that were involved
in accidents in 1989.
Note: this is not the same as the total number of accidents in 1989.
We must count people with veral accidents only once.
lect count (distinct name)
from accident, participated, person
port number = port number
and participated.driver id = person.driver id
and date between date ’1989-00-00’ and date ’1989-12-31’Exercis 11
person (driver id, name, address)
car (licen, model, year)
accident (report number, date, location)
owns (driver id, licen)
participated (driver id, car, report number, damage amount)
Figure ??. Insurance databa.
b. Add a new accident to the databa; assume any values for required attributes.
We assume the driver was “Jones,” although it could be someone
el. Also, we assume “Jones” owns one Toyota. First we must find
the licen of the given car. Then the participated and accidentrelations 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 damage amount. inrt into accident
values (4007, ’2001-09-01’, ’Berkeley’)
inrt into participated
lect o.driver id, c.licen, 4007, 3000
from person p, owns o, car c
where p.name = ’Jones’ and p.driver id = o.driver id and
o.licen = c.licen del = ’Toyota’
c. Delete the Mazda belonging 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 licen in
(lect licen
from person p, owns o
where p.name = ’John Smith’ and p.driver id = o.driver id)
Note: The owns, accident and participated records associated with the Mazda still exist.
