Oracle__11. Oracle 1Z0-051 Exam - Set Operators

Question 1

List 4 common set operators?

Answer 1

1. UNION2. UNION ALL3. INTERSECT4. MINUS

Question 2

What order are set operators evaluated?

Answer 2

Left to Right unless parenthesis are used

Question 3

What is needed in each select list used in a set?

Answer 3

each select must have the same number and data type of columns.

Question 4

If alias are used in set operators which are used in the final results?

Answer 4

The first Select statement.

Question 5

What is the difference between UNION and UNION ALL?

Answer 5

UNION eliminates duplicates.

Question 6

By default what is the sort order in UNION set operator?

Answer 6

The first column

Question 7

What happens if one select in a column is a CHAR(7) and a CHAR(10) in an Select?

Answer 7

The final data type is Varchar2(10) of the largest length.

Question 8

What happens if one column in a select is Varchar while numeric in the same column in another select?

Answer 8

Oracle returns an error.There is not implicit data type conversions in set operations.

Question 9

Are null columns ignored in duplicate checking of a UNION?

Answer 9

No

Question 10

Is the output of UNION ALL sorted by default?

Answer 10

No

Question 11

What is returned by the INTERSECT operator?

Answer 11

all rows that are common to the both queries.

Question 12

What is returned by the minus operator?

Answer 12

All rows from the first table that are not in the second table.

Question 13

Do column names need to be the same in tables for the INTERACT or MINUS set operators?

Answer 13

No

Question 14

If a particular column in the select in a set operation is a VARCHAR and the same column in the other select is a TO_CHAR will it cause an error?

Answer 14

No

Question 15

If a particular column in the select in a set operation is a Number and the same column in the other select is a value of 5 will it cause an error?

Answer 15

NoBoth are numbers

Question 16

Where does the order by clause belong in a set operation?

Answer 16

At the very end

Question 17

Using only set operator how can you return promo_category values from table promotions where promo_category is not equal to ‘discount’

Answer 17

SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_category = ‘discount’SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_category <> ‘discount’

Question 18

Does the INTERSECT remove duplicate rows?

Answer 18

Yes

Question 19

What must be the same in both selects of the INTERSECT?

Answer 19

The number of column and the order of the data types.The column names to not have to match

Question 20

What 2 different set operator can give you the same result, if you want all promo_category records where the promo_category does not equal discount?

Answer 20

SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_category = ‘discount’SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_category <> ‘discount’

Question 21

Will this statement produce an error? MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary.01 DELETE WHERE (S.salary > 8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary0.1) WHERE (S.salary <= 8000);

Answer 21

No error, this statement is validThe WHEN MATCHED and WHEN NOT MATCHED occur after the JOIN and WHERE clauses

Question 22

Between WHEN MATCHED and THEN NOT MATCHED, where does the SET clause belong in a MERGE statement?

Answer 22

in the WHEN MATCHEDThere must matching records to be updated by the SET

Question 23

Between WHEN MATCHED and THEN NOT MATCHED, where does the DELETE clause belong in a MERGE statement?

Answer 23

in the WHEN MATCHEDThere must matching records to be deleted

Question 24

Between WHEN MATCHED and THEN NOT MATCHED, where does the INSERT clause belong in a MERGE statement?

Answer 24

in the WHEN NOT MATCHNo record can exist if any records are inserted

Question 25

What is the return from this series of statements? SELECT 1 FROM dual UNION SELECT 2 FROM dual UNION SELECT 3 FROM dual UNION ALL SELECT 3 FROM dual MINUS SELECT 2 FROM dual INTERSECT SELECT 1 FROM dual

Answer 25

1The sequence is from top to bottom.The after the 3 unions there are 3 recordsThe union all add 1 more record (a union would not have added it)The minus remove 1 record.The intersect only matches the records with the value of 1

Question 26

What is returned with this statement? SELECT na FROM promo ORDER BY 1 DESC UNION SELECT na FROM promo WHERE cat = ‘TV’

Answer 26

an error because the ORDER BY clause must the last statement for any set operators such as UNION

Question 27

What is returned with this statement? SELECT na name1 FROM promo UNION SELECT na name2 FROM promo WHERE cat = ‘TV’ ORDER BY 1 DESC

Answer 27

The field name will be name1 and it will be sorted in descending order

Question 28

Does reversing tables using an INTERSECT operation change the results?

Answer 28

No.On reversing the order of the intersected table, it does not alter the result

Question 29

In a UNION are null values ignored?

Answer 29

No