Query List
46. List all the employees who joined before or after 1981.
Query:
Select * from employee where to_char(hire_date,'YYYY') not in ('1981');
(OR)
Select * from employee where to_char(hire_date,'YYYY')!='1981';
(OR)
Select * from employee where to_char(hire_date,'YYYY') <> '1981';
(OR)
Select * from employee where to_char(hire_date,'YYYY') not like '1981';
Output:
47. List the employees who are working under ‘Manager’.
Query:
Select e.ename || ' works for ' || m.ename from employee e ,employee m where e.manager = m.eno;
(OR)
Select e.ename || ' has an employee '|| m.ename from employee e , employee m where e.eno = m.manager;
Output:
48. List the emps who joined in any year but not belongs to the month of January.
Query:
Select * from employee where to_char (hire_date,'MON') not in ('JAN');
(OR)
Select * from employee where to_char (hire_date,'MON') != 'JAN';
(OR)
Select * from employee where to_char(hire_date,'MONTH') not like 'JAN%';
(OR)
Select * from employee where to_char(hire_date,’MON’) <> ‘JAN’;
Output:
49. List the emps of Department no 301 or 101 joined in the year 1981.
Query:
Select * from employee where to_char(hire_date,'YYYY') = '1981' and (dno =301 or dno =101);
(OR)
Select * from employee where to_char (hire_date,'YYYY') in ('1981') and (dno = 301 or dno =101 );
Output:
50. List the Eno, Ename, Salary, Dname of all the ‘Manager’ and ‘ANALYST’ working in Paris, Tokyo with an experience more than 7 years without receiving the Commission ascending order of Location.
Query:
Select e.eno, e.ename, e.salary, d.dname from employee e, department d where d.location in ('Paris', 'Tokyo') and e.dno = d.dno and e.eno in (select e.eno from employee e where e.job_type in ('Manager','Analyst') and (months_between(sysdate, e.hire_date)/12)> 7 and e.commission is null) order by d.location asc;
Output:
