Test: Final Exam Semester 2 - Part II (1-10)

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Part II of the Semester 2 Final Exam covers Sections 10-14 of Database Programming with SQL.

	Section 10
		1.	For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?		Mark for Review (1) Points
			The view will allow the user to check it against the data dictionary
			Prohibits changing rows not returned by the subquery in the view definition. (*)
			Prohibits DML actions without administrator CHECK approval
			Allows for DELETES from other tables, including ones not listed in subquery
				Correct
		2.	You create a view on the EMPLOYEES and DEPARTMENTS tables to display salary information per department. What will happen if you issue the following statement: CREATE OR REPLACE VIEW sal_dept AS SELECT SUM(e.salary) sal, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name ORDER BY d.department_name;		Mark for Review (1) Points
			A complex view is created that returns the sum of salaries per department, sorted by department name. (*)
			A simple view is created that returns the sum of salaries per department, sorted by department name.
			A complex view is created that returns the sum of salaries per department, sorted by department id.
			Nothing, as the statement constains an error and will fail.
				Correct
		3.	You administer an Oracle database. Jack manages the Sales department. He and his employees often find it necessary to query the database to identify customers and their orders. He has asked you to create a view that will simplify this procedure for himself and his staff. The view should not accept INSERT, UPDATE or DELETE operations. Which of the following statements should you issue?		Mark for Review (1) Points
			CREATE VIEW sales_view &nbspAS (SELECT companyname, city, orderid, orderdate, total &nbspFROM customers, orders &nbspWHERE custid = custid) WITH READ ONLY;
			CREATE VIEW sales_view (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total &nbspFROM customers c, orders o &nbspWHERE c.custid = o.custid) WITH READ ONLY;
			CREATE VIEW sales_view AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total FROM customers c, orders o WHERE c.custid = o.custid);
			CREATE VIEW sales_view &nbspAS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total &nbspFROM customers c, orders o &nbspWHERE c.custid = o.custid) WITH READ ONLY; (*)
				Correct
		4.	Which statement about performing DML operations on a view is true?		Mark for Review (1) Points
			You can perform DML operations on simple views. (*)
			You cannot perform DML operations on a view that contains the WITH CHECK OPTION clause.
			You can perform DML operations on a view that contains the WITH READ ONLY option.
			You can perform DML operations on a view that contains columns defined by expressions, such as COST + 1.
				Correct
		5.	What is the purpose of including the WITH CHECK OPTION clause when creating a view?		Mark for Review (1) Points
			To make sure that the parent table(s) actually exist
			To keep views form being queried by unauthorized persons
			To make sure that data is not duplicated in the view
			To make sure no rows are updated through the view that will hinder those rows from being returned by the view. (*)
				Correct
		6.	You cannot insert data through a view if the view includes ______.		Mark for Review (1) Points
			A WHERE clause
			A join
			A column alias
			A GROUP BY clause (*)
				Correct
		7.	You need to create a new view on the EMPLOYEES table to update salary information for employees in Department 50. You need to ensure that DML operations through the view do not change the result set of the view. Which clause should include in the CREATE VIEW statement?		Mark for Review (1) Points
			FORCE
			OR REPLACE
			WITH READ ONLY
			WITH CHECK OPTION (*)
				Correct
		8.	A view can be used to keep a history record of old data from the underlying tables, so even if a row is deleted from a table, you can still select the row through the view. True or False?		Mark for Review (1) Points
			True
			False (*)
				Correct
		9.	Views must be used to select data from a table. As soon as a view is created on a table, you can no longer select directly from the table. True or False?		Mark for Review (1) Points
			True
			False (*)
				Correct
		10.	Which keyword(s) would you include in a CREATE VIEW statement to create the view regardless of whether or not the base table exists?		Mark for Review (1) Points
			FORCE (*)
			NOFORCE
			OR REPLACE
			WITH READ ONLY
				Correct