This lecture begins a mini-project to analyze student grades.
Let’s begin our second mini-project. This time, we will create a database to store student grades for different subjects. This project is designed to use the aggregate functions and the GROUP BY clause we have just learned.
Project Goal
Our goal is to create a table that stores individual grades for students. We then want to perform some basic analysis, such as finding the average grade for each student and the average grade for each subject.
Step 1: Designing the Table
We need to store the following information for each grade entry:
- A unique ID for the grade entry.
- The name of the student.
- The subject.
- The grade or score they received (out of 100).
Here is the plan for our `Grades` table:
GradeID:INTEGER PRIMARY KEY– The unique identifier.StudentName:TEXT– The name of the student.Subject:TEXT– The name of the subject (e.g., ‘Math’, ‘History’).Score:INTEGER– The score the student received (0-100).
Step 2: Creating and Populating the Table
Now, let’s write the SQL to create the `Grades` table and insert some data. We will add grades for three students: Alice, Bob, and Charlie.
CREATE TABLE Grades (
GradeID INTEGER PRIMARY KEY,
StudentName TEXT,
Subject TEXT,
Score INTEGER
);
-- Alice's Grades
INSERT INTO Grades (StudentName, Subject, Score) VALUES ('Alice', 'Math', 90);
INSERT INTO Grades (StudentName, Subject, Score) VALUES ('Alice', 'History', 85);
INSERT INTO Grades (StudentName, Subject, Score) VALUES ('Alice', 'Science', 92);
-- Bob's Grades
INSERT INTO Grades (StudentName, Subject, Score) VALUES ('Bob', 'Math', 78);
INSERT INTO Grades (StudentName, Subject, Score) VALUES ('Bob', 'History', 88);
INSERT INTO Grades (StudentName, Subject, Score) VALUES ('Bob', 'Science', 72);
-- Charlie's Grades
INSERT INTO Grades (StudentName, Subject, Score) VALUES ('Charlie', 'Math', 95);
INSERT INTO Grades (StudentName, Subject, Score) VALUES ('Charlie', 'History', 91);
INSERT INTO Grades (StudentName, Subject, Score) VALUES ('Charlie', 'Science', 89);Run these commands to create and populate your table. We now have a good set of data to work with.
In the Next Lecture…
In the second part of this project, we will use our new data analysis skills. We will write queries to calculate the average score for each student, find the highest and lowest grade in each subject, and count how many tests each student has taken. This will put GROUP BY and our aggregate functions to a real-world test.
