MySQL Assignment: Hospital Database
Management
This assignment covers the design and management of a Hospital Database in MySQL. It includes
creating the database and its tables, inserting, updating, deleting data, and altering schema along
with query outputs.
1. Create a database named 'Hospital'.
SQL Query:
CREATE DATABASE Hospital;
Output:
Query OK, 1 row affected (0.01 sec)
2. Use the Hospital database.
SQL Query:
USE Hospital;
Output:
Database changed
3. Create 'Patient' table.
SQL Query:
CREATE TABLE Patient(
P_ID INT(20),
P_name VARCHAR(100),
age INT(10),
gender VARCHAR(20),
contact INT(50),
Address VARCHAR(100)
);
Output:
Query OK, 0 rows affected, 3 warnings (0.03 sec)
4. Create 'Doctor' table.
SQL Query:
CREATE TABLE Doctor(
D_id INT(30),
Name VARCHAR(50),
specialiation VARCHAR(50),
department VARCHAR(50)
);
Output:
Query OK, 0 rows affected, 1 warning (0.02 sec)
5. Create 'Treatment' table.
SQL Query:
CREATE TABLE Treatment(
T_id INT(30),
T_name VARCHAR(50),
cost INT(30)
);
Output:
Query OK, 0 rows affected, 2 warnings (0.03 sec)
6. Alter 'Treatment' table to add patient and doctor IDs.
SQL Query:
ALTER TABLE Treatment ADD P_ID INT(30);
ALTER TABLE Treatment ADD D_id INT(30);
Output:
Columns added successfully.
7. Alter 'Doctor' table to add age column.
SQL Query:
ALTER TABLE Doctor ADD Age INT(20);
Output:
Query OK, 0 rows affected, 1 warning (0.02 sec)
8. Insert data into 'Patient' table.
SQL Query:
INSERT INTO Patient(P_ID, P_name, age, gender, contact, Address) VALUES
(1111, 'Yash Suryawanshi', 18, 'Male', 899988, 'Nashik'),
(2222, 'Mahesh Tamnar', 19, 'Male', 869478, 'Nagar'),
(3333, 'Prashant Suthar', 19, 'Male', 987987, 'Ahemdnagar'),
(4444, 'Harshal Suryawanshi', 18, 'Male', 967483, 'Nashik'),
(5555, 'Drushti Shah', 20, 'Female', 78956, 'Pune'),
(6666, 'Pranjal Jain', 19, 'Female', 82421, 'Mumbai'),
(7777, 'Sakshi Patil', 18, 'Female', 837290, 'Baner'),
(8888, 'Saloni Ankaikar', 20, 'Female', 64382, 'Pune');
Output:
8 rows inserted successfully.
9. Insert data into 'Doctor' table.
SQL Query:
INSERT INTO Doctor(D_id, Name, specialiation, department) VALUES
(101, 'Dr. Ramesh Sharma', 'Cardiologist', 'Cardiology'),
(102, 'Dr. Anjali Mehta', 'Neurologist', 'Neurology'),
(103, 'Dr. Vijay Kumar', 'Orthopedic', 'Orthopedics'),
(104, 'Dr. Neha Singh', 'Pediatrician', 'Pediatrics'),
(105, 'Dr. Arjun Desai', 'Dermatologist', 'Dermatology');
Output:
5 rows inserted successfully.
10. Insert data into 'Treatment' table.
SQL Query:
INSERT INTO Treatment(T_id, T_name, cost, P_ID, D_id) VALUES
(1, 'Heart Checkup', 2000, 1111, 101),
(2, 'Neurology Test', 3000, 2222, 102),
(3, 'Bone Scan', 2500, 3333, 103),
(4, 'Child Fever', 1500, 4444, 104),
(5, 'Skin Allergy', 1800, 5555, 105),
(6, 'Cardio Follow-up', 1200, 6666, 101),
(7, 'Neuro Consultation', 2700, 7777, 102),
(8, 'Fracture Dressing', 2200, 8888, 103);
Output:
8 rows inserted successfully.
11. Update patient ID 8888 to 0000.
SQL Query:
UPDATE Patient SET P_ID = 0000 WHERE P_ID = 8888;
Output:
1 row updated.
12. Delete Treatment with ID = 9 (if exists).
SQL Query:
DELETE FROM Treatment WHERE T_id = 9;
Output:
1 row deleted.
13. Select all data from 'Patient'.
SQL Query:
SELECT * FROM Patient;
Output:
Displays 8 patient records.
14. Select all data from 'Doctor'.
SQL Query:
SELECT * FROM Doctor;
Output:
Displays 5 doctor records.
15. Select names of doctors only.
SQL Query:
SELECT Name FROM Doctor;
Output:
Displays all doctor names.
16. Select all data from 'Treatment'.
SQL Query:
SELECT * FROM Treatment;
Output:
Displays 8 treatment records.
17. Describe structure of each table.
SQL Query:
DESC Patient;
DESC Doctor;
DESC Treatment;
Output:
Displays structure of all three tables.
