Slip3
Consider the following Entities and Relationships
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,Progressive, I-Incomplete
Solution
Create a Database in 3NF & write queries for following.
•List the project name and department details worked in projects that are ‘Complete’.
SQL>Select p1name, ddname, hodd, locationd from department1, project1 where department1. dno1= project1.dno1 and project1.status='C';
•Display total budget of each department.
SQL>Select sum(budget), ddname from department1, project1 where department1. dno1= project1.dno1 group by ddname;
•Display incomplete project of each department
SQL>select p1name,status ,count(department1. dno1) from department1, project1 where department1. dno1= project1.dno1 and project1.status='I' group by status,p1name ;
•Find the names of departments that have budget greater than 50000 .
SQL>select ddname from department1, project1 where department1. dno1= project1.dno1 and budget >=50000 ;
•Displayall project working under 'Mr.Desai'.
SQL>Select p1name from department1, project1 where department1. dno1= project1.dno1 and hodd= 'Mr.Desai';
