Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use toyu; Database changed mysql> SELECT e.* -- step 2 -> FROM Enroll e; -- step 1 +--------+---------+-------+----------+ | 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.00 sec) mysql> SELECT s.*, e.* -- step 2 -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1 -> ; +--------+---------+---------+-------+-------+---------+---------+--------+---------+-------+----------+ | stuId | fname | lname | major | minor | credits | advisor | stuId | classId | grade | n_alerts | +--------+---------+---------+-------+-------+---------+---------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10003 | D | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1015 | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1015 | 100006 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | ENGL | 90 | 1018 | 100008 | 10007 | C- | 0 | +--------+---------+---------+-------+-------+---------+---------+--------+---------+-------+----------+ 22 rows in set (0.03 sec) mysql> SELECT s.*, e.* -- step 3 -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1: bit temp table -> WHERE s.major = 'CSCI' -- step 2: filter step 1 -> ; +--------+-------+-------+-------+-------+---------+---------+--------+---------+-------+----------+ | stuId | fname | lname | major | minor | credits | advisor | stuId | classId | grade | n_alerts | +--------+-------+-------+-------+-------+---------+---------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10003 | D | 4 | +--------+-------+-------+-------+-------+---------+---------+--------+---------+-------+----------+ 11 rows in set (0.00 sec) mysql> SELECT s.stuId, s.fname, s.lname, e.classId, e.grade -> -- step 3: get the columns -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1: bit temp table -> WHERE s.major = 'CSCI' -- step 2: filter step 1 -> ; +--------+-------+-------+---------+-------+ | stuId | fname | lname | classId | grade | +--------+-------+-------+---------+-------+ | 100000 | Tony | Hawk | 10000 | A | | 100000 | Tony | Hawk | 10001 | A | | 100000 | Tony | Hawk | 10002 | B+ | | 100000 | Tony | Hawk | 10003 | C | | 100000 | Tony | Hawk | 10004 | A- | | 100000 | Tony | Hawk | 11001 | D | | 100001 | Mary | Hawk | 10000 | NULL | | 100001 | Mary | Hawk | 10001 | A- | | 100002 | David | Hawk | 10000 | B- | | 100002 | David | Hawk | 10002 | B+ | | 100002 | David | Hawk | 10003 | D | +--------+-------+-------+---------+-------+ 11 rows in set (0.00 sec) mysql> SELECT s.stuId -> -- s.stuId, s.fname, s.lname, e.classId, e.grade -> -- step 3: get the columns -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1: bit temp table (22 rows) -> WHERE s.major = 'CSCI' -- step 2: filter step 1 (11 rows) -> GROUP BY s.stuId -- step 3: form groups by unique values of s.stuId -> ; +--------+ | stuId | +--------+ | 100000 | | 100001 | | 100002 | +--------+ 3 rows in set (0.00 sec) mysql> SELECT s.stuId -> -- s.stuId, s.fname, s.lname, e.classId, e.grade -> -- step 4: get the columns -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1: bit temp1 table (22 rows) -> WHERE s.major = 'CSCI' -- step 2: filter step 1 (11 rows) -> GROUP BY s.stuId -- step 3: form groups by unique values of s.stuId -> -- stuId = 100000 -> group #1 => 6 rows become 1 group -> 1 new row (Temp2) -> -- stuId = 100001 -> group #2 => 2 rows become 1 group -> 1 new row (Temp2) -> -- stuId = 100002 -> group #3 => 3 rows become 1 group -> 1 new row (Temp2) -> ; +--------+ | stuId | +--------+ | 100000 | | 100001 | | 100002 | +--------+ 3 rows in set (0.00 sec) mysql> mysql> SELECT s.stuId, -> COUNT(e.classId) AS `# of classes` -- COUNT: group function -> -- s.fname, s.lname, e.classId, e.grade -> -- step 4: get the columns -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1: bit temp1 table (22 rows) -> WHERE s.major = 'CSCI' -- step 2: filter step 1 (11 rows) -> GROUP BY s.stuId -- step 3: form groups by unique values of s.stuId -> -- stuId = 100000 -> group #1 => 6 rows become 1 group -> 1 new row (Temp2) -> -- stuId = 100001 -> group #2 => 2 rows become 1 group -> 1 new row (Temp2) -> -- stuId = 100002 -> group #3 => 3 rows become 1 group -> 1 new row (Temp2) -> ; +--------+--------------+ | stuId | # of classes | +--------+--------------+ | 100000 | 6 | | 100001 | 2 | | 100002 | 3 | +--------+--------------+ 3 rows in set (0.00 sec) mysql> mysql> mysql> SELECT s.stuId, s.fname, s.lname, -> COUNT(e.classId) AS `# of classes` -- COUNT: group function -> -- step 4: get the columns -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1: bit temp1 table (22 rows) -> WHERE s.major = 'CSCI' -- step 2: filter step 1 (11 rows) -> GROUP BY s.stuId , s.fname, s.lname -- step 3: form groups by unique values of s.stuId -> -- stuId = 100000 -> group #1 => 6 rows become 1 group -> 1 new row (Temp2) -> -- stuId = 100001 -> group #2 => 2 rows become 1 group -> 1 new row (Temp2) -> -- stuId = 100002 -> group #3 => 3 rows become 1 group -> 1 new row (Temp2) -> ; +--------+-------+-------+--------------+ | stuId | fname | lname | # of classes | +--------+-------+-------+--------------+ | 100000 | Tony | Hawk | 6 | | 100001 | Mary | Hawk | 2 | | 100002 | David | Hawk | 3 | +--------+-------+-------+--------------+ 3 rows in set (0.00 sec) mysql> SELECT s.stuId, s.fname, s.lname, s.major, e.classId -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1: bit temp1 table (22 rows) -> ; +--------+---------+---------+-------+---------+ | stuId | fname | lname | major | classId | +--------+---------+---------+-------+---------+ | 100000 | Tony | Hawk | CSCI | 10000 | | 100000 | Tony | Hawk | CSCI | 10001 | | 100000 | Tony | Hawk | CSCI | 10002 | | 100000 | Tony | Hawk | CSCI | 10003 | | 100000 | Tony | Hawk | CSCI | 10004 | | 100000 | Tony | Hawk | CSCI | 11001 | | 100001 | Mary | Hawk | CSCI | 10000 | | 100001 | Mary | Hawk | CSCI | 10001 | | 100002 | David | Hawk | CSCI | 10000 | | 100002 | David | Hawk | CSCI | 10002 | | 100002 | David | Hawk | CSCI | 10003 | | 100004 | Larry | Johnson | ITEC | 10003 | | 100004 | Larry | Johnson | ITEC | 10004 | | 100005 | Linda | Johnson | CINF | 10003 | | 100005 | Linda | Johnson | CINF | 10004 | | 100005 | Linda | Johnson | CINF | 10005 | | 100005 | Linda | Johnson | CINF | 10006 | | 100006 | Lillian | Johnson | CINF | 10004 | | 100006 | Lillian | Johnson | CINF | 10005 | | 100007 | Ben | Zico | NULL | 10007 | | 100007 | Ben | Zico | NULL | 10008 | | 100008 | Bill | Ching | ARTS | 10007 | +--------+---------+---------+-------+---------+ 22 rows in set (0.00 sec) mysql> SELECT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, -> s.major, e.classId -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1 -> ; +--------+-----------------+-------+---------+ | stuId | student | major | classId | +--------+-----------------+-------+---------+ | 100000 | Tony Hawk | CSCI | 10000 | | 100000 | Tony Hawk | CSCI | 10001 | | 100000 | Tony Hawk | CSCI | 10002 | | 100000 | Tony Hawk | CSCI | 10003 | | 100000 | Tony Hawk | CSCI | 10004 | | 100000 | Tony Hawk | CSCI | 11001 | | 100001 | Mary Hawk | CSCI | 10000 | | 100001 | Mary Hawk | CSCI | 10001 | | 100002 | David Hawk | CSCI | 10000 | | 100002 | David Hawk | CSCI | 10002 | | 100002 | David Hawk | CSCI | 10003 | | 100004 | Larry Johnson | ITEC | 10003 | | 100004 | Larry Johnson | ITEC | 10004 | | 100005 | Linda Johnson | CINF | 10003 | | 100005 | Linda Johnson | CINF | 10004 | | 100005 | Linda Johnson | CINF | 10005 | | 100005 | Linda Johnson | CINF | 10006 | | 100006 | Lillian Johnson | CINF | 10004 | | 100006 | Lillian Johnson | CINF | 10005 | | 100007 | Ben Zico | NULL | 10007 | | 100007 | Ben Zico | NULL | 10008 | | 100008 | Bill Ching | ARTS | 10007 | +--------+-----------------+-------+---------+ 22 rows in set (0.00 sec) mysql> 100000 | Tony Hawk | CSCI | 10000 | -> | 100000 | Tony Hawk | CSCI | 10001 | -> | 100000 | Tony Hawk | CSCI | 10002 | -> | 100000 | Tony Hawk | CSCI | 10003 | -> | 100000 | Tony Hawk | CSCI | 10004 | -> | 100000 | Tony Hawk | CSCI | 11001 ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '100000 | Tony Hawk | CSCI | 10000 | | 100000 | Tony Hawk | CSCI ' at line 1 mysql> SELECT s.stuId, CONCAT(s.fname, ' ', s.lname) AS student, -> s.major, -- step #2 -> COUNT(e.classId) AS `Number of classes` -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1 -> GROUP BY s.stuId, student, s.major -- step 3 -> ; +--------+-----------------+-------+-------------------+ | stuId | student | major | Number of classes | +--------+-----------------+-------+-------------------+ | 100000 | Tony Hawk | CSCI | 6 | | 100001 | Mary Hawk | CSCI | 2 | | 100002 | David Hawk | CSCI | 3 | | 100004 | Larry Johnson | ITEC | 2 | | 100005 | Linda Johnson | CINF | 4 | | 100006 | Lillian Johnson | CINF | 2 | | 100007 | Ben Zico | NULL | 2 | | 100008 | Bill Ching | ARTS | 1 | +--------+-----------------+-------+-------------------+ 8 rows in set (0.00 sec) mysql> SELECT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS "student", -> s.major, -- step #2 -> COUNT(e.classId) AS `Number of classes` -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1 -> GROUP BY s.stuId, student, s.major -- step 3 -> ; +--------+-----------------+-------+-------------------+ | stuId | student | major | Number of classes | +--------+-----------------+-------+-------------------+ | 100000 | Tony Hawk | CSCI | 6 | | 100001 | Mary Hawk | CSCI | 2 | | 100002 | David Hawk | CSCI | 3 | | 100004 | Larry Johnson | ITEC | 2 | | 100005 | Linda Johnson | CINF | 4 | | 100006 | Lillian Johnson | CINF | 2 | | 100007 | Ben Zico | NULL | 2 | | 100008 | Bill Ching | ARTS | 1 | +--------+-----------------+-------+-------------------+ 8 rows in set (0.03 sec) mysql> SELECT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS `student`, -> s.major, -- step #2 -> COUNT(e.classId) AS `Number of classes` -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1 -> GROUP BY s.stuId, `student`, s.major -- step 3 -> ; +--------+-----------------+-------+-------------------+ | stuId | student | major | Number of classes | +--------+-----------------+-------+-------------------+ | 100000 | Tony Hawk | CSCI | 6 | | 100001 | Mary Hawk | CSCI | 2 | | 100002 | David Hawk | CSCI | 3 | | 100004 | Larry Johnson | ITEC | 2 | | 100005 | Linda Johnson | CINF | 4 | | 100006 | Lillian Johnson | CINF | 2 | | 100007 | Ben Zico | NULL | 2 | | 100008 | Bill Ching | ARTS | 1 | +--------+-----------------+-------+-------------------+ 8 rows in set (0.00 sec) mysql> SELECT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS `student`, -> s.major, -- step #4 -> COUNT(e.classId) AS `Number of classes` -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1 -> GROUP BY s.stuId, `student`, s.major -- step 3 -> ORDER BY `Number of classes` DESC -- step $5 -> ; +--------+-----------------+-------+-------------------+ | stuId | student | major | Number of classes | +--------+-----------------+-------+-------------------+ | 100000 | Tony Hawk | CSCI | 6 | | 100005 | Linda Johnson | CINF | 4 | | 100002 | David Hawk | CSCI | 3 | | 100001 | Mary Hawk | CSCI | 2 | | 100004 | Larry Johnson | ITEC | 2 | | 100006 | Lillian Johnson | CINF | 2 | | 100007 | Ben Zico | NULL | 2 | | 100008 | Bill Ching | ARTS | 1 | +--------+-----------------+-------+-------------------+ 8 rows in set (0.00 sec) mysql> SELECT s.stuId, s.fname, s.lname, s.major, e.* -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1: bit temp1 table (22 rows) -> ; +--------+---------+---------+-------+--------+---------+-------+----------+ | stuId | fname | lname | major | stuId | classId | grade | n_alerts | +--------+---------+---------+-------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | 100002 | 10003 | D | 4 | | 100004 | Larry | Johnson | ITEC | 100004 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | 100004 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | 100005 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | 100006 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | 100007 | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | 100007 | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | 100008 | 10007 | C- | 0 | +--------+---------+---------+-------+--------+---------+-------+----------+ 22 rows in set (0.00 sec) mysql> SELECT s.*, e.* -> FROM Student s INNER JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1: bit temp1 table (22 rows) -> ; +--------+---------+---------+-------+-------+---------+---------+--------+---------+-------+----------+ | stuId | fname | lname | major | minor | credits | advisor | stuId | classId | grade | n_alerts | +--------+---------+---------+-------+-------+---------+---------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10003 | D | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1015 | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1015 | 100006 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | ENGL | 90 | 1018 | 100008 | 10007 | C- | 0 | +--------+---------+---------+-------+-------+---------+---------+--------+---------+-------+----------+ 22 rows in set (0.00 sec) mysql> SELECT s.*, e.* -> FROM Student s LEFT JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1: bit temp1 table (22 rows) -> ; +--------+-----------+---------+-------+-------+---------+---------+--------+---------+-------+----------+ | stuId | fname | lname | major | minor | credits | advisor | stuId | classId | grade | n_alerts | +--------+-----------+---------+-------+-------+---------+---------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | 100002 | 10003 | D | 4 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | 1017 | NULL | NULL | NULL | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1015 | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1015 | 100006 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | ENGL | 90 | 1018 | 100008 | 10007 | C- | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | NULL | NULL | NULL | NULL | +--------+-----------+---------+-------+-------+---------+---------+--------+---------+-------+----------+ 24 rows in set (0.00 sec) mysql> SELECT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS `student`, -> s.major, -- step #4 -> COUNT(e.classId) AS `Number of classes` -> FROM Student s LEFT JOIN Enroll e -> ON (s.stuId = e.stuId) -- step 1 -> GROUP BY s.stuId, `student`, s.major -- step 3 -> ORDER BY `Number of classes` DESC -- step #5 -> ; +--------+-----------------+-------+-------------------+ | stuId | student | major | Number of classes | +--------+-----------------+-------+-------------------+ | 100000 | Tony Hawk | CSCI | 6 | | 100005 | Linda Johnson | CINF | 4 | | 100002 | David Hawk | CSCI | 3 | | 100001 | Mary Hawk | CSCI | 2 | | 100004 | Larry Johnson | ITEC | 2 | | 100006 | Lillian Johnson | CINF | 2 | | 100007 | Ben Zico | NULL | 2 | | 100008 | Bill Ching | ARTS | 1 | | 100009 | Linda King | ARTS | 0 | | 100003 | Catherine Lim | ITEC | 0 | +--------+-----------------+-------+-------------------+ 10 rows in set (0.00 sec) mysql> mysql> SELECT sc.*, d.* -> FROM school sc INNER JOIN department d ON (d.schoolCode = sc.schoolCode); +------------+-------------------------------+----------+------------------------------+------------+------------+ | schoolCode | schoolName | deptCode | deptName | schoolCode | numFaculty | +------------+-------------------------------+----------+------------------------------+------------+------------+ | BUS | Business | ACCT | Accounting | BUS | 10 | | HSH | Human Sciences and Humanities | ARTS | Arts | HSH | 5 | | HSH | Human Sciences and Humanities | ENGL | English | HSH | 12 | | CSE | Science and Engineering | CINF | Computer Information Systems | CSE | 5 | | CSE | Science and Engineering | CSCI | Computer Science | CSE | 12 | | CSE | Science and Engineering | ITEC | Information Technology | CSE | 4 | | CSE | Science and Engineering | MATH | Mathematics | CSE | 7 | +------------+-------------------------------+----------+------------------------------+------------+------------+ 7 rows in set (0.00 sec) mysql> SELECT sc.*, d.* -> FROM school sc LEFT JOIN department d ON (d.schoolCode = sc.schoolCode); +------------+-------------------------------+----------+------------------------------+------------+------------+ | schoolCode | schoolName | deptCode | deptName | schoolCode | numFaculty | +------------+-------------------------------+----------+------------------------------+------------+------------+ | BUS | Business | ACCT | Accounting | BUS | 10 | | EDU | Education | NULL | NULL | NULL | NULL | | HSH | Human Sciences and Humanities | ARTS | Arts | HSH | 5 | | HSH | Human Sciences and Humanities | ENGL | English | HSH | 12 | | CSE | Science and Engineering | CINF | Computer Information Systems | CSE | 5 | | CSE | Science and Engineering | CSCI | Computer Science | CSE | 12 | | CSE | Science and Engineering | ITEC | Information Technology | CSE | 4 | | CSE | Science and Engineering | MATH | Mathematics | CSE | 7 | +------------+-------------------------------+----------+------------------------------+------------+------------+ 8 rows in set (0.00 sec) mysql> mysql> SELECT sc.schoolCode, -> COUNT(d.deptCode) AS `# of departments` -> FROM school sc LEFT JOIN department d ON (d.schoolCode = sc.schoolCode) -> GROUP BY sc.schoolCode; +------------+------------------+ | schoolCode | # of departments | +------------+------------------+ | BUS | 1 | | CSE | 4 | | EDU | 0 | | HSH | 2 | +------------+------------------+ 4 rows in set (0.03 sec) mysql> mysql> SELECT sc.schoolCode, sc.schoolName -> COUNT(d.deptCode) AS `# of departments` -> FROM school sc LEFT JOIN department d ON (d.schoolCode = sc.schoolCode) -> GROUP BY sc.schoolCode, sc.schoolName; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(d.deptCode) AS `# of departments` FROM school sc LEFT JOIN department d ON' at line 2 mysql> SELECT sc.schoolCode, sc.schoolName, -> COUNT(d.deptCode) AS `# of departments` -> FROM school sc LEFT JOIN department d ON (d.schoolCode = sc.schoolCode) -> GROUP BY sc.schoolCode, sc.schoolName; +------------+-------------------------------+------------------+ | schoolCode | schoolName | # of departments | +------------+-------------------------------+------------------+ | BUS | Business | 1 | | EDU | Education | 0 | | HSH | Human Sciences and Humanities | 2 | | CSE | Science and Engineering | 4 | +------------+-------------------------------+------------------+ 4 rows in set (0.00 sec) mysql> SELECT s.major, s.minor, COUNT(s.stuId) AS `count` -> FROM student s -> GROUP BY s.major, s.minor; +-------+-------+-------+ | major | minor | count | +-------+-------+-------+ | CSCI | CINF | 2 | | CSCI | ITEC | 1 | | ITEC | CINF | 1 | | ITEC | NULL | 1 | | CINF | ENGL | 1 | | CINF | ITEC | 1 | | NULL | NULL | 1 | | ARTS | ENGL | 1 | | ARTS | CSCI | 1 | +-------+-------+-------+ 9 rows in set (0.00 sec) mysql> mysql> SELECT s.major, s.minor, COUNT(s.stuId) AS `count`, -> AVF(s.credits) AS `average credits` -> FROM student s -> GROUP BY s.major, s.minor; ERROR 1305 (42000): FUNCTION toyu.AVF does not exist mysql> SELECT s.major, s.minor, COUNT(s.stuId) AS `count`, -> AVG(s.credits) AS `average credits` -> FROM student s -> GROUP BY s.major, s.minor; +-------+-------+-------+-----------------+ | major | minor | count | average credits | +-------+-------+-------+-----------------+ | CSCI | CINF | 2 | 37.5000 | | CSCI | ITEC | 1 | 66.0000 | | ITEC | CINF | 1 | 20.0000 | | ITEC | NULL | 1 | 66.0000 | | CINF | ENGL | 1 | 13.0000 | | CINF | ITEC | 1 | 18.0000 | | NULL | NULL | 1 | 16.0000 | | ARTS | ENGL | 1 | 90.0000 | | ARTS | CSCI | 1 | 125.0000 | +-------+-------+-------+-----------------+ 9 rows in set (0.04 sec) mysql> SELECT d.deptCode, d.deptName, sc.schoolName -> FROM department d INNER JOIN school sc ON (d.schoolCode = sc.schoolCode); +----------+------------------------------+-------------------------------+ | deptCode | deptName | schoolName | +----------+------------------------------+-------------------------------+ | ACCT | Accounting | Business | | ARTS | Arts | Human Sciences and Humanities | | CINF | Computer Information Systems | Science and Engineering | | CSCI | Computer Science | Science and Engineering | | ENGL | English | Human Sciences and Humanities | | ITEC | Information Technology | Science and Engineering | | MATH | Mathematics | Science and Engineering | +----------+------------------------------+-------------------------------+ 7 rows in set (0.00 sec) mysql> mysql> SELECT f.fname, f.lname, temp.deptName, temp.schoolName -> FROM faculty f, -> (SELECT d.deptCode, d.deptName, sc.schoolName -> FROM department d INNER JOIN school sc ON (d.schoolCode = sc.schoolCode)) -> AS temp -> WHERE f.facId = temp.facId; ERROR 1054 (42S22): Unknown column 'temp.facId' in 'where clause' mysql> mysql> SELECT f.fname, f.lname, temp.deptName, temp.schoolName -> FROM faculty f, -> (SELECT d.deptCode, d.deptName, sc.schoolName -> FROM department d INNER JOIN school sc ON (d.schoolCode = sc.schoolCode)) -> AS temp -> WHERE f.deptCode = temp.deptCode; +----------+----------+------------------------------+-------------------------------+ | fname | lname | deptName | schoolName | +----------+----------+------------------------------+-------------------------------+ | Jorginlo | Neymar | Accounting | Business | | Art | Allister | Arts | Human Sciences and Humanities | | Daniel | Kim | Computer Information Systems | Science and Engineering | | Andrew | Byre | Computer Information Systems | Science and Engineering | | Paul | Smith | Computer Science | Science and Engineering | | Mary | Tran | Computer Science | Science and Engineering | | David | Love | Computer Science | Science and Engineering | | Sharon | Mannes | Computer Science | Science and Engineering | | Katrina | Bajaj | English | Human Sciences and Humanities | | Deborah | Gump | Information Technology | Science and Engineering | | Benjamin | Yu | Information Technology | Science and Engineering | +----------+----------+------------------------------+-------------------------------+ 11 rows in set (0.00 sec) mysql>