sample SQL

1. which year did most people join the company

select to_char(hiredate,'YYYY')

from employee

group by to_char(hiredate, 'YYYY')

where count(empid)=select max(count(empid)) from employee group by to_char(hiredate,'YYYY')

2. list out the employees who earn more than average salary of their department

select *

from employee e1

group by dept_id

having salary>(select avg(salary) from employee e2

where e1.dept_id = e2.dept_id )

3. find nth maximum salary

select ename

from employee a

where &N =( select count(distinct(sal)) from employee b where a.sal < b.sal)

N = 5

4. select duplicate records in the employee table

select * from employee e

where e.empno in(select b.empno from employee b group by b.empno

having count(b.empno)<1)

and a.rowid!=min(b.rowid)

5. list the length of service the employee (n years and m months)

select ename,

to_char(trunc(months_between(sysdate,hiredate)/12)) || 'year' ||

to_char(trunk(months_between(systdate,hiredate)/12)) || 'month'

from employee