The table shows the length of service of all employees by year, month and day The table is the origi

As of the current date.

select emp.*,to_char(trunc(months_between(sysdate,hiredate)/12))|'years'||

to_char(trunc(months_between(sysdate,hiredate)-trunc(months_between

sysdate,hiredate)/12)*12))|'months'||

to_char(round((months_between(sysdate,hiredate)-trunc(months_between

sysdate,hiredate)/12)*12-

trunc(months_between(sysdate,hiredate)-trunc(months_between(sysdate,hiredate)/12)*12))

last_day(add_months(sysdate,-1))-add_months(sysdate,-1)))'days'Years of service.

from emp;

Time is limited, so I'll help you write a few first.

2.List the name, number, and department number of all clerksSelect empno, ename, deptno from emp

where job = "clerk";

3.Find out who has a higher commission than their salary.

select ename from empwhere comm>sal;

4.Find out employees whose commissions are higher than 60% of their salary.

select ename from empwhere comm>sal*;

5.Find out the details of all managers in department 10 and all clerks in department 20.

select * from emp

where deptno = 10 and job = "manager" or deptno = 20 and job = "clerk";

No, you can tell me, and I'll change it.

What format you have stored in the database is what format comes out, and if you need to convert the format, convert it in the program.

It's so complicated and don't give points, who will help you.

There's definitely something wrong with you.

Here's a way for you.

You can calculate how many integer months you've worked on so far, divide that number by 12, and get the remaining months of the year following this principle.

For example, if I joined the company on January 31, 2000, it will be 11 years if it is March 28, 2011.

The 28th in March is smaller than the 31st in January, so it can't be directly 2 integer months, but it will be treated as 1 month, and the remaining days will not be difficult.

There is nothing wrong with the syntax, but assuming 365 days per year, 30 days per month, there will be some errors.

It's as simple as subtracting the start date from the current date to display the number of days, as follows:

select empno,ename,sysdate-hiredate from emp;

If you want to display the year, month, and day, you have to perform a series of function conversions, as follows:

select empno,ename,to_char(floor(to_number((sysdate-hiredate)/365)))' years '||

to_char(ceil(months_between(sysdate,hiredate)-(floor(to_number((sysdate-hiredate)/365)))12))|' months '

from emp

The above sql will show the number of years and months of the work period, I don't think it is necessary to show the number of days anymore, because the number of days in each month is also inconsistent.