칸아카데미>>SQL>>gradebook

<< 칸아카데미의  gradebook을 학습한 내용입니다. 칸선생님 감사합니다. by 해인>>

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

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.

SELECT name, number_grade, ROUND(fraction_completed * 100) AS percent_completed FROM student_grades;

Step 2

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 thename, number_grade, and percent_completed, which you can compute by multiplying and rounding the fraction_completed column.

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";

<전체출력>

SELECT name, number_grade FROM student_grades ;

<잘못된 코드>

SELECT name, number_grade, ROUND(number_grade*fraction_completed) AS percent_completed FROM student_grades
GROUP BY name 
HAVING percent_completed;

DATABASE SCHEMA

student_grades6 rows
id (PK)INTEGER
nameTEXT
number_gradeINTEGER
fraction_completedREAL

RESULTS

name	number_grade	percent_completed
Winston	90	81
Winnefer	95	90
Winsteen	85	91
Wincifer	66	71
Winster	76	50
Winstonia	82	90

COUNT(*)	letter_grade
1	A
3	B
1	C
1	F