Answer Happy

class_registrations: dass_registration_id dass_section_id 1 2 3 4 5 6 7 18 NULL class_sections: dass_section_id 1 2 3 4 5 6 HULL classes: class_id name 1 2 NULL grades: 3 4 5 HULL students: Test HULL 1 2 3 HULL ABCDF grade_id letter_grade 1 2 А с 1 1 1 3 4 1 1 2 2 2 3 MULL 6 6 Mary Kim HULL 6 NULL dass_id 1 1 2 3 2 3 student_id grade_id signup_timestamp instructor_id 1 2 3 1 1 1 2 3 NULL Davidson Tatum Walls HULL term_id 1 2 description Introduction to Database Usage Application-intensive course on database s... COLLEGE ALGEBRA Emphasis on the concept of functions as m.... This class is used for testing our databases! NULL student_id first_name last_name birthdate Christopher 2 HULL 1 3 4 1 2 3 2 1 1 3 HULL HULL HULL code CS-HU 310 MATH 143 test101 HULL maximum_students 32 32 3 NULL
ELSEIF letter_grade = 'C' THEN SET result = 2; ELSEIF letter_grade = 'D' THEN SET result = 1; ELSEIF letter_grade = 'F' THEN SET result = 0; ELSEIF letter_grade IS NULL THEN SET result = NULL; END IF; RETURN result; END $$
Step 7: For each of "reports" below create a SQL query that the returns the same columns provided in the example out. Add each query to the reporting_queries.sql file in the project. Include an SQL comment indicating which report the query is for. Example: 1.Calculate the GPA for student given a student_id (use student_id=1) SELECT FROM <> JOIN <> JOIN <> JOIN <> GROUP BY ; Reports 1. Calculate the GPA for student given a student_id (use student_id=1) first_name last_name number_of_classes total_grade_points_earned GPA Christopher Davidson 4 3.7500 2. Calculate the GPA for each student (across all classes and all terms) first_name last_name Christopher Mary Kim number_of_classes code CS-HU 310 MATH 143 test101 Davidson 4 Tatum 2 Walls 1 3. Calculate the avg GPA for each class name Introduction to Database Usage COLLEGE ALGEBRA Test 3 NN 2 15 2 total_grade_points_earned number_of_grades total_grade_points 15 GPA 3.7500 2.5000 2.0000 976 AVG GPA 3.0000 3.5000 3.0000
4. Calculate the avg GPA for each class and term code MATH 143 CS-HU 310 MATH 143 CS-HU 310 MATH 143 test 101 term COLLEGE ALGEBRA FALL 2021 Introduction to Database Usage FALL 2021 COLLEGE ALGEBRA name 1 3 SPRING 2021 1 Introduction to Database Usage SPRING 2021 0 COLLEGE ALGEBRA 0 FALL 2020 SPRING 2020 2 Test first_name last_name title Arthur Arthur 5. List all the classes being taught by an instructor (use instructor_id=1) Putnam Adjunct Instructor Putnam Adjunct Instructor 6. List all classes with terms & instructor code CS-HU 310 CS-HU 310 MATH 143 MATH 143 code CS-HU 310 CS-HU 310 code CS-HU 310 MATH 143 MATH 143 test101 AVG number_of_grades total_grade_points GPA 4.0000 3.0000 3.0000 HULL NULL name Introduction to Database Usage Introduction to Database Usage COLLEGE ALGEBRA COLLEGE ALGEBRA COLLEGE ALGEBRA Test MATH 143 test101 7. Calculate the remaining space left in a class term FALL 2021 SPRING 2021 SPRING 2021 dass_name Introduction to Database Usage Introduction to Database Usage FALL 2021 FALL 2020 SPRING 2020 name term Introduction to Database Usage FALL 2021 COLLEGE ALGEBRA SPRING 2021 COLLEGE ALGEBRA FALL 2021 Test SPRING 2020 4 9 3 HULL NULL 6 1 1 3 Arthur Arthur Helen Brent Helen Brent first_name last_name Putnam Putnam Johnson Miller Johnson Miller enrolled students 3 space_remaining 29 31 31 term FALL 2021 SPRING 2021 0 3.0000
3. CREATE DATABASE IF NOT EXISTS cs_hu_310_final_project; 4. USE cs_hu_310_final_project; DROP TABLE IF EXISTS DROP TABLE IF EXISTS grades; DROP TABLE IF EXISTS class_sections; DROP TABLE IF EXISTS instructors; DROP TABLE IF EXISTS academic_titles; DROP TABLE IF EXISTS students; DROP TABLE IF EXISTS classes; DROP FUNCTION IF EXISTS convert_to_grade_point; 5. 6. 7. 8. 9 10. 11. 12. 13 14 15 16 17 18 9 20 21 22 class_registrations; CREATE TABLE IF NOT EXISTS classes ( class_id INT AUTO_INCREMENT, ); name VARCHAR(50) NOT NULL, description VARCHAR(1000), code VARCHAR(10) UNIQUE, maximum_students INT DEFAULT 10, PRIMARY KEY(class_id)
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 CREATE TABLE IF NOT EXISTS students ( student_id INT AUTO_INCREMENT, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(50) NOT NULL, birthdate DATE, PRIMARY KEY (student_id) CREATE TABLE IF NOT EXISTS academic_titles( academic_title_id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) NOT NULL, PRIMARY KEY (academic_title_id) CREATE TABLE IF NOT EXISTS instructors ( instructor_id INT AUTO_INCREMENT NOT NULL, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, ); academic_title_id INT, PRIMARY KEY (instructor_id), FOREIGN KEY (academic_title_id) REFERENCES academic_titles (academic_title_id)
46 47 48 49 50 51 52 53 54 55 56 57 58 59. 60 61. 62 63 64 65 66 67 CO CREATE TABLE IF NOT EXISTS terms ( ); term_id INT AUTO_INCREMENT NOT NULL, name VARCHAR(80) NOT NULL, PRIMARY KEY (term_id) CREATE TABLE IF NOT EXISTS class_sections ( class_section_id INT AUTO_INCREMENT NOT NULL, class_id INT NOT NULL, instructor_id INT NOT NULL, term_id INT NOT NULL, PRIMARY KEY(class_section_id) ); ALTER TABLE class_sections add FOREIGN KEY(class_id) REFERENCES classes (class_id); ALTER TABLE class_sections add FOREIGN KEY(instructor_id) REFERENCES instructors (instructor_id); ALTER TABLE class_sections add FOREIGN KEY(term_id) REFERENCES terms (term_id); CREATE TABLE IF NOT EXISTS grades ( grade_id INT AUTO_INCREMENT NOT NULL, letter_grade CHAR (2) NOT NULL, PRIMARY KEY(grade_id)
68 69 70 71 72 73 74 75 76 77. 78. 79. 80. 81 82 83. 84 85 86 87 88 89 90 91 92 93 94 CREATE TABLE IF NOT EXISTS class_registrations ( class_registration_id INT AUTO_INCREMENT, class_section_id INT NOT NULL, student_id INT NOT NULL, grade_id INT, e signup_timestamp datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(class_registration_id) ); ALTER TABLE class_registrations add FOREIGN KEY(class_section_id) REFERENCES class_sections (class_section_id); ALTER TABLE class_registrations add FOREIGN KEY(student_id) REFERENCES students (student_id); ALTER TABLE class_registrations add FOREIGN KEY(grade_id) REFERENCES grades (grade_id); ALTER TABLE class_registrations add CONSTRAINT duplicates UNIQUE (student_id, class_section_id); DELIMITER $$ CREATE FUNCTION convert_to_grade_point(letter_grade char(2)) RETURNS INT DETERMINISTIC ⒸBEGIN DECLARE result INT; SET result = 0; IF letter_grade = "A" THEN SET result = 4; ELSEIF letter_grade = 'B' THEN SET result = 3; ELSEIF letter_grade = 'C' THEN SET result = 2;