Mini project: student marks register

50 min · Beginner

A marks register is a table that is never finished: marks are entered, corrected, moderated, removed and re-evaluated all semester. Each of those jobs is one careful INSERT, UPDATE, DELETE or upsert.

What you'll build

You write one SQL script, queries.sql, for a class teacher. The three tables (students, subjects and marks) and a semester of marks for five students are already in the database: your repository's data/shop.sql creates and fills them.

Your script then keeps the register correct as the semester goes on. Borderline marks are moderated to the pass mark, a student who withdrew is removed, and the re-evaluation results arrive as a list that mixes changed marks, unchanged marks and one new row. After each change the script prints ROW_COUNT(), so you can see that exactly the expected rows changed, and it ends with the final register.

Sample run

+-----------+
| moderated |
+-----------+
|         1 |
+-----------+
+---------------+
| marks_removed |
+---------------+
|             3 |
+---------------+
+------------------+
| revaluation_rows |
+------------------+
|                5 |
+------------------+
+---------+----------------+---------+
| roll_no | name           | section |
+---------+----------------+---------+
|     101 | Aarav Kulkarni | A       |
|     102 | Bhumika Rao    | A       |
|     103 | Chetan Yadav   | B       |
|     104 | Diya Menon     | B       |
+---------+----------------+---------+
+---------+--------------+-------+----------+
| roll_no | subject_code | marks | revalued |
+---------+--------------+-------+----------+
|     101 | CS301        |    78 |        0 |
|     101 | CS302        |    71 |        1 |
|     101 | MA301        |    82 |        0 |
|     102 | CS301        |    91 |        0 |
|     102 | CS302        |    88 |        0 |
|     102 | MA301        |    40 |        0 |
|     103 | CS301        |    54 |        0 |
|     103 | CS302        |    52 |        0 |
|     103 | MA301        |    61 |        1 |
|     104 | CS301        |    67 |        0 |
|     104 | CS302        |    74 |        0 |
|     104 | MA301        |    70 |        1 |
+---------+--------------+-------+----------+

Requirements

Your program should do all of these.

queries.sql does not create or fill tables: mr_students, mr_subjects and mr_marks (primary keys, foreign keys, a CHECK that marks are 0 to 100, and a revalued flag that starts as false) come from data/shop.sql.
One mark is corrected with an UPDATE on the full primary key: roll 103, subject CS301, becomes 54.
Marks of 37 to 39 are raised to 40 with one UPDATE, and SELECT ROW_COUNT() AS moderated; prints how many rows it changed (right after that UPDATE).
The withdrawn student (roll 105) is removed: marks first, with SELECT ROW_COUNT() AS marks_removed; printed after that DELETE, then the student.
Re-evaluation results are loaded with one INSERT ... AS new ON DUPLICATE KEY UPDATE: roll 101 CS302 is 71, roll 103 MA301 is 61 and roll 104 MA301 is 70, all with revalued true. SELECT ROW_COUNT() AS revaluation_rows; follows it.
The script ends with two result sets: the students (roll_no, name, section) by roll number, then the marks (roll_no, subject_code, marks, revalued) by roll number and subject code.

Build it step by step in the Build tab (5 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

What's next

You finished the preview of Inserting, Updating & Deleting Data

Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.

Create account I already have an account

Next step

Start building

5 milestones

What you'll build

Sample run

+-----------+
| moderated |
+-----------+
|         1 |
+-----------+
+---------------+
| marks_removed |
+---------------+
|             3 |
+---------------+
+------------------+
| revaluation_rows |
+------------------+
|                5 |
+------------------+
+---------+----------------+---------+
| roll_no | name           | section |
+---------+----------------+---------+
|     101 | Aarav Kulkarni | A       |
|     102 | Bhumika Rao    | A       |
|     103 | Chetan Yadav   | B       |
|     104 | Diya Menon     | B       |
+---------+----------------+---------+
+---------+--------------+-------+----------+
| roll_no | subject_code | marks | revalued |
+---------+--------------+-------+----------+
|     101 | CS301        |    78 |        0 |
|     101 | CS302        |    71 |        1 |
|     101 | MA301        |    82 |        0 |
|     102 | CS301        |    91 |        0 |
|     102 | CS302        |    88 |        0 |
|     102 | MA301        |    40 |        0 |
|     103 | CS301        |    54 |        0 |
|     103 | CS302        |    52 |        0 |
|     103 | MA301        |    61 |        1 |
|     104 | CS301        |    67 |        0 |
|     104 | CS302        |    74 |        0 |
|     104 | MA301        |    70 |        1 |
+---------+--------------+-------+----------+

Requirements

Your program should do all of these.

queries.sql does not create or fill tables: mr_students, mr_subjects and mr_marks (primary keys, foreign keys, a CHECK that marks are 0 to 100, and a revalued flag that starts as false) come from data/shop.sql.

One mark is corrected with an UPDATE on the full primary key: roll 103, subject CS301, becomes 54.

Marks of 37 to 39 are raised to 40 with one UPDATE, and SELECT ROW_COUNT() AS moderated; prints how many rows it changed (right after that UPDATE).

The withdrawn student (roll 105) is removed: marks first, with SELECT ROW_COUNT() AS marks_removed; printed after that DELETE, then the student.

Re-evaluation results are loaded with one INSERT ... AS new ON DUPLICATE KEY UPDATE: roll 101 CS302 is 71, roll 103 MA301 is 61 and roll 104 MA301 is 70, all with revalued true. SELECT ROW_COUNT() AS revaluation_rows; follows it.

The script ends with two result sets: the students (roll_no, name, section) by roll number, then the marks (roll_no, subject_code, marks, revalued) by roll number and subject code.