Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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> Show databases;
+--------------------+
| Database           |
+--------------------+
| c4333u             |
| clystms            |
| experiment         |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| supply             |
| sys                |
| toyu               |
| universal          |
+--------------------+
11 rows in set (0.18 sec)

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.09 sec)

mysql> SELECT * from toyu.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 tables;
+----------------+
| Tables_in_toyu |
+----------------+
| class          |
| course         |
| department     |
| enroll         |
| faculty        |
| grade          |
| school         |
| student        |
+----------------+
8 rows in set (0.05 sec)

mysql> DROP SCHEMA IF EXISTS toyu;
Query OK, 8 rows affected (0.71 sec)

mysql> Show tables;
ERROR 1046 (3D000): No database selected
mysql> Show databases;
+--------------------+
| Database           |
+--------------------+
| c4333u             |
| clystms            |
| experiment         |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| supply             |
| sys                |
| universal          |
+--------------------+
10 rows in set (0.00 sec)

mysql> CREATE SCHEMA toyu;
Query OK, 1 row affected (0.11 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> use toyu;
Database changed
mysql> Show tables;
Empty set (0.00 sec)

mysql> DROP TABLE IF EXISTS Enroll;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> DROP TABLE IF EXISTS Student;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Class;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Course;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Faculty;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Department;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS school;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Grade;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS grade (
    ->     grade VARCHAR(2),
    ->     gradePoint DECIMAL(5,4),
    ->     CONSTRAINT Grade_grade_pk PRIMARY KEY (grade)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> show tables;
+----------------+
| Tables_in_toyu |
+----------------+
| grade          |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM grade;
Empty set (0.00 sec)

mysql> desc grade;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| grade      | varchar(2)   | NO   | PRI | NULL    |       |
| gradePoint | decimal(5,4) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

mysql> desc Grade;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| grade      | varchar(2)   | NO   | PRI | NULL    |       |
| gradePoint | decimal(5,4) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> DESC Grade;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| grade      | varchar(2)   | NO   | PRI | NULL    |       |
| gradePoint | decimal(5,4) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM grade;
Empty set (0.00 sec)

mysql> -- population by insertion. Data manipulation language
mysql> INSERT INTO Grade VALUES
    ->     ('A',4),('A-',3.6667),('B+',3.3333),('B',3),('B-',2.6667),
    ->     ('C+',2.3333),('C',2),('C-',1.6667),
    ->     ('D+',1.3333),('D',1),('D-',0.6667),('F',0),
    ->     ('WX', NULL);
Query OK, 13 rows affected (0.06 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM grade;
+-------+------------+
| grade | gradePoint |
+-------+------------+
| A     |     4.0000 |
| A-    |     3.6667 |
| B     |     3.0000 |
| B-    |     2.6667 |
| B+    |     3.3333 |
| C     |     2.0000 |
| C-    |     1.6667 |
| C+    |     2.3333 |
| D     |     1.0000 |
| D-    |     0.6667 |
| D+    |     1.3333 |
| F     |     0.0000 |
| WX    |       NULL |
+-------+------------+
13 rows in set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS school (
    ->     schoolCode VARCHAR(3),
    ->     schoolName VARCHAR(30),
    ->     CONSTRAINT School_schoolCode_pk PRIMARY KEY (schoolCode),
    ->     CONSTRAINT School_name_ck UNIQUE (schoolName)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> desc school;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| schoolCode | varchar(3)  | NO   | PRI | NULL    |       |
| schoolName | varchar(30) | YES  | UNI | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> -- hello
mysql> INSERT INTO school VALUES
    ->     ('BUS','Business'),('EDU','Education'),
    ->     ('HSH','Human Sciences and Humanities'),
    ->     ('CSE','Science and Engineering');
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * from school;
+------------+-------------------------------+
| schoolCode | schoolName                    |
+------------+-------------------------------+
| BUS        | Business                      |
| EDU        | Education                     |
| HSH        | Human Sciences and Humanities |
| CSE        | Science and Engineering       |
+------------+-------------------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO school VALUES ('LAW', 'College of Law');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * from school;
+------------+-------------------------------+
| schoolCode | schoolName                    |
+------------+-------------------------------+
| BUS        | Business                      |
| LAW        | College of Law                |
| EDU        | Education                     |
| HSH        | Human Sciences and Humanities |
| CSE        | Science and Engineering       |
+------------+-------------------------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO school VALUES ('BUS', 'College of Busy People');
ERROR 1062 (23000): Duplicate entry 'BUS' for key 'PRIMARY'
mysql> INSERT INTO school VALUES ('BUSP', 'College of Busy People');
ERROR 1406 (22001): Data too long for column 'schoolCode' at row 1
mysql> desc school;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| schoolCode | varchar(3)  | NO   | PRI | NULL    |       |
| schoolName | varchar(30) | YES  | UNI | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO school VALUES ('BSP', 'College of Busy People');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * from school;
+------------+-------------------------------+
| schoolCode | schoolName                    |
+------------+-------------------------------+
| BUS        | Business                      |
| BSP        | College of Busy People        |
| LAW        | College of Law                |
| EDU        | Education                     |
| HSH        | Human Sciences and Humanities |
| CSE        | Science and Engineering       |
+------------+-------------------------------+
6 rows in set (0.00 sec)

mysql> INSERT INTO school VALUES ('SCE', 'Computing and Engineering');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * from school;
+------------+-------------------------------+
| schoolCode | schoolName                    |
+------------+-------------------------------+
| BUS        | Business                      |
| BSP        | College of Busy People        |
| LAW        | College of Law                |
| SCE        | Computing and Engineering     |
| EDU        | Education                     |
| HSH        | Human Sciences and Humanities |
| CSE        | Science and Engineering       |
+------------+-------------------------------+
7 rows in set (0.00 sec)

mysql> INSERT INTO school VALUES ('CCE', 'Science and Engineering');
ERROR 1062 (23000): Duplicate entry 'Science and Engineering' for key 'School_name_ck'
mysql> INSERT INTO school VALUES ('CCE', 'SCience and ENgineering');
ERROR 1062 (23000): Duplicate entry 'SCience and ENgineering' for key 'School_name_ck'
mysql> show tables;
+----------------+
| Tables_in_toyu |
+----------------+
| grade          |
| school         |
+----------------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS Department (
    ->     deptCode    VARCHAR(4),
    ->     deptName    VARCHAR(30),
    ->     schoolCode  VARCHAR(3),
    ->     numFaculty  TINYINT,
    ->     CONSTRAINT Department_deptCode_pk PRIMARY KEY (deptCode),
    ->     CONSTRAINT Department_name_ck UNIQUE (deptName),
    ->     CONSTRAINT Department_schoolCode_fk FOREIGN KEY (schoolCode)
    ->         REFERENCES School(schoolCode)
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql> desc department;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| deptCode   | varchar(4)  | NO   | PRI | NULL    |       |
| deptName   | varchar(30) | YES  | UNI | NULL    |       |
| schoolCode | varchar(3)  | YES  | MUL | NULL    |       |
| numFaculty | tinyint(4)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> INSERT INTO DEPARTMENT VALUES
    ->     ('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),
    ->     ('MATH','Mathematics','CSE',7);
Query OK, 7 rows affected (0.14 sec)
Records: 7  Duplicates: 0  Warnings: 0

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 |
| MATH     | Mathematics                  | CSE        |          7 |
+----------+------------------------------+------------+------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM School'
    '> ;
    '> ';
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 ''
;
'' at line 1
mysql> SELECT * FROM School;
+------------+-------------------------------+
| schoolCode | schoolName                    |
+------------+-------------------------------+
| BUS        | Business                      |
| BSP        | College of Busy People        |
| LAW        | College of Law                |
| SCE        | Computing and Engineering     |
| EDU        | Education                     |
| HSH        | Human Sciences and Humanities |
| CSE        | Science and Engineering       |
+------------+-------------------------------+
7 rows in set (0.00 sec)

mysql> INSERT INTO department VALUES
    ->     ('MUSC','Music','ART',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`toyu`.`department`, CONSTRAINT `Department_schoolCode_fk` FOREIGN KEY (`schoolCode`) REFERENCES `school` (`schoolcode`))
mysql> INSERT INTO school VALUES ('ART', 'College of Arts');
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM School;
+------------+-------------------------------+
| schoolCode | schoolName                    |
+------------+-------------------------------+
| BUS        | Business                      |
| ART        | College of Arts               |
| BSP        | College of Busy People        |
| LAW        | College of Law                |
| SCE        | Computing and Engineering     |
| EDU        | Education                     |
| HSH        | Human Sciences and Humanities |
| CSE        | Science and Engineering       |
+------------+-------------------------------+
8 rows in set (0.00 sec)

mysql>
mysql> INSERT INTO department VALUES
    ->     ('MUSC','Music','ART',5);
Query OK, 1 row affected (0.13 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 |
| MATH     | Mathematics                  | CSE        |          7 |
| MUSC     | Music                        | ART        |          5 |
+----------+------------------------------+------------+------------+
8 rows in set (0.00 sec)

mysql> DELETE FROM department WHERE deptCode = 'MUSC';
Query OK, 1 row affected (0.11 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 |
| MATH     | Mathematics                  | CSE        |          7 |
+----------+------------------------------+------------+------------+
7 rows in set (0.00 sec)

mysql> DELETE FROM school WHERE schoolCode = 'CSE';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`toyu`.`department`, CONSTRAINT `Department_schoolCode_fk` FOREIGN KEY (`schoolCode`) REFERENCES `school` (`schoolcode`))
mysql> --
mysql> --
mysql> -- Create a very simplified university database
mysql> --
mysql> --
mysql> -- Drop tables for house-keeping if necessary
mysql> --
mysql>
mysql> SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
Query OK, 0 rows affected (0.03 sec)

mysql> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> -- schema = database
mysql> DROP SCHEMA IF EXISTS toyu;
Query OK, 3 rows affected (0.39 sec)

mysql> -- create the new database initially empty)
mysql> CREATE SCHEMA toyu;
Query OK, 1 row affected (0.01 sec)

mysql> USE toyu;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS Enroll;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Student;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Class;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Course;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Faculty;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Department;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS school;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS Grade;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>
mysql>
mysql> --
mysql>
mysql> CREATE TABLE IF NOT EXISTS grade (
    ->     grade VARCHAR(2),
    ->     gradePoint DECIMAL(5,4),
    ->     CONSTRAINT Grade_grade_pk PRIMARY KEY (grade)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql>
mysql>
mysql> CREATE TABLE IF NOT EXISTS school (
    ->     schoolCode VARCHAR(3),
    ->     schoolName VARCHAR(30),
    ->     CONSTRAINT School_schoolCode_pk PRIMARY KEY (schoolCode),
    ->     CONSTRAINT School_name_ck UNIQUE (schoolName)
    -> );
Query OK, 0 rows affected (0.22 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS Department (
    ->     deptCode    VARCHAR(4),
    ->     deptName    VARCHAR(30),
    ->     schoolCode  VARCHAR(3),
    ->     numFaculty  TINYINT,
    ->     CONSTRAINT Department_deptCode_pk PRIMARY KEY (deptCode),
    ->     CONSTRAINT Department_name_ck UNIQUE (deptName),
    ->     CONSTRAINT Department_schoolCode_fk FOREIGN KEY (schoolCode)
    ->         REFERENCES School(schoolCode)
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS Faculty    (
    ->     facId       INT NOT NULL,
    ->     fname       VARCHAR(20) NOT NULL,
    ->     lname       VARCHAR(20) NOT NULL,
    ->     deptCode    VARCHAR(4) NOT NULL,
    ->     `rank`      VARCHAR(25),
    ->     CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId),
    ->     CONSTRAINT Faculty_deptCode_fk FOREIGN KEY (deptCode)
    ->         REFERENCES Department(deptCode));
Query OK, 0 rows affected (0.17 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS Course (
    ->     courseId    INT NOT NULL,
    ->     rubric      char(4) NOT NULL,
    ->     number      char(4) NOT NULL,
    ->     name        VARCHAR(80) NOT NULL,
    ->     credits     TINYINT,
    ->     CONSTRAINT Course_courseId_pk PRIMARY KEY (courseId),
    ->     CONSTRAINT Course_deptCode_fk FOREIGN KEY (rubric)
    ->         REFERENCES Department(deptCode));
Query OK, 0 rows affected (0.15 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS Class (
    ->     classId     INT NOT NULL AUTO_INCREMENT,
    ->     courseId    INT NOT NULL,
    ->     semester    VARCHAR(10) NOT NULL,
    ->     year        DECIMAL(4,0) NOT NULL,
    ->     facId       INT NOT NULL,
    ->     room        VARCHAR(6),
    ->     CONSTRAINT Class_classId_pk PRIMARY KEY (classId),
    ->     CONSTRAINT Class_courseId_fk FOREIGN KEY (courseId)
    ->         REFERENCES Course(courseId) ON DELETE CASCADE,
    ->     CONSTRAINT Class_facId_fk FOREIGN KEY (facId)
    ->         REFERENCES Faculty (facId) ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (0.26 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS Student    (
    ->     stuId        INT NOT NULL,
    ->     fname        VARCHAR(20) NOT NULL,
    ->     lname        VARCHAR(20) NOT NULL,
    ->     major        VARCHAR(4) NULL,
    ->     minor        VARCHAR(4) NULL,
    ->     credits      integer(3) DEFAULT 0,
    ->     advisor      INT NULL,
    ->     CONSTRAINT Student_stuId_pk PRIMARY KEY(stuId),
    ->     CONSTRAINT Student_credits_cc CHECK ((credits>=0) AND (credits < 250)),
    ->     CONSTRAINT Student_major_fk FOREIGN KEY (major)
    ->         REFERENCES Department(deptCode) ON DELETE CASCADE,
    ->     CONSTRAINT Student_minor_fk FOREIGN KEY (minor)
    ->         REFERENCES Department(deptCode) ON DELETE CASCADE,
    ->     CONSTRAINT Student_advisor_fk FOREIGN KEY (advisor)
    ->         REFERENCES Faculty(facId)
    -> );
Query OK, 0 rows affected (0.22 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS Enroll(
    ->     stuId      INT NOT NULL,
    ->     classId    INT NOT NULL,
    ->     grade      VARCHAR(2),
    ->     n_alerts   INT,
    ->     CONSTRAINT Enroll_classId_stuId_pk PRIMARY KEY (classId, stuId),
    ->     CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classId)
    ->         REFERENCES Class(classId) ON DELETE CASCADE,
    ->     CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId)
    ->         REFERENCES Student (stuId) ON DELETE CASCADE,
    ->     CONSTRAINT Enroll_grade_fk FOREIGN KEY (grade)
    ->         REFERENCES Grade (grade) ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql>
mysql> -- population by insertion. Data manipulation language
mysql> INSERT INTO Grade VALUES
    ->     ('A',4),('A-',3.6667),('B+',3.3333),('B',3),('B-',2.6667),
    ->     ('C+',2.3333),('C',2),('C-',1.6667),
    ->     ('D+',1.3333),('D',1),('D-',0.6667),('F',0),
    ->     ('WX', NULL);
Query OK, 13 rows affected (0.01 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO school VALUES
    ->     ('BUS','Business'),('EDU','Education'),
    ->     ('HSH','Human Sciences and Humanities'),
    ->     ('CSE','Science and Engineering');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO DEPARTMENT VALUES
    ->     ('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),
    ->     ('MATH','Mathematics','CSE',7);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO FACULTY VALUES
    ->     (1011,'Paul','Smith','CSCI','Professor'),
    ->     (1012,'Mary','Tran','CSCI','Associate Professor'),
    ->     (1013,'David','Love','CSCI',NULL),
    ->     (1014,'Sharon','Mannes','CSCI','Assistant Professor'),
    ->     (1015,'Daniel','Kim','CINF','Professor'),
    ->     (1016,'Andrew','Byre','CINF','Associate Professor'),
    ->     (1017,'Deborah','Gump','ITEC','Professor'),
    ->     (1018,'Art','Allister','ARTS','Assistant Professor'),
    ->     (1019,'Benjamin','Yu','ITEC','Lecturer'),
    ->     (1020,'Katrina','Bajaj','ENGL','Lecturer'),
    ->     (1021,'Jorginlo','Neymar','ACCT','Assistant Professor');
Query OK, 11 rows affected (0.03 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO COURSE VALUES
    ->     (2000,'CSCI',3333,'Data Structures',3),
    ->     (2001,'CSCI',4333,'Design of Database Systems',3),
    ->     (2002,'CSCI',5333,'DBMS',3),
    ->     (2020,'CINF',3321,'Introduction to Information Systems',3),
    ->     (2021,'CINF',4320,'Web Application Development',3),
    ->     (2040,'ITEC',3335,'Database Development',3),
    ->     (2041,'ITEC',3312,'Introduction to Scripting',3),
    ->     (2060,'ENGL',1410,'English I',4),
    ->     (2061,'ENGL',1311,'English II',3),
    ->     (2080,'ARTS',3311,'Hindu Arts',3),
    ->     (2090,'ACCT',3333,'Managerial Accounting',3);
Query OK, 11 rows affected (0.05 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO CLASS VALUES
    ->     (10000,2000,'Fall',2019,1011,'D241'),
    ->     (10001,2001,'Fall',2019,1011,'D242'),
    ->     (10002,2002,'Fall',2019,1012,'D136'),
    ->     (10003,2020,'Fall',2019,1014,'D241'),
    ->     (10004,2021,'Fall',2019,1014,'D241'),
    ->     (10005,2040,'Fall',2019,1015,'D237'),
    ->     (10006,2041,'Fall',2019,1019,'D217'),
    ->     (10007,2060,'Fall',2019,1020,'B101'),
    ->     (10008,2080,'Fall',2019,1018,'D241'),
    ->     (11000,2000,'Spring',2020,1011,'D241'),
    ->     (11001,2001,'Spring',2020,1013,'D242'),
    ->     (11002,2002,'Spring',2020,1013,'D136'),
    ->     (11003,2020,'Spring',2020,1016,'D217'),
    ->     (11004,2061,'Spring',2020,1018,'B101');
Query OK, 14 rows affected (0.07 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO STUDENT VALUES
    ->     (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);
Query OK, 10 rows affected (0.07 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO ENROLL VALUES
    ->     (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);
Query OK, 22 rows affected (0.05 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql>
mysql> SET SQL_MODE=@OLD_SQL_MODE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
Query OK, 0 rows affected (0.00 sec)

mysql> SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql>     show tables'
    '> ;
    '> '
    -> ;
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 ''
;
'' at line 1
mysql> show tables;
+----------------+
| Tables_in_toyu |
+----------------+
| class          |
| course         |
| department     |
| enroll         |
| faculty        |
| grade          |
| school         |
| student        |
+----------------+
8 rows in set (0.04 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> 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 |
| MATH     | Mathematics                  | CSE        |          7 |
+----------+------------------------------+------------+------------+
7 rows in set (0.00 sec)

mysql> SELECT DISTINCT -- 1. output column
    ->     s.fname, s.lname,
    ->     d.deptName AS major
    -> -- [2]
    -> -- 1.Student
    -> -- 2.Department
    ->
    -> FROM student AS s,  -- alias
    ->     department AS d
    ->     -- 2. source
    -> -- [3]    1.Join conditions: student.major = department.deptCode
    -> -- 2.Problem condition: minor in ITEC
    -> -- a.Student.minor = ‘ITEC’
    -> WHERE s.major = d.deptCode
    -> AND s.minor = 'ITEC';
+---------+---------+------------------------------+
| fname   | lname   | major                        |
+---------+---------+------------------------------+
| David   | Hawk    | Computer Science             |
| Lillian | Johnson | Computer Information Systems |
+---------+---------+------------------------------+
2 rows in set (0.00 sec)

mysql>--