MariaDB [toyu]> 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 +---------+---------+---------+-------+ | fname | lname | classId | grade | +---------+---------+---------+-------+ | Tony | Hawk | 10000 | A | | Mary | Hawk | 10000 | NULL | | David | Hawk | 10000 | B- | | Tony | Hawk | 10001 | A | | Mary | Hawk | 10001 | A- | | Tony | Hawk | 10002 | B+ | | David | Hawk | 10002 | B+ | | Tony | Hawk | 10003 | C | | David | Hawk | 10003 | D | | Larry | Johnson | 10003 | A | | Linda | Johnson | 10003 | NULL | | Tony | Hawk | 10004 | A- | | Larry | Johnson | 10004 | B+ | | Linda | Johnson | 10004 | A- | | Lillian | Johnson | 10004 | C+ | | Linda | Johnson | 10005 | A- | | Lillian | Johnson | 10005 | A | | Linda | Johnson | 10006 | B+ | | Ben | Zico | 10007 | F | | Bill | Ching | 10007 | C- | | Ben | Zico | 10008 | A- | +---------+---------+---------+-------+ 21 rows in set (0.028 sec) MariaDB [toyu]> MariaDB [toyu]> 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 +---------+---------+---------+-------+ | fname | lname | classId | grade | +---------+---------+---------+-------+ | Tony | Hawk | 10000 | A | | Mary | Hawk | 10000 | NULL | | David | Hawk | 10000 | B- | | Tony | Hawk | 10001 | A | | Mary | Hawk | 10001 | A- | | Tony | Hawk | 10002 | B+ | | David | Hawk | 10002 | B+ | | Tony | Hawk | 10003 | C | | David | Hawk | 10003 | D | | Larry | Johnson | 10003 | A | | Linda | Johnson | 10003 | NULL | | Tony | Hawk | 10004 | A- | | Larry | Johnson | 10004 | B+ | | Linda | Johnson | 10004 | A- | | Lillian | Johnson | 10004 | C+ | | Linda | Johnson | 10005 | A- | | Lillian | Johnson | 10005 | A | | Linda | Johnson | 10006 | B+ | | Ben | Zico | 10007 | F | | Bill | Ching | 10007 | C- | | Ben | Zico | 10008 | A- | +---------+---------+---------+-------+ 21 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- alternative: using the USING clause. MariaDB [toyu]> 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 +---------+---------+---------+-------+ | fname | lname | classId | grade | +---------+---------+---------+-------+ | Tony | Hawk | 10000 | A | | Mary | Hawk | 10000 | NULL | | David | Hawk | 10000 | B- | | Tony | Hawk | 10001 | A | | Mary | Hawk | 10001 | A- | | Tony | Hawk | 10002 | B+ | | David | Hawk | 10002 | B+ | | Tony | Hawk | 10003 | C | | David | Hawk | 10003 | D | | Larry | Johnson | 10003 | A | | Linda | Johnson | 10003 | NULL | | Tony | Hawk | 10004 | A- | | Larry | Johnson | 10004 | B+ | | Linda | Johnson | 10004 | A- | | Lillian | Johnson | 10004 | C+ | | Linda | Johnson | 10005 | A- | | Lillian | Johnson | 10005 | A | | Linda | Johnson | 10006 | B+ | | Ben | Zico | 10007 | F | | Bill | Ching | 10007 | C- | | Ben | Zico | 10008 | A- | +---------+---------+---------+-------+ 21 rows in set (0.002 sec) MariaDB [toyu]> MariaDB [toyu]> -- the ON clause is more general and can be more effective. MariaDB [toyu]> 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 +---------+---------+---------+-------+ | fname | lname | classId | grade | +---------+---------+---------+-------+ | Tony | Hawk | 10000 | A | | Mary | Hawk | 10000 | NULL | | David | Hawk | 10000 | B- | | Tony | Hawk | 10001 | A | | Mary | Hawk | 10001 | A- | | Tony | Hawk | 10002 | B+ | | David | Hawk | 10002 | B+ | | Tony | Hawk | 10003 | C | | David | Hawk | 10003 | D | | Larry | Johnson | 10003 | A | | Linda | Johnson | 10003 | NULL | | Tony | Hawk | 10004 | A- | | Larry | Johnson | 10004 | B+ | | Linda | Johnson | 10004 | A- | | Lillian | Johnson | 10004 | C+ | | Linda | Johnson | 10005 | A- | | Lillian | Johnson | 10005 | A | | Linda | Johnson | 10006 | B+ | | Ben | Zico | 10007 | F | | Bill | Ching | 10007 | C- | | Ben | Zico | 10008 | A- | +---------+---------+---------+-------+ 21 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- List the names of the students with their minors (in full name). MariaDB [toyu]> -- Student with no department not listed. MariaDB [toyu]> 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); +-----------------+------------------------------+ | student | minor department | +-----------------+------------------------------+ | Tony Hawk | Computer Information Systems | | Mary Hawk | Computer Information Systems | | David Hawk | Information Technology | | Catherine Lim | Computer Information Systems | | Linda Johnson | English | | Lillian Johnson | Information Technology | | Linda King | Computer Science | +-----------------+------------------------------+ 7 rows in set (0.003 sec) MariaDB [toyu]> MariaDB [toyu]> -- List the names of the students with their minors (in full name). MariaDB [toyu]> 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); +-----------------+------------------------------+ | student | minor department | +-----------------+------------------------------+ | Tony Hawk | Computer Information Systems | | Mary Hawk | Computer Information Systems | | David Hawk | Information Technology | | Catherine Lim | Computer Information Systems | | Larry Johnson | NULL | | Linda Johnson | English | | Lillian Johnson | Information Technology | | Ben Zico | NULL | | Bill Ching | NULL | | Linda King | Computer Science | | Cathy Johanson | NULL | +-----------------+------------------------------+ 11 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- List the names of the students with their minors (in full name). MariaDB [toyu]> -- more readable form. MariaDB [toyu]> 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); +-----------------+------------------------------+ | student | minor department | +-----------------+------------------------------+ | Tony Hawk | Computer Information Systems | | Mary Hawk | Computer Information Systems | | David Hawk | Information Technology | | Catherine Lim | Computer Information Systems | | Larry Johnson | N/A | | Linda Johnson | English | | Lillian Johnson | Information Technology | | Ben Zico | N/A | | Bill Ching | N/A | | Linda King | Computer Science | | Cathy Johanson | N/A | +-----------------+------------------------------+ 11 rows in set (0.002 sec) MariaDB [toyu]> -- List the names of the students with their minors (in full name). MariaDB [toyu]> 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); +-----------------+------------------------------+--------+----------+------------+ | student | minor department | stuId | deptCode | schoolCode | +-----------------+------------------------------+--------+----------+------------+ | Tony Hawk | Computer Information Systems | 100000 | CINF | CSE | | Mary Hawk | Computer Information Systems | 100001 | CINF | CSE | | David Hawk | Information Technology | 100002 | ITEC | CSE | | Catherine Lim | Computer Information Systems | 100003 | CINF | CSE | | Larry Johnson | NULL | 100004 | NULL | NULL | | Linda Johnson | English | 100005 | ENGL | HSH | | Lillian Johnson | Information Technology | 100006 | ITEC | CSE | | Ben Zico | NULL | 100007 | NULL | NULL | | Bill Ching | NULL | 100008 | NULL | NULL | | Linda King | Computer Science | 100009 | CSCI | CSE | | Cathy Johanson | NULL | 100111 | NULL | NULL | +-----------------+------------------------------+--------+----------+------------+ 11 rows in set (0.001 sec) MariaDB [toyu]> notee