-- Create the Patient table with TEXT columns changed to VARCHAR(100)
CREATE TABLE Patient (
    Patient_ID INT PRIMARY KEY,
    First_name VARCHAR(30),
    Last_name VARCHAR(30),
    DOB DATE,
    Gender VARCHAR(8),
    Phone_number VARCHAR(30),
    Address VARCHAR(200),
    Insurance_info VARCHAR(100),
    Diagnosis_details VARCHAR(100),
    Treatment_plans VARCHAR(100),
    Prescriptions VARCHAR(100),
    Test_results VARCHAR(100),
    Medical_history VARCHAR(100)
);

-- Create the Medical_Staff table
CREATE TABLE Medical_Staff (
    Staff_ID INT PRIMARY KEY,
    Role VARCHAR(50),
    First_name VARCHAR(50),
    Last_name VARCHAR(50),
    Contact_details VARCHAR(100),
    Shift_timing VARCHAR(50),
    Salary DECIMAL(10, 2)
);

-- Create the Doctor table
CREATE TABLE Doctor (
    Doctor_ID INT PRIMARY KEY,
    Staff_ID INT,
    First_name VARCHAR(50),
    Last_name VARCHAR(50),
    Specialization VARCHAR(50),
    Contact_info VARCHAR(100),
    License_number VARCHAR(20),
    Office_hours VARCHAR(100),
    Email VARCHAR(100),
    FOREIGN KEY (Staff_ID) REFERENCES Medical_Staff(Staff_ID)

);

-- Create the Patient_Doctor table (junction table)
CREATE TABLE Patient_Doctor (
    Patient_ID INT,
    Doctor_ID INT,
    PRIMARY KEY (Patient_ID, Doctor_ID),
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

-- Create the Appointment table
CREATE TABLE Appointment (
    Appointment_ID INT PRIMARY KEY,
    Patient_ID INT,
    Doctor_ID INT,
    Appointment_date_time DATETIME,
    Visit_reason VARCHAR(100),
    Appointment_status VARCHAR(20),
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

-- Create the Medical_Record table
CREATE TABLE Medical_Record (
    Record_ID INT PRIMARY KEY,
    Patient_ID INT,
    Doctor_Medical_Record INT,
    Doctor_ID INT,
    FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID),
    FOREIGN KEY (Doctor_Medical_Record) REFERENCES Medical_Record(Record_ID),
    FOREIGN KEY (Doctor_ID) REFERENCES Doctor(Doctor_ID)
);

-- Create the Visit_Dates table
CREATE TABLE Visit_Dates (
    Record_ID INT,
    Date_of_each_visit DATE,
    PRIMARY KEY (Record_ID, Date_of_each_visit),
    FOREIGN KEY (Record_ID) REFERENCES Medical_Record(Record_ID)
);



-- Insert data into the Patient table
INSERT INTO Patient (Patient_ID, First_name, Last_name, DOB, Gender, Phone_number, Address, Insurance_info, Diagnosis_details, Treatment_plans, Prescriptions, Test_results, Medical_history)
VALUES
 (1, 'Amelia', 'Smith', '1981-06-10', 'Female', '555111222', 'Oak Street', 'MediCare Plus', 'Allergies', 'Antihistamine', 'Prescription 777', 'Test Allergy: Positive', 'Medical history notes for Amelia Smith'),
    (2, 'Ethan', 'Johnson', '1993-03-18', 'Male', '555222333', 'Maple Street', 'HealthGuard Insurance', 'Migraine', 'Painkillers', 'Prescription 888', 'Test Migraine: Negative', 'Medical history notes for Ethan Johnson'),
    (3, 'Olivia', 'Davis', '1985-09-22', 'Female', '555333444', 'Cedar Street', 'MediLife Insurance', 'Digestive Issues', 'Probiotics', 'Prescription 999', 'Test Digestion: Positive', 'Medical history notes for Olivia Davis'),
    (4, 'Noah', 'Taylor', '1977-12-05', 'Male', '555444555', 'Pine Street', 'SecureHealth Inc', 'High Blood Pressure', 'Beta Blockers', 'Prescription 1010', 'Test Blood Pressure: Negative', 'Medical history notes for Noah Taylor'),
    (5, 'Ava', 'Brown', '1998-08-15', 'Female', '555555666', 'Spruce Street', 'HealthPlus Insurance', 'Anxiety', 'Counseling', 'Prescription 1111', 'Test Anxiety: Positive', 'Medical history notes for Ava Brown');


-- Insert data into the Medical_Staff table
INSERT INTO Medical_Staff (Staff_ID, Role, First_name, Last_name, Contact_details, Shift_timing, Salary)
VALUES
  (1, 'Receptionist', 'Alice', 'Smith', '555-1234', 'Day Shift', 45000.00),
  (2, 'Clinic Manager', 'Bob', 'Johnson', '555-5678', 'Morning Shift', 40000.00),
  (3, 'Doctor Contact Point', 'Charlie', 'Davis', '555-9101', 'Evening Shift', 50000.00),
  (4, 'Lab Technician', 'David', 'Wilson', '555-1122', 'Night Shift', 48000.00),
  (5, 'Janitor', 'Eva', 'Miller', '555-3344', 'Custodial Shift', 35000.00);

-- Insert data into the Doctor table
INSERT INTO Doctor (Doctor_ID, Staff_ID, First_name, Last_name, Specialization, Contact_info, License_number, Office_hours, Email)
VALUES
  (1, 101, 'Dr. Sarah', 'Johnson', 'Cardiologist', '555-1234', 'MD56789', 'Mon-Fri 8:00 AM - 4:00 PM', '[email protected]'),
  (2, 102, 'Dr. Michael', 'Williams', 'Neurologist', '555-5678', 'MD12345', 'Tue-Sat 9:00 AM - 5:00 PM', '[email protected]'),
  (3, 103, 'Dr. Emily', 'Brown', 'Psychiatrist', '555-9876', 'MD54321', 'Mon-Wed 10:00 AM - 6:00 PM', '[email protected]'),
  (4, 104, 'Dr. James', 'Smith', 'Orthopedic Surgeon', '555-4321', 'MD11111', 'Thu-Sat 8:00 AM - 4:00 PM', '[email protected]'),
  (5, 105, 'Dr. Maria', 'Garcia', 'Dermatologist', '555-8765', 'MD99999', 'Wed-Fri 9:00 AM - 5:00 PM', '[email protected]');


-- Insert data into the Patient_Doctor table (junction table)
INSERT INTO Patient_Doctor (Patient_ID, Doctor_ID)
VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 4),
    (5, 5);

-- Insert data into the Appointment table
INSERT INTO Appointment (Appointment_ID, Patient_ID, Doctor_ID, Appointment_date_time, Visit_reason, Appointment_status)
VALUES
   (1, 1, 1, '2023-9-11 10:00:00', 'Surgery', 'Scheduled'),
    (2, 2, 1, '2023-9-30 10:00:00', 'Surgery', 'Scheduled'),
    (3, 3, 1, '2023-5-30 10:00:00', 'Surgery', 'Scheduled'),
    (4, 4, 1, '2023-9-25 10:00:00', 'Surgery', 'Scheduled'),
    (5, 5, 1, '2023-9-1 10:00:00', 'Surgery', 'Scheduled'),

  (6, 5, 1, '2023-12-11 10:00:00', 'Surgery', 'Scheduled'),
  (7, 4, 1, '2023-2-15 10:00:00', 'Surgery', 'Scheduled'),
  (8, 4, 1, '2023-3-2 10:00:00', 'Surgery', 'Scheduled'),
  (9, 3, 1, '2023-4-3 10:00:00', 'Surgery', 'Scheduled'),
  (10, 2, 1, '2023-1-8 10:00:00', 'Surgery', 'Scheduled'),
  (11, 1, 1, '2023-7-7 10:00:00', 'Surgery', 'Scheduled'),

    (15, 5, 5, '2023-11-02 15:00:00', 'Cancer treatment follow-up', 'Scheduled');
    (12, 2, 2, '2023-11-02 14:30:00', 'Pediatric vaccination', 'Scheduled'),
    (14, 4, 4, '2023-11-02 09:45:00', 'Orthopedic consultation', 'Scheduled'),
    (13, 3, 3, '2023-11-02 11:15:00', 'Skin condition evaluation', 'Scheduled'),



-- Insert data into the Medical_Record table
INSERT INTO Medical_Record (Record_ID, Patient_ID, Doctor_Medical_Record, Doctor_ID)
VALUES
    (1, 1, NULL, 1),
    (2, 2, NULL, 2),
    (3, 3, NULL, 3),
    (4, 4, NULL, 4),
    (5, 5, NULL, 5);

-- Insert data into the Visit_Dates table
INSERT INTO Visit_Dates (Record_ID, Date_of_each_visit)
VALUES
    (1, '2022-10-30'),
    (2, '2022-11-02'),
    (3, '2022-11-05'),
    (4, '2022-11-07'),
    (5, '2022-11-10');


-- 1)	List of Doctors who have performed more than 10 surgeries.
SELECT D.First_name, D.Last_name, COUNT(A.Appointment_ID) AS Surgeries_Performed
FROM Doctor AS D
INNER JOIN Appointment AS A ON D.Doctor_ID = A.Doctor_ID
WHERE A.Visit_reason = 'Surgery'
GROUP BY D.Doctor_ID, D.First_name, D.Last_name
HAVING COUNT(A.Appointment_ID) > 10;

-- 2)	List of Patients admitted for surgery between 1/9/2023 and 31/9/2023.

SELECT DISTINCT P.First_name, P.Last_name
FROM Patient AS P
INNER JOIN Appointment AS A ON P.Patient_ID = A.Patient_ID
WHERE A.Visit_reason = 'Surgery'
  AND A.Appointment_date_time >= '2023-05-01 00:00:00'
  AND A.Appointment_date_time <= '2023-04-20 23:59:59';

-- 3)	List of all the Patients who have not paid their bills till today.

-- THERE IS NO BILL ATTRIBUTE!! But assume if there is a bill_paid attribute in the Patient table:
SELECT bill_paid FROM Patient WHERE bill_paid = false;

-- 4)	List of Doctors on leave in a particular month.

-- THERE IS NO LEAVE ATTRIBUTE!! But assume if there is a on_leave & leave_month attribute in the Doctor table:
SELECT on_leave FROM Doctor WHERE on_leave = true AND leave_month = "october";

-- 5)	List of Appointments with each Doctor with Patient name for a particular day.

SELECT D.First_name AS Doctor_FirstName, D.Last_name AS Doctor_LastName,
       P.First_name AS Patient_FirstName, P.Last_name AS Patient_LastName
FROM Appointment AS A
JOIN Doctor AS D ON A.Doctor_ID = D.Doctor_ID
JOIN Patient AS P ON A.Patient_ID = P.Patient_ID
WHERE DATE(A.Appointment_date_time) = '2026-11-02';

MySQL online editor

Write, Run & Share MySQL queries online using OneCompiler's MySQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for MySQL. Getting started with the OneCompiler's MySQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'MySQL' and start writing queries to learn and test online without worrying about tedious process of installation.

About MySQL

MySQL is a open-source, free and very popular relational database management system which is developed, distributed and supported by Oracle corporation.

Key Features:

  • Open-source relational database management systems.
  • Reliable, very fast and easy to use database server.
  • Works on client-server model.
  • Highly Secure and Scalable
  • High Performance
  • High productivity as it uses stored procedures, triggers, views to write a highly productive code.
  • Supports large databases efficiently.
  • Supports many operating systems like Linux*,CentOS*, Solaris*,Ubuntu*,Windows*, MacOS*,FreeBSD* and others.

Syntax help

Commands

1. CREATE

CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

Example

CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

2. ALTER

ALTER TABLE Table_name ADD column_name datatype;

Example

INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');

3. TRUNCATE

TRUNCATE table table_name;

4. DROP

DROP TABLE table_name;

5. RENAME

RENAME TABLE table_name1 to new_table_name1;

6. COMMENT

Single-Line Comments:

 --Line1;

Multi-Line comments:

   /* Line1,
   Line2 */

DML Commands

1. INSERT

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Note: Column names are optional.

Example

INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');

2. SELECT

SELECT column1, column2, ...
FROM table_name
[where condition];

Example

SELECT * FROM EMPLOYEE where dept ='sales';

3. UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';

4. DELETE

DELETE FROM table_name where condition;

Example

DELETE from EMPLOYEE where empId='0001';

Indexes

1. CREATE INDEX

  CREATE INDEX index_name on table_name(column_name);
  • To Create Unique index:
  CREATE UNIQUE INDEX index_name on table_name(column_name);

2. DROP INDEX

DROP INDEX index_name ON table_name;

Views

1. Create a View

Creating a View:
CREATE VIEW View_name AS 
Query;

2. How to call view

SELECT * FROM View_name;

3. Altering a View

ALTER View View_name AS 
Query;

4. Deleting a View

DROP VIEW View_name;

Triggers

1. Create a Trigger

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW [trigger_order] trigger_body
/* where
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } */

2. Drop a Trigger

DROP TRIGGER [IF EXISTS] trigger_name;

Stored Procedures

1. Create a Stored Procedure

CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;

2. How to call Stored procedure

CALL sp_name;

3. How to delete stored procedure

DROP PROCEDURE sp_name;

Joins

1. INNER JOIN

SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;

2. LEFT JOIN

SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;

3. RIGHT JOIN

SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;

4. CROSS JOIN

SELECT select_list from TABLE1 CROSS JOIN TABLE2;