assignment2.sql

CREATE TABLE EMP(
EMPNO DECIMAL(4) PRIMARY KEY,
ENAME VARCHAR(20) NOT NULL,
JOB VARCHAR(15),
MGR_ID DECIMAL(4),
HIREDATE DATE,
SALARY DECIMAL(7) CHECK(SALARY>0),
COMM DECIMAL(4),
DEPTNO DECIMAL(2) REFERENCES DEPT(DEPTNO));

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,3000,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR_ID,HIREDATE,SALARY,COMM,DEPTNO) VALUES(7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);


CREATE TABLE DEPT(
DEPTNO DECIMAL(2) PRIMARY KEY,
DNAME VARCHAR(15) NOT NULL,
LOC VARCHAR(20));

INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(20,'RESEARCH','DALLAS');
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(30,'SALES','CHICAGO');
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(40,'OPERATIONS','BOSTON');

SELECT * FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE EMPNO =7369 AND SALARY>(SELECT SALARY FROM EMP WHERE EMPNO=7369))

select* from emp
--list all employess whoese name begins with 'a'

SELECT * FROM EMP WHERE ENAME LIKE 'A%';

--select all those employess who don't have manager

select empno,ename from emp where MGR_ID is null

--3:
select empno,ename,salary from emp where salary between 1200 and 1400

--4:
update emp set salary=case DEPTNO when 20 then salary+(salary*.10) end

select empno,ename,salary from emp where deptno=20

--5
select count(*) from emp where job='clerk'

--6
select job,avg(salary) from emp group by job;

--7
select ename,salary from emp where salary=(select max(salary) from emp) or salary= (select min(salary) from emp);

--8
select * from DEPT where DEPTNO not in (select DEPTNO from emp)

--9
select ename,salary from emp where salary>1200 and DEPTNO=20
order by ename

--10
select deptno,count(*) as count,sum(salary) as tot_salary from emp group by DEPTNO

--11
select ename,salary from emp where ename in('miller','smith')

--12

SELECT ENAME FROM EMP WHERE ENAME LIKE 'A%' or ENAME LIKE 'M%'


--13
Select ename,salary*12 as earlysal from emp where ename='smith'

--14
select ename,salary from emp where salary not between 1500 and 2850