/database_course/quiz.md

1. Create a Table and Insert Data

SQL Statements:

-- Create the students table
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    major VARCHAR(50)
);

-- Insert data into the students table
INSERT INTO students (id, name, age, major) VALUES 
(1, 'Alice', 20, 'Computer Science'),
(2, 'Bob', 21, 'Mathematics'),
(3, 'Charlie', 22, 'Physics'),
(4, 'Diana', 23, 'Computer Science'),
(5, 'Edward', 21, 'Mathematics');

2. Query Students by Major

(a) SQL Statement:

SELECT name, age 
FROM students 
WHERE major = 'Computer Science';

(b) Database Execution Workflow:

FROM: 从表 students 中读取所有数据，形成一个简单表。
WHERE: 对简单表的每一行判断 major 是否等于 'Computer Science'，筛选出符合条件的行，形成一个新的中间结果简单表。
SELECT: 从中间结果表中逐行取出 name 和 age 列，形成最终结果表。
Return Results: 返回最终结果表中的数据。

3. Count Students per Major

(a) SQL Statement:

SELECT major, COUNT(*) AS student_count 
FROM students 
GROUP BY major 
ORDER BY student_count DESC;

(b) Database Execution Workflow:

FROM: 从表 students 中读取所有数据，形成一个简单表。
GROUP BY: 按 major 列进行分组，形成一个分组后的复杂表，其中每一组包含相同专业的所有行。
SELECT: 针对每个分组逐一计算：
- major：每组的分组键。
- COUNT(*)：统计每组的行数。生成一个新的结果表，包含 major 和 student_count 列。
ORDER BY: 按 student_count 列降序排列结果表中的行。
Return Results: 返回排序后的最终结果表。

4. Query Students Younger than the Average Age

(a) SQL Statement:

SELECT name, major 
FROM students as s1
WHERE age < (
    SELECT AVG(age) 
    FROM students AS s2 
    WHERE s2.major = s1.major
);

(b) Database Execution Workflow:

Main Query - FROM:
- 从表 students 中读取所有数据，形成一个简单表。
Main Query - WHERE:
- Subquery Execution (SELECT AVG):
  - FROM: 从表 students（使用别名 s2）中读取数据。
  - WHERE: 筛选出 major 等于当前主查询中学生的 major 的行，形成一个中间结果表。
  - SELECT: 针对中间结果表计算 age 的平均值，返回一个标量值（该专业的平均年龄）。
- 针对主查询的每一行，对 age 判断是否小于子查询返回的平均年龄值。
- 筛选出符合条件的行，形成一个新的中间结果简单表。
SELECT:
- 从中间结果表中逐行取出 name 和 major 列，形成最终结果表。
Return Results:
- 将最终结果表返回给用户。

5. Create a Courses Table

-- Create the courses table with a foreign key constraint
CREATE TABLE courses (
    student_id INT,
    course_name VARCHAR(50),
    grade INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

-- Insert data into the courses table
INSERT INTO courses (student_id, course_name, grade) VALUES 
(1, 'Database', 85),
(1, 'Mathematics', 90),
(2, 'Database', 78),
(2, 'Physics', 88),
(3, 'Mathematics', 70),
(3, 'Physics', 95);

6. Create a View and Query Student Statistics

(a) Create the View:

-- Create the student_statistics view
CREATE VIEW student_statistics AS
SELECT 
    student_id, 
    COUNT(course_name) AS total_courses, 
    AVG(grade) AS average_grade
FROM courses
GROUP BY student_id;

(b) Query Students with Average Grade > 80 Using Subquery:

SELECT id AS student_id, name, 
       (SELECT average_grade 
        FROM student_statistics 
        WHERE student_statistics.student_id = students.id) AS average_grade
FROM students
WHERE (SELECT average_grade 
       FROM student_statistics 
       WHERE student_statistics.student_id = students.id) > 80;

Database Execution Workflow: （1） View Execution (student_statistics):

FROM: 从表 courses 中读取所有数据，形成一个简单表。
GROUP BY: 按 student_id 分组，形成一个分组后的复杂表。
SELECT: 针对每个分组逐一计算：
- student_id：分组键。
- COUNT(course_name)：每组的课程数。
- AVG(grade)：每组的平均成绩。生成 student_statistics 视图的结果表。

（2） Main Query - FROM:

FROM: 从表 students 中读取所有数据，形成一个简单表。

（3）Subquery Execution (SELECT average_grade):

针对 students 表的每一行，执行子查询：
- Subquery FROM: 从 student_statistics 读取数据。
- Subquery WHERE: 根据条件 student_statistics.student_id = students.id 匹配当前学生的统计信息，取出对应的 average_grade。
- 返回单个值（该学生的平均成绩）。

（4） WHERE:

对每一行结果执行条件判断：检查子查询返回的 average_grade 是否大于 80。
筛选出符合条件的行，形成新的中间结果简单表。

（5）SELECT:

从中间结果表中逐行取出 id（student_id）、name 和子查询返回的 average_grade，形成最终结果表。

（6）Return Results:

将最终结果表返回给用户。