Oracle Database Programming with SQL Section 13
1. Evaluate this CREATE VIEW statement:
CREATE VIEW emp_view
AS SELECT SUM(salary)
FROM employees;
CREATE VIEW emp_view
AS SELECT SUM(salary)
FROM employees;
Which statement is true?
♦You cannot update data in the EMPLOYEES table using the EMP_VIEW view. (*)
♦You can update any data in the EMPLOYEES table using the EMP_VIEW view.
♦You can update only the SALARY column in the EMPLOYEES table using the EMP_VIEW view.
♦You can delete records from the EMPLOYEES table using the EMP_VIEW view.
♦You can update any data in the EMPLOYEES table using the EMP_VIEW view.
♦You can update only the SALARY column in the EMPLOYEES table using the EMP_VIEW view.
♦You can delete records from the EMPLOYEES table using the EMP_VIEW view.
2. Which keyword(s) would you include in a CREATE VIEW statement to create the view whether or not the base table exists?
♦FORCE (*)
♦WITH READ ONLY
♦NOFORCE
♦OR REPLACE
♦FORCE (*)
♦WITH READ ONLY
♦NOFORCE
♦OR REPLACE
3. 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?
♦True
♦False (*)
♦True
♦False (*)
4. 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?
♦True
♦False (*)
♦True
♦False (*)
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.
♦You can retrieve data from a view as you would from any table. (*)
♦The tables you are selecting from can be empty, yet the view still returns the original data from those tables.
♦Use special VIEW SELECT keywords.
♦You can never see all the rows in the table through the view.
♦You can retrieve data from a view as you would from any table. (*)
♦The tables you are selecting from can be empty, yet the view still returns the original data from those tables.
♦Use special VIEW SELECT keywords.
6. If a database administrator wants to ensure that changes performed through a view do not violate existing constraints, which clause should he include when creating the view?
♦FORCE
♦WITH CHECK OPTION (*)
♦WITH READ ONLY
♦WITH CONSTRAINT CHECK
♦FORCE
♦WITH CHECK OPTION (*)
♦WITH READ ONLY
♦WITH CONSTRAINT CHECK
7. What is the purpose of including the WITH CHECK OPTION clause when creating a view?
♦To keep views form being queried by unauthorized persons
♦To make sure that the parent table(s) actually exist
♦To make sure that data is not duplicated in the view
♦To keep views form being queried by unauthorized persons
♦To make sure that the parent table(s) actually exist
♦To make sure that data is not duplicated in the view
♦To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)
8. Only one type of view exists. True or False?
♦True
♦False (*)
♦True
♦False (*)
9. For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?
♦Prohibits changing rows not returned by the subquery in the view definition. (*)
♦The view will allow the user to check it against the data dictionary
♦Prohibits DML actions without administrator CHECK approval
♦Allows for DELETES from other tables, including ones not listed in subquery
♦Prohibits changing rows not returned by the subquery in the view definition. (*)
♦The view will allow the user to check it against the data dictionary
♦Prohibits DML actions without administrator CHECK approval
♦Allows for DELETES from other tables, including ones not listed in subquery
10. You cannot insert data through a view if the view includes ______.
♦A join
♦A WHERE clause
♦A column alias
♦A GROUP BY clause (*)
♦A join
♦A WHERE clause
♦A column alias
♦A GROUP BY clause (*)
11. When you drop a view, the data it contains is also deleted. True or False?
♦True
♦False (*)
♦True
♦False (*)
12. 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;
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?
♦You can 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.
♦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.
♦You cannot modify data in the SALES table using the SALES_VIEW view. (*)
13. 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 AS keyword
♦A GROUP BY clause (*)
♦A WHERE clause
♦The IN keyword
♦The AS keyword
♦A GROUP BY clause (*)
♦A WHERE clause
♦The IN keyword
14. How do you remove a view?
♦DELETE VIEW view_name
♦REMOVE VIEW view_name
♦DROP VIEW view_name (*)
♦You cannot remove a view
♦DELETE VIEW view_name
♦REMOVE VIEW view_name
♦DROP VIEW view_name (*)
♦You cannot remove a view
15. When you drop a table referenced by a view, the view is automatically dropped as well. True or False?
♦True
♦False (*)
♦True
♦False (*)
16. Which statement about an inline view is true?
♦An inline view is a complex view.
♦An inline view is a subquery in the FROM clause, often named with an alias. (*)
♦An inline view is a schema object.
♦An inline view can be used to perform DML operations.
♦An inline view is a complex view.
♦An inline view is a subquery in the FROM clause, often named with an alias. (*)
♦An inline view is a schema object.
♦An inline view can be used to perform DML operations.
17. A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?
♦True (*)
♦False
♦True (*)
♦False
18. Which of these Keywords is typically used with a Top-N Analysis?
♦Number
♦Rowid
♦Rownum (*)
♦Sequence
♦Number
♦Rowid
♦Rownum (*)
♦Sequence
19. You must create a view that 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;
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?
♦SELECT (*)
♦UPDATE
♦DELETE
♦INSERT
♦UPDATE
♦DELETE
♦INSERT
20. 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;
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?
♦You can only insert records into the SALES table using the SALES_VIEW view.
♦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. (*)
♦The CREATE VIEW statement generates an error.
♦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. (*)
♦The CREATE VIEW statement generates an error.
21. 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;
(*)
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);
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;
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;
(SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
FROM customers c, orders o
WHERE c.custid = o.custid)
WITH READ ONLY;
22. You cannot insert data through a view if the view includes ______.
♦A WHERE clause
♦A GROUP BY clause (*)
♦A column alias
♦A join
♦A WHERE clause
♦A GROUP BY clause (*)
♦A column alias
♦A join
23. For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?
♦Prohibits DML actions without administrator CHECK approval
♦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. (*)
♦Allows for DELETES from other tables, including ones not listed in subquery
♦Prohibits DML actions without administrator CHECK approval
♦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. (*)
♦Allows for DELETES from other tables, including ones not listed in subquery
24. Which statement about performing DML operations on a view is true?
♦You can perform DML operations on a view that contains columns defined by expressions, such as COST + 1.
♦You can perform DML operations on simple views. (*)
♦You can perform DML operations on a view that contains the WITH READ ONLY option.
♦You cannot perform DML operations on a view that contains the WITH CHECK OPTION clause.
25. Which option would you use when creating a view to ensure that no DML operations occur on the view?
♦NOFORCE
♦FORCE
♦WITH ADMIN OPTION
♦WITH READ ONLY (*)
♦NOFORCE
♦FORCE
♦WITH ADMIN OPTION
♦WITH READ ONLY (*)
26. Unlike tables, views contain no data of their own. True or False?
♦True (*)
♦False
♦True (*)
♦False
27. 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?
♦True
♦False (*)
♦True
♦False (*)
28. 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?
♦True
♦False (*)
♦True
♦False (*)
29. Which of the following statements is a valid reason for using a view?
♦Views are used when you only want to restrict DML operations using a WITH CHECK OPTION.
♦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. (*)
♦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.
30. 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 create the table and the view at the same time using the FORCE option.
♦You must create the SALES table before creating the view.
♦You must create the SALES table before creating the view.
♦You can use the FORCE option to create the view before the SALES table has been created. (*)
♦By default, the view will be created even if the SALES table does not exist.
31. Which of the following keywords cannot be used when creating a view?
♦HAVING
♦WHERE
♦ORDER BY (*)
♦They are all valid keywords when creating views.
♦HAVING
♦WHERE
♦ORDER BY (*)
♦They are all valid keywords when creating views.
32. A view can contain a select statement with a subquery. True or False?
♦True (*)
♦False
♦True (*)
♦False
33. You need to create a view on the SALES table, but the SALES table has not yet been created. Which statement is true?
♦You must create the SALES table before creating the view.
♦By default, the view will be created even if the SALES table does not exist.
♦You can create the table and the view at the same time using the FORCE option.
♦You must create the SALES table before creating the view.
♦By default, the view will be created even if the SALES table does not exist.
♦You can create the table and the view at the same time using the FORCE option.
♦You can use the FORCE option to create the view before the SALES table has been created. (*)
34. The FACULTY table contains these columns:
FACULTYID VARCHAR2(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(15)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
STATUS VARCHAR2(2) NOT NULL
FACULTYID VARCHAR2(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(15)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
STATUS VARCHAR2(2) NOT NULL
The COURSE table contains these columns:
COURSEID VARCHAR2(5) NOT NULL PRIMARY KEY
SUBJECT VARCHAR2(5)
TERM VARCHAR2(6)
FACULTYID VARCHAR2(5) NOT NULL FOREIGN KEY
SUBJECT VARCHAR2(5)
TERM VARCHAR2(6)
FACULTYID VARCHAR2(5) NOT NULL FOREIGN KEY
You have been asked to compile a report that identifies all adjunct professors who will be teaching classes in the upcoming term. You want to create a view that will simplify the creation of this report. Which CREATE VIEW statements will accomplish this task?
♦CREATE VIEW pt_view AS
(SELECT first_name, last_name, status, courseid, subject, term
FROM faculty f, course c
WHERE f.facultyid = c.facultyid);
(*)
(SELECT first_name, last_name, status, courseid, subject, term
FROM faculty f, course c
WHERE f.facultyid = c.facultyid);
(*)
♦CREATE VIEW pt_view IN (SELECT first_name, last_name, status, courseid, subject, term
FROM faculty course);
FROM faculty course);
♦CREATE VIEW
(SELECT first_name, last_name, status, courseid, subject, term
FROM faculty, course
WHERE facultyid = facultyid);
(SELECT first_name, last_name, status, courseid, subject, term
FROM faculty, course
WHERE facultyid = facultyid);
♦CREATE VIEW pt_view
ON (SELECT first_name, last_name, status, courseid, subject, term
FROM faculty f and course c
WHERE f.facultyid = c.facultyid);
ON (SELECT first_name, last_name, status, courseid, subject, term
FROM faculty f and course c
WHERE f.facultyid = c.facultyid);
35. What is one advantage of using views?
♦To be able to store the same data in more than one place
♦To provide data dependence
♦To provide restricted data access (*)
♦To be able to store the same data in more than one place
♦To provide data dependence
♦To provide restricted data access (*)
36. 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;
(*)
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 companyname, city, orderid, orderdate, total
FROM customers, orders
WHERE custid = custid)
WITH READ ONLY;
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;
(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);
AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
FROM customers c, orders o
WHERE c.custid = o.custid);
37. You can create a view if the view subquery contains an inline view. True or False?
♦True (*)
♦False
♦True (*)
♦False
38. For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?
♦Prohibits DML actions without administrator CHECK approval
♦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. (*)
♦Allows for DELETES from other tables, including ones not listed in subquery
♦Prohibits DML actions without administrator CHECK approval
♦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. (*)
♦Allows for DELETES from other tables, including ones not listed in subquery
39. Only one type of view exists. True or False?
♦True
♦False (*)
♦True
♦False (*)
40. What is the purpose of including the WITH CHECK OPTION clause when creating a view?
♦To make sure that data is not duplicated in the view
♦To make sure that data is not duplicated in the view
♦To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)
♦To keep views form being queried by unauthorized persons
♦To make sure that the parent table(s) actually exist
♦To make sure that the parent table(s) actually exist
41. 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
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
FROM employees
ORDER BY salary)
WHERE ROWNUM <=10 AND job_id LIKE ‘CLERK’ AND department_id = 70;
FROM (SELECT last_name, first_name, salary
FROM employees
ORDER BY salary)
WHERE ROWNUM <=10 AND job_id LIKE ‘CLERK’ AND department_id = 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;
(*)
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;
(*)
♦SELECT ROWNUM “Ranking”, last_name||’ ,’||first_name “Employee”, salary “Salary”
FROM (SELECT last_name, first_name, salary, job_id, dept_id
FROM employees
WHERE ROWNUM <=10
ORDER BY salary)
WHERE job_id LIKE ‘CLERK’ AND department_id = 70;
FROM (SELECT last_name, first_name, salary, job_id, dept_id
FROM employees
WHERE ROWNUM <=10
ORDER BY salary)
WHERE job_id LIKE ‘CLERK’ AND department_id = 70;
♦The only way is to use the data dictionary.
42. Which of the following describes a top-N query?
♦A top-N query returns a limited result set, returning data based on highest or lowest criteria. (*)
♦A top-N query returns a result set that is sorted according to the specified column values.
♦A top-N query returns the top 15 records from the specified table.
♦A top-N query returns the bottom 15 records from the specified table.
♦A top-N query returns the top 15 records from the specified table.
♦A top-N query returns the bottom 15 records from the specified table.
43. Which statement about an inline view is true?
♦An inline view is a subquery in the FROM clause, often named with an alias. (*)
♦An inline view can be used to perform DML operations.
♦An inline view is a complex view.
♦An inline view is a schema object.
♦An inline view is a subquery in the FROM clause, often named with an alias. (*)
♦An inline view can be used to perform DML operations.
♦An inline view is a complex view.
♦An inline view is a schema object.
44. Which of these is not a valid type of View?
♦INLINE
♦COMPLEX
♦SIMPLE
♦ONLINE (*)
♦INLINE
♦COMPLEX
♦SIMPLE
♦ONLINE (*)
45. You must create a view that 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;
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?
♦UPDATE
♦INSERT
♦DELETE
♦INSERT
♦DELETE
♦SELECT (*)
InfoCad Blog 