Project (pno, pname, start_date, budget, status) Department (dno, dname, HOD, loc) The relationship between Project and Department is Many to One.

 

Many-to-One

 

How to join Two Tables in Many-to-One

 

Select (Field Names/or Agg.( ))from  First Table ,Second Table  Where Second table.Second tables primary key =First Table. Second tables primary key

 

 

Project (pno, pname, start_date, budget, status)

Department (dno, dname, HOD, loc)

The relationship between Project and Department is Many to One.

 

Solution

SQL>Create table Project(pno int primary key ,pname varchar(10),sdate varchar(10),budget int ,status varchar(10),dno int);

SQL>Insert into Project values(1,’xyz’,’13/4/2021’,4000,’complete’,20);

SQL>Select * from Project;

SQL>Commit;

 

SQL>Create table Department (dno int primary key ,dname varchar(10),HOD varchar(10),loc varchar(10));

SQL>Insert into Department values(20,’abc’,’computer’,’pune’);

SQL>Select * from Department;

SQL>Commit;

 

Constraint: Primary key.

Project Status Constraints: C – Completed,P - Progressive,I - Incomplete

Consider the above tables and execute the following queries:[15mk]

1.Drop loc column from department table.

SQL>ALTER TABLE  department DROP COLUMN loc;

 

2. Display the details of project whose start_date is before one month and status is “Progressive”

SQL> Select  pname ,sdate from project where   status =’Progressive’ and sdate>’13-jun-2020’;

 

Consider the above tables and execute the following queries: [25 Mk]

1.Display the names of project and department who are worked on projects whose status is ‘Completed’.

SQL>Select  pname, dname, hod, location from department, project where   department. dno= project.dno and  project.status='Completed';

 

2.Display total budget of each department.

SQL>Select sum(budget), dname from  department, project where department. dno= project.dno  group by dname;

 

 3.Display incomplete project of each department.

SQL> select dname,count(department. dno1) from department, project where   department. dno= project.dno and project.status='I'  group by dname  ;

 

4.Display all project working under 'Mr.Desai'.

SQL>Select  pname  from department, project where   department. dno= project.dno and hod= 'Desai';

 

5.Display department wise HOD.

SQL>Select dname ,hod from department order by dname;