Consider the
following entities and their relationships. Create a RDB in 3 NF with
appropriate
data types and Constraints.
Hospital
(hno ,hname , city, Est_year, addr)
Doctor (dno
, dname , addr, Speciality)
The
relationship between Hospital and Doctor is one - to – Many
Constraints:
- Primary Key, Est_year should be greater than 1990.
Consider the
above tables and execute the following queries:
SQL>create
table hospital(hno int primary key,hname varchar(20),city varchar(20),est_year
int check(est_year>1990),addr varchar(20));
SQL>insert into hospital values(101,'ruby','pune',1993,'dholepatil road');
SQL>create
table doctor(dno int primary key,dname varchar(20),addr1 varchar(20),speciality
varchar(20),hno int );
SQL>insert into doctor values(1,'PATIL','pune','Cancer',101);
1. Delete
addr column from Hospital table.
SQL> Alter
table hospital drop column addr;
2. Display
doctor name, Hospital name and specialty of doctors from “Pune City”
SQL>
select dname,hname,speciality from doctor,hospital where
doctor.hno=hospital.hno and city='pune';
Consider the above tables and execute the following queries:
1. Display
the names of the hospitals which are located at “Pimpri” city.
SQL>
select hname from hospital,doctor where doctor.hno=hospital.hno and
city='Pimpri';
2.Display
the names of doctors who are working in “Birla” Hospital and city
name is
“Chinchwad”.
SQL>
select dname from doctor,hospital where doctor.hno=hospital.hno and
hname='Birla' and city='Chinchwad';
3.Display
the specialty of the doctors who are working in “Ruby” hospital.
SQL>
select speciality from hospital,doctor where doctor.hno=hospital.hno and
hname='Ruby';
4.Give the
count of doctor’s hospital wise which are located at “Pimple Gurav”.
SQL>
select hname,count(dno) from doctor,hospital
where doctor.hno=hospital.hno and addr='Pimple' group by hname;
5.Update an
address of Doctor to “Pimpri” whose hospital is “Ruby clinic”.
SQL>update
doctor set addr='Pimpri' where hno in(select hno from hospital where
hname='Ruby');
