Basics - Create a query that display the last name,hire date and the day of the week on which the employee st

GeekInterview.com > Interview Questions > Oracle > Basics

Go To First | Previous Question | Next Question

Basics | Question 24 of 49

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

Total Answers and Comments: 7

Last Update: June 20, 2008 Asked by: zecar

Sponsored Links

Best Rated Answer

—

Submitted by: Deepali

try this query-

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

FROM employees

ORDER BY hire_date, DAY;

Above answer was rated as good by the following members:
subajay

September 14, 2006 09:34:15

Deepali

RE: Create a query that display the last name,hire dat...

try this query-

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

FROM employees

ORDER BY hire_date DAY;

Is this answer useful? Yes | No

1 0

Overall Rating: +1

September 14, 2006 13:58:38

zecar

Member Since: September 2006 Contribution: 52

RE: Create a query that display the last name,hire dat...

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

Is this answer useful? Yes | No

September 22, 2006 02:05:39

Manikandan

RE: Create a query that display the last name,hire dat...

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

Is this answer useful? Yes | No

November 16, 2006 07:09:32

pallavimeher

Member Since: November 2006 Contribution: 1

RE: Create a query that display the last name,hire dat...

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');

Is this answer useful? Yes | No

May 15, 2007 05:58:43

onlyforyou

Member Since: April 2007 Contribution: 2

RE: Create a query that display the last name,hire dat...

--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')

Is this answer useful? Yes | No

June 03, 2008 14:10:11

amikat

Member Since: June 2008 Contribution: 5

RE: 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 DAYGrant 24-MAY-99

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

Is this answer useful? Yes | No

June 19, 2008 20:46:43

saginandkishore

Member Since: June 2008 Contribution: 5

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

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank