美文网首页
Database Exercise 4 about ''Date

Database Exercise 4 about ''Date

作者: Drebin | 来源:发表于2018-10-20 11:46 被阅读0次

    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:

    image.png

    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

    相关文章

      网友评论

          本文标题:Database Exercise 4 about ''Date

          本文链接:https://www.haomeiwen.com/subject/cinpzftx.html