Microsoft Windows [Version 10.0.17134.1006] (c) 2018 Microsoft Corporation. All rights reserved. C:\Users\yue>mysql8 -u yue -p C:\Users\yue>"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -P 3308 -u yue -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 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> show tables; +----------------+ | Tables_in_toyu | +----------------+ | class | | course | | department | | enroll | | faculty | | grade | | school | | student | +----------------+ 8 rows in set (0.01 sec) mysql> select * from student; +--------+-----------+---------+-------+-------+---------+---------+ | stuId | fname | lname | major | minor | credits | advisor | +--------+-----------+---------+-------+-------+---------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1011 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | 1017 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1015 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | ENGL | 90 | 1018 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | +--------+-----------+---------+-------+-------+---------+---------+ 10 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | c4333u | | clystms | | experiment | | information_schema | | mysql | | performance_schema | | sakila | | supply | | sys | | toyu | | universal | +--------------------+ 11 rows in set (0.00 sec) mysql> SELECT DISTINCT -- 1. output column (SELECT CLAUSE) -> s.stuId, s.fname, s.lname, d.deptName, -> f.fname AS `adv. fname`, -> f.lname AS `adv. lname` -> -- 1.student -> -- 2.department -> -- 3.faculty -> -> FROM -- 2. source table instances (FROM CLAUSE) -> student AS s, -> department AS d, -> faculty AS f -> -- 1.Join conditions: -> -- a.Student.minor = department.deptCode (e.g. Tony Hawk: CINF) -> -- b.Student.advisor = faculty.facId -> -- 2.Problem conditions: -> -- a.majoring in CSCI: student.major = ‘CSCI’ -> -> WHERE -- WHERE Clause: conditions -> s.minor = d.deptCode -> AND S.advisor = f.facId -> AND s.major = 'CSCI'; +--------+-------+-------+------------------------------+------------+------------+ | stuId | fname | lname | deptName | adv. fname | adv. lname | +--------+-------+-------+------------------------------+------------+------------+ | 100000 | Tony | Hawk | Computer Information Systems | Paul | Smith | | 100001 | Mary | Hawk | Computer Information Systems | Paul | Smith | | 100002 | David | Hawk | Information Technology | Paul | Smith | +--------+-------+-------+------------------------------+------------+------------+ 3 rows in set (0.01 sec) mysql> -- ITEC 3335 Fall 2018 HW #2. mysql> mysql> -- Q1. Show the first name, last time, minor and number of credits mysql> -- of all students majoringin ITEC. mysql> mysql> select distinct fname, lname, minor, credits -> from student -> where major = 'ITEC'; +-----------+---------+-------+---------+ | fname | lname | minor | credits | +-----------+---------+-------+---------+ | Catherine | Lim | CINF | 20 | | Larry | Johnson | NULL | 66 | +-----------+---------+-------+---------+ 2 rows in set (0.00 sec) mysql> mysql> -- Q2. Show the names, school codes and numbers of faculty members mysql> -- of all departments. mysql> select distinct deptName, schoolCode, numFaculty -> from department; +------------------------------+------------+------------+ | deptName | schoolCode | numFaculty | +------------------------------+------------+------------+ | Accounting | BUS | 10 | | Arts | HSH | 5 | | Computer Information Systems | CSE | 5 | | Computer Science | CSE | 12 | | English | HSH | 12 | | Information Technology | CSE | 4 | | Mathematics | CSE | 7 | +------------------------------+------------+------------+ 7 rows in set (0.00 sec) mysql> mysql> -- Q3. Show the names, department names and school codes of the faculty members mysql> -- working for a department in the school 'CSE'. mysql> mysql> select distinct f.fname, f.lname, d.deptName, d.schoolCode -> from faculty f, department d -> where f.deptCode = d.deptCode -> and d.schoolCode = 'CSE'; +----------+--------+------------------------------+------------+ | fname | lname | deptName | schoolCode | +----------+--------+------------------------------+------------+ | Daniel | Kim | Computer Information Systems | CSE | | Andrew | Byre | Computer Information Systems | CSE | | Paul | Smith | Computer Science | CSE | | Mary | Tran | Computer Science | CSE | | David | Love | Computer Science | CSE | | Sharon | Mannes | Computer Science | CSE | | Deborah | Gump | Information Technology | CSE | | Benjamin | Yu | Information Technology | CSE | +----------+--------+------------------------------+------------+ 8 rows in set (0.01 sec) mysql> mysql> -- Q4. Show the names, ranks and department names of the faculty members mysql> -- working for a department in the school 'CSE' or 'HSH' in the following mysql> -- manner Note that the result is shown in ascending order of last name mysql> -- and then first name. mysql> mysql> select distinct f.fname as `first name`, -> f.lname as `last name`, -> d.deptName as department, f.rank -> from faculty f, department d -> where f.deptCode = d.deptCode -> and (d.schoolCode = 'CSE' or d.schoolCode = 'HSH') -> order by `last name`,`first name`; +------------+-----------+------------------------------+---------------------+ | first name | last name | department | rank | +------------+-----------+------------------------------+---------------------+ | Art | Allister | Arts | Assistant Professor | | Katrina | Bajaj | English | Lecturer | | Andrew | Byre | Computer Information Systems | Associate Professor | | Deborah | Gump | Information Technology | Professor | | Daniel | Kim | Computer Information Systems | Professor | | David | Love | Computer Science | NULL | | Sharon | Mannes | Computer Science | Assistant Professor | | Paul | Smith | Computer Science | Professor | | Mary | Tran | Computer Science | Associate Professor | | Benjamin | Yu | Information Technology | Lecturer | +------------+-----------+------------------------------+---------------------+ 10 rows in set (0.00 sec) mysql> mysql> -- Q5. Show the ids and names of the students and the semesters mysql> -- that they have enrolled in the course with id 2000. mysql> mysql> select distinct s.stuId, s.fname, s.lname -> from student s, enroll e, class c -> where s.stuId = e.stuId -> and e.classId = c.classId -> and c.courseId = 2000; +--------+-------+-------+ | stuId | fname | lname | +--------+-------+-------+ | 100000 | Tony | Hawk | | 100001 | Mary | Hawk | | 100002 | David | Hawk | +--------+-------+-------+ 3 rows in set (0.00 sec) mysql> mysql> mysql> -- Q6. Show the ids and names of the students mysql> -- who have enrolled in a 'CINF' course. mysql> mysql> select distinct s.stuId, s.fname, s.lname -> from student s, enroll e, class c, course co -> where s.stuId = e.stuId -> and e.classId = c.classId -> and c.courseId = co.courseId -> and co.rubric = 'CINF'; +--------+---------+---------+ | stuId | fname | lname | +--------+---------+---------+ | 100000 | Tony | Hawk | | 100002 | David | Hawk | | 100004 | Larry | Johnson | | 100005 | Linda | Johnson | | 100006 | Lillian | Johnson | +--------+---------+---------+ 5 rows in set (0.01 sec) mysql> mysql> -- Q7. Show the student ids, names, minor department names, and mysql> -- faculty advsior names of all students majoring in CSCI. mysql> mysql> select distinct s.stuId, s.fname, s.lname, -> d.deptName, -> f.fname as "adv. fname", f.lname as "adv. lname" -> from student s, department d, faculty f -> where s.advisor = f.facId -> and s.minor = d.deptCode -> and s.major = 'CSCI'; +--------+-------+-------+------------------------------+------------+------------+ | stuId | fname | lname | deptName | adv. fname | adv. lname | +--------+-------+-------+------------------------------+------------+------------+ | 100000 | Tony | Hawk | Computer Information Systems | Paul | Smith | | 100001 | Mary | Hawk | Computer Information Systems | Paul | Smith | | 100002 | David | Hawk | Information Technology | Paul | Smith | +--------+-------+-------+------------------------------+------------+------------+ 3 rows in set (0.00 sec) mysql> mysql> mysql> mysql> mysql> mysql> C:\Bun\1_F19_ITEC3335\website\demo>dir Volume in drive C is Windows Volume Serial Number is 52C3-8219 Directory of C:\Bun\1_F19_ITEC3335\website\demo 09/18/2019 01:44 PM . 09/18/2019 01:44 PM .. 08/28/2019 02:27 PM 1,310,516 2019_8_28.docx 09/16/2019 01:04 PM 1,631,131 2019_9_11.docx 09/11/2019 02:07 PM 193 2019_9_11.txt 09/11/2019 02:16 PM 22,938 2019_9_11_mysql_log.txt 09/16/2019 02:18 PM 162,530 2019_9_16.docx 09/16/2019 02:15 PM 1,127 2019_9_16.sql.txt 09/16/2019 01:53 PM 608 2019_9_16.txt 09/16/2019 02:17 PM 33,029 2019_9_16_mysql_log.txt 09/18/2019 01:31 PM 1,432,207 2019_9_18.docx 09/18/2019 01:39 PM 1,394 2019_9_18.sql 09/18/2019 12:06 PM 0 2019_9_18_mysql_log.txt 09/04/2019 02:28 PM 2,441,241 2019_9_4.docx 09/09/2019 02:17 PM 1,034,156 2019_9_9.docx 09/16/2019 02:16 PM 7,929 Createtoyu.sql 09/18/2019 01:44 PM 2,112 f18_h2sol.sql 09/11/2019 02:27 PM 892,928 toyu_1.accdb 09/16/2019 02:18 PM _notes 16 File(s) 8,974,039 bytes 3 Dir(s) 145,651,924,992 bytes free C:\Bun\1_F19_ITEC3335\website\demo>mysql8 -u yue -p C:\Bun\1_F19_ITEC3335\website\demo>"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -P 3308 -u yue -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 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> show tables; +----------------+ | Tables_in_toyu | +----------------+ | class | | course | | department | | enroll | | faculty | | grade | | school | | student | +----------------+ 8 rows in set (0.01 sec) mysql> source f18_h2sol.sql +-----------+---------+-------+---------+ | fname | lname | minor | credits | +-----------+---------+-------+---------+ | Catherine | Lim | CINF | 20 | | Larry | Johnson | NULL | 66 | +-----------+---------+-------+---------+ 2 rows in set (0.00 sec) +------------------------------+------------+------------+ | deptName | schoolCode | numFaculty | +------------------------------+------------+------------+ | Accounting | BUS | 10 | | Arts | HSH | 5 | | Computer Information Systems | CSE | 5 | | Computer Science | CSE | 12 | | English | HSH | 12 | | Information Technology | CSE | 4 | | Mathematics | CSE | 7 | +------------------------------+------------+------------+ 7 rows in set (0.01 sec) +----------+--------+------------------------------+------------+ | fname | lname | deptName | schoolCode | +----------+--------+------------------------------+------------+ | Daniel | Kim | Computer Information Systems | CSE | | Andrew | Byre | Computer Information Systems | CSE | | Paul | Smith | Computer Science | CSE | | Mary | Tran | Computer Science | CSE | | David | Love | Computer Science | CSE | | Sharon | Mannes | Computer Science | CSE | | Deborah | Gump | Information Technology | CSE | | Benjamin | Yu | Information Technology | CSE | +----------+--------+------------------------------+------------+ 8 rows in set (0.00 sec) +------------+-----------+------------------------------+---------------------+ | first name | last name | department | rank | +------------+-----------+------------------------------+---------------------+ | Art | Allister | Arts | Assistant Professor | | Katrina | Bajaj | English | Lecturer | | Andrew | Byre | Computer Information Systems | Associate Professor | | Deborah | Gump | Information Technology | Professor | | Daniel | Kim | Computer Information Systems | Professor | | David | Love | Computer Science | NULL | | Sharon | Mannes | Computer Science | Assistant Professor | | Paul | Smith | Computer Science | Professor | | Mary | Tran | Computer Science | Associate Professor | | Benjamin | Yu | Information Technology | Lecturer | +------------+-----------+------------------------------+---------------------+ 10 rows in set (0.00 sec) +--------+-------+-------+ | stuId | fname | lname | +--------+-------+-------+ | 100000 | Tony | Hawk | | 100001 | Mary | Hawk | | 100002 | David | Hawk | +--------+-------+-------+ 3 rows in set (0.00 sec) +--------+---------+---------+ | stuId | fname | lname | +--------+---------+---------+ | 100000 | Tony | Hawk | | 100002 | David | Hawk | | 100004 | Larry | Johnson | | 100005 | Linda | Johnson | | 100006 | Lillian | Johnson | +--------+---------+---------+ 5 rows in set (0.00 sec) +--------+-------+-------+------------------------------+------------+------------+ | stuId | fname | lname | deptName | adv. fname | adv. lname | +--------+-------+-------+------------------------------+------------+------------+ | 100000 | Tony | Hawk | Computer Information Systems | Paul | Smith | | 100001 | Mary | Hawk | Computer Information Systems | Paul | Smith | | 100002 | David | Hawk | Information Technology | Paul | Smith | +--------+-------+-------+------------------------------+------------+------------+ 3 rows in set (0.00 sec) mysql> desc student; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | stuId | int(11) | NO | PRI | NULL | | | fname | varchar(20) | NO | | NULL | | | lname | varchar(20) | NO | | NULL | | | major | varchar(4) | YES | MUL | NULL | | | minor | varchar(4) | YES | MUL | NULL | | | credits | int(3) | YES | | 0 | | | advisor | int(11) | YES | MUL | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql>