PROBLEM 2 – SQL BASICS
Create the following databases with proper constraints (ignore foreign keys) and populate
the corresponding tables with at least 10 records.
Instructions:
1. Lab 3 is only to create the database, tables and insert values
2. Lab 4 is for executing the queries on the same database
3. Write SQL commands in a SQL Script file (.sql) and maintain a copy of the file with you for
Lab 4.
4. Create a database with name as your SRN and work only on that
Create the hospital database for the given schema of tables.
Patient(pid varchar(10), name varchar(20), dob date, weight int, gender
varchar(6), phno varchar(6), disease varchar(6), doctorid varchar(10))
Doctor( did varchar(10), dname varchar(10), dept varchar(10))
Lab( Labno varchar(10), pid varchar(10), weight int, did varchar(10), date date,
category varchar(20), patient_type varchar(20), amt int)
Inpatient( pid varchar(10), roomno varchar(20), admdate date, discdate date,
advance int, Labno varchar(10))
Outpatient(pid varchar(10), date date, Labno varchar(10))
Room(roomno varchar(20), roomtype varchar(20), roomstatus varchar(20))
Bill( Billno varchar(10), pid varchar(10), doc_charge varchar(10), med_charge
varchar(10), room_charge varchar(10), oper_charge varchar(10), no_of_days
int, billamt int, adv varchar(10), lab_charge varchar(10), healthcardno
varchar(20))
Write the following Queries for the above Hospital Schema
1. List all doctors whose name begins with M
2. List all the patients treated by Dr. Smith of Cardiology department
3. List all patients admitted and discharged on the same day
4. List the phone# of patients whose total bill amount exceeded 75000 Rs.
But had a health card
5. List all the rooms that were allocated for inpatient admission on 27-Jun-
17
6. List all outpatients who underwent a blood test referred by Dr. Charles
7. List name of doctors who did not take consultation fees for a patient visit
Additional queries:
1. List all male patients who were underweight (say <65kgs)
2. List all ICU numbers which had no patients
3. List all outpatients who visited for consultation but did not undergo any tests
