Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Appendix C. Answers to Quizzes and Exercises
Hour 20, "Creating and Using Views and Synonyms"
Quiz Answers
1:
Can a row of data be deleted from a view that was created from multiple tables?
A1:
No. The DELETE, INSERT, and UPDATE commands can only be used on views created from a single table.
2:
When creating a table, the owner is automatically granted the appropriate privileges on that table. Is this true when creating a view?
A2:
Yes. The owner of a view is automatically granted the appropriate privileges on the view.
3:
What clause is used to order data when creating a view?
A3:
The GROUP BY clause functions in a view much as the ORDER BY clause (or GROUP BY clause) does in a regular query.
4:
What option can be used, when creating a view from a view, to check integrity constraints?
A4:
The WITH CHECK OPTION.
5:
You try to drop a view and receive an error because there are one or more underlying views. What must you do to drop the view?
A5:
Re-execute your DROP statement with the CASCADE option. This allows the DROP statement to succeed by also dropping all underlying views.
Exercise Answers
1:
Write a statement to create a view based on the total contents of the EMPLOYEE_TBL table.
A1:
CREATE VIEW EMP_VIEW AS SELECT * FROM EMPLOYEE_TBL;
2:
Write a statement that creates a summarized view containing the average pay rate and average salary for each city in the EMPLOYEE_TBL table.
A2:
CREATE VIEW AVG_PAY_VIEW AS SELECT E.CITY, AVG(P.PAY_RATE), AVG(P.SALARY) FROM EMPLOYEE_PAY_TBL P, EMPLOYEE_TBL E WHERE P.EMP_ID = E.EMP_ID GROUP BY E.CITY;
3:
Write statements that drop the two views that you created in Exercises 1 and 2.
