Hospital Management | Skillpundit | India's No.1 | Learning

Tables Overview

S.No	Table Name	No. of Fields	Demo Data
S.No	Table Name	No. of Fields	Create	Insert
1	discharge_patient	8	Create	Insert
2	patients	6	Create	Insert
3	patient_address	7	Create	Insert
4	patient_category	4	Create	Insert
5	patient_disease	7	Create	Insert
6	rooms	4	Create	Insert
7	room_allotment	7	Create	Insert
8	staff	10	Create	Insert
9	staff_types	4	Create	Insert
10	transactions	7	Create	Insert
11	reports	4	Create	Insert
12	patient_reports	5	Create	Insert

Table Structure: discharge_patient

Field Name	Data Type
Field Name	Id	int
patient_id	varchar
room_id	varchar
joining_date	date
discharge_date	date
feedback	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "discharge_patient"

CREATE TABLE discharge_patient (
id int,
patient_id varchar(100),
room_id varchar(100),
joining_date date,
discharge_date date,
feedback varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE discharge_patient
ADD PRIMARY KEY (`id`);

Following SQL inserts 8 records in "discharge_patient"

INSERT INTO discharge_patient (id, patient_id, room_id, joining_date, discharge_date, feedback, created_at, updated_at) VALUES
(1, 'p1', 'r1', '2020-11-05', '2020-12-12', 'good', '2020-12-12 16:53:37', '2020-12-12 16:53:37'),
(2, 'p2', 'r2', '2020-11-11', '2020-12-12', 'average', '2020-12-12 16:53:37', '2020-12-12 16:53:37'),
(3, 'p3', 'r3', '2020-11-01', '2020-12-12', 'excellent', '2020-12-12 16:53:38', '2020-12-12 16:53:38');

Table Structure: patients

Field Name	Data Type
Field Name	Id	int
patient_cat	varchar
patient_id	varchar
name	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "patients"

CREATE TABLE patients (
id int,
patient_cat varchar(100),
patient_id varchar(100),
name varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE patients
ADD PRIMARY KEY (`id`);

Following SQL inserts 6 records in "patients"

INSERT INTO `patients` (id, patient_cat, patient_id, name, created_at, updated_at) VALUES
(1, '1', 'p1', 'pavan', '2020-12-12 16:56:07', '2020-12-12 16:56:07'),
(2, '2', 'p2', 'Kiran', '2020-12-12 16:56:07', '2020-12-12 16:56:07'),
(3, '2', 'p3', 'niranjan', '2020-12-12 16:56:07', '2020-12-12 16:56:07');

Table Structure: patient_address

Field Name	Data Type
Field Name	Id	int
patient_id	varchar
address	varchar
mobile	varchar
alternate_mobile	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "patient_address"

CREATE TABLE patient_address (
id int,
patient_id varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE patient_address
ADD PRIMARY KEY (`id`);

Following SQL inserts 7 records in "patient_address"

INSERT INTO `patient_address` (id, patient_id, address, mobile, alternate_mobile, created_at, updated_at) VALUES
(1, 'p1', 'orissa', '5241856325', '3571596421', '2020-12-12 16:58:50', '2020-12-12 16:58:50'),
(2, 'p2', 'mumbai', '8543217896', '3584129654', '2020-12-12 16:58:50', '2020-12-12 16:58:50'),
(3, 'p3', 'delhi', '3598462144', '8547621521', '2020-12-12 16:58:50', '2020-12-12 16:58:50');

Table Structure: patient_category

Field name	Data type
Field name	Id	int
type	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "patient_category"

CREATE TABLE patient_category (
id int,
type varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE patient_category
ADD PRIMARY KEY (`id`);

Following SQL inserts 4 records in "patient_category"

INSERT INTO patient_category (id, type, created_at, updated_at) VALUES
(1, 'op', '2020-12-12 16:59:41', '2020-12-12 16:59:41'),
(2, 'joining', '2020-12-12 16:59:41', '2020-12-12 16:59:41');

Table Structure: patient_disease

Field name	Data type
Field name	Id	int
patient_id	varchar
disease	varchar
symptoms	varchar
duration	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "patient_disease"

CREATE TABLE patient_disease (
id int,
patient_id varchar(100),
disease varchar(100),
symptoms varchar(100),
duration varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE patient_disease
ADD PRIMARY KEY (`id`);

Following SQL inserts 7 records in "patient_disease"

INSERT INTO patient_disease (id, patient_id, disease, symptoms, duration, created_at, updated_at) VALUES
(1, 'p1', 'fever', 'high_temperature', '3days', '2020-12-12 17:03:08', '2020-12-12 17:03:08'),
(2, 'p2', 'typhoid', 'high_temperature', '7days', '2020-12-12 17:03:08', '2020-12-12 17:03:08'),
(3, 'p3', 'stomachpain', 'paininstomach', '2days', '2020-12-12 17:03:08', '2020-12-12 17:03:08');

Table Structure: rooms

Field name	Data type
Field name	Id	int
room	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "rooms"

CREATE TABLE rooms (
id int,
room_number varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE rooms
ADD PRIMARY KEY (`id`);

Following SQL inserts 4 records in "rooms"

INSERT INTO `rooms` (id, room_number, created_at, updated_at) VALUES
(1, 'r1', '2020-12-12 17:03:41', '2020-12-12 17:03:41'),
(2, 'r2', '2020-12-12 17:03:41', '2020-12-12 17:03:41'),
(3, 'r3', '2020-12-12 17:03:41', '2020-12-12 17:03:41');

Table Structure: room_allotment

Field name	Data type
Field name	Id	int
room_id	varchar
patient_id	varchar
date	date
beds	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "room_allotment"

CREATE TABLE room_allotment (
id int,
room_id varchar(100),
patient_id varchar(100),
date date,
beds varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE room_allotment
ADD PRIMARY KEY (`id`);

Following SQL inserts 7 records in "room_allotment"

INSERT INTO room_allotment (id, room_id, patient_id, date, beds, created_at, updated_at) VALUES
(1, 'r1', 'p2', '2020-12-05', '1', '2020-12-12 17:05:49', '2020-12-12 17:05:49'),
(2, 'r2', 'p3', '2020-12-05', '1', '2020-12-12 17:05:49', '2020-12-12 17:05:49');

Table Structure: staff

Field name	Data type
Field name	Id	int
staff_type	varchar
name	varchar
address	varchar
mobile	varchar
alternate_mobile	varchar
qualification	varchar
experience	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "staff"

CREATE TABLE staff (
id int,
staff_type varchar(100),
name varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
qualification varchar(100),
experience varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE staff
ADD PRIMARY KEY (`id`);

Following SQL inserts 10 records in "staff"

INSERT INTO staff (id, staff_type, name, address, mobile, alternate_mobile, qualification, experience, created_at, updated_at) VALUES
(1, '1', 'Ravikumar', 'Delhi', '2587413698', '528545456456', 'Mbbs', '10', '2020-12-12 17:26:44', '2020-12-12 17:26:44'),
(2, '2', 'veena', 'kerala', '3478512546', '35874521853', 'bsc-nursing', '5', '2020-12-12 17:26:44', '2020-12-12 17:26:44'),
(3, '3', 'giri', 'tamilnadu', '25857413695', '528545456456', 'Mbbs', '7', '2020-12-12 17:26:44', '2020-12-12 17:26:44');

Table Structure: staff_types

Field name	Data type
Field name	Id	int
staff_type	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "staff_types"

CREATE TABLE staff_types (
id int,
staff_type varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE staff_types
ADD PRIMARY KEY (`id`);

Following SQL inserts 4 records in "staff_types"

INSERT INTO staff_types (id, staff_type, created_at, updated_at) VALUES
(1, 'doctor', '2020-12-12 17:09:10', '2020-12-12 17:09:10'),
(2, 'nurse', '2020-12-12 17:09:10', '2020-12-12 17:09:10'),
(3, 'technician', '2020-12-12 17:09:11', '2020-12-12 17:09:11');

Table Structure: transactions

Field name	Data type
Field name	Id	int
patient_id	varchar
billnumber	varchar
remarks	varchar
mobile	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "transactions"

CREATE TABLE transactions (
id int,
patient_id varchar(100),
billnumber varchar(100),
remark varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE transactions
ADD PRIMARY KEY (`id`);

Following SQL inserts 7 records in "transactions"

INSERT INTO transactions (id, patient_id, billnumber, remark, created_at, updated_at) VALUES
(1, 'p2', 'b001', 'room bill', '2020-12-12 17:28:12', '2020-12-12 17:28:12'),
(2, 'p3', 'b002', 'ventilator bill', '2020-12-12 17:28:12', '2020-12-12 17:28:12');

Table Structure: reports

Field name	Data type
Field name	Id	int
reports	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "reports"

CREATE TABLE reports (
id int,
patient_id varchar(100),
reports varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE reports
ADD PRIMARY KEY (`id`);

Following SQL inserts 4 records in "reports"

INSERT INTO reports (id, patient_id, reports, created_at, updated_at) VALUES
(1, 'p2','scanning', '2020-12-12 17:28:12', '2020-12-12 17:28:12'),
(2, 'p3', 'x-ray', '2020-12-12 17:28:12', '2020-12-12 17:28:12');

Table Structure: patient_reports

Field name	Data type
Field name	Id	int
patient_id	varchar
report_id	varchar
created_at	datetime
updated_at	datetime

Following SQL creates a new table called "patient_reports"

CREATE TABLE patient_reports (
id int,
patient_id varchar(100),
reports varchar(100),
created_at datetime ,
updated_at datetime );

Following syntax altering the privileges

ALTER TABLE patient_reports
ADD PRIMARY KEY (`id`);

Questions

1. How many rooms are there overall?

A. SELECTcount(*) FROM `rooms`

2. Display the transactions based on patient id?

A. SELECT* FROM `transactions` where patient_id="p2"

3. On which date the room is allotted to the patient?

A. SELECT * FROM `room_allotment`

4. How many staff types are there?

A. SELECT count(*) FROM `staff`

5. Display the remark of the transaction based on patient id?

A. SELECT * FROM `transactions` where patient_id="p3"

6. For what reason the patient has paid the bill?

A. SELECTt.remark from transactions t where t.patient_id="p2"

7. How many beds are there in patient id(p1)?

A. SELECT * FROM `patients` where DATE(created_at) = DATE('2020-12-12')
(Here given example date )

8. Display the patient disease details?

A. SELECT r.room_id,p.name,r.beds from room_allotment r left join patients p on r.patient_id=p.patient_id

9. Display the patient feedback based on patient id?

A. SELECT p.id,p.patient_id,p.name,pd.disease,pd.symptoms,pd.duration from patients p left join patient_disease pd on p.patient_id=pd.patient_id

10. Display the date time of the patients records?

A. SELECT d.id,p.name,d.feedback from discharge_patient d left join patients p on d.patient_id=p.patient_id

11. Display the patients with their categories?

A. SELECT p.id,pc.type,p.patient_id,p.name from patients p left join patient_category pc on p.patient_cat=pc.id"

12. How to know in which room which patient is there?

A. SELECT r.id,r.room_id,p.name,r.beds from room_allotment r left join patients p on r.patient_id=p.patient_id

13. For what reason does the patient come to hospital?

A. SELECT p.id,p.name,pd.disease from patients p left join patient_disease pd on p.patient_id=pd.patient_id

14. On which date the patient is discharged?

A. SELECT SELECT dp.id,dp.discharge_date,p.name from discharge_patient dp left join patients p on p.patient_id=dp.patient_id

15. Display the qualification based on staff type?

A. SELECT s.id,st.staff_type,s.name,s.address,s.mobile,s.alternate_mobile,s.qualification from staff s left join staff_types st on s.staff_type=st.id

16. Display the room number for patient id (p1)?

A. SELECT r.room_id,p.name,r.beds from room_allotment r left join patients p on r.patient_id=p.patient_id

Web Tutorials

</> SQL Tutorial

Hospital Management Project

Database Name: hospital_management

Practical Questions