Que3

Q.3]Consider the following Entities and Relationships                [30 Marks]
Project (pno, pname, start_date, budget, status)
Department (dno, dname, HOD)
Relation between Project and Department is Many to One
          Constraint: Primary key.
                      Project Status Constraints: C – completed,
                      P-Progressive, I-Incomplete 
         Create a Database in 3NF & write queries for following.
•    List the project name and department details worked in projects that are ‘Complete’.
•    Display total budget of each department.
•    Display incomplete project of each department
•    Find the names of departments that have total budget greater than 50000   .
•    Display all project working under 'Mr.Desai'.

Ans.

drop table project cascade constraints;
drop table department2 cascade constraints;
create table project(
dno int references department2(dno),
pno int primary key,
pname varchar2(20),
sdate date,
budget int,
status char(1) check(status in ('y','n'))
);

create table department2(
dno int primary key,
dname varchar2(20),
hod varchar2(20)
);

insert into department2 values(101,'computer','james');
insert into department2 values(102,'networking','bill');
insert into department2 values(103,'database','mark');
insert into department2 values(104,'static','steve');

insert into project values(101,11,'image-processing','1-jan-2017',3000,'y');
insert into project values(101,12,'driverless car','1-jan-2017',3000,'y');
insert into project values(103,13,'wather mark','1-jan-2017',3000,'y');
insert into project values(104,14,'mobile jammer','1-jan-2017',3000,'y');
insert into project values(104,15,'mobile jammer','1-jan-2017',3000,'y');
insert into project values(103,16,'airtel shop','1-jan-2017',3000,'n');
insert into project values(103,17,'mobile database','1-jan-2017',3000,'n');
insert into project values(102,18,'router','1-jan-2017',3000,'n');

select *from project;
select *from department2;


1]List the project name and department details worked in projects that are ‘Complete’.

select pname,dno from project where status='y';

2]Display total budget of each department.

select dno ,sum(budget) from project group by dno;

3]    Display incomplete project of each department

select dno ,pno from project where status='n';

4]Find the names of departments that have total budget greater than 50000   .

select dname from department2  where dno=(select  dno from project group by dno having sum(budget)>500 );

5]Display all project working under 'Mr.Desai'.

select pname from project where dno in (select dno from department2 where dname='bill');