Question 1

The SQL SELECT command is used to ____ a relational database.

Accepted Answer

A)  retrieve data from 
B)  update records in 
C)  insert new records into 
D)  delete records from 
E)  None of the above. 
A)  retrieve data from 
B)  update records in 
C)  insert new records into 
D)  delete records from 
E)  None of the above. A

Question 2

The SQL SELECT ORDER BY operator ____.

Accepted Answer

A)  groups together rows with identical values of a specified attribute 
B)  groups together columns with identical values of a specified attribute 
C)  changes the order of the rows of one of the tables in a Join operation 
D)  changes the order of the columns of one of the tables in a Join operation 
E)  None of the above. 
A)  groups together rows with identical values of a specified attribute 
B)  groups together columns with identical values of a specified attribute 
C)  changes the order of the rows of one of the tables in a Join operation 
D)  changes the order of the columns of one of the tables in a Join operation 
E)  None of the above. E

Question 3

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.
 $$\begin{array} { | l | l | l | l | } 
\hline \text { PLOTNUM } & \text { PILOTNAME } & \text { BIRTHDATE } & \text { HIREDATE } \
\hline
\end{array}$$ PILOT Relation
 $$\begin{array} { | l | l | l | l | l | l|} 
\hline \text { FLIGHTNUM } & \text { DATE } & \text { DEPTIME } & \text { ARRTIME } & \text { PLINNUM } & \text { PLANENUM }\
\hline
\end{array}$$ FLIGHT Relation
 $$\begin{array} { | l | l | l | l | } 
\hline \text { PASSNUM } & \text { PASSNAME } & \text { ADDRESS } & \text { PHONE } \
\hline
\end{array}$$ PASSENGER Relation
 $$\begin{array} { | l | l | l | l | l | } 
\hline\text { FLIGHTNUM } & \text { DATE } & \text { PASSNUM } & \text { FARE } & \text { RESVDATE } \
\hline
\end{array}$$ RESERVATION Relation
 $$\begin{array} { | l | l | l | l | l | } 
\hline \text { PLANENUM } & \text { MODEL } & \text { CAPACITY } & \text { YEARBULL } & \text { MANUF }\
\hline
\end{array}$$ AIRPLANE Relation
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.

Accepted Answer

a. SELECT *
FROM AIRPLANE
WHERE MANUF='Boeing';

b. SELECT COUNT(*)
FROM RESERVATION
WHERE FLIGHTNUM=278
AND DATE='FEB-21-2004;

c. SELECT DISTINCT FLIGHTNUM
FROM FLIGHT
WHERE DATE='MAR-07-2004'
AND (DEPTIME BETWEEN 1000 AND 1059
OR ARRTIME>1500);

d. SELECT COUNT(*)
FROM AIRPLANE
WHERE MANUF='Boeing'
AND MODEL='737';

e. SELECT MODEL, COUNT(*)
FROM AIRPLANE
WHERE MANUF='Boeing'
GROUP BY MODEL;

f. SELECT PILOTNAME, HIREDATE
FROM PILOT, FLIGHT, AIRPLANE
WHERE PILOT.PILOTNUM=FLIGHT.PILOTNUM
AND FLIGHT.PLANENUM=AIRPLANE.PLANENUM
AND MANUF='Airbus'
AND MODEL='A320'
AND DATE BETWEEN 'MAR-01-2004' AND 'MAR-31-2004';

g. SELECT PASSNAME, ADDRESS, PHONE
FROM PASSENGER, RESERVATION
WHERE PASSENGER.PASSNUM=RESERVATION.PASSNUM
AND FLIGHTNUM=562
AND DATE='JAN-15-2004';

h. SELECT FLIGHT.FLIGHTNUM, SUM(FARE)
FROM FLIGHT, RESERVATION
WHERE FLIGHT.FLIGHTNUM=RESERVATION.FLIGHTNUM
AND FLIGHT.DATE=RESERVATION.DATE
AND RESERVATION.DATE='DEC-23-2003'
AND DEPTIME BETWEEN 900 AND 1000
GROUP BY FLIGHT.FLIGHTNUM
HAVING SUM(FARE)>=5000;

i. SELECT PLANENUM
FROM AIRPLANE
WHERE MANUF='Boeing'
AND MODEL='737'
AND CAPACITY=
(SELECT MIN(CAPACITY)
FROM AIRPLANE
WHERE MANUF='Boeing'
AND MODEL='737');

j. SELECT PLANENUM
FROM AIRPLANE
WHERE MANUF='Airbus'
AND MODEL='A310'
AND CAPACITY>
(SELECT MIN(CAPACITY)
FROM AIRPLANE
WHERE MANUF='Boeing'
AND MODEL='737');

Question 4

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.

Accepted Answer

A) True 
 B)False

Question 5

In an SQL SELECT command a SELECT clause with several attributes, a FROM clause with multiple tables and a WHERE clause with join fields set equal plus other predicates indicate a(n) ____.

Accepted Answer

A)  relational Select operation 
B)  relational Project operation 
C)  combination of relational Select and Project operations 
D)  combination of relational Select, Project, and Join operations 
E)  None of the above. 
A)  relational Select operation 
B)  relational Project operation 
C)  combination of relational Select and Project operations 
D)  combination of relational Select, Project, and Join operations 
E)  None of the above.

Question 6

Search arguments appear in the SQL SELECT command WHERE clause.

Accepted Answer

A) True 
 B)False

Question 7

The HAVING clause limits the results of a GROUP BY clause based on the values calculated for each group with the built-in functions.

Accepted Answer

A) True 
 B)False

Question 8

A data manipulation language (DML) is capable of ____.

Accepted Answer

A)  inserting new records or rows into a relation 
B)  deleting records or rows of a relation 
C)  retrieving data from a relation 
D)  a and b above 
E)  a, b, and c above 
A)  inserting new records or rows into a relation 
B)  deleting records or rows of a relation 
C)  retrieving data from a relation 
D)  a and b above 
E)  a, b, and c above

Question 9

In a merge-scan join ____.

Accepted Answer

A)  neither of the two join attributes has to be in sorted order or has to have an index built over it 
B)  one of the two join attributes either has to be in sorted order or has to have an index built over it 
C)  both of the join attributes have to be in sorted order or have to have indexes built over them 
D)  the only requirement is that every row of one table is compared against every row of the other table looking for a match on the join attributes. 
E)  None of the above. 
A)  neither of the two join attributes has to be in sorted order or has to have an index built over it 
B)  one of the two join attributes either has to be in sorted order or has to have an index built over it 
C)  both of the join attributes have to be in sorted order or have to have indexes built over them 
D)  the only requirement is that every row of one table is compared against every row of the other table looking for a match on the join attributes. 
E)  None of the above.

Question 10

A relational DBMS's relational query optimizer obtains the information it needs about the database from the DBMS's relational catalog.

Accepted Answer

A) True 
 B)False

Question 11

The SQL SELECT built-in function COUNT ____.

Accepted Answer

A)  adds the values of a specified set of attributes 
B)  adds the values of the numeric attributes in a row of the output of an SQL SELECT command 
C)  counts the number of columns in the output of an SQL SELECT command 
D)  counts the number of columns in the table resulting from a Join operation 
E)  None of the above. 
A)  adds the values of a specified set of attributes 
B)  adds the values of the numeric attributes in a row of the output of an SQL SELECT command 
C)  counts the number of columns in the output of an SQL SELECT command 
D)  counts the number of columns in the table resulting from a Join operation 
E)  None of the above.

Question 12

In the SQL SELECT command, the table(s) involved in the query is (are) specified in the ____ clause.

Accepted Answer

A)  SELECT 
B)  PROJECT 
C)  FROM 
D)  WHERE 
E)  RETRIEVE 
A)  SELECT 
B)  PROJECT 
C)  FROM 
D)  WHERE 
E)  RETRIEVE

Question 13

The built-in function COUNT in an SQL SELECT command adds a specified set of attribute values.

Accepted Answer

A) True 
 B)False

Question 14

All of the following are SQL commands, except ____.

Accepted Answer

A)  CREATE TABLE 
B)  CREATE SELECT 
C)  CREATE INDEX 
D)  CREATE VIEW 
E)  DROP TABLE 
A)  CREATE TABLE 
B)  CREATE SELECT 
C)  CREATE INDEX 
D)  CREATE VIEW 
E)  DROP TABLE

Question 15

A relational query optimizer is an expert system within a relational DBMS.

Accepted Answer

A) True 
 B)False

Question 16

When a subquery is present in an SQL SELECT statement, the subquery is executed before the main query.

Accepted Answer

A) True 
 B)False

Question 17

The SQL SELECT BETWEEN operator is used ____.

Accepted Answer

A)  to find a character string in between two specified character strings 
B)  to find a character string between a numeric value and a specified character string 
C)  to find a numeric value between two numeric values 
D)  to find a numeric value between a numeric value and a specified character string 
E)  to find a numeric value between two specified character strings 
A)  to find a character string in between two specified character strings 
B)  to find a character string between a numeric value and a specified character string 
C)  to find a numeric value between two numeric values 
D)  to find a numeric value between a numeric value and a specified character string 
E)  to find a numeric value between two specified character strings

Question 18

SQL SELECT commands must be issued against the actual, physical database tables rather than against "logical views" of the tables.

Accepted Answer

A) True 
 B)False

Question 19

The SQL SELECT IN operator ____.

Accepted Answer

A)  involves looking for numeric values and can be used instead of a series of AND operators 
B)  involves looking for numeric values and can be used instead of a series of OR operators 
C)  involves looking for character strings and can be used instead of a series of AND operators 
D)  involves looking for character strings and can be used instead of a series of OR operators 
E)  None of the above. 
A)  involves looking for numeric values and can be used instead of a series of AND operators 
B)  involves looking for numeric values and can be used instead of a series of OR operators 
C)  involves looking for character strings and can be used instead of a series of AND operators 
D)  involves looking for character strings and can be used instead of a series of OR operators 
E)  None of the above.

Question 20

The tables to be joined in an SQL SELECT statement are listed in the FROM clause.

Accepted Answer

A) True 
 B)False

The SQL SELECT command is used to ____ a relational database.

The SQL SELECT ORDER BY operator ____.

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.

In an SQL SELECT command a SELECT clause with several attributes, a FROM clause with multiple tables and a WHERE clause with join fields set equal plus other predicates indicate a(n) ____.

Search arguments appear in the SQL SELECT command WHERE clause.

The HAVING clause limits the results of a GROUP BY clause based on the values calculated for each group with the built-in functions.

A data manipulation language (DML) is capable of ____.

In a merge-scan join ____.

A relational DBMS's relational query optimizer obtains the information it needs about the database from the DBMS's relational catalog.

The SQL SELECT built-in function COUNT ____.

In the SQL SELECT command, the table(s) involved in the query is (are) specified in the ____ clause.

The built-in function COUNT in an SQL SELECT command adds a specified set of attribute values.

All of the following are SQL commands, except ____.

A relational query optimizer is an expert system within a relational DBMS.

When a subquery is present in an SQL SELECT statement, the subquery is executed before the main query.

The SQL SELECT BETWEEN operator is used ____.

SQL SELECT commands must be issued against the actual, physical database tables rather than against "logical views" of the tables.

The SQL SELECT IN operator ____.

The tables to be joined in an SQL SELECT statement are listed in the FROM clause.

Data: the New Corporate Resource

Data Modeling

The Database Management System Concept

The Relational Database Model: Introduction

The Relational Database Model: Additional Concepts

Logical Database Design

Physical Database Design

Object-Oriented Database Management

Data Administration, Database Administration, and Data Dictionaries

Database Control Issues: Security, Backup and Recovery, Concurrency

Clientserver Database and Distributed Database

The Data Warehouse

Database and the Internet

Filters

Exam 4: Relational Data Retrieval: SQL

The SQL SELECT command is used to ____ a relational database.

The SQL SELECT ORDER BY operator ____.

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.

In an SQL SELECT command a SELECT clause with several attributes, a FROM clause with multiple tables and a WHERE clause with join fields set equal plus other predicates indicate a(n) ____.

Search arguments appear in the SQL SELECT command WHERE clause.

The HAVING clause limits the results of a GROUP BY clause based on the values calculated for each group with the built-in functions.

A data manipulation language (DML) is capable of ____.

In a merge-scan join ____.

A relational DBMS's relational query optimizer obtains the information it needs about the database from the DBMS's relational catalog.

The SQL SELECT built-in function COUNT ____.

In the SQL SELECT command, the table(s) involved in the query is (are) specified in the ____ clause.

The built-in function COUNT in an SQL SELECT command adds a specified set of attribute values.

All of the following are SQL commands, except ____.

A relational query optimizer is an expert system within a relational DBMS.

When a subquery is present in an SQL SELECT statement, the subquery is executed before the main query.

The SQL SELECT BETWEEN operator is used ____.

SQL SELECT commands must be issued against the actual, physical database tables rather than against "logical views" of the tables.

The SQL SELECT IN operator ____.

The tables to be joined in an SQL SELECT statement are listed in the FROM clause.

Data: the New Corporate Resource

Data Modeling

The Database Management System Concept

The Relational Database Model: Introduction

The Relational Database Model: Additional Concepts

Logical Database Design

Physical Database Design

Object-Oriented Database Management

Data Administration, Database Administration, and Data Dictionaries

Database Control Issues: Security, Backup and Recovery, Concurrency

Clientserver Database and Distributed Database

The Data Warehouse

Database and the Internet

Filters