SELECT CONCAT('A', 'B', 'C');

SELECT CONCAT('A', 'BXY', 'CDE');

SELECT CONCAT('firstname: ', fname, ' lastname: ', lname)
    AS `student`
FROM student;

-- Output: student: s.fname, ' ', s.lname
-- Source: student s,  Enroll e (join condition)
-- Condition: classid in enroll #10000 and #10004.

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE e.classId = 10000 AND 10004;

-- erronous: data type mismatch: 
-- e.classId = 10000 AND 10004 (10004 an int, not a Boolean value)
-- TruthValue AND TruthValue (T or F)
-- MySQL; implicit convert 10004 to True.
-- (e.classId = 10000 AND 10004) = (e.classId = 10000 AND True)
-- = e.classId = 10000


-- Mind the data type.

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE e.classId = 10000 
AND e.classId = 10004;

-- empty set.
-- (e.classId = 10000 AND e.classId = 10004) -> false.

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE e.classId = 10000 
OR e.classId = 10004;

-- Output: student: s.fname, ‘ ‘, s.lname
-- Source: student s, Enroll e1, Enroll e2
-- Condition: join conditions
-- 1.	E1.classId = 10000
-- 2.	E2.classId = 10004

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
FROM student s INNER JOIN enroll e1 ON (s.stuId = e1.stuId)
    INNER JOIN enroll e2 ON (s.stuId = e2.stuId)
WHERE e1.classId = 10000 
AND e2.classId = 10004;

-- 7a.  List the names of students taking both classes id #10000
-- Output: student: s.fname, ' ', s.lname
-- Source: student s,  Enroll e (join condition)
-- Condition: classid in enroll #10000.

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE e.classId = 10000;

-- 7b.  List the names of students taking both classes id #10000
-- Output: student: s.fname, ' ', s.lname
-- Source: student s,  Enroll e (join condition)
-- Condition: classid in enroll #10004.

SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE e.classId = 10004;

-- 7. 7a intersect 7b
(SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE e.classId = 10000)
INTERSECT
(SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE e.classId = 10004);  -- no.

SELECT t_a.`student`  -- step 3: main query
FROM (SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
      FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
      WHERE e.classId = 10000) AS t_a,  -- step 1, subquery
     (SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS `student`
      FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
      WHERE e.classId = 10004) AS t_b -- step 2
WHERE t_a.`student` =  t_b.`student`;