Topic 6

Question 1

The SQL data manipulation command HAVING:

a. restricts the selection of rows based on a conditional expression.
b. restricts the selection of grouped rows based on a condition.
c. modifies an attribute’s values in one or more table’s rows.
d. groups the selected rows based on one or more attributes.

Answer 1

restricts the selection of grouped rows based on a condition

Question 2

The SQL command that allows a user to permanently save data changes is _____

a. INSERT
b. SELECT
c. COMMIT
d. UPDATE

Answer 2

COMMIT

Question 3

The _____ command defines a default value for a column when no value is given

a. CHECK
b. UNIQUE
c. NOT NULL
d. DEFAULT

Answer 3

DEFAULT

Question 4

The _____ command restricts the selection of grouped rows based on a condition

a. DISPLAY
b. HAVING
c. FROM
d. CONVERT

Answer 4

HAVING

Question 5

A (n) _____ query specifies which data should be retrieved and how it should be filtered, aggregated, and displayed

a. INSERT
b. SELECT
c. COMMIT
d. UPDATE

Answer 5

SELECT

Question 6

A (n) _____ is an alternate name given to a column or table in any SQL statement

a. alias
b. data type
c. stored function
d. trigger

Answer 6

alias

Question 7

According to the rules of precedence, which of the following computations should be completed first?

a. Additions and subtractions
b. Multiplications and divisions
c. Operations within parentheses
d. Power operations

Answer 7

Operations within parentheses

Question 8

Which query is used to list a unique value for V_CODE, where the list will produce only a list of those values that are different from one another?

a. 	SELECT ONLY V_CODE
FROM PRODUCT;	
b. 	SELECT UNIQUE V_CODE
FROM PRODUCT;
c. 	SELECT DIFFERENT V_CODE
FROM PRODUCT;	
d. 	SELECT DISTINCT V_CODE
FROM PRODUCT;

Answer 8

SELECT ONLY V_CODE

FROM PRODUCT;

Question 9

When using a (n) _____ join, only rows from the tables that match on a common value are returned.

a. full
b. outer
c. inner
d. set

Answer 9

inner

Question 10

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

a. natural
b. outer
c. full
d. cross

Answer 10

natural

Question 11

If a designer wishes to create an inner join, but the two tables do not have a commonly named attribute, he can use a (n) _____ clause

a. OF
b. USING
c. HAS
d. JOIN ON

Answer 11

JOIN ON

Question 12

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

a. outer
b. inner
c. equi-
d. cross

Answer 12

outer

Question 13

The syntax for a left outer join is _____

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	
d. 	SELECT    column-list
FROM       table1 LEFT table2 [JOIN]
WHERE     join-condition

Answer 13

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

Question 14

A (n) _____ join performs a relational product (also known as the Cartesian product) of two tables

a. full
b. cross
c. natural
d. equi-

Answer 14

cross

Question 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?

a. 8
b. 18
c. 26
d. 144

Answer 15

144

Question 16

Which comparison operator indicates a value is not equal?

a. <
b. <=
c. >=
d. <>

Answer 16

<>

Question 17

What type of command does this SQL statement use?

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

Answer 17

“old-style” join

Question 18

The special operator used to check whether an attribute value is within a range of values is _____

a. BETWEEN
b. NULL
c. LIKE
d. IN

Answer 18

BETWEEN

Question 19

The special operator used to check whether an attribute value matches a given string pattern is _____

a. BETWEEN
b. IS NULL
c. LIKE
d. IN

Answer 19

LIKE

Question 20

The SQL aggregate function that gives the number of rows containing non-null values for a given column is _____

a. COUNT
b. MIN
c. MAX
d. SUM

Answer 20

COUNT

Question 21

A (n) _____ is a query that is embedded (or nested) inside another query

a. alias
b. operator
c. subquery
d. view

Answer 21

subquery

Question 22

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

a. outer
b. left
c. inner
d. base

Answer 22

outer

Question 23

The special operator used to check whether a subquery returns any rows is _____

a. BETWEEN
b. EXISTS
c. LIKE
d. IN

Answer 23

EXISTS

Question 24

Which is a feature of a correlated subquery?

a. The inner subquery executes first.
b. The outer subquery initiates the process of execution in a subquery.
c. The inner subquery initiates the process of execution in a subquery.
d. The outer subquery executes independent of the inner subquery.

Answer 24

The outer subquery initiates the process of execution in a subquery

Question 25

The _____ function returns the current system date in MS Access a. TO_DATE() b. SYSDATE() c. DATE() d. TODAY()

Answer 25

DATE()

Question 26

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

Answer 26

MON

Question 27

_____ is a string function that returns the number of characters in a string value a. LENGTH b. SUBSTRING c. CONCAT d. UCASE

Answer 27

LENGTH

Question 28

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

Answer 28

DECODE

Question 29

In Oracle, the _____ function converts a date to a character string a. CONVERT() b. TO_DATE c. TO_CHAR() d. TO_STRING()

Answer 29

TO_CHAR()

Question 30

_____ is a relational set operator a. EXCEPT b. PLUS c. ALL d. EXISTS

Answer 30

EXCEPT