SOLOSOLOKU: Section 15 Quiz

Section 15 Quiz
(Answer all questions in this section)

1. Which of the following keywords cannot be used when creating a view?

HAVING
WHERE
ORDER BY (*)
They are all valid keywords when creating views.

Incorrect Incorrect. Refer to Section 15 Lesson 1.

2. A view can contain group functions. True or False?
True (*)
False

3. Which of the following statements is a valid reason for using a view?

Views are not valid unless you have more than one user.
Views allow access to the data because the view displays all of the columns from the table.
Views are used when you only want to restrict DML operations using a WITH CHECK OPTION.
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. (*)

4. Which statement about the CREATE VIEW statement is true?  

A CREATE VIEW statement CANNOT contain a function.
A CREATE VIEW statement CAN contain a join query. (*)
A CREATE VIEW statement CANNOT contain an ORDER BY clause.
A CREATE VIEW statement CANNOT contain a GROUP BY clause.

5. In order to query a database using a view, which of the following statements applies?

You can never see all the rows in the table through the view
The tables you are selecting from can be empty, yet the view still returns the original data from those tables.
You can retrieve data from a view as you would from any table. (*)
Use special VIEW SELECT keywords.

Section 15 Quiz
(Answer all questions in this section)

6. Which option would you use when creating a view to ensure that no DML operations occur on the view?  

NOFORCE
WITH ADMIN OPTION
FORCE
WITH READ ONLY (*)

Incorrect Incorrect. Refer to Section 15 Lesson 2.

7. 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;

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 contains an error and will fail.

8. 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?  

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)
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
   AS (SELECT companyname, city, orderid, orderdate, total
   FROM customers, orders
   WHERE custid = custid)
WITH READ ONLY;

CREATE VIEW sales_view
    (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
    FROM customers c, orders o
    WHERE c.custid = o.custid)
WITH READ ONLY;

9. 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 can not change salary values in other departments. Which clause should be included in the CREATE VIEW statement?

WITH CHECK OPTION (*)
WITH READ ONLY
OR REPLACE
FORCE

10. Using the pseudocolumn ROWNUM in a view has no implications on the ability to do DML's through the view. True or False?  

True
False (*)

Section 15 Quiz
(Answer all questions in this section)

11. You want to create a view based on the SALESREP table. You plan to grant access to this view to members of the Sales department. You want Sales employees to be able to update the SALESREP table through the view, which you plan to name SALESREP_VIEW. What should not be specified in your CREATE VIEW statement?

The IN keyword
A WHERE clause
A GROUP BY clause (*)
The AS keyword

12. When you drop a table referenced by a view, the view is automatically dropped as well. True or False?
True
False (*)

13. How do you remove a view?  

DELETE VIEW view_name
REMOVE VIEW view_name
DROP VIEW view_name (*)
You cannot remove a view

14. An inline view is an unnamed select statement found:

In a special database column of a users table.
Enclosed in parentheses within the FROM clause of a surrounding query. (*)
Enclosed in parentheses within the select list of a surrounding query.
In the user_views data dictionary view.

15. 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?

A complex view
A simple view
A Top-n query (*)
A hierarchical view