AIM :- SQL Queries:

a. Design and Develop SQL DDL statements which demonstrate the use of
SQL objects such
as Table, View, Index, Sequence, Synonym, different constraints etc.
b. Write at least 10 SQL queries on the suitable database application using
SQL DML
Statements.

Problem Statement:
Design and develop a real-time Hospital Appointment Management System
that allows patients to register, book appointments with doctors, view
appointment schedules, and receive notifications. The system should allow
hospitals to manage doctor profiles,assign doctors to departments, and
maintain appointment records. The goal is to streamline the appointment
process, reduce manual errors, and improve efficiency in hospital
operations.

CODE :-

mysql> CREATE USER 'Atharv'@'localhost'IDENTIFIED BY 'password';

Query OK, 0 rows affected (0.04 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'Atharv'@'localhost' WITH GRANT OPTION;

Query OK, 0 rows affected (0.03 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DATABASE hospital_db;

ERROR 1007 (HY000): Can't create database 'hospital_db'; database exists
mysql> USE hospital_db;CREATE TABLE Hospital (
Database changed
-> Hospital_ID INT PRIMARY KEY,
-> Hospital_name VARCHAR(100) NOT NULL,
-> Contact_number VARCHAR(15),
-> Hospital_address VARCHAR(255)
-> );
Query OK, 0 rows affected (0.16 sec)
mysql>
mysql> CREATE TABLE Doctor (
-> Doctor_ID INT PRIMARY KEY,
-> Doctor_name VARCHAR(100) NOT NULL,
-> Specialty VARCHAR(50),
-> Contact VARCHAR(15),
-> Hospital_ID INT,
-> FOREIGN KEY (Hospital_ID) REFERENCES Hospital(Hospital_ID)
-> );
Query OK, 0 rows affected (0.12 sec)

mysql>
mysql> CREATE TABLE Patient (
-> Patient_ID INT PRIMARY KEY,
-> Patient_name VARCHAR(100) NOT NULL,
-> Patient_age INT CHECK (Patient_age > 0),
-> Patient_gender VARCHAR(10) CHECK (Patient_gender IN ('Male', 'Female',
'Other'))
-> );
Query OK, 0 rows affected (0.25 sec)

mysql>
mysql> CREATE TABLE Appointment (
-> Appointment_ID INT PRIMARY KEY,
-> Status VARCHAR(50),
-> Appointment_Date DATE,
-> Time TIME,
-> Patient_ID INT,
-> Doctor_ID INT,
-> Hospital_ID INT,
-> FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
-> FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID),
-> FOREIGN KEY (Hospital_ID) REFERENCES Hospital(Hospital_ID)
-> );
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE VIEW DoctorAppointments AS

-> SELECT a.Appointment_ID, a.Appointment_Date, d.Doctor_name,
p.Patient_name
-> FROM Appointment a
-> JOIN Doctor d ON a.Doctor_ID = d.Doctor_ID
 -> JOIN Patient p ON a.Patient_ID = p.Patient_ID;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE INDEX idx_specialty ON Doctor(Specialty);

Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE Bill (

-> Bill_ID INT AUTO_INCREMENT PRIMARY KEY,
-> Appointment_ID INT,
-> Amount DECIMAL(10, 2),
-> FOREIGN KEY (Appointment_ID) REFERENCES
Appointment(Appointment_ID)
-> );
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO Hospital VALUES (1, 'City Hospital', '9999999999', 'Mumbai');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO Doctor VALUES (101, 'Dr. Ravi', 'Cardiology', '8888888888', 1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Patient VALUES (201, 'Aman Khan', 30, 'Male');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Appointment VALUES (301, 'Scheduled', '2025-07-15', '[Link]',

201, 101, 1);
Query OK, 1 row affected (0.02 sec)

mysql> -- 1. Select all patients

mysql> SELECT * FROM Patient;
+------------+--------------+-------------+----------------+
| Patient_ID | Patient_name | Patient_age | Patient_gender |
+------------+--------------+-------------+----------------+
| 201 | Aman Khan | 30 | Male |
+------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

mysql>
mysql> -- 2. Get all appointments for a doctor
mysql> SELECT * FROM Appointment WHERE Doctor_ID = 101;
+----------------+-----------+------------------+----------+------------+-----------+-------------+
| Appointment_ID | Status | Appointment_Date | Time | Patient_ID | Doctor_ID |
Hospital_ID |
+----------------+-----------+------------------+----------+------------+-----------+-------------+
| 301 | Scheduled | 2025-07-15 | [Link] | 201 | 101 | 1|
+----------------+-----------+------------------+----------+------------+-----------+-------------+
1 row in set (0.00 sec)

mysql>
mysql> -- 3. Count total appointments
mysql> SELECT COUNT(*) FROM Appointment;
+----------+
| COUNT(*) |
+----------+
| 1|
+----------+
1 row in set (0.00 sec)

mysql>
mysql> -- 4. Update patient age
mysql> UPDATE Patient SET Patient_age = 31 WHERE Patient_ID = 201;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
mysql> -- 5. Delete a doctor
mysql> DELETE FROM Doctor WHERE Doctor_ID = 102;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> -- 6. Use LIKE
mysql> SELECT * FROM Doctor WHERE Doctor_name LIKE 'Dr%';
+-----------+-------------+------------+------------+-------------+
| Doctor_ID | Doctor_name | Specialty | Contact | Hospital_ID |
+-----------+-------------+------------+------------+-------------+
| 101 | Dr. Ravi | Cardiology | 8888888888 | 1|
+-----------+-------------+------------+------------+-------------+
1 row in set (0.00 sec)

mysql>
mysql> -- 7. UPPER + Arithmetic Function
mysql> SELECT UPPER(Patient_name), Patient_age + 5 FROM Patient;
+---------------------+-----------------+
| UPPER(Patient_name) | Patient_age + 5 |
+---------------------+-----------------+
| AMAN KHAN | 36 |
+---------------------+-----------------+
1 row in set (0.01 sec)

mysql>
mysql> -- 8. GROUP BY + COUNT
mysql> SELECT Doctor_ID, COUNT(*) FROM Appointment GROUP BY Doctor_ID;
+-----------+----------+
| Doctor_ID | COUNT(*) |
+-----------+----------+
| 101 | 1|
+-----------+----------+
1 row in set (0.00 sec)

mysql>
mysql> -- 9. Set Operation (MySQL workaround using LEFT JOIN)
mysql> SELECT p.Patient_ID
-> FROM Patient p
-> LEFT JOIN Appointment a ON p.Patient_ID = a.Patient_ID
-> WHERE a.Patient_ID IS NULL;
Empty set (0.01 sec)

mysql>
mysql> -- 10. View data from your VIEW
mysql> SELECT * FROM DoctorAppointments;
+----------------+------------------+-------------+--------------+
| Appointment_ID | Appointment_Date | Doctor_name | Patient_name |
+----------------+------------------+-------------+--------------+
| 301 | 2025-07-15 | Dr. Ravi | Aman Khan |
+----------------+------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM DoctorAppointments;