Section 15 Quiz
(Answer
all questions in this section)
1. Evaluate
this CREATE VIEW statement:
CREATE VIEW emp_view
AS SELECT SUM(salary)
FROM employees;
Which statement is true?
Mark for Review
(1) Points
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.
2. Which
keyword(s) would you include in a CREATE VIEW statement to create the view
whether or not the base table exists?
Mark for Review
(1) Points
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? Mark for Review
(1) Points
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? Mark for
Review
(1) Points
True
False
(*)
5. In
order to query a database using a view, which of the following statements
applies? Mark for Review
(1) Points
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? Mark for Review
(1) Points
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? Mark for Review
(1) Points
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?
Mark for Review
(1) Points
True
False
(*)
9. For a
View created using the WITH CHECK OPTION keywords, which of the following
statements are true? Mark
for Review
(1) Points
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 ______. Mark for Review
(1) Points
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? Mark for Review
(1) Points
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;
Which statement is true?
Mark for Review
(1) Points
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. (*)
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? Mark for Review
(1) Points
The
AS keyword
A
GROUP BY clause (*)
A
WHERE clause
The
IN keyword
14. How do
you remove a view? Mark for Review
(1) Points
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? Mark for Review
(1) Points
True
False
(*)
1. Which
statement about an inline view is true?
Mark for Review
(1) Points
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.
2. A
Top-N Analysis is capable of ranking a top or bottom set of results. True or
False? Mark for Review
(1) Points
True
(*)
False
3. Which
of these Keywords is typically used with a Top-N Analysis? Mark for Review
(1) Points
Number
Rowid
Rownum
(*)
Sequence
4. 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;
Which type of SQL command can be issued on the
CUST_CREDIT_V view?
Mark for Review
(1) Points
SELECT
(*)
UPDATE
DELETE
INSERT
5. 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 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.
6. 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? Mark for Review
(1) Points
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;
7. You
cannot insert data through a view if the view includes ______. Mark for Review
(1) Points
A
WHERE clause
A
GROUP BY clause (*)
A
column alias
A
join
8. For a
View created using the WITH CHECK OPTION keywords, which of the following
statements are true? Mark
for Review
(1) Points
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
9. Which
statement about performing DML operations on a view is true? Mark for Review
(1) Points
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.
10. Which option would you use when creating
a view to ensure that no DML operations occur on the view? Mark for Review
(1) Points
NOFORCE
FORCE
WITH
ADMIN OPTION
WITH
READ ONLY (*)
11. Unlike tables, views contain no data of
their own. True or False?
Mark for Review
(1) Points
True
(*)
False
12. 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?
Mark for Review
(1) Points
True
False
(*)
13. 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? Mark for Review
(1) Points
True
False
(*)
14. Which of the following statements is a
valid reason for using a view? Mark
for Review
(1) Points
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.
15. You need to create a view on the SALES
table, but the SALES table has not yet been created. Which statement is true? Mark for Review
(1) Points
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
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.
1. 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.
2. A view
can contain a select statement with a subquery. True or False? Mark for Review
(1) Points
True
(*)
False
3. You
need to create a view on the SALES table, but the SALES table has not yet been
created. Which statement is true?
Mark for Review
(1) Points
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. (*)
4. 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
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
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?
Mark for Review
(1) Points
CREATE
VIEW pt_view AS
(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);
CREATE
VIEW
(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);
5. What
is one advantage of using views? Mark
for Review
(1) Points
To
be able to store the same data in more than one place
To
provide data dependence
To
provide restricted data access (*)
6. 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? Mark for Review
(1) Points
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
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;
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);
7. You
can create a view if the view subquery contains an inline view. True or False? Mark for Review
(1) Points
True
(*)
False
8. For a
View created using the WITH CHECK OPTION keywords, which of the following
statements are true? Mark
for Review
(1) Points
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
9. Only
one type of view exists. True or False?
Mark for Review
(1) Points
True
False
(*)
10. What is the purpose of including the
WITH CHECK OPTION clause when creating a view?
Mark for Review
(1) Points
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
11. 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?
Mark for Review
(1) Points
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;
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;
(*)
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;
The
only way is to use the data dictionary.
12. Which of the following describes a top-N
query? Mark for Review
(1) Points
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.
13. Which statement about an inline view is
true? Mark for Review
(1) Points
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.
14. Which of these is not a valid type of
View? Mark for Review
(1) Points
INLINE
COMPLEX
SIMPLE
ONLINE
(*)
15. 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;
Which type of SQL command can be issued on the
CUST_CREDIT_V view?
Mark for Review
(1) Points
UPDATE
INSERT
DELETE
SELECT
(*)
The CUSTOMER_FINANCE table contains these columns:
BalasHapusCUSTOMER_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?
Inner query: WHERE ROWNUM = 800
Inner query: SELECT customer_id, new_balance ROWNUM
Outer query: ORDER BY new_balance DESC
Inner query: ORDER BY new_balance DESC (*)
he EMPLOYEES table contains these columns:
BalasHapusEMPLOYEE_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;
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;
(*)
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;
The only way is to use the data dictionary.
1- Given the following view, which operations would be allowed on the emp_dept view?
BalasHapusCREATE 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 all columns
SELECT, INSERT
SELECT, UPDATE of some columns, DELETE (*)
SELECT, DELETE
2- 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?
FORCE
WITH READ ONLY
OR REPLACE
WITH CHECK OPTION (*)
3- 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;
A complex view is created that returns the sum of salaries per department. (*)
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.
4- Which statement about performing DML operations on a view is true?
You cannot modify data in a view if the view contains a WHERE clause.
You can modify data in a view if the view contains a GROUP BY clause.
You cannot modify data in a view if the view contains a group function. (*)
You can delete data in a view if the view contains the DISTINCT keyword.
5- Which of these Keywords is typically used with a Top-N Analysis?
Rowid
Sequence
Rownum (*)
Number
6- 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;
(*)
INSERT INTO part_name_v (part_id, part_name, product_id, cost)
VALUES (857986, ムcylinderメ, 8790, 3.45);
DELETE FROM part_name_v
WHERE part_id = 56897;
UPDATE part_name_v
SET cost = cost * 1.23
WHERE part_id = 56990;
7- Any select statement can be stored in the database as a view. True or False
True (*)
False
8- Which statement about the CREATE VIEW statement is true?
A CREATE VIEW statement CANNOT contain a GROUP BY clause.
A CREATE VIEW statement CANNOT contain an ORDER BY clause.
A CREATE VIEW statement CANNOT contain a function.
A CREATE VIEW statement CAN contain a join query. (*)
9- In order to query a database using a view, which of the following statements applies?
You can retrieve data from a view as you would from any table. (*)
Use special VIEW SELECT keywords.
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.
1- Which statement would you use to alter a view?
BalasHapusALTER TABLE
CREATE OR REPLACE VIEW (*)
MODIFY VIEW
ALTER VIEW
2- Which keyword(s) would you include in a CREATE VIEW statement to create the view whether or not the base table exists?
OR REPLACE
WITH READ ONLY
FORCE (*)
NOFORCE
3- 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
CREATE OR REPLACE 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); (*)
DROP VIEW dj_view;
SELECT last_name, number_events FROM dj_view;
4- You cannot insert data through a view if the view includes ______.
A WHERE clause
A GROUP BY clause (*)
A column alias
A join
5- Which action can be performed by using DML statements?
Deleting records in a table (*)
Disabling an index
Creating PRIMARY KEY constraints
Altering a table
6- Unlike tables, views contain no data of their own. True or False?
True (*)
False
7- When you drop a table referenced by a view, the view is automatically dropped as well. True or False?
True
False (*)
8- When you drop a view, the data it contains is also deleted. True or False?
True
False (*)
9- Which of the following is true about ROWNUM?
It is the number assigned to each row returned from a query after it is ordered.
It is the number assigned to each row returned from a query as it is read from the table. (*)
It is the number of rows in a table.
None of the above
27. Which of the following DML operations is not allowed when using a Simple View created with read only?
BalasHapusMark for Review
(1) Points
INSERT
UPDATE
DELETE
All of the above (*)
30. An inline view is an unnamed select statement found:
Mark for Review
(1) Points
Enclosed in parentheses within the FROM clause of a surrounding query. (*)
In a special database column of a users table.
Enclosed in parentheses within the select list of a surrounding query.
In the user_views data dictionary view.
Which of the following keywords cannot be used when creating a view?
BalasHapusHAVING
WHERE
ORDER BY
They are all valid keywords when creating views. (*)
yang bener yang ini
Hapus