Chapter 8

Question 1

____ is a relational set operator.

Answer 1

-MINUS

PLUS

ALL

EXISTS

Question 2

The ____ query combines rows from two queries and excludes duplicates.

Answer 2

-UNION

UNION ALL

INTERSECT

MINUS

Question 3

The syntax for the UNION query is ____.

Answer 3

query + query

UNION (query, query)

UNION: query query

-query UNION query

Question 4

Assume you are using the UNION operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the UNION operator?

Answer 4

7

10

-15

17

Question 5

Assume you are using the UNION ALL operator to combine the results from two tables with identical structure, CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains 7 rows. Customers Dunne and Olowski are included in the CUSTOMER table as well as in the CUSTOMER_2 table. How many records are returned when using the UNION ALL operator?

Answer 5

7

10

15

-17

Question 6

The PL/SQL block starts with the ____ clause.

Answer 6

IS

OPEN

DECLARE

-BEGIN

Question 7

A stored function uses the ____ statement to return a value.

Answer 7

EXIT

END

-RETURN

PROCESS

Question 8

UNION, INTERSECT, and MINUS work properly only if relations are union-compatible, which means that the names of the relation attributes and their data types must be different.

Answer 8

false

Question 9

SQL supports the conditional execution of procedures (if…then…else statements) that are typically supported by a programming language.

Answer 9

false

Question 10

Procedural code is executed on the database client machine.

Answer 10

false

Question 11

The following SQL statement uses a(n) ____.
SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

Answer 11

set operator

natural join

-“old-style” join

procedural statement

Question 12

When using a(n) ____ join, only rows that meet the given criteria are returned.

Answer 12

full

-inner

outer

set

Question 13

How many rows would be returned from a cross join of tables A and B, if A contains 8 rows and B contains 18?

Answer 13

8

18

26

-144

Question 14

A(n) ____ join will select only the rows with matching values in the common attribute(s).

Answer 14

-natural

cross

full

outer

Question 15

If you wish to create an inner join, but the two tables do not have a commonly named attribute, you can use a(n) ____ clause.

Answer 15

OF

USING

HAS

-JOIN ON

Question 16

A(n) ____ join returns not only the rows matching the join condition (that is, rows with matching values in the common columns) but also the rows with unmatched values.

Answer 16

-outer

inner

equi-

cross

Question 17

The syntax for a left outer join is ____.

Answer 17

SELECT column-list
FROM table1 OUTER JOIN table2 LEFT
WHERE join-condition

-SELECT column-list
FROM table1 LEFT [OUTER] JOIN table2
ON join-condition

SELECT column-list
WHERE LEFT table1 = table 2

SELECT column-list
FROM table1 LEFT table2 [JOIN]
WHERE join-condition

Question 18

A ____ join returns rows with matching values and includes all rows from both tables (T1 and T2) with unmatched values.

Answer 18

natural

cross

-full outer

left outer

Question 19

In subquery terminology, the first query in the SQL statement is known as the ____ query.

Answer 19

outer

left

-inner

base

Question 20

Improving ____ leads to more flexible queries.

Answer 20

-atomicity

normalization

denormalization

derived attributes

Question 21

The most likely data type for a surrogate key is ____.

Answer 21

Character

Date

Logical

-Numeric

Question 22

From a system functionality point of view, ____ attribute values can be calculated when they are needed to write reports or invoices.

Answer 22

-derived

atomic

granular

historical

Question 23

For most business transactional databases, we should normalize relations into ____.

Answer 23

1NF

2NF

-3NF

6NF

Question 24

A table where every determinant is a candidate key is said to be in ____.

Answer 24

-BCNF

2NF

3NF

4NF

Question 25

Most designers consider the BCNF as a special case of the ____.

Answer 25

1NF

2NF

-3NF

4NF

Question 26

A table is in 4NF if it is in 3NF and ____.

Answer 26

all attributes must be dependent on the primary key, and must be dependent on each other

all attributes are unrelated

-has no multivalued dependencies

no column contains the same values

Question 27

Normalization represents a micro view of the ____ within the ERD.

Answer 27

-entities

attributes

relationships

forms

Question 28

The conflicts between design efficiency, information requirements, and processing speed are often resolved through ____.

Answer 28

conversion from 1NF to 2NF

conversion from 2NF to 3NF

-compromises that include denormalization

conversion from 3NF to 4NF