Section A – DDL Commands
CREATE TABLE. Patients (
PatientID INT PRIMARY KEY,
PatientName VARCHAR(100) NOT NULL,
Gender VARCHAR(10),
Age INT CHECK (Age > 0),
Phone VARCHAR(15),
Address VARCHAR(255)
);
INSERT INTO Patients (PatientID, PatientName, Gender, Age, Phone, Address) VALUES
(1, 'Rahul Sharma', 'Male', 52, '9876543210', '123 MG Road, Delhi'),
(2, 'Priya Patel', 'Female', 28, '8765432109', '456 Park Street, Mumbai'),
(3, 'Amit Verma', 'Male', 65, '7654321098', '789 Link Road, Bangalore');
Section B – DML Commands
CREATE TABLE Doctors (
DoctorID INT PRIMARY KEY,
DoctorName VARCHAR(100) NOT NULL,
Specialization VARCHAR(100),
Experience INT CHECK (Experience >= 0),
Salary DECIMAL(10, 2) CHECK (Salary >= 0)
);
INSERT INTO Doctors (DoctorID, DoctorName, Specialization, Experience, Salary) VALUES
(101, 'Dr. A.K. Mishra', 'Cardiology', 15, 95000.00),
(102, 'Dr. Sneha Reddy', 'Pediatrics', 8, 70000.00),
(103, 'Dr. Rajesh Kumar', 'Neurology', 12, 85000.00);
Update the salary of a doctor -
UPDATE Doctors
SET Salary = 78000.00
WHERE DoctorID = 102;
Delete a patient record using the PatientID
DELETE FROM Patients
WHERE PatientID = 3;
Display only the doctor names and their specializations
SELECT DoctorName, Specialization FROM Doctors;
Display patients whose age is greater than 50
SELECT * FROM Patients
WHERE Age > 50;
Display doctors whose salary is greater than ₹75,000
SELECT * FROM Doctors
WHERE Salary > 75000;
Display patient names in ascending alphabetical order
SELECT PatientName FROM Patients ORDER BY PatientName ASC;
Section D – Joins & Aggregate Functions
CREATE TABLE Appointments (
AppointmentID INT PRIMARY KEY,
PatientID INT,
DoctorID INT,
AppointmentDate DATE,
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);
Display patient names along with their appointment dates
SELECT [Link], [Link]
FROM Patients p
INNER JOIN Appointments a ON [Link] = [Link];
Display doctor names along with the number of appointments handled by each doctor
SELECT [Link], COUNT([Link]) AS TotalAppointments
FROM Doctors d
LEFT JOIN Appointments a ON [Link] = [Link]
GROUP BY [Link], [Link];
Find the total number of patients
SELECT COUNT(*) AS TotalPatients FROM Patients;
Find the average salary of doctors
SELECT AVG(Salary) AS AverageSalary FROM Doctors;
Section E – Advanced SQL
Create a VIEW named PatientDetails to display patient names and phone numbers
CREATE VIEW PatientDetails AS
SELECT PatientName, Phone
FROM Patients;
Find the doctor with the highest salary
SELECT * FROM Doctors
WHERE Salary = (SELECT MAX(Salary) FROM Doctors);
Display patients who have not booked any appointment
SELECT * FROM Patients
WHERE PatientID NOT IN (SELECT DISTINCT PatientID FROM Appointments WHERE PatientID IS NOT NULL);
Count the number of doctors in each specialization
SELECT Specialization, COUNT(*) AS NumberOfDoctors FROM Doctors
GROUP BY Specialization;
Create an INDEX on the PatientName column of the Patients table
CREATE INDEX idx_patient_name
ON Patients(PatientName);