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
