Query List 51 to 55

 Query List

51. Display the Employee no, Ename, Salary, Dname, Location, Department no, Job of all employees working at Tokyo or working for ACCOUNTING department with Annual Salary>28000, but the Salary should not be = 30000 or 28000 who doesn’t belongs to the Manager and whose eno is having a digit ‘7’ or ‘8’ in 3rd position in the ascending order of Dno and desc order of job. 

 Query: 

Select E.eno, E.ename, E.salary, D.dname, D.location, E.dno, E.job_type from employee E, department D where (D.location = 'Tokyo' or D.dname = 'Accounting') and E.dno=D.dno and E.eno in (select E.eno from employee E where (12 * E.salary) > 28000 and E.salary not in (30000, 28000) and E.job_type != 'Manager' and ( E.eno like '__7' or E.eno like '__8')) order by E.dno asc , E.job_type desc; 

 Output: 

No data found.

 Outer 

Query:

 Select E.eno, E.ename, E.salary, D.dname, D.location, E.dno, E.job_type from employee E, department D where (D.location = 'Tokyo' or D.dname = 'Accounting') and E.dno = D.dno; 

Output:

Inner Query: 

 Select E.eno from employee E where (12 * E.salary) > 28000 and E.salary not in (30000, 28000) and E.job_type != 'Manager' and ( E.eno like '__7' or E.eno like '__8');

 Output: 

52. List the employees who are senior to Karanveer working at Paris & Beijing. 

 Query: 

Select * from employee e, department d where d.location in ('Paris', 'Beijing') and e.dno = d.dno and e.hire_date < (select e.hire_date from employee e where e.ename = 'Karanveer'); 

Output:

53. List the employees whose Jobs are same as Manish or Salary is more than Amit.

 Query:

 Select * from employee where job_type = (select job_type from employee where ename = ‘Manish’) or salary > (Select salary from employee where ename = ‘Amit’); 

 Output:

54. List the Employees whose Salary is greater than the total remuneration of the SALESMAN.  

Query: 

Select * from employee where salary > (Select sum(nvl2 (commission, salary + commission, salary)) from employee where job_type = 'Salesman'); 

Output:

Note: NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null then NVL2 returns expr2.

55. List the employees whose jobs same as Sameer or Arjun. 

 Query: 

Select * from employee where job_type in (select job_type from employee where ename = 'Sameer' or ename = 'Arjun'); 

(OR)

 Select * from employee where job_type in (select job_type from employee where ename in ('Sameer', 'Arjun'));

Output