Contains some important queries that I practiced upon

sql_queries_practice.sql

--display employees details along with thier managers details
select e1.empno, e1.ename, e1.job, e2.ename as manager, e2.job as manager_job, e1.sal from emp e1 left join emp e2 on e1.mgr=e2.empno;

--display employees details along with their manager details(manager name, job, dept name), dept details
select e1.empno, e1.ename, e1.job, e1.sal, d1.dname, e2.ename as manager, e2.job as manager_job, d2.dname as manager_dept 
from emp e1 left join emp e2 on e1.mgr=e2.empno
            inner join dept d1 on e1.deptno=d1.deptno
            inner join dept d2 on e2.deptno=d2.deptno
order by e1.empno

--display an employee with second highest salary
select * from (select * from emp where sal!=(select max(sal) from emp) order by sal desc) where rownum<2

--display count of employees in each department
select d.dname, count(*) as emp_count from emp e inner join dept d on d.deptno=e.deptno group by d.dname

--display all the departments with no employees in it
select * from dept where deptno not in (select distinct(deptno) from emp)

--display employees with top 3 salaries
select * from (select * from emp order by sal desc) where rownum<4

--total salary department wise
select d.dname, sum(e.sal) as total_salary from dept d left join emp e on d.deptno=e.deptno group by(d.dname)

-- following queries will be executed on HR schema, provided by oracle
--find all the employees details who belong the region 'Europe'
select e.employee_id, e.first_name, e.last_name, d.department_name, l.city, c.country_name, r.region_name, e1.first_name as manager_name
 from employees e 
        left join employees e1 on e.manager_id=e1.employee_id
        inner join departments d on e.department_id=d.department_id
        inner join locations l on d.location_id=l.location_id
        inner join countries c on l.country_id=c.country_id
        inner join regions r on c.region_id=r.region_id
 where r.region_name='Europe'
 
--find the highest salary, region wise
select r.region_id, r.region_name, max(e.salary) as max_salary from employees e
        inner join departments d on e.department_id=d.department_id
        inner join locations l on d.location_id=l.location_id
        inner join countries c on l.country_id=c.country_id
        right join regions r on c.region_id=r.region_id
group by r.region_id, r.region_name

 
--find the employee with highest salary, region wise
select * from employees where salary in (select max(e.salary) from employees e
        inner join departments d on e.department_id=d.department_id
        inner join locations l on d.location_id=l.location_id
        inner join countries c on l.country_id=c.country_id
        right join regions r on c.region_id=r.region_id
group by r.region_id)

-- display the list of all managers details(including their manager name, dept details) who have more than 3 subordinates
select e.empno, e.ename, e.job, d.dname as dept, e2.ename as manager, e2.job as manager_job, d2.dname as manager_dept from emp e 
                inner join (select mgr from emp group by mgr having count(*)>3) temp on e.empno=temp.mgr
                inner join emp e2 on e.mgr=e2.empno
                inner join dept d on e.deptno=d.deptno
                inner join dept d2 on e2.deptno=d2.deptno;

--find the largest order amount for each salesperson and the associated order number, along with the customer to whom that order belongs to
select o2.orderid, o2.customerid, c.companyname, o2.employeeid, e2.firstname, o2.freight from orders o2 
        inner join (select e.employeeid, max(o.freight) as max_freight from orders o 
                        inner join employees e on o.employeeid=e.employeeid group by e.employeeid) temp on 
                        o2.freight=temp.max_freight
        inner join employees e2 on o2.employeeid=e2.employeeid
        inner join customers c on o2.customerid=c.customerid