Review Questions – Chapter 8

                                    Review Questions – Chapter 8

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
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
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;
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
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
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
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
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
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.
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
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.
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
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
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
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? SELECT * FROM (SELECT EMP_NAME, SALARY  FROM   EMPLOYEES  ORDER BY SALARY ASC) WHERE ROWNUM <= 5; SELECT EMP_NAME, SALARY FROM (SELECT *  FROM   EMPLOYEES  ORDER BY SALARY DESC) WHERE ROWNUM < 5; SELECT * FROM (SELECT EMP_NAME, SALARY  FROM   EMPLOYEES  ORDER BY SALARY DESC) WHERE ROWNUM <= 5; SELECT EMP_NAME, SALARY (SELECT *  FROM   EMPLOYEES  ORDER BY SALARY DESC) WHERE ROWNUM = 5;
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? INSERT INTO EMP_IN_DEPT10 VALUES (1000, 'JOHN',1500,'HR'); INSERT INTO EMP_IN_DEPT10 VALUES (1001, NULL,1700,'AM'); INSERT INTO EMP_IN_DEPT10 VALUES (1002, 'BILL',2500,'AC'); D.   All of the above
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
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
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.
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

Answers

1.	C. 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.
2.	A. When the view is created with the READ ONLY option, only reads are allowed from the view.
3.	B. A view is dropped using the DROP VIEW view_name; command.
4.	B. The USER_UPDATABLE_COLUMNS view shows the columns that can be updated.
5.	A. 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.
6.	A. 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.
7.	D. Since the view definition includes a DISTINCT clause, only queries are allowed on the view.
8.	B, E. 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.
9.	C. 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.
10.	C. The constraint type will be V for the constraints created on views with the WITH CHECK OPTION clause.
11.	B. 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.
12.	B. 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.
13.	C. 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.
14.	D. The FOR UPDATE OF clause is not supported in the view definition. The FOR UPDATE clause locks the rows, so it is not allowed.
15.	C. The top five salaries can be found using an inline view with the ORDER BY clause. Oracle9i optimizer understands the top-'n' rows query.
16.	D. 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.
17.	A, C, E, F. 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.
18.	B. 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.
19.	A, D. 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 <view name> COMPILE statement to recompile the view.
20.	C. 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.