Examine the structure of the EMPLOYEES and DEPARTMENTS tables and consider the following query and answer the questions 22 and 23.

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 departments

Name Null? Type

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

DEPARTMENT_ID NOT NULL NUMBER(4)

DEPARTMENT_NAME NOT NULL VARCHAR2(30)

MANAGER_ID NUMBER(6)

LOCATION_ID NUMBER(4)

SELECT department_id

FROM employees e

UNION

SELECT department_id

FROM departments

22.What will be displayed in the result of this query?

A. It will display distinct department id(s) contained jointly in EMPLOYEES and DEPARTMENTS table

B. It will throw ORA error

C. No rows selected

D. None of the above

23.What is true about the query given above?

A. This query returns an ORA error

B. It executes successfully but gives no results

C. Queries from different tables cannot be used with the SET operators

D. The query executes successfully and gives the results as expected

24.What is the default sorting order of the results when UNION ALL operator is used?

A. Descending

B. Ascending

C. Either A or B

D. All of the above

25.What will be the output of the compound query in which columns in the SELECT are of CHAR and equal length?

A. The output will have VARCHAR2 data type of equal length

B. The output will have CHAR data type of equal length

C. The output will have CHAR data type of different lengths

D. The output will have NUMBER data type of equal length

26.What will be the output of the compound query in which columns in the SELECT are of CHAR and different lengths?

A. The output will have VARCHAR2 data type of equal length

B. The output will have CHAR data type of equal length

C. The output will have CHAR data type of different lengths

D. The output will have VARCHAR2 data type with the length of the larger CHAR value

27.What will be the output of a compound query if either or both queries select values of VARCHAR2?

A. The output will have VARCHAR2 data type.

B. The output will have CHAR data type of equal length

C. The output will have CHAR data type of different lengths

D. The output will have VARCHAR2 data type with the length of the larger CHAR value

28.What is true if the compound queries select numeric data?

A. There will be an equal precedence of the numeric values, operators

B. The return values will be determined by the numeric precedence

C. The return values will be of NUMBER data type

D. None of the above

29.What will happen if the SELECT list of the compound queries returns both a VARCHAR2 and a NUMBER data type result?

A. Oracle will convert them implicitly and return a VARCHAR2 data type result

B. Oracle will convert them implicitly and return a NUMBER data type result

C. An ORA error is thrown

D. None of the above

30.What is true about the UNION operator?

A. It eliminates the duplicate values ignoring NULL values

B. It returns duplicate values ignoring NULL values

C. It returns duplicate values including NULL values

D. It eliminates duplicate values and does not ignore NULL values

31.What can be said about the names and columns of a SQL query which uses the UNION operator?

A. The names of the columns should be identical

B. The names and data type of the columns should be identical

C. The names of the columns need not be identical

D. None of the above

.

Date: 2016-01-14; view: 869