-
Notifications
You must be signed in to change notification settings - Fork 0
/
Challenge: Gradebook
62 lines (49 loc) · 1.9 KB
/
Challenge: Gradebook
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
/*
We've created a database to track student grades, with their name, number grade, and what percent of activities they've completed. 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.
*/
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);
SELECT name, number_grade,
ROUND(fraction_completed * 100)
AS percent_completed
FROM student_grades;
/*
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_gradecolumn, 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 name, number_grade ,
CASE
WHEN number_grade > 90 THEN 'A'
WHEN number_grade > 80 THEN 'B'
WHEN number_grade > 70 THEN 'C'
ELSE 'F'
END
AS letter_grade
FROM student_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 letter_grade
FROM student_grades
GROUP BY letter_grade;