Skip to content

Instantly share code, notes, and snippets.

@Penguin5681

Penguin5681/Project.sql

Created November 24, 2025 14:03
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Penguin5681/1a547d6d53e08d431f992321ac063fed to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Penguin5681/1a547d6d53e08d431f992321ac063fed to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Project.sql
CREATE TABLE Departments (
DeptID INT PRIMARY KEY IDENTITY(1,1),
DeptName VARCHAR(100) NOT NULL,
HeadDoctorID INT NULL,
Location VARCHAR(100)
);
CREATE TABLE Patients (
PatientID INT PRIMARY KEY IDENTITY(1,1),
FullName VARCHAR(100) NOT NULL,
DOB VARCHAR(20),
Gender VARCHAR(10),
ContactNumber VARCHAR(20),
Email VARCHAR(100),
Address VARCHAR(255)
);
CREATE TABLE Doctors (
DoctorID INT PRIMARY KEY IDENTITY(1,1),
FullName VARCHAR(100) NOT NULL,
DeptID INT FOREIGN KEY REFERENCES Departments(DeptID),
Specialization VARCHAR(100),
JoiningDate VARCHAR(20),
Salary FLOAT
);
CREATE TABLE Treatments (
TreatmentID INT PRIMARY KEY IDENTITY(1,1),
TreatmentName VARCHAR(100),
Cost FLOAT,
Description VARCHAR(255)
);
CREATE TABLE Appointments (
ApptID INT PRIMARY KEY IDENTITY(1,1),
PatientID INT FOREIGN KEY REFERENCES Patients(PatientID),
DoctorID INT FOREIGN KEY REFERENCES Doctors(DoctorID),
ApptDate VARCHAR(20),
Status VARCHAR(20) CHECK (Status IN ('Scheduled', 'Completed', 'Cancelled')),
Reason VARCHAR(255)
);
CREATE TABLE MedicalRecords (
RecordID INT PRIMARY KEY IDENTITY(1,1),
ApptID INT FOREIGN KEY REFERENCES Appointments(ApptID),
Diagnosis VARCHAR(MAX),
Prescription VARCHAR(MAX),
RecordDate VARCHAR(20)
);
GO
CREATE TABLE Billing (
BillID INT PRIMARY KEY IDENTITY(1,1),
PatientID INT FOREIGN KEY REFERENCES Patients(PatientID),
Amount FLOAT,
PaymentStatus VARCHAR(20) CHECK (PaymentStatus IN ('Paid', 'Pending', 'Failed')),
TransactionDate VARCHAR(20)
);
GO
INSERT INTO Departments (DeptName, Location) VALUES
('Cardiology', 'Building A, Floor 1'),
('Neurology', 'Building A, Floor 2'),
('Pediatrics', 'Building B, Floor 1'),
('Orthopedics', 'Building B, Floor 2'),
('Oncology', 'Building C, Wing East'),
('Dermatology', 'Building C, Wing West'),
('General Medicine', 'Main Hall'),
('Radiology', 'Basement Level 1'),
('Emergency', 'Ground Floor'),
('Psychiatry', 'Building D, Quiet Zone');
INSERT INTO Patients (FullName, DOB, Gender, ContactNumber, Email, Address) VALUES
('John Doe', '1985-04-12', 'Male', '555-0101', '[email protected]', '123 Maple St, New York'),
('Jane Smith', '1990-08-23', 'Female', '555-0102', '[email protected]', '456 Oak Ave, New York'),
('Alice Johnson', '1978-11-30', 'Female', '555-0103', '[email protected]', '789 Pine Ln, Brooklyn'),
('Bob Brown', '1965-02-15', 'Male', '555-0104', '[email protected]', '321 Cedar Blvd, Queens'),
('Charlie Davis', '2000-05-20', 'Male', '555-0105', '[email protected]', '654 Elm St, Bronx'),
('Diana Evans', '1995-12-10', 'Female', '555-0106', '[email protected]', '987 Birch Rd, Manhattan'),
('Evan Wright', '1982-03-25', 'Male', '555-0107', '[email protected]', '159 Spruce St, New Jersey'),
('Fiona Scott', '1988-07-14', 'Female', '555-0108', '[email protected]', '753 Willow Way, Brooklyn'),
('George Baker', '1950-09-05', 'Male', '555-0109', '[email protected]', '852 Aspen Cir, Queens'),
('Hannah Clark', '1992-01-18', 'Female', '555-0110', '[email protected]', '951 Poplar Pl, New York'),
('Ian Lewis', '1975-06-22', 'Male', '555-0111', '[email protected]', '357 Fir Dr, Bronx'),
('Julia Walker', '1980-10-08', 'Female', '555-0112', '[email protected]', '258 Cypress Ct, Manhattan'),
('Kevin Hall', '1998-04-30', 'Male', '555-0113', '[email protected]', '147 Redwood Rd, New Jersey'),
('Laura Allen', '1960-11-12', 'Female', '555-0114', '[email protected]', '369 Sycamore St, Brooklyn'),
('Mike Young', '1985-08-19', 'Male', '555-0115', '[email protected]', '741 Teak Ln, Queens');
INSERT INTO Doctors (FullName, DeptID, Specialization, JoiningDate, Salary) VALUES
('Dr. Gregory House', 7, 'Diagnostician', '2010-05-15', 150000.00),
('Dr. Meredith Grey', 7, 'General Surgery', '2012-07-01', 120000.00),
('Dr. Derek Shepherd', 2, 'Neurosurgery', '2008-03-10', 250000.00),
('Dr. Stephen Strange', 2, 'Neurosurgery', '2015-11-20', 240000.00),
('Dr. Shaun Murphy', 7, 'Surgical Resident', '2020-01-15', 90000.00),
('Dr. John Dorian', 7, 'Internal Medicine', '2018-06-30', 95000.00),
('Dr. Miranda Bailey', 7, 'General Surgery', '2005-09-12', 180000.00),
('Dr. Cristina Yang', 1, 'Cardiothoracic Surgery', '2011-04-25', 220000.00),
('Dr. Alex Karev', 3, 'Pediatric Surgery', '2013-08-19', 160000.00),
('Dr. Addison Montgomery', 6, 'Neonatal Surgery', '2009-02-14', 210000.00),
('Dr. Leonard McCoy', 7, 'Chief Medical Officer', '2000-01-01', 130000.00),
('Dr. Beverly Crusher', 7, 'General Medicine', '2002-05-20', 135000.00),
('Dr. Julius Hibbert', 3, 'Family Medicine', '1995-12-01', 170000.00),
('Dr. Nick Riviera', 6, 'Plastic Surgery', '2019-10-31', 80000.00),
('Dr. Zoidberg', 9, 'Emergency Medicine', '3000-01-01', 45000.00);
INSERT INTO Treatments (TreatmentName, Cost, Description) VALUES
('General Consultation', 50.00, 'Standard checkup with a physician'),
('MRI Scan', 500.00, 'Full body magnetic resonance imaging'),
('X-Ray', 100.00, 'Radiographic imaging of bones'),
('Blood Test', 30.00, 'Complete blood count (CBC)'),
('Heart Surgery', 5000.00, 'Bypass surgery'),
('Physical Therapy', 80.00, '1-hour rehabilitation session'),
('Dermatology Screening', 75.00, 'Skin cancer screening'),
('Chemotherapy', 1200.00, 'Single cycle cancer treatment'),
('Vaccination', 20.00, 'Flu shot or standard vaccine'),
('Dental Cleaning', 90.00, 'Standard dental hygiene'),
('Appendectomy', 1500.00, 'Surgical removal of appendix'),
('Cataract Surgery', 2000.00, 'Eye lens replacement');
INSERT INTO Appointments (PatientID, DoctorID, ApptDate, Status, Reason) VALUES
(1, 1, '2023-10-01 09:00', 'Completed', 'Severe leg pain'),
(2, 2, '2023-10-01 10:00', 'Completed', 'Annual Checkup'),
(3, 3, '2023-10-02 11:30', 'Completed', 'Migraine consultation'),
(4, 4, '2023-10-03 14:00', 'Cancelled', 'Follow up'),
(5, 5, '2023-10-05 09:00', 'Completed', 'Stomach ache'),
(1, 8, '2023-11-15 10:00', 'Scheduled', 'Heart rhythm check'),
(6, 9, '2023-10-06 15:00', 'Completed', 'Child fever'),
(7, 10, '2023-10-07 11:00', 'Completed', 'Skin rash'),
(8, 1, '2023-12-01 09:30', 'Scheduled', 'Limping'),
(9, 11, '2023-10-08 13:00', 'Completed', 'General fatigue'),
(10, 12, '2023-10-09 10:30', 'Cancelled', 'Cold symptoms'),
(11, 6, '2023-10-10 16:00', 'Completed', 'Hypertension'),
(12, 7, '2023-10-11 09:00', 'Completed', 'Appendicitis check'),
(13, 13, '2024-01-05 10:00', 'Scheduled', 'Routine vaccination'),
(14, 14, '2023-10-12 14:30', 'Completed', 'Cosmetic consultation');
INSERT INTO MedicalRecords (ApptID, Diagnosis, Prescription, RecordDate) VALUES
(1, 'Muscle Strain', 'Ibuprofen 400mg', '2023-10-01'),
(2, 'Healthy', 'Vitamins', '2023-10-01'),
(3, 'Chronic Migraine', 'Sumatriptan 50mg', '2023-10-02'),
(5, 'Gastritis', 'Omeprazole 20mg', '2023-10-05'),
(7, 'Viral Infection', 'Rest and Fluids', '2023-10-07'),
(8, 'Eczema', 'Hydrocortisone Cream', '2023-10-07'),
(10, 'Anemia', 'Iron Supplements', '2023-10-09'),
(12, 'High Blood Pressure', 'Lisinopril 10mg', '2023-10-11'),
(13, 'Acute Appendicitis', 'Referral for Surgery', '2024-01-05'),
(15, 'Minor Laceration', 'Topical Antibiotic', '2023-10-12'),
(1, 'Follow up - Healing', 'Continue Physio', '2023-10-15'),
(2, 'Healthy - Follow up', 'None', '2023-12-01');
INSERT INTO Billing (PatientID, Amount, PaymentStatus, TransactionDate) VALUES
(1, 150.00, 'Paid', '2023-10-01 10:00'),
(2, 50.00, 'Paid', '2023-10-01 11:00'),
(3, 250.00, 'Pending', '2023-10-02 12:30'),
(5, 80.00, 'Paid', '2023-10-05 10:00'),
(6, 45.00, 'Paid', '2023-10-06 16:00'),
(7, 75.00, 'Failed', '2023-10-07 12:00'),
(9, 30.00, 'Paid', '2023-10-08 14:00'),
(11, 60.00, 'Pending', '2023-10-10 17:00'),
(12, 1500.00, 'Pending', '2023-10-11 10:00'),
(14, 200.00, 'Paid', '2023-10-12 15:30'),
(1, 500.00, 'Paid', '2023-10-15 09:00'),
(4, 100.00, 'Failed', '2023-10-03 14:00'),
(8, 50.00, 'Paid', '2023-12-01 10:00'),
(10, 50.00, 'Paid', '2023-10-09 10:30'),
(13, 20.00, 'Pending', '2024-01-05 11:00');
-- =============================================
-- EXISTING QUERIES (Joins & Basics)
-- =============================================
-- Character Function: Uppercase name and extract first 7 chars of email
SELECT UPPER(FullName), SUBSTRING(Email, 1, 7)
FROM Patients
-- Aggregate Functions: Count bills, Sum total amount, Average amount
SELECT COUNT(BillID), SUM(Amount), AVG(Amount)
FROM Billing
-- Inner Join: Get Doctor Name and their Department Name
SELECT Doctors.FullName, Departments.DeptName
FROM Doctors
INNER JOIN Departments ON Doctors.DeptID = Departments.DeptID
-- Left Join: Get All Patients and their appointment dates (if any)
SELECT Patients.FullName, Appointments.ApptDate
FROM Patients
LEFT JOIN Appointments ON Patients.PatientID = Appointments.PatientID
-- Right Join: Get All Medical Records and associated Appointment Dates
SELECT Appointments.ApptDate, MedicalRecords.Diagnosis
FROM Appointments
RIGHT JOIN MedicalRecords ON Appointments.ApptID = MedicalRecords.ApptID
-- Full Outer Join: Get all Patients and all Bills, matching where possible
SELECT Patients.FullName, Billing.Amount
FROM Patients
FULL OUTER JOIN Billing ON Patients.PatientID = Billing.PatientID
-- Transaction: Update a bill status safely
BEGIN TRANSACTION
UPDATE Billing SET PaymentStatus = 'Paid' WHERE BillID = 3
COMMIT TRANSACTION
-- Transaction: Delete an appointment safely
BEGIN TRANSACTION
DELETE FROM Appointments WHERE ApptID = 5
ROLLBACK TRANSACTION
-- =============================================
-- NEW QUERIES (Pattern Matching, Aggregates, Char Functions)
-- =============================================
-- PATTERN MATCHING (LIKE Operator)
-- Find all Doctors whose name starts with 'Dr. S'
SELECT * FROM Doctors WHERE FullName LIKE 'Dr. S%'
-- Find all Patients who have a gmail account
SELECT * FROM Patients WHERE Email LIKE '%gmail.com'
-- Find all Patients who live in 'New York' (Anywhere in address)
SELECT * FROM Patients WHERE Address LIKE '%New York%'
-- Find Departments located in 'Building C'
SELECT * FROM Departments WHERE Location LIKE 'Building C%'
-- AGGREGATE FUNCTIONS
-- Find the highest salary paid to a doctor
SELECT MAX(Salary) FROM Doctors
-- Find the minimum cost of a treatment in the catalog
SELECT MIN(Cost) FROM Treatments
-- Count how many Female patients are registered
SELECT COUNT(PatientID) FROM Patients WHERE Gender = 'Female'
-- Calculate the total potential cost of all treatments combined
SELECT SUM(Cost) FROM Treatments
-- CHARACTER FUNCTIONS
-- Get the length of every patient's full name
SELECT FullName, LEN(FullName) FROM Patients
-- Convert Department names to Lowercase
SELECT LOWER(DeptName) FROM Departments
-- Display just the first 3 letters of the Appointment Status
SELECT Status, LEFT(Status, 3) FROM Appointments
-- Reverse the Doctor's name (useful for demonstrating string manipulation)
SELECT FullName, REVERSE(FullName) FROM Doctors
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment