Scribd
Upload
8 views8 pages

MySQL 9.3 Patient Database Operations

The document details a MySQL database session for a hospital management system, including operations such as creating tables for patients, doctors, consultations, bills, and appointments. It demonstrates various SQL commands like inserting records, altering tables, and querying data to retrieve patient and doctor information. The session also includes error handling for foreign key constraints and showcases the use of joins to combine data from multiple tables.

Uploaded by

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

MySQL 9.3 Patient Database Operations

The document details a MySQL database session for a hospital management system, including operations such as creating tables for patients, doctors, consultations, bills, and appointments. It demonstrates various SQL commands like inserting records, altering tables, and querying data to retrieve patient and doctor information. The session also includes error handling for foreign key constraints and showcases the use of joins to combine data from multiple tables.

Uploaded by

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

Enter password: ******

Welcome to the MySQL monitor. Commands end with ; or \g.


Your MySQL connection id is 9
Server version: 9.3.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its


affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;


+--------------------+
| Database |
+--------------------+
| asian |
| class |
| company |
| grande_hospital |
| information_schema |
| mydb |
| mysql |
| performance_schema |
| sakila |
| student |
| sys |
| test |
| world |
+--------------------+
13 rows in set (0.221 sec)

mysql> use grande_hospital;


Database changed
mysql> select * from patient;
+------+-------+--------+------+
| P_ID | NAME | GENDER | AGE |
+------+-------+--------+------+
| 101 | Aditi | Female | 20 |
+------+-------+--------+------+
1 row in set (0.368 sec)

mysql> alter table patient


-> add SURNAME varchar(20);
Query OK, 0 rows affected (0.299 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe patient;


+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| P_ID | varchar(15) | NO | PRI | NULL | |
| NAME | varchar(50) | YES | | NULL | |
| GENDER | char(10) | YES | | NULL | |
| AGE | int | YES | | NULL | |
| SURNAME | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.140 sec)
mysql> alter table patient
-> drop column SURNAME;
Query OK, 0 rows affected (0.226 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe patient;


+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| P_ID | varchar(15) | NO | PRI | NULL | |
| NAME | varchar(50) | YES | | NULL | |
| GENDER | char(10) | YES | | NULL | |
| AGE | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.053 sec)

mysql> insert into patient values


-> (102,'Asmita','Female',25),
-> (103,'Raj','Male',30),
-> (104,'sneha','Female',28),
-> (105,'aryan','Male',40);
Query OK, 4 rows affected (0.097 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from patient;


+------+--------+--------+------+
| P_ID | NAME | GENDER | AGE |
+------+--------+--------+------+
| 101 | Aditi | Female | 20 |
| 102 | Asmita | Female | 25 |
| 103 | Raj | Male | 30 |
| 104 | sneha | Female | 28 |
| 105 | aryan | Male | 40 |
+------+--------+--------+------+
5 rows in set (0.019 sec)

mysql> create table doctor(


-> D_ID int primary key,
-> NAME varchar(20),
-> DEPT varchar(20)
-> );
Query OK, 0 rows affected (0.237 sec)

mysql> create table consults (


-> P_ID int,
-> D_ID int,
-> foreign key (P_ID) references patient(P_ID),
-> foreign key (D_ID) references doctor(D_ID));
ERROR 3780 (HY000): Referencing column 'P_ID' and referenced column 'P_ID' in
foreign key constraint 'consults_ibfk_1' are incompatible.
mysql> alter table patient
-> modify column P_ID int(20);
Query OK, 5 rows affected, 1 warning (0.763 sec)
Records: 5 Duplicates: 0 Warnings: 1

mysql> create table consults(


-> P_ID int,
-> D_ID int,
-> foreign key (P_ID) references patient(P_ID),
-> foreign key (D_ID) references doctor(D_ID));
Query OK, 0 rows affected (0.528 sec)

mysql> create table bills (


-> B_ID int primary key,
-> P_ID int,
-> amount decimal(10,2),
-> foreign key(P_ID) references patient(P_ID));
Query OK, 0 rows affected (0.575 sec)

mysql> create table rooms (


-> R_ID int primary key,
-> type varchar(50),
-> capacity int
-> );
Query OK, 0 rows affected (0.258 sec)

mysql> insert into doctor values


-> (201,'[Link]','cardiology'),
-> (202,'[Link]','neurology'),
-> (203,'[Link]','pediatrics'),
-> (204,'[Link]','cardiology');
Query OK, 4 rows affected (0.096 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into consults values


-> (101,201),
-> (102,202),
-> (102,203),
-> (103,201),
-> (104,203);
Query OK, 5 rows affected (0.108 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> insert into bills values


-> (301,101,2000),
-> (302,102,1500),
-> (303,103,3000),
-> (304,104,2500);
Query OK, 4 rows affected (0.094 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into rooms values


-> (401,'ICU',1),
-> (402,'general',4),
-> (403,'private',2);
Query OK, 3 rows affected (0.246 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM Patient WHERE Gender = 'F';


Empty set (0.058 sec)

mysql> SELECT * FROM Patient WHERE Gender = 'Female';


+------+--------+--------+------+
| P_ID | NAME | GENDER | AGE |
+------+--------+--------+------+
| 101 | Aditi | Female | 20 |
| 102 | Asmita | Female | 25 |
| 104 | sneha | Female | 28 |
+------+--------+--------+------+
3 rows in set (0.018 sec)

mysql> SELECT Name FROM Doctor;


+----------+
| Name |
+----------+
| [Link] |
| [Link] |
| [Link] |
| [Link] |
+----------+
4 rows in set (0.022 sec)

mysql> SELECT Name FROM Patient


-> UNION
-> SELECT Name FROM Doctor;
+----------+
| Name |
+----------+
| Aditi |
| Asmita |
| Raj |
| sneha |
| aryan |
| [Link] |
| [Link] |
| [Link] |
| [Link] |
+----------+
9 rows in set (0.045 sec)

mysql> SELECT P_ID, Name FROM Patient


-> WHERE P_ID NOT IN (SELECT P_ID FROM Bills);
+------+-------+
| P_ID | Name |
+------+-------+
| 105 | aryan |
+------+-------+
1 row in set (0.020 sec)

mysql> SELECT [Link], [Link]


-> FROM Patient CROSS JOIN Rooms;
+--------+---------+
| Name | Type |
+--------+---------+
| Aditi | private |
| Aditi | general |
| Aditi | ICU |
| Asmita | private |
| Asmita | general |
| Asmita | ICU |
| Raj | private |
| Raj | general |
| Raj | ICU |
| sneha | private |
| sneha | general |
| sneha | ICU |
| aryan | private |
| aryan | general |
| aryan | ICU |
+--------+---------+
15 rows in set (0.023 sec)

mysql> SELECT [Link] AS Patient_Name, [Link] AS Doctor_Name, [Link]


-> FROM Patient
-> INNER JOIN Consults ON Patient.P_ID = Consults.P_ID
-> INNER JOIN Doctor ON Consults.D_ID = Doctor.D_ID;
+--------------+-------------+------------+
| Patient_Name | Doctor_Name | Dept |
+--------------+-------------+------------+
| Aditi | [Link] | cardiology |
| Raj | [Link] | cardiology |
| Asmita | [Link] | neurology |
| Asmita | [Link] | pediatrics |
| sneha | [Link] | pediatrics |
+--------------+-------------+------------+
5 rows in set (0.031 sec)

mysql> SELECT [Link] AS Patient_Name, [Link] AS Doctor_Name, [Link]


-> FROM Patient
-> INNER JOIN Consults ON Patient.P_ID = Consults.P_ID
-> INNER JOIN Doctor ON Consults.D_ID = Doctor.D_ID
-> WHERE [Link] = 'Cardiology';
+--------------+-------------+------------+
| Patient_Name | Doctor_Name | Dept |
+--------------+-------------+------------+
| Aditi | [Link] | cardiology |
| Raj | [Link] | cardiology |
+--------------+-------------+------------+
2 rows in set (0.019 sec)

mysql> SELECT * FROM Patient


-> NATURAL JOIN Consults;
+------+--------+--------+------+------+
| P_ID | NAME | GENDER | AGE | D_ID |
+------+--------+--------+------+------+
| 101 | Aditi | Female | 20 | 201 |
| 102 | Asmita | Female | 25 | 202 |
| 102 | Asmita | Female | 25 | 203 |
| 103 | Raj | Male | 30 | 201 |
| 104 | sneha | Female | 28 | 203 |
+------+--------+--------+------+------+
5 rows in set (0.032 sec)

mysql> SELECT [Link] AS Patient_Name, [Link] AS Doctor_Name, [Link]


-> FROM Patient
-> LEFT JOIN Consults ON Patient.P_ID = Consults.P_ID
-> LEFT JOIN Doctor ON Consults.D_ID = Doctor.D_ID;
+--------------+-------------+------------+
| Patient_Name | Doctor_Name | Dept |
+--------------+-------------+------------+
| Aditi | [Link] | cardiology |
| Asmita | [Link] | neurology |
| Asmita | [Link] | pediatrics |
| Raj | [Link] | cardiology |
| sneha | [Link] | pediatrics |
| aryan | NULL | NULL |
+--------------+-------------+------------+
6 rows in set (0.027 sec)

mysql> SELECT [Link] AS Doctor_Name, [Link] AS Patient_Name


-> FROM Patient
-> RIGHT JOIN Consults ON Patient.P_ID = Consults.P_ID
-> RIGHT JOIN Doctor ON Consults.D_ID = Doctor.D_ID;
+-------------+--------------+
| Doctor_Name | Patient_Name |
+-------------+--------------+
| [Link] | Aditi |
| [Link] | Raj |
| [Link] | Asmita |
| [Link] | Asmita |
| [Link] | sneha |
| [Link] | NULL |
+-------------+--------------+
6 rows in set (0.030 sec)

mysql> SELECT [Link] AS Patient_Name, [Link] AS Doctor_Name


-> FROM Patient
-> LEFT JOIN Consults ON Patient.P_ID = Consults.P_ID
-> LEFT JOIN Doctor ON Consults.D_ID = Doctor.D_ID
-> UNION
-> SELECT [Link] AS Patient_Name, [Link] AS Doctor_Name
-> FROM Patient
-> RIGHT JOIN Consults ON Patient.P_ID = Consults.P_ID
-> RIGHT JOIN Doctor ON Consults.D_ID = Doctor.D_ID;
+--------------+-------------+
| Patient_Name | Doctor_Name |
+--------------+-------------+
| Aditi | [Link] |
| Asmita | [Link] |
| Asmita | [Link] |
| Raj | [Link] |
| sneha | [Link] |
| aryan | NULL |
| NULL | [Link] |
+--------------+-------------+
7 rows in set (0.048 sec)

mysql> SELECT P_ID


-> FROM Consults
-> WHERE D_ID IN (SELECT D_ID FROM Doctor WHERE Dept = 'Cardiology')
-> GROUP BY P_ID
-> HAVING COUNT(DISTINCT D_ID) = (SELECT COUNT(*) FROM Doctor WHERE Dept =
'Cardiology');
Empty set (0.062 sec)

mysql> SELECT [Link], COUNT(Appointment.a_id) AS total_appointments


-> FROM Doctor
-> LEFT JOIN Appointment ON Doctor.d_id = Appointment.d_id
-> GROUP BY [Link];
ERROR 1146 (42S02): Table 'grande_hospital.appointment' doesn't exist
mysql> CREATE TABLE Appointment (
-> a_id INT PRIMARY KEY AUTO_INCREMENT,
-> p_id INT,
-> d_id INT,
-> appointment_date DATE,
-> status VARCHAR(20),
-> FOREIGN KEY (p_id) REFERENCES Patient(p_id),
-> FOREIGN KEY (d_id) REFERENCES Doctor(d_id)
-> );
Query OK, 0 rows affected (0.528 sec)

mysql> CREATE TABLE Medicine (


-> m_id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> price DECIMAL(8,2) NOT NULL
-> );
Query OK, 0 rows affected (0.253 sec)

mysql> CREATE TABLE Prescription (


-> pr_id INT PRIMARY KEY AUTO_INCREMENT,
-> a_id INT,
-> m_id INT,
-> dosage VARCHAR(50),
-> FOREIGN KEY (a_id) REFERENCES Appointment(a_id),
-> FOREIGN KEY (m_id) REFERENCES Medicine(m_id)
-> );
Query OK, 0 rows affected (0.599 sec)

mysql> INSERT INTO Appointment (p_id, d_id, appointment_date, status) VALUES


-> (101, 201, '2025-08-10', 'Scheduled'),
-> (102, 202, '2025-08-11', 'Completed'),
-> (103, 201, '2025-08-12', 'Cancelled'),
-> (104, 203, '2025-08-15', 'Scheduled'),
-> (105, 204, '2025-08-17', 'Completed');
Query OK, 5 rows affected (0.091 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> INSERT INTO Medicine (name, price) VALUES


-> ('Paracetamol', 5.00),
-> ('Amoxicillin', 10.50),
-> ('Ibuprofen', 7.25),
-> ('Metformin', 12.00),
-> ('Omeprazole', 15.00);
Query OK, 5 rows affected (0.069 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> INSERT INTO Prescription (a_id, m_id, dosage) VALUES


-> (1, 1, '500mg twice daily'),
-> (1, 3, '200mg once daily'),
-> (2, 2, '250mg three times daily'),
-> (4, 4, '500mg once daily'),
-> (5, 5, '20mg before meal');
Query OK, 5 rows affected (0.110 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM Appointment WHERE appointment_date >= CURDATE();


+------+------+------+------------------+-----------+
| a_id | p_id | d_id | appointment_date | status |
+------+------+------+------------------+-----------+
| 5 | 105 | 204 | 2025-08-17 | Completed |
+------+------+------+------------------+-----------+
1 row in set (0.047 sec)
mysql> SELECT *
-> FROM patient
-> ORDER BY Age ASC;
+------+--------+--------+------+
| P_ID | NAME | GENDER | AGE |
+------+--------+--------+------+
| 101 | Aditi | Female | 20 |
| 102 | Asmita | Female | 25 |
| 104 | sneha | Female | 28 |
| 103 | Raj | Male | 30 |
| 105 | aryan | Male | 40 |
+------+--------+--------+------+
5 rows in set (0.048 sec)

mysql> SELECT *
-> FROM patient
-> ORDER BY Age DESC;
+------+--------+--------+------+
| P_ID | NAME | GENDER | AGE |
+------+--------+--------+------+
| 105 | aryan | Male | 40 |
| 103 | Raj | Male | 30 |
| 104 | sneha | Female | 28 |
| 102 | Asmita | Female | 25 |
| 101 | Aditi | Female | 20 |
+------+--------+--------+------+
5 rows in set (0.009 sec)

mysql> SELECT *
-> FROM patient
-> WHERE Age > 18;
+------+--------+--------+------+
| P_ID | NAME | GENDER | AGE |
+------+--------+--------+------+
| 101 | Aditi | Female | 20 |
| 102 | Asmita | Female | 25 |
| 103 | Raj | Male | 30 |
| 104 | sneha | Female | 28 |
| 105 | aryan | Male | 40 |
+------+--------+--------+------+
5 rows in set (0.013 sec)

mysql> INSERT INTO patient (P_ID, P_Name, Age, Gender) VALUES


-> (101, 'Aarav Sharma', 25, 'Male'),
-> (102, 'Sita Thapa', 17, 'Female'),
-> (103, 'Rahul Khadka', 35, 'Male'),
-> (104, 'Anjali Koirala', 42, 'Female'),
-> (105, 'Bikash Gurung', 19, 'Male'),
-> (106, 'Kabita Lama', 29, 'Female'),
-> (107, 'Prakash Bhandari', 60, 'Male'),
-> (10, 'Prakash Bhandari', 60, 'Male'),

Common questions

Powered by AI

SQL was used to manage patient appointments by creating tables with appropriate keys, such as 'Appointment' with patient and doctor references ensuring data association with foreign keys. Data consistency was ensured by inserting data using explicit constraints and referencing existing entities in Patient and Doctor tables, thus maintaining link integrity and preventing insert anomalies like duplicate or incorrect records. Records in the Appointment table demonstrate scheduled statuses linked to existing entities, ensuring comprehensive data management .

The process of adding and removing a column involves altering the table structure. When a column is added, the structure of the table changes to include the new column with its specified properties. In the MySQL monitor example, the column 'SURNAME' was first added to the 'patient' table with a varchar data type, and then it was removed, reverting the table structure to its original form without the 'SURNAME' column. The operations themselves were executed using 'ALTER TABLE' commands, demonstrating dynamic schema modification capabilities of MySQL .

Challenges with foreign key constraints occur when the referenced and referencing columns have incompatible types, as seen when an error was returned due to an incompatibility between 'P_ID' in Patient and 'P_ID' in Consults. Initially, 'P_ID' was a varchar, causing a type mismatch when a foreign key was attempted. The resolution involved modifying the 'P_ID' column in the Patient table to an integer, aligning data types across tables, which then allowed the constraint to be created successfully .

A CROSS JOIN produces a Cartesian product of the two tables, where each row from the first table is combined with all rows of the second table. This is useful when listing all possible combinations of records from two tables, like assigning each patient to all room types for evaluation purposes. In practice, CROSS JOINS should be used cautiously with large tables due to the potential increase in computational complexity and result set size, which can affect performance and limit practical application .

Common errors include datatype mismatches for foreign keys or absent references leading to constraint failures, exemplified by initial creation errors in the Consults table due to column type discrepancies. Troubleshooting involves verifying datatype compatibility and ensuring referenced entities exist prior to constraint application. Corrective actions taken in the example such as modifying column types showcase practical debugging approaches, reinforcing table interlinks and constraints while maintaining database integrity and operational reliability .

UNION and JOINS are SQL operations that facilitate complex queries across multiple tables. UNION merges the results of two SELECT queries while ensuring uniqueness in entries, exemplified by merging names from both Patient and Doctor tables. JOINS, such as INNER JOIN, CROSS JOIN, and LEFT JOIN, link tables based on a related column and control how to merge the records from each. For example, an INNER JOIN fetches records with matching columns from both tables, showing how patients and their consulting doctors are related. These operations are vital for managing and retrieving related data from interconnected tables efficiently .

Creating the 'Appointment' table involved defining columns for appointment IDs, patient and doctor associations, appointment date, and status, with 'a_id' set as a primary key. It established relationships with the Patient and Doctor tables via foreign keys on 'p_id' and 'd_id'. This design supports functionality such as tracking appointment status and ensuring that all appointments are linked to existing patients and doctors, allowing effective management and query operations for further scheduling and status updates .

Type constraints in SQL Table definitions enforce datatype conformance across related columns, ensuring that all entries fit predetermined formats and structures. This was crucial when aligning 'P_ID' from Patient and Consults, as the error highlighted the need for type integrity. Correcting the inconsistency between varchar and integer types enabled the foreign key constraint, solidifying the integrity of relational references. These constraints ensure precise, structured data input and mitigate errors arising from datatype faults .

Primary keys uniquely identify records within a table, while foreign keys establish relational links between tables, enforcing referential integrity. In the 'doctor' and 'consults' tables, 'D_ID' is a primary key in 'doctor' and a foreign key in 'consults', ensuring all consulted doctors exist in the doctor table. This linkage maintains relational integrity across the database by preventing orphans and invalid data references, central to maintaining database consistency and integrity .

Table alteration can directly impact data integrity by changing the schema, which if not handled cautiously, can lead to loss of data or corruption. In the examples, alterations, such as modifying column data types or dropping columns, could potentially disrupt existing data relationships and constraints. Successful alterations as shown were executed without impacting the integrity through careful management and correct application of SQL commands to ensure data consistency was maintained at each step .

You might also like