*
UNIONUNION ALLINTERSECTMINUS

Answer: A. Set operators are supplied to integrate the outcomes of two (or more) pick statements.Valid collection operators in Oracle 11g room UNION, UNION ALL, INTERSECT, and also MINUS. When supplied with two choose statements, the UNION collection operator returns the outcomes of both queries.However,if over there are any duplicates, they are removed, and the replicated record is detailed only once.To include duplicates in the results,use the UNION ALL collection operator.INTERSECT lists just records the are reverted by both queries; the MINUS collection operator clears the second query"s outcomes from the output if they are likewise found in the an initial query"s results. INTERSECT and also MINUS collection operations produce unduplicated results.

You are watching: The _____ operator could be used in place of intersect if the dbms does not support it.

2.Which collection operator walk the following figure indicate?

*
UNIONUNION ALLINTERSECTMINUS

Answer: B. UNION every Returns the merged rows from 2 queries there is no sorting or remove duplicates.

sql_certificate

3.Which collection operator go the following number indicate?

*
UNIONUNION ALLINTERSECTMINUS

Answer: C. INTERSECT Returns just the rows that take place in both queries" an outcome sets, sorting them and removing duplicates.

4.Which collection operator does the following number indicate?

*
UNIONUNION ALLINTERSECTMINUS

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

5.What is true about set operators?

They readjust values of rowsThey integrate the results of just two ingredient queries into one resultThey combine the results of 10 ingredient queries right into two an outcome sets.They incorporate the outcomes of two or an ext component queries into one result

Answer: D. Set operators are provided to incorporate the results of two (or more) choose statements. Valid set operators in Oracle 11g space UNION, UNION ALL, INTERSECT, and also MINUS.

6.What space the queries containing set operators called?

Sub-queriesCo-related sub-queriesGROUP by queriesCompound queries

Answer: D.

7.What is true about the UNION operator?

It returns rows from the combined queries in addition to NULL valuesIt return rows for the linked queries after ~ eliminating duplicatesIt return rows for the linked queries in addition to duplicate valuesIt return rows for the combined queries ignoring the NULL values

Answer: B. UNION returns the linked rows from 2 queries, sorting them and removing duplicates.

8.What is true around the UNION all operator?

It return rows from the an unified queries along with NULL valuesIt return rows because that the combined queries ~ eliminating duplicatesIt returns rows for the merged queries in addition to duplicate valuesIt returns rows because that the combined queries ignoring the NULL values

Answer: C. UNION every Returns the merged rows from 2 queries without sorting or removed duplicates.

9.What is true about the crossing operator?

It return rows from the merged queries in addition to NULL valuesIt return rows for the combined queries after ~ eliminating duplicatesIt returns the usual rows native the linked queriesNone the the above

Answer: C. INTERSECT Returns just the rows that occur in both queries" an outcome sets, sorting them and removing duplicates.

10.What is true around the MINUS operator?

It returns rows indigenous the an initial query yet not from the 2nd queryIt return rows for the second query however not native the an initial queryIt return duplicate rows for the linked queries It return rows for the merged queries skipping the NULL values

Answer: A. MINUS Returns only the rows in the an initial result set that do not appear in the second an outcome set, sorting them and removing duplicates.

11.What is the precedence that the collection operators UNION, UNION ALL, INTERSECT and also MINUS?

UNION, UNION ALL, INTERSECT and MINUSMINUS, UNION, UNION ALL and INTERSECTINTERSECT, MINUS, UNION ALL, UNIONEqual precedence

Answer: D. SET operators have actually an equal precedence.

12.What is the bespeak of review of collection operators?

Left to RightRight to LeftRandom EvaluationTop to Bottom

Answer: A, D. Assuming the there space no group of queries making use of parentheses, the set operators will certainly be evaluate from peak to bottom and left to ideal horizontally.

13.In i beg your pardon of the adhering to cases, parenthesis need to be specified?

When intersect is supplied with other collection operatorsWhen UNION is supplied with UNION ALLWhen MINUS is supplied for the queriesNone of the above

Answer: A. Using parenthesis will explicitly adjust the stimulate of evaluation when INTERSECT is used with various other operators.

14.What is true about the select clause when set operators room used?

There is no limit on the columns being selectedThe columns, expressions provided in the select clause must match in number in the an unified queriesThe columns, expressions provided in the select clause need to be N in the an initial query and also N-1 in the subsequent merged queriesBoth B and C

Answer: B. All the an unified should have actually the very same no. The columns when using collection operators. The corresponding columns in the queries that consist of a compound query should be that the exact same data kind group.

15.What is true about the collection operators?

The select clause should have actually the same number of columns, data species can it is in differentThe collection operators have the right to be supplied only because that combining 2 queriesThe data type of each tower in the second query must match the data form of its corresponding column in the an initial query.None of the above

Answer: C. All the an unified should have the same no. The columns when using set operators. The equivalent columns in the queries that comprise a compound query should be of the exact same data type group.

16.Where deserve to the order BY clause be supplied in instance when set operators are used?

In every of the queries being linked In the an initial query just At the very end the the link query none of the over

Answer: C. If the bespeak BY i is provided in between any of the queries joined using collection operators, it will certainly throw an ORA error.

17.What is true about the queries that have collection operators in their whereby clause?

These queries must have actually the same no. And data type of columns in their select clause. The no. The columns supplied in the wherein clause query and also the main pick can be various The no. Of columns provided in the whereby clause have to be the same, the data type can be various None the the over

Answer: A. All the merged should have actually the same no. That columns when using collection operators. The corresponding columns in the queries that make up a compound query need to be the the exact same data form group.

18.What is true around the columns in the 2nd query with respect come the columns in the very first query?

The column in the 2nd query should be in the same data type group as the corresponding column in the first query If a obelisk in the first query is a NUMBER, the equivalent column in the 2nd query should be a VARCHAR2 If a column in the first query is a NUMBER, the matching column in the 2nd query have to be also be NUMBER. None of the over

Answer: A, C.

19.What amongst the following is true about collection operators?

SET operator cannot be used in sub-queries set operators have the right to only be supplied in the where clause order BY have the right to be supplied for all queries combined by a collection operator collection operators deserve to be used in sub-queries

Answer: D.

20.What is the best means to readjust the precedence of collection operators offered the truth that they have equal precedence?

The stimulate of consumption of the set operators have the right to be adjusted to change the precedence The same precedence cannot be readjusted Parenthesis can be supplied to adjust the precedence no one of the above

Answer: C. Parenthesis deserve to be offered to team the particular queries in order to adjust the precedence explicitly. Bracket are wanted over other set operators during execution.

21.What can be said around duplicate values and set operators?

No set operator display screens duplicate valuesAll collection operators can display screen duplicate valuesOnly UNION every operator displays duplicate valuesNone the the above

Answer: C. UNION, INTERSECT and also MINUS instantly eliminate duplicate values

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

SQL> DESC employees name Null? type ----------------------- -------- ---------------- EMPLOYEE_ID not NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME no NULL VARCHAR2(25) EMAIL no NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE not NULL day JOB_ID no NULL VARCHAR2(10) value NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SQL> DESC departments name Null? type ----------------------- -------- ---------------- DEPARTMENT_ID no NULL NUMBER(4) DEPARTMENT_NAME not NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)SELECT department_id FROM employee eUNION select department_id from departments22.What will be presented in the result of this query?

It will screen distinct department id(s) included jointly in EMPLOYEES and DEPARTMENTS table It will throw ORA error No rows selected no one of the above

Answer: A. UNION returns the merged rows from two queries, sorting them and removing duplicates.

23.What is true around the query given above?

This query returns an ORA error it executes successfully yet gives no outcomes Queries from various tables cannot be provided with the collection operators The questions executes successfully and also gives the outcomes as expected

Answer: D. A compound query is one query consisted of of several queries using various tables.

24.What is the default sorting bespeak of the results once UNION every operator is used?

Descending Ascending either A or B every one of the over

Answer: B. A link query will by default return rows sorted throughout all the columns,from left to ideal in ascending order.The only exemption is UNION ALL, where the rows will not it is in sorted. The only ar where an bespeak BY i is permitted is in ~ the finish of the compound query.

25.What will be the calculation of the link query in i m sorry columns in the select are that CHAR and equal length?

The output will have actually VARCHAR2 data form of equal length The output will have actually CHAR data kind of equal size The calculation will have actually CHAR data form of different lengths The calculation will have actually NUMBER data kind of equal length

Answer: B. The columns in the queries that consist of a compound query can have various names, but the output an outcome set will use the name of the columns in the an initial query. The matching columns in the queries that make up a compound query should be the the very same data form group.

26.What will be the output of the compound query in i beg your pardon columns in the choose are that CHAR and also different lengths?

The output will have VARCHAR2 data kind of equal size The output will have CHAR data form of equal length The calculation will have CHAR data kind of various lengths The calculation will have VARCHAR2 data type with the length of the larger CHAR value

Answer: D. While the selected pillar lists carry out not have to be precisely the exact same data type, they need to be indigenous the exact same data kind group. The an outcome set that the compound query will have columns v the higher level of precision.

27.What will certainly be the calculation of a link query if one of two people or both queries pick values the VARCHAR2?

The calculation will have actually VARCHAR2 data type. The output will have CHAR data kind of equal size The calculation will have actually CHAR data type of various lengths The calculation will have actually VARCHAR2 data kind with the size of the bigger CHAR worth

Answer: A. While the selected column lists carry out not need to be exactly the same data type, they have to be indigenous the exact same data form group. The result set the the link query will have actually columns with the higher level that precision.

28.What is true if the link queries choose numeric data?

There will be an same precedence the the numeric values, operator The return worths will be determined by the numeric precedence The return worths will it is in of NUMBER data kind None of the above

Answer: B, C. While the selected obelisk lists do not need to be specifically the very same data type, they must be indigenous the same data kind group. The an outcome set of the compound query will have columns through the higher level of precision.

29.What will take place if the pick list of the link queries returns both a VARCHAR2 and a NUMBER data type result?

Oracle will transform them implicitly and also return a VARCHAR2 data kind result Oracle will transform them implicitly and return a NUMBER data kind result an ORA error is thrown no one of the over

Answer: C. Oracle walk not convert data species implicitly.

30.What is true about the UNION operator?

It eliminates the duplicate values ignoring NULL values It return duplicate worths ignoring NULL values It returns duplicate values consisting of NULL worths It eliminates duplicate values and does not overlook NULL worths

Answer: D. NULL values room not ignored once the UNION operator is used

31.What can be said about the names and also columns the a SQL questions which provides the UNION operator?

The name of the columns should be the same The names and data type of the columns must be identical The name of the columns need not be the same None of the over

Answer: C. The columns in the queries that consist of a compound query can have various names, yet the output result set will usage the name of the columns in the first query.

Consider the following exhibit that the JOB_HISTORY table and also the query that follows. Answer the concerns 32 and also 33 below the query.

SQL> desc job_history name Null? kind ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE not NULL day END_DATE no NULL day JOB_ID no NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT employee_id , first_name, last_name, job_idFROM employees EUNIONSELECT employee_id , first_name, last_name, job_idFrom job_history; 32.How plenty of times the every employee will get displayed by the above query?

0 1 2 4

Answer: B. UNION return the an unified rows from two queries, sorting them and also removing duplicates.

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

It screens the current and previous project details the the employees double It displays the current and also previous project details the the employees only as soon as Either A or B none of the above

Answer: B.

Examine the given table structures and consider the complying with query and answer the questions 34 to 37 the follow:

SQL> DESC employees name Null? kind ----------------------- -------- ---------------- EMPLOYEE_ID not NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME no NULL VARCHAR2(25) EMAIL no NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE not NULL date JOB_ID no NULL VARCHAR2(10) value NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SQL> desc job_history name Null? form ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE not NULL day END_DATE no NULL day JOB_ID not NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT employee_id , job_id, department_id native employeesUNIONSELECT employee_id , job_id, department_id native job_history; 34.Assuming the an employee v ID 121 has held 2 job IDs in his tenure in the company. Considering the above query, how many times will certainly his records be shown in the results?

Once twice Thrice nobody of the over

Answer: B. UNION returns the linked rows from 2 queries, sorting them and also removing duplicates. Replicate is measured by the combination of columns and also not the individual pillar separately.

35.Assuming the the employee through ID 121 hosted two location in two different departments - 10 and 20 in the company.He functioned as "SA_REP" in both the departments 10 and 20. What will be the outcome of the over query ?

2 rows 3 rows No rows ORA error

Answer: B.

36.Which statements ideal describes the inference attracted from the concerns 34 and also 35?

There room duplicate values for job codes The ask executes but results produced are unanticipated There are no duplicate values for departments None that the above

Answer: C. As the combination of the task codes and also departments is unique, there space no duplicates obtained.

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

Descending top top Employee i would Descending on task ID Ascending ~ above Employee i would Ascending on department ID

Answer: C. The default sorting will be ascending based upon the first column i.e.: Employee id in this case.However, this habits can it is in modified by place a solitary ORDER BY clause at the end.

38.Which the the adhering to operators will certainly be offered to acquire duplicate documents from the ingredient queries?

UNION UNION every MINUS none of the over

Answer: B. UNION every doesn"t eliminates the duplicate values.

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

There is no distinction UNION ALL displays duplicate values also The calculation in the situation of UNION all is no sorted by default none of the over

Answer: B, C. When used with two choose statements, the UNION collection operator return the results of both queries. However, if there are any type of duplicates, they are removed, and the replicated record is provided only once. To encompass duplicates in the results, usage the UNION ALL set operator

40.What is true about the intersect operator?

The number of columns and also data types of the columns in the ingredient queries must be the very same The surname of the columns and also data varieties of the columns in the component queries should be the exact same Both A and also B nobody of the above

Answer: A. This is typical property criteria attribute of collection operators.

41.What deserve to be said around the an outcome set if the bespeak of the intersected tables is transformed when using INTERSECT?

The an outcome is changed The an outcome remains the exact same The sorting changes on modification None that the over

Answer: B.

42.What amongst the complying with is true about the crossing operator?

It ignores NULL worths It walk not neglect NULL worths It returns all the rows native the very first component query none of the above

Answer: B.

Answer the related inquiries 43 and 44 offered below.

43.You require to display screen the names and job IDs the those employees who right now have a project title that is the same as their previous one. I m sorry of the adhering to queries will work? (Consider the table frameworks as given)

SQL> DESC employees name Null? form ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME no NULL VARCHAR2(25) EMAIL no NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE not NULL date JOB_ID no NULL VARCHAR2(10) value NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SQL> desc job_history surname Null? kind ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE no NULL day END_DATE no NULL date JOB_ID no NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT employee_id , job_id, first_name, last_nameFROM employeesUNIONSELECT employee_id , job_id, first_name, last_nameFROM job_history;SELECT employee_id , job_id, first_name, last_nameFROM employeesINTERSECTSELECT employee_id , job_id, first_name, last_nameFROM job_history;SELECT employee_id , job_id, first_name, last_nameFROM employeesUNION ALLSELECT employee_id , job_id, first_name, last_nameFROM job_history;None that the above Answer: B.

44.Considering the over query i.e. Option B in inquiry 43, what will be the result if the room ID is also included in the choose clause?

The an outcome will be the exact same The an outcome will be different The result will be the same however the order will certainly be different None the the over

Answer: A. The an outcome can be taken as - the employees that have worked with the same task title in the very same department.

45.What is true about the MINUS operator?

It returns all the rows from all the component queries that returns only the typical rows from every the ingredient queriesIt returns every the rows from the an initial query and not from the subsequent queries It return all distinct rows selected through the first query, however not current in the succeeding queries

Answer: D. MINUS set operator removes the 2nd query"s results from the output if castle are likewise found in the first query"s results

46.What deserve to be said regarding the number of columns and also data species of the ingredient queries when a MINUS operator is used?

They should be the same, the data type might be different but they should belong come the very same data type group. They should be the same in addition to the surname of the columns Both A and also B none of the over

Answer: A. Common attribute of collection operators.

47.You require to display the employee IDs that the employee who have actually not readjusted their jobs also once throughout tenure in the company. Which of the following queries will be exactly in this case? (Consider the table structures as given)

SQL> DESC employees name Null? type ----------------------- -------- ---------------- EMPLOYEE_ID no 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 no 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? form ----------------------- -------- ---------------- EMPLOYEE_ID not NULL NUMBER(6) START_DATE no NULL date END_DATE no NULL date JOB_ID not NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT employee_id indigenous employeesUNIONSELECT employee_id native job_history;SELECT employee_id native employeesINTERSECTSelect employee_id native job_history;SELECT employee_id from employeesMINUSSelect employee_id native job_history;SELECT employee_id from employeesUNION ALLSELECT employee_id from job_history;Answer: C.

Examine the offered table structures and also consider the complying with query prize the inquiries 48 and 49 that follow:

SQL> DESC employees surname Null? form ----------------------- -------- ---------------- 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 no NULL date JOB_ID not NULL VARCHAR2(10) value NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SQL> desc job_history name Null? form ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE not NULL date END_DATE no NULL day JOB_ID no NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT employee_id , first_name, job_idFROM employeesUNIONSELECT employee_id , NULL "first_name", job_idFROM job_history; 48.What is true about the above query?

It throw an error together TO_CHAR (NULL) can not be offered It executes successfully and gives the worths for employees" id, first_name and also current job function including duplicate rows the executes successfully and also gives the values for employees" id, first_name and all jobs held by the employee excluding duplicate rows nobody of the over

Answer: C. Each query should contain the same number of columns, i beg your pardon are compared positionally. NULL deserve to be substituted in location of tower which is absent in the various other query in ~ the very same compound query.

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

The result shows those employee who have actually an entrance in the JOB_HISTORY table The an outcome shows those employees who execute not have actually an entrance in the JOB_HISTORY, yet they are present in the employee table one of two people of A or B nobody of the above

Answer: B. MINUS offers the unique results that are existing in the first query yet not the 2nd query.

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

50.What will be the result of the adhering to query?

SELECT AU_DETAILS native AUDITUNIONSELECT AU_DETAILSFROM AUDIT_YEARLY; the executes properly giving the correct results including the duplicate values It executes efficiently giving the correct results excluding the duplicate worths It throws an ORA error none of the over 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?

It will execute efficiently giving the exactly results including duplicate worths It throws an ORA error It will certainly execute properly giving the correct outcomes excluding duplicate values It executes successfully however gives the incorrect results.

Answer: B. .UNION, UNION ALL, INTERSECT and MINUS operators when supplied with a lengthy or CLOB obelisk throws error.

52.Assume the there space 4 ingredient queries. Just how many collection operators can be used to incorporate them in a single compound query?

1 2 4 3

Answer: D. The set operator to be offered will room N-1 wherein N is the number of component queries.

53.What are set operators dubbed owning to the truth that 2 or an ext SELECTs are involved based top top columns instead of rows when set operators space used?

Horizontal join Cartesian joins Vertical join outer joins

Answer: C.

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

UNION combine the results of two component queries into one an outcome set v duplicate values INTERSECT returns just those rows that are reverted by each of the two component queries UNION gives the distinct values from the ingredient queries, INTERSECT offers the common values from the component queries Both B and C

Answer: C.

Examine the structure of the employee table and consider the complying with query. Answer the concerns 55 to 60 the follow.

SQL> DESC employees surname Null? type ----------------------- -------- ---------------- EMPLOYEE_ID no 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 day JOB_ID no NULL VARCHAR2(10) salary NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) Query 1SELECT *FROM EMPLOYEES whereby department_id = 10Query 2SELECT *FROM employee Ewhere E.job_id IN (select first_name from employees E1 wherein E1.job_id = "CLERK" and also E.job_id = E1.job_id )55.You have to extract a report whereby the results from both the queries room displayed. Which of the following operators need to be provided to acquire the required results?

UNION UNION every intersect None that the above

Answer: B. UNION all Returns the merged rows from 2 queries there is no sorting or remove duplicates.

56.You need to display screen all the duplicate values together with all the worths existing in the an outcome set indigenous both the queries. I m sorry of the following collection operators you deserve to use in the above given queries?

crossing UNION MINUS nobody of the above

Answer: D. UNION every will provide the unsorted outcomes with duplicates.

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

Result set from UNION all is filtered consisting of duplicate values an outcome set native UNION is filtered and sorted including duplicate values result set from UNION all is no sorted and also it has duplicate values result set indigenous UNION is filtered and sorted there is no duplicate worths

Answer: C, D.

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

The explain is exactly UNION all operator has an ext overhead ~ above the Data base 보다 the UNION operator UNION needs to sort and also eliminate duplicates which results into added overhead no one of the over

Answer: A, C. UNION has to perform more tasks 보다 UNION ALL due to the fact that it sorts and also deduplicates the an outcome sets. Hence it is recommended the unless distinct rows space required, UNION ALL should be used.

59.What will certainly be the outcome if the two queries given over are an unified using the intersect operator?

It will display screen only those employee who room Clerks in the room 10 the will screen all those employee who are in the room 10 the will display screen all the Clerks. None of the above

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

60.What among the adhering to is the difference in between the INTERSECT and the UNION operators?

INTERSECT follows the "AND" Boolean logic, UNION adheres to the "OR" Boolean logic UNION complies with the "OR" Boolean logic, conversely, INTERSECT follows the "AND" logic one of two people of A or B none of the over

Answer: A.

61.In i m sorry of the following set operators, transforming the bespeak of the component queries will adjust the an outcome set?

UNION UNION all MINUS INTERSECT

Answer: C. MINUS Returns just the rows in the first result set that execute not show up in the second an outcome set, sorting them and removing duplicates.

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

SELECT 4 native dualINTERSECTSELECT 1 from dual; 62.What will certainly be the outcome of the provided query?

No rows 4 1 NULL

Answer: A. No rows will be selected as the crossing operator will not get any kind of common outcomes from both the queries - crossing operators gives usual results present in questions 1 and query 2.

63.What will be the outcome of the ask if the intersect operator is changed with MINUS operator?

3 4 0 1

Answer: B. MINUS gives results the are existing in the first query and also not existing in the 2nd query.

64.What will be the result of the over query if the crossing operator is changed with the UNION operator?

1

4

4

1

NULL0

Answer: A. UNION will create distinct rows in the an outcome set in ascending order.

65.What will be the outcome of the over query if the crossing operator is changed with the UNION every operator?

4

1

0NULL1

4

Answer: A. UNION ALL display screens the results as they space positioned in the query without sorting them.

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

SELECT 1 native dualUNION ALLSELECT 4 indigenous dual;14

4

1

NULLNone of the above

Answer: A.

Examine the JOB_HISTORY_ARCHIVE table structure. That is a backup table for the JOB_HISTORY table with no extr column. Assuming that both the table have actually dissimilar data, think about the query given below and answer the concerns 67 come 70 the follow:

*

SQL> desc job_history surname Null? form ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE not NULL day END_DATE no NULL date JOB_ID no NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4) (SELECT * native job_history;MINUSSELECT * indigenous job_history_archive)UNION ALL(SELECT * from job_history_archiveMINUSSELECT * from job_history;);67.What will be the result of the query provided above? (Choose the ideal answer)

It will certainly return those rows the are different in the 2 tables It will certainly return the common rows in the 2 tables It will certainly return all the rows from the two tables nobody of the over

Answer: A.

68.What have the right to concluded if the above given query returns rows only from JOB_HISTORY table?

It shows that the JOB_HISTORY contains two rows different from JOB_HISTORY_ARCHIVE table It mirrors that two rows are exact same in JOB_HISTORY and JOB_HISTORY_ARCHIVE tables It shows that the JOB_HISTORY_ARCHIVE has two rows various from JOB_HISTORY table none of the over

Answer: A.

69.What can be stated if the over query offers no results?

It mirrors that the 2 tables have same data It reflects that the ingredient queries space wrongly placed It mirrors that the collection operators are erroneously used in the compound query none of the over

Answer: A.

70.With respect to the query provided above, if duplicate records exist in the 2 tables, i m sorry of the following modifications should be made come the over given query?

COUNT(*) COUNT(*) and also GROUP by employee_id count (*) and also ORDER by employee_id none of the over

Answer: B. COUNT(*) deserve to be supplied to watch the difference in between the tables.

Consider the following query:

SELECT 1 NUM, "employee" text FROM dualUNIONSELECT TO_CHAR(NULL) NUM, "departments" message FROM dual;71.What will certainly be the result of the query provided above?

NUM TEXT---------- ----------- 1 employee department NUM TEXT---------- ----------- 1 employee NULL departmentsORA error NUM TEXT---------- ----------- departments 1 employeeAnswer: C. Here the numeric 1 is compared to a character NULL which throws the error "ORA-01790: expression must have same datatype as equivalent expression".

Consider the following query and also answer the inquiries 72 and also 73 that follow:

SELECT months_between (sysdate, to_date("21-MAY-2013","DD-MON-YYYY")) indigenous dualUNIONSELECT TO_date(NULL) NUM indigenous dual;72.What will certainly be the result of the query given above? (Assume the the SYSDATE is 1st July, 2013)

It executes properly with correct outcomes It executes successfully however with no results It throw an ORA error no one of the above

Answer: C. NUMBER and DATE do not belong to exact same data type fail. Below a number derived by MONTHS_BETWEEN is contrasted 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 indigenous dual;What will be the outcome as such change?

It executes successfully with exactly resultsIt executes successfully but with no resultsIt throw an ORA errorNone of the above

Answer: A.

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

SQL> DESC employees surname Null? form ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME not NULL VARCHAR2(25) EMAIL not NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE no NULL day JOB_ID not NULL VARCHAR2(10) value 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 no NULL day END_DATE no NULL day JOB_ID no NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT employee_id "Employee ID"FROM employeesUNIONSELECT employee_id "EMP ID"FROM job_history;Which of the below column headings will display in the result set?

EMP i would Employee i would EMPLOYEE_ID ORA error since the shaft names should be very same in the ingredient queries.

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

Examine the two table frameworks given and consider the adhering to query and answer the concerns 75 and 76 the follow:

SQL> DESC employees name Null? type ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME no NULL VARCHAR2(25) EMAIL no NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE no NULL day JOB_ID not NULL VARCHAR2(10) value NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SQL> desc job_history surname Null? type ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE no NULL day END_DATE not NULL day JOB_ID not NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT employee_id FROM employees eUNIONSELECT employee_id indigenous job_history jORDER through j.employee_id ;75.What will certainly be the result of the query provided above?

The results will it is in ordered by the employee ID indigenous the JOB_HISTORY table The outcomes will it is in ordered through the employee ID indigenous the employee table There will be no notified of the results ORA error

Answer: D. The stimulate BY must be done based on the names of the columns native the first query and not indigenous the 2nd query columns.

76.Which that the adhering to ORDER by clauses can replace the erroneous order BY in the query offered above?

ORDER by e.employee_id bespeak BY j.2 bespeak BY 1None of the above, bespeak BY is not allowed in the query

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

77.Consider the adhering to exhibit and also answer the question below:

SELECT au_docFrom auditUNIONSELECT au_docFrom audit_yearly;What will be the outcome of the over given query?

It gives the Audit documents between the two tables It provides an ORA error on execution It gives the Audit documents from the table AUDIT none of the above

Answer: B. LONG columns cannot be provided with collection operators.

78.Consider the query provided below:

SELECT col_1From TABLE (package1.proc1)UNIONSELECT col_1From TABLE (package2.proc2);What will be the outcome of the query given above?

It executes successfully with duplicates the executes properly without duplicates it throws one ORA error nobody of the over

Answer: C. TABLE expressions can not be provided with collection operators.

Examine the 2 table structures given and also consider the following query. Prize the concerns 79 and 80 that follow:

SQL> DESC employees surname 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 no NULL date JOB_ID no NULL VARCHAR2(10) salary NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SQL> desc job_history surname Null? form ----------------------- -------- ---------------- EMPLOYEE_ID not NULL NUMBER(6) START_DATE no NULL day END_DATE no NULL day JOB_ID not NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT employee_id , job_idFROM employee EUNIONSELECT employee_id , job_idFROM job_history JFOR update OF job_id;79.What happens as soon as the query is executed?

ORA error Employee_id and also job_id Employee_id no one of the over

Answer: A. The FOR update clause can not be provided with the query linked using the collection operators.

80.What will be the result of the adhering to query?

SELECT * from employeesUNIONSELECT job_id from job_history;;It will provide all the columns indigenous the employees tables and only the job_id shaft from the job_history table It will throw an error together the number of columns should enhance in the component queries neither B or C none of the above Answer: B.

81.If UNION, UNION ALL, intersect are supplied in one SQL statement i m sorry of the following is true regarding the SQL statement?

UNION, UNION ALL will be executed an initial and climate the result set will go because that the crossing statement. The execution that INTERSECT will precede the UNION and UNION every execution. The execution will certainly be done from best to left taking into factor to consider all the operator at the same time. The execution will certainly be done from left to right taking into factor to consider all the operators at the same time.

Answer: D.

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

SELECT "3" from dualINTERSECTSELECT 3f from dual;What is true concerning the execution that the query provided above?

It executes successfully. It throws one error It provides the result 3. It gives the an outcome 3f

Answer: B. Character literals must be attached within solitary quotes.

83.Which of the adhering to is false for collection operators provided in SQL queries?

The collection operators are valid when supplied on columns v the lengthy datatype. The collection operators are not precious on columns of kind BLOB, CLOB, BFILE, VARRAY, or nested table. In order because that the select query include an expression, a shaft alias must be noted in order come refer it come the order_by_clause. Friend cannot usage these operators in select statements comprise TABLE arsenal expressions.

Answer: A. SET operators room unsupported because that LONG, CLOB and also BLOB data types.

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

SQL> DESC employees name Null? kind ----------------------- -------- ---------------- EMPLOYEE_ID no 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 no NULL VARCHAR2(10) value NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SELECT employee_id , last_name "Last Name"FROM employeesWHERE department_id = 100UNIONSELECT employee_id EMPLOYEE_NO, last_nameFROM employeesWHERE department_id = 101;Which order BY clauses space valid for the above query? (Choose all the apply.)

ORDER by 2,1 bespeak BY EMPLOYEE_NO order BY 2, employee_id stimulate BY "EMPLOYEE_NO"

Answer: A, C. The stimulate BY clause have to reference obelisk by its place or the name referred by the an initial query.

85.Which the the following clauses would you use to exclude, the pillar from the second query out of the 2 queries linked using set operators?

GROUP BY stimulate BY MINUS UNION

Answer: C.

86.Examine the provided table structure as given. What will be the result 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 no NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE no NULL day JOB_ID no NULL VARCHAR2(10) value NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SELECT distinct department_id native employeesWHERE salary > any type of (SELECT AVG (salary)FROM employeesGROUP through department_id )UNIONSELECT *FROM employeesWHERE value > any (SELECT MAX (salary)FROM employeesGROUP by department_id );It will screen all the room IDs which have the median salaries and also the maximum wages It will throw one ORA error as the no. That columns selected in both the ask is differentIt will display all the room IDs which have actually the typical salaries it will screen all the room IDs which have the maximum salariesAnswer: B. The no. Of columns have to be the same.

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

UNION operates over only the first column in the choose list UNION operates over the an initial columns that the pick lists in the ingredient queries UNION operates over every the columns being selected. None of the above

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

88.You require to display screen the departments where the employees with the project IDs "SA_REP" or "ACCOUNTANT" work. I beg your pardon of the complying with queries will fetch friend the compelled results? (Consider the given table structure)

SQL> DESC employees name Null? form ----------------------- -------- ---------------- EMPLOYEE_ID not NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME no NULL VARCHAR2(25) EMAIL not NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE not NULL day JOB_ID not NULL VARCHAR2(10) value NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SELECT department_id FROM employees EWhere job_id = "SA_RE"UNIONSELECT department_id FROM employees EWhere job_id = "ACCOUNTANT";SELECT department_id FROM employee EWhere job_id = "SA_REP"UNION ALL pick department_id FROM employees EWhere job_id = "ACCOUNTANT";SELECT department_id FROM employees EWhere job_id = "SA_REP"INTERSECTSelect department_id FROM employees EWhere job_id = "ACCOUNTANT";SELECT department_id FROM employee EWhere job_id = "SA_REP"MINUSSelect department_id FROM employee EWhere job_id = "ACCOUNTANT";Answer: A.

89.Which the the complying with statement is true about the bespeak of rows in a query i m sorry uses set operator?

It is not feasible to use ORDER by in the separation, personal, instance queries the make a link query. An order BY clause can be appended come the finish of a link query. The rows reverted by a UNION ALL will be in the order they take place in the two resource queries. The rows went back by a UNION will certainly be sorted throughout all your columns, ideal to left.

Answer: A, B, C.

90.The UNION operator was offered to satisfy which the the following function before the ANSI SQL syntax in place?

RIGHT OUTER sign up with LEFT OUTER join EQUI-JOIN complete OUTER join

Answer: D.

Answer the related questions 91 and also 92 given below. Consider the table frameworks as offered 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 day JOB_ID no 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? kind ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE no NULL day END_DATE no NULL date JOB_ID not NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)91.You require to find the task IDs which do not have any JOB background logged for them. I beg your pardon of the following queries will work? (Consider the given table structures)

SELECT job_idFROM employee UNION ALLSELECT job_idFROM job_history;;SELECT job_idFROM employee MINUSSelect job_idFROM job_history;;SELECT job_idFROM employee UNIONSELECT job_idFROM job_history;;None the the over Answer: B.

92.Consider the adhering to query:

SELECT distinct job_idFROM employees organic JOIN job_history ; which of the following queries are the same to the above query?

SELECT job_idFROM employeesUNION choose job_idFROM job_history;;SELECT job_idFROM employeesUNION ALLSELECT job_idFROM job_history;;SELECT job_idFROM employeesMINUSSelect job_idFROM job_history;;SELECT job_idFROM employeesINTERSECT select job_idFROM job_history;;Answer: A.

Examine the table structures given here. Take into consideration the query provided below and answer the related inquiries 93 come 97 the follow:

SQL> DESC employees name Null? type ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME not NULL VARCHAR2(25) EMAIL no NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE no NULL date JOB_ID not NULL VARCHAR2(10) value NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SQL> desc job_history surname Null? type ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE not NULL day END_DATE no NULL day JOB_ID not NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT job_idFROM employeesUNION ALLSELECT job_idFROM job_history;; 93.If the employee table has 5 records and also the JOB_HISTORY consists of 3 records, how plenty of records will certainly be obtained from the below query?

4308

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

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

5326

Answer: D. UNION returns the linked rows from 2 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 room 3 distinctive values in EMPLOYEES and 2 in JOB_HISTORY)

3210

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

96.If the UNION all operator is replaced with crossing operator, how numerous records will certainly be obtained? (Assume there are 3 values common between the two tables)

8632

Answer: C. INTERSECT Returns just the rows that occur in both queries" an outcome sets, sorting them and also removing duplicates.

97.Consider the following query:

1.select job_id2. Native employees3.ORDER through department_id 4.UNION ALL5.select job_id6.FROM job_history;7.ORDER by department_id ; The over query generates an error. Which heat in the over query generates one error?

372No error is obtained

Answer: A. ORDER BY must only show up at the end of the link query and not in the ingredient queries.

98.Which that the following set operator attributes are supported in SQL/Foundation:2003 however not through Oracle?

UNION ALLMINUS ALLINTERSECT ALLEXCEPT ALL

Answer: B, C, D.

99.You require to uncover out the typical JOB IDs (excluding duplicates) in the department 100 and also 200. Which query will certainly you fire to obtain the forced results? (Consider the table structure as given)

SQL> DESC employees name Null? form ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME not NULL VARCHAR2(25) EMAIL no NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE no NULL date JOB_ID no NULL VARCHAR2(10) salary NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)SELECT job_id indigenous employeeWHERE department_id = 100INTERSECT pick job_id from employeeWHERE department_id = 200;SELECT job_id native employeeWHERE department_id = 100UNION ALLSELECT job_id from employeeWHERE department_id = 200;SELECT job_id native employeeWHERE department_id = 100MINUSSelect job_id native employeeWHERE department_id = 200;SELECT job_id indigenous employeeWHERE department_id = 100INTERSECT ALLSelect job_id from employeeWHERE department_id = 200;Answer: A.

100.If a link query consists of both a MINUS and also an intersect operator, which will be applied first? (Choose the finest answer.)

The INTERSECT, because INTERSECT has greater precedence 보다 MINUS.The MINUS, because MINUS has actually a greater precedence than INTERSECT.The precedence is figured out by the stimulate in i beg your pardon they space specified.It is not possible for a compound query to include both MINUS and INTERSECT.

See more: Buy Ronda Rousey Red Carpet At Affordable Price, Ronda Rousey Red Carpet Stock Photos And Images

Answer: C. All collection operators have equal precedence, for this reason the precedence is established by the sequence in which lock occur.