MariaDB [toyu]>
MariaDB [toyu]> SELECT s.StuId, COUNT(e.classId) AS `Number of classes`
-> FROM student AS s LEFT JOIN enroll AS e USING (stuId)
-> WHERE e.grade IS NOT NULL
-> GROUP BY s.stuId
-> HAVING `Number of classes` >= 1
-> ORDER BY`Number of classes` DESC;
+--------+-------------------+
| StuId | Number of classes |
+--------+-------------------+
| 100000 | 6 |
| 100002 | 3 |
| 100005 | 3 |
| 100004 | 2 |
| 100006 | 2 |
| 100007 | 2 |
| 100008 | 1 |
| 100001 | 1 |
+--------+-------------------+
8 rows in set (0.020 sec)
MariaDB [toyu]> SELECT s.StuId, CONCAT(s.fname, ' ', s.lname) AS student,
-> COUNT(e.classId) AS `Number of classes`
-> FROM student AS s LEFT JOIN enroll AS e USING (stuId)
-> WHERE e.grade IS NOT NULL
-> GROUP BY s.stuId
-> HAVING `Number of classes` >= 1
-> ORDER BY`Number of classes` DESC;
+--------+-----------------+-------------------+
| StuId | student | Number of classes |
+--------+-----------------+-------------------+
| 100000 | Tony Hawk | 6 |
| 100002 | David Hawk | 3 |
| 100005 | Linda Johnson | 3 |
| 100006 | Lillian Johnson | 2 |
| 100007 | Ben Zico | 2 |
| 100004 | Larry Johnson | 2 |
| 100008 | Bill Ching | 1 |
| 100001 | Mary Hawk | 1 |
+--------+-----------------+-------------------+
8 rows in set (0.004 sec)
MariaDB [toyu]>
MariaDB [toyu]> SELECT s.StuId, -- group by column
-> CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
-> ' => ',
-> COUNT(e.classId) AS `Number of classes` -- group function
-> FROM student AS s LEFT JOIN enroll AS e USING (stuId)
-> WHERE e.grade IS NOT NULL
-> GROUP BY s.stuId, student -- group by columns
-> HAVING `Number of classes` >= 1
-> ORDER BY`Number of classes` DESC;
+--------+-----------------+------+-------------------+
| StuId | student | => | Number of classes |
+--------+-----------------+------+-------------------+
| 100000 | Tony Hawk | => | 6 |
| 100002 | David Hawk | => | 3 |
| 100005 | Linda Johnson | => | 3 |
| 100006 | Lillian Johnson | => | 2 |
| 100007 | Ben Zico | => | 2 |
| 100004 | Larry Johnson | => | 2 |
| 100008 | Bill Ching | => | 1 |
| 100001 | Mary Hawk | => | 1 |
+--------+-----------------+------+-------------------+
8 rows in set (0.003 sec)
MariaDB [toyu]> SELECT s.StuId, -- group by column
-> CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
-> ' => ',
-> COUNT(e.classId) AS `Number of classes`, -- group function
-> CONCAT(e.classId, ': ', e.grade) AS classes
-> FROM student AS s LEFT JOIN enroll AS e USING (stuId)
-> WHERE e.grade IS NOT NULL
-> GROUP BY s.stuId, student -- group by columns
-> HAVING `Number of classes` >= 1
-> ORDER BY`Number of classes` DESC;
+--------+-----------------+------+-------------------+-----------+
| StuId | student | => | Number of classes | classes |
+--------+-----------------+------+-------------------+-----------+
| 100000 | Tony Hawk | => | 6 | 10000: A |
| 100002 | David Hawk | => | 3 | 10000: B- |
| 100005 | Linda Johnson | => | 3 | 10004: A- |
| 100006 | Lillian Johnson | => | 2 | 10004: C+ |
| 100007 | Ben Zico | => | 2 | 10007: F |
| 100004 | Larry Johnson | => | 2 | 10003: A |
| 100001 | Mary Hawk | => | 1 | 10001: A- |
| 100008 | Bill Ching | => | 1 | 10007: C- |
+--------+-----------------+------+-------------------+-----------+
8 rows in set (0.001 sec)
MariaDB [toyu]> select * from enroll;
+--------+---------+-------+----------+
| stuId | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100000 | 10000 | A | 0 |
| 100001 | 10000 | NULL | NULL |
| 100002 | 10000 | B- | 3 |
| 100000 | 10001 | A | 2 |
| 100001 | 10001 | A- | 0 |
| 100000 | 10002 | B+ | 1 |
| 100002 | 10002 | B+ | 2 |
| 100000 | 10003 | C | 0 |
| 100002 | 10003 | D | 4 |
| 100004 | 10003 | A | 0 |
| 100005 | 10003 | NULL | NULL |
| 100000 | 10004 | A- | 1 |
| 100004 | 10004 | B+ | NULL |
| 100005 | 10004 | A- | 0 |
| 100006 | 10004 | C+ | NULL |
| 100005 | 10005 | A- | 0 |
| 100006 | 10005 | A | NULL |
| 100005 | 10006 | B+ | NULL |
| 100007 | 10007 | F | 4 |
| 100008 | 10007 | C- | 0 |
| 100007 | 10008 | A- | 0 |
| 100000 | 11001 | D | 4 |
+--------+---------+-------+----------+
22 rows in set (0.000 sec)
MariaDB [toyu]> select * from enroll order by stuId;
+--------+---------+-------+----------+
| stuId | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100000 | 10000 | A | 0 |
| 100000 | 10004 | A- | 1 |
| 100000 | 10003 | C | 0 |
| 100000 | 10002 | B+ | 1 |
| 100000 | 11001 | D | 4 |
| 100000 | 10001 | A | 2 |
| 100001 | 10001 | A- | 0 |
| 100001 | 10000 | NULL | NULL |
| 100002 | 10003 | D | 4 |
| 100002 | 10000 | B- | 3 |
| 100002 | 10002 | B+ | 2 |
| 100004 | 10003 | A | 0 |
| 100004 | 10004 | B+ | NULL |
| 100005 | 10006 | B+ | NULL |
| 100005 | 10005 | A- | 0 |
| 100005 | 10003 | NULL | NULL |
| 100005 | 10004 | A- | 0 |
| 100006 | 10004 | C+ | NULL |
| 100006 | 10005 | A | NULL |
| 100007 | 10007 | F | 4 |
| 100007 | 10008 | A- | 0 |
| 100008 | 10007 | C- | 0 |
+--------+---------+-------+----------+
22 rows in set (0.000 sec)
MariaDB [toyu]> SELECT s.StuId, -- group by column
-> CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
-> ' => ',
-> COUNT(e.classId) AS `Number of classes`, -- group function
-> GROUP_CONCAT(CONCAT(e.classId, ': ', e.grade)) AS classes -- semantic error: not a group by column, not a group function.
-> FROM student AS s LEFT JOIN enroll AS e USING (stuId)
-> WHERE e.grade IS NOT NULL
-> GROUP BY s.stuId, student -- group by columns
-> HAVING `Number of classes` >= 1
-> ORDER BY`Number of classes` DESC;
+--------+-----------------+------+-------------------+---------------------------------------------------------+
| StuId | student | => | Number of classes | classes |
+--------+-----------------+------+-------------------+---------------------------------------------------------+
| 100000 | Tony Hawk | => | 6 | 10000: A,10001: A,10002: B+,10003: C,10004: A-,11001: D |
| 100002 | David Hawk | => | 3 | 10000: B-,10002: B+,10003: D |
| 100005 | Linda Johnson | => | 3 | 10004: A-,10005: A-,10006: B+ |
| 100004 | Larry Johnson | => | 2 | 10003: A,10004: B+ |
| 100006 | Lillian Johnson | => | 2 | 10004: C+,10005: A |
| 100007 | Ben Zico | => | 2 | 10007: F,10008: A- |
| 100001 | Mary Hawk | => | 1 | 10001: A- |
| 100008 | Bill Ching | => | 1 | 10007: C- |
+--------+-----------------+------+-------------------+---------------------------------------------------------+
8 rows in set (0.009 sec)
MariaDB [toyu]> SELECT s.StuId, -- group by column
-> CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
-> COUNT(e.classId) AS `Number of classes`, -- group function
-> GROUP_CONCAT(CONCAT(e.classId, ': ', e.grade) ORDER BY e.classId DESC) AS classes -- semantic error: not a group by column, not a group function.
-> FROM student AS s LEFT JOIN enroll AS e USING (stuId)
-> WHERE e.grade IS NOT NULL
-> GROUP BY s.stuId, student -- group by columns
-> HAVING `Number of classes` >= 1
-> ORDER BY`Number of classes` DESC;
+--------+-----------------+-------------------+---------------------------------------------------------+
| StuId | student | Number of classes | classes |
+--------+-----------------+-------------------+---------------------------------------------------------+
| 100000 | Tony Hawk | 6 | 11001: D,10004: A-,10003: C,10002: B+,10001: A,10000: A |
| 100002 | David Hawk | 3 | 10003: D,10002: B+,10000: B- |
| 100005 | Linda Johnson | 3 | 10006: B+,10005: A-,10004: A- |
| 100004 | Larry Johnson | 2 | 10004: B+,10003: A |
| 100006 | Lillian Johnson | 2 | 10005: A,10004: C+ |
| 100007 | Ben Zico | 2 | 10008: A-,10007: F |
| 100001 | Mary Hawk | 1 | 10001: A- |
| 100008 | Bill Ching | 1 | 10007: C- |
+--------+-----------------+-------------------+---------------------------------------------------------+
8 rows in set (0.015 sec)
MariaDB [toyu]> SELECT s.StuId, -- group by column
-> CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
-> COUNT(e.classId) AS `Number of classes`, -- group function
-> GROUP_CONCAT(CONCAT(e.classId, ': ', e.grade) ORDER BY e.classId DESC SEPARATOR '; ') AS classes -- semantic error: not a group by column, not a group function.
-> FROM student AS s LEFT JOIN enroll AS e USING (stuId)
-> WHERE e.grade IS NOT NULL
-> GROUP BY s.stuId, student -- group by columns
-> HAVING `Number of classes` >= 1
-> ORDER BY`Number of classes` DESC;
+--------+-----------------+-------------------+--------------------------------------------------------------+
| StuId | student | Number of classes | classes |
+--------+-----------------+-------------------+--------------------------------------------------------------+
| 100000 | Tony Hawk | 6 | 11001: D; 10004: A-; 10003: C; 10002: B+; 10001: A; 10000: A |
| 100002 | David Hawk | 3 | 10003: D; 10002: B+; 10000: B- |
| 100005 | Linda Johnson | 3 | 10006: B+; 10005: A-; 10004: A- |
| 100004 | Larry Johnson | 2 | 10004: B+; 10003: A |
| 100006 | Lillian Johnson | 2 | 10005: A; 10004: C+ |
| 100007 | Ben Zico | 2 | 10008: A-; 10007: F |
| 100001 | Mary Hawk | 1 | 10001: A- |
| 100008 | Bill Ching | 1 | 10007: C- |
+--------+-----------------+-------------------+--------------------------------------------------------------+
8 rows in set (0.009 sec)
MariaDB [toyu]>
MariaDB [toyu]>
MariaDB [toyu]> SELECT f.deptCode,
-> COUNT(f.facId) AS numFaculty
-> FROM faculty AS f
-> GROUP BY f.deptCode;
+----------+------------+
| deptCode | numFaculty |
+----------+------------+
| ACCT | 1 |
| ARTS | 1 |
| CINF | 2 |
| CSCI | 4 |
| ENGL | 1 |
| ITEC | 2 |
+----------+------------+
6 rows in set (0.002 sec)
MariaDB [toyu]> SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
-> FROM student AS s
-> GROUP BY s.major;
+----------+----------+
| deptCode | numMajor |
+----------+----------+
| NULL | 2 |
| ARTS | 2 |
| CINF | 2 |
| CSCI | 3 |
| ITEC | 2 |
+----------+----------+
5 rows in set (0.002 sec)
MariaDB [toyu]> SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
-> FROM student AS s
-> GROUP BY s.minor
->
-> ;
+----------+----------+
| deptCode | numMinor |
+----------+----------+
| NULL | 4 |
| CINF | 3 |
| CSCI | 1 |
| ENGL | 1 |
| ITEC | 2 |
+----------+----------+
5 rows in set (0.002 sec)
MariaDB [toyu]> WITH ma AS
-> (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
-> FROM student AS s
-> GROUP BY s.major),
-> mi AS
-> (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
-> FROM student AS s
-> GROUP BY s.minor),
-> f AS
-> (SELECT f.deptCode, COUNT(f.facId) AS numFaculty
-> FROM faculty AS f
-> GROUP BY f.deptCode)
-> SELECT d.deptCode,
-> d.deptName,
-> IFNULL(f.numFaculty, 0) AS numFaculty,
-> IFNULL(ma.numMajor, 0) AS numMajor,
-> IFNULL(mi.numMinor, 0) AS numMinor
-> FROM department AS d LEFT JOIN ma USING (deptCode)
-> LEFT JOIN mi USING (deptCode)
-> LEFT JOIN f USING (deptCode);
+----------+------------------------------+------------+----------+----------+
| deptCode | deptName | numFaculty | numMajor | numMinor |
+----------+------------------------------+------------+----------+----------+
| ACCT | Accounting | 1 | 0 | 0 |
| ARTS | Arts | 1 | 2 | 0 |
| CINF | Computer Information Systems | 2 | 2 | 3 |
| CSCI | Computer Science | 4 | 3 | 1 |
| ENGL | English | 1 | 0 | 1 |
| ITEC | Information Technology | 2 | 2 | 2 |
| MATH | Mathematics | 0 | 0 | 0 |
+----------+------------------------------+------------+----------+----------+
7 rows in set (0.006 sec)
MariaDB [toyu]> Adding row number and rank:
-> WITH ma AS
-> (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
-> FROM student AS s
-> GROUP BY s.major),
-> mi AS
-> (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
-> FROM student AS s
-> GROUP BY s.minor),
-> f AS
-> (SELECT f.deptCode, COUNT(f.facId) AS numFaculty
-> FROM faculty AS f
-> GROUP BY f.deptCode)
-> SELECT ROW_NUMBER() OVER () AS `#`,
-> RANK() OVER (ORDER BY f.numFaculty DESC) AS `# in descending number of faculty`,
-> d.deptCode,
-> d.deptName,
-> IFNULL(f.numFaculty, 0) AS numFaculty,
-> IFNULL(ma.numMajor, 0) AS numMajor,
-> IFNULL(mi.numMinor, 0) AS numMinor
-> FROM department AS d LEFT JOIN ma USING (deptCode)
-> LEFT JOIN mi USING (deptCode)
-> LEFT JOIN f USING (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 'Adding row number and rank:
WITH ma AS
(SELECT s.major AS deptCode, COUNT(s.s...' at line 1
MariaDB [toyu]>
MariaDB [toyu]> (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
-> FROM student AS s
-> GROUP BY s.major),
-> mi AS
-> (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
-> FROM student AS s
-> GROUP BY s.minor),
-> f AS
-> (SELECT f.deptCode, COUNT(f.facId) AS numFaculty
-> FROM faculty AS f
-> GROUP BY f.deptCode)
-> SELECT d.deptCode,
-> d.deptName,
-> IFNULL(f.numFaculty, 0) AS numFaculty,
-> IFNULL(ma.numMajor, 0) AS numMajor,
-> IFNULL(mi.numMinor, 0) AS numMinor
-> FROM department AS d LEFT JOIN ma USING (deptCode)
-> LEFT JOIN mi USING (deptCode)
-> LEFT JOIN f USING (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 '
mi AS
(SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
FROM st...' at line 3
MariaDB [toyu]> (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
-> FROM student AS s
-> GROUP BY s.major),
-> mi AS
-> (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
-> FROM student AS s
-> GROUP BY s.minor),
-> f AS
-> (SELECT f.deptCode, COUNT(f.facId) AS numFaculty
-> FROM faculty AS f
-> GROUP BY f.deptCode)
-> SELECT d.deptCode,
-> d.deptName,
-> IFNULL(f.numFaculty, 0) AS numFaculty,
-> IFNULL(ma.numMajor, 0) AS numMajor,
-> IFNULL(mi.numMinor, 0) AS numMinor
-> FROM department AS d LEFT JOIN ma USING (deptCode)
-> LEFT JOIN mi USING (deptCode)
-> LEFT JOIN f USING (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 '
mi AS
(SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
FROM st...' at line 3
MariaDB [toyu]> WITH ma AS
-> (SELECT s.major AS deptCode, COUNT(s.stuId) AS numMajor
-> FROM student AS s
-> GROUP BY s.major),
-> mi AS
-> (SELECT s.minor AS deptCode, COUNT(s.stuId) AS numMinor
-> FROM student AS s
-> GROUP BY s.minor),
-> f AS
-> (SELECT f.deptCode, COUNT(f.facId) AS numFaculty
-> FROM faculty AS f
-> GROUP BY f.deptCode)
-> SELECT d.deptCode,
-> d.deptName,
-> IFNULL(f.numFaculty, 0) AS numFaculty,
-> IFNULL(ma.numMajor, 0) AS numMajor,
-> IFNULL(mi.numMinor, 0) AS numMinor
-> FROM department AS d LEFT JOIN ma USING (deptCode)
-> LEFT JOIN mi USING (deptCode)
-> LEFT JOIN f USING (deptCode);
+----------+------------------------------+------------+----------+----------+
| deptCode | deptName | numFaculty | numMajor | numMinor |
+----------+------------------------------+------------+----------+----------+
| ACCT | Accounting | 1 | 0 | 0 |
| ARTS | Arts | 1 | 2 | 0 |
| CINF | Computer Information Systems | 2 | 2 | 3 |
| CSCI | Computer Science | 4 | 3 | 1 |
| ENGL | English | 1 | 0 | 1 |
| ITEC | Information Technology | 2 | 2 | 2 |
| MATH | Mathematics | 0 | 0 | 0 |
+----------+------------------------------+------------+----------+----------+
7 rows in set (0.001 sec)
MariaDB [toyu]> select * from ma;
ERROR 1146 (42S02): Table 'toyu.ma' doesn't exist
MariaDB [toyu]> SELECT s.StuId, -- group by column
-> CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
-> COUNT(e.classId) AS `Number of classes`, -- group function
-> GROUP_CONCAT(CONCAT(e.classId, ': ', e.grade) ORDER BY e.classId DESC SEPARATOR '; ') AS classes -- semantic error: not a group by column, not a group function.
-> FROM student AS s LEFT JOIN enroll AS e USING (stuId)
-> WHERE e.grade IS NOT NULL
-> GROUP BY s.stuId, student -- group by columns (named group by columns)
-> HAVING `Number of classes` >= 1
-> ORDER BY`Number of classes` DESC;
+--------+-----------------+-------------------+--------------------------------------------------------------+
| StuId | student | Number of classes | classes |
+--------+-----------------+-------------------+--------------------------------------------------------------+
| 100000 | Tony Hawk | 6 | 11001: D; 10004: A-; 10003: C; 10002: B+; 10001: A; 10000: A |
| 100002 | David Hawk | 3 | 10003: D; 10002: B+; 10000: B- |
| 100005 | Linda Johnson | 3 | 10006: B+; 10005: A-; 10004: A- |
| 100004 | Larry Johnson | 2 | 10004: B+; 10003: A |
| 100006 | Lillian Johnson | 2 | 10005: A; 10004: C+ |
| 100007 | Ben Zico | 2 | 10008: A-; 10007: F |
| 100001 | Mary Hawk | 1 | 10001: A- |
| 100008 | Bill Ching | 1 | 10007: C- |
+--------+-----------------+-------------------+--------------------------------------------------------------+
8 rows in set (0.022 sec)
MariaDB [toyu]> SELECT s.StuId, -- group by column
-> CONCAT(s.fname, ' ', s.lname) AS student, -- group by columns
-> COUNT(e.classId) AS `Number of classes`, -- group function
-> GROUP_CONCAT(CONCAT(e.classId, ': ', e.grade) ORDER BY e.classId DESC SEPARATOR '; ') AS classes -- semantic error: not a group by column, not a group function.
-> FROM student AS s LEFT JOIN enroll AS e USING (stuId)
-> WHERE e.grade IS NOT NULL
-> GROUP BY 1, 2 -- group by columns (positional colum ns)
-> HAVING `Number of classes` >= 1
-> ORDER BY`Number of classes` DESC;
+--------+-----------------+-------------------+--------------------------------------------------------------+
| StuId | student | Number of classes | classes |
+--------+-----------------+-------------------+--------------------------------------------------------------+
| 100000 | Tony Hawk | 6 | 11001: D; 10004: A-; 10003: C; 10002: B+; 10001: A; 10000: A |
| 100002 | David Hawk | 3 | 10003: D; 10002: B+; 10000: B- |
| 100005 | Linda Johnson | 3 | 10006: B+; 10005: A-; 10004: A- |
| 100004 | Larry Johnson | 2 | 10004: B+; 10003: A |
| 100006 | Lillian Johnson | 2 | 10005: A; 10004: C+ |
| 100007 | Ben Zico | 2 | 10008: A-; 10007: F |
| 100001 | Mary Hawk | 1 | 10001: A- |
| 100008 | Bill Ching | 1 | 10007: C- |
+--------+-----------------+-------------------+--------------------------------------------------------------+
8 rows in set (0.020 sec)
MariaDB [toyu]> SELECT CONCAT (s.fname, ' ', s.lname) AS student,
-> d.deptName,
-> CONCAT(f.fname, ' ', f.lname) as advisor
-> FROM student AS s LEFT JOIN department AS d
-> ON (s.major = d.deptCode)
-> LEFT JOIN faculty AS f
-> ON (s.advisor = f.facId);
+-----------------+------------------------------+--------------+
| student | deptName | advisor |
+-----------------+------------------------------+--------------+
| Tony Hawk | Computer Science | Paul Smith |
| Mary Hawk | Computer Science | Paul Smith |
| David Hawk | Computer Science | Mary Tran |
| Catherine Lim | Information Technology | NULL |
| Larry Johnson | Information Technology | Deborah Gump |
| Linda Johnson | Computer Information Systems | Daniel Kim |
| Lillian Johnson | Computer Information Systems | Andrew Byre |
| Ben Zico | NULL | NULL |
| Bill Ching | Arts | NULL |
| Linda King | Arts | Art Allister |
| Cathy Johanson | NULL | Art Allister |
+-----------------+------------------------------+--------------+
11 rows in set (0.001 sec)
MariaDB [toyu]> SELECT CONCAT (s.fname, ' ', s.lname) AS student,
-> d.deptName,
-> CONCAT(f.fname, ' ', f.lname) as advisor
-> FROM student AS s LEFT JOIN department AS d
-> ON (s.major = d.deptCode)
-> LEFT JOIN faculty AS f
-> ON (s.advisor = f.facId)
-> WHERE d.schoolCode = 'CSE';
+-----------------+------------------------------+--------------+
| student | deptName | advisor |
+-----------------+------------------------------+--------------+
| Linda Johnson | Computer Information Systems | Daniel Kim |
| Lillian Johnson | Computer Information Systems | Andrew Byre |
| Tony Hawk | Computer Science | Paul Smith |
| Mary Hawk | Computer Science | Paul Smith |
| David Hawk | Computer Science | Mary Tran |
| Catherine Lim | Information Technology | NULL |
| Larry Johnson | Information Technology | Deborah Gump |
+-----------------+------------------------------+--------------+
7 rows in set (0.001 sec)
MariaDB [toyu]>
MariaDB [toyu]> SELECT CONCAT (s.fname, ' ', s.lname) AS student,
-> d.deptName,
-> CONCAT(f.fname, ' ', f.lname) as advisor
-> FROM student AS s LEFT JOIN department AS d
-> ON (s.major = d.deptCode)
-> LEFT JOIN faculty AS f
-> ON (s.advisor = f.facId)
-> WHERE d.schoolCode = 'CoB';
Empty set (0.002 sec)
MariaDB [toyu]> SELECT CONCAT (s.fname, ' ', s.lname) AS student,
-> d.deptName,
-> CONCAT(f.fname, ' ', f.lname) as advisor
-> FROM student AS s LEFT JOIN department AS d
-> ON (s.major = d.deptCode)
-> LEFT JOIN faculty AS f
-> ON (s.advisor = f.facId)
-> WHERE d.schoolCode = 'HSH';
+------------+----------+--------------+
| student | deptName | advisor |
+------------+----------+--------------+
| Bill Ching | Arts | NULL |
| Linda King | Arts | Art Allister |
+------------+----------+--------------+
2 rows in set (0.001 sec)
MariaDB [toyu]>