Que2

Q. 2)     Consider the following Entities and Relationships                [30 Marks]
Department (dept_no, dept_name, location)
             Employee (emp_no, emp_name, address, salary, designation)
             Relation between Department and Employee is One to Many
Constraint: Primary key, salary should be > 0.
Create a Database in 3NF & write queries for following.
•    Find total salary of all computer department employees.
•    Find the name of department whose salary is above 10000.
•    Count the number of employees in each department.
•    Display the maximum salary of each department.
•    Display department wise employee list.

Ans.

drop table emp cascade constraints;
drop table department cascade constraints;
create table department(
dno int primary key,
dname varchar2(20),
location varchar2(10)
);

create table emp(
dno int references department(dno),
eno int primary key,
ename varchar2(10),
address varchar2(10),
salary int check(salary>0),
designation varchar2(10)
);

desc department;
desc emp;

insert into department values(101,'computer sci','pune');

insert into department values(102,'maths','nashik');
insert into department values(103,'electronic','satara');
insert into department values(104,'static','pune');
insert into department values(105,'engg','pune');
insert into emp values(101,11,'james','mumbai',10000,'staff');
insert into emp values(102,17,'steve','nashik',20000,'hod');
insert into emp values(101,13,'mark','usa',30000,'manager');
insert into emp values(101,14,'bill','pune',40000,'staff');

select *from department;
select *from emp;

1]Find total salary of all computer department employees.

select sum(salary) from emp group by(dno) having dno=101;

2]Find the name of department whose salary is above 10000.

select dname,max(salary) from department,emp group by emp.dno;

3]Count the number of employees in each department.

select dno,count(eno) from emp group by dno;

4]Display the maximum salary of each department.

select dno,max(salary) from emp group by dno;

5]Display department wise employee list.

select