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,'Dr.Arun','cardiology'),
-> (202,'Dr.Ritu','neurology'),
-> (203,'Dr.Mano','pediatrics'),
-> (204,'Dr.kabir','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 |
+----------+
| Dr.Arun |
| Dr.Ritu |
| Dr.Mano |
| Dr.kabir |
+----------+
4 rows in set (0.022 sec)

mysql> SELECT Name FROM Patient

-> UNION
-> SELECT Name FROM Doctor;
+----------+
| Name |
+----------+
| Aditi |
| Asmita |
| Raj |
| sneha |
| aryan |
| Dr.Arun |
| Dr.Ritu |
| Dr.Mano |
| Dr.kabir |
+----------+
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 Patient.Name, Rooms.Type

-> 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 Patient.Name AS Patient_Name, Doctor.Name AS Doctor_Name, Doctor.Dept

-> 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 | Dr.Arun | cardiology |
| Raj | Dr.Arun | cardiology |
| Asmita | Dr.Ritu | neurology |
| Asmita | Dr.Mano | pediatrics |
| sneha | Dr.Mano | pediatrics |
+--------------+-------------+------------+
5 rows in set (0.031 sec)

mysql> SELECT Patient.Name AS Patient_Name, Doctor.Name AS Doctor_Name, Doctor.Dept

-> FROM Patient
-> INNER JOIN Consults ON Patient.P_ID = Consults.P_ID
-> INNER JOIN Doctor ON Consults.D_ID = Doctor.D_ID
-> WHERE Doctor.Dept = 'Cardiology';
+--------------+-------------+------------+
| Patient_Name | Doctor_Name | Dept |
+--------------+-------------+------------+
| Aditi | Dr.Arun | cardiology |
| Raj | Dr.Arun | 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 Patient.Name AS Patient_Name, Doctor.Name AS Doctor_Name, Doctor.Dept

-> 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 | Dr.Arun | cardiology |
| Asmita | Dr.Ritu | neurology |
| Asmita | Dr.Mano | pediatrics |
| Raj | Dr.Arun | cardiology |
| sneha | Dr.Mano | pediatrics |
| aryan | NULL | NULL |
+--------------+-------------+------------+
6 rows in set (0.027 sec)

mysql> SELECT Doctor.Name AS Doctor_Name, Patient.Name 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 |
+-------------+--------------+
| Dr.Arun | Aditi |
| Dr.Arun | Raj |
| Dr.Ritu | Asmita |
| Dr.Mano | Asmita |
| Dr.Mano | sneha |
| Dr.kabir | NULL |
+-------------+--------------+
6 rows in set (0.030 sec)

mysql> SELECT Patient.Name AS Patient_Name, Doctor.Name 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 Patient.Name AS Patient_Name, Doctor.Name 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 | Dr.Arun |
| Asmita | Dr.Ritu |
| Asmita | Dr.Mano |
| Raj | Dr.Arun |
| sneha | Dr.Mano |
| aryan | NULL |
| NULL | Dr.kabir |
+--------------+-------------+
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 Doctor.name, COUNT(Appointment.a_id) AS total_appointments

-> FROM Doctor
-> LEFT JOIN Appointment ON Doctor.d_id = Appointment.d_id
-> GROUP BY Doctor.name;
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'),