MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | swim | | test | | toyu | +--------------------+ 7 rows in set (0.002 sec) MariaDB [(none)]> SELECT DISTINCT s.stuId, -> s.fname & ' ' & s.lname AS student, -> s.major, -> s.advisor AS advisorId, -> -- output -> FROM student AS s; 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 'FROM student AS s' at line 6 MariaDB [(none)]> select * from student; ERROR 1046 (3D000): No database selected MariaDB [(none)]> use toyu; Database changed MariaDB [toyu]> select * from student; +--------+-----------+----------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+----------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | +--------+-----------+----------+-------+-------+------+---------+ 11 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.stuId, -> s.fname & ' ' & s.lname AS student, -> s.major, -> s.advisor AS advisorId, -> -- output -> FROM student AS s; 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 'FROM student AS s' at line 6 MariaDB [toyu]> SELECT DISTINCT s.stuId, -> s.fname & ' ' & s.lname AS student, -> s.major, -> s.advisor AS advisorId -> -- output -> FROM student AS s; +--------+---------+-------+-----------+ | stuId | student | major | advisorId | +--------+---------+-------+-----------+ | 100000 | 0 | CSCI | 1011 | | 100001 | 0 | CSCI | 1011 | | 100002 | 0 | CSCI | 1012 | | 100003 | 0 | ITEC | NULL | | 100004 | 0 | ITEC | 1017 | | 100005 | 0 | CINF | 1015 | | 100006 | 0 | CINF | 1016 | | 100007 | 0 | NULL | NULL | | 100008 | 0 | ARTS | NULL | | 100009 | 0 | ARTS | 1018 | | 100111 | 0 | NULL | 1018 | +--------+---------+-------+-----------+ 11 rows in set, 33 warnings (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(fs.fname, ' ', s.lname) AS student, -> s.major, -> s.advisor AS advisorId -> -- output -> FROM student AS s; ERROR 1054 (42S22): Unknown column 'fs.fname' in 'field list' MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS student, -> s.major, -> s.advisor AS advisorId -> -- output -> FROM student AS s; +--------+-----------------+-------+-----------+ | stuId | student | major | advisorId | +--------+-----------------+-------+-----------+ | 100000 | Tony Hawk | CSCI | 1011 | | 100001 | Mary Hawk | CSCI | 1011 | | 100002 | David Hawk | CSCI | 1012 | | 100003 | Catherine Lim | ITEC | NULL | | 100004 | Larry Johnson | ITEC | 1017 | | 100005 | Linda Johnson | CINF | 1015 | | 100006 | Lillian Johnson | CINF | 1016 | | 100007 | Ben Zico | NULL | NULL | | 100008 | Bill Ching | ARTS | NULL | | 100009 | Linda King | ARTS | 1018 | | 100111 | Cathy Johanson | NULL | 1018 | +--------+-----------------+-------+-----------+ 11 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS student, -> IFNULL(s.major, 'Have fun') AS major, -> s.advisor AS advisorId -> -- output -> FROM student AS s; +--------+-----------------+----------+-----------+ | stuId | student | major | advisorId | +--------+-----------------+----------+-----------+ | 100000 | Tony Hawk | CSCI | 1011 | | 100001 | Mary Hawk | CSCI | 1011 | | 100002 | David Hawk | CSCI | 1012 | | 100003 | Catherine Lim | ITEC | NULL | | 100004 | Larry Johnson | ITEC | 1017 | | 100005 | Linda Johnson | CINF | 1015 | | 100006 | Lillian Johnson | CINF | 1016 | | 100007 | Ben Zico | Have fun | NULL | | 100008 | Bill Ching | ARTS | NULL | | 100009 | Linda King | ARTS | 1018 | | 100111 | Cathy Johanson | Have fun | 1018 | +--------+-----------------+----------+-----------+ 11 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS student, -> IFNULL(s.major, '') AS major, -> s.advisor AS advisorId -> -- output -> FROM student AS s; +--------+-----------------+-------+-----------+ | stuId | student | major | advisorId | +--------+-----------------+-------+-----------+ | 100000 | Tony Hawk | CSCI | 1011 | | 100001 | Mary Hawk | CSCI | 1011 | | 100002 | David Hawk | CSCI | 1012 | | 100003 | Catherine Lim | ITEC | NULL | | 100004 | Larry Johnson | ITEC | 1017 | | 100005 | Linda Johnson | CINF | 1015 | | 100006 | Lillian Johnson | CINF | 1016 | | 100007 | Ben Zico | | NULL | | 100008 | Bill Ching | ARTS | NULL | | 100009 | Linda King | ARTS | 1018 | | 100111 | Cathy Johanson | | 1018 | +--------+-----------------+-------+-----------+ 11 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS student, -> IFNULL(s.major, '') AS major, -> IFNULL(s.advisor, '') AS advisorId -> -- output -> FROM student AS s; +--------+-----------------+-------+-----------+ | stuId | student | major | advisorId | +--------+-----------------+-------+-----------+ | 100000 | Tony Hawk | CSCI | 1011 | | 100001 | Mary Hawk | CSCI | 1011 | | 100002 | David Hawk | CSCI | 1012 | | 100003 | Catherine Lim | ITEC | | | 100004 | Larry Johnson | ITEC | 1017 | | 100005 | Linda Johnson | CINF | 1015 | | 100006 | Lillian Johnson | CINF | 1016 | | 100007 | Ben Zico | | | | 100008 | Bill Ching | ARTS | | | 100009 | Linda King | ARTS | 1018 | | 100111 | Cathy Johanson | | 1018 | +--------+-----------------+-------+-----------+ 11 rows in set (0.001 sec) MariaDB [toyu]> -- MariaDB [toyu]> -- Join DB Fall 2023 HW #2 MariaDB [toyu]> -- MariaDB [toyu]> MariaDB [toyu]> -- (a) Show the stuId, name, major, and minor of all students who are majoring in neither CSCI nor CINF. MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ' ,s.lname) AS name, -> s.major, s.minor -> FROM student AS s -> WHERE s.major NOT IN ('CSCI', 'CINF'); +--------+---------------+-------+-------+ | stuId | name | major | minor | +--------+---------------+-------+-------+ | 100003 | Catherine Lim | ITEC | CINF | | 100004 | Larry Johnson | ITEC | NULL | | 100008 | Bill Ching | ARTS | NULL | | 100009 | Linda King | ARTS | CSCI | +--------+---------------+-------+-------+ 4 rows in set (0.006 sec) MariaDB [toyu]> MariaDB [toyu]> -- (b) List the names of all departments together with their faculty members' names and ranks of the School 'Science and Engineering' in the following format. You should not use 'CSE' in your query. MariaDB [toyu]> SELECT DISTINCT CONCAT(f.fname, ' ' ,f.lname) AS faculty, -> f.`rank`, -> d.deptName AS department -> FROM faculty AS f INNER JOIN department AS d ON (d.deptCode = f.deptCode) -> INNER JOIN school AS s ON (s.schoolCode = d.schoolCode) -> WHERE s.schoolName = 'Science and Engineering'; +---------------+---------------------+------------------------------+ | faculty | rank | department | +---------------+---------------------+------------------------------+ | Daniel Kim | Professor | Computer Information Systems | | Andrew Byre | Associate Professor | Computer Information Systems | | Paul Smith | Professor | Computer Science | | Mary Tran | Associate Professor | Computer Science | | David Love | NULL | Computer Science | | Sharon Mannes | Assistant Professor | Computer Science | | Deborah Gump | Professor | Information Technology | | Benjamin Yu | Lecturer | Information Technology | +---------------+---------------------+------------------------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- (c) List the names of students, and their grades of all enrolled 'ENGL', 'CSCI' and 'ITEC' classes, together with the students' grades, semesters and years of the classes, and the course names in the following manner. Note that the result is shown in the ascending order of the student names and then course names. MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ' ,s.lname) AS student, -> co.rubric, co.`number`, co.title, -> c.semester, c.year, e.grade -> FROM student AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId) -> INNER JOIN `class` AS c ON (e.classId = c.classId) -> INNER JOIN course AS co ON (c.courseId = co.courseId) -> WHERE co.rubric IN ('ENGL', 'CSCI', 'ITEC') -> ORDER BY student ASC, title ASC; +--------+-----------------+--------+--------+----------------------------+----------+------+-------+ | stuId | student | rubric | number | title | semester | year | grade | +--------+-----------------+--------+--------+----------------------------+----------+------+-------+ | 100007 | Ben Zico | ENGL | 1410 | English I | Fall | 2019 | F | | 100008 | Bill Ching | ENGL | 1410 | English I | Fall | 2019 | C- | | 100002 | David Hawk | CSCI | 3333 | Data Structures | Fall | 2019 | B- | | 100002 | David Hawk | CSCI | 5333 | DBMS | Fall | 2019 | B+ | | 100006 | Lillian Johnson | ITEC | 3335 | Database Development | Fall | 2019 | A | | 100005 | Linda Johnson | ITEC | 3335 | Database Development | Fall | 2019 | A- | | 100005 | Linda Johnson | ITEC | 3312 | Introduction to Scripting | Fall | 2019 | B+ | | 100001 | Mary Hawk | CSCI | 3333 | Data Structures | Fall | 2019 | NULL | | 100001 | Mary Hawk | CSCI | 4333 | Design of Database Systems | Fall | 2019 | A- | | 100000 | Tony Hawk | CSCI | 3333 | Data Structures | Fall | 2019 | A | | 100000 | Tony Hawk | CSCI | 5333 | DBMS | Fall | 2019 | B+ | | 100000 | Tony Hawk | CSCI | 4333 | Design of Database Systems | Fall | 2019 | A | | 100000 | Tony Hawk | CSCI | 4333 | Design of Database Systems | Spring | 2020 | D | +--------+-----------------+--------+--------+----------------------------+----------+------+-------+ 13 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- (d) LList the ids, names, major names and faculty advisor name of every student in the following manner. MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ' ,s.lname) AS student, -> d1.deptName AS major, -> CONCAT(f.fname, ' ' ,f.lname) AS advisor -> FROM student AS s LEFT JOIN department AS d1 ON (s.major = d1.deptCode) -> LEFT JOIN faculty AS f ON (s.advisor = f.facId); +--------+-----------------+------------------------------+--------------+ | stuId | student | major | advisor | +--------+-----------------+------------------------------+--------------+ | 100000 | Tony Hawk | Computer Science | Paul Smith | | 100001 | Mary Hawk | Computer Science | Paul Smith | | 100002 | David Hawk | Computer Science | Mary Tran | | 100003 | Catherine Lim | Information Technology | NULL | | 100004 | Larry Johnson | Information Technology | Deborah Gump | | 100005 | Linda Johnson | Computer Information Systems | Daniel Kim | | 100006 | Lillian Johnson | Computer Information Systems | Andrew Byre | | 100007 | Ben Zico | NULL | NULL | | 100008 | Bill Ching | Arts | NULL | | 100009 | Linda King | Arts | Art Allister | | 100111 | Cathy Johanson | NULL | Art Allister | +--------+-----------------+------------------------------+--------------+ 11 rows in set (0.002 sec) MariaDB [toyu]> MariaDB [toyu]> -- (e) List the ids, names and major names of all students with a declared major but no declared minor in the following manner. MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ' ,s.lname) AS student, -> d.deptName AS major -> FROM student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> WHERE s.minor IS NULL; +--------+---------------+------------------------+ | stuId | student | major | +--------+---------------+------------------------+ | 100004 | Larry Johnson | Information Technology | | 100008 | Bill Ching | Arts | +--------+---------------+------------------------+ 2 rows in set (0.002 sec) MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty, -> f.rank, -> d.deptName AS department -> -- output -> FROM -> faculty AS f, -> department AS d, -> school AS s -> -- sources -> WHERE f.deptCode = d.deptCode -> AND d.schoolCode = s.schoolCode -> AND s.schoolName = Science and Engineering' ; '> '> '> '> '; 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 '' ; '' at line 12 MariaDB [toyu]> SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty, -> f.rank, -> d.deptName AS department -> -- output -> FROM -> faculty AS f, -> department AS d, -> school AS s -> -- sources -> WHERE f.deptCode = d.deptCode -> AND d.schoolCode = s.schoolCode -> AND s.schoolName = 'Science and Engineering' ; +---------------+---------------------+------------------------------+ | faculty | rank | department | +---------------+---------------------+------------------------------+ | Daniel Kim | Professor | Computer Information Systems | | Andrew Byre | Associate Professor | Computer Information Systems | | Paul Smith | Professor | Computer Science | | Mary Tran | Associate Professor | Computer Science | | David Love | NULL | Computer Science | | Sharon Mannes | Assistant Professor | Computer Science | | Deborah Gump | Professor | Information Technology | | Benjamin Yu | Lecturer | Information Technology | +---------------+---------------------+------------------------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty, -> f.rank, -> d.deptName AS department -> -- output -> FROM faculty AS f INNER JOIN department AS d ON (f.deptCode = d.deptCode) -> INNER JOIN school AS s ON (d.schoolCode = s.schoolCode) -> -- sources -> WHERE s.schoolName = 'Science and Engineering' ; +---------------+---------------------+------------------------------+ | faculty | rank | department | +---------------+---------------------+------------------------------+ | Daniel Kim | Professor | Computer Information Systems | | Andrew Byre | Associate Professor | Computer Information Systems | | Paul Smith | Professor | Computer Science | | Mary Tran | Associate Professor | Computer Science | | David Love | NULL | Computer Science | | Sharon Mannes | Assistant Professor | Computer Science | | Deborah Gump | Professor | Information Technology | | Benjamin Yu | Lecturer | Information Technology | +---------------+---------------------+------------------------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> notee