Chapter 7 to 8 Multiple choice

Which of the following operators is used with subqueries that use inequality operators on a list of values?

ANY

The basic SQL aggregate function that gives the arithmetic mean for the specific column is ________.

AVG

Which command would be used to delete a table row whose (P_CODE) is 'BRT-345'?

DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345';

A column can be deleted from a table by using the ________ command.

DROP

The SQL command that limits values to unique values is ________.

Distinct

________ is a term used to describe an environment in which the SQL statement is generated at run time.

Dynamic SQL

The ________ function is used to find the highest value in a table column.

MAX

The ________ pseudo column is used to select the next available value from a sequence.

NEXTVAL

Which of the following subqueries can be used in place of MINUS if the RDBMS does not support it?

NOT IN subquery

Which of the following can be classified as an outer join?

Natural join

________ is an example of a logical operator.

Not

The ________ clause is especially useful when the listing sequence is important.

ORDER BY

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 ________ command is not supported by MS Access.

ROLLBACK

The ________ command is used to restore the database when the COMMIT has not yet been used to store the changes permanently.

ROLLBACK

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

persistent stored module

Which of the following is the syntax for the UNION statement?

query UNION query

A(n) ________ is a logical group of database objects that are related to each other.

schema

When using an equality (=) or inequality (<, >, etc.) operator for a subquery, the subquery returns a ________.

single value

INSERT INTO PRODUCT SELECT * FROM P; is an example of a ________.

subquery

A ________ is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a given data manipulation event.

trigger

A ________ is a virtual table based on a SELECT query.

view

Which of the following types of inner joins requires the use of a table qualifier?

JOIN ON

The DELETE FROM tablename command without a specific WHERE condition will delete ________.

all rows

When a new database is created, the RDBMS automatically creates the ________.

authentication required to use the RDBMS

The Oracle ________ 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 function

The ________ statement can be used to combine rows from two queries, returning only the rows that appear in both sets.

INTERSECT statement

The ________ operator is used to check whether an attribute value exists or not.

IS NULL

If you wish to create an inner join but the two tables do not have a common attribute names, you can use a ________.

JOIN ON clause

The Oracle ________ returns the number of characters in a string value.

LENGTH function

The ________ operator is used to find whether an attribute matches a given string pattern.

LIKE

The UNION statement is used 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?

15

The UNION ALL statement is used 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?

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

200

The ________ operator is used to check whether an attribute value is within a range.

BETWEEN

Which of the following data types is considered union-compatible with VARCHAR(35)?

CHAR(15)

Which of the following is a SQL character data type(s)?

CHAR8

The basic SQL aggregate function that gives the number of rows containing non-null values for the given column is ________.

COUNT

The ________ command validates data in an attribute.

Check

Some RDBMSs (like Oracle) will automatically ________ data changes when issuing data definition commands.

Commit

The SQL command that permanently saves data changes is ________.

Commit

________ is an example of an aggregate function.

Count

Before using a new RDBMS, which of the following tasks should be completed?

Creating the database structure and the tables that will hold end-user data

________ is a relational set operator.

EXCEPT

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

EXCEPT statement

The ________ operator is used to check whether a subquery returns any rows.

EXISTS

The SQL command that lets you fits data into a table is ________.

Insert

To join tables, it is enough to list the tables in the ________ clause of the SELECT statement.

FROM

In an SQL statement, the ________ specifies the table from which the data will be drawn.

FROM clause

In Oracle, the ________ command is used to place a dollar symbol ($) in front of a numeric value.

Format

The ________ clause is valid only when used in conjunction with one of the SQL aggregate functions, such as COUNT, MIN, MAX, AVG, and SUM.

GROUP BY

The ________ clause is applied to the output of a GROUP BY operation.

HAVING

The ________ is used to restrict the output of a GROUP BY query by applying conditional criteria to the grouped rows.

HAVING clause

Many queries that would require the use of the logical OR can be more easily handled with the help of the special operator ________.

IN

In order to compare one value to a list of values returned by a subquery, you must use a(n) ________.

IN operand

Which of the following subqueries can be used in place of INTERSECT if the RDBMS does not support it?

IN subquery

What command is used to produce a list of only those unique values, for the column Vendor Code (V_CODE), those are different from one another?

SELECT DISTINCT V_CODE FROM PRODUCT;

Which of the following is an example of a recursive query?

SELECT E.EMP_MGR, M.EMP_LNAME, E.EMP_NUM, E.EMP_LNAME FROM EMP E, EMP M WHERE E.EMP_MGR=M.EMP_NUM ORDER BY E.EMP_MGR;

What is the SQL command that lists the contents of the EMPLOYEE table sorted by last name, first name, and initial?

SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;

What is the command used to list the P_CODE, P_DESCRIPT, P_INDATE, and P_PRICE fields from the PRODUCT table in ascending order by P_PRICE?

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE;

Which of the following syntax would yield a list of all rows in which the value of the entities in the column P_CODE is alphabetically less than 1558-QW1?

SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P-CODE < '1558-QW1';

In MS Access, which query command will list all of the rows in which the inventory stock dates occur on or after January 20, 2014?

SELECT P_DESCRIPT, P_HAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= #20-JAN-14#;

Which of the following query will return an output that will list all of the rows for which the vendor code is less than or equal to 21344?

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <= 21344;

Which of the following query would list all of the rows for which the vendor code is not equal to 21344?

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344;

What is the SQL syntax to list the table contents in the column V_CODE for either V_CODE = 21344 or V_CODE = 24288?

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288;

Which of the following syntax will return the description, date, and price of products with a vendor code of 21344?

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;

Which of the following commands is used to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE and V_CONTACT fields from the VENDOR table where the values of V_CODE match?

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

Which command is used to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the values of V_CODE match and the output is ordered by P_PRICE?

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; ORDER BY P_PRICE;

Which command uses a column alias TOTVALUE to display the result of the total value of each of the products currently held in inventory?

SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE AS TOTVALUE FROM PRODUCT;

Which of the following is the syntax for a left outer join?

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

Which command is used to choose partial table contents?

SELECT columnlist FROM tablelist [WHERE conditionlist];

Which of the following data types is compatible with NUMBER?

SMALLINT

The basic SQL aggregate function that gives the total of all values for a selected attribute in a given column is ________.

SUM

The Oracle ________ returns the current date.

SYSDATE function

The SQL command that chooses attributes from rows in one or more tables or views is ________.

Select

The command used to see all the contents of the PRODUCT table is ________.

Select*From Product;

The ________ returns the number of the day in MS Access.

TO DATE( ) function

In Oracle, the ________ converts a date to a character string.

TO_CHAR function

A(n) ________ query can be used to produce a relation that retains duplicate rows.

UNION ALL statement

The ________ combines rows from two or more queries without including duplicate rows.

UNION statement

Which of the following command should be used to change the P_INDATE in the second row of a PRODUCT table?

UPDATE PRODUCT SET P_INDATE = '18-JAN-2014' WHERE P_CODE = '13-Q2/P2';

Complete the following join: SELECT * FROM T1 JOIN T2 ________ (C1)

USING

The ________ command ensures that a column will not have duplicate keys.

Unique

The SQL command that modifies an attribute's values in one or more table's rows is ________.

Update

The most common type of subquery uses an inner SELECT subquery on the right side of a(n) ________ expression.

WHERE comparison

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

alias

Oracle sequences ________.

can be used anywhere a value is expected

A multilevel ordered sequence is known as a ________ order sequence.

cascading

A subquery that executes once for each row in the outer query is known as a(n) ________.

correlated subquery

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

cross join

A ________ of two tables returns rows with matching values and includes all rows from both tables with unmatched values.

full outer join

In a SELECT query, the attribute list can include a subquery expression known as a(n) ________.

inline subquery

The ________ is the traditional join in which only rows that meet a given criteria are selected.

inner join

In a subquery, the ________ is executed first.

inner query

A relational view _______.

is recreated on demand each time it is invoked

In the Oracle TO_DATE function, the code MON denotes the ________.

name of the month

A(n) ________ will select only the rows with common values in the common columns, excluding rows with unmatched values and duplicate columns.

natural join

ROUND(value, p) is an example of a ________.

numeric function

A(n) ________ returns not only the rows matching the join condition but also the rows with unmatched values.

outer join

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

outer query