DBMS Practical Slips

2. Department_Employee

create table dept0521
(
dept_no number(5) primary key,
dept_name varchar(20),
location varchar2(15)
);

create table emp0522
(
emp_no number(5) primary key,
emp_name varchar(20),
adr varchar2(15),
sal number(8)check(sal>0),
desig varchar2(10),
dept_no number(5) references dept0521(dept_no)
);

insert into dept0521 values(1,'marketing','pune') ;
insert into dept0521 values(2,'administration','mumbai') ;
insert into dept0521 values(3,'purchasing','nashik') ;
insert into dept0521 values(4,'it','pune') ;
insert into dept0521 values(5,'computer','solapur') ;

insert into emp0522 values(10,'vivek','gokulnagar',10000,'employee',1) ;
insert into emp0522 values(20,'hitesh','katraj',12000,'manager',2) ;
insert into emp0522 values(30,'ankit','ambegaon',15000,'clerk',3) ;
insert into emp0522 values(40,'rohit','katraj',20000,'jr.manager',4) ;
insert into emp0522 values(50,'laxman','tilak chowk',25000,'employee',5) ;

1. select SUM(sal) from  dept0521,emp0522
where dept0521.dept_no=emp0522.dept_no AND
dept0521.dept_name='computer';

SUM(SAL)
-------------------
                25000

2. select dept_name from   dept0521,emp0522
 where dept0521.dept_no=emp0522.dept_no AND sal>10000;

DEPT_NAME
--------------------
administration
purchasing
it
computer

3. select COUNT(emp0522.emp_no),dept_name from  dept0521,emp0522
 where dept0521.dept_no=emp0522.dept_no group by dept_name;

COUNT(emp0522.EMP_NO)  DEPT_NAME
-------------------------------------  -----------------------------
                                                1  purchasing
                                                1  it
                                                1  marketing
                                                1  administration
                                                1  computer

4. select MAX(emp0522.sal),dept_name from  dept0521,emp0522
 where dept0521.dept_no=emp0522.dept_no group by dept_name;

MAX(emp0522.SAL)  DEPT_NAME
-------------------------------  --------------------------
                                 15000  purchasing
                                 20000  it
                                 10000  marketing
                                 12000  administration
                                 25000  computer