The COE has a (fake) database to keep track of students who attend courses of the MMOR program: Registrations DB (zipped). The Year here is the admission year.

We are going to update the MMOR course database by adding this year’s students and ‘registering’ them. First we are going to update the list of Students.

Write the SQL commands to perform each of the following tasks in Access. Save separate queries for each one, indicating the exercise number.

1. Add a single MMOR 2016 student into the Students table.

2a. Create a table with at least three MMOR 2016 students and …

2b. add those students to the Students table.

3. Remove students admitted before 2010.

Now we are going to register the 2016 students in the MMOR courses, using an auxiliary table to add the new tuples to the Registration table.

4. Select the course ID and term of all courses.

5. Select the student ID and admission year of MMOR 2016 students.

6. Select the student ID, student admission year, course ID and course term of the 2016 students and all the courses. We will assume that new students will be registered in all the available courses.

7. Repeat the previous query, now saving the results in a new auxiliary table called Registrations_2016.

8. Insert the rows from the auxiliary table into the registrations table.

9. Update the grades for MMOR 2016 as 100 in the registration table.

You have registered the new students in all the courses by adding new tuples into the Registration table. Now let’s reset the tables and see if you can do the same without using an auxiliary table.

10. Delete the auxiliary table Registrations_2016.

11. Remove 2016 students from the Registrations table.

12.* Add 2016 students and courses in the registration table without using an auxiliary table.

13.* Update the final grade = 100 for the 2016 students in the registration table.