Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
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 * 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> set @major = 'CSCI';
Query OK, 0 rows affected (0.04 sec)

mysql> select @major;
+--------+
| @major |
+--------+
| CSCI   |
+--------+
1 row in set (0.03 sec)

mysql> SELECT * FROM student where major = @major;
+--------+-------+-------+-------+-------+---------+---------+
| 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 |
+--------+-------+-------+-------+-------+---------+---------+
3 rows in set (0.03 sec)

mysql> INSERT INTO Student VALUES
    ->     (200000,'Tom','Yue','CSCI','MAGIC',70,2011);
ERROR 1406 (22001): Data too long for column 'minor' at row 1
mysql> INSERT INTO Student VALUES
    ->     (200000,'Tom','Yue','CSCI','MAGI',70,2011);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`toyu`.`student`, CONSTRAINT `Student_minor_fk` FOREIGN KEY (`minor`) REFERENCES `department` (`deptcode`) ON DELETE CASCADE)
mysql> INSERT INTO Department VALUES
    ->     ('MAGI','Magic','BUS',5);
Query OK, 1 row affected (0.10 sec)

mysql> select * from department;
+----------+------------------------------+------------+------------+
| deptCode | deptName                     | schoolCode | numFaculty |
+----------+------------------------------+------------+------------+
| ACCT     | Accounting                   | BUS        |         10 |
| ARTS     | Arts                         | HSH        |          5 |
| CINF     | Computer Information Systems | CSE        |          5 |
| CSCI     | Computer Science             | CSE        |         12 |
| ENGL     | English                      | HSH        |         12 |
| ITEC     | Information Technology       | CSE        |          4 |
| MAGI     | Magic                        | BUS        |          5 |
| MATH     | Mathematics                  | CSE        |          7 |
+----------+------------------------------+------------+------------+
8 rows in set (0.00 sec)

mysql> INSERT INTO Student VALUES
    ->     (200000,'Tom','Yue','CSCI','MAGI',70,2011);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`toyu`.`student`, CONSTRAINT `Student_advisor_fk` FOREIGN KEY (`advisor`) REFERENCES `faculty` (`facid`))
mysql> INSERT INTO Student VALUES
    ->     (200000,'Tom','Yue','CSCI','MAGI',70,1015);
Query OK, 1 row affected (0.10 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 |
| 200000 | Tom       | Yue     | CSCI  | MAGI  |      70 |    1015 |
+--------+-----------+---------+-------+-------+---------+---------+
11 rows in set (0.00 sec)

mysql>