ADBMS - lab practice

lab_practice.sql

select * from emp where emp.ename = 'ALLEN';
select * from dept;
select * from salgrade;

/*1)
select
 emp.empno, emp.ename, emp.job, dept.loc
from 
 emp
join dept
on emp.deptno = dept.deptno
where dept.loc = 'CHICAGO' OR dept.loc = 'DALLAS'
*/

/*2)
select
 emp.empno, emp.ename, emp.sal, salgrade.grade
from emp, salgrade
where
 emp.sal between salgrade.losal and salgrade.hisal
and 
 salgrade.grade = 2 or salgrade.grade = 3
*/

/*3)
select distinct emp.empno, emp.ename, emp.job, salgrade.grade
from
 emp, salgrade 
where
 (emp.job = 'MANAGER' or emp.job = 'ANALYST') and (emp.sal between salgrade.losal and salgrade.hisal)
and 
 (salgrade.grade = 4 or salgrade.grade = 5)
*/

select * from emp where emp.job = 'SALESMAN'

/*4)
select
 e1.ename, e1.job
from
 emp e1
join 
 emp e2
on e1.job = e2.job
where
 e2.ename = 'ALLEN'
and
 e1.ename <> 'ALLEN'
*/

/*
5)
select 
 e1.empno, e1.ename, e1.sal, e1.mgr, e2.sal
from
 emp e1
join 
 emp e2
on
 e1.mgr = e2.empno
where
 e1.sal > e2.sal
*/


/*6)
select 
 distinct e1.empno, e1.ename, e1.job, e1.sal
from 
 emp e1
join
 emp e2
on
 e1.job = e2.job
join 
 emp e3
on 
 e1.sal > e3.sal
where e2.ename = 'MILLER' or e3.ename = 'ALLEN'
*/


/*7)
select
 e1.empno, e1.ename, e1.sal
from 
 emp e1
where e1.sal = (select max(emp.sal)as max_sal from emp)
*/


/*8)
select
 e1.empno, e1.ename, e1.job
from
 emp e1
join 
 dept 
on e1.deptno = dept.deptno
where
 dept.loc <> 'DALLAS'
and 
 e1.comm is not null
*/