MariaDB [toyu]> MariaDB [toyu]> DROP SCHEMA IF EXISTS tinker; Query OK, 0 rows affected, 1 warning (0.001 sec) MariaDB [toyu]> CREATE SCHEMA tinker; Query OK, 1 row affected (0.002 sec) MariaDB [toyu]> USE tinker; Database changed MariaDB [tinker]> MariaDB [tinker]> CREATE TABLE s2 -> SELECT * FROM toyu.student; Query OK, 11 rows affected (0.029 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [tinker]> MariaDB [tinker]> SELECT * -> FROM s2; +--------+-----------+----------+-------+-------+------+---------+ | 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.004 sec) MariaDB [tinker]> 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]> 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.000 sec) MariaDB [tinker]> MariaDB [tinker]> CREATE TABLE s4 LIKE toyu.student; Query OK, 0 rows affected (0.028 sec) MariaDB [tinker]> MariaDB [tinker]> SELECT * -> FROM s4; Empty set (0.001 sec) MariaDB [tinker]> MariaDB [tinker]> INSERT INTO s4 -> SELECT * FROM toyu.student; Query OK, 11 rows affected (0.003 sec) Records: 11 Duplicates: 0 Warnings: 0 MariaDB [tinker]> MariaDB [tinker]> SELECT * -> FROM s4; +--------+-----------+----------+-------+-------+------+---------+ | 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.000 sec) MariaDB [tinker]> MariaDB [tinker]> SHOW TABLES; +------------------+ | Tables_in_tinker | +------------------+ | s2 | | s4 | +------------------+ 2 rows in set (0.003 sec) MariaDB [tinker]> set @x = 5; Query OK, 0 rows affected (0.001 sec) MariaDB [tinker]> SELECT @x; +------+ | @x | +------+ | 5 | +------+ 1 row in set (0.001 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) unsigned | YES | | 0 | | | advisor | int(11) | YES | MUL | NULL | | +---------+-----------------+------+-----+---------+-------+ 7 rows in set (0.013 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) unsigned | YES | | 0 | | | advisor | int(11) | YES | | NULL | | +---------+-----------------+------+-----+---------+-------+ 7 rows in set (0.009 sec) MariaDB [tinker]> DESC s3; +---------+-----------------+------+-----+---------+-------+ | 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) unsigned | YES | | 0 | | | advisor | int(11) | YES | | NULL | | +---------+-----------------+------+-----+---------+-------+ 7 rows in set (0.010 sec) MariaDB [tinker]> DESC s4; +---------+-----------------+------+-----+---------+-------+ | 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) unsigned | YES | | 0 | | | advisor | int(11) | YES | MUL | NULL | | +---------+-----------------+------+-----+---------+-------+ 7 rows in set (0.011 sec) MariaDB [tinker]> DROP TABLE s2; Query OK, 0 rows affected (0.010 sec) MariaDB [tinker]> DROP TABLE s3; Query OK, 0 rows affected (0.001 sec) MariaDB [tinker]> DROP TABLE s4; Query OK, 0 rows affected (0.010 sec) MariaDB [tinker]> MariaDB [tinker]> SHOW TABLES; Empty set (0.001 sec) MariaDB [tinker]> MariaDB [tinker]> DROP SCHEMA IF EXISTS tinker; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> UPDATE Student -> SET major = 'ITEC' -> WHERE StuId = 100000; ERROR 1046 (3D000): No database selected MariaDB [(none)]> use toyu; Database changed MariaDB [toyu]> UPDATE Student -> SET major = 'ITEC' -> WHERE StuId = 100000; Query OK, 1 row affected (0.008 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [toyu]> MariaDB [toyu]> UPDATE Student -> SET major = 'ITEC', minor = 'CSCI' -> WHERE StuId = 100000; Query OK, 1 row affected (0.003 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [toyu]> UPDATE Student -> SET major = 'ITEC'; Query OK, 8 rows affected (0.005 sec) Rows matched: 11 Changed: 8 Warnings: 0 MariaDB [toyu]> SELECT * FROM Student LIMIT 5; +--------+-----------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+---------+-------+-------+------+---------+ | 100000 | Tony | Hawk | ITEC | CSCI | 40 | 1011 | | 100001 | Mary | Hawk | ITEC | CINF | 35 | 1011 | | 100002 | David | Hawk | ITEC | ITEC | 66 | 1012 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | +--------+-----------+---------+-------+-------+------+---------+ 5 rows in set (0.001 sec) MariaDB [toyu]> DELETE FROM student; Query OK, 11 rows affected (0.017 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.000 sec) Query OK, 8 rows affected (0.151 sec) Query OK, 1 row affected (0.002 sec) Database changed 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.000 sec) Query OK, 0 rows affected, 1 warning (0.007 sec) Query OK, 0 rows affected, 1 warning (0.010 sec) Query OK, 0 rows affected, 1 warning (0.011 sec) Query OK, 0 rows affected, 1 warning (0.010 sec) Query OK, 0 rows affected, 1 warning (0.012 sec) Query OK, 0 rows affected, 1 warning (0.012 sec) Query OK, 0 rows affected, 1 warning (0.014 sec) Query OK, 0 rows affected, 1 warning (0.011 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.006 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 15 rows affected (0.002 sec) Records: 15 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.002 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 7 rows affected (0.003 sec) Records: 7 Duplicates: 0 Warnings: 0 Query OK, 11 rows affected (0.001 sec) Records: 11 Duplicates: 0 Warnings: 0 Query OK, 11 rows affected (0.003 sec) Records: 11 Duplicates: 0 Warnings: 0 Query OK, 14 rows affected (0.004 sec) Records: 14 Duplicates: 0 Warnings: 0 Query OK, 11 rows affected (0.002 sec) Records: 11 Duplicates: 0 Warnings: 0 Query OK, 22 rows affected (0.004 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]> SELECT COUNT(*) -> FROM faculty; +----------+ | COUNT(*) | +----------+ | 11 | +----------+ 1 row in set (0.001 sec) MariaDB [toyu]> SELECT COUNT(*) INTO @c -> FROM faculty; Query OK, 1 row affected (0.001 sec) MariaDB [toyu]> SELECT @c; +------+ | @c | +------+ | 11 | +------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * INTO @c -> FROM faculty; ERROR 1222 (21000): The used SELECT statements have a different number of columns MariaDB [toyu]> SELECT * INTO @c -> FROM faculty -> WHERE facId = 1011; ERROR 1222 (21000): The used SELECT statements have a different number of columns MariaDB [toyu]> SELECT facId INTO @c -> FROM faculty; ERROR 1172 (42000): Result consisted of more than one row MariaDB [toyu]> SELECT DISTINCT * -> FROM Student -> WHERE credits BETWEEN 30 AND 70; ERROR 1054 (42S22): Unknown column 'credits' in 'where clause' MariaDB [toyu]> SELECT DISTINCT * -> FROM Student -> WHERE ach BETWEEN 30 AND 70; +--------+-------+---------+-------+-------+------+---------+ | 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 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | +--------+-------+---------+-------+-------+------+---------+ 4 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT * -> FROM Student -> WHERE 30 < ach AND ach < 70; +--------+-------+---------+-------+-------+------+---------+ | 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 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | +--------+-------+---------+-------+-------+------+---------+ 4 rows in set (0.000 sec) MariaDB [toyu]> SELECT DISTINCT * -> FROM Student -> WHERE ach BETWEEN 20 AND 90; +--------+-----------+---------+-------+-------+------+---------+ | 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 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | +--------+-----------+---------+-------+-------+------+---------+ 6 rows in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT * -> FROM Student -> WHERE 20 < ach AND ach < 90; +--------+-------+---------+-------+-------+------+---------+ | 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 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | +--------+-------+---------+-------+-------+------+---------+ 4 rows in set (0.000 sec) MariaDB [toyu]> SELECT DISTINCT * -> FROM Student -> WHERE 20 <= ach AND ach <= 90; +--------+-----------+---------+-------+-------+------+---------+ | 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 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | +--------+-----------+---------+-------+-------+------+---------+ 6 rows in set (0.001 sec) MariaDB [toyu]> -- student in selected majors MariaDB [toyu]> SELECT DISTINCT * -> FROM Student -> WHERE major IN ('CSCI', 'CINF', 'ITEC'); +--------+-----------+---------+-------+-------+------+---------+ | 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 | +--------+-----------+---------+-------+-------+------+---------+ 7 rows in set (0.002 sec) MariaDB [toyu]> -- student enrolled in some classes. MariaDB [toyu]> SELECT DISTINCT * -> FROM Student AS s -> WHERE EXISTS -> (SELECT * -- a subquery -> FROM Enroll AS e -> WHERE e.stuId = s.stuId); +--------+---------+---------+-------+-------+------+---------+ | 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 | | 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 | +--------+---------+---------+-------+-------+------+---------+ 8 rows in set (0.004 sec) MariaDB [toyu]> -- or MariaDB [toyu]> SELECT DISTINCT s.* -> FROM Student AS s INNER JOIN Enroll AS e USING (stuId); +--------+---------+---------+-------+-------+------+---------+ | 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 | | 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 | +--------+---------+---------+-------+-------+------+---------+ 8 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]>