MariaDB [tinker]> MariaDB [tinker]> CREATE TABLE s2 -> SELECT * FROM toyu.student; Query OK, 11 rows affected (0.030 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [tinker]> show tables; +------------------+ | Tables_in_tinker | +------------------+ | s2 | +------------------+ 1 row in set (0.001 sec) MariaDB [tinker]> desc s2; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | stuId | int(11) | NO | | NULL | | | fname | varchar(30) | NO | | NULL | | | lname | varchar(30) | NO | | NULL | | | major | char(4) | YES | | NULL | | | minor | char(4) | YES | | NULL | | | ach | int(3) | YES | | 0 | | | advisor | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.009 sec) MariaDB [tinker]> desc toyu.student; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | stuId | int(11) | NO | PRI | NULL | | | fname | varchar(30) | NO | | NULL | | | lname | varchar(30) | NO | | NULL | | | major | char(4) | YES | MUL | NULL | | | minor | char(4) | YES | MUL | NULL | | | ach | int(3) | YES | | 0 | | | advisor | int(11) | YES | MUL | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.009 sec) MariaDB [tinker]> CREATE TEMPORARY TABLE s3 -> SELECT * FROM toyu.student; Query OK, 11 rows affected (0.005 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [tinker]> MariaDB [tinker]> SET @major = 'CSCI'; Query OK, 0 rows affected (0.000 sec) MariaDB [tinker]> MariaDB [tinker]> SELECT s.* -> FROM toyu.student AS s -> WHERE s.major = @major; +--------+-------+-------+-------+-------+------+---------+ | 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 | +--------+-------+-------+-------+-------+------+---------+ 3 rows in set (0.002 sec) MariaDB [tinker]> select @major; +--------+ | @major | +--------+ | CSCI | +--------+ 1 row in set (0.000 sec) MariaDB [tinker]> show tables; +------------------+ | Tables_in_tinker | +------------------+ | s2 | +------------------+ 1 row in set (0.001 sec) MariaDB [tinker]> SELECT * -> FROM s3; +--------+-----------+----------+-------+-------+------+---------+ | 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 [tinker]> use toyu; Database changed 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]> DELETE FROM student -> WHERE 1; Query OK, 11 rows affected (0.020 sec) MariaDB [toyu]> SELECT * FROM student; Empty set (0.000 sec) MariaDB [toyu]> -- MariaDB [toyu]> -- MariaDB [toyu]> -- Create a very simplified university database MariaDB [toyu]> -- MariaDB [toyu]> -- MariaDB [toyu]> -- Drop tables for house-keeping if necessary MariaDB [toyu]> -- MariaDB [toyu]> MariaDB [toyu]> SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> DROP SCHEMA IF EXISTS toyu; -- MySQL: schema = database Query OK, 9 rows affected (0.148 sec) MariaDB [(none)]> CREATE SCHEMA toyu; Query OK, 1 row affected (0.002 sec) MariaDB [(none)]> USE toyu; Database changed MariaDB [toyu]> MariaDB [toyu]> DROP TABLE IF EXISTS Enroll; Query OK, 0 rows affected, 1 warning (0.001 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Student; Query OK, 0 rows affected, 1 warning (0.001 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Class; Query OK, 0 rows affected, 1 warning (0.002 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Course; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Faculty; Query OK, 0 rows affected, 1 warning (0.001 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Department; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [toyu]> DROP TABLE IF EXISTS school; Query OK, 0 rows affected, 1 warning (0.001 sec) MariaDB [toyu]> DROP TABLE IF EXISTS Grade; Query OK, 0 rows affected, 1 warning (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- MariaDB [toyu]> -- MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Grade ( -> grade CHAR(2) NOT NULL, -> gradePoint DECIMAL(5,4) NULL, -> CONSTRAINT Grade_grade_pk PRIMARY KEY (grade) -> ); Query OK, 0 rows affected, 1 warning (0.025 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS School ( -> schoolCode CHAR(3) NOT NULL, -> schoolName VARCHAR(30) NOT NULL, -> CONSTRAINT School_schoolCode_pk PRIMARY KEY (schoolCode), -> -- alternate keys: [1] schoolName -> CONSTRAINT School_name_ck UNIQUE (schoolName) -> ); Query OK, 0 rows affected, 1 warning (0.030 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Department ( -> deptCode CHAR(4) NOT NULL, -> deptName VARCHAR(30) NOT NULL, -> schoolCode CHAR(3) NULL, -> numStaff TINYINT NULL, -> CONSTRAINT Department_deptCode_pk PRIMARY KEY (deptCode), -> -- alternate keys: [1] deptName -> CONSTRAINT Department_name_ck UNIQUE (deptName), -> CONSTRAINT Department_schoolCode_fk FOREIGN KEY (schoolCode) -> REFERENCES School(schoolCode) -> ); Query OK, 0 rows affected, 1 warning (0.041 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Faculty ( -> facId INT NOT NULL, -> fname VARCHAR(30) NOT NULL, -> lname VARCHAR(30) NOT NULL, -> deptCode CHAR(4) NOT NULL, -> `rank` VARCHAR(25) NULL, -> CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId), -> CONSTRAINT Faculty_deptCode_fk FOREIGN KEY (deptCode) -> REFERENCES Department(deptCode)); Query OK, 0 rows affected, 1 warning (0.033 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Course ( -> courseId INT NOT NULL, -> rubric CHAR(4) NOT NULL, -> number CHAR(4) NOT NULL, -> title VARCHAR(80) NOT NULL, -> credits TINYINT NULL, -> CONSTRAINT Course_courseId_pk PRIMARY KEY (courseId), -> CONSTRAINT Course_deptCode_fk FOREIGN KEY (rubric) -> REFERENCES Department(deptCode)); Query OK, 0 rows affected, 1 warning (0.038 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Class ( -> classId INT NOT NULL, -> courseId INT NOT NULL, -> semester VARCHAR(10) NOT NULL, -> year DECIMAL(4,0) NOT NULL, -> facId INT NOT NULL, -> room VARCHAR(6) NULL, -> 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, 1 warning (0.031 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Student ( -> stuId INT NOT NULL, -> fname VARCHAR(30) NOT NULL, -> lname VARCHAR(30) NOT NULL, -> major CHAR(4) NULL, -> minor CHAR(4) NULL, -> -- ach: accumulated credit hours, including transferred credits. -> ach INTEGER(3) NULL DEFAULT 0, -> advisor INT NULL, -> CONSTRAINT Student_stuId_pk PRIMARY KEY(stuId), -> -- an artificial example of a CHECK constraint. -> CONSTRAINT Student_ach_cc CHECK ((ach>=0) AND (ach < 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, 1 warning (0.041 sec) MariaDB [toyu]> MariaDB [toyu]> CREATE TABLE IF NOT EXISTS Enroll( -> stuId INT NOT NULL, -> classId INT NOT NULL, -> grade VARCHAR(2) NULL, -> n_alerts INT NULL, -> 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, 1 warning (0.040 sec) MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Grade(grade, gradePoint) 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), -> ('P', NULL), ('IP', NULL), ('WX', NULL); Query OK, 15 rows affected (0.007 sec) Records: 15 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO School(schoolCode, schoolName) VALUES -> ('BUS','Business'), -> ('EDU','Education'), -> ('HSH','Human Sciences and Humanities'), -> ('CSE','Science and Engineering'); Query OK, 4 rows affected (0.006 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Department(deptCode, deptName, schoolCode, numStaff) 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.006 sec) Records: 7 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Faculty(facId, fname, lname, deptCode, `rank`) 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.007 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Course(courseId, rubric, number, title, credits) 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.006 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Class(classId, courseId, semester, year, facId, room) 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,1012,'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.010 sec) Records: 14 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Student(stuId, fname, lname, major, minor, ach, advisor) VALUES -> (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); Query OK, 11 rows affected (0.011 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> INSERT INTO Enroll(stuId, classId, grade, n_alerts) 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.005 sec) Records: 22 Duplicates: 0 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> SET SQL_MODE=@OLD_SQL_MODE; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]>