dbms set 6 mca 3 gtu

SET – 6

Create the database HOSPITAL and create given tables with all necessary constraints such as primary key, foreign key, unique key, not null and check constraints.

DOCTOR (DNO, DNAME, SPECIALIZATION, CLINIC_ADDR)

CREATE TABLE DOCTOR (

DNO INT PRIMARY KEY,

DNAME VARCHAR(60) NOT NULL,

SPECIALIZATION VARCHAR(100) NOT NULL,

CLINIC_ADDR VARCHAR(200) NOT NULL

);

MEDICINE (MNO, MNAME, TYPE, CONTENT, MANUFACTURER)

CREATE TABLE MEDICINE (

MNO VARCHAR(20) PRIMARY KEY,

MNAME VARCHAR(200) NOT NULL,

TYPE VARCHAR(40) NOT NULL,

CONTENT VARCHAR(500) NOT NULL,

MANUFACTURER VARCHAR(100) NOT NULL

);

DISEASE (DISEASE_NAME, SYMPTOM1, SYMPTOM2, SYMPTOM3)

CREATE TABLE DISEASE (

DISEASE_NAME VARCHAR(100) NOT NULL,

SYMPTOM1 VARCHAR(40) NOT NULL,

SYMPTOM2 VARCHAR(40) NOT NULL,

SYMPTOM3 VARCHAR(40) NOT NULL   

);

TREATMENT (TNO, DNO, DISEASE_NAME, MNO, DOSAGE, AVG_CURE_TIME)

CREATE TABLE TREATMENT (

TNO INT PRIMARY KEY,

DNO INT REFERENCES doctor(DNO) ,

DISEASE_NAME VARCHAR(100) REFERENCES disease(DISEASE_NAME),

MNO VARCHAR(20) REFERENCES medicine(MNO),

DOSAGE int NOT NULL,

AVG_CUR_TIME int NOT NULL

);

                                               

1.     Display records of each table in ascending order.

SELECT * FROM `disease` ORDER BY DISEASE_NAME ASC;

2.     Count total number of doctors which has not given any treatment.

SELECT * FROM `doctor` WHERE DNO NOT IN (SELECT DNO FROM treatment);

3.     Display all Chennai doctors who treat cancer.

SELECT * FROM `doctor` WHERE CLINIC_ADDR = 'rajkot' AND SPECIALIZATION = 'heart';

4.     Remove disease “cancer” from disease table as well as treatment table.

DELETE FROM disease WHERE DISEASE_NAME = 'cancer';

DELETE FROM treatment WHERE DISEASE_NAME = 'cancer';

     

5.     Delete all those treatment related to liver of Dr.Prem.

DELETE FROM `treatment` WHERE `DNO` = (SELECT DNO FROM doctor WHERE DNAme = 'Dr.prem');

6.     Create index on dno, Disease name in the treatment table.

CREATE INDEX in_dno on disease(DISEASE_NAME);

7.     Display details of doctors who treat migraines.

SELECT * FROM `doctor` WHERE DNO = (SELECT DNO FROM treatment WHERE disease_name = 'fever');

8.     What is the maximum dosage of “penicillin” prescribe by the doctor for the treatment of any disease?

SELECT MAX(DOSAGE) FROM treatment WHERE MNO = (SELECT MNO FROM medicine WHERE mname = 'PARASITA MOL');

9.     Display total number of disease treated by every doctor.

SELECT COUNT(DISEASE_NAME) as NumberOfDisease  FROM `treatment`;

10.  Which doctor have no treatment for “depression”?

SELECT * FROM `doctor` WHERE dno not in (SELECT dno FROM treatment WHERE disease_name = 'fever');

11.  Create a view which contains the treatment and doctors details. Make sure that no body is allowed to modify any detail in the view.

CREATE OR REPLACE VIEW doctor_View AS

SELECT a.*, b.TNO, b.DISEASE_NAME, b.MNO, b.DOSAGE, b.AVG_CUR_TIME from doctor a, treatment b

WHERE a.DNO = b.DNO;

12.  Write a PL/SQL block to print the following report ( Symptoms wise print total number of medicine given )

13.  Write a trigger which does not allow to insert or update treatment table if AVG_CURE_TIME is less than 1.