OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL problem [message #601494]

Thu, 21 November 2013 08:09

saturnel
Messages: 8
Registered: October 2013

Junior Member

i am preparing my expert sql exam. please i need your help about this question. i don't understand the answer, please somebody can give me an explanation about??

View the Exhibit and examine the description of the EMPLOYEES table. You executed the following SQL statement: SELECT first_name, department_id, salary FROM employees ORDER BY department_id, first_name, salary desc; Which two statements are true regarding the output of the above query? (Choose two.)

A. The values in all the columns would be sorted in the descending order.
B. The values in the SALARY column would be sorted in descending order for all the employees
having the same value in the DEPARTMENT_ID column.
C. The values in the FIRST_NAME column would be sorted in ascending order for all the
employees having the same value in the DEPARTMENT_ID column.
D. The values in the FIRST_NAME column would be sorted in the descending order for all the
employees having the same value in the DEPARTMENT_ID column.
E. The values in the SALARY column would be sorted in descending order for all the employees
having the same value in the DEPARTMENT_ID and FIRST_NAME column.

answer: C,E

Report message to a moderator

Re: SQL problem [message #601495 is a reply to message #601494]

Thu, 21 November 2013 08:12

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

I don't understand why you don't understand the answer - it exactly describes the behaviour of that order by.
Do you think it should do something else?

Report message to a moderator

Re: SQL problem [message #601497 is a reply to message #601494]

Thu, 21 November 2013 08:29

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

saturnel wrote on Thu, 21 November 2013 19:39

please somebody can give me an explanation about??

SQL> DROP TABLE employees;
 
Table dropped
SQL> CREATE TABLE employees(first_name VARCHAR2(10), department_id NUMBER(4), salary NUMBER(10));
 
Table created
SQL> INSERT INTO employees VALUES('C',20,1000);
 
1 row inserted
SQL> INSERT INTO employees VALUES('B',20,3000);
 
1 row inserted
SQL> INSERT INTO employees VALUES('A',20,2000);
 
1 row inserted
SQL> INSERT INTO employees VALUES('D',10,3000);
 
1 row inserted
SQL> INSERT INTO employees VALUES('F',10,1000);
 
1 row inserted
SQL> INSERT INTO employees VALUES('E',10,2000);
 
1 row inserted
SQL> INSERT INTO employees VALUES('G',30,9999);
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> SELECT * FROM employees;
 
FIRST_NAME DEPARTMENT_ID      SALARY
---------- ------------- -----------
C                     20        1000
B                     20        3000
A                     20        2000
D                     10        3000
F                     10        1000
E                     10        2000
G                     30        9999
 
7 rows selected
SQL> SELECT first_name, department_id, salary FROM employees ORDER BY department_id, first_name, salary DESC;
 
FIRST_NAME DEPARTMENT_ID      SALARY
---------- ------------- -----------
D                     10        3000
E                     10        2000
F                     10        1000
A                     20        2000
B                     20        3000
C                     20        1000
G                     30        9999
 
7 rows selected
SQL> SELECT first_name, department_id, salary FROM employees ORDER BY first_name, department_id, salary DESC;
 
FIRST_NAME DEPARTMENT_ID      SALARY
---------- ------------- -----------
A                     20        2000
B                     20        3000
C                     20        1000
D                     10        3000
E                     10        2000
F                     10        1000
G                     30        9999
 
7 rows selected
SQL> SELECT first_name, department_id, salary FROM employees ORDER BY salary, department_id, first_name DESC;
 
FIRST_NAME DEPARTMENT_ID      SALARY
---------- ------------- -----------
F                     10        1000
C                     20        1000
E                     10        2000
A                     20        2000
D                     10        3000
B                     20        3000
G                     30        9999
 
7 rows selected

Focus on the use of order by clause for each column. Let me know where do you fail to understand.

Regards,
Lalit

Report message to a moderator

Re: SQL problem [message #601504 is a reply to message #601497]

Thu, 21 November 2013 11:56

saturnel
Messages: 8
Registered: October 2013

Junior Member

thank you my dear but like you explained i can said that there not difference between the two statement:
SQL>SELECT first_name, department_id, salary FROM employees ORDER BY first_name, department_id, salary DESC;
SQL>SELECT first_name, department_id, salary FROM employees ORDER BY first_name, department_id, salary;
see bellow:

SQL>SELECT first_name, department_id, salary FROM employees ORDER BY first_name, department_id, salary DESC;

FIRST_NAME DEPARTMENT_ID SALARY
---------- ------------- ----------
D 10 3000
E 10 2000
F 10 1000
A 20 2000
B 20 3000
C 20 1000
G 30 9999

7 rows selected

SQL>SELECT first_name, department_id, salary FROM employees ORDER BY first_name, department_id, salary;

FIRST_NAME DEPARTMENT_ID SALARY
---------- ------------- ----------
D 10 3000
E 10 2000
F 10 1000
A 20 2000
B 20 3000
C 20 1000
G 30 9999

7 rows selected

to my point of view and assuming the definition of "order by desc " i tought that it would order the column in the desc order. but i am supprised the columns is order in asc order. Please try to give me more details about.

Report message to a moderator

Re: SQL problem [message #601505 is a reply to message #601504]

Thu, 21 November 2013 12:05

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Neither of these outputs can be produced by those queries.

I am feeling under the weather today, so maybe I am not seeing straight, but with ORDER BY first_name, it is not possible to get that output.

Aside from that, when there are multiple column to be ordered by, it first sorts by the first column listed, then by the next when there are multiple rows with the same value for the first sort column, and so on...

[Updated on: Thu, 21 November 2013 12:09]

Report message to a moderator

Re: SQL problem [message #601507 is a reply to message #601505]

Thu, 21 November 2013 12:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

joy_division wrote on Thu, 21 November 2013 10:05

Neither of these outputs can be produced by those queries.

I am feeling under the weather today, so maybe I am not seeing straight, but with ORDER BY first_name, it is not possible to get that output.

+1

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Thu, 21 November 2013 12:52]

Report message to a moderator

Previous Topic:	sql
Next Topic:	how to ignore year from date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 00:21:12 CDT 2024