create table dept(
deptno number(3) primary key,
dname varchar2(10),
loc varchar2(10)
);
create table employee(
empno number(10) primary key,
ename varchar2(10),
job varchar2(10),
hiredate date ,
mgr number(10) references employee(empno),
sal number(10) ,
comm number(10),
deptno number(3) references dept(deptno)
);
insert into dept values(10,'accounting','new york');
insert into dept values(20,'research','dallas');
insert into dept values(30,'sales','chicago');
insert into dept values(40,'operations','boston');
desc employee;
insert into employee values(7839,'king','president','17-NOV-81',NULL,5000,null,10);
insert into employee values(7566,'jones','manager','02-APR-81',7839,2975,null,20);
insert into employee values(7902,'ford','analyst','03-DEC-81',7566,3000,null,20);
insert into employee values(7369,'smith','clerk','17-DEC-80',7902,800,null,20);
insert into employee values(7698,'blake','manager','01-MAY-81',7839,2850,null,30);
insert into employee values(7499,'allen','salesman','20-FEB-80',7698,1600,300,30);
insert into employee values(7521,'ward','salesman','22-FEB-81',7698,1250,500,30);
insert into employee values(7654,'martin','salesman','28-SEP-81',7698,1250,1400,30);
insert into employee values(7782,'clark','manager','09-JUN-81',7839,2450,null,10);
insert into employee values(7788,'scott','analyst','19-APR-87',7566,3000,null,20);
insert into employee values(7844,'turner','salesman','08-SEP-81',7698,1500,null,30);
insert into employee values(7876,'adams','clerk','23-MAY-87',7788,1100,null,20);
insert into employee values(7900,'james','clerk','03-DEC-81',7698,950,null,30);
insert into employee values(7934,'miller','clerk','23-JAN-82',7782,1300,null,10);
select * from employee;
select ename from employee;
select ename,sal from employee;
select ename, comm from employee;
select empno,deptno from employee;
select ename,hiredate from employee;
select ename,job from employee;
select ename,job,sal from employee;
select dname from dept;
select dname,loc from dept;
select ename,sal*12 Annual_Sal from employee;
select ename,job,sal*6 Half_term from employee;
select ename,sal*12+2000 Annual_Sal_with_bonus from employee;
select ename,sal,sal*1.1 sal_with_hike from employee;
select ename,sal*.75 with_deduction from employee;
select ename,sal+50 month_sal_with_hike from employee;
select ename,sal*12-sal*12*.1 Annual_Sal_with_deduction from employee;
select sal+nvl(comm,0) from employee;
select empno,ename,job,hiredate,mgr,sal,comm,deptno,sal*12 annual_salary from employee;
select ename,job,sal-100 from employee;
select sal*12 from employee where ename='smith';
select ename from employee where job='clerk';
select sal from employee where job='salesman';
select * from employee where sal>2000;
select * from employee where ename='jones';
select * from employee where hiredate>'01-JAN-81';
select ename,sal,sal*12 from employee where sal*12>12000;
select * from employee where deptno=30;
select ename,hiredate from employee where hiredate < '01-JAN-81';
select * from employee where job='manager';
select ename,sal from employee where comm=1400;
select * from employee where comm>sal;
select empno from employee where hiredate < '01-JAN-87';
select * from employee where job='analyst';
select * from employee where sal>2000;
select * from employee where job='clerk' and sal<1500;
select ename,hiredate from employee where job='manager' and deptno=30;
select ename, sal*12 from employee where deptno=30 and job='salesman' and sal*12>14000;
select * from employee where deptno=30 or job='analyst';
select ename from employee where sal<1100 and job='clerk';
select ename,sal,sal*12,deptno from employee where deptno=20 and sal>1100 and sal*12>12000;
select empno ,ename from employee where job='manager' and deptno=20;
select * from employee where deptno in (20,30);
select * from employee where deptno=10 and job='analyst';
select * from employee where job='president' and sal=4000;
-- 1.WAQTD NUMBER OF EMPLOYEES GETTING SALARY LESS
-- THAN 2000 IN DEPTNO 10
select * from employee where sal<2000 and deptno=10;
-- 2.WAQTD TOTAL SALARY NEEDED TO PAY EMPLOYEES
-- WORKING AS CLERK
select sum(sal) from employee where job='clerk';
-- 3.WAQTD AVERAGE SALARY NEEDED TO PAY ALL
-- EMPLOYEES
select avg(sal) from employee;
-- 4.WAQTD NUMBER OF EMPLOYEES HAVING 'A' AS THEIR
-- FIRST CHARACTER
select count(*) from employee where ename like 'a%';
-- 5.WAQTD NUMBER OF EMPLOYEES WORKING AS CLERK OR
-- MANAGER
select count(*) from employee where job in ('clerk','manager');
-- 6.WAQTD TOTAL SALARY NEEDED TO PAY EMPLOYEES
-- HIRED IN FEB
select sum(sal) from employee where hiredate like '__-FEB-__';
-- 7.WAQTD NUMBER OF EMPLOYEES REPORTING TO 7839 (MGR)
select count(*) from employee where mgr=7839;
-- 8.WAQTD NUMBER OF EMPLOYEES GETTING COMISSION IN
-- DEPTNO 30
select count(*) from employee where comm is not null and DEPTNO=30; 
-- 9.WAQTD AVG SAL , TOTAL SAL , NUMBER OF EMPS AND
-- MAXIMUM SALARY GIVEN TO EMPLOYEES WORKING AS
-- PERSIDENT
select avg(sal),sum(sal),count(*),max(sal) from employee where job='president';
-- 10.WAQTD NUMBER OF EMPLOYEES HAVING 'A' IN THEIR
-- NAMES
select count(*) from employee where ename like '%a%';
-- 11.WAQTD NUMBER OF EMPS AND TOTAL SALARY NEEDED
-- TO PAY THE EMPLOYEES WHO HAVE 2 CONSICUTIVE L's IN
-- THEIR NAMES
select count(*),sum(sal) from employee where ename like '%ll%';
-- 12.WAQTD NUMBER OF DEPARTMENTS PRESENT IN
-- EMPLOYEE TABLE
select count(DISTINCT deptno)  from EMPLOYEE;
-- 13.WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER 'Z'
-- IN THEIR NAMES
select count(*) from EMPLOYEE where ename like '%z%';
-- 14.WAQTD NUMBER OF EMPLOYEES HAVING '$' IN THEIR
-- NAMES .
select count(*) from EMPLOYEE where ename like '%s%';
-- 15.WAQTD TOTAL SALARY GIVEN TO EMPLOYEES WORKING
-- AS CLERK IN DEPT 30
select sum(sal) from EMPLOYEE where job='clerk' and deptno=30;
-- 16.WAQTD MAXIMUM SALARY GIVEN TO THE EMPLOYEES
-- WORKING AS ANALYST
select max(sal) from EMPLOYEE where job='analyst';
-- 17.WAQTD NUMBER OF DISTINCT SALARIES PRESENT IN
-- EMPLOYEE TABLE
select count(DISTINCT sal) from EMPLOYEE;
-- 18.WAQTD NUMBER OF JOBS PRESENT IN EMPLOYEE TABLE
select count(distinct job)  from EMPLOYEE;
-- 19.WATQD AVG SALARY GIVEN TO THE CLERK
select avg(sal) from EMPLOYEE where job='clerk';
-- 20.WAQTD MINIMUM SALARY GIVEN TO THE EMPLOYEES
-- WHO WORK IN DEPT 10 AS MANAGER OR A CLERK
select min(sal) from EMPLOYEE where job in ('manager','clerk') and deptno=10;



-- 1.WAQTD NUMBER OF EMPLOYEES WORKING IN EACH 
-- DEPARTEMENT EXCEPT PRESIDENT.
select count(*),deptno from employee where job!='president' group by deptno;
-- 2.WAQTD TOTAL SALARY NEEDED TO PAY ALL THE 
-- EMPLOYEES IN EACH JOB. 
select sum(sal),job from employee group by job;
-- 3.WAQTD NUMBER OF EMPLOYEEES WORKING AS 
-- MANAGER IN EACH DEPARTMENT .
select count(*), deptno from employee where job='manager' group by deptno;
-- 4.WAQTD AVG SALARY NEEDED TO PAY ALL THE 
-- EMPLOYEES IN EACH DEPARTMENT EXCLUDING THE 
-- EMPLOYEES OF DEPTNO 20.
select avg(sal),deptno from employee group by deptno having deptno!=20;
-- 5.WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER 
-- 'A' IN THEIR NAMES IN EACH JOB .
select count(*),job from employee where ename like '%a%' group by job;
-- 6.WAQTD NUMBER OF EMPLOYEES AND AVG SALARY 
-- NEEDED TO PAY THE EMPLOYEES WHO SALARY IN 
-- GREATER THAN 2000 IN EACH DEPT.
select count(*) , avg(sal),deptno from employee where sal>2000 group by deptno;
-- 7.WAQDTD TOTAL SALARY NEEDED TO PAY AND NUMBER 
-- OF SALESMANS IN EACH DEPT.
select sum(sal), count(*),deptno from EMPLOYEE where job='salesman' group by deptno;
-- 8.WAQTD NUMBER OF EMPLOYEES WITH THEIR MAXIMUM 
-- SALARIES IN EACH JOB.
select max(sal), job ,count(*) from EMPLOYEE group by job ;
-- 9.WAQTD MAXIMUM SALARIES GIVEN TO AN EMPLOYEE 
-- WORKING IN EACH DEPT.
select max(sal), deptno from EMPLOYEE group by deptno;
-- 10.WAQTD NUMBER OF TIMES THE SALARIES PRESENT IN 
-- EMPLOYEE TABLE .
select count(*),sal from EMPLOYEE group by sal;


-- [4:21 PM] Chinni Ravi Teja, Ganji (Cognizant)
-- 1.WAQTD DNO AND NUMBER OF EMP WORKING IN EACH 
-- DEPT IF THERE 
-- ARE ATLEAST 2 CLERKS IN EACH DEPT 
select deptno,count(*) from EMPLOYEE where job='clerk' group by deptno having count(*)>=2;
-- 2.WAQTD DNO AND TOTAL SAALARYNEEDED TO PAY ALL 
-- EMP 
-- IN EACH DEPT IF THERE ARE ATLEAST 4 EMP IN EACH 
-- DEPT 
select deptno, sum(sal) from EMPLOYEE group by deptno having count(*)>=4;
-- 3.WAQTD NUMBER OF EMP EARNING SAL MORE THAN 1200 
-- IN EACH JOB 
-- AND THE TOTAL SAL NEEDED TO PAY EMP OF EACH JOB 
-- MUST EXCEES 3800
select count(*), job,sum(sal) from employee where sal>1200 group by job having sum(sal)>3800;
-- 4.WAQTD DEPTNO AND NUMBER OF EMP WORKING ONLY 
-- IF THERE ARE 2 EMP WORKING IN EACH DEPT AS 
-- MANAGER .
select deptno,count(*) from employee where job='manager' group by deptno having count(*)=2;
-- 5.WAQTD JOB AND MAX SAL OF EMP IN EACH JOB IF THE 
-- MAX SAL EXCEEDS 2600
select job,max(sal) from employee group by job having max(sal)>2600;
-- 6.WAQTD THE SALARIES WHICH ARE REPEATED IN EMP 
-- TABLE
select sal,count(sal) from employee group by sal having count(sal)>1;
-- 7.WAQTD THE HIREDATE WHICH ARE DUPLICATED IN EMP 
-- TABLE 
select hiredate,count(hiredate) from employee group by hiredate having count(hiredate)>1;
-- 8.WAQTD AVG SALARY OF EACH DEPT IF AVG SAL IS LESS 
-- THAN 3000
select avg(sal) from employee group by deptno having avg(sal)>3000;
-- 9.WAQTD DEPTNO IF THERE ARE ATLEAST 3 EMP IN EACH 
-- DEPT WHOS NAME 
-- HAS CHAR 'A' OR 'S' .
select deptno from employee where ename like '%a%' or ename like '%s%' group by deptno having count(deptno)>=3;
-- 10.WAQTD MIN AND MAX SALARIES OF EACH JOB IF MIN 
-- SAL IS MORE THAN 1000 AND MAX SAL IS LESS THAN 5000 .
select min(sal),max(sal) from employee group by job having max(sal)<5000 and min(sal)>1000;


-- 
-- 1.WAQTD NAME OF THE EMPLOYEES EARNING MORE THAN 
-- ADAMS
select ename from employee where sal > (select sal from employee where ename='adams');
-- 2.WAQTD NAME AND SALARY OF THE EMPLOYEES EARNING 
-- LESS THAN KING 
select ename, sal from employee where sal < (select sal from employee where ename='king');
-- 3.WAQTD NAME AND DEPTNO OF THE EMPLOYEES IF THEY ARE 
-- WORKING IN THE SAME DEPT AS JONES 
select ename,deptno from employee where deptno=(select deptno from employee where ename='jones');
-- 4.WAQTD NAME AND JOB OF ALL THE EMPLOYEES WORKING 
-- IN THE SAME DESIGNATION AS JAMES 
select ename, job from employee where job=(select job from employee where ename='james');
-- 5.WAQTD EMPNO AND ENAME ALONG WITH ANNUAL SALARY 
-- OF ALL THEEMPLOYEES IF THEIR ANNUAL SALARY IS 
-- GREATER THAN WARDS ANNUAL SALARY.
select empno , ename , sal*12 from employee where sal*12>(select sal*12 from employee where ename in 'ward');
-- 6.WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF THEY 
-- ARE HIRED BEFORE SCOTT 
select ename,hiredate from employee where hiredate<(select hiredate from employee where ename='scott');
-- 7.WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF THEY 
-- ARE HIRED AFTER THE PRESIDENT 
select ename,hiredate from employee where hiredate>(select hiredate from employee where job='president');
-- 8.WAQTD NAME AND SAL OF THE EMPLOYEE IF THEY ARE 
-- EARNING SAL LESS THAN THE EMPLOYEE WHOS EMPNO IS 7839
select ename,sal from EMPLOYEE where sal<(select sal from EMPLOYEE where empno=7839);
-- 9.WAQTD ALL THE DETAILS OF THE EMPLOYEES IF THE 
-- EMPLOYEES ARE HIRED BEFORE MILLER
select * from employee where hiredate<(select hiredate from employee where ename='miller');
-- 10.WAQTD ENAME AND EMPNO OF THE EMPLOYEES IF 
-- EMPLOYEES ARE EARNING MORE THAN ALLEN 
select ename,empno from employee where sal >(select sal from employee where ename='allen' );
-- 11.WAQTD ENAME AND SALARY OF ALL THE EMPLOYEES WHO 
-- ARE EARNING MORE THAN MILLER BUT LESS THAN ALLEN .
select ename,sal from employee where sal>(select sal from employee where ename='miller') and sal<(select sal from employee where ename='allen');
-- 12.WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING IN 
-- DEPT 20 AND WORKING IN THE SAME DESIGNATION AS SMITH 
select * from employee where deptno=20 and job=(select job from employee where ename='smith');
-- 13.WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING AS 
-- MANAGER IN THE SAME DEPT AS TURNER 
select * from employee where job='manager' and deptno=(select deptno from employee where ename='turner');
-- 14.WAQTD NAME AND HIREDATE OF THE EMPLOYEES HIRED 
-- AFTER 1980 AND BEFORE KING 
select ename,hiredate from employee where hiredate > '01-JAN-80' and hiredate<(select hiredate from employee where ename='king');
-- 15.WAQTD NAME AND SAL ALONG WITH ANNUAL SAL FOR ALL 
-- EMPLOYEES WHOS SAL IS LESS THAN BLAKE AND MORE THAN 
-- 3500
select ename, sal,sal*12 from employee where sal<(select sal from employee where ename='blake') and sal>3500;
-- 16.WAQTD ALL THE DETAILS OF EMPLOYEES WHO EARN MORE 
-- THAN SCOTT BUT LESS THAN KING 
select * from employee where sal>(select sal from employee where ename='scott') and sal<(select sal from employee where ename='king');
-- 17.WAQTD NAME OF THE EMPLOYEES WHOS NAME STARTS 
-- WITH 'A' AND WORKS IN THE SAME DEPT AS BLAKE 
select ename from employee where ename like 'a%' and deptno = (select deptno from employee where ename='blake');
-- 18.WAQTD NAME AND COMM IF EMPLOYEES EARN COMISSION 
-- AND WORK IN THE SAME DESIGNATION AS SMITH 
select ename,comm from EMPLOYEE where comm is not null and job in (select job from EMPLOYEE where ename='smith');
-- 19.WAQTD DETAILS OF ALL THE EMPLOYEES WORKING AS 
-- CLERK IN THE SAME DEPT AS TURNER .
select * from EMPLOYEE where job='clerk' and deptno=(select deptno from EMPLOYEE where ename='turner');
-- 20.WAQTD ENAME, SAL AND DESIGNATION OF THE EMPLOYEES 
-- WHOS ANNUAL SALARY IS MORE THAN SMITH AND LESS THAN 
-- KING.
select ename,sal,job from employee where sal*12 between (select sal*12 from employee where ename='smith') and (select sal*12 from employee where ename='king');



-- ASSIGNMENT ON CASE 2 :
-- 21.WAQTD DNAME OF THE EMPLOYEES WHOS NAME IS 
-- SMITH 
select dname from dept where deptno=(select deptno from employee where ename='smith');
-- 22.WAQTD DNAME AND LOC OF THE EMPLOYEE WHOS 
-- ENAME IS KING 
select dname,loc from dept where deptno=(select deptno from employee where ename='king');
-- 23.WAQTD LOC OF THE EMP WHOS EMPLOYEE NUMBER IS 
-- 7902
select loc from dept where deptno=(select deptno from EMPLOYEE where empno=7902);
-- 24.WAQTD DNAME AND LOC ALONG WITH DEPTNO OF THE 
-- EMPLOYEE WHOS NAME ENDS WITH 'R' .
select dname,loc,deptno from dept where deptno in (select deptno from EMPLOYEE where ename like '%r');
-- 25.WAQTD DNAME OF THE EMPLOYEE WHOS DESIGNATION 
-- IS PRESIDENT 
select dname from dept where deptno in (select deptno from EMPLOYEE where job='president');
-- 26.WAQTD NAMES OF THE EMPLOYEES WORKING IN 
-- ACCOUNTING DEPARTMENT 
select ename from EMPLOYEE where deptno=(select deptno from dept where dname='accounting');
-- 27.WAQTD ENAME AND SALARIES OF THE EMPLOYEES WHO 
-- ARE WORKING IN THE LOCATION CHICAGO
select ename,sal from EMPLOYEE where deptno=(select deptno from dept where loc='chicago');
-- 28.WAQTD DETAILS OF THE EMPLOYEES WORKING IN SALES
select * from EMPLOYEE where deptno=(select deptno from dept where dname='sales');
-- 29.WAQTD DETAILS OF THE EMP ALONG WITH ANNUAL 
-- SALARY IF EMPLOYEES ARE WORKING IN NEW YORK 
select ename,sal*12 from EMPLOYEE where deptno=(select deptno from dept where loc='new york');
-- 30.WAQTD NAMES OF EMPLOYEES WORKING IN 
-- OPERATIONS DEPARTMENT
select ename from EMPLOYEE where deptno=(select deptno from dept where dname='operations');
-- ASSIGNMENT ON CASE 1 & 2 
-- 31.WAQTD NAMES OF THE EMPLOYEES EARNING MORE 
-- THAN SCOTT IN ACCOUNTING DEPT 
select ename from EMPLOYEE where sal>(select sal from EMPLOYEE where ename='scott' )and deptno = (select deptno from dept where dname='accounting');
-- 32.WAQTD DETAILS OF THE EMPLOYEES WORKING AS 
-- MANAGER IN THE LOCATION CHICAGO
select * from EMPLOYEE where job='manager' and deptno=(select deptno from dept where loc='chicago');
-- 33.WAQTD NAME AND SAL OF THE EMPLOYEES EARNING 
-- MORE THAN KING IN THE DEPT ACCOUNTING 
select ename,sal from EMPLOYEE where sal>(select sal from EMPLOYEE where ename='king') and deptno=(select deptno from dept where DNAME='accounting');
-- 34.WAQTD DETAILS OF THE EMPLOYEES WORKING AS 
-- SALESMAN IN THE DEPARTEMENT SALES 
select * from EMPLOYEE where job='salesman' and deptno=(select deptno from dept where DNAME='sales');
-- 35.WAQTD NAME , SAL , JOB , HIREDATE OF THE EMPLOYEES 
-- WORKING IN OPERATIONS DEPARTMENT AND HIRED 
-- BEFORE KING 
select ename,sal,job,HIREDATE from EMPLOYEE where HIREDATE<(select HIREDATE from EMPLOYEE where ename='king') and deptno=(select deptno from DEPT where DNAME='operations');
-- 36.DISPLAY ALL THE EMPLOYEES WHOSE DEPARTMET 
-- NAMES ENDING 'S'.
select * from EMPLOYEE where deptno in (select deptno from dept where dname like '%s');
-- 37.WAQTD DNAME OF THE EMPLOYEES WHOS NAMES HAS 
-- CHARACTER 'A' IN IT .
select dname from dept where deptno in(select deptno from EMPLOYEE where ename like '%a%');
-- 38.WAQTD DNAME AND LOC OF THE EMPLOYEES WHOS 
-- SALARY IS RUPEES 800 .
select dname,loc from dept where deptno in (select deptno from EMPLOYEE where sal=800);
-- 39.WAQTD DNAME OF THE EMPLOYEES WHO EARN 
-- COMISSION 
select dname from dept where deptno in (select deptno from EMPLOYEE where comm is not null);
-- 40.WAQTD LOC OF THE EMPLOYEES IF THEY EARN 
-- COMISSION IN DEPT 40 
select loc from dept where deptno in (select deptno from EMPLOYEE where comm is not null and deptno=40);


-- 41.WAQTD NAME OF THE EMPLOYEE EARNING MAXIMUM
-- SALARY
select ename from EMPLOYEE where sal in (select max(sal) from EMPLOYEE);
-- 42.WAQTD NAME OF THE EMPLOYEE EARNING MINIMUM
-- SALARY
select ename from EMPLOYEE where sal in (select min(sal) from EMPLOYEE);
-- 43.WAQTD NAME AND HIREDATE OF THE EMPLOYEE HIRED
-- BEFORE
-- ALL THE EMPLOYEES (FIRST EMP)
select ename,hiredate from EMPLOYEE where hiredate in (select min(hiredate) from EMPLOYEE);
-- 44.WAQTD NAME AND HIREDATE OF THE EMPLOYEES HIRED
-- AT THE LAST
select ename,hiredate from EMPLOYEE where hiredate in (select max(hiredate) from EMPLOYEE);
-- 45.WAQTD NAME, COMM OF THE EMPLOYEE WHO EARNS
-- MIN COMISSION
select ename,comm from EMPLOYEE where comm in (select min(comm) from EMPLOYEE);
-- 46.WAQTD NAME, SAL AND COMM OF THE EMPLOYEE
-- EARNING MAXIMUM
-- COMISSION
select ename,sal,comm from EMPLOYEE EMPLOYEE where comm in (select max(comm) from EMPLOYEE);
-- 47.WAQTD DETAILS OF THE EMPLOYEE WHO HAS GREATEST
-- EMPNO
select * from EMPLOYEE where empno in (select max(empno) from EMPLOYEE);
-- 48.WAQTD DETAILS OF THE EMPLOYEES HAVING THE LEAST
-- HIREDATE
select * from EMPLOYEE where HIREDATE in(select min(hiredate) from EMPLOYEE);
-- 49.WAQTD DETAILS OF THE EMPLOYEES EARNING LEAST
-- ANNUAL SALARY
select * from EMPLOYEE where sal in (select min(sal) from EMPLOYEE);
-- 50.WAQTD NAME , ANNUAL SALARY OF THE EMPLOYEES IF
-- THEIR ANNUAL SALARY IS MORE THAN ALL THE SALESMAN
select ename,sal*12 from EMPLOYEE where sal>(select max(sal) from EMPLOYEE where job='salesman');



-- 51.WAQTD NAME OF THE EMPLOYEES EARNING SALARY 
-- MORE THAN THE SALESMAN
select ename from EMPLOYEE where sal>(select max(sal) from EMPLOYEE where job='salesman');
-- 52.WAQTD DETAILS OF THE EMPLOYEES HIRED AFTER ALL 
-- THE CLERKS 
select * from EMPLOYEE where hiredate > (select max(hiredate) from EMPLOYEE where job='clerk');
-- 53.WAQTD NAME AND SALARY FOR ALL THE EMPLOYEES IF 
-- THEY ARE EARNING LESS THAN ATLEST A MANAGER
select ename,sal from EMPLOYEE where sal<(select min(sal) from EMPLOYEE where job='manager');
-- 54.WAQTD NAME AND HIREDATE OF EMPLOYEES HIRED 
-- BEFORE ALL THE MANAGERS
select ename,hiredate from EMPLOYEE where hiredate<(select min(hiredate) from EMPLOYEE where job='manager');
-- 55.WAQTD NAMES OF THE EMPLOYEES HIRED AFTER ALL 
-- THE MANAGERS AND EARNING SALARY MORE THAN ALL 
-- THE CLERKS
select ename from EMPLOYEE where hiredate>(select max(hiredate) from EMPLOYEE where job='manager') and sal>(select max(sal) from EMPLOYEE where job='clerk');
-- 56.WAQTD DETAILS OF THE EMPLOYEES WORKING AS 
-- CLERK AND HIRED BEFORE ATLEST A SALESMAN 
select * from EMPLOYEE where job='clerk' and hiredate<(select max(hiredate) from EMPLOYEE where job='salesman');
-- 57.WAQTD DETAILS OF EMPLOYEES WORKING IN 
-- ACCOUNTING OR SALES DEPT 
select * from EMPLOYEE where deptno in (select deptno from dept where dname in ('sales','accounting'));
-- 58.WAQTD DEPARTMENT NAMES OF THE EMPOYEES WITH 
-- NAME SMITH , KING AND MILLER 
select dname from dept where deptno in (select deptno from EMPLOYEE where ename in ('miller','smith','king'));
-- 59.WAQTD DETAILS OF EMPLOYEES WORKING NEWYORK 
-- OR CHICAGO 
select * from EMPLOYEE where deptno in (select deptno from dept where loc in ('chicago','new york'));
-- 60.WAQTD EMP NAMES IF EMPLOYEES ARE HIRED AFTER 
-- ALL THE EMPLOYEES OF DEPT 10 
select ename from EMPLOYEE where hiredate>(select max(hiredate) from EMPLOYEE where deptno=10);

select ename,sal,deptno from EMPLOYEE order by sal;
-- 61.WAQTD 2ND MINIMUM SALARY
select min(sal) from EMPLOYEE where sal!=(select min(sal) from EMPLOYEE);
-- 62.WAQTD 5TH MAXIMUM SALARY
select max(sal) from EMPLOYEE where sal < (select max(sal) from EMPLOYEE where sal < (select max(sal) from EMPLOYEE where sal < (select max(sal) from EMPLOYEE where sal<(select max(sal) from EMPLOYEE))));
-- select max(sal) from (select sal from EMPLOYEE where sal!= (select max(sal) from EMPLOYEE));
-- select min(sal) from EMPLOYEE where sal!=(select min(sal) from EMPLOYEE where sal!=(select min(sal) from EMPLOYEE where sal!=(select min(sal) from EMPLOYEE where sal!=(select max(sal) from EMPLOYEE where sal!=(select max(sal) from EMPLOYEE)))));
-- 63.WAQTD NAME OF THE EMPLOYEE EARNING 3RD
-- MAXIMUM SALARY
select ename from EMPLOYEE where sal=(select max(sal) from EMPLOYEE where sal < (select max(sal) from EMPLOYEE where sal<(select max(sal) from EMPLOYEE)));
-- 64.WAQTD EMPNO OF THE EMPLOYEE EARNING 2D
-- MAXIMUM SALARY
select empno from EMPLOYEE where sal=(select max(sal) from EMPLOYEE where sal<(select max(sal) from EMPLOYEE));
-- 65.WAQTD DEPARTMENT NAME OF AN EMPLOYEE GETTING
-- 4TH MAX SAL
select dname from dept where deptno=(select deptno from EMPLOYEE where sal=(select max(sal) from EMPLOYEE where sal < (select max(sal) from EMPLOYEE where sal < (select max(sal) from EMPLOYEE where sal<(select max(sal) from EMPLOYEE)))));
-- 66.WAQTD DETAILS OF THE EMPLOYEE WHO WAS HIRED 2nd
select min(hiredate) from EMPLOYEE where hiredate>(select min(hiredate) from EMPLOYEE);
-- 67.WAQTD NAME OF THE EMPLOYEE HIRED BEFORE THE
-- LAST EMPLOYEE
select ename from EMPLOYEE where hiredate=(select max(hiredate) from EMPLOYEE where hiredate<(select max(hiredate) from EMPLOYEE));
-- 68.WAQTD LOC OF THE EMPLOYEE WHO WAS HIRED FIRST
select loc from dept where deptno=(select deptno from EMPLOYEE where hiredate=(select max(hiredate) from EMPLOYEE));
-- 69.WAQTD DETAILS OF THE EMPLOYEE EARNING 7TH
-- MINIMUM SALARY
select * from EMPLOYEE order by sal limit 6,1;
select * from EMPLOYEE where sal=(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE)))))));
-- 70.WAQTD DNAME OF EMPLOYEE GETTING 2ND MAXIMUM
-- SALARY
select dname from dept where deptno in (select deptno from EMPLOYEE where sal=(select max(sal) from EMPLOYEE where sal<(select max(sal) from EMPLOYEE)));

-- 71.WAQTD SMITHS REPORTING MANAGER'S NAME
select ename from EMPLOYEE where empno=(select mgr from EMPLOYEE where ename='smith');
-- 72.WAQTD ADAMS MANAGER'S MANAGER NAME
select ename from EMPLOYEE where empno=(select mgr from EMPLOYEE where ename='adam');
-- 73.WAQTD DNAME OF JONES MANAGER
select dname from dept where deptno in (select deptno from EMPLOYEE where empno=(select mgr from EMPLOYEE where ename='jones'));
-- 74.WAQTD MILLER'S MANAGER'S SALARY
select sal from EMPLOYEE where empno=(select mgr from EMPLOYEE where ename='miller');
-- 75.WAQTD LOC OF SMITH'S MANAGER'S MANAGER.
select loc from dept where deptno in (select deptno from EMPLOYEE where empno=(select mgr from EMPLOYEE where ename='smith'));
-- 76.WAQTD NAME OF THE EMPLOYEES REPORTING TO
-- BLAKE
select ename from EMPLOYEE where mgr=(select empno from EMPLOYEE where ename='blake');
-- 77.WAQTD NUMBER OF EMPLPOYEES REPORTING TO KING
select count(*) from EMPLOYEE where mgr=(select empno from EMPLOYEE where ename='king');
-- 78.WAQTD DETAILS OF THE EMPLOYEES REPORTING TO
-- JONES
select * from EMPLOYEE where mgr=(select empno from EMPLOYEE where ename='jones');
-- 79.WAQTD ENAMES OF THE EMPLOYEES REPORTING TO
-- BLAKE'S MANAGER
select ename from EMPLOYEE where mgr=(select mgr from EMPLOYEE where ename='blake');
-- 80.WAQTD NUMBER OF EMPLOYEES REPORTING TO FORD'S
-- MANAGER
select count(*) from EMPLOYEE where mgr=(select mgr from EMPLOYEE where ename='ford');

-- 71.WAQTD SMITHS REPORTING MANAGER'S NAME
select ename from EMPLOYEE where empno=(select mgr from EMPLOYEE where ename='smith');
-- 72.WAQTD ADAMS MANAGER'S MANAGER NAME
select ename from EMPLOYEE where empno=(select mgr from EMPLOYEE where ename='adam');
-- 73.WAQTD DNAME OF JONES MANAGER
select dname from dept where deptno=(select deptno from EMPLOYEE where empno=(select mgr from EMPLOYEE where ename='adam'));
-- 74.WAQTD MILLER'S MANAGER'S SALARY
select sal from EMPLOYEE where empno=(select mgr from EMPLOYEE where ename='miller'); 
-- 75.WAQTD LOC OF SMITH'S MANAGER'S MANAGER.
select loc from dept where deptno=(select deptno from EMPLOYEE where empno=(select mgr from EMPLOYEE where ename='smith'));
-- 76.WAQTD NAME OF THE EMPLOYEES REPORTING TO
-- BLAKE
select ename from EMPLOYEE where mgr=(select empno from EMPLOYEE where ename='blake');
-- 77.WAQTD NUMBER OF EMPLPOYEES REPORTING TO KING
select count(*) from EMPLOYEE where mgr=(select empno from EMPLOYEE where ename='king');
-- 78.WAQTD DETAILS OF THE EMPLOYEES REPORTING TO
-- JONES
select * from EMPLOYEE where mgr=(select empno from EMPLOYEE where ename='jones');
-- 79.WAQTD ENAMES OF THE EMPLOYEES REPORTING TO
-- BLAKE'S MANAGER
select ename from EMPLOYEE where mgr=(select mgr from EMPLOYEE where ename='blake');
-- 80.WAQTD NUMBER OF EMPLOYEES REPORTING TO FORD'S
-- MANAGER
select count(*) from EMPLOYEE where mgr=(select empno from EMPLOYEE where ename='ford');
-- select sal from EMPLOYEE order by sal  limit 5;
select distinct TOP 5 from EMPLOYEE order by sal;
select * from EMPLOYEE where sal=(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE where sal>(select min(sal) from EMPLOYEE)))))));