OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas
Review Questions
1. A view created with which option makes sure that rows added to the base table through the view are accessible to the view?
A. WHERE
B. WITH READ ONLY
C. WITH CHECK OPTION
D. CREATE OR REPLACE VIEW
----
WITH CHECK OPTION makes sure that the new rows added or the rows updated are accessible to the view. The WHERE clause in the view definition limits the rows selected in the view from the base table.
Ans: C.
2. A view is created using the following code. What operations are permitted on the view?
CREATE VIEW USA_STATES
AS SELECT * FROM STATE
WHERE CNT_CODE = 1
WITH READ ONLY;
A. SELECT
B. SELECT, UPDATE
C. SELECT, DELETE
D. SELECT, INSERT
---
When the view is created with the READ ONLY option, only reads are allowed from the view.
Ans: A.
3. How do you remove the view USA_STATES from the schema?
A. ALTER VIEW USA_STATES REMOVE;
B. DROP VIEW USA_STATES;
C. DROP VIEW USA_STATES CASCADE;
D. DROP USA_STATES;
-----
A view is dropped using the DROP VIEW view_name; command.
Ans: B.
4. Which data dictionary view has information on the columns in a view that are updatable?
A. USER_VIEWS
B. USER_UPDATABLE_COLUMNS
C. USER_COLUMNS
D. USER_COLUMNS_UPDATABLE
-----
The USER_UPDATABLE_COLUMNS view shows the columns that can be updated.
Ans: B.
5. Which option in view creation creates a view even if there are syntax errors?
A. CREATE FORCE VIEW ...
B. CREATE OR REPLACE VIEW ...
C. CREATE OR REPLACE VIEW FORCE ...
D. CREATE VIEW ... IGNORE ERRORS
----
The CREATE FORCE VIEW statement creates an invalid view, even if there are syntax errors. Normally, a view will not be created if there are compilation errors.
Ans: A.
6. In a join view, on how many base tables can you perform a DML operation (UPDATE/INSERT/DELETE) in a single step?
A. One
B. The number of base tables in the view definition
C. The number of base tables minus one
D. None
----
You can perform an INSERT, UPDATE, or DELETE operation on the columns involving only one base table at a time. There are also some restrictions on the DML operations you perform on a join view.
Ans: A.
7. The following code is used to define a view. The EMP table does not have a primary key or any other constraints.
CREATE VIEW MYVIEW AS
SELECT DISTINCT ENAME, SALARY
FROM EMP
WHERE DEPT_ID = 10;
Which operations are allowed on the view?
A. SELECT, INSERT, UPDATE, DELETE
B. SELECT, UPDATE
C. SELECT, INSERT, DELETE
D. SELECT
E. SELECT, UPDATE, DELETE
----
Since the view definition includes a DISTINCT clause, only queries are allowed on the view.
Ans: D.
8. Which two statements are used to modify a view definition?
A. ALTER VIEW
B. CREATE OR REPLACE VIEW
C. REPLACE VIEW
D. CREATE FORCE VIEW
E. CREATE OR REPLACE FORCE VIEW
-----
The OR REPLACE option in the CREATE VIEW statement is used to modify the definition of the view. The FORCE option can be used to create the view with errors. The ALTER VIEW statement is used to compile a view or to add or modify constraints on the view.
Ans: B, E.
9. You create a view based on the EMPLOYEES table using the following SQL.
CREATE VIEW MYVIEW AS SELECT * FROM EMPLOYEES;
You modify the table to add a column named EMP_SSN. What do you need to do to have this new column appear in the view?
A. Nothing, since the view definition is selecting all columns, the new column will appear in the view automatically.
B. Recompile the view using ALTER VIEW MYVIEW RECOMPILE.
C. Re-create the view using CREATE OR REPLACE VIEW.
D. Add the column to the view using ALTER VIEW MYVIEW ADD EMP_SSN.
----
When you modify the base table, the view becomes invalid. Recompiling the view will make it valid, but the new column will not be available in the view. This is because when you create the view using *, Oracle expands the column names and stores the column names in the dictionary.
Ans: C
10. You can view the constraints on the objects in your schema in the USER_CONSTRAINTS dictionary view. The CONSTRAINT_TYPE column shows the type of constraint. What is the type of constraint created when you create a view with the WITH CHECK OPTION clause?
A. R
B. C
C. V
D. F
----
The constraint type will be V for the constraints created on views with the WITH CHECK OPTION clause.
Ans: C.
11. Which types of constraints can be created on a view?
A. Check, NOT NULL
B. Primary key, foreign key, unique key
C. Check, NOT NULL, primary key, foreign key, unique key
D. No constraints can be created on a view.
----
You can create primary key, foreign key, and unique key constraints on a view. The constraints on views are not enforced by Oracle. To enforce a constraint it must be defined on a table.
Ans: B.
12. Which is a valid status of a constraint created on a view?
A. DISABLE VALIDATE
B. DISABLE NOVALIDATE
C. ENABLE NOVALIDATE
D. All of the above
----
Since the constraints on the view are not enforced by Oracle, the only valid status of a constraint can be DISABLE NOVALIDATE. You must specify this status when creating constraints on a view.
Ans: B.
13. The SALARY column of the EMPLOYEE table is defined as NUMBER (8,2), and the COMMISSION_PCT column is defined as NUMBER(2,2). A view is created with the following code.
CREATE VIEW EMP_COMM AS
SELECT LAST_NAME,
SALARY * NVL(COMMISSION_PCT,0) Commission
FROM EMPLOYEES;
What is the datatype of the COMMISSION column in the view?
A. NUMBER (8,2)
B. NUMBER (10,2)
C. NUMBER
D. FLOAT
----
When numeric operations are performed using numeric datatypes in the view definition, the resulting column will be a floating datatype, which is NUMBER without any precision or scale.
Ans: C.
14. Which clause in the SELECT statement is not supported in a view definition subquery?
A. GROUP BY
B. HAVING
C. CUBE
D. FOR UPDATE OF
E. ORDER BY
-----
The FOR UPDATE OF clause is not supported in the view definition. The FOR UPDATE clause locks the rows, so it is not allowed.
Ans: D.
15. The EMPLOYEE table has the following columns:
EMP_ID NUMBER (4)
EMP_NAME VARCHAR2 (30)
SALARY NUMBER (5,2)
DEPT_ID VARCHAR2 (2)
Which query will show the top-five highest paid employees?
A.
SELECT * FROM
(SELECT EMP_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY ASC)
WHERE ROWNUM <= 5;
B.
SELECT EMP_NAME, SALARY FROM
(SELECT *
FROM EMPLOYEES
ORDER BY SALARY DESC)
WHERE ROWNUM < 5;
C.
SELECT * FROM
(SELECT EMP_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;
D.
SELECT EMP_NAME, SALARY
(SELECT *
FROM EMPLOYEES
ORDER BY SALARY DESC)
WHERE ROWNUM = 5;
----
The top five salaries can be found using an inline view with the ORDER BY clause. Oracle9i optimizer understands the top-'n' rows query.
Ans: C.
16. The EMPLOYEE table has the following columns:
EMP_ID NUMBER (4) PRIMARY KEY
EMP_NAME VARCHAR2 (30)
SALARY NUMBER (5,2)
DEPT_ID VARCHAR2 (2)
A view is defined using the following SQL.
CREATE VIEW EMP_IN_DEPT10 AS
SELECT * FROM EMPLOYEE
WHERE DEPT_ID = 'HR';
Which INSERT statement will succeed through the view?
A.
INSERT INTO EMP_IN_DEPT10 VALUES (1000,
'JOHN',1500,'HR');
B.
INSERT INTO EMP_IN_DEPT10 VALUES (1001,
NULL,1700,'AM');
C.
INSERT INTO EMP_IN_DEPT10 VALUES (1002,
'BILL',2500,'AC');
D.
All of the above
----
The view is based on a single table and the only constraint on the table is the primary key. Although the view defined with a WHERE clause, we have not enforced that check while using DML statements through the WITH CHECK OPTION clause.
Ans: D.
17. To be able to modify a join view, the view definition should not contain which of the following in the top-level query? (Choose all that apply.)
A. DISTINCT operator
B. ORDER BY clause
C. Aggregate functions such as SUM, AVG, and COUNT
D. WHERE clause
E. GROUP BY clause
F. ROWNUM pseudo-column
----
To be able to update a base table using the view, the view definition should not have a DISTINCT clause, GROUP BY clause, START WITH clause, CONNECT BY clause, ROWNUM, set operators (UNION, UNION ALL, INTERSECT, or MINUS), or subquery in the SELECT clause.
Ans: A, C, E, F
18. What is an inline view?
A. A subquery appearing in the WHERE clause
B. A subquery appearing in the FROM clause
C. A view created using the same column names of the base table
D. A view created with an ORDER BY clause
-----
A subquery appearing in the FROM clause of the SELECT statement is similar to defining and using a view, hence the name inline view. The subquery in the FROM clause is enclosed in parentheses and may be given an alias name. The columns selected in the subquery can be referenced in the parent query, just as you would select from any normal table or view.
Ans: B.
19. Which of the following two statements are true?
A. A view can be created before creating the base table.
B. A view cannot be created before creating the base table.
C. A view will become invalid if the base table's column referred to in the view is altered.
D. A view will become invalid if any column in the base table is altered.
----
The CREATE FORCE VIEW statement can be used to create a view before its base table is created. Any modification to the table will invalidate the view. Use the ALTER VIEW
Ans: A, D.
20. Which pseudo-column (with an inline view) can be used to get the top-n rows from a table?
A. ROWID
B. ROW_ID
C. ROWNUM
D. ROW_NUM
----
The ROWNUM pseudo-column gives a record number for each row returned. The row number is assigned as the record is fetched; the number is not stored in the database.
Ans: C.
No comments:
Post a Comment