Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
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.04 sec)

mysql> select * from faculty_more;
ERROR 1146 (42S02): Table 'toyu.faculty_more' doesn't exist
mysql> CREATE TEMPORARY TABLE IF NOT EXISTS Faculty_more (
    ->     facId       INT NOT NULL,
    ->     fname       VARCHAR(20) NOT NULL,
    ->     lname       VARCHAR(20) NOT NULL,
    ->     deptCode    VARCHAR(4) NOT NULL,
    ->     deptName    VARCHAR(30),
    ->     `rank`      VARCHAR(25),
    ->     CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from faculty_more;
Empty set (0.00 sec)

mysql> INSERT INTO faculty_more
    -> SELECT DISTINCT f.facId,
    ->     f.fname, f.lname, f.deptCode,
    ->     d.deptName AS department,
    ->     f.`rank`
    -> FROM faculty f INNER JOIN department d
    ->     ON (f.deptCode = d.deptCode);
Query OK, 11 rows affected (0.03 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from faculty_more;
+-------+----------+----------+----------+------------------------------+---------------------+
| facId | fname    | lname    | deptCode | deptName                     | rank                |
+-------+----------+----------+----------+------------------------------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Computer Science             | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Computer Science             | Associate Professor |
|  1013 | David    | Love     | CSCI     | Computer Science             | NULL                |
|  1014 | Sharon   | Mannes   | CSCI     | Computer Science             | Assistant Professor |
|  1015 | Daniel   | Kim      | CINF     | Computer Information Systems | Professor           |
|  1016 | Andrew   | Byre     | CINF     | Computer Information Systems | Associate Professor |
|  1017 | Deborah  | Gump     | ITEC     | Information Technology       | Professor           |
|  1018 | Art      | Allister | ARTS     | Arts                         | Assistant Professor |
|  1019 | Benjamin | Yu       | ITEC     | Information Technology       | Lecturer            |
|  1020 | Katrina  | Bajaj    | ENGL     | English                      | Lecturer            |
|  1021 | Jorginlo | Neymar   | ACCT     | Accounting                   | Assistant Professor |
+-------+----------+----------+----------+------------------------------+---------------------+
11 rows in set (0.00 sec)

mysql> SELECT DISTINCT f.facId,
    ->     f.fname, f.lname, f.deptCode,
    ->     d.deptName AS department,
    ->     f.`rank`
    -> FROM faculty f INNER JOIN department d
    ->     ON (f.deptCode = d.deptCode);
+-------+----------+----------+----------+------------------------------+---------------------+
| facId | fname    | lname    | deptCode | department                   | rank                |
+-------+----------+----------+----------+------------------------------+---------------------+
|  1021 | Jorginlo | Neymar   | ACCT     | Accounting                   | Assistant Professor |
|  1018 | Art      | Allister | ARTS     | Arts                         | Assistant Professor |
|  1015 | Daniel   | Kim      | CINF     | Computer Information Systems | Professor           |
|  1016 | Andrew   | Byre     | CINF     | Computer Information Systems | Associate Professor |
|  1011 | Paul     | Smith    | CSCI     | Computer Science             | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Computer Science             | Associate Professor |
|  1013 | David    | Love     | CSCI     | Computer Science             | NULL                |
|  1014 | Sharon   | Mannes   | CSCI     | Computer Science             | Assistant Professor |
|  1020 | Katrina  | Bajaj    | ENGL     | English                      | Lecturer            |
|  1017 | Deborah  | Gump     | ITEC     | Information Technology       | Professor           |
|  1019 | Benjamin | Yu       | ITEC     | Information Technology       | Lecturer            |
+-------+----------+----------+----------+------------------------------+---------------------+
11 rows in set (0.00 sec)

mysql>  CREATE TABLE IF NOT EXISTS Faculty_more_2 (
    ->     facId       INT NOT NULL,
    ->     fname       VARCHAR(20) NOT NULL,
    ->     lname       VARCHAR(20) NOT NULL,
    ->     deptCode    VARCHAR(4) NOT NULL,
    ->     deptName    VARCHAR(30),
    ->     `rank`      VARCHAR(25),
    ->     CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId));
Query OK, 0 rows affected (0.23 sec)

mysql> select * from faculty_more _2;
+-------+----------+----------+----------+------------------------------+---------------------+
| facId | fname    | lname    | deptCode | deptName                     | rank                |
+-------+----------+----------+----------+------------------------------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Computer Science             | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Computer Science             | Associate Professor |
|  1013 | David    | Love     | CSCI     | Computer Science             | NULL                |
|  1014 | Sharon   | Mannes   | CSCI     | Computer Science             | Assistant Professor |
|  1015 | Daniel   | Kim      | CINF     | Computer Information Systems | Professor           |
|  1016 | Andrew   | Byre     | CINF     | Computer Information Systems | Associate Professor |
|  1017 | Deborah  | Gump     | ITEC     | Information Technology       | Professor           |
|  1018 | Art      | Allister | ARTS     | Arts                         | Assistant Professor |
|  1019 | Benjamin | Yu       | ITEC     | Information Technology       | Lecturer            |
|  1020 | Katrina  | Bajaj    | ENGL     | English                      | Lecturer            |
|  1021 | Jorginlo | Neymar   | ACCT     | Accounting                   | Assistant Professor |
+-------+----------+----------+----------+------------------------------+---------------------+
11 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_toyu |
+----------------+
| class          |
| course         |
| department     |
| enroll         |
| faculty        |
| faculty_more_2 |
| grade          |
| school         |
| student        |
+----------------+
9 rows in set (0.01 sec)

mysql> select * from faculty_more _2;
+-------+----------+----------+----------+------------------------------+---------------------+
| facId | fname    | lname    | deptCode | deptName                     | rank                |
+-------+----------+----------+----------+------------------------------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Computer Science             | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Computer Science             | Associate Professor |
|  1013 | David    | Love     | CSCI     | Computer Science             | NULL                |
|  1014 | Sharon   | Mannes   | CSCI     | Computer Science             | Assistant Professor |
|  1015 | Daniel   | Kim      | CINF     | Computer Information Systems | Professor           |
|  1016 | Andrew   | Byre     | CINF     | Computer Information Systems | Associate Professor |
|  1017 | Deborah  | Gump     | ITEC     | Information Technology       | Professor           |
|  1018 | Art      | Allister | ARTS     | Arts                         | Assistant Professor |
|  1019 | Benjamin | Yu       | ITEC     | Information Technology       | Lecturer            |
|  1020 | Katrina  | Bajaj    | ENGL     | English                      | Lecturer            |
|  1021 | Jorginlo | Neymar   | ACCT     | Accounting                   | Assistant Professor |
+-------+----------+----------+----------+------------------------------+---------------------+
11 rows in set (0.00 sec)

mysql> select * from faculty_more;
+-------+----------+----------+----------+------------------------------+---------------------+
| facId | fname    | lname    | deptCode | deptName                     | rank                |
+-------+----------+----------+----------+------------------------------+---------------------+
|  1011 | Paul     | Smith    | CSCI     | Computer Science             | Professor           |
|  1012 | Mary     | Tran     | CSCI     | Computer Science             | Associate Professor |
|  1013 | David    | Love     | CSCI     | Computer Science             | NULL                |
|  1014 | Sharon   | Mannes   | CSCI     | Computer Science             | Assistant Professor |
|  1015 | Daniel   | Kim      | CINF     | Computer Information Systems | Professor           |
|  1016 | Andrew   | Byre     | CINF     | Computer Information Systems | Associate Professor |
|  1017 | Deborah  | Gump     | ITEC     | Information Technology       | Professor           |
|  1018 | Art      | Allister | ARTS     | Arts                         | Assistant Professor |
|  1019 | Benjamin | Yu       | ITEC     | Information Technology       | Lecturer            |
|  1020 | Katrina  | Bajaj    | ENGL     | English                      | Lecturer            |
|  1021 | Jorginlo | Neymar   | ACCT     | Accounting                   | Assistant Professor |
+-------+----------+----------+----------+------------------------------+---------------------+
11 rows in set (0.00 sec)