Test: Final Exam Semester 2 - Part II (11-20)

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
		11.	Which of the following keywords cannot be used when creating a view?		Mark for Review (1) Points
			HAVING
			WHERE
			ORDER BY
			They are all valid keywords when creating views. (*)
				Correct
		12.	Which statement would you use to alter a view?		Mark for Review (1) Points
			ALTER VIEW
			MODIFY VIEW
			ALTER TABLE
			CREATE OR REPLACE VIEW (*)
				Correct
		13.	Which of the following statements is a valid reason for using a view?		Mark for Review (1) Points
			Views allow access to the data because the view displays all of the columns from the table.
			Views provide data independence for infrequent users and application programs. One view can be used to retrieve data from several tables. Views can be used to provide data security. (*)
			Views are used when you only want to restrict DML operations using a WITH CHECK OPTION.
			Views are not valid unless you have more than one user.
				Correct
		14.	Evaluate this view definition: CREATE OR REPLACE VIEW part_name_v AS SELECT DISTINCT part_name FROM parts WHERE cost >= 45; Which of the following statements using the PART_NAME_V view will execute successfully?		Mark for Review (1) Points
			SELECT * FROM part_name_v; (*)
			UPDATE part_name_v SET cost = cost * 1.23 WHERE part_id = 56990;
			DELETE FROM part_name_v WHERE part_id = 56897;
			INSERT INTO part_name_v (part_id, part_name, product_id, cost) VALUES (857986, 'cylinder', 8790, 3.45);
				Correct
		15.	Evaluate this CREATE VIEW statement: CREATE VIEW pt_view AS (SELECT first_name, last_name, status, courseid, subject, term &nbspFROM faculty f, course c &nbspWHERE f.facultyid = c.facultyid); Which type of view will this statement create?		Mark for Review (1) Points
			Nested
			Simple
			Inline
			Complex (*)
				Incorrect. Refer to Section 10
		16.	Evaluate this SELECT statement: SELECT ROWNUM "Rank", customer_id, new_balance FROM (SELECT customer_id, new_balance FROM customer_finance ORDER BY new_balance DESC) WHERE ROWNUM <= 25; Which type of query is this SELECT statement?		Mark for Review (1) Points
			A Top-n query (*)
			A complex view
			A simple view
			A hierarchical view
				Correct
		17.	Evaluate this CREATE VIEW statement: CREATE VIEW sales_view AS SELECT customer_id, region, SUM(sales_amount) FROM sales WHERE region IN (10, 20, 30, 40) GROUP BY region, customer_id; Which statement is true?		Mark for Review (1) Points
			You can modify data in the SALES table using the SALES_VIEW view.
			You cannot modify data in the SALES table using the SALES_VIEW view. (*)
			You can only insert records into the SALES table using the SALES_VIEW view.
			The CREATE VIEW statement generates an error.
				Correct
		18.	You must create a view that when queried will display the name, customer identification number, new balance, finance charge and credit limit of all customers. You issue this statement: CREATE OR REPLACE VIEW CUST_CREDIT_V AS SELECT c.last_name, c.customer_id, a.new_balance, a.finance_charge, a.credit_limit FROM customers c, accounts a WHERE c.account_id = a.account_id WITH READ ONLY; Which type of SQL command can be issued on the CUST_CREDIT_V view?		Mark for Review (1) Points
			UPDATE
			DELETE
			INSERT
			SELECT (*)
				Correct
		19.	The EMP_HIST_V view is no longer needed. Which statement should you use to the remove this view?		Mark for Review (1) Points
			DROP emp_hist_v;
			DELETE emp_hist_v;
			REMOVE emp_hist_v;
			DROP VIEW emp_hist_v; (*)
				Correct
		20.	The CUSTOMER_FINANCE table contains these columns: CUSTOMER_ID NUMBER(9) NEW_BALANCE NUMBER(7,2) PREV_BALANCE NUMBER(7,2) PAYMENTS NUMBER(7,2) FINANCE_CHARGE NUMBER(7,2) CREDIT_LIMIT NUMBER(7) You created a Top-n query report that displays the account numbers and new balance of the 800 accounts that have the highest new balance value. The results are sorted by payments value from highest to lowest. Which SELECT statement clause is included in your query?		Mark for Review (1) Points
			Inner query: ORDER BY new_balance DESC (*)
			Inner query: WHERE ROWNUM = 800
			Outer query: ORDER BY new_balance DESC
			Inner query: SELECT customer_id, new_balance ROWNUM
				Correct