Description
Joan Casteel Oracle 11g SQL Chapters 13 Multiple Choice Questions Solution
To answer the following questions, refer to the tables in the JustLee Books database.
Questions 1–7 are based on successful execution of the following statement:
CREATE VIEW changeaddress
AS SELECT customer#, lastname, firstname, order#,
shipstreet, shipcity, shipstate, shipzip
FROM customers JOIN orders USING (customer#)
WHERE shipdate IS NULL
WITH CHECK OPTION;
1. Which of the following statements is correct?
a. No DML operations can be performed on the CHANGEADDRESS view.
b. The CHANGEADDRESS view is a simple view.
c. The CHANGEADDRESS view is a complex view.
d. The CHANGEADDRESS view is an inline view.
2. Assuming there’s only a primary key, and FOREIGN KEY constraints exist on the underlying tables, which of the following commands returns an error message?
a. UPDATE changeaddress SET shipstreet = ‘958 ELM ROAD’ WHERE customer# = 1020;
b. INSERT INTO changeaddress VALUES (9999, ‘LAST’, ‘FIRST’, 9999, ‘123 HERE AVE’, ‘MYTOWN’, ‘AA’, 99999);
c. DELETE FROM changeaddress WHERE customer# = 1020;
d. all of the above
e. only a and b
f. only a and c
g. none of the above
3. Which of the following is the key-preserved table for the CHANGEADDRESS view?
a. CUSTOMERS table
b. ORDERS table
c. Both tables together serve as a composite key-preserved table.
d. none of the above
4. Which of the following columns serves as the primary key for the CHANGEADDRESS view?
a. Customer#
b. Lastname
c. Firstname
d. Order#
e. Shipstreet
5. If a record is deleted from the CHANGEADDRESS view based on the Customer# column, the customer information is then deleted from which underlying table?
a. CUSTOMERS
b. ORDERS
c. CUSTOMERS and ORDERS
d. Neither—the DELETE command can’t be used on the CHANGEADDRESS view.
6. Which of the following is correct?
a. ROWNUM can’t be used with the view because it isn’t included in the results the subquery returns.
b. The view is a simple view because it doesn’t include a group function or a GROUP BY clause.
c. The data in the view can’t be displayed in descending order by customer number because an ORDER BY clause isn’t allowed when working with views.
d. all of the above
e. none of the above
7. Assuming one of the orders has shipped, which of the following is true?
a. The CHANGEADDRESS view can’t be used to update an order’s ship date because of the WITH CHECK OPTION constraint.
b. The CHANGEADDRESS view can’t be used to update an order’s ship date because the Shipdate column isn’t included in the view.
c. The CHANGEADDRESS view can’t be used to update an order’s ship date because the ORDERS table is not the key-preserved table.
d. The CHANGEADDRESS view can’t be used to update an order’s ship date because the UPDATE command can’t be used on data in the view.
Questions 8–12 are based on successful execution of the following command:
CREATE VIEW changename
AS SELECT customer#, lastname, firstname
FROM customers
WITH CHECK OPTION;
Assume that the only constraint on the CUSTOMERS table is a PRIMARY KEY constraint.
8. Which of the following is a correct statement?
a. No DML operations can be performed on the CHANGENAME view.
b. The CHANGENAME view is a simple view.
c. The CHANGENAME view is a complex view.
d. The CHANGENAME view is an inline view.
9. Which of the following columns serves as the primary key for the CHANGENAME view?
a. Customer#
b. Lastname
c. Firstname
d. The view doesn’t have or need a primary key.
10. Which of the following DML operations could never be used on the CHANGENAME view?
a. INSERT
b. UPDATE
c. DELETE
d. All of the above are valid DML operations for the CHANGENAME view.
11. The INSERT command can’t be used with the CHANGENAME view because:
a. A key-preserved table isn’t included in the view.
b. The view was created with the WITH CHECK OPTION constraint.
c. The inserted record couldn’t be accessed by the view.
d. None of the above—an INSERT command can be used on the table as long as the PRIMARY KEY constraint isn’t violated.
12. If the CHANGENAME view needs to include the customer’s zip code as a means of verifying the change (that is, to authenticate the user), which of the following is true?
a. The CREATE OR REPLACE VIEW command can be used to re-create the view with the necessary column included in the new view.
b. The ALTER VIEW . . . ADD COLUMN command can be used to add the necessary column to the existing view.
c. The CHANGENAME view can be dropped, and then the CREATE VIEW command can be used to re-create the view with the necessary column included in the new view.
d. All of the above can be performed to include the customer’s zip code in the view.
e. Only a and b include the customer’s zip code in the view.
f. Only a and c include the customer’s zip code in the view.
g. None of the above includes the customer’s zip code in the view.
13. Which of the following DML operations can’t be performed on a view containing a group function?
a. INSERT
b. UPDATE
c. DELETE
d. All of the above can be performed on a view containing a group function.
e. None of the above can be performed on a view containing a group function.
14. You can’t perform any DML operations on which of the following?
a. views created with the WITH READ ONLY option
b. views that include the DISTINCT keyword
c. views that include a GROUP BY clause
d. All of the above allow DML operations.
e. None of the above allow DML operations.
15. A TOP-N analysis is performed by determining the rows with:
a. the highest ROWNUM values
b. a ROWNUM value greater than or equal to N
c. the lowest ROWNUM values
d. a ROWNUM value less than or equal to N
16. To assign names to the columns in a view, you can do which of the following?
a. Assign aliases in the subquery, and the aliases are used for the column names.
b. Use the ALTER VIEW command to change column names.
c. Assign names for up to three columns in the CREATE VIEW clause before the subquery is listed in the AS clause.
d. None of the above—columns can’t be assigned names for a view; they must keep their original names.
17. Which of the following is correct?
a. The ORDER BY clause can’t be used in the subquery of a CREATE VIEW command.
b. The ORDER BY clause can’t be used in an inline view.
c. The DISTINCT keyword can’t be used in an inline view.
d. The WITH READ ONLY option must be used with an inline view.
18. If you try to add a row to a complex view that includes a GROUP BY clause, you get which of the following error messages?
a. virtual column not allowed here
b. data manipulation operation not legal on this view
c. cannot map to a column in a non-key-preserved table
d. None of the above—no error message is returned.
19. A simple view can contain which of the following?
a. data from one or more tables
b. an expression
c. a GROUP BY clause for data retrieved from one table
d. five columns from one table
e. all of the above
f. none of the above
20. A complex view can contain which of the following?
a. data from one or more tables
b. an expression
c. a GROUP BY clause for data retrieved from one table
d. five columns from one table
e. all of the above
f. none of the above
Reviews
There are no reviews yet.