 معلومات العضو
|
|
|
|
إحصائية العضو
|
|
|
|
QUESTION NO: 18
Evaluate the set of SQL statements:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCNAR2(14),
loc VARCNAR2(13));
ROLLBACK;
DESCRIBE DEPT
What is true about the set?
A. The DESCRIBE DEPT statement displays the structure of the DEPT table.
B. The ROLLBACK statement frees the storage space occupies by the DEPT table.
C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not
exist.
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 20 -
D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if
there is a COMMIT statement introduced before the ROLLBACK statement.
Answer: A
Explanation:
The structure of the DEPT table will be displayed because the CREATE TABLE statement is
DDL operation and it cannot be rolled back because implicit commit occurs on the database
when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a
create table statement, user to create a database object, or an alter table statement, used to
alter a database object.
Incorrect Answers
B: The ROLLBACK statement has nothing to do with the storage space of the DEPT table.
C: The DESCRIBE DEPT statement does not produce the error. It displays the structure of
the DEPT table.
D: The COMMIT statement does not need to be introduced because implicit commit occurs
on the database after creation of the table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-283
Chapter 6: Manipulating Oracle Data
QUESTION NO: 19
Which data dictionary table should you query to view the object privileges granted to
the user on specific columns?
A. USER_TAB_PRIVS_MADE
B. USER_TAB_PRIVS
C. USER_COL_PRIVS_MADE
D. USER_COL_PRIVS
Answer: D
Explanation:
The USER_COL_PRIVS data dictionary view will show the object privileges granted to the
user on specific columns.
Incorrect Answers
A: There is no USER_TAB_PRIVS_MADE view in Oracle.
B: The USER_TAB_PRIVS data dictionary view is used to show the object privileges
granted to the user on the tables, not specific columns.
C: There is no USER_COL_PRIVS_MADE view in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access Control in Oracle
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 21 -
QUESTION NO: 20
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
E
MPLOYEES
Column name Data type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2 (30)
JOB_ID VARCHAR2 (20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_ID COLUMN
DEPARTMENT ID NUMBER Foreign key to DEPARTMENT ID
column of the DEPARTMENTS table
DEPARTMENTS
Column name Data type Remarks
DEPARTMENT_ID NUMBER NOT NULL, Primary Key
DEPARTMENT_NAME VARCHAR2(30)
MGR_ID NUMBER References MGR_ID column of the
EMPLOYEES table
Evaluate this SQL statement:
SELECT employee_id, e.department_id, department_name,
salary
FROM employees e, departments d
WHERE e.department_id = d.department_id;
Which SQL statement is equivalent to the above SQL statement?
A. SELECT employee_id, department_id, department_name,
salary
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments);
B. SELECT employee_id, department_id, department_name,
salary
FROM employees
NATURAL JOIN departments;
C. SELECT employee_id, d.department_id, department_name,
salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
D. SELECT employee_id, department_id, department_name,
Salary
FROM employees
JOIN departments
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 22 -
USING (e.department_id, d.department_id);
Answer: C
Explanation:
This query shows correct JOIN ON clause syntax and provides equivalent to the above SQL
statement.
Incorrect Answers
A: This statement will show data only for the EMPLOYEES table with records that have
department ID from DEPARTMENTS table, not join result of two tables.
B: NATURAL join selects rows from the tables that have equal values in all matched
columns (same column names). If the columns having the same names have different
datatypes, an error is returned.
D: There is incorrect usage of JOIN clause with USING keyword.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 112-114
Chapter 3: Advanced Data Selection in Oracle
QUESTION NO: 21
The EMP table contains these columns:
LAST NAME VARCHAR2(25)
SALARY NUMBER(6,2)
DEPARTMENT_ID NUMBER(6)
You need to display the employees who have not been assigned to any department.
You write the SELECT statement:
SELECT LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMP
WHERE DEPARTMENT_ID = NULL;
What is true about this SQL statement?
A. The SQL statement displays the desired results.
B. The column in the WHERE clause should be changed to display the desired results.
C. The operator in the WHERE clause should be changed to display the desired results.
D. The WHERE clause should be changed to use an outer join to display the desired
results.
Answer: C
Explanation:
The operator in the WHERE clause should be changed to display the desired results. There are
times when you want to substitute a value in place of NULL. Oracle provides this
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 23 -
functionality with a special function, called NVL(). You cannot use operation equal with
NULL, but you can achieve desired results using NVL() function after the WHERE clause.
Incorrect Answers
A: The SQL statement will generate an error because you cannot use operation equal with
NULL.
B: The column in the WHERE clause should not be changed to display the desired results.
D: Since there is only one table used in this query you don’t need to use outer join to display
the desired results.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 31-32
Chapter 1: Overview of Oracle Databases
QUESTION NO: 22
Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2)
FROM dual;
What will be displayed?
A. 0
B. 1
C. 0.00
D. An error statement
Answer: A
Explanation:
Result will be 0. MOD(x,y) function calculates the modulus of x, defined in long division as
the integer remainder when x is divided by y until no further whole number can be produced.
TRUNC() function truncates x to the decimal precision of y. ROUND(x,y) rounds x to the
decimal precision of y.
Incorrect Answers
B: Result will be 0, not 1.
C: Result will be 0, not 0.00 because MOD(1600,10) return 0 and all other functions
(TRUNC and ROUND) return 0 also.
D: There is no error in this statement.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 69-71
Chapter 2: Limiting, Sorting, and Manipulating Return Data |