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 Exerc6ises 1 and 2.
A3: 
DROP VIEW EMP_VIEW
DROP VIEW AVG_PAY_VIEW;

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset