*
UNIONUNION ALLINTERSECTMINUS

Answer: A. Set operator are offered to incorporate the results of 2 (or more) choose statements.Valid set operators in Oracle 11g room UNION, UNION ALL, INTERSECT, and MINUS. When offered with two select statements, the UNION set operator returns the results of both queries.However,if over there are any duplicates, they are removed, and also the copied record is listed only once.To encompass duplicates in the results,use the UNION ALL collection operator.INTERSECT lists just records the are reverted by both queries; the MINUS collection operator gets rid of the second query"s results from the output if castle are additionally found in the an initial query"s results. INTERSECT and also MINUS collection operations create unduplicated results.

You are watching: _____ is a relational set operator.

2.Which collection operator go the following figure indicate?

*
UNIONUNION ALLINTERSECTMINUS

Answer: B. UNION every Returns the linked rows from two queries there is no sorting or removing duplicates.

sql_certificate

3.Which collection operator walk the following figure indicate?

*
UNIONUNION ALLINTERSECTMINUS

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

4.Which set operator walk the following number indicate?

*
UNIONUNION ALLINTERSECTMINUS

Answer: D. MINUS Returns just the rows in the an initial result collection that perform not show up in the second an outcome set, sorting them and also removing duplicates.

5.What is true about set operators?

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

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

6.What are the queries containing collection operators called?

Sub-queriesCo-related sub-queriesGROUP by queriesCompound queries

Answer: D.

7.What is true around the UNION operator?

It return rows native the combined queries in addition to NULL valuesIt returns rows for the an unified queries after ~ eliminating duplicatesIt return rows for the an unified queries in addition to duplicate valuesIt return rows because that the linked queries skip the NULL values

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

8.What is true around the UNION all operator?

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

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

9.What is true around the intersect operator?

It return rows native the an unified queries together with NULL valuesIt return rows because that the an unified queries after eliminating duplicatesIt returns the common rows from the combined queriesNone the the above

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

10.What is true about the MINUS operator?

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

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

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

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

Answer: D. SET operators have an same precedence.

12.What is the bespeak of evaluation 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 collection operators will be evaluate from peak to bottom and also left to ideal horizontally.

13.In i m sorry of the complying with cases, parenthesis must be specified?

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

Answer: A. Using parenthesis will certainly explicitly adjust the bespeak of evaluation once INTERSECT is supplied with other operators.

14.What is true about the select clause when collection operators are used?

There is no limit on the columns being selectedThe columns, expressions supplied in the pick clause must match in number in the an unified queriesThe columns, expressions offered in the select clause should be N in the very first query and N-1 in the subsequent combined queriesBoth B and also C

Answer: B. All the linked should have the exact same no. Of columns once using collection operators. The equivalent columns in the queries that comprise a link query should be that the exact same data kind group.

15.What is true around the set operators?

The select clause should have the same variety of columns, data species can be differentThe collection operators have the right to be used only because that combining two queriesThe data form of each column in the 2nd query must match the data type of its corresponding column in the an initial query.None of the above

Answer: C. All the combined should have the same no. That columns when using set operators. The matching columns in the queries that consist of a compound query have to be of the same data form group.

16.Where can the stimulate BY clause be supplied in case when collection operators space used?

In every of the queries being an unified In the very first query only At the an extremely end the the link query nobody of the above

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

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

These queries must have actually the same no. And also data kind of columns in their pick clause. The no. The columns offered in the where clause query and also the main pick can be different The no. The columns offered in the wherein clause need to be the same, the data kind can be different None the the over

Answer: A. All the an unified should have actually the very same no. Of columns as soon as using set operators. The equivalent columns in the queries that make up a compound query must be of the exact same data form group.

18.What is true about the columns in the second query v respect come the columns in the an initial query?

The shaft in the second query need to be in the very same data form group together the matching column in the first query If a obelisk in the 1st query is a NUMBER, the matching column in the second query have to be a VARCHAR2 If a pillar in the 1st query is a NUMBER, the corresponding column in the second query need to be also be NUMBER. Nobody of the above

Answer: A, C.

19.What amongst the adhering to is true about set operators?

SET operators cannot be provided in sub-queries collection operators deserve to only be offered in the wherein clause stimulate BY deserve to be supplied for all queries an unified by a set operator collection operators can be used in sub-queries

Answer: D.

20.What is the best means to adjust the precedence of collection operators offered the reality that they have actually equal precedence?

The stimulate of consumption of the collection operators can be readjusted to readjust the precedence The same precedence can not be readjusted Parenthesis can be offered to change the precedence nobody of the above

Answer: C. Parenthesis can be offered to group the certain queries in stimulate to change the precedence explicitly. Parentheses are wanted over other set operators during execution.

21.What can be said about duplicate worths and collection operators?

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

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

Examine the framework of the EMPLOYEES and also DEPARTMENTS tables and also consider the adhering to query and answer the concerns 22 and 23.

SQL> DESC employees surname Null? form ----------------------- -------- ---------------- 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 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 departments surname Null? kind ----------------------- -------- ---------------- 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 eUNION pick department_id native departments22.What will be displayed in the an outcome of this query?

It will display distinct room id(s) included jointly in EMPLOYEES and also DEPARTMENTS table It will throw ORA error No rows selected none of the over

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

23.What is true around the query given above?

This query return an ORA error it executes successfully however gives no results Queries from different tables cannot be provided with the set operators The questions executes successfully and also gives the results as expected

Answer: D. A link query is one query comprised of numerous queries using various tables.

24.What is the default sorting stimulate of the results as soon as UNION all operator is used?

Descending Ascending either A or B every one of the above

Answer: B. A link query will certainly by default return rows sorted throughout all the columns,from left to appropriate in ascending order.The only exemption is UNION ALL, where the rows will not it is in sorted. The only place where an stimulate BY clause is permitted is at the end of the link query.

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

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

Answer: B. The columns in the queries that consist of a compound query can have various names, yet the output an outcome set will use the names of the columns in the first query. The matching columns in the queries that consist of a link query need to be the the same data type group.

26.What will certainly be the calculation of the compound query in i beg your pardon columns in the pick are that CHAR and different lengths?

The output will have actually VARCHAR2 data form of equal length The output will have actually CHAR data kind of equal length The calculation will have CHAR data kind of different lengths The output will have VARCHAR2 data kind with the length of the larger CHAR worth

Answer: D. While the selected tower lists carry out not have to be specifically the very same data type, they need to be native the very same data form group. The result set of the link query will have columns v the higher level of precision.

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

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

Answer: A. While the selected tower lists do not need to be exactly the very same data type, they need to be from the exact same data kind group. The result set that the compound query will have columns through the higher level the precision.

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

There will certainly be an same precedence the the numeric values, operators The return worths will be figured out by the numeric precedence The return worths will be of NUMBER data kind None that the over

Answer: B, C. While the selected obelisk lists carry out not have to be exactly the same data type, they must be native the same data type group. The result set that the compound query will have columns with the greater level of precision.

29.What will happen if the select list the the compound queries return both a VARCHAR2 and a NUMBER data form result?

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

Answer: C. Oracle does not convert data types implicitly.

30.What is true around the UNION operator?

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

Answer: D. NULL values space not ignored when the UNION operator is used

31.What have the right to be said around the names and also columns the a SQL questions which uses the UNION operator?

The surname of the columns have to be identical The names and also data form of the columns have to be the same The name of the columns need not be similar None that the above

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

Consider the complying with exhibit of the JOB_HISTORY table and the query the follows. Price the inquiries 32 and also 33 listed below the query.

SQL> desc job_history surname Null? form ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE not NULL date END_DATE no NULL day JOB_ID not NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT employee_id , first_name, last_name, job_idFROM employee EUNIONSELECT employee_id , first_name, last_name, job_idFrom job_history; 32.How numerous times the each employee will certainly get presented by the above query?

0 1 2 4

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

33.What will certainly be the result of the over query?

It displays the current and also previous job details of the employees double It screens the current and also previous task details the the employee only as soon as Either A or B no one of the above

Answer: B.

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

SQL> DESC employees surname 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) 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 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 from 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 over query, how numerous times will his records be shown in the results?

Once twice Thrice nobody of the over

Answer: B. UNION return the an unified rows from 2 queries, sorting them and also removing duplicates. Replicate is measured by the mix of columns and not the individual obelisk separately.

35.Assuming that the employee through ID 121 hosted two positions in two different departments - 10 and 20 in the company.He operated as "SA_REP" in both the department 10 and 20. What will certainly be the result of the over query ?

2 rows 3 rows No rows ORA error

Answer: B.

36.Which statements finest describes the inference drawn from the inquiries 34 and 35?

There space duplicate values for task codes The questions executes but results produced are unexpected There are no duplicate values for departments None that the over

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

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

Descending on Employee identifier Descending on job ID Ascending ~ above Employee id Ascending on room ID

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

38.Which that the adhering to operators will be used to attain duplicate records from the component queries?

UNION UNION all MINUS nobody of the over

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

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

There is no distinction UNION ALL display screens duplicate values too The calculation in the case of UNION every is not sorted through default no one of the over

Answer: B, C. When supplied with two choose statements, the UNION set operator returns the results of both queries. However, if there are any type of duplicates, they room removed, and also the duplicated record is provided only once. To include duplicates in the results, use the UNION ALL collection operator

40.What is true around the intersect operator?

The number of columns and data types of the columns in the component queries should be the exact same The surname of the columns and also data varieties of the columns in the component queries have to be the same Both A and also B no one of the over

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

41.What can be said around the result set if the stimulate of the intersected tables is transformed when making use of INTERSECT?

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

Answer: B.

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

It ignores NULL values It walk not ignore NULL worths It returns all the rows indigenous the first component query nobody of the over

Answer: B.

Answer the related inquiries 43 and also 44 given below.

43.You require to screen the names and also job IDs of those employee who right now have a job title that is the very same as your previous one. Which of the adhering to queries will certainly work? (Consider the table frameworks as given)

SQL> DESC employees surname 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 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_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 of the above Answer: B.

44.Considering the over query i.e. Alternative B in question 43, what will be the result if the department ID is additionally included in the pick clause?

The result will it is in the exact same The an outcome will be various The an outcome will be the same however the order will certainly be different None the the above

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

45.What is true about the MINUS operator?

It returns all the rows from all the ingredient queries it returns just the typical rows from every the component queriesIt returns all the rows from the very first query and not native the subsequent queries It return all distinct rows selected by the first query, but not present in the subsequent queries

Answer: D. MINUS collection operator clears the 2nd query"s results from the calculation if lock are additionally found in the very first query"s results

46.What have the right to be said regarding the variety of columns and also data types of the component queries when a MINUS operator is used?

They must be the same, the data form might it is in different however they must belong to the exact same data type group. They need to be the same along with the name of the columns Both A and also B none of the above

Answer: A. Common attribute of set operators.

47.You require to display the employee IDs that the employees who have actually not changed their jobs also once during tenure in the company. I m sorry of the following queries will be correct in this case? (Consider the table structures as given)

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

Examine the offered table structures and consider the following query prize the concerns 48 and also 49 that follow:

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 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? kind ----------------------- -------- ---------------- EMPLOYEE_ID not NULL NUMBER(6) START_DATE not NULL date END_DATE not NULL date 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 around the over query?

It throws an error as TO_CHAR (NULL) cannot be used It executes successfully and also gives the worths for employees" id, first_name and current job role including duplicate rows the executes successfully and gives the worths for employees" id, first_name and also all jobs held by the employees excluding duplicate rows none of the over

Answer: C. Each query should contain the same variety of columns, i m sorry are contrasted positionally. NULL have the right to be substituted in place of obelisk which is absent in the various other query in ~ the exact same compound query.

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

The an outcome shows those employee who have actually an entry in the JOB_HISTORY table The result shows those employees who carry out not have an entry in the JOB_HISTORY, but they are current in the employees table either of A or B none of the over

Answer: B. MINUS offers the distinct results the are existing in the first query however not the 2nd query.

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

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

SELECT AU_DETAILS from AUDITUNIONSELECT AU_DETAILSFROM AUDIT_YEARLY; the executes properly giving the exactly results consisting of the duplicate worths It executes successfully giving the correct results excluding the duplicate values It throws an ORA error nobody of the above Answer: C. CLOB or long columns can not be in the pick clause once using the UNION collection operators.

51.What will be the outcome of the questions if UNION is changed with UNION ALL?

It will certainly execute efficiently giving the exactly results consisting of duplicate values It litter an ORA error It will certainly execute successfully giving the correct outcomes excluding duplicate values It executes successfully however gives the untrue results.

Answer: B. .UNION, UNION ALL, INTERSECT and also MINUS operator when provided with a lengthy or CLOB obelisk throws error.

52.Assume that there are 4 ingredient queries. Just how many set operators deserve to be offered to combine them in a single compound query?

1 2 4 3

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

53.What are collection operators dubbed owning to the truth that two or more SELECTs are associated based ~ above columns instead of rows when collection operators space used?

Horizontal joins Cartesian join Vertical join outer joins

Answer: C.

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

UNION combine the outcomes of two component queries into one result set with duplicate values INTERSECT returns just those rows the are changed by each of the 2 component queries UNION provides the distinctive values native the ingredient queries, INTERSECT offers the typical values indigenous the ingredient queries Both B and also C

Answer: C.

Examine the structure of the employees table and consider the adhering to query. Price the questions 55 to 60 that follow.

SQL> DESC employees surname 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) 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 employee E1 whereby E1.job_id = "CLERK" and E.job_id = E1.job_id )55.You need to extract a report whereby the outcomes from both the queries are displayed. I beg your pardon of the adhering to operators have to be provided to get the compelled results?

UNION UNION all intersect None the the above

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

56.You require to display screen all the duplicate values along with all the worths existing in the result set native both the queries. I m sorry of the following set operators you can use in the above given queries?

crossing UNION MINUS none of the above

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

57.What is the difference in between the an outcome sets as soon as using a UNION and a UNION ALL set operators?

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

Answer: C, D.

58.The UNION operator has an ext overhead top top the database 보다 the UNION ALL. What is dorn in this statement?

The declare is correct UNION all operator has more overhead top top the Data base than the UNION operator UNION has to sort and eliminate duplicates which results into additional overhead nobody of the over

Answer: A, C. UNION needs to perform an ext tasks than UNION ALL since it sorts and also deduplicates the an outcome sets. Therefore it is recommended the unless unique rows space required, UNION ALL need to be used.

59.What will certainly be the result if the 2 queries given over are linked using the crossing operator?

It will screen only those employee who are Clerks in the department 10 that will screen all those employees who room in the department 10 it will display screen all the Clerks. No one of the above

Answer: A. INTERSECT return those documents that are existing in questions 1 and also query 2.

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

INTERSECT follows the "AND" Boolean logic, UNION complies with the "OR" Boolean logic UNION follows the "OR" Boolean logic, conversely, INTERSECT complies with the "AND" logic one of two people of A or B nobody of the above

Answer: A.

61.In which of the following collection operators, changing the stimulate of the component queries will readjust the result set?

UNION UNION every MINUS INTERSECT

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

Consider the adhering to query and also answer the inquiries 62 come 66 the follow:

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

No rows 4 1 NULL

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

63.What will certainly be the result of the questions if the intersect operator is changed with MINUS operator?

3 4 0 1

Answer: B. MINUS gives results the are present in the first query and 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 produce distinct rows in the an outcome set in ascending order.

65.What will certainly be the result of the over query if the crossing operator is replaced with the UNION every operator?

4

1

0NULL1

4

Answer: A. UNION ALL screens the results as they space positioned in the query there is no sorting them.

66.What will be the result if the over query is modified together below?

SELECT 1 from dualUNION ALLSELECT 4 native dual;14

4

1

NULLNone that the above

Answer: A.

Examine the JOB_HISTORY_ARCHIVE table structure. The is a back-up table for the JOB_HISTORY table with no added column. Assuming the both the table have actually dissimilar data, think about the query offered 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 no NULL date END_DATE no NULL date JOB_ID no NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4) (SELECT * from job_history;MINUSSELECT * native job_history_archive)UNION ALL(SELECT * from job_history_archiveMINUSSELECT * native job_history;);67.What will be the result of the query given above? (Choose the finest answer)

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

Answer: A.

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

It shows that the JOB_HISTORY contains two rows various from JOB_HISTORY_ARCHIVE table It mirrors that two rows are same in JOB_HISTORY and also JOB_HISTORY_ARCHIVE tables It mirrors that the JOB_HISTORY_ARCHIVE has two rows various from JOB_HISTORY table no one of the over

Answer: A.

69.What deserve to be claimed if the over query provides no results?

It reflects that the two tables have same data It mirrors that the component queries are wrongly inserted It reflects that the collection operators are erroneously used in the compound query nobody of the above

Answer: A.

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

COUNT(*) COUNT(*) and GROUP through employee_id counting (*) and also ORDER by employee_id none of the above

Answer: B. COUNT(*) have the right to be offered to check out the difference between the tables.

Consider the complying with query:

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

NUM TEXT---------- ----------- 1 employee departments NUM TEXT---------- ----------- 1 employee NULL departmentsORA error NUM TEXT---------- ----------- departments 1 employeeAnswer: C. Here the numeric 1 is contrasted to a character NULL i m sorry throws the error "ORA-01790: expression must have same datatype as matching expression".

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

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

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

Answer: C. NUMBER and also DATE perform not belong to very same data kind fail. Right here a number derived by MONTHS_BETWEEN is compared with a DATE and also hence the error.

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

SELECT to_number (NULL) NUM native dual;What will be the outcome because of this change?

It executes properly with exactly resultsIt executes successfully however 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? kind ----------------------- -------- ---------------- 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? 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 employee_id "Employee ID"FROM employeesUNIONSELECT employee_id "EMP ID"FROM job_history;Which the the below column headings will display in the an outcome set?

EMP id Employee i would EMPLOYEE_ID ORA error due to the fact that the pillar names should be very same in the component queries.

Answer: B. The columns in the queries that consist of a link query deserve to have different names, however the output result set will use the names of the columns in the an initial query.

Examine the 2 table frameworks given and consider the complying with query and answer the inquiries 75 and also 76 the 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 no 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? kind ----------------------- -------- ---------------- 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 employee eUNIONSELECT employee_id indigenous job_history jORDER by j.employee_id ;75.What will certainly be the outcome of the query offered above?

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

Answer: D. The bespeak BY have to be done based on the names of the columns indigenous the first query and also not from the second query columns.

76.Which the the following ORDER through clauses have the right to replace the erroneous stimulate BY in the query given above?

ORDER through e.employee_id order BY j.2 bespeak BY 1None the the above, bespeak BY is not permitted in the query

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

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

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

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

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

78.Consider the query provided below:

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

It executes properly with duplicates it executes efficiently without duplicates the throws one ORA error none of the over

Answer: C. TABLE expressions can not be supplied with set operators.

Examine the two table frameworks given and consider the following query. Answer the inquiries 79 and also 80 the follow:

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 day 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? kind ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE not NULL date END_DATE not NULL day JOB_ID no NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT employee_id , job_idFROM employees EUNIONSELECT employee_id , job_idFROM job_history JFOR upgrade OF job_id;79.What happens as soon as the questions is executed?

ORA error Employee_id and job_id Employee_id nobody of the above

Answer: A. The FOR update clause cannot be provided with the query linked using the set operators.

80.What will certainly be the outcome of the complying with query?

SELECT * native employeesUNIONSELECT job_id from job_history;;It will offer all the columns native the employees tables and also only the job_id obelisk from the job_history table It will throw an error as the variety of columns should match in the ingredient queries neither B or C no one of the above Answer: B.

81.If UNION, UNION ALL, intersect are offered in one SQL statement i beg your pardon of the complying with is true regarding the SQL statement?

UNION, UNION ALL will certainly be executed an initial and climate the an outcome set will go for the intersect statement. The execution that INTERSECT will certainly precede the UNION and UNION all execution. The execution will certainly be excellent from appropriate to left acquisition into factor to consider all the operators at the exact same time. The execution will be excellent from left to ideal taking into factor to consider all the operator at the exact same time.

Answer: D.

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

SELECT "3" indigenous dualINTERSECTSELECT 3f native dual;What is true concerning the execution of the query provided above?

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

Answer: B. Character literals should be fastened within solitary quotes.

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

The set operators space valid when provided on columns v the long datatype. The set operators are not valid on columns of kind BLOB, CLOB, BFILE, VARRAY, or nested table. In order because that the select query comprise an expression, a tower alias have to be noted in order come refer it to the order_by_clause. Friend cannot use these operators in pick statements include TABLE repertoire expressions.

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

84.Examine the offered table structure and evaluate the complying with SQL statement:

SQL> DESC employees surname Null? form ----------------------- -------- ---------------- 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 no 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 room valid for the above query? (Choose all the apply.)

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

Answer: A, C. The bespeak BY clause need to reference obelisk by its place or the surname referred by the an initial query.

85.Which the the complying with clauses would certainly you use to to exclude, the pillar from the second query the end of the two queries an unified using collection operators?

GROUP BY stimulate BY MINUS UNION

Answer: C.

86.Examine the given table framework as given. What will be the result of the listed below query?

SQL> DESC employees surname Null? kind ----------------------- -------- ---------------- 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 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)SELECT distinctive department_id native employeesWHERE salary > any kind of (SELECT AVG (salary)FROM employeesGROUP through department_id )UNIONSELECT *FROM employeesWHERE value > any kind of (SELECT MAX (salary)FROM employeesGROUP by department_id );It will display screen all the department IDs which have actually the typical salaries and also the maximum incomes It will throw one ORA error together the no. That columns selected in both the ask is differentIt will display screen all the department IDs which have the median salaries that will display screen all the room IDs which have actually the maximum salariesAnswer: B. The no. The columns have to be the same.

87.What among the complying with is true around the UNION operator?

UNION operates over only the first column in the pick list UNION operates over the first columns the the choose lists in the ingredient queries UNION operates over every the columns gift selected. No one of the above

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

88.You need to display the departments where the employees through the task IDs "SA_REP" or "ACCOUNTANT" work. I beg your pardon of the following queries will fetch girlfriend the compelled results? (Consider the given table structure)

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 no NULL day JOB_ID not NULL VARCHAR2(10) salary 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 employee EWhere job_id = "ACCOUNTANT";SELECT department_id FROM employees EWhere job_id = "SA_REP"UNION ALL select department_id FROM employees EWhere job_id = "ACCOUNTANT";SELECT department_id FROM employee EWhere job_id = "SA_REP"INTERSECTSelect department_id FROM employee EWhere job_id = "ACCOUNTANT";SELECT department_id FROM employees EWhere job_id = "SA_REP"MINUSSelect department_id FROM employees EWhere job_id = "ACCOUNTANT";Answer: A.

89.Which of the adhering to statement is true about the ordering of rows in a query i beg your pardon uses set operator?

It is not feasible to use ORDER through in the separation, personal, instance queries that make a link query. An order BY clause can be appended to the end of a compound query. The rows returned by a UNION ALL will be in the bespeak they take place in the two source queries. The rows reverted by a UNION will certainly be sorted across all your columns, appropriate to left.

Answer: A, B, C.

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

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

Answer: D.

Answer the related questions 91 and also 92 offered below. Take into consideration the table frameworks as offered here:

SQL> DESC employees name Null? form ----------------------- -------- ---------------- 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 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? form ----------------------- -------- ---------------- EMPLOYEE_ID no NULL NUMBER(6) START_DATE no NULL day END_DATE no NULL day JOB_ID no NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)91.You require to find the job IDs which execute not have any type of JOB background logged for them. I m sorry of the following queries will certainly work? (Consider the given table structures)

SELECT job_idFROM employees 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 of the above Answer: B.

92.Consider the following query:

SELECT distinctive job_idFROM employees herbal JOIN job_history ; i m sorry of the adhering to 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 provided here. Think about the query provided below and answer the related inquiries 93 come 97 the follow:

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 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? kind ----------------------- -------- ---------------- EMPLOYEE_ID not NULL NUMBER(6) START_DATE no NULL date END_DATE not NULL date JOB_ID no NULL VARCHAR2(10) DEPARTMENT_ID NUMBER(4)SELECT job_idFROM employeesUNION ALLSELECT job_idFROM job_history;; 93.If the employee table contains 5 records and the JOB_HISTORY consists of 3 records, how many records will be obtained from the below query?

4308

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

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

5326

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

95.If the UNION all operator is changed with MINUS operator, how countless records will certainly be obtained? (Assume there are 3 distinctive values in EMPLOYEES and also 2 in JOB_HISTORY)

3210

Answer: C. MINUS Returns only the rows in the very 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 intersect operator, how countless records will certainly be obtained? (Assume there are 3 values common in between the two tables)

8632

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

97.Consider the complying with 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 one error. Which line in the above query generates one error?

372No error is obtained

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

98.Which of the following set operator functions are sustained in SQL/Foundation:2003 but not by Oracle?

UNION ALLMINUS ALLINTERSECT ALLEXCEPT ALL

Answer: B, C, D.

99.You need to uncover out the typical JOB IDs (excluding duplicates) in the departments 100 and 200. Which query will you fire to acquire the compelled results? (Consider the table structure as given)

SQL> DESC employees surname 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) 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 indigenous employeeWHERE department_id = 200;SELECT job_id native employeeWHERE department_id = 100UNION ALLSELECT job_id indigenous employeeWHERE department_id = 200;SELECT job_id indigenous employeeWHERE department_id = 100MINUSSelect job_id from employeeWHERE department_id = 200;SELECT job_id from employeeWHERE department_id = 100INTERSECT ALLSelect job_id from employeeWHERE department_id = 200;Answer: A.

100.If a compound query has both a MINUS and an crossing operator, which will certainly be used first? (Choose the ideal answer.)

The INTERSECT, due to the fact that INTERSECT has greater precedence than MINUS.The MINUS, due to the fact that MINUS has actually a greater precedence 보다 INTERSECT.The precedence is determined by the order in which they are specified.It is not possible for a compound query to include both MINUS and also INTERSECT.

See more: Should You Use Your Real Name On A Fake Id, Fake Id Sites That Do Not Disclose Their Location

Answer: C. All set operators have actually equal precedence, therefore the precedence is established by the sequence in which lock occur.