-
Notifications
You must be signed in to change notification settings - Fork 0
/
challenge7-Gradebook.sql
40 lines (36 loc) · 1.69 KB
/
challenge7-Gradebook.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
/*Challenge: Gradebook*/
CREATE TABLE student_grades (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
number_grade INTEGER,
fraction_completed REAL);
INSERT INTO student_grades (name, number_grade, fraction_completed)
VALUES ("Winston", 90, 0.805);
INSERT INTO student_grades (name, number_grade, fraction_completed)
VALUES ("Winnefer", 95, 0.901);
INSERT INTO student_grades (name, number_grade, fraction_completed)
VALUES ("Winsteen", 85, 0.906);
INSERT INTO student_grades (name, number_grade, fraction_completed)
VALUES ("Wincifer", 66, 0.7054);
INSERT INTO student_grades (name, number_grade, fraction_completed)
VALUES ("Winster", 76, 0.5013);
INSERT INTO student_grades (name, number_grade, fraction_completed)
VALUES ("Winstonia", 82, 0.9045);
/*Step 1
- In this first step, select all of the rows, and display the name, number_grade, and percent_completed, which you can compute by multiplying and rounding the fraction_completed column.
*/
SELECT name, number_grade, ROUND(fraction_completed * 100) as percent_completed FROM student_grades
/* Step 2
- Now, this step is a little tricky. The goal is a table that shows how many students have earned which letter_grade.
You can output the letter_grade by using CASE with the number_grade column, outputting 'A' for grades > 90, 'B' for grades > 80,
'C' for grades > 70, and 'F' otherwise. Then you can use COUNT with GROUP BY to show the number of students with each of those grades.
*/
SELECT COUNT(*),
CASE
WHEN number_grade > 90 THEN 'A'
WHEN number_grade > 80 THEN 'B'
WHEN number_grade > 70 THEN 'C'
ELSE 'F'
END as letterGrades
FROM student_grades
GROUP BY letterGrades;