PROBLEM-2 (15 Points) Student Grades We have a table called RAW_SCORES which con

Question

PROBLEM-2 (15 Points) Student Grades We have a table called RAW_SCORES which contains the raw scores of students in a class. For each student we have SSN, LName, FName, and scores for HW1, HW2a, HW2b, Midterm, HW3 and FExam. The maximum points for each HW or test are stored in the same table as the scores of a student whose SSN is 0001. The weights for each HW or Test are given as scores for a student whose SSN is 0002. Use the following CREATE TABLE and INSERT statements to create the table. DROP TABLE IF EXISTS RAW_SCORES; CREATE TABLE RAW_SCORES ( SSN CHAR(4), LName VARCHAR(16), FName VARCHAR(16), Section CHAR(3), HW1 FLOAT, HW2a FLOAT, HW2b FLOAT, Midterm FLOAT, HW3 FLOAT, FExam FLOAT, PRIMARY KEY (SSN) ); INSERT INTO RAW_SCORES VALUES ('9176', 'Epp', 'Eric', '415', 99, 79, 31, 99, 119, 199), ('5992', 'Lin', 'Linda', '415', 98, 71, 29, 83, 105, 171), ('3774', 'Adams', 'Abigail', '315', 85, 63, 27, 88, 112, 180), ('1212', 'Osborne', 'Danny', '315', 29, 31, 12, 66, 61, 106), ('0001', 'MAX', 'POINTS', '415', 100, 80, 32, 100, 120, 200), ('0002', 'Weight', 'OfScore', '415', 0.10, 0.10, 0.05, 0.25, 0.10, 0.40); For each procedure below, show the code, and the result of a CALL to that procedure: 1. CREATE a Procedure called SHOW_ALL_RAW_SCORES() which simply displays the entire table. After creating it, use a CALL SHOW_ALL_RAW_SCORES(); statement to demonstrate that it works.(3) 2. CREATE a Procedure called SHOW_SPECIFIC_RAW_SCORES(StudentSSN CHAR(4)) which simply displays the raw scores for the specific student whose SSN is StudentSSN. After creating it, use a CALL SHOW_SPECIFIC_RAW_SCORES('9176'); statement to demonstrate that it works.(2) 3. CREATE a Procedure called SHOW_ALL_PCT_WTAVG() which simply displays a table of percentages and weighted average. For each valid student (i.e. other than the ones with SSN = '0001' and SSN = '0002') it displays: SSN, LName, FName, HW1Pct, HW2aPct, HW2bPct, MidtermPct, HW3Pct, FExamPct, and the WeightedAverage (It looks like this):(10) SSN FName LName HW1Pct HW2aPct HW2bPct MidtermPct HW3Pct FExamPct WeightedAverage 9176
Eric


Epp
99
98.75
96.875

99
99.1666666666667
99.5
0.990854177743507
5992
Linda


Lin
98
88.75
90.625

83
87.5
85.5
0.869062509858049
3774
Abigail


Adams
85
78.75
84.375

88
93.3333333333333
90
0.879270843157234
1212
Danny


Osborne
29
38.75
37.5

66
50.8333333333333
53
0.514333338538806
HW1Pct is calculated as (HW1 / hw1M * 100) where hw1M is the max score possible for HW1 (i.e. the HW1 value for the student with SSN = '0001'). The other percentages are calculated similarly: divide the score by the max score possible for that column and multiply by 100. Weighted average is calculated as: HW1/hw1M * hw1W + HW2a/hw2aM * hw2aW + HW2b/hw2bM * hw2bW + Midterm/MidtermM * MidtermW + HW3/hw3M * hw3W + FExam/fExamM * fExamW where hw1M is the HW1 value for student with SSN = '0001' and hw1W is the HW1 value for student with SSN = '0002', etc. Other values are to be calculated similarly. Hint: You have to declare many variables hw1M, hw2aM, … hw1W, … and set values to it using statements such as: SET hw1M = (SELECT hw1 FROM RAW_SCORES WHERE (SSN = '0001')); SET hw1W = (SELECT hw1 FROM RAW_SCORES WHERE (SSN = '0002')); After creating the procedure, use a CALL SHOW_ALL_PERCENTAGES (); statement to demonstrate that it works. PROBLEM-2 (15 Points) Student Grades We have a table called RAW_SCORES which contains the raw scores of students in a class. For each student we have SSN, LName, FName, and scores for HW1, HW2a, HW2b, Midterm, HW3 and FExam. The maximum points for each HW or test are stored in the same table as the scores of a student whose SSN is 0001. The weights for each HW or Test are given as scores for a student whose SSN is 0002. Use the following CREATE TABLE and INSERT statements to create the table. DROP TABLE IF EXISTS RAW_SCORES; CREATE TABLE RAW_SCORES ( SSN CHAR(4), LName VARCHAR(16), FName VARCHAR(16), Section CHAR(3), HW1 FLOAT, HW2a FLOAT, HW2b FLOAT, Midterm FLOAT, HW3 FLOAT, FExam FLOAT, PRIMARY KEY (SSN) ); INSERT INTO RAW_SCORES VALUES ('9176', 'Epp', 'Eric', '415', 99, 79, 31, 99, 119, 199), ('5992', 'Lin', 'Linda', '415', 98, 71, 29, 83, 105, 171), ('3774', 'Adams', 'Abigail', '315', 85, 63, 27, 88, 112, 180), ('1212', 'Osborne', 'Danny', '315', 29, 31, 12, 66, 61, 106), ('0001', 'MAX', 'POINTS', '415', 100, 80, 32, 100, 120, 200), ('0002', 'Weight', 'OfScore', '415', 0.10, 0.10, 0.05, 0.25, 0.10, 0.40); For each procedure below, show the code, and the result of a CALL to that procedure: 1. CREATE a Procedure called SHOW_ALL_RAW_SCORES() which simply displays the entire table. After creating it, use a CALL SHOW_ALL_RAW_SCORES(); statement to demonstrate that it works.(3) 2. CREATE a Procedure called SHOW_SPECIFIC_RAW_SCORES(StudentSSN CHAR(4)) which simply displays the raw scores for the specific student whose SSN is StudentSSN. After creating it, use a CALL SHOW_SPECIFIC_RAW_SCORES('9176'); statement to demonstrate that it works.(2) 3. CREATE a Procedure called SHOW_ALL_PCT_WTAVG() which simply displays a table of percentages and weighted average. For each valid student (i.e. other than the ones with SSN = '0001' and SSN = '0002') it displays: SSN, LName, FName, HW1Pct, HW2aPct, HW2bPct, MidtermPct, HW3Pct, FExamPct, and the WeightedAverage (It looks like this):(10) SSN FName LName HW1Pct HW2aPct HW2bPct MidtermPct HW3Pct FExamPct WeightedAverage 9176
Eric


Epp
99
98.75
96.875

99
99.1666666666667
99.5
0.990854177743507
5992
Linda


Lin
98
88.75
90.625

83
87.5
85.5
0.869062509858049
3774
Abigail


Adams
85
78.75
84.375

88
93.3333333333333
90
0.879270843157234
1212
Danny


Osborne
29
38.75
37.5

66
50.8333333333333
53
0.514333338538806
HW1Pct is calculated as (HW1 / hw1M * 100) where hw1M is the max score possible for HW1 (i.e. the HW1 value for the student with SSN = '0001'). The other percentages are calculated similarly: divide the score by the max score possible for that column and multiply by 100. Weighted average is calculated as: HW1/hw1M * hw1W + HW2a/hw2aM * hw2aW + HW2b/hw2bM * hw2bW + Midterm/MidtermM * MidtermW + HW3/hw3M * hw3W + FExam/fExamM * fExamW where hw1M is the HW1 value for student with SSN = '0001' and hw1W is the HW1 value for student with SSN = '0002', etc. Other values are to be calculated similarly. Hint: You have to declare many variables hw1M, hw2aM, … hw1W, … and set values to it using statements such as: SET hw1M = (SELECT hw1 FROM RAW_SCORES WHERE (SSN = '0001')); SET hw1W = (SELECT hw1 FROM RAW_SCORES WHERE (SSN = '0002')); After creating the procedure, use a CALL SHOW_ALL_PERCENTAGES (); statement to demonstrate that it works. PROBLEM-2 (15 Points) Student Grades We have a table called RAW_SCORES which contains the raw scores of students in a class. For each student we have SSN, LName, FName, and scores for HW1, HW2a, HW2b, Midterm, HW3 and FExam. The maximum points for each HW or test are stored in the same table as the scores of a student whose SSN is 0001. The weights for each HW or Test are given as scores for a student whose SSN is 0002. Use the following CREATE TABLE and INSERT statements to create the table. DROP TABLE IF EXISTS RAW_SCORES; CREATE TABLE RAW_SCORES ( SSN CHAR(4), LName VARCHAR(16), FName VARCHAR(16), Section CHAR(3), HW1 FLOAT, HW2a FLOAT, HW2b FLOAT, Midterm FLOAT, HW3 FLOAT, FExam FLOAT, PRIMARY KEY (SSN) ); INSERT INTO RAW_SCORES VALUES ('9176', 'Epp', 'Eric', '415', 99, 79, 31, 99, 119, 199), ('5992', 'Lin', 'Linda', '415', 98, 71, 29, 83, 105, 171), ('3774', 'Adams', 'Abigail', '315', 85, 63, 27, 88, 112, 180), ('1212', 'Osborne', 'Danny', '315', 29, 31, 12, 66, 61, 106), ('0001', 'MAX', 'POINTS', '415', 100, 80, 32, 100, 120, 200), ('0002', 'Weight', 'OfScore', '415', 0.10, 0.10, 0.05, 0.25, 0.10, 0.40); For each procedure below, show the code, and the result of a CALL to that procedure: 1. CREATE a Procedure called SHOW_ALL_RAW_SCORES() which simply displays the entire table. After creating it, use a CALL SHOW_ALL_RAW_SCORES(); statement to demonstrate that it works.(3) 2. CREATE a Procedure called SHOW_SPECIFIC_RAW_SCORES(StudentSSN CHAR(4)) which simply displays the raw scores for the specific student whose SSN is StudentSSN. After creating it, use a CALL SHOW_SPECIFIC_RAW_SCORES('9176'); statement to demonstrate that it works.(2) 3. CREATE a Procedure called SHOW_ALL_PCT_WTAVG() which simply displays a table of percentages and weighted average. For each valid student (i.e. other than the ones with SSN = '0001' and SSN = '0002') it displays: SSN, LName, FName, HW1Pct, HW2aPct, HW2bPct, MidtermPct, HW3Pct, FExamPct, and the WeightedAverage (It looks like this):(10) SSN FName LName HW1Pct HW2aPct HW2bPct MidtermPct HW3Pct FExamPct WeightedAverage 9176
Eric


Epp
99
98.75
96.875

99
99.1666666666667
99.5
0.990854177743507
5992
Linda


Lin
98
88.75
90.625

83
87.5
85.5
0.869062509858049
3774
Abigail


Adams
85
78.75
84.375

88
93.3333333333333
90
0.879270843157234
1212
Danny


Osborne
29
38.75
37.5

66
50.8333333333333
53
0.514333338538806
HW1Pct is calculated as (HW1 / hw1M * 100) where hw1M is the max score possible for HW1 (i.e. the HW1 value for the student with SSN = '0001'). The other percentages are calculated similarly: divide the score by the max score possible for that column and multiply by 100. Weighted average is calculated as: HW1/hw1M * hw1W + HW2a/hw2aM * hw2aW + HW2b/hw2bM * hw2bW + Midterm/MidtermM * MidtermW + HW3/hw3M * hw3W + FExam/fExamM * fExamW where hw1M is the HW1 value for student with SSN = '0001' and hw1W is the HW1 value for student with SSN = '0002', etc. Other values are to be calculated similarly. Hint: You have to declare many variables hw1M, hw2aM, … hw1W, … and set values to it using statements such as: SET hw1M = (SELECT hw1 FROM RAW_SCORES WHERE (SSN = '0001')); SET hw1W = (SELECT hw1 FROM RAW_SCORES WHERE (SSN = '0002')); After creating the procedure, use a CALL SHOW_ALL_PERCENTAGES (); statement to demonstrate that it works.

Explanation / Answer

Answer to Question 1:

DELIMITER //

CREATE PROCEDURE SHOW_ALL_RAW_SCORES()

BEGIN

SELECT * FROM RAW_SCORES;

END//

DELIMITER ;

CALL SHOW_ALL_RAW_SCORES();

Answer to Question 2:

DELIMITER //

CREATE PROCEDURE SHOW_SPECIFIC_RAW_SCORES(StudentSSN CHAR(4))

BEGIN

SELECT * FROM RAW_SCORES WHERE SSN=StudentSSN;

END//

DELIMITER ;

CALL SHOW_SPECIFIC_RAW_SCORES('9176');

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.