 معلومات العضو
|
|
|
|
إحصائية العضو
|
|
|
|
QUESTION NO: 14
Evaluate the SQL statement:
1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal
2 FROM employees a,
3 (SELECT dept_id, MAX(sal) maxsal
4. FROM employees
5 GROUP BY dept_id) b
6 WHERE a.dept_id = b.dept_id
7 AND a.sal < b.maxsal;
What is the result of the statement?
A. The statement produces an error at line 1.
B. The statement produces an error at line 3.
C. The statement produces an error at line 6.
D. The statement returns the employee name, salary, department ID, and maximum salary
earned in the department of the employee for all departments that pay less salary then
the maximum salary paid in the company.
E. The statement returns the employee name, salary, department ID, and maximum salary
earned in the department of the employee for all employees who earn less than the
maximum salary in their department.
Answer: E
Explanation:
The statement returns the employee name, salary, department ID, and maximum salary earned
in the department of the employee for all employees who earn less than the maximum salary
in their department. This query is example of an inline view which is the sub-query in the
FROM clause of the main query. The sub-query can be a SELECT statement that utilizes
joins, the GROUP BY clause, or the ORDER BY clause.
Incorrect Answers
A: The statement does not produce an error at line 1.
1z0 -007
B: The statement does not produce an error at line 3.
C: The statement does not produce an error at line 6.
D: The statement returns the employee name, salary, department ID, and maximum salary
earned in the department of the employee for all EMPLOYEES, NOT DEPARTMENTS, who
earn less than the maximum salary in their department.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 161-165
Chapter 4: Subqueries
QUESTION NO: 15
Examine the data in the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
EMPLOYEE_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA*DIR 6500
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Admin
20 Education
30 IT
40 Human Resources
Also examine the SQL statements that create the EMPLOYEES and DEPARTMENTS
tables:
CREATE TABLE departments
(department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(30));
CREATE TABLE employees
(EMPLOYEE_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(20),
DEPT_ID NUMBER REFERENCES
departments(department_id),
MGR_ID NUMBER REFERENCES
employees(employee id),
MGR_ID NUMBER REFERENCES
1z0 -007
employees(employee id),
JOB_ID VARCHAR2(15).
SALARY NUMBER);
ON the EMPLOYEES,
On the EMPLOYEES table, EMPLOYEE_ID is the primary key.
MGR_ID is the ID of managers and refers to the EMPLOYEE_ID.
DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table.
On the DEPARTMENTS table, DEPARTMENT_ID is the primary key.
Examine this DELETE statement:
DELETE
FROM departments
WHERE department id = 40;
What happens when you execute the DELETE statement?
A. Only the row with department ID 40 is deleted in the DEPARTMENTS table.
B. The statement fails because there are child records in the EMPLOYEES table with
department ID 40.
C. The row with department ID 40 is deleted in the DEPARTMENTS table. Also the
rows with employee IDs 110 and 106 are deleted from the EMPLOYEES table.
D. The row with department ID 40 is deleted in the DEPARTMENTS table. Also the
rows with employee IDs 106 and 110 and the employees working under employee 110
are deleted from the EMPLOYEES table.
E. The row with department ID 40 is deleted in the DEPARTMENTS table. Also all the
rows in the EMPLOYEES table are deleted.
F. The statement fails because there are no columns specifies in the DELETE clause of
the DELETE statement.
Answer: B
Explanation:
It will be error generated because there are 2 child records in the EMPLOYEES table with
department number you try to delete from the DEPARTMENTS table.
Incorrect Answers
A: The row with department ID 40 will not be deleted because of the child records in the
EMPLOYEES table.
C: Neither the row with department ID 40 will not be deleted not child records in the
EMPLOYEES table will be deleted.
D: It will be error when you try to execute the DELETE statement, no rows will be deleted in
the EMPLOYEES or the DEPARTMENTS tables.
E: It will be error when you try to execute the DELETE statement, no rows will be deleted in
the EMPLOYEES or the DEPARTMENTS tables.
F: The statement fails because of constraint violation not because there are no columns
specifies in the DELETE clause of the DELETE statement.
1z0 -007
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 240-245
Chapter 5: Creating Oracle Database Objects
QUESTION NO: 16
Which three are DATETIME data types that can be used when specifying column
definitions? (Choose three.)
A. TIMESTAMP
B. INTERVAL MONTH TO DAY
C. INTERVAL DAY TO SECOND
D. INTERVAL YEAR TO MONTH
E. TIMESTAMP WITH DATABASE TIMEZONE
Answer: A, C, D
Explanation:
TIMESTAMP, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH can be
used to specify column definition.
Incorrect Answers
B: The INTERVAL MONTH TO DAY data type cannot be used when specifying column
definitions there are only INTERVAL DAY TO SECOND and INTERVAL YEAR TO
MONTH data types.
E: The TIMESTAMP WITH DATABASE TIMEZONE data type cannot be used when
specifying column definitions, because there are only TIMESTAMP WITH TIME ZONE
and TIMESTAMP WITH LOCAL TIME ZONE data types.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 215-217
Chapter 5: Creating Oracle Database Objects
QUESTION NO: 17
Which SQL statement defines the FOREIGN KEY constraint on the DEPTNO column
of the EMP table?
A. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCNAR2(35),
deptno NUMBER(7,2) NOT NULL
CONSTRAINT emp_deptno_fk FOREIGN KEY deptno
REFERENCES dept deptno);
B. CREATE TABLE EMP
(empno NUMBER(4),
ename VARCNAR2(35),
deptno NUMBER(7,2)
1z0 -007
Leading the way in IT testing and certification tools, www.testking.com
- 19 -
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C. CREATE TABLE EMP
(empno NUMBER(4)
ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)
FOREIGN KEY (deptno));
D. CREATE TABLE EMP (empno NUMBER(4),
ename VARCNAR2(35),
deptno NUMBER(7,2) FOREIGN KEY
CONSTRAINT emp deptno fk REFERENCES dept (deptno));
Answer: B
Explanation:
This statement provides correct syntax to define the FOREIGN KEY constraint on the
DEPTNO column of the EMP table.
Incorrect Answers
A: There is incorrect syntax, because list of columns and column for the constraint need to be
surrounded with the brackets.
C: It is incorrect to use FOREIGN KEY keywords to define constraint on the table. It can be
used to add integrity constraint to existing table.
D: It is incorrect to use FOREIGN KEY keywords to define constraint on the table. It can be
used to add integrity constraint to existing table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 238-245
Chapter 5: Creating Oracle Database Objects |