Chapter 4 Relational Data Retrieval: SQL
4-12
11. A basic SQL SELECT command is capable of performing a combination of relational
Select and Project operations.
12. A basic SQL SELECT command is capable of retrieving a single column value from
a single row of a table.
13. To retrieve an entire table, an SQL SELECT statement must specify the rows in the
WHERE clause.
14. All of the standard comparison operators can be use in the SQL SELECT command
WHERE clause.
15. The OR operator in the SQL SELECT command WHERE clause means one or the
other, but not both.
16. If several AND and OR operators appear in an SQL SELECT command WHERE
clause and they are not separated by parentheses, all of the OR operations will be
performed before any of the AND operations.
17. The BETWEEN operator in the SQL SELECT command WHERE clause requires
that a character string must be between two specified character strings.
Chapter 4 Relational Data Retrieval: SQL
18. The LIKE operator in the SQL SELECT command WHERE clause allows the use of
wildcards to substitute for character strings.
19. The DISTINCT operator in the SQL SELECT command removes duplicate rows
from a query result.
20. The ORDER BY operator in the SQL SELECT command orders a query result’s
output rows by a set of attribute values.
21. The built-in function COUNT in an SQL SELECT command adds a specified set of
attribute values.
22. A GROUP BY clause in an SQL SELECT command groups together columns based
on common attribute values.
23. Once a GROUP BY clause in an SQL SELECT command groups together rows
based on common attribute values, a built-in function can produce a result for each
group.
24. The HAVING clause limits the results of a GROUP BY clause based on the values
calculated for each group with the built-in functions.
25. Only two tables can be joined in an SQL SELECT statement.
26. The tables to be joined in an SQL SELECT statement are listed in the FROM clause.
27. When tables are to be joined in an SQL SELECT statement, the join attributes from
each table are set equal to each other in the FROM clause.
28. In an SQL SELECT statement, a subquery can accomplish the same function as a
join.
29. The use of a subquery in an SQL SELECT statement is always optional.
30. When a subquery is present in an SQL SELECT statement, the subquery is executed
before the main query.
4-15
31. A relational query optimizer is a separate piece of software that can be called in by
the relational DBMS when it has to respond to an especially complex query.
32. A relational query optimizer is an expert system within a relational DBMS.
33. A relational DBMS’s relational query optimizer obtains the information it needs
about the database from the DBMS’s relational catalog.
34. For the nested-loop join to work, each of the two join attributes either has to be in
sorted order or has to have an index built over it.
Problems
1. Consider the following relational database for the Central Zoo. Central Zoo wants to
maintain information about its animals, the enclosures in which they live, and its
zookeepers and the services they perform for the animals. In addition, Central Zoo
has a program by which people can be sponsor of animals. Central Zoo wants to
track its sponsors, their dependents, and associated data.
Each animal has a unique animal number and each enclosure has a unique enclosure
number. An animal can live in only one enclosure. An enclosure can have several
animals in it or it can be currently empty. A zookeeper has a unique employee
number. Every animal has been cared for by at least one and generally many
zookeepers; each zookeeper has cared for at least one and generally many animals.
Each time a zookeeper performs a specific, significant service for an animal the
service type, date, and time are recorded. A zookeeper may perform a particular
service on a particular animal more than once on a given day.
Chapter 4 Relational Data Retrieval: SQL
4-16
A sponsor, who has a unique sponsor number and a unique social security number,
sponsors at least one and possibly several animals. An animal may have several
sponsors or none. For each animal that a particular sponsor sponsors, the zoo wants
to track the annual sponsorship contribution and renewal date. In addition, Central
Zoo wants to keep track of each sponsor’s dependents. A sponsor may have several
dependents or none. A dependent is associated with exactly one sponsor.
ENCLNUM
TYPE
LOCATION
SIZE
DATEBUILT
ENCLOSURE Relation
ANIMALNUM
SPECIES
ANIMALNAME
COUNTRY
WEIGHT
ENCLNUM
ANIMAL Relation
EMPNUM
EMPNAME
TITLE
YEARHIRED
ZOOKEEPER Relation
ANIMALNUM
EMPNUM
SERVICETYPE
DATE
TIME
CARESFOR Relation
SPONSORNUM
SOCSECNUM
SPONSORNAME
ADDRESS
PHONE
SPONSOR Relation
ANIMALNUM
SPONSORNUM
ANNUALCONT
RENEWDATE
CONTRIBUTION Relation
SPONSORNUM
DEPENNAME
RELATIONSHIP
BIRTHDATE
DEPENDENT Relation
Write SQL SELECT commands to answer the following queries.
a. Find the size of enclosure number 2582.
4-17
4627.
c. List the animal number, species, gender, and weight of all of the lions, tigers, and
leopards that weigh between 700 and 900 pounds, from heaviest to lightest.
d. Find the average weight of all of the male tigers.
e. Find the average weight of the males of each species.
f. List the names, addresses, and phone numbers of all of the sponsors of tigers that
were born in India.
g. How many monkeys live in cages that are less than 400 square feet in size?
h. Find the average weight of each species of animal that that live in cages that are
less than 400 square feet in size. Only include those species for which the
average weight is at least 200 pounds.
i. Find the name and number of the heaviest tiger.
j. Find the name, number, and species of the animals from China that are at least as
heavy as the heaviest female tiger that was born in India.
Answer
Chapter 4 Relational Data Retrieval: SQL
4-18
Chapter 4 Relational Data Retrieval: SQL
4-19
2. Consider the following relational database for Grand Travel Airlines.
Grand Travel Airlines has to keep track of its flight and airplane history. A flight
is uniquely identified by the combination of a flight number and a date. Every
passenger who has flown on Grand Travel has a unique passenger number. For a
particular passenger who has taken a particular flight, the company wants to keep
track of the fare that she paid for it and the date that she made the reservation for
it. Clearly, a passenger may have taken many flights (he must have taken at least
one to be in the database) and every flight has had many passengers on it.
A pilot is identified by a unique pilot (or employee) number. A flight on a
particular date has exactly one pilot. Each pilot has typically flown many flights
but a pilot may be new to the company, is in training, and has not flown any
flights, yet. Each airplane has a unique serial number. A flight on a particular
date used one airplane. Each airplane has flown on many flights and dates, but a
new airplane may not have been used at all, yet.
PILOTNUM
PILOTNAME
BIRTHDATE
HIREDATE
PILOT Relation
FLIGHTNUM
DATE
DEPTIME
ARRTIME
PILOTNUM
PLANENUM
FLIGHT Relation
PASSNUM
PASSNAME
ADDRESS
PHONE
PASSENGER Relation
FLIGHTNUM
DATE
PASSNUM
FARE
RESVDATE
RESERVATION Relation
PLANENUM
MODEL
CAPACITY
YEARBUILT
MANUF
AIRPLANE Relation
Chapter 4 Relational Data Retrieval: SQL
4-20
Write SQL SELECT commands to answer the following queries.
a. Find the records for the airplanes manufactured by Boeing.
b. How many reservations are there for flight 278 on February 21, 2004?
c. List the flights on March 7, 2004 that are scheduled to depart between 10 and
11AM or that are scheduled to arrive after 3PM on that date.
d. How many Boeing 737s does Grand Travel have?
e. How many of each model of Boeing aircraft does Grand Travel have?
f. List the names and dates of hire of the pilots who flew Airbus A320 aircraft in
March, 2004.
g. List the names, addresses, and telephone numbers of the passengers who have
reservations on Flight 562 on January 15, 2004.
h. What was the total fare paid for each flight scheduled to depart between 9 and
10AM on December 23, 2003? Only include those flights for which the total fare
was at least $5,000.
i. List the smallest (in terms of passenger capacity) Boeing 737s.
j. List the Airbus A310s that are larger (in terms of passenger capacity) than the
smallest Boeing 737s.
Answer