Oracle__11. Oracle 1Z0-051 Exam - Set Operators Flashcards
List 4 common set operators?
- UNION2. UNION ALL3. INTERSECT4. MINUS
What order are set operators evaluated?
Left to Right unless parenthesis are used
What is needed in each select list used in a set?
each select must have the same number and data type of columns.
If alias are used in set operators which are used in the final results?
The first Select statement.
What is the difference between UNION and UNION ALL?
UNION eliminates duplicates.
By default what is the sort order in UNION set operator?
The first column
What happens if one select in a column is a CHAR(7) and a CHAR(10) in an Select?
The final data type is Varchar2(10) of the largest length.
What happens if one column in a select is Varchar while numeric in the same column in another select?
Oracle returns an error.There is not implicit data type conversions in set operations.
Are null columns ignored in duplicate checking of a UNION?
No
Is the output of UNION ALL sorted by default?
No
What is returned by the INTERSECT operator?
all rows that are common to the both queries.
What is returned by the minus operator?
All rows from the first table that are not in the second table.
Do column names need to be the same in tables for the INTERACT or MINUS set operators?
No
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?
No
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?
NoBoth are numbers
Where does the order by clause belong in a set operation?
At the very end
Using only set operator how can you return promo_category values from table promotions where promo_category is not equal to ‘discount’
SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_category = ‘discount’SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_category <> ‘discount’
Does the INTERSECT remove duplicate rows?
Yes
What must be the same in both selects of the INTERSECT?
The number of column and the order of the data types.The column names to not have to match
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?
SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_category = ‘discount’SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_category <> ‘discount’
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);
No error, this statement is validThe WHEN MATCHED and WHEN NOT MATCHED occur after the JOIN and WHERE clauses
Between WHEN MATCHED and THEN NOT MATCHED, where does the SET clause belong in a MERGE statement?
in the WHEN MATCHEDThere must matching records to be updated by the SET
Between WHEN MATCHED and THEN NOT MATCHED, where does the DELETE clause belong in a MERGE statement?
in the WHEN MATCHEDThere must matching records to be deleted
Between WHEN MATCHED and THEN NOT MATCHED, where does the INSERT clause belong in a MERGE statement?
in the WHEN NOT MATCHNo record can exist if any records are inserted
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
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
What is returned with this statement? SELECT na FROM promo ORDER BY 1 DESC UNION SELECT na FROM promo WHERE cat = ‘TV’
an error because the ORDER BY clause must the last statement for any set operators such as UNION
What is returned with this statement? SELECT na name1 FROM promo UNION SELECT na name2 FROM promo WHERE cat = ‘TV’ ORDER BY 1 DESC
The field name will be name1 and it will be sorted in descending order
Does reversing tables using an INTERSECT operation change the results?
No.On reversing the order of the intersected table, it does not alter the result
In a UNION are null values ignored?
No
