DBMS Practical 1
create table dept(
DEPTNO number(4)primary key,
DNAME varchar2(20),
LOC varchar2(20)
);
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,'Operation','Boston');
create table emp(
EMPNO number(10)primary key,
ENAME varchar2(20),
JOB varchar2(20),
MGR number(6),
HIREDATE date,
SAL number(20),
COMM number(20),
DEPTNO int,
FOREIGN KEY(DEPTNO)REFERENCES dept(DEPTNO)
);
insert into emp values(7369,'Smith','Clerk',7902,'17-Dec-80',800,'',20);
insert into emp values(7499,'Allen','Salesman',7698,'20-Feb-81',1600,300,30);
insert into emp values(7521,'Ward','Salesman',7698,'22-Feb-81',1250,500,30);
insert into emp values(7566,'Jones','Manager',7839,'02-Apr-81',2975,'',20);
insert into emp values(7354,'Martin','Salesman',7698,'28-Sep-81',1250,1400,30);
insert into emp values(7698,'Blake','Manager',7839,'01-May-81',2850,'',30);
insert into emp values(7782,'Clark','Manager',7839,'09-Jun-81',2450,'',10);
insert into emp values(7788,'Scott','Analyst',7566,'09-Dec-82',3000,'',20);
insert into emp values(7849,'King',' President','','17-Nov-81',5000,'',10);
insert into emp values(7844,'Turner','Salesman',7698,'08-Sep-81',1500,0,30);
insert into emp values(7876,'Adams','Clerk',7788,'12-Jan-83',1100,'',20);
insert into emp values(7900,'James','Clerk',7698,'03-Dec-81',950,'',30);
insert into emp values(7902,'Ford','Analyst',7566,'04-Dec-81',3000,'',20);
insert into emp values(7934,'Miller','Clerk',7782,'23-Jan-82',1300,'',10);
1____select ename, sal from emp where sal>1000 ;
2____select ename from emp where deptno=20 and job='Clerk';
3____select ename, job from emp where job='Analysts' or job='Salesman';
4____select ename , job from emp where job !='Manager';
5____select * from emp where hiredate<'30-Sep-81';
6___select ename from emp where empno=7369 or empno=7521 or empno=7566 or empno=7782;
7___select ename from emp where deptno!=10 and deptno!=20;
8____select ename, sal from emp where sal>1000 and sal<2000;
9____select distinct job from emp;
10____select ename from emp where comm is null;
11___select * from emp where comm is not null;
12___select * from emp where sal>2000 and comm is null;
13___select ename from emp where ename like 'S%';
14___select ename from emp where ename like '_i%';
15___select ename,sal, sal*.12 pf from emp;
16___select empno,ename,sal from emp order by sal asc;
17__select ename, hiredate from emp order by hiredate desc;
18____Select ename,sal ,job,deptno from emp order by deptno asc, sal desc;
19____
gross=salary+hra+da-pf;
select ename,sal,sal*.12 pf,sal*.5 hra,sal*.3 da,sal+sal*.5+sal*.3-sal*.12 gross from emp order by
gross asc;
20____
select count(*) from emp;
21___
select count(distinct job) from emp;
22__
select sum(sal) from emp;
23___select max(sal) from emp where job='Salesman';
24___
select min(sal) from emp;
25____select avg(sal),count(*) from emp where deptno=20;
26___select deptno, count(*) from emp group by deptno;
27___select job, count(*) from emp group by job order by count(*) desc;
28___select job, sum(sal),max(sal) ,min(sal) from emp group by job;
29___select deptno, avg(sal),count(empno) from emp group by deptno having count(*)>3;
30_____select job,max(sal) from emp group by job having max(sal) > 1000 or max(sal) =1000;
List the average sal for each job in the department
select deptno,job,avg(sal) from emp group by deptno,job order by deptno;
