MariaDB [toyu]> SELECT s.*, d.* -> FROM student AS s, department AS d; +--------+-----------+----------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | stuId | fname | lname | major | minor | ach | advisor | deptCode | deptName | schoolCode | numStaff | +--------+-----------+----------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | ACCT | Accounting | BUS | 10 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | ARTS | Arts | HSH | 5 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | CINF | Computer Information Systems | CSE | 5 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | CSCI | Computer Science | CSE | 12 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | ENGL | English | HSH | 12 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | ITEC | Information Technology | CSE | 4 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | MATH | Mathematics | CSE | 7 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | ACCT | Accounting | BUS | 10 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | ARTS | Arts | HSH | 5 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | CINF | Computer Information Systems | CSE | 5 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | CSCI | Computer Science | CSE | 12 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | ENGL | English | HSH | 12 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | ITEC | Information Technology | CSE | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | MATH | Mathematics | CSE | 7 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | ACCT | Accounting | BUS | 10 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | ARTS | Arts | HSH | 5 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | CINF | Computer Information Systems | CSE | 5 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | CSCI | Computer Science | CSE | 12 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | ENGL | English | HSH | 12 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | ITEC | Information Technology | CSE | 4 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | MATH | Mathematics | CSE | 7 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ACCT | Accounting | BUS | 10 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ARTS | Arts | HSH | 5 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | CINF | Computer Information Systems | CSE | 5 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | CSCI | Computer Science | CSE | 12 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ENGL | English | HSH | 12 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ITEC | Information Technology | CSE | 4 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | MATH | Mathematics | CSE | 7 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ACCT | Accounting | BUS | 10 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ARTS | Arts | HSH | 5 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | CINF | Computer Information Systems | CSE | 5 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | CSCI | Computer Science | CSE | 12 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ENGL | English | HSH | 12 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ITEC | Information Technology | CSE | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | MATH | Mathematics | CSE | 7 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | ACCT | Accounting | BUS | 10 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | ARTS | Arts | HSH | 5 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | CINF | Computer Information Systems | CSE | 5 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | CSCI | Computer Science | CSE | 12 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | ENGL | English | HSH | 12 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | ITEC | Information Technology | CSE | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | MATH | Mathematics | CSE | 7 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | ACCT | Accounting | BUS | 10 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | ARTS | Arts | HSH | 5 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | CINF | Computer Information Systems | CSE | 5 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | CSCI | Computer Science | CSE | 12 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | ENGL | English | HSH | 12 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | ITEC | Information Technology | CSE | 4 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | MATH | Mathematics | CSE | 7 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | ACCT | Accounting | BUS | 10 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | ARTS | Arts | HSH | 5 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | CINF | Computer Information Systems | CSE | 5 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | CSCI | Computer Science | CSE | 12 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | ENGL | English | HSH | 12 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | ITEC | Information Technology | CSE | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | MATH | Mathematics | CSE | 7 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ACCT | Accounting | BUS | 10 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ARTS | Arts | HSH | 5 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | CINF | Computer Information Systems | CSE | 5 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | CSCI | Computer Science | CSE | 12 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ENGL | English | HSH | 12 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ITEC | Information Technology | CSE | 4 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | MATH | Mathematics | CSE | 7 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ACCT | Accounting | BUS | 10 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ARTS | Arts | HSH | 5 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | CINF | Computer Information Systems | CSE | 5 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | CSCI | Computer Science | CSE | 12 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ENGL | English | HSH | 12 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ITEC | Information Technology | CSE | 4 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | MATH | Mathematics | CSE | 7 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | ACCT | Accounting | BUS | 10 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | ARTS | Arts | HSH | 5 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | CINF | Computer Information Systems | CSE | 5 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | CSCI | Computer Science | CSE | 12 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | ENGL | English | HSH | 12 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | ITEC | Information Technology | CSE | 4 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | MATH | Mathematics | CSE | 7 | +--------+-----------+----------+-------+-------+------+---------+----------+------------------------------+------------+----------+ 77 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.*, d.* -> FROM student AS s INNER JOIN department AS d -> ON (s.major = d.deptCode); +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | stuId | fname | lname | major | minor | ach | advisor | deptCode | deptName | schoolCode | numStaff | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | CSCI | Computer Science | CSE | 12 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | CSCI | Computer Science | CSE | 12 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | CSCI | Computer Science | CSE | 12 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ITEC | Information Technology | CSE | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ITEC | Information Technology | CSE | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | CINF | Computer Information Systems | CSE | 5 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | CINF | Computer Information Systems | CSE | 5 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ARTS | Arts | HSH | 5 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ARTS | Arts | HSH | 5 | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ 9 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.*, d.* -> FROM student AS s INNER JOIN department AS d -> ON (s.minor = d.deptCode); +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | stuId | fname | lname | major | minor | ach | advisor | deptCode | deptName | schoolCode | numStaff | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | CINF | Computer Information Systems | CSE | 5 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | CINF | Computer Information Systems | CSE | 5 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | ITEC | Information Technology | CSE | 4 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | CINF | Computer Information Systems | CSE | 5 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | ENGL | English | HSH | 12 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | ITEC | Information Technology | CSE | 4 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | CSCI | Computer Science | CSE | 12 | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ 7 rows in set (0.001 sec) MariaDB [toyu]> -- list students and minor department information. MariaDB [toyu]> SELECT s.*, d.deptNode AS minor -> FROM student AS s INNER JOIN department AS d -> ON (s.minor = d.deptCode); ERROR 1054 (42S22): Unknown column 'd.deptNode' in 'field list' MariaDB [toyu]> SELECT s.*, d.deptName AS minor -> FROM student AS s INNER JOIN department AS d -> ON (s.minor = d.deptCode); +--------+-----------+---------+-------+-------+------+---------+------------------------------+ | stuId | fname | lname | major | minor | ach | advisor | minor | +--------+-----------+---------+-------+-------+------+---------+------------------------------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | Computer Information Systems | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | Computer Information Systems | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | Information Technology | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | Computer Information Systems | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | English | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | Information Technology | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | Computer Science | +--------+-----------+---------+-------+-------+------+---------+------------------------------+ 7 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.*, d.deptName AS `minor department` -> FROM student AS s INNER JOIN department AS d -> ON (s.minor = d.deptCode); +--------+-----------+---------+-------+-------+------+---------+------------------------------+ | stuId | fname | lname | major | minor | ach | advisor | minor department | +--------+-----------+---------+-------+-------+------+---------+------------------------------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | Computer Information Systems | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | Computer Information Systems | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | Information Technology | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | Computer Information Systems | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | English | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | Information Technology | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | Computer Science | +--------+-----------+---------+-------+-------+------+---------+------------------------------+ 7 rows in set (0.000 sec) MariaDB [toyu]> SELECT s.*, d.deptName AS `minor department` -> FROM student AS s INNER JOIN department AS d -> ON (s.minor = d.deptCode) -> WHERE d.numStaff < 11; +--------+-----------+---------+-------+-------+------+---------+------------------------------+ | stuId | fname | lname | major | minor | ach | advisor | minor department | +--------+-----------+---------+-------+-------+------+---------+------------------------------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | Computer Information Systems | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | Computer Information Systems | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | Information Technology | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | Computer Information Systems | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | Information Technology | +--------+-----------+---------+-------+-------+------+---------+------------------------------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.*, d.deptName AS `minor department` -> FROM student AS s, department AS d -> WHERE s.minor = d.deptCode -> AND d.numStaff < 11; +--------+-----------+---------+-------+-------+------+---------+------------------------------+ | stuId | fname | lname | major | minor | ach | advisor | minor department | +--------+-----------+---------+-------+-------+------+---------+------------------------------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | Computer Information Systems | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | Computer Information Systems | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | Information Technology | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | Computer Information Systems | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | Information Technology | +--------+-----------+---------+-------+-------+------+---------+------------------------------+ 5 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.*, d.* -> FROM student AS s INNER JOIN department AS d -> ON (s.major = d.deptCode); +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | stuId | fname | lname | major | minor | ach | advisor | deptCode | deptName | schoolCode | numStaff | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | CSCI | Computer Science | CSE | 12 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | CSCI | Computer Science | CSE | 12 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | CSCI | Computer Science | CSE | 12 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ITEC | Information Technology | CSE | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ITEC | Information Technology | CSE | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | CINF | Computer Information Systems | CSE | 5 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | CINF | Computer Information Systems | CSE | 5 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ARTS | Arts | HSH | 5 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ARTS | Arts | HSH | 5 | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ 9 rows in set (0.000 sec) MariaDB [toyu]> -- list students and major department information if available. MariaDB [toyu]> SELECT s.*, d.* -> FROM student AS s LEFT JOIN department AS d -> ON (s.major = d.deptCode); +--------+-----------+----------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | stuId | fname | lname | major | minor | ach | advisor | deptCode | deptName | schoolCode | numStaff | +--------+-----------+----------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | CSCI | Computer Science | CSE | 12 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | CSCI | Computer Science | CSE | 12 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | CSCI | Computer Science | CSE | 12 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ITEC | Information Technology | CSE | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ITEC | Information Technology | CSE | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | CINF | Computer Information Systems | CSE | 5 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | CINF | Computer Information Systems | CSE | 5 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | NULL | NULL | NULL | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ARTS | Arts | HSH | 5 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ARTS | Arts | HSH | 5 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | NULL | NULL | NULL | NULL | +--------+-----------+----------+-------+-------+------+---------+----------+------------------------------+------------+----------+ 11 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.*, d.* -> FROM department AS d LEFT JOIN student AS s -> ON (s.major = d.deptCode); +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | stuId | fname | lname | major | minor | ach | advisor | deptCode | deptName | schoolCode | numStaff | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ACCT | Accounting | BUS | 10 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ARTS | Arts | HSH | 5 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ARTS | Arts | HSH | 5 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | CINF | Computer Information Systems | CSE | 5 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | CINF | Computer Information Systems | CSE | 5 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | CSCI | Computer Science | CSE | 12 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | CSCI | Computer Science | CSE | 12 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | CSCI | Computer Science | CSE | 12 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ENGL | English | HSH | 12 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ITEC | Information Technology | CSE | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ITEC | Information Technology | CSE | 4 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | MATH | Mathematics | CSE | 7 | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ 12 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.*, d.* -> FROM department AS d RIGHT JOIN student AS s -> ON (s.major = d.deptCode); +--------+-----------+----------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | stuId | fname | lname | major | minor | ach | advisor | deptCode | deptName | schoolCode | numStaff | +--------+-----------+----------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | CSCI | Computer Science | CSE | 12 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | CSCI | Computer Science | CSE | 12 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | CSCI | Computer Science | CSE | 12 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ITEC | Information Technology | CSE | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ITEC | Information Technology | CSE | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | CINF | Computer Information Systems | CSE | 5 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | CINF | Computer Information Systems | CSE | 5 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | NULL | NULL | NULL | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ARTS | Arts | HSH | 5 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ARTS | Arts | HSH | 5 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | NULL | NULL | NULL | NULL | +--------+-----------+----------+-------+-------+------+---------+----------+------------------------------+------------+----------+ 11 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.*, d.* -> FROM student AS s INNER JOIN department AS d -> ON (s.major = d.deptCode); +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | stuId | fname | lname | major | minor | ach | advisor | deptCode | deptName | schoolCode | numStaff | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | CSCI | Computer Science | CSE | 12 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | CSCI | Computer Science | CSE | 12 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | CSCI | Computer Science | CSE | 12 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | ITEC | Information Technology | CSE | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | ITEC | Information Technology | CSE | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | CINF | Computer Information Systems | CSE | 5 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | CINF | Computer Information Systems | CSE | 5 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | ARTS | Arts | HSH | 5 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | ARTS | Arts | HSH | 5 | +--------+-----------+---------+-------+-------+------+---------+----------+------------------------------+------------+----------+ 9 rows in set (0.001 sec) MariaDB [toyu]> -- students with no advisor MariaDB [toyu]> SELECT s.* -> FROM student AS s -> WHERE s.advisor IS NULL; +--------+-----------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-----------+-------+-------+-------+------+---------+ | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | +--------+-----------+-------+-------+-------+------+---------+ 3 rows in set (0.001 sec) MariaDB [toyu]> SELECT s.* -> FROM student AS s -> WHERE s.advisor = NULL; Empty set (0.000 sec) MariaDB [toyu]> SELECT FALSE, -> TRUE; +-------+------+ | FALSE | TRUE | +-------+------+ | 0 | 1 | +-------+------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE 0; Empty set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE 1; +--------+-----------+----------+-------+-------+------+---------+ | 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]> SELECT * -> FROM student -> WHERE 2697; +--------+-----------+----------+-------+-------+------+---------+ | 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]> SELECT * -> FROM student -> WHERE '0'; Empty set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE '145'; +--------+-----------+----------+-------+-------+------+---------+ | 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]> -- warning: '' cannot be converted to a number. MariaDB [toyu]> -- "Warning 1292 Truncated incorrect DOUBLE value: ''" MariaDB [toyu]> SELECT * -> FROM student -> WHERE ''; Empty set, 1 warning (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- warning: '' cannot be converted to a number. MariaDB [toyu]> -- "Warning 1292 Truncated incorrect DOUBLE value: ''" MariaDB [toyu]> SELECT * -> FROM student -> WHERE 'Hello world'; Empty set, 1 warning (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM student -> WHERE 1.49; +--------+-----------+----------+-------+-------+------+---------+ | 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]> notee