SQL Tutorial: Building a Student Database 🎯

Welcome to our SQL tutorial! Today, we're going to create a Student Database from scratch. By the end of this tutorial, you'll understand how to design and manage a database using SQL, a powerful language for managing and manipulating databases. Let's dive in! 🐋

What is SQL? 📝

SQL (Structured Query Language) is a language used to communicate with and manipulate databases. It allows us to perform tasks such as creating tables, inserting data, querying data, and updating data within a database.

Creating a Student Database 💡

Before we start, let's list the tables we need for our Student Database:

Students
Courses
Enrollments

Students Table ✅

The Students table will store information about each student, such as their name, ID, and email.

sql

CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

📝 Note: We've used INT for the ID, VARCHAR for Name and Email. We've set the ID as the primary key, meaning each student has a unique ID.

Courses Table ✅

The Courses table will store information about each course, such as its ID, name, and duration.

sql

CREATE TABLE Courses (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Duration INT
);

📝 Note: We've used INT for the ID and Duration, and VARCHAR for the Name.

Enrollments Table ✅

The Enrollments table will store information about each student's enrollment in a course, such as the student's ID, the course's ID, and the enrollment date.

sql

CREATE TABLE Enrollments (
    ID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE
);

📝 Note: We've used INT for the ID, StudentID, and CourseID, and DATE for the EnrollmentDate.

Inserting Data 💡

Now that our tables are created, let's insert some data.

sql

-- Inserting data into Students table
INSERT INTO Students (ID, Name, Email)
VALUES (1, 'John Doe', 'john.doe@example.com'),
       (2, 'Jane Smith', 'jane.smith@example.com');

-- Inserting data into Courses table
INSERT INTO Courses (ID, Name, Duration)
VALUES (1, 'Web Development', 12),
       (2, 'Data Science', 18);

📝 Note: We've used multiple INSERT statements to insert data into our tables.

Queries 💡

Now that we have some data, let's perform some queries to retrieve information.

Finding all students 📝

sql

SELECT * FROM Students;

Finding a specific student 📝

sql

SELECT * FROM Students WHERE ID = 1;

Finding a student by email 📝

sql

SELECT * FROM Students WHERE Email = 'john.doe@example.com';

Finding all courses 📝

sql

SELECT * FROM Courses;

Enrolling a student in a course 💡

sql

INSERT INTO Enrollments (ID, StudentID, CourseID, EnrollmentDate)
VALUES (1, 1, 1, '2022-01-01');

📝 Note: We've used the IDs 1 for both the student and the course, and a date for the enrollment.

Wrapping Up 💡

You've now learned how to create a basic Student Database using SQL. You've seen how to create tables, insert data, and query data. With this foundation, you're well on your way to becoming an SQL expert!