This lecture concludes the student grades mini-project with data analysis.
Welcome back. In the last lecture, we created our `Grades` table and filled it with data. Now, let’s use our knowledge of aggregate functions and GROUP BY to ask some interesting questions and get meaningful insights from that data.
Query 1: Calculate the Average Score for Each Student
This is a classic task. We want to see each student’s overall performance. To do this, we need to group the rows by `StudentName` and then calculate the average of the `Score` for each group.
SELECT
StudentName,
AVG(Score) AS AverageScore
FROM Grades
GROUP BY StudentName;This query will give us a clean summary of each student’s average grade across all their subjects.
Query 2: Find the Highest and Lowest Score in Each Subject
Now let’s analyze the subjects. Which subject has the highest score, and which has the lowest? We can group by `Subject` and use the MAX() and MIN() functions.
SELECT
Subject,
MAX(Score) AS HighestScore,
MIN(Score) AS LowestScore
FROM Grades
GROUP BY Subject;This gives us a great overview of the performance range within each subject.
Query 3: Count the Number of Tests Each Student Has Taken
To make sure every student has the same number of grades recorded, we can count the number of entries for each student. We group by `StudentName` and use COUNT().
SELECT
StudentName,
COUNT(*) AS NumberOfTests
FROM Grades
GROUP BY StudentName;Query 4: Find the Overall Average Score for Each Subject
Finally, let’s find the average score for each subject across all students. This can help identify which subjects students are finding easier or harder.
SELECT
Subject,
AVG(Score) AS AverageScoreForSubject
FROM Grades
GROUP BY Subject
ORDER BY AverageScoreForSubject DESC; -- Sort from highest to lowest avg
Project Complete!
Excellent work! You’ve not only stored data but also analyzed it to produce valuable summaries. This is the core of what makes databases so useful. You’ve learned to take raw data and transform it into information.
In the Next Part of the Series…
We will begin Part 3: Shaping and Connecting Your Data. We’ll learn why storing all our data in one big table isn’t always a good idea and introduce the concept of Primary Keys and Foreign Keys to link multiple tables together. This is the key to building powerful, relational databases.
