Deck 4: Relational Data Retrieval: SQL

Question 1

The SQL SELECT command is capable of executing ____.

A) only relational Select operations
B) only relational Select and Project operations but not relational Join operations
C) relational Select, Project, and Join operations individually but not in combination
D) relational Select, Project, and Join operations individually or in combination
E) None of the above.

Answer 1

D

Question 2

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

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

Answer 2

A

Question 3

All of the following are SQL commands, except ____.

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

Answer 3

B

Question 4

The SQL CREATE TABLE command is capable of doing all of the following, except ____.

A) building indexes
B) specifying which attributes are unique
C) specifying which attributes serve as the primary key
D) specifying referential integrity constraints
E) specifying which attributes serve as foreign keys

Question 5

A data definition language (DDL) ____.

A) is the same as a data manipulation language
B) is limited to defining the size and type of attributes
C) specifies what tables will be in the database, what attributes will be in the tables, and which attributes will be indexed
D) specifies the programming language statements that will be used to access the data in a relational database
E) specifies the operating system parameters that will permit the relational DBMS to function within its environment

Question 6

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) ____.

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 7

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

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 8

In a relational database, a logical view ____.

A) is a physical duplicate of part of a table
B) is a physical duplicate of all of a table
C) is created with the SQL CREATE TABLE command
D) must be built over a single table, i.e. it cannot involve joined tables
E) None of the above.

Question 9

In an SQL SELECT command, a SELECT clause with several attributes and the absence of a WHERE clause indicates a(n) ____.

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 10

A plan for the steps to take to respond to a relational query is called a(n) ____.

A) retrieval path
B) access path
C) acquisition path
D) data path
E) indexed path

Question 11

In the SQL SELECT command, the search arguments that limit the rows involved in the query are specified in the ____ clause.

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

Question 12

In the SQL SELECT command, the attributes to be retrieved are specified in the ____ clause.

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

Question 13

Structured Query Language (SQL) ____.

A) is capable of retrieving data from a relational database
B) is capable of defining the tables, attributes, and indexes of a relational database
C) is a relational DBMS
D) a and b above
E) a, b, and c above

Question 14

All of the following are true about the use of AND and OR operators in an SQL SELECT command, except ____.

A) AND has higher precedence than OR
B) AND and OR can be used together with any of the comparison operators
C) OR means one condition or the other or both
D) enclosing an AND or an OR operation in parentheses ensures that it will be performed before those outside of parentheses
E) either the AND or the OR operator may be used in a given SQL SELECT command, but not both

Question 15

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

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

Question 16

Typing SQL SELECT commands in at a keyboard and sending them directly to the relational DBMS for processing is referred to as ____.

A) online mode
B) query mode
C) programming
D) embedded
E) retrieval mode

Question 17

The SQL SELECT IN operator ____.

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 18

The SQL SELECT BETWEEN operator is used ____.

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 19

Placing SQL SELECT commands among the lines of a higher-level language program if referred to as ____.

A) online mode
B) query mode
C) programming
D) embedded
E) retrieval mode

Question 20

A subset of the columns and/or rows of a table is called a ____.

A) logical view
B) virtual view
C) physical view
D) partial view
E) index view

Question 21

The SQL SELECT HAVING clause limits the output of a GROUP BY operation based on ____.

A) individual attribute values in the groups
B) calculated values of the built-in functions
C) the arrangement of rows specified by the ORDER BY clause
D) the query's output conforming to the range specified by the BETWEEN operator
E) the query's output conforming to the values specified by the IN operator

Question 22

The SQL command to delete a table is DELETE TABLE.

Question 23

SQL has commands that are capable of creating and dropping indexes on relational tables.

Question 24

The purpose of the SQL SELECT GROUP BY clause is ____.

A) to present rows of a table with common attribute values together as groups in the query output
B) to present columns of a table with common attribute values together as groups in the query output
C) to group together rows of a table with common attribute values in order to perform calculations using the built-in functions on attributes within each group
D) to group together columns of a table with common attribute values in order to perform calculations using the built-in functions on attributes within each group
E) None of the above.

Question 25

SQL incorporates features of both a data definition language and a data manipulation language.

Question 26

All of the following features of a relational query or of the relational database that it accesses are considered by the relational query optimizer, except ____.

A) whether a search argument in the query is a unique attribute in the database
B) whether a search argument in the query involves an indexed attribute
C) the number of rows of output the query produces
D) the number of rows of the database tables involved
E) whether a join attribute in the query involves an indexed attribute

Question 27

The SQL SELECT built-in function COUNT ____.

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 28

The SQL SELECT ORDER BY operator ____.

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.

Question 29

The SQL SELECT LIKE operator ____.

A) involves looking for numeric values close to a specified numeric value
B) involves numeric values in between two specified numeric values
C) involves the use of wildcards
D) involves limiting duplicate values in the output of an SQL SELECT command
E) None of the above.

Question 30

In a merge-scan join ____.

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 31

When an SQL SELECT command has a subquery within it ____.

A) the subquery is executed first
B) the subquery is executed last
C) there is just one SELECT clause in the entire command, including the subquery
D) all predicates in the subquery's WHERE clause are passed back to the main query
E) all predicates in the main query's WHERE clause are passed down to the subquery

Question 32

A relational query optimizer optimizes ____.

A) the performance of a relational query
B) the performance of a relational catalog
C) a higher-level language program that has embedded SQL SELECT commands
D) the data normalization process
E) None of the above.

Question 33

A relational query optimizer ____.

A) is a form of a relational catalog
B) is a separate piece of software that can be added to the relational DBMS environment as an option
C) can be invoked at the option of the user issuing the query
D) is only used with queries involving joins
E) None of the above.

Question 34

A subquery can be used in place of a(n) ____.

A) GROUP BY
B) ORDER BY
C) Count
D) join
E) HAVING

Question 35

The SQL SELECT command is used to update, insert, and delete rows in relational tables.

Question 36

The SQL SELECT GROUP BY clause is designed to ____.

A) group together the rows of a table that have a common attribute value
B) group together the columns of a table that have a common attribute value
C) group together the attributes of a table that have a common domain of values
D) group together the attributes of a group of tables that have a common domain of values
E) None of the above.

Question 37

The SQL SELECT command is capable of executing relational Select, Project, and Join operations, singly or in combination.

Question 38

The SQL SELECT DISTINCT operator ____.

A) eliminates duplicate rows in the output of an SQL SELECT command
B) eliminates duplicate columns in the output of an SQL SELECT command
C) eliminates duplicate attribute values within a row of the output of an SQL SELECT command
D) eliminates duplicate attribute values within a column of the output of an SQL SELECT command
E) None of the above.

Question 39

All of the following are true regarding a join operation in an SQL SELECT command, except ____.

A) it requires at least two tables to be specified in the FROM clause
B) it can involve the specification of more than two tables in the FROM clause
C) it requires corresponding join attributes to be set equal to each other in the WHERE clause
D) it can also involve relational Select and Project operations in the same SQL SELECT command
E) there will be at least two tables in the query output because at least two tables were involved in the join

Question 40

The SQL SELECT built-in function that adds the values of a specified set of attributes is ____.

A) COUNT
B) MIN
C) MAX
D) SUM
E) AVG

Question 41

The LIKE operator in the SQL SELECT command WHERE clause allows the use of wildcards to substitute for character strings.

Question 42

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.

Question 43

The DISTINCT operator in the SQL SELECT command removes duplicate rows from a query result.

Question 44

The tables used in a SELECT command are listed in the FROM clause.

Question 45

The OR operator in the SQL SELECT command WHERE clause means one or the other, but not both.

Question 46

The ORDER BY operator in the SQL SELECT command orders a query result's output rows by a set of attribute values.

Question 47

The BETWEEN operator in the SQL SELECT command WHERE clause requires that a character string must be between two specified character strings.

Question 48

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

Question 49

Only two tables can be joined in an SQL SELECT statement.

Question 50

A basic SQL SELECT command is capable of performing a combination of relational Select and Project operations.

Question 51

To retrieve an entire table, an SQL SELECT statement must specify the rows in the WHERE clause.

Question 52

The only way to retrieve all of the fields of a table with an SQL SELECT command is to list them all in the SELECT clause.

Question 53

In the SQL SELECT command embedded mode, the user types the command at a workstation and presses the Enter key. The command goes directly to the relational DBMS, which evaluates the query and processes it against the database.

Question 54

A basic SQL SELECT command is capable of retrieving a single column value from a single row of a table.

Question 55

Search arguments appear in the SQL SELECT command WHERE clause.

Question 56

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

Question 57

A GROUP BY clause in an SQL SELECT command groups together columns based on common attribute values.

Question 58

All of the standard comparison operators can be use in the SQL SELECT command WHERE clause.

Question 59

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

Question 60

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.

Question 61

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

Question 62

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

Question 63

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

Question 64

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.

Question 65

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

Question 66

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.

Question 67

In an SQL SELECT statement, a subquery can accomplish the same function as a join.

Question 68

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.

Question 69

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.
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.
ENCLOSURE Relation
ANIMAL Relation
ZOOKEEPER Relation
CARESFOR Relation
SPONSOR Relation
CONTRIBUTION Relation
DEPENDENT Relation
Write SQL SELECT commands to answer the following queries.
a. Find the size of enclosure number 2582.
b. List the names and numbers of the female tigers who live in enclosure number 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.

Question 70

The use of a subquery in an SQL SELECT statement is always optional.

Question 71

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.
PILOT Relation
FLIGHT Relation
PASSENGER Relation
RESERVATION Relation
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.