tee 2025_10_6_sql_log.txt

SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s, enroll AS e, class AS c
WHERE s.stuId = e.stuId -- Join condition
AND e.classId = c.classId -- Join condition
AND c.semester = 'Fall' -- problem condition
AND c.year = 2019; -- problem condition

SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -- Join condition
    INNER JOIN class AS c ON (e.classId = c.classId) -- Join condition
WHERE c.semester = 'Fall' -- Problem condition
AND c.year = 2019; -- Problem condition

-- alternative: using the USING clause.
SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s INNER JOIN enroll e USING (stuId) -- Join condition
    INNER JOIN class AS c USING (classId) -- Join condition
WHERE c.semester = 'Fall' -- Problem condition
AND c.year = 2019; -- Problem condition

-- the ON clause is more general and can be more effective.
SELECT DISTINCT s.fname, s.lname, c.classId, e.grade
FROM student AS s INNER JOIN enroll e ON (s.stuId = e.stuId) -- Join condition
    INNER JOIN class AS c
   ON (e.classId = c.classId -- Join condition
      AND c.semester = 'Fall' -- Problem condition
      AND c.year = 2019); -- Problem condition

-- List the names of the students with their minors (in full name).
-- Student with no department not listed.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student AS s INNER JOIN department AS d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`, s.stuId, d.deptCode, d.schoolCode	
FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
-- more readable form.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    IFNULL (d.deptName, 'N/A') AS `minor department`
FROM student s LEFT JOIN department d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
-- Student with no department not listed.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student AS s INNER JOIN department AS d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    d.deptName AS `minor department`
FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode);

-- List the names of the students with their minors (in full name).
-- more readable form.
SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student,
    IFNULL (d.deptName, 'N/A') AS `minor department`
FROM student s LEFT JOIN department d ON (s.minor = d.deptCode);