Scribd
Upload
34 views4 pages

MySQL Hospital Assignment

The document outlines a MySQL assignment for creating and managing a hospital database. It includes steps for creating the database and tables for Patient, Doctor, and Treatment, altering tables, inserting and deleting records, and querying data. The assignment demonstrates various SQL commands and their expected outputs for managing hospital-related data.

Uploaded by

Yash Suryawanshi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
34 views4 pages

MySQL Hospital Assignment

The document outlines a MySQL assignment for creating and managing a hospital database. It includes steps for creating the database and tables for Patient, Doctor, and Treatment, altering tables, inserting and deleting records, and querying data. The assignment demonstrates various SQL commands and their expected outputs for managing hospital-related data.

Uploaded by

Yash Suryawanshi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

MySQL Hospital Database Assignment

1. Create a new database named 'Hospital'.

SQL Query:

CREATE DATABASE Hospital;

Output:

Query OK, 1 row affected

2. Create tables: Patient, Doctor, and Treatment.

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)
);

CREATE TABLE Doctor(


D_id INT(30),
Name VARCHAR(50),
specialiation VARCHAR(50),
department VARCHAR(50)
);

CREATE TABLE Treatment(


T_id INT(30),
T_name VARCHAR(50),
cost INT(30)
);

Output:

Tables created successfully.


3. Alter tables to add necessary columns.

SQL Query:

ALTER TABLE Treatment ADD P_ID INT(30);


ALTER TABLE Treatment ADD D_id INT(30);
ALTER TABLE Doctor ADD Age INT(20);

Output:

Columns added successfully.

4. Insert patient records.

SQL Query:

INSERT INTO Patient (...) VALUES (...);

Output:

8 rows inserted successfully.

5. Insert doctor records.

SQL Query:

INSERT INTO Doctor (...) VALUES (...);

Output:

10 rows inserted successfully (with duplicates).

6. Delete duplicate doctor records.

SQL Query:

DELETE FROM Doctor WHERE D_id NOT IN (101, 102, 103, 104, 105);

Output:

10 rows deleted.
7. Insert treatment records.

SQL Query:

INSERT INTO Treatment (...) VALUES (...);

Output:

9 rows inserted successfully.

8. Delete a treatment record.

SQL Query:

DELETE FROM Treatment WHERE T_id = 9;

Output:

1 row deleted.

9. Update a patient record.

SQL Query:

UPDATE Patient SET P_ID = 0000 WHERE P_ID = 8888;

Output:

1 row updated.

10. Select all patient records.

SQL Query:

SELECT * FROM Patient;

Output:

Displays 8 patient records.

11. Select all doctor names.

SQL Query:
SELECT Name FROM Doctor;

Output:

Displays 5 doctor names.

12. Select all treatment records.

SQL Query:

SELECT * FROM Treatment;

Output:

Displays 8 treatment records.

13. Describe structure of all tables.

SQL Query:

DESC Patient;
DESC Doctor;
DESC Treatment;

Output:

Shows table structures.

You might also like