MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | swim | | test | | toyu | +--------------------+ 7 rows in set (0.003 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | swim | | test | | toyu | +--------------------+ 7 rows in set (0.003 sec) MariaDB [(none)]> DROP SCHEMA IF EXISTS toyu; Query OK, 8 rows affected (0.126 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | swim | | test | +--------------------+ 6 rows in set (0.007 sec) MariaDB [(none)]> use toyu; ERROR 1049 (42000): Unknown database 'toyu' MariaDB [(none)]> 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, 0 rows affected, 1 warning (0.001 sec) Query OK, 1 row affected (0.003 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.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.026 sec) Query OK, 0 rows affected, 1 warning (0.050 sec) Query OK, 0 rows affected, 1 warning (0.033 sec) Query OK, 0 rows affected, 1 warning (0.030 sec) Query OK, 0 rows affected, 1 warning (0.026 sec) Query OK, 0 rows affected, 1 warning (0.042 sec) Query OK, 0 rows affected, 1 warning (0.035 sec) Query OK, 0 rows affected, 1 warning (0.031 sec) Query OK, 15 rows affected (0.005 sec) Records: 15 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.007 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.013 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.014 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]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | swim | | test | | toyu | +--------------------+ 7 rows in set (0.004 sec) MariaDB [toyu]> 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.002 sec) MariaDB [toyu]> MariaDB [toyu]> DROP SCHEMA IF EXISTS toyu; Query OK, 8 rows affected (0.147 sec) MariaDB [(none)]> SELECT * -> FROM student; ERROR 1046 (3D000): No database selected MariaDB [(none)]> SELECT * -> FROM toyu.student; ERROR 1146 (42S02): Table 'toyu.student' doesn't exist MariaDB [(none)]> source createtoyu.sql Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected, 1 warning (0.001 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.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.029 sec) Query OK, 0 rows affected, 1 warning (0.027 sec) Query OK, 0 rows affected, 1 warning (0.027 sec) Query OK, 0 rows affected, 1 warning (0.022 sec) Query OK, 0 rows affected, 1 warning (0.028 sec) Query OK, 0 rows affected, 1 warning (0.027 sec) Query OK, 0 rows affected, 1 warning (0.029 sec) Query OK, 0 rows affected, 1 warning (0.026 sec) Query OK, 15 rows affected (0.004 sec) Records: 15 Duplicates: 0 Warnings: 0 Query OK, 4 rows affected (0.011 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 7 rows affected (0.004 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.010 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.013 sec) Records: 11 Duplicates: 0 Warnings: 0 Query OK, 22 rows affected (0.013 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]> MariaDB [toyu]> SELECT * -> FROM toyu.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.000 sec) MariaDB [toyu]> SELECT DISTINCT s.stuId, -> s.fname & ' ' & s.lname AS student, -> s.major, -> s.advsior AS AdvisorId -> -- columns -> FROM student AS s; ERROR 1054 (42S22): Unknown column 's.advsior' in 'field list' MariaDB [toyu]> SELECT DISTINCT s.stuId, -> s.fname & ' ' & s.lname AS student, -> s.major, -> s.advisor AS AdvisorId -> -- columns -> FROM student AS s; +--------+---------+-------+-----------+ | stuId | student | major | AdvisorId | +--------+---------+-------+-----------+ | 100000 | 0 | CSCI | 1011 | | 100001 | 0 | CSCI | 1011 | | 100002 | 0 | CSCI | 1012 | | 100003 | 0 | ITEC | NULL | | 100004 | 0 | ITEC | 1017 | | 100005 | 0 | CINF | 1015 | | 100006 | 0 | CINF | 1016 | | 100007 | 0 | NULL | NULL | | 100008 | 0 | ARTS | NULL | | 100009 | 0 | ARTS | 1018 | | 100111 | 0 | NULL | 1018 | +--------+---------+-------+-----------+ 11 rows in set, 33 warnings (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.stuId, -> s.fname& ' ' & s.lname AS student, -> s.major, -> s.advisor AS AdvisorId -> -- columns -> FROM student AS s; +--------+---------+-------+-----------+ | stuId | student | major | AdvisorId | +--------+---------+-------+-----------+ | 100000 | 0 | CSCI | 1011 | | 100001 | 0 | CSCI | 1011 | | 100002 | 0 | CSCI | 1012 | | 100003 | 0 | ITEC | NULL | | 100004 | 0 | ITEC | 1017 | | 100005 | 0 | CINF | 1015 | | 100006 | 0 | CINF | 1016 | | 100007 | 0 | NULL | NULL | | 100008 | 0 | ARTS | NULL | | 100009 | 0 | ARTS | 1018 | | 100111 | 0 | NULL | 1018 | +--------+---------+-------+-----------+ 11 rows in set, 33 warnings (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.stuId, -> CONCAT(s.fname, ' ', s.lname) AS student, -> s.major, -> s.advisor AS AdvisorId -> -- columns -> FROM student AS s; +--------+-----------------+-------+-----------+ | stuId | student | major | AdvisorId | +--------+-----------------+-------+-----------+ | 100000 | Tony Hawk | CSCI | 1011 | | 100001 | Mary Hawk | CSCI | 1011 | | 100002 | David Hawk | CSCI | 1012 | | 100003 | Catherine Lim | ITEC | NULL | | 100004 | Larry Johnson | ITEC | 1017 | | 100005 | Linda Johnson | CINF | 1015 | | 100006 | Lillian Johnson | CINF | 1016 | | 100007 | Ben Zico | NULL | NULL | | 100008 | Bill Ching | ARTS | NULL | | 100009 | Linda King | ARTS | 1018 | | 100111 | Cathy Johanson | NULL | 1018 | +--------+-----------------+-------+-----------+ 11 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faulty, -> f.rank, -> d.deptName AS department -> -- columns -> FROM faculty AS f, -> department AS d, -> school AS s -> -- source tables -> WHERE f.deptCode = d.deptCode -- Join conditions -> AND d.schoolCode = s.schoolCode -> AND s.schoolName = 'Science and Engineering'; -- problem conditions +---------------+---------------------+------------------------------+ | faulty | rank | department | +---------------+---------------------+------------------------------+ | Daniel Kim | Professor | Computer Information Systems | | Andrew Byre | Associate Professor | Computer Information Systems | | Paul Smith | Professor | Computer Science | | Mary Tran | Associate Professor | Computer Science | | David Love | NULL | Computer Science | | Sharon Mannes | Assistant Professor | Computer Science | | Deborah Gump | Professor | Information Technology | | Benjamin Yu | Lecturer | Information Technology | +---------------+---------------------+------------------------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty, -> f.rank, -> d.deptName AS department -> -- columns -> FROM faculty AS f, -> department AS d, -> school AS s -> -- source tables -> WHERE f.deptCode = d.deptCode -- Join conditions -> AND d.schoolCode = s.schoolCode -> AND s.schoolName = 'Science and Engineering'; -- problem conditions +---------------+---------------------+------------------------------+ | faculty | rank | department | +---------------+---------------------+------------------------------+ | Daniel Kim | Professor | Computer Information Systems | | Andrew Byre | Associate Professor | Computer Information Systems | | Paul Smith | Professor | Computer Science | | Mary Tran | Associate Professor | Computer Science | | David Love | NULL | Computer Science | | Sharon Mannes | Assistant Professor | Computer Science | | Deborah Gump | Professor | Information Technology | | Benjamin Yu | Lecturer | Information Technology | +---------------+---------------------+------------------------------+ 8 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT CONCAT(f.fname, ' ', f.lname) AS faculty, -> f.rank, -> d.deptName AS department -> -- columns -> FROM faculty AS f INNER JOIN department AS d ON (f.deptCode = d.deptCode) -> INNER JOIN school AS s ON (d.schoolCode = s.schoolCode) -> -- source tables -> WHERE s.schoolName = 'Science and Engineering'; -- problem conditions +---------------+---------------------+------------------------------+ | faculty | rank | department | +---------------+---------------------+------------------------------+ | Daniel Kim | Professor | Computer Information Systems | | Andrew Byre | Associate Professor | Computer Information Systems | | Paul Smith | Professor | Computer Science | | Mary Tran | Associate Professor | Computer Science | | David Love | NULL | Computer Science | | Sharon Mannes | Assistant Professor | Computer Science | | Deborah Gump | Professor | Information Technology | | Benjamin Yu | Lecturer | Information Technology | +---------------+---------------------+------------------------------+ 8 rows in set (0.002 sec) MariaDB [toyu]> notee