LeetCode SQL - Missing Values

Posted on Edited on

1907. Count Salary Categories

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DROP TABLE if EXISTS Accounts;
CREATE TABLE
    If NOT EXISTS Accounts (account_id INT, income INT);
TRUNCATE TABLE Accounts;
INSERT INTO
    Accounts (account_id, income)
VALUES
    ('3', '108939');
INSERT INTO
    Accounts (account_id, income)
VALUES
    ('2', '12747');
INSERT INTO
    Accounts (account_id, income)
VALUES
    ('8', '87709');
INSERT INTO
    Accounts (account_id, income)
VALUES
    ('6', '91796');
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
WITH
    cte AS (
        SELECT
            account_id,
            income,
            CASE
                WHEN income < 20000 THEN 'Low Salary'
                WHEN income > 50000 THEN 'High Salary'
                ELSE 'Average Salary'
            END AS Category
        FROM
            Accounts
    ),
    categories (category) AS (
        SELECT
            'Low Salary'
        UNION
        SELECT
            'Average Salary'
        UNION
        SELECT
            'High Salary'
    )
SELECT
    c.category,
    IFNULL (COUNT(cte.Category), 0) AS accounts_count
FROM
    categories c
    LEFT JOIN cte USING (category)
GROUP BY
    1

1280. Students and Examinations

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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');
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT s.student_id,
    s.student_name,
    sub.subject_name,
    COUNT(e.subject_name) AS attended_exams
FROM Students s
    INNER JOIN Subjects sub
    LEFT JOIN Examinations e ON s.student_id = e.student_id
    AND sub.subject_name = e.subject_name
GROUP BY 1,
    2,
    3
ORDER BY 1,
    3