This is Database of Oracle.
And grammar below is suitable to Oralce Database.
to_char(date,format)
presents a date in the specified format.
example:
select to_char(hiredate, 'yyyy-mm-dd') from emp;
result:
add_months(date,numberic)
adds a number of months to a date (the specified number may be negative)
last_day(date)
shows the last day of its month
example:
select last_day('2018-10-20') from emp;
It will return the result
"2018-10-31"
next_day(date,day)
shows the effect of moving a date forward to a given day of the week.
Sunday -- 1
Monday -- 2
Tuesday -- 3
Wednesday -- 4
Thursday -- 5
Friday -- 6
Saturday -- 7
example:
select next_day('2018-10-20',6) from emp;
results:
'2018-10-26' which is Friday
Exercise 4 Dates
- 1 Select the name, job, and date of hire of the employees in department 20. (Format the hiredate column using a picture MM/DD/YY)
select ename, job, to_char(hiredate, 'MM-DD-YY') from emp2016150071 where deptno = 20
- 2 Use a picture to format hiredate as DAY(day of the week), MONTH (name of the month, ) DD (day of the month) and YYYY(year)
select to_char(hiredate, 'Day-Month-DD-YYYY') from emp2016150071
- 3 Which employees were hired in March?
(March cant be found, so I repalce it by May)
select * from emp2016150071 where hiredate like '%5月%'
- 4 Which employees were hired on a Tuesday?
select * from emp2016150071 where to_char(hiredate, 'Day') = '星期二'
- 5 Are there any employees who have worked more than 16 years for the company?
select * from emp2016150071 where months_between(sysdate, hiredate) > 16*12
- 6 Show the weekday of the first day of the month in which each employee was hired. (plus their names)
select ename, to_char(round(hiredate), 'Day') from emp2016150071
- 7 Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month) (plus their names)
(select
next_day(last_day(hiredate) - 7, '星期五') as payday, ename, hiredate from
emp2016150071
where
next_day(last_day(hiredate) - 7, '星期五') > hiredate)
union
(select
next_day(last_day(add_months(hiredate, 1)) - 7, '星期五') as payday, ename, hiredate from
emp2016150071
where
next_day(last_day(hiredate) - 7, '星期五') <= hiredate)
The employee Martin is hired after the last Friday of the month
So, we have to handle such problem, which is why there is another SELECT behind 'union'
- 8 Refine your answer to 7 such that it works even if an employee is hired after the last Friday of the month (cf Martin)
The answer is the same as question 7
网友评论