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 ,names 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; 

-- 11.WAQTD NAMES AND DEPTNO , JOB OF EMPS 
-- WORKING AS CLERK IN DEPT 10 OR 20 
select ename,DEPTNO,job from employee where job='clerk' and DEPTNO in (10,20);
-- 12.WAQTD DETAILS OF EMPLOYEES WORKING AS 
-- CLERK OR MANAGER IN DEPT 10 .
select * from employee where job in ('clerk','manager') and deptno=10;
-- 13.WAQTD NAMES OF EMPLOYEES WORKING IN 
-- DEPT 10 , 20 , 30 , 40 .
select ename from employee where deptno in (10,20,30,40);
-- 14.WAQTD DETAILS OF EMPLOYEES WITH EMPNO 
-- 7902,7839.
select * from employee where empno in (7902,7839);
-- 15.WAQTD DETAILS OF EMPLOYEES WORKING AS 
-- MANAGER OR SALESMAN OR CLERK 
select * from employee where job in ('salesman','manager','clerk');
-- 16.WAQTD NAMES OF EMPLOYEES HIRED AFTER 81 
-- AND BEFORE 87
select * from employee where hiredate>='01-JAN-87';
-- 17.WAQTD DETAILS OF EMPLOYEES EARNING MORE 
-- THAN 1250 BUT LESS THAN 3000 
select * from employee where sal BETWEEN 1250 and 3000;
-- 18.WAQTD NAMES OF EMPLOYEES HIRED AFTER 81 
-- INTO DEPT 10 OR 30 
select ename from employee where hiredate>='01-JAN-87' and deptno in (10,30);
-- 19.WAQTD NAMES OF EMPLOYEES ALONG WITH 
-- ANNUAL SALARY FOR THE EMPLOYEES WORKING 
-- AS MANAGER OR CLERK INTO DEPT 10 OR 30 
select ename, sal*12 from employee where job in ('manager','clerk') and deptno in (10,30);
-- 20.WAQTD ALL THE DETAILS ALONG WITH ANNUAL 
-- SALARY IF SAL IS BETWEEN 1000 AND 4000 ANNUAL 
-- SALARY MORE THAN 15000
select ename, sal * 12 from employee where sal BETWEEN 1000 and 4000 and sal*12>15000;

-- 1) LIST ALL THE EMPLOYEES WHOSE COMMISSION IS 
-- NULL
select * from employee where comm is null;
-- 2) LIST ALL THE EMPLOYEES WHO DON’T HAVE A 
-- REPORTING MANAGER
select * from employee where mgr is null;
-- 3) LIST ALL THE SALESMEN IN DEPT 30
select * from employee where job='salesman' and deptno=30;
 -- 4) LIST ALL THE SALESMEN IN DEPT NUMBER 30 AND 
-- HAVING SALARY GREATER THAN 1500
select * from employee where job='salesman' and deptno =30 and sal>1500;
-- 5) LIST ALL THE EMPLOYEES WHOSE NAME STARTS 
-- WITH ‘S’ OR ‘A’
select * from employee where ename like 's%' or ename like 'a%';
-- 6) LIST ALL THE EMPLOYEES EXCEPT THOSE WHO ARE 
-- WORKING IN DEPT 10 & 20.
select * from EMPLOYEE where deptno not in(10,20);
-- 7) LIST THE EMPLOYEES WHOSE NAME DOES NOT START 
-- WITH ‘S’
select * from EMPLOYEE where ename not like 's%';
-- 8) LIST ALL THE EMPLOYEES WHO ARE HAVING 
-- REPORTING MANAGERS IN DEPT 10
select ename from employee where mgr in(select mgr from employee where deptno=10);
-- 9) LIST ALL THE EMPLOYEES WHOSE COMMISSION IS 
-- NULL AND WORKING AS CLERK
select * from employee where comm is null and job='clerk';
-- 10) LIST ALL THE EMPLOYEES WHO DON’T HAVE A 
-- REPORTING MANAGER IN DEPTNO 10 OR 30
select * from employee where mgr not in(select mgr from employee where deptno in(10,30));
-- 11) LIST ALL THE SALESMEN IN DEPT 30 WITH SAL MORE 
-- THAN 2450
select * from employee where job='salesman' and deptno=30 and sal>2450;
-- 12) LIST ALL THE ANALYST IN DEPT NUMBER 20 AND 
-- HAVING SALARY GREATER THAN 2500
select * from employee where job='analyst' where deptno=20 and sal>2500;
-- 13) LIST ALL THE EMPLOYEES WHOSE NAME STARTS 
-- WITH ‘M’ OR ‘J’
select * from employee where ename like 'm%' or ename like 'j%';
-- 14) LIST ALL THE EMPLOYEES WITH ANNUAL SALARY 
-- EXCEPT THOSE WHO ARE WORKING IN DEPT 30
select ename, sal*12 from employee where deptno!= 30;
-- 15) LIST THE EMPLOYEES WHOSE NAME DOES NOT END 
-- WITH ‘ES’ OR ‘R’
select * from employee where ename not like '%e' or ename not like '%r';
-- 16) LIST ALL THE EMPLOYEES WHO ARE HAVING 
-- REPORTING MANAGERS IN DEPT 10 ALONG WITH 10% 
-- HIKE IN SALARY
select ename, sal*1.1 from EMPLOYEE where mgr in(select mgr from EMPLOYEE where deptno=10);
-- 17) DISPLAY ALL THE EMPLOYEE WHO ARE 
-- ‘SALESMAN’S HAVING ‘E’ AS THE LAST BUT ONE 
-- CHARACTER IN ENAME BUT SALARY HAVING EXACTLY 
-- 4 CHARACTER
select * from EMPLOYEE where job='salesman' and ename like '%e_' and sal like '____';
-- 18) DISPLAY ALL THE EMPLOYEE WHO ARE JOINED 
-- AFTER YEAR 81
select * from EMPLOYEE where hiredate>'01-JAN-81';
-- 19) DISPLAY ALL THE EMPLOYEE WHO ARE JOINED IN 
-- FEB
select * from EMPLOYEE where hiredate like '__-FEB-__';
-- 20) LIST THE EMPLOYEES WHO ARE NOT WORKING AS 
-- MANAGERS AND CLERKS IN DEPT 10 AND 20 WITH A 
-- SALARY IN THE RANGE OF 1000 TO 3000.
select * from EMPLOYEE where empno not in (select empno from  EMPLOYEE where job in ('manager','clerk') and deptno in (10,20) and sal BETWEEN 1000 and 3000);


-- 1.NAME OF THE EMPLOYEE AND HIS LOCATION OF ALL THE 
-- EMPLOYEES .
select ename,loc from EMPLOYEE,dept where EMPLOYEE.deptno=dept.deptno;
-- 2.WAQTD DNAME AND SALARY FOR ALL THE EMPLOYEE 
-- WORKING IN ACCOUNTING.
select dname,sal from EMPLOYEE,dept where EMPLOYEE.deptno=dept.deptno and dept.dname='accounting';
-- 3.WAQTD DNAME AND ANNUAL SALARY FOR ALL 
-- EMPLOYEES WHOS SALARY IS MORE THAN 2340
select dname,sal*12 from EMPLOYEE,dept where EMPLOYEE.sal>2340;
-- 4.WAQTD ENAME AND DNAME FOR EMPLOYEES HAVING 
-- CAHARACTER 'A' IN THEIR DNAME 
select ename,dname from EMPLOYEE,dept where dname like '%a%';
-- 5.WAQTD ENAME AND DNAME FOR ALL THE EMPLOYEES 
-- WORKING AS SALESMAN
select ename,dname from EMPLOYEE,dept where job='salesman';
-- 6.WADTD DNAME AND JOB FOR ALL THE EMPLOYEES WHOS 
-- JOB AND DNAME STARTS WITH CHARACTER 'S'
select dname,job from EMPLOYEE,dept where dname like 's%' and job like 's%';
-- 7.WAQTD DNAME AND MGR NO FOR EMPLOYEES 
-- REPORTING TO 7839
select dname,mgr from EMPLOYEE,dept where EMPLOYEE.deptno=dept.deptno and mgr=7839;
-- 8.WAQTD DNAME AND HIREDATE FOR EMPLOYEES HIRED 
-- AFTER 83 INTO ACCOUNTING OR RESEARCH DEPT 
select dname,hiredate from EMPLOYEE,dept where hiredate>'01-JAN-83' and dname in ('accounting','research');
-- 9.WAQTD ENAME AND DNAME OF THE EMPLOYEES WHO 
-- ARE GETTING COMM IN DEPT 10 OR 30 
select ename,dname from EMPLOYEE,dept where comm is not null and EMPLOYEE.deptno in (10,30);
-- 10.WAQTD DNAME AND EMPNO FOR ALL THE EMPLOYEES 
-- WHO'S EMPNO ARE (7839,7902) AND ARE WORKING IN LOC 
-- NEW YORK.
select dname,empno,loc from EMPLOYEE, dept where EMPLOYEE.deptno=dept.deptno and empno in (7839,7902) and loc='new york';

-- 1.WAQTD NAME OF THE EMPLOYEE AND HIS MANAGER'S 
-- NAME IF EMPLOYEE IS WORKING AS CLERK
select e1.ename employ,e2.ename mgr from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno and e1.job='clerk';
-- 2.WAQTD NAME OF THE EMPLOYEE AND MANAGER'S 
-- DESIGNATION IF MANAGER WORKS IN DEPT 10 OR 20 
select e1.ename name,e2.job mdes from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno and e2.deptno in (10,20);
-- 3.WAQTD NAME OF THE EMP AND MANAGERS SALARY IF 
-- EMPLOYEE AND MANAGER BOTH EARN MORE THAN 2300
select e1.ename,e2.sal from EMPLOYEE e1,EMPLOYEE e2  where e1.mgr=e2.empno and e1.sal>2300 and e2.sal>2300;
-- 4.WAQTD EMP NAME AND MANAGER'S HIREDATE IF 
-- EMPLOYEE WAS HIRED BEFORE1982
select e1.ename,e2.hiredate  from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno and e1.hiredate<'01-JAN-81';
-- 5.WAQTD EMP NAME AND MANAGER'S COMM IF 
-- EMPLOYEE WORKS AS SALESMAN AND MANAGER 
-- WORKS IN DEPT 30 
select e1.ename,e2.comm from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno and e1.job='salesman' and e2.deptno=30;
-- 6.WAQTD EMP NAME AND MANAGER NAME AND THEIR 
-- SALARIES IF EMPLOYEE EARNS MORE THAN MANAGER
select e1.ename,e2.ename,e1.sal,e2.sal from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno and e1.sal>e2.sal;
-- 7.WAQTD EMP NAME AND HIREDATE , MANAGER NAME 
-- AND HIREDATE IF 
-- MANAGER WAS HIRED BEFORE EMPLOYEE
select e1.ename,e1.hiredate,e2.ename,e2.hiredate from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno and e2.hiredate<e1.hiredate;
-- 8.WAQTD EMP NAME AND MANAGER NAME IF BOTH ARE 
-- WORKING IN SAME JOB
select e1.ename ,e2.ename from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno and e1.job=e2.job;
-- 9.WAQTD EMP NAME AND MANAGER NAME IF MANAGER 
-- IS WORKING AS ACTUAL MANAGER
select e1.ename,e2.ename from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno and e2.job='manager';
-- 10.WAQTD EMP NAME AND MANAGER NAME ALONG 
-- WITH THEIR ANNUAL SALARIES IF EMPLOYEE WORKS IN 
-- DEPT 10 , 20 AND MANAGER'S SAL IS GREATER THAN 
-- EMPLOYEES SALARY . 
select e1.ename,e2.ename,e1.sal*12,e2.sal*12 from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno and e1.deptno in (10,20) and e2.sal>e1.sal;
-- 11.WAQTD EMPLOYEE'S NAME AND MANAGER'S 
-- DESIGNATION FOR ALL THE EMPLOYEES
select e1.ename,e2.job from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno;
-- 12.WAQTD EMPLOYEE'S NAME AND MANAGER'S SALARY 
-- FOR ALL THE EMPLOYEES IF MANAGER'S SALARY ENDS 
-- WITH 50 
select e1.ename ,e2.sal from EMPLOYEE e1,Employee e2 where e1.mgr=e2.empno and e2.sal like  '%50';
select * from dual;
desc dual;
select 'hi'||'hello' from dual;
rename employee to emp;