Answer the related questions 43 and 44 given below.

43.You need to display the names and job IDs of those employees who currently have a job title that is the same as their previous one. Which of the following queries will work? (Consider the table structures as given)

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)

A. SELECT employee_id , job_id, first_name, last_name

B. FROM employees

C. UNION

D. SELECT employee_id , job_id, first_name, last_name

E. FROM job_history;

F. SELECT employee_id , job_id, first_name, last_name

G. FROM employees

H. INTERSECT

I. SELECT employee_id , job_id, first_name, last_name

J. FROM job_history;

K. SELECT employee_id , job_id, first_name, last_name

L. FROM employees

M. UNION ALL

N. SELECT employee_id , job_id, first_name, last_name

O. FROM job_history;

P. None of the above

Answer: B.

44.Considering the above query i.e. Option B in question 43, what will be the result if the department ID is also included in the SELECT clause?

A. The result will be the same

B. The result will be different

C. The result will be the same but the order will be different

D. None of the above

Answer: A.The result can be interpreted as - the employees who have worked with the same job title in the same department.

45.What is true about the MINUS operator?

A. It returns all the rows from all the component queries

B. It returns only the common rows from all the component queries

C. It returns all the rows from the first query and not from the subsequent queries

D. It returns all distinct rows selected by the first query, but not present in the subsequent queries

Answer: D.MINUS set operator removes the second query's results from the output if they are also found in the first query's results

46.What can be said regarding the number of columns and data types of the component queries when a MINUS operator is used?

A. They should be the same, the data type might be different but they should belong to the same data type group.

B. They should be the same along with the names of the columns

C. Both A and B

D. None of the above

Answer: A.Common feature of SET operators.

47.You need to display the employee IDs of the employees who have not changed their jobs even once during tenure in the company. Which of the following queries will be correct in this case? (Consider the table structures as given)

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)

A. SELECT employee_id

B. FROM employees

C. UNION

D. SELECT employee_id

E. FROM job_history;

F. SELECT employee_id

G. FROM employees

H. INTERSECT

I. Select employee_id

J. FROM job_history;

K. SELECT employee_id

L. FROM employees

M. MINUS

N. Select employee_id

O. FROM job_history;

P. SELECT employee_id

Q. FROM employees

R. UNION ALL

S. SELECT employee_id

T. FROM job_history;

Answer: C.

Examine the given table structures and consider the following query answer the questions 48 and 49 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 , first_name, job_id

FROM employees

UNION

SELECT employee_id , NULL "first_name", job_id

FROM job_history;

48.What is true about the above query?

A. It throws an error as TO_CHAR (NULL) cannot be used

B. It executes successfully and gives the values for employees' id, first_name and current job role including duplicate rows

C. It executes successfully and gives the values for employees' id, first_name and all jobs held by the employees excluding duplicate rows

D. None of the above

Answer: C.Each query must contain the same number of columns, which are compared positionally. NULL can be substituted in place of column which is missing in the other query within the same compound query.

49.Considering the above query, if the UNION operator is replaced by the MINUS operator, what will the result mean?

A. The result shows those employees who have an entry in the JOB_HISTORY table

B. The result shows those employees who do not have an entry in the JOB_HISTORY, but they are present in the EMPLOYEES table

C. Either of A or B

D. None of the above

Answer: B.MINUS gives the unique results that are present in the first query but not the second query.

Consider the exhibit given below and answer the questions 50 and 51 that follow:

50.What will be the outcome of the following query?

SELECT AU_DETAILS

FROM AUDIT

UNION

SELECT AU_DETAILS

FROM AUDIT_YEARLY;

A. It executes successfully giving the correct results including the duplicate values

B. It executes successfully giving the correct results excluding the duplicate values

C. It throws an ORA error

D. None of the above

Answer: C.CLOB or LONG columns cannot be in the SELECT clause when using the UNION set operators.

51.What will be the outcome of the query if UNION is replaced with UNION ALL?

A. It will execute successfully giving the correct results including duplicate values

B. It throws an ORA error

C. It will execute successfully giving the correct results excluding duplicate values

D. It executes successfully but gives the incorrect results.

Answer: B..UNION, UNION ALL, INTERSECT and MINUS operators when used with a LONG or CLOB column throws error.

52.Assume that there are 4 component queries. How many SET operators can be used to combine them in a single compound query?

A. 1

B. 2

C. 4

D. 3

Answer: D.The SET operator to be used will are N-1 where N is the number of component queries.

53.What are SET operators called owning to the fact that two or more SELECTs are involved based on columns instead of rows when SET operators are used?

A. Horizontal joins

B. Cartesian Joins

C. Vertical joins

D. Outer joins

Answer: C.

54.What is the difference between a UNION and INTERSECT operators? (Choose only the best difference)

A. UNION combines the results of two component queries into one result set with duplicate values

B. INTERSECT returns only those rows that are returned by each of the two component queries

C. UNION gives the distinct values from the component queries, INTERSECT gives the common values from the component queries

D. Both B and C

Answer: C.

Date: 2016-01-14; view: 916