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.002 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.001 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]> Execute the following code and ensure that you understand the result. -> -- 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); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'following code and ensure that you understand the result. SELECT DISTINCT C...' at line 1 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.001 sec) MariaDB [toyu]> Execute the following code and ensure that you understand the result. -> -- 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); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'following code and ensure that you understand the result. SELECT DISTINCT C...' at line 1 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.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.000 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.000 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.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT CONCAT(s.fname, ' ', s.lname) AS student, -> d.deptName AS `minor department`, s.stuId, d.deptCode, d.schoolCode, d.numStaff -> FROM student AS s LEFT JOIN department AS d ON (s.minor = d.deptCode); +-----------------+------------------------------+--------+----------+------------+----------+ | student | minor department | stuId | deptCode | schoolCode | numStaff | +-----------------+------------------------------+--------+----------+------------+----------+ | Tony Hawk | Computer Information Systems | 100000 | CINF | CSE | 5 | | Mary Hawk | Computer Information Systems | 100001 | CINF | CSE | 5 | | David Hawk | Information Technology | 100002 | ITEC | CSE | 4 | | Catherine Lim | Computer Information Systems | 100003 | CINF | CSE | 5 | | Larry Johnson | NULL | 100004 | NULL | NULL | NULL | | Linda Johnson | English | 100005 | ENGL | HSH | 12 | | Lillian Johnson | Information Technology | 100006 | ITEC | CSE | 4 | | Ben Zico | NULL | 100007 | NULL | NULL | NULL | | Bill Ching | NULL | 100008 | NULL | NULL | NULL | | Linda King | Computer Science | 100009 | CSCI | CSE | 12 | | Cathy Johanson | NULL | 100111 | NULL | NULL | NULL | +-----------------+------------------------------+--------+----------+------------+----------+ 11 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> notee