SQL, The logic for grouping

Question

The SQL is executed without issue. The question is HOW the result is grouped all courses in one line for each person? Or how the SQL is executed step by step?

select name,
(select 'o' from Courses where name = c.name and course = 'SQL')as SQL,
(select 'o' from Courses where name = c.name and course = 'UNIX')as UNIX,
(select 'o' from Courses where name = c.name and course = 'Java')as Java
from Courses c
group by name

CREATE TABLE Courses
(name   VARCHAR(32), 
 course VARCHAR(32), 
 PRIMARY KEY(name, course));

INSERT INTO Courses VALUES('Tom', 'SQL');
INSERT INTO Courses VALUES('Tom', 'UNIX');
INSERT INTO Courses VALUES('Jack', 'SQL');
INSERT INTO Courses VALUES('Mike', 'SQL');
INSERT INTO Courses VALUES('Mike', 'Java');
INSERT INTO Courses VALUES('Jane', 'UNIX');
INSERT INTO Courses VALUES('Mary', 'SQL');

Answer 1

For each row in the outer query, an inner subquery is checking if a certain course exists for a given person. This approach will only work if the subqueries will always return at most one record. Also, your query will only run on databases like MySQL which tolerate non ANSI compliant GROUP BY behavior. Otherwise, your query would result in an error.

Here is a more canonical way of doing this via conditional aggregation:

SELECT
    name,
    CASE WHEN COUNT(CASE WHEN course = 'SQL' THEN 1 END) > 0  THEN 'o' END AS SQL,
    CASE WHEN COUNT(CASE WHEN course = 'UNIX' THEN 1 END) > 0 THEN 'o' END AS UNIX,
    CASE WHEN COUNT(CASE WHEN course = 'Java' THEN 1 END) > 0 THEN 'o' END AS Java
FROM Courses
GROUP BY name;