IT Quiz Chapter 8

____ is a relational set operator.

MINUS

"Union-compatible" means that the ____.

names of the relation attributes must be the same and their data types must be identical

A ____ is a query (SELECT statement) inside a query.

Subquery

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

Full Outer

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

Return

A(n) ____ is a block of code (containing standard SQL statements and procedural extensions) that is stored and executed at the DBMS server.

PSM

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.

Outer

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

Natural

Assume you are using the INTERSECT 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 INTERSECT operator?

2

Assume you are using the MINUS 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 MINUS operator?

8

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?

17

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?

15

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

144

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.

JOIN ON

In Oracle, ____ make(s) it possible to merge SQL and traditional programming constructs, such as variables, conditional processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops,) and error trapping.

Procedural SQL

In Oracle, the ____ function converts a date to a character string.

To_CHAR()

In a subquery, the ____ query is executed first.

Inner

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

Outer

Oracle recommends ____ for creating audit logs.

Triggers

The ANSI standard defines ____ type(s) of outer join(s)

Three

The Oracle ____ function compares an attribute or expression with a series of values and returns an associated value or a default value if no match is found.

DECODE

The Oracle ____ function returns the current date.

SYSDATE

The Oracle equivalent to an MS Access AutoNumber is a(n) ____.

Sequence

The Oracle string concatenation function is ____.

II

The PL/SQL block starts with the ____ clause.

BEGIN

The ____ data type is compatible with NUMBER.

SMALLINT

The ____ data type is considered compatible with VARCHAR(35).

CHAR(15)

The ____ function returns the current system date in MS Access.

DATE()

The ____ operator could be used in place of INTERSECT if the RDBMS does not support it.

IN

The ____ operator could be used in place of MINUS if the RDBMS does not support it.

NOT IN

The ____ pseudo-column is used to select the next value from a sequence.

NEXTVAL

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

UNION

The ____ statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not in the second.

MINUS

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;

"Old-style" Join

The statement SELECT * FROM T1, T2 produces a(n) ____ join.

Cross

The syntax for a left outer join is ____.

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

The syntax for the UNION query is ____.

query UNION query

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

Inner

When using the Oracle TO_DATE function, the code ____ represents a three-letter month name.

MON

When using the Oracle TO_NUMBER function to convert a character string into a number, ____ represents a digit.

9