96.
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
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| 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. |
97.
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
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| 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. |
98.
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
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| The FOR UPDATE OF clause is not supported in the view definition. The FOR UPDATE clause locks the rows, so it is not allowed. |
99.
The EMPLOYEE table has the following columns:
EMP_ID NUMBER (4) EMP_NAME VARCHAR2 (30) SALARY NUMBER (6,2) DEPT_ID VARCHAR2 (2) Which query will show the top five highest-paid employees?
- [A]
SELECT * FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY ASC) WHERE ROWNUM <= 5;
- [B]
SELECT EMP_NAME, SALARY FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM < 5;
- [C]
SELECT * FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM <= 5;
- [D]
SELECT EMP_NAME, SALARY (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC) WHERE ROWNUM = 5;
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| You can find the top five salaries using an inline view with the ORDER BY clause. The Oracle 11g Optimizer understands the top-n rows query. Option B would have been correct if you had ROWNUM <= 5 in the WHERE clause. |
100.
The EMPLOYEE table has the following columns:
EMP_ID NUMBER (4) PRIMARY KEY EMP_NAME VARCHAR2 (30) SALARY NUMBER (6,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
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| The view is based on a single table, and the only constraint on the table is the primary key. Although the view is defined with a WHERE clause, you have not enforced that check while using DML statements through the WITH CHECK OPTION clause. |
|
|
||||||||||||||||||||||||||||
