Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? (Choose two.)
A.
Column positions must be used in the ORDER BY clause
B.
Only column names from the first SELECT statement in the compound query are recognized
C.
The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause
D.
Each SELECT statement in the compound query must have its own ORDER BY clause
E.
Each SELECT statement in the compound query can have its own ORDER BY clause
A - FALSE; column ord_no is part of the primary key, hence it cannot be NULL. There is no default value after sequence is dropped. When an attempt to insert a row without ord_no specified is made, an error ORA-02289 occurs.
B - TRUE; if GRANT SELECT is missing, an error "ORA-00942: table or view does not exists" occurs after INSERT attempt
C - TRUE; tested
D - FALSE; it cycles after value 100000 is reached, so after value 100000, next value is 1
E - FALSE; sequence cycles, so when MAXVALUE is reached, duplicates can occur
I am sorry, answers above belong to another question. Right answers are:
A - FALSE; e.g. SELECT dept_no,ename FROM emp UNION SELECT deptno,ename FROM employees ORDER BY ename;
B - TRUE; tested; not documented in SQL Reference, but works
C - TRUE; tested; NULLs are last in output
D - FALSE; Release 19 SQL Reference: "Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query."
E - FALSE; see D
The E is not correct because in the Union statement composition you add an ORDER BY clause to the last SELECT statement
A is incorrect because the column composition has no reason to be used in the order by, you can use only one of the columns to sort only the first select list , if it has more than one column.
B is correct because indeed the names of the columns of the first select list are those that are shown in the output of the query and are recognized
C is correct
So by default the correct answers are B and C
Provided answer are correct:
A. Column positions must be used in the ORDER BY clause --> False, you can use column name too.
B. Only column names from the first SELECT statement in the compound query are recognized --> True
C. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause --> False, to have an order, you must use ORDER BY clause.
D. Each SELECT statement in the compound query must have its own ORDER BY clause. False, ORDER BY clause is optional.
E. Each SELECT statement in the compound query can have its own ORDER BY clause --> True
it's just a case because probably you used few records, try with milion records maybe splitted in partitions ... maybe it's still working, but Oracle doesn't garantee you any order if you don't use the ORDER BY clause. At least you can try to find an official Oracle link saying that the sentence is True, but I don't think you'll be able to find one! :)
select employee_id,last_name "Last Name"
FROM employees
where employee_id=101
union
select employee_id EMP_NO, last_name
from employees
where employee_id=113;
C is correct.
Please ignore this - I want to type for other question
B is wrong - you can insert if you have insert privilege on table from other schema. not required separate privileges on sequence.
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
danito
Highly Voted 4 years, 2 months agoEkos
3 years, 7 months agoama
4 years, 1 month agoyou1234
Highly Voted 4 years agoama
4 years agoAbdullejr
Most Recent 6 months, 3 weeks agojagz3000
7 months, 3 weeks agonautil2
10 months, 3 weeks agonautil2
10 months, 3 weeks agoOracle2020
11 months, 2 weeks agozouve
1 year agoauwia
1 year agomamadu
1 year agoauwia
1 year, 1 month agomamadu
1 year agoauwia
1 year agotrgbighero
1 year, 9 months agoalgerianphoenix
1 year, 11 months agoemburria
2 years, 6 months agoryuah
2 years, 6 months agomianjee
2 years, 11 months agoyou1234
4 years agoyou1234
4 years ago