Relational model mental model in SQL - Mini Project: Build & Apply

Building a Simple Student Grades Database

📖 Scenario: You are helping a school keep track of students and their grades in different subjects. This will help teachers quickly find out how students are doing.

🎯 Goal: Create a small database with tables for Students and Grades. Then, link the grades to the students using a common key. Finally, write a query to see each student's name with their grades.

📋 What You'll Learn

Create a Students table with columns StudentID (integer), Name (text).

Create a Grades table with columns GradeID (integer), StudentID (integer), Subject (text), and Score (integer).

Insert exactly three students with IDs 1, 2, 3 and names 'Alice', 'Bob', 'Charlie'.

Insert exactly five grades linking to the students by StudentID.

Write a query to join Students and Grades on StudentID to show each student's name, subject, and score.

💡 Why This Matters

🌍 Real World

Schools and educational institutions use databases like this to keep track of students and their grades efficiently.

💼 Career

Understanding relational models and SQL queries is essential for database administrators, data analysts, and backend developers.

1

Create the Students table and add data

Write SQL to create a table called Students with columns StudentID as INTEGER and Name as TEXT. Then insert these three rows exactly: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie').

SQL

-- Create Students table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to make the table. Use INSERT INTO Students (StudentID, Name) VALUES to add the three students.

2

Create the Grades table and add data

Write SQL to create a table called Grades with columns GradeID INTEGER, StudentID INTEGER, Subject TEXT, and Score INTEGER. Then insert these five rows exactly: (1, 1, 'Math', 85), (2, 1, 'English', 90), (3, 2, 'Math', 78), (4, 3, 'English', 88), (5, 3, 'Science', 92).

SQL

CREATE TABLE Students (
    StudentID INTEGER,
    Name TEXT
);

INSERT INTO Students (StudentID, Name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Create Grades table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE Grades with the four columns. Use INSERT INTO Grades to add the five grade records.

3

Write a query to join Students and Grades

Write a SQL SELECT query that joins Students and Grades on StudentID. Select Students.Name, Grades.Subject, and Grades.Score.

SQL

CREATE TABLE Students (
    StudentID INTEGER,
    Name TEXT
);

INSERT INTO Students (StudentID, Name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE Grades (
    GradeID INTEGER,
    StudentID INTEGER,
    Subject TEXT,
    Score INTEGER
);

INSERT INTO Grades (GradeID, StudentID, Subject, Score) VALUES
(1, 1, 'Math', 85),
(2, 1, 'English', 90),
(3, 2, 'Math', 78),
(4, 3, 'English', 88),
(5, 3, 'Science', 92);

-- Write SELECT query joining Students and Grades
-- Your code here

Need a hint?

Use JOIN to connect the two tables on StudentID. Select the student's name, subject, and score.

4

Add a primary key and foreign key constraints

Alter the Students table to add a primary key on StudentID. Alter the Grades table to add a primary key on GradeID and a foreign key on StudentID referencing Students(StudentID).

SQL

CREATE TABLE Students (
    StudentID INTEGER,
    Name TEXT
);

INSERT INTO Students (StudentID, Name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE Grades (
    GradeID INTEGER,
    StudentID INTEGER,
    Subject TEXT,
    Score INTEGER
);

INSERT INTO Grades (GradeID, StudentID, Subject, Score) VALUES
(1, 1, 'Math', 85),
(2, 1, 'English', 90),
(3, 2, 'Math', 78),
(4, 3, 'English', 88),
(5, 3, 'Science', 92);

SELECT Students.Name, Grades.Subject, Grades.Score
FROM Students
JOIN Grades ON Students.StudentID = Grades.StudentID;

-- Add primary key and foreign key constraints
-- Your code here

Need a hint?

Add PRIMARY KEY to StudentID in Students and GradeID in Grades. Add a FOREIGN KEY on Grades.StudentID referencing Students.StudentID.