MariaDB [toyu]> source createtoyu.sql
Query OK, 0 rows affected (0.004 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 8 rows affected (0.146 sec)

Query OK, 1 row affected (0.002 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected, 1 warning (0.023 sec)

Query OK, 0 rows affected, 1 warning (0.023 sec)

Query OK, 0 rows affected, 1 warning (0.026 sec)

Query OK, 0 rows affected, 1 warning (0.024 sec)

Query OK, 0 rows affected, 1 warning (0.024 sec)

Query OK, 0 rows affected, 1 warning (0.025 sec)

Query OK, 0 rows affected, 1 warning (0.026 sec)

Query OK, 0 rows affected, 1 warning (0.024 sec)

Query OK, 0 rows affected (0.016 sec)

Query OK, 0 rows affected (0.010 sec)

Query OK, 0 rows affected (0.014 sec)

Query OK, 0 rows affected (0.010 sec)

Query OK, 0 rows affected (0.010 sec)

Query OK, 15 rows affected (0.007 sec)
Records: 15  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.004 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 7 rows affected (0.023 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.005 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.006 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 14 rows affected (0.011 sec)
Records: 14  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.007 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 22 rows affected (0.010 sec)
Records: 22  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]> INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (100000,'Bun','Yue','CSCI','MATH',64,1011);
ERROR 1062 (23000): Duplicate entry '100000' for key 'PRIMARY'
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]> 
MariaDB [toyu]> INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (333000,'Bun','Yue','CSCI','MATH',64,1011);
Query OK, 1 row affected (0.003 sec)

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 |
| 333000 | Bun       | Yue      | CSCI  | MATH  |   64 |    1011 |
+--------+-----------+----------+-------+-------+------+---------+
12 rows in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (333000,'Susan','Yue','ECON','MATH',22,2314);
ERROR 1062 (23000): Duplicate entry '333000' for key 'PRIMARY'
MariaDB [toyu]> INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (355601,'Susan','Yue','ECON','MATH',22,2314);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`toyu`.`student`, CONSTRAINT `Student_major_fk` FOREIGN KEY (`major`) REFERENCES `department` (`deptCode`) ON DELETE CASCADE)
MariaDB [toyu]> INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES ('ECON','Economics','HHS',2);
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`))
MariaDB [toyu]> INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) VALUES ('ECON','Economics','HSH',2);
Query OK, 1 row affected (0.004 sec)

MariaDB [toyu]> INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (355601,'Susan','Yue','ECON','MATH',22,2314);
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`))
MariaDB [toyu]> INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES (355601,'Susan','Yue','ECON','MATH',22,1018);
Query OK, 1 row affected (0.005 sec)

MariaDB [toyu]> SELECT DISTINCT stuId, fname, lname, major, minor 
    ->     -- output; select clause
    -> FROM student -- sources; from clause
    -> WHERE major = 'CSCI';
+--------+-------+-------+-------+-------+
| stuId  | fname | lname | major | minor |
+--------+-------+-------+-------+-------+
| 100000 | Tony  | Hawk  | CSCI  | CINF  |
| 100001 | Mary  | Hawk  | CSCI  | CINF  |
| 100002 | David | Hawk  | CSCI  | ITEC  |
| 333000 | Bun   | Yue   | CSCI  | MATH  |
+--------+-------+-------+-------+-------+
4 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT s.stuId,
    ->     CONCAT(s.fname, ' ' , s.lname) AS student,
    ->     d.deptName AS major
    ->     -- output; select clause
    -> FROM student AS s, department AS d 
    ->     -- sources; from clause
    -> WHERE s.major = d.deptName
    -> AND s.minor = 'ITEC' 
    -> AND s.advisor = 1016; 
Empty set (0.002 sec)

MariaDB [toyu]> SELECT DISTINCT s.stuId,
    ->     CONCAT(s.fname, ' ' , s.lname) AS student,
    ->     d.deptName AS major
    ->     -- output; select clause
    -> FROM student AS s, department AS d 
    ->     -- sources; from clause
    -> WHERE s.major = d.deptCode
    -> AND s.minor = 'ITEC' 
    -> AND s.advisor = 1016; 
+--------+-----------------+------------------------------+
| stuId  | student         | major                        |
+--------+-----------------+------------------------------+
| 100006 | Lillian Johnson | Computer Information Systems |
+--------+-----------------+------------------------------+
1 row in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT s.stuId,
    ->     CONCAT(s.fname, ' ' , s.lname) AS student,
    ->     d.deptName AS major
    ->     -- output; select clause
    -> FROM student AS s INNER JOIN department AS d 
    ->     ON (s.major = d.deptCode)
    ->     -- sources; from clause
    -> WHERE s.minor = 'ITEC' 
    -> AND s.advisor = 1016; 
+--------+-----------------+------------------------------+
| stuId  | student         | major                        |
+--------+-----------------+------------------------------+
| 100006 | Lillian Johnson | Computer Information Systems |
+--------+-----------------+------------------------------+
1 row in set (0.001 sec)

MariaDB [toyu]> source createtoyu.sql
Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 8 rows affected (0.222 sec)

Query OK, 1 row affected (0.004 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.035 sec)

Query OK, 0 rows affected, 1 warning (0.039 sec)

Query OK, 0 rows affected, 1 warning (0.040 sec)

Query OK, 0 rows affected, 1 warning (0.037 sec)

Query OK, 0 rows affected, 1 warning (0.028 sec)

Query OK, 0 rows affected, 1 warning (0.033 sec)

Query OK, 0 rows affected, 1 warning (0.038 sec)

Query OK, 0 rows affected, 1 warning (0.035 sec)

Query OK, 0 rows affected (0.013 sec)

Query OK, 0 rows affected (0.014 sec)

Query OK, 0 rows affected (0.012 sec)

Query OK, 0 rows affected (0.013 sec)

Query OK, 0 rows affected (0.017 sec)

Query OK, 15 rows affected (0.014 sec)
Records: 15  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.012 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 7 rows affected (0.012 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.006 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.014 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 14 rows affected (0.013 sec)
Records: 14  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.005 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 22 rows affected (0.017 sec)
Records: 22  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]>