Database programming section 15

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

2. Given the following view, which 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 (*)

3. Which statement about performing DML operations on a view is true?
You cannot modify data in a view if the view contains a group function. (*)

4. 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;
(*)

5. Only one type of view exists. True or False?
False (*)

6. 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?
A GROUP BY clause (*)

7. Which of the following is true about ROWNUM?
It is the number assigned to each row returned from a query as it is read from the table. (*)

8. 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 Top-n query (*)

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

10. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
JOB_ID NUMBER
MANAGER_ID NUMBER
SALARY NUMBER(9,2)
COMMISSOIN NUMBER(7,2)
HIRE_DATE DATE
Which SELECT statement could be used to display the 10 lowest paid clerks that belong to department 70?
SELECT ROWNUM "Ranking",last_name||','||first_name "Employee", salary "Salary"
FROM (SELECT last_name, first_name, salary, job_id
     FROM employees
     WHERE job_id LIKE 'CLERK' AND department_id = 70
     ORDER BY salary)
WHERE ROWNUM <=10;
(*)

11. Which statement would you use to alter a view?
CREATE OR REPLACE VIEW (*)

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

SELECT *
FROM part_name_v;
(*)

13. Unlike tables, views contain no data of their own. True or False?
True (*)

14. You need to create a view on the SALES table, but the SALES table has not yet been created. Which statement is true?

You can use the FORCE option to create the view before the SALES table has been created. (*)

15. 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?
False (*)