Chapter 4 Relational Data Retrieval: SQL
4-1
Last Update: November 9, 2011 — 8AM
Chapter 4: Relational Data Retrieval: SQL
Multiple Choice
1. 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
2. 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
3. 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
4. The SQL CREATE TABLE command is capable of doing all of the following, except
____.
Chapter 4 Relational Data Retrieval: SQL
4-2
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
5. 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
6. 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.
7. All of the following are SQL commands, except ____.
a. CREATE TABLE
b. CREATE SELECT
c. CREATE INDEX
d. CREATE VIEW
e. DROP TABLE
8. The SQL SELECT command is used to ____ a relational database.
a. retrieve data from
b. update records in
Chapter 4 Relational Data Retrieval: SQL
4-3
c. insert new records into
d. delete records from
e. None of the above.
9. 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
10. 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
11. 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
12. 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
Chapter 4 Relational Data Retrieval: SQL
4-4
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.
13. 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
14. 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
15. 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
16. In an SQL SELECT command, a SELECT clause with several attributes and the
absence of a WHERE clause indicates a(n) ____.
Chapter 4 Relational Data Retrieval: SQL
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.
17. 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.
18. 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
19. 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
4-6
20. 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.
21. 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.
22. 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.
23. 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
Chapter 4 Relational Data Retrieval: SQL
4-7
e. None of the above.
24. 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
25. 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.
26. 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.
27. 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
Chapter 4 Relational Data Retrieval: SQL
4-8
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.
28. 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
29. 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
30. 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
Chapter 4 Relational Data Retrieval: SQL
4-9
31. A subquery can be used in place of a(n) ____.
a. GROUP BY
b. ORDER BY
c. Count
d. join
e. HAVING
32. 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.
33. 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.
34. 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
Chapter 4 Relational Data Retrieval: SQL
4-10
35. 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.
True/False
1. SQL incorporates features of both a data definition language and a data manipulation
language.
2. The SQL command to delete a table is DELETE TABLE.
3. SQL has commands that are capable of creating and dropping indexes on relational
tables.
4. The SQL SELECT command is used to update, insert, and delete rows in relational
tables.
Chapter 4 Relational Data Retrieval: SQL
5. The SQL SELECT command is capable of executing relational Select, Project, and
Join operations, singly or in combination.
6. 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.
7. SQL SELECT commands must be issued against the actual, physical database tables
rather than against “logical views” of the tables.
8. The tables used in a SELECT command are listed in the FROM clause.
9. Search arguments appear in the SQL SELECT command WHERE clause.
10. 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.