Home Random Page


CATEGORIES:

BiologyChemistryConstructionCultureEcologyEconomyElectronicsFinanceGeographyHistoryInformaticsLawMathematicsMechanicsMedicineOtherPedagogyPhilosophyPhysicsPolicyPsychologySociologySportTourism






Examine the structure of the EMPLOYEES table and consider the following query. Answer the questions 55 to 60 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)

Query 1

SELECT *

FROM EMPLOYEES

where department_id = 10

Query 2

SELECT *

FROM EMPLOYEES E

where E.job_id IN (select first_name from EMPLOYEES E1 where E1.job_id = 'CLERK' and E.job_id = E1.job_id )

55.You need to extract a report where the results from both the queries are displayed. Which of the following operators should be used to get the required results?

A. UNION

B. UNION ALL

C. INTERSECT

D. None of the above



Answer: B.UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.

56.You need to display all the duplicate values along with all the values existing in the result set from both the queries. Which of the following SET operators you can use in the above given queries?

A. INTERSECT

B. UNION

C. MINUS

D. None of the above



Answer: D.UNION ALL will give the unsorted results with duplicates.

57.What is the difference between the result sets when using a UNION and a UNION ALL set operators?

A. Result set from UNION ALL is filtered including duplicate values

B. Result set from UNION is filtered and sorted including duplicate values

C. Result set from UNION ALL is not sorted and it has duplicate values

D. Result set from UNION is filtered and sorted without duplicate values

Answer: C, D.

58.The UNION operator has more overhead on the database than the UNION ALL. What is wrong in this statement?

A. The statement is correct

B. UNION ALL operator has more overhead on the Data base than the UNION operator

C. UNION has to sort and eliminate duplicates which results into additional overhead

D. None of the above



Answer: A, C.UNION has to perform more tasks than UNION ALL because it sorts and deduplicates the result sets. Hence it is recommended that unless distinct rows are required, UNION ALL should be used.

59.What will be the outcome if the two queries given above are combined using the INTERSECT operator?

A. It will display only those employees who are Clerks in the Department 10

B. It will display all those employees who are in the department 10

C. It will display all the Clerks.

D. None of the above



Answer: A.INTERSECT returns those records that are present in query 1 AND query 2.

60.What among the following is the difference between the INTERSECT and the UNION operators?

A. INTERSECT follows the 'AND' Boolean logic, UNION follows the 'OR' Boolean logic

B. UNION follows the 'OR' Boolean logic, whereas INTERSECT follows the 'AND' logic

C. Either of A or B

D. None of the above



Answer: A.

61.In which of the following SET operators, changing the order of the component queries will change the result set?

A. UNION

B. UNION ALL

C. MINUS

D. INTERSECT

Answer: C.MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

Consider the following query and answer the questions 62 to 66 that follow:

SELECT 4 from dual

INTERSECT

SELECT 1 from dual;

62.What will be the outcome of the given query?

A. No rows

B. 4

C. 1

D. NULL

Answer: A.No rows will be selected as the INTERSECT operator will not get any common results from both the queries - INTERSECT operators gives common results present in query 1 AND query 2.

63.What will be the outcome of the query if the INTERSECT operator is replaced with MINUS operator?

A. 3

B. 4

C. 0

D. 1

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

64.What will be the outcome of the above query if the INTERSECT operator is replaced with the UNION operator?

A. 1

B. 4

C. NULL

D. 0

Answer: A.UNION will produce distinct rows in the result set in ascending order.

65.What will be the outcome of the above query if the INTERSECT operator is replaced with the UNION ALL operator?

A. 4

B. 0

C. NULL

D. 1

Answer: A.UNION ALL displays the results as they are positioned in the query without sorting them.

66.What will be the outcome if the above query is modified as below?

SELECT 1 from dual

UNION ALL

SELECT 4 from dual;

A. 1

B. 4

C. NULL

D. None of the above



Answer: A.

Examine the JOB_HISTORY_ARCHIVE table structure. It is a backup table for the JOB_HISTORY table with no additional column. Assuming that both the table have dissimilar data, consider the query given below and answer the questions 67 to 70 that follow:

 

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 * FROM job_history;

MINUS

SELECT * FROM job_history_archive)

UNION ALL

(SELECT * FROM job_history_archive

MINUS

SELECT * FROM job_history;);

67. What will be the outcome of the query given above? (Choose the best answer)

A. It will return those rows that are different in the two tables

B. It will return the common rows in the two tables

C. It will return all the rows from the two tables

D. None of the above



Answer: A.

68.What can concluded if the above given query yields rows only from JOB_HISTORY table?

A. It shows that the JOB_HISTORY contains two rows different from JOB_HISTORY_ARCHIVE table

B. It shows that two rows are same in JOB_HISTORY and JOB_HISTORY_ARCHIVE tables

C. It shows that the JOB_HISTORY_ARCHIVE contains two rows different from JOB_HISTORY table

D. None of the above



Answer: A.

69.What can be said if the above query gives no results?

A. It shows that the two tables have same data

B. It shows that the component queries are wrongly placed

C. It shows that the SET operators are wrongly used in the compound query

D. None of the above



Answer: A.

70.With respect to the query given above, if duplicate records exist in the two tables, which of the following modifications should be made to the above given query?

A. COUNT(*)

B. COUNT(*) and GROUP BY employee_id

C. COUNT (*) and ORDER BY employee_id

D. None of the above



Answer: B.COUNT(*) can be used to see the difference between the tables.

Consider the following query:

SELECT 1 NUM, 'employee' TEXT FROM dual

UNION

SELECT TO_CHAR(NULL) NUM, 'departments' TEXT FROM dual;

71.What will be the outcome of the query given above?

A. NUM TEXT

B. ---------- -----------

C. 1 employee

D. departments

E. NUM TEXT

F. ---------- -----------

G. 1 employee

H. NULL departments

I. ORA error

J. NUM TEXT

K. ---------- -----------

L. departments

M. 1 employee

Answer: C.Here the numeric 1 is compared to a character NULL which throws the error "ORA-01790: expression must have same datatype as corresponding expression".

Consider the following query and answer the questions 72 and 73 that follow:

SELECT months_between (sysdate, to_date('21-MAY-2013','DD-MON-YYYY')) FROM dual

UNION

SELECT TO_date(NULL) NUM FROM dual;

72.What will be the outcome of the query given above? (Assume that the SYSDATE is 1st July, 2013)

A. It executes successfully with correct results

B. It executes successfully but with no results

C. It throws an ORA error

D. None of the above



Answer: C.NUMBER and DATE do not belong to same data type fail. Here a number obtained by MONTHS_BETWEEN is compared with a DATE and hence the error.

73.Assume that the SELECT statement in the 2nd query is modified as below:

SELECT to_number (NULL) NUM FROM dual;

What will be the outcome because of this change?

A. It executes successfully with correct results

B. It executes successfully but with no results

C. It throws an ORA error

D. None of the above



Answer: A.

74.Examine the table structures and consider the following query:

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 "Employee ID"

FROM employees

UNION

SELECT employee_id "EMP ID"

FROM job_history;

Which of the below column headings will display in the result set?

A. EMP ID

B. Employee ID

C. EMPLOYEE_ID

D. ORA error because the column names must be same in the component queries.

Answer: B.The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

Examine the two table structures given and consider the following query and answer the questions 75 and 76 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

FROM employees e

UNION

SELECT employee_id

FROM job_history j

ORDER BY j.employee_id ;

75.What will be the outcome of the query given above?

A. The results will be ordered by the employee ID from the JOB_HISTORY table

B. The results will be ordered by the employee ID from the EMPLOYEES table

C. There will be no ordering of the results

D. ORA error

Answer: D.The ORDER BY should be done based on the names of the columns from the first query and not from the 2nd query columns.

76.Which of the following ORDER BY clauses can replace the erroneous ORDER BY in the query given above?

A. ORDER BY e.employee_id

B. ORDER BY j.2

C. ORDER BY 1

D. None of the above, ORDER BY is not allowed in the query

Answer: C.This is a more generic specification and Oracle will order based on the first column of the first query.

77.Consider the following exhibit and answer the question below:

 

SELECT au_doc

From audit

UNION

SELECT au_doc

From audit_yearly;

What will be the outcome of the above given query?

A. It gives the Audit documents between the two tables

B. It gives an ORA error on execution

C. It gives the Audit documents from the table AUDIT

D. None of the above



Answer: B.LONG columns cannot be used with SET operators.

78.Consider the query given below:

SELECT col_1

From TABLE (package1.proc1)

UNION

SELECT col_1

From TABLE (package2.proc2);

What will be the outcome of the query given above?

A. It executes successfully with duplicates

B. It executes successfully without duplicates

C. It throws an ORA error

D. None of the above



Answer: C.TABLE expressions cannot be used with SET operators.

Examine the two table structures given and consider the following query. Answer the questions 79 and 80 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

FROM employees E

UNION

SELECT employee_id , job_id

FROM job_history J

FOR UPDATE OF job_id;

79. What happens when the query is executed?

A. ORA error

B. Employee_id and job_id

C. Employee_id

D. None of the above



Answer: A.The FOR UPDATE clause cannot be used with the query combined using the SET operators.

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

SELECT * from employees

UNION

SELECT job_id FROM job_history;;

A. It will give all the columns from the employees tables and only the job_id column from the job_history table

B. It will throw an error as the number of columns should match in the component queries

C. Neither B or C

D. None of the above



Answer: B.

81.If UNION, UNION ALL, INTERSECT are used in one SQL statement which of the following is true regarding the SQL statement?

A. UNION, UNION ALL will be executed first and then the result set will go for the INTERSECT statement.

B. The execution of INTERSECT will precede the UNION and UNION ALL execution.

C. The execution will be done from right to left taking into consideration all the operators at the same time.

D. The execution will be done from left to right taking into consideration all the operators at the same time.

Answer: D.

82.Consider the query given below and answer the question that follow:

SELECT '3' FROM dual

INTERSECT

SELECT 3f FROM dual;

What is true regarding the execution of the query given above?

A. It executes successfully.

B. It throws an error

C. It gives the result 3.

D. It gives the result 3f

Answer: B.Character literals must be enclosed within single quotes.

83.Which of the following is false for set operators used in SQL queries?

A. The set operators are valid when used on columns with the LONG datatype.

B. The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.

C. In order for the select query containing an expression, a column alias should be provided in order to refer it to the order_by_clause.

D. You cannot use these operators in SELECT statements containing TABLE collection expressions.

Answer: A.SET operators are unsupported for LONG, CLOB and BLOB data types.

84.Examine the given table structure and evaluate the following SQL statement:

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)

SELECT employee_id , last_name "Last Name"

FROM employees

WHERE department_id = 100

UNION

SELECT employee_id EMPLOYEE_NO, last_name

FROM employees

WHERE department_id = 101;

Which ORDER BY clauses are valid for the above query? (Choose all that apply.)

A. ORDER BY 2,1

B. ORDER BY EMPLOYEE_NO

C. ORDER BY 2, employee_id

D. ORDER BY "EMPLOYEE_NO"

Answer: A, C.The ORDER BY clause must reference column by its position or the name referred by the first query.

85.Which of the following clauses would you use to exclude the column from the 2nd query out of the two queries combined using SET operators?

A. GROUP BY

B. ORDER BY

C. MINUS

D. UNION

Answer: C.

86.Examine the given table structure as given. What will be the outcome of the below query?

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)

SELECT distinct department_id

FROM employees

WHERE salary > ANY (SELECT AVG (salary)

FROM employees

GROUP BY department_id )

UNION

SELECT *

FROM employees

WHERE salary > ANY (SELECT MAX (salary)

FROM employees

GROUP BY department_id );

A. It will display all the department IDs which have the average salaries and the maximum salaries

B. It will throw an ORA error as the no. of columns selected in both the query is different

C. It will display all the department IDs which have the average salaries

D. It will display all the department IDs which have the maximum salaries

Answer: B.The no. of columns should be the same.

87.What among the following is true about the UNION operator?

A. UNION operates over only the first column in the SELECT list

B. UNION operates over the first columns of the SELECT lists in the component queries

C. UNION operates over all the columns being selected.

D. None of the above



Answer: C.UNION operates over all the columns in the SELECT list and does not ignore any columns.

88.You need to display the departments where the employees with the JOB IDs 'SA_REP' or 'ACCOUNTANT' work. Which of the following queries will fetch you the required results? (Consider the given table structure)

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)

A. SELECT department_id

B. FROM employees E

C. Where job_id = 'SA_RE'

D. UNION

E. SELECT department_id

F. FROM employees E

G. Where job_id = 'ACCOUNTANT';

H. SELECT department_id

I. FROM employees E

J. Where job_id = 'SA_REP'

K. UNION ALL

L. Select department_id

M. FROM employees E

N. Where job_id = 'ACCOUNTANT';

O. SELECT department_id

P. FROM employees E

Q. Where job_id = 'SA_REP'

R. INTERSECT

S. Select department_id

T. FROM employees E

U. Where job_id = 'ACCOUNTANT';

V. SELECT department_id

W. FROM employees E

X. Where job_id = 'SA_REP'

Y. MINUS

Z. Select department_id

AA. FROM employees E

BB. Where job_id = 'ACCOUNTANT';

Answer: A.

89.Which of the following statement is true about the ordering of rows in a query which uses SET operator?

A. It is not possible to use ORDER BY in the individual queries that make a compound query.

B. An ORDER BY clause can be appended to the end of a compound query.

C. The rows returned by a UNION ALL will be in the order they occur in the two source queries.

D. The rows returned by a UNION will be sorted across all their columns, right to left.

Answer: A, B, C.

90.The UNION operator was used to fulfill which of the following function before the ANSI SQL syntax in place?

A. RIGHT OUTER JOIN

B. LEFT OUTER JOIN

C. EQUI-JOIN

D. FULL OUTER JOIN

Answer: D.

Answer the related questions 91 and 92 given below. Consider the table structures as given here:

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)

91.You need to find the job IDs which do not have any JOB history logged for them. Which of the following queries will work? (Consider the given table structures)

A. SELECT job_id

B. FROM employees

C. UNION ALL

D. SELECT job_id

E. FROM job_history;;

F. SELECT job_id

G. FROM employees

H. MINUS

I. Select job_id

J. FROM job_history;;

K. SELECT job_id

L. FROM employees

M. UNION

N. SELECT job_id

O. FROM job_history;;

P. None of the above

Answer: B.

92.Consider the following query:

SELECT distinct job_id

FROM employees

NATURAL JOIN job_history ;

Which of the following queries are identical to the above query?

A. SELECT job_id

B. FROM employees

C. UNION

D. SELECT job_id

E. FROM job_history;;

F. SELECT job_id

G. FROM employees

H. UNION ALL

I. SELECT job_id

J. FROM job_history;;

K. SELECT job_id

L. FROM employees

M. MINUS

N. Select job_id

O. FROM job_history;;

P. SELECT job_id

Q. FROM employees

R. INTERSECT

S. SELECT job_id

T. FROM job_history;;

Answer: A.

Examine the table structures given here. Consider the query given below and answer the related questions 93 to 97 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 job_id

FROM employees

UNION ALL

SELECT job_id

FROM job_history;;

93.If the EMPLOYEES table contains 5 records and the JOB_HISTORY contains 3 records, how many records will be obtained from the below query?

A. 4

B. 3

C. 0

D. 8

Answer: D.UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.

94.If the UNION ALL operator is replaced with UNION operator, how many records will be obtained? (Assume there are 6 distinct values in both the tables)

A. 5

B. 3

C. 2

D. 6

Answer: D.UNION Returns the combined rows from two queries, sorting them and removing duplicates.

95.If the UNION ALL operator is replaced with MINUS operator, how many records will be obtained? (Assume there are 3 distinct values in EMPLOYEES and 2 in JOB_HISTORY)

A. 3

B. 2

C. 1

D. 0

Answer: C.MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

96.If the UNION ALL operator is replaced with INTERSECT operator, how many records will be obtained? (Assume there are 3 values common between the two tables)

A. 8

B. 6

C. 3

D. 2

Answer: C.INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.

97.Consider the following query:

1.select job_id

2. from employees

3.ORDER BY department_id

4.UNION ALL

5.select job_id

6.FROM job_history;

7.ORDER BY department_id ;

The above query generates an error. Which line in the above query generates an error?

A. 3

B. 7

C. 2

D. No error is obtained

Answer: A.ORDER BY should only appear at the end of the compound query and not in the component queries.

98.Which of the following SET operator features are supported in SQL/Foundation:2003 but not by Oracle?

A. UNION ALL

B. MINUS ALL

C. INTERSECT ALL

D. EXCEPT ALL

Answer: B, C, D.

99.You need to find out the common JOB IDs (excluding duplicates) in the departments 100 and 200. Which query will you fire to get the required results? (Consider the table structure 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)

A. SELECT job_id from employee

B. WHERE department_id = 100

C. INTERSECT

D. SELECT job_id from employee

E. WHERE department_id = 200;

F. SELECT job_id from employee

G. WHERE department_id = 100

H. UNION ALL

I. SELECT job_id from employee

J. WHERE department_id = 200;

K. SELECT job_id from employee

L. WHERE department_id = 100

M. MINUS

N. Select job_id from employee

O. WHERE department_id = 200;

P. SELECT job_id from employee

Q. WHERE department_id = 100

R. INTERSECT ALL

S. Select job_id from employee

T. WHERE department_id = 200;

Answer: A.

100.If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first? (Choose the best answer.)

A. The INTERSECT, because INTERSECT has higher precedence than MINUS.

B. The MINUS, because MINUS has a higher precedence than INTERSECT.

C. The precedence is determined by the order in which they are specified.

D. It is not possible for a compound query to include both MINUS and INTERSECT.

Answer: C.All set operators have equal precedence, so the precedence is determined by the sequence in which they occur.

 


Date: 2016-01-14; view: 625


<== previous page | next page ==>
Answer the related questions 43 and 44 given below. | Classification of compounds
doclecture.net - lectures - 2014-2019 year. Copyright infringement or personal data (0.039 sec.)