Query and report exercises (Class registration system)

Case background:

This is an exercise that is an expansion of case 2 in the tech plugin 5. Notice that in the database given, there are a few differences from the original case:

The data type for section id is set to be auto number instead of 4 random digits. Section ID is the PK of the section table.

The grade conversion table is added to help compute GPA.

Task:

When naming your queries and reports, please include query number and report number. For example, for the first query, please name it query1: students majors.

Query 1: Make a list of majors and the number of the students for each of those majors. Make sure that the output will have headings of major and number of students.

Query 2: What is the GPA of each student?

Query 3: Show the name(s) of students who has(have) taken at least a course section with professor Jen Jackson and also show the section name and semester when taking that course section.

Query 4: What is the total credit of a certain student? Your query will prompt the user for a student id and then return the total credits of that student. The output should include the headings of student id and total credits.

Query 5: Update the cap size of all the classes assigned to a certain instructor by asking (1) what is the first name, (2) what is the last name, (3) how many to increase by? The query will then accordingly update the cap size of all the classes assigned to that instructor. Make sure that your query prompts the user with the questions in the right order.

Query 6: What is the office location of a certain full time professor? Prompt the user with the following two questions: (1) what is the instructors first name? and (2) what is the instructors last name?

Query 7: What is the lowest grade each student has received? Show the students student id, first name, last name, and lowest grade as the headings.

Query 8: What is the total number of students that have registered for each course? Show course id, course title, and number of students as the headings.

Query 9: The school would like to know the grade distribution of each course section. For each section, they would like the number of students for each grade issued. The output should show section id, course id, grade, number of students as the headings.

Query 10: What are the names of students who have taken courses whose course title contains the word introduction? The output should show students first name, last name, course id and course title as the headings.

Query 11: The school would like to have a query that allows the instructor to change a students grade for a particular course section. The query will prompt the user with the first question what is the section id?, the second question what is the student id?, and finally the third question what is the new grade? The questions must be presented in the order as described. They query will then update the grade accordingly.

Query 12: The school would like to give bonus for instructors. The bonus amount for each instructor will be 10% of the total pay each instructor has received for all the course sections he/ she taught. The output should show instructor instructor id, first name, last name, total pay, and bonus. The bonus should be formatted as currency.

Report 1: Create a roster report. It will list the course title in the first level, section name in the second level, and grade, grade point, first name, and last name in the third level. And it will average the grade point respectively for each section and each course. Make sure that all the information is visible. Sort the students in each section by their grade.

Report 2: Show the students names and their grade for each course section.

Report 3: Create a report for students in each major. It should show major at the first level and student id, first name and last name in the second level (as shown in the picture below).