1.Consider the
following entities and their relationships. Create a RDB in 3 NF for the
following and answer the queries:
Employee(eno
,ename ,designation ,salary,DOJ)
Department(dno,dname
,loc)
The relationship
between Dept & Emp is one-to-many.
1. Insert at
least five records into the tables.
2. Display the
names of employees who are working in “Quality Department”.
3. Display the
name of employee who is ‘Manager’ of “Purchase Department”.
4. Display the
name of department whose location is “Baramati” and “Mr. Pawar” is working in
it.
5. Display the
names of employees whose salary is greater than 50000 and department is
“Quality”.
1.Create table
Employee(E_No int primary key, E_Name varchar(15), Designation varchar(10),
Salary int, Date_of_Joining varchar(15));
Create table
Department(D_No int primary key, D_Name varchar(15), Location varhar(15));
2.Select E_Name
from Employee, Department where Employee.E_No= Department.E_No and D_Name=
‘Quality’;
3.Select E_Name
from Employee, Department where Employee.E_No= Department.E_No and Designation=
‘Manager’ and D_Name= ‘Purchase’;
4.Select D_Name
from Employee, Department where Employee.E_No= Department.E_No and Location=
‘Baramati’ and E_Name= ‘Pawar’;
5.Select E_Name
from Employee, Department where Employee.E_No= Department.E_No and Salary >
50000 and D_Name= ‘Quality’;
2.Consider the
following entities and their relationships. Create a RDB in 3 NF for the
following and answer the queries:
Hospital(hno
,hname , city, Est_year)
Doctor(dno ,
dname , addr, Speciality)
The relationship
between Hospital and Doctor is one - to – Many
1. Insert at
least 10 records into the tables.
2. Display the
names of hospitals which are located at “Pimpri” city.
3. Display the
names of doctors who are working in “Birla” Hospital and city name is
“Chinchwad”.
4. Display the
specialty and name of doctor who is working in “Ruby” hospital and his address
is “Pimple Gurav”.
5. Display the
names of doctors whose speciality is “medicine”.
1.Create table
Hospital(H_No int primary key, H_Name varchar(15), City varchar (15), Established_Year int);
Create table
Doctor(D_No int primary key, D_Name varchar(15), Address varchar(15),
Speciality vatchar(15));
2.Select H_Name
from Hospital where City= ‘Pimpri’;
3.Select D_Name
from Hospital, Doctor where Hospital.H_No= Doctor.H _No and H_Name= ‘Birla’ and
City= ‘Chinchwad’;
4.Select
Speciality from Hospital, Doctor where Hospital.H_No= Doctor.H_No and H_Name=
‘Ruby’ and Address= ‘Pimple Gurav’;
5.Select D_Name
from Doctor where Sppeciality= ‘Medicine’;
3.Consider the following entities and their relationships. Create a RDB in 3 NF for the following and answer the queries:
Patient (PCode, PName , Addr , Disease)
Bed (Bed_No, RoomNo, loc)
Relationship: - A one-one relationship between patient and bed.
1. Insert at least five records into the tables.
2. Display the names of patients who are admitted in room no 101.
3. Display the disease of patient whose bed_No is 1.
4. Give the room_no and bed_no of patient whose name is “Mr Ajay”.
Solution:-
Step 1 :-
SQL>Create table Patient(P_Code int primary key, P_Name varchar(15), Address varchar(15), Disease varchar(10));
SQL>Insert into Patient values(101 ,’Asha’,’pune’,’cancer’);
SQL>Select * from Patient;
SQL>COMMIT;
SQL>Create table Bed(Bed_No int primary key, Room_No int, Location varchar(10));
SQL>Insert into Bed values(101,5 ,’pune’);
SQL>Select * from Bed;
SQL>COMMIT;
Step 2 :- HOW TO JOIN TWO TABLES ?
Select ( Field names) from First Table , Second Table where
First Table.primary key = Second Table.primary key
1.SQL>Insert into Patient values(101 ,’Asha’,’pune’,’cancer’);
SQL>Insert into Bed values(101,5 ,’pune’);
2.Select P_Name from Patient, Bed where Patient.P_Code= Bed.Bed_No and Room_No=101;
3.Select Disease from Patient, Bed where Patient.P_Code= Bed.Bed_No and Bed_No=1;
4.Select Room_No and Bed_No from Patient, Bed where Patient.P_Code= Bed.Bed_No and P_Name= ‘Ajay’;
