Consider the following exhibit of the JOB_HISTORY table and the query that follows. Answer the questions 32 and 33 below the query.

SQL> desc job_history

Name Null? Type

----------------------- -------- ----------------

EMPLOYEE_ID NOT NULL NUMBER(6)

START_DATE NOT NULL DATE

END_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2(10)

DEPARTMENT_ID NUMBER(4)

SELECT employee_id , first_name, last_name, job_id

FROM employees E

UNION

SELECT employee_id , first_name, last_name, job_id

From job_history;

32.How many times the each employee will get displayed by the above query?

A. 0

B. 1

C. 2

D. 4

33.What will be the outcome of the above query?

A. It displays the current and previous job details of the employees twice

B. It displays the current and previous job details of the employees only once

C. Either A or B

D. None of the above

Examine the given table structures and consider the following query and answer the questions 34 to 37 that follow:

SQL> DESC employees

Name Null? Type

----------------------- -------- ----------------

EMPLOYEE_ID NOT NULL NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

EMAIL NOT NULL VARCHAR2(25)

PHONE_NUMBER VARCHAR2(20)

HIRE_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2(10)

SALARY NUMBER(8,2)

COMMISSION_PCT NUMBER(2,2)

MANAGER_ID NUMBER(6)

DEPARTMENT_ID NUMBER(4)

SQL> desc job_history

Name Null? Type

----------------------- -------- ----------------

EMPLOYEE_ID NOT NULL NUMBER(6)

START_DATE NOT NULL DATE

END_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2(10)

DEPARTMENT_ID NUMBER(4)

SELECT employee_id , job_id, department_id

FROM employees

UNION

SELECT employee_id , job_id, department_id

From job_history;

34.Assuming that an employee with ID 121 has held 2 job IDs in his tenure in the company. Considering the above query, how many times will his records be displayed in the results?

A. Once

B. Twice

C. Thrice

D. None of the above

.

35.Assuming that the employee with ID 121 held two positions in two different departments - 10 and 20 in the company.He worked as 'SA_REP' in both the departments 10 and 20. What will be the outcome of the above query ?

A. 2 rows

B. 3 rows

C. No rows

D. ORA error

36.Which statements best describes the inference drawn from the questions 34 and 35?

A. There are duplicate values for job codes

B. The query executes but results produced are unexpected

C. There are no duplicate values for departments

D. None of the above

Answer: C.As the combination of the job codes and departments is unique, there are no duplicates obtained.

37.What will be the sorting in the result set obtained by the query?

A. Descending on Employee ID

B. Descending on Job ID

C. Ascending on Employee ID

D. Ascending on Department ID

Answer: C.The default sorting will be ascending based on the first column i.e.: Employee ID in this case.However, this behavior can be modified by placing a single ORDER BY clause at the end.

38.Which of the following operators will be used to obtain duplicate records from the component queries?

A. UNION

B. UNION ALL

C. MINUS

D. None of the above

Answer: B.UNION ALL doesn't eliminates the duplicate values.

39.What is the difference between the UNION and the UNION ALL operators?

A. There is no difference

B. UNION ALL displays duplicate values too

C. The output in the case of UNION ALL is not sorted by default

D. None of the above

Answer: B, C.When used with two SELECT statements, the UNION set operator returns the results of both queries. However, if there are any duplicates, they are removed, and the duplicated record is listed only once. To include duplicates in the results, use the UNION ALL set operator

40.What is true about the INTERSECT operator?

A. The number of columns and data types of the columns in the component queries should be the same

B. The names of the columns and data types of the columns in the component queries should be the same

C. Both A and B

D. None of the above

Answer: A.This is common property criteria feature of SET operators.

41.What can be said about the result set if the order of the intersected tables is altered when using INTERSECT?

A. The result is altered

B. The result remains the same

C. The sorting changes on alteration

D. None of the above

Answer: B.

42.What among the following is true about the INTERSECT operator?

A. It ignores NULL values

B. It does not ignore NULL values

C. It returns all the rows from the first component query

D. None of the above

Answer: B.

Date: 2016-01-14; view: 865