GeekInterview.com
Series: Subject: Topic:
Question: 60 of 85

Create a query that display the last name,hire date and the day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday.

LAST_NAME HIRE_DATE DAY

Grant 24-MAY-99 MONDAY
Ernst 21-MAY-99 TUESDAY
Mourgos 16-NOV-99 TUESDAY
----
--- -----

SUNDAY

Asked by: zecar | Member Since Sep-2006 | Asked on: Sep 12th, 2006

View all questions by zecar   View all answers by zecar

Showing Answers 1 - 11 of 11 Answers
Deepali

Answered On : Sep 14th, 2006

try this query-

SELECT last_name, hire_date, TO_CHAR(hire_date,'DAY') AS DAY

FROM employees

ORDER BY hire_date, DAY;

Yes  1 User has rated as useful.
  
Login to rate this answer.
zecar

Answered On : Sep 14th, 2006

View all questions by zecar   View all answers by zecar

if we order by hire date it will give the dates in ascending order which is fine but if we order by day then it will give the days in ascending order like monday,friday,saturday,sunday,thurday.... (alphaebetically)it wont give monday,tueday,wednesday

  
Login to rate this answer.
Manikandan

Answered On : Sep 22nd, 2006

SELECT last_name, hire_date, TO_CHAR(hire_date,'DAY') AS DAYFROM employeesORDER BY DAY;

  
Login to rate this answer.
pallavimeher

Answered On : Nov 16th, 2006

View all answers by pallavimeher

SELECT last_name , hire_date,to_char(hire_date,'DAY') DAY,to_char(hire_date,'D') FROM employees
ORDER BY to_char(hire_date-1,'d');

Yes  1 User has rated as useful.
  
Login to rate this answer.


--This is the answer......

select ename,to_char(hiredate,'DD-Mon-YYYY') as hire_date,to_char(hiredate,'Day') as day

from emp

order by to_char(hiredate,'d')

  
Login to rate this answer.
amikat

Answered On : Jun 3rd, 2008

View all answers by amikat

select last_name,hire_date, to_char(hire_date,'DAY') as DAY
 from emp
order by to_char(hire_date-1,'d');

  
Login to rate this answer.

NANDU@NQP3>select dayofweek Day from test456;

DAY
--------------------
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday

7 rows selected.

NANDU@NQP3>select dayofweek day, decode(dayofweek , 'Monday' , 1
2 , 'Tuesday' , 2
3 , 'Wednesday' , 3
4 , 'Thursday' , 4
5 , 'Friday' , 5
6 , 'Saturday' , 6
7 , 'Sunday' , 7) DISCARD_STUFF
8 from test456 order by 2;

DAY DISCARD_STUFF
-------------------- -------------
Monday 1
Tuesday 2
Wednesday 3
Thursday 4
Friday 5
Saturday 6
Sunday 7

7 rows selected.

NANDU@NQP3>

With Oracle there are 100's of ways of doing things :). Hope this is the way you wanted the output to be displayed.


in continuation, you can discard the discard_stuff and get the order wanted as shown below.

select dayofweek day
from test456
order by decode(dayofweek , 'Monday' , 1
, 'Tuesday' , 2
, 'Wednesday' , 3
, 'Thursday' , 4
, 'Friday' , 5
, 'Saturday' , 6
, 'Sunday' , 7)

--Sagi


--Sagi

  
Login to rate this answer.

select ename, TO_CHAR(hiredate-1,'d'), hiredate, TO_CHAR(hiredate, 'DAY') as "DAY" from emp order by TO_CHAR(hiredate-1,'d') asc 



  
Login to rate this answer.
emraan

Answered On : Dec 28th, 2009

View all answers by emraan

SELECT ename, hiredate, to_char(hiredate, 'DAY') FROM emp ORDER BY to_char(hiredate-1, 'd') 

  
Login to rate this answer.
mjennna52

Answered On : Aug 28th, 2010

View all answers by mjennna52

eg. if use scott/tiger schema:

select ename, TO_CHAR(hiredate, 'DAY') as DAY from emp;

  
Login to rate this answer.
sankar babu

Answered On : May 25th, 2011

View all answers by sankar babu

select last_name,hiredate,to_char(hiredate,'day') day

from emp

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.