PROGRAMMING: Section 11

CREATING VIEWS
What is one advantage of using views?
To provide restricted data access

Views contain no data of their own. True or False?
True

Given the following CREATE VIEW statement, what data will be returned?
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name, e.salary, e.hire_date, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_id >=50;
First character from employee first_name concatenated to the last_name, the salary, the hire_date and department_name of all employees working in department number 50 or higher

A view can contain a select statement with a subquery. True or False?
True

A view can contain group functions. True or False?
True

Any select statement can be stored in the database as a view. True or False?
True


DML OPERATIONS AND VIEWS
Given the following view what operations would be allowed on the emp_dept view:
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name, e.salary, e.hire_date, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_id >=50;
SELECT, UPDATE of some columns, DELETE

If a database administrator wants to ensure that changes performed through a view do not violate existing constraints, which clause should he/she include when creating the view?
WITH CHECK OPTION
Which of the following DML operations is not allowed when using a Simple View created with read only?
All of the above

There is only one kind view? True or False?
False

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

Examine the view below and choose the operation that CANNOT be performed on it.
CREATE VIEW dj_view (last_name, number_events) AS
SELECT c.last_name, COUNT(e.name)
FROM d_clients c, d_events e
WHERE c.client_number = e.client_number
GROUP BY c.last_name
INSERT INTO dj_view VALUES ('Turner', 8);


MANAGING VIEWS
When you drop a table referenced by a view, the view is automatically dropped as well. True or False?
False

Which of the following is true about ROWNUM?
It is the number assigned to each row returned from a query as they are read from the table.

How do you remove a view?
DROP VIEW view_name

A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?
True

When you drop a view, the data it contains is also deleted. True or False?
False

Which of these Keywords is typically used with a Top-N Analysis?
Rownum

Which of these is not a valid type of View?
ONLINE