Chapter 8 Real Ting Set

The ____ function returns the number of the day in MS Access/SQLServer. a. TO_DATE() b. SYSDATE() c. DATE()

DATE

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. a. NVL b. TO_CHAR c. DECODE d. CONVERT

DECODE

In addition to a WHERE clause, a subquery can be used with a(n) ____ clause. a. ON b. HAVING c. GROUP BY d. MINUS

HAVING

In order to compare one value to a list of values returned by a subquery, you must use a(n) ____ operand. a. = b. MINUS c. ON d. IN

IN

What type of subquery could be used in place of INTERSECT if the RDBMS does not support it? a. IN b. OF c. AND d. UNION

IN

The ____ statement can be used to combine rows from two queries, returning only the rows that appear in both sets. a. UNION b. UNION ALL c. INTERSECT d. MINUS

INTERSECT

The Oracle ____ function returns the number of characters in a string value. a. LEN b. LENGTH c. SUM d. ASCII

LENGTH

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. a. UNION b. UNION ALL c. INTERSECT d. MINUS

MINUS

When using the Oracle TO_DATE function, the code ____ represents a three letter month name. a. MON b. MM c. MONTH d. M

MON

The ____ pseudo column is used to select the next available value from a sequence. a. CURRVAL b. NEXTVAL c. NEXT d. GET_NEXT

NEXTVAL

Complete the following inner join: SELECT * FROM T1 JOIN T2 ____ (C1) a. OF b. USING c. HAS d. JOIN ON

OF

What is the syntax for a left outer join? a. SELECT column-list FROM table1 OUTER JOIN table2 LEFT WHERE join-condition b. SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition c. SELECT column-list WHERE LEFT table1 = table 2 d. SELECT column-list FROM table1 LEFT table2 [JOIN] WHERE join-condition

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

Which data type is compatible with NUMBER? a. VARCHAR(15) b. SMALLINT c. DATE d. CHAR(10)

SMALLINT

The Oracle ____ function returns the current date. a. DATE b. SYSDATE c. CURRENT_DATE d. TO_DATE

SYSDATE

In Oracle, the ____ function converts a date to a character string. a. CONVERT b. TO_DATE c. TO_CHAR d. TO_STRIN

TO_CHAR

The ____ statement combines rows from two or more queries without including duplicate rows. a. UNION b. UNION ALL c. INTERSECT d. MINUS

UNION

The most common type of subquery uses an inner SELECT subquery on the right side of a(n) ____ comparison expression. a. WHERE b. JOIN c. UNION d. EXISTS

WHERE

When using the Oracle TO_NUMBER function to convert a character string into a number, the ____ format character displays a digit. a. 0 b. 9 c. $ d. #

a. 9

Which operator(s) is/are used with subqueries that use inequality operators on a list of values? a. IN b. ANY and ALL c. ON d. ONLY

a. ANY and ALL

A(n) ____ is a block of code (containing standard SQL statements and procedural extensions) that is stored and executed at the DBMS server. a. persistent stored module b. permanent SQL module c. SQL statement d. program manipulation command

a. persistent stored module

When using an equality (=) or inequality (<, >, etc.) operator for a subquery, what type of value must the subquery return? a. single value b. list of values c. no value d. virtual table

a. single value

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. a. OF b. USING c. HAS d. JOIN ON

b. JOIN ON

What type of inner join requires the use of a table qualifier? a. USING b. NATURAL c. EQUI- d. JOIN ON

b. JOIN ON

What type of subquery could be used in place of MINUS if the RDBMS does not support it? a. IN b. NOT IN c. AND d. UNION

b. NOT IN

A(n) ____ query can be used to produce a relation that retains duplicate rows. a. UNION b. UNION ALL c. INTERSECT d. MINUS

b. UNION ALL

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; a. set operator b. natural join c. "old-style" join d. procedural statement

d. "old-style" join

Which data type is considered compatible with VARCHAR(35)? a. DATE b. INT c. TINYINT d. CHAR(15)

d. CHAR(15)

____ 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. a. Triggers b. Indexes c. Embedded SQL

d. Procedural SQL

What is the syntax for the UNION statement? a. query + query b. UNION(query, query) c. UNION: query query d. query UNION query

d. query UNION query

In a SELECT query, the attribute list can include a subquery expression, known as a(n) ____ subquery. a. having b. correlated c. inline d. nested

inline

The ____ join is the traditional join in which only rows that meet a given criteria are selected. a. full b. inner c. outer d. set

inner

28. In a subquery, which query is executed first? a. leftmost b. rightmost c. innermost d. outermost

innermost

____ is a relational set operator. a. MINUS b. PLUS c. ALL d. EXISTS

minus

A(n) ____ join will select only the rows with common values in the common column(s), excluding rows with unmatched values and duplicate columns. a. natural b. cross c. full d. outer

natural

A(n) ____ join returns not only the rows matching the join condition but also the rows with unmatched values. a. outer b. inner c. equi- d. cross

outer

In subquery terminology, the first query in the SQL statement is known as the ____ query. a. outer b. left c. inner d. base

outer

The Oracle equivalent to a MS Access AutoNumber is a(n) ____. a. AutoNumber b. sequence c. TO_NUMBER function d. trigger

sequence

29. INSERT INTO PRODUCT SELECT * FROM P; is an example of a ____. a. join b. subquery c. set operator d. stored procedure

subquery

A ____ is a query inside a query. a. subquery b. basequery c. join d. set query

subquery

Assume you are using the UNION statement to combine the results from two identical tables CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains seven rows. There are two customers included in both tables. How many records are returned when using the UNION statement? a. 7 b. 10 c. 15 d. 17

15

Assume you are using the UNION ALL statement to combine the results from two identical tables CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains seven rows. There are two customers included in both tables. How many records are returned when using the UNION ALL statement? a. 7 b. 10 c. 15 d. 17

17

How many rows would be returned from a cross join of tables A and B if A contains 10 rows and B contains 20? a. 10 b. 20 c. 30 d. 200

200

How many different types of outer joins exist? a. 1 b. 2 c. 3 d. 4

3

A ____ join of two tables returns rows with matching values and includes all rows from both tables with unmatched values. a. natural b. cross c. full outer d. left outer

c. full outer

What is meant by "union-compatible"? a. the names of the relation attributes can be different but the data types must be alike b. the names of the relation attributes must be the same but the data types can be different c. the names of the relation attributes must be the same and their data types must be alike d. the number of attributes must be the same but the names and data types can be different

c. the names of the relation attributes must be the same and their data types must be alike

The Oracle string concatenation function is ____. a. CONCAT b. + c. || d. &&

c. ||

A subquery that executes once for each row in the outer query is a(n) ____ subquery. a. nested b. correlated c. inline d. grouped

correlated

The statement SELECT * FROM T1, T2 produces a(n) ____ join. a. cross b. natural c. equi- d. full

cross