1280-StudentsAndExaminations.sql

-- 1280. Students and Examinations
-- Table: Students
-- +---------------+---------+
-- | Column Name   | Type    |
-- +---------------+---------+
-- | student_id    | int     |
-- | student_name  | varchar |
-- +---------------+---------+
-- student_id is the primary key (column with unique values) for this table.
-- Each row of this table contains the ID and the name of one student in the school.
 
-- Table: Subjects
-- +--------------+---------+
-- | Column Name  | Type    |
-- +--------------+---------+
-- | subject_name | varchar |
-- +--------------+---------+
-- subject_name is the primary key (column with unique values) for this table.
-- Each row of this table contains the name of one subject in the school.
 
-- Table: Examinations
-- +--------------+---------+
-- | Column Name  | Type    |
-- +--------------+---------+
-- | student_id   | int     |
-- | subject_name | varchar |
-- +--------------+---------+
-- There is no primary key (column with unique values) for this table. It may contain duplicates.
-- Each student from the Students table takes every course from the Subjects table.
-- Each row of this table indicates that a student with ID student_id attended the exam of subject_name.
 
-- Write a solution to find the number of times each student attended each exam.
-- Return the result table ordered by student_id and subject_name.
-- The result format is in the following example.

-- Example 1:
-- Input: 
-- Students table:
-- +------------+--------------+
-- | student_id | student_name |
-- +------------+--------------+
-- | 1          | Alice        |
-- | 2          | Bob          |
-- | 13         | John         |
-- | 6          | Alex         |
-- +------------+--------------+
-- Subjects table:
-- +--------------+
-- | subject_name |
-- +--------------+
-- | Math         |
-- | Physics      |
-- | Programming  |
-- +--------------+
-- Examinations table:
-- +------------+--------------+
-- | student_id | subject_name |
-- +------------+--------------+
-- | 1          | Math         |
-- | 1          | Physics      |
-- | 1          | Programming  |
-- | 2          | Programming  |
-- | 1          | Physics      |
-- | 1          | Math         |
-- | 13         | Math         |
-- | 13         | Programming  |
-- | 13         | Physics      |
-- | 2          | Math         |
-- | 1          | Math         |
-- +------------+--------------+
-- Output: 
-- +------------+--------------+--------------+----------------+
-- | student_id | student_name | subject_name | attended_exams |
-- +------------+--------------+--------------+----------------+
-- | 1          | Alice        | Math         | 3              |
-- | 1          | Alice        | Physics      | 2              |
-- | 1          | Alice        | Programming  | 1              |
-- | 2          | Bob          | Math         | 1              |
-- | 2          | Bob          | Physics      | 0              |
-- | 2          | Bob          | Programming  | 1              |
-- | 6          | Alex         | Math         | 0              |
-- | 6          | Alex         | Physics      | 0              |
-- | 6          | Alex         | Programming  | 0              |
-- | 13         | John         | Math         | 1              |
-- | 13         | John         | Physics      | 1              |
-- | 13         | John         | Programming  | 1              |
-- +------------+--------------+--------------+----------------+
-- Explanation: 
-- The result table should contain all students and all subjects.
-- Alice attended the Math exam 3 times, the Physics exam 2 times, and the Programming exam 1 time.
-- Bob attended the Math exam 1 time, the Programming exam 1 time, and did not attend the Physics exam.
-- Alex did not attend any exams.
-- John attended the Math exam 1 time, the Physics exam 1 time, and the Programming exam 1 time.

-- Create table If Not Exists Students (student_id int, student_name varchar(20))
-- Create table If Not Exists Subjects (subject_name varchar(20))
-- Create table If Not Exists Examinations (student_id int, subject_name varchar(20))
-- Truncate table Students
-- insert into Students (student_id, student_name) values ('1', 'Alice')
-- insert into Students (student_id, student_name) values ('2', 'Bob')
-- insert into Students (student_id, student_name) values ('13', 'John')
-- insert into Students (student_id, student_name) values ('6', 'Alex')
-- Truncate table Subjects
-- insert into Subjects (subject_name) values ('Math')
-- insert into Subjects (subject_name) values ('Physics')
-- insert into Subjects (subject_name) values ('Programming')
-- Truncate table Examinations
-- insert into Examinations (student_id, subject_name) values ('1', 'Math')
-- insert into Examinations (student_id, subject_name) values ('1', 'Physics')
-- insert into Examinations (student_id, subject_name) values ('1', 'Programming')
-- insert into Examinations (student_id, subject_name) values ('2', 'Programming')
-- insert into Examinations (student_id, subject_name) values ('1', 'Physics')
-- insert into Examinations (student_id, subject_name) values ('1', 'Math')
-- insert into Examinations (student_id, subject_name) values ('13', 'Math')
-- insert into Examinations (student_id, subject_name) values ('13', 'Programming')
-- insert into Examinations (student_id, subject_name) values ('13', 'Physics')
-- insert into Examinations (student_id, subject_name) values ('2', 'Math')
-- insert into Examinations (student_id, subject_name) values ('1', 'Math')

-- Write your MySQL query statement below
SELECT 
    s.student_id,
    s.student_name,
    s.subject_name,
    IFNULL(e.attended_exams,0) AS attended_exams
FROM
    (
        SELECT 
            a.student_id,
            a.student_name,
            b.subject_name
        FROM
            Students AS a,
            Subjects AS b
    ) AS s -- 组合成一张 学生 & 每科的全表
LEFT JOIN
    (
        SELECT 
            student_id,
            subject_name,
            count(*) AS attended_exams 
        FROM 
            Examinations 
        GROUP BY
            student_id,subject_name
    ) AS e -- 统计 学生的每科的考试次数 
ON
    s.student_id = e.student_id AND
    s.subject_name = e.subject_name
ORDER BY 
    s.student_id ASC, s.subject_name ASC