SQL Tutorial: Hospital Management Project

Welcome to the SQL Tutorial! In this comprehensive guide, we'll be diving into the world of Structured Query Language (SQL) using a practical example - a Hospital Management System. By the end of this lesson, you'll have a solid understanding of SQL, ready to manage databases in real-world scenarios.

🎯 Objective: Learn SQL by creating and manipulating a database for a hospital.

Getting Started

Before we begin, ensure you have a SQL-compatible database system installed, such as MySQL, PostgreSQL, or SQLite. For this tutorial, we'll use SQLite.

📝 Note: SQL syntax is consistent across most database systems, so learning SQL with SQLite will help you adapt to other systems as well.

Creating the Database

First, let's create our Hospital database:

sql

sqlite3 Hospital.db

Now, let's create the necessary tables:

Patients table
Doctors table
Appointments table

💡 Pro Tip: Use the CREATE TABLE statement to create new tables.

Patients Table

Let's create the Patients table with the following fields:

PatientID (integer, primary key)
FirstName (text)
LastName (text)
Address (text)
PhoneNumber (text)

sql

CREATE TABLE Patients (
    PatientID INTEGER PRIMARY KEY,
    FirstName TEXT,
    LastName TEXT,
    Address TEXT,
    PhoneNumber TEXT
);

Doctors Table

Next, let's create the Doctors table with the following fields:

DoctorID (integer, primary key)
FirstName (text)
LastName (text)
Specialization (text)

sql

CREATE TABLE Doctors (
    DoctorID INTEGER PRIMARY KEY,
    FirstName TEXT,
    LastName TEXT,
    Specialization TEXT
);

Appointments Table

Finally, let's create the Appointments table with the following fields:

AppointmentID (integer, primary key)
PatientID (integer, foreign key referencing Patients.PatientID)
DoctorID (integer, foreign key referencing Doctors.DoctorID)
AppointmentDate (date)

sql

CREATE TABLE Appointments (
    AppointmentID INTEGER PRIMARY KEY,
    PatientID INTEGER,
    DoctorID INTEGER,
    AppointmentDate DATE,
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);

Data Insertion

Now that our tables are set up, let's insert some data:

sql

-- Insert sample data into Patients table
INSERT INTO Patients (PatientID, FirstName, LastName, Address, PhoneNumber)
VALUES (1, 'John', 'Doe', '123 Main St', '555-1234');

-- Insert sample data into Doctors table
INSERT INTO Doctors (DoctorID, FirstName, LastName, Specialization)
VALUES (1, 'Jane', 'Smith', 'Cardiology');

Querying the Data

Now that we have some data, let's see how to query it using SQL:

sql

-- View all patients
SELECT * FROM Patients;

-- View all doctors
SELECT * FROM Doctors;

-- View appointments for a specific doctor
SELECT * FROM Appointments WHERE DoctorID = 1;

Quiz

Stay tuned for more advanced SQL concepts and examples!

🚀 Onwards to mastering SQL! 🚀