MariaDB [toyu]> source h2sol.sql +--------+-------+-------+---------+ | stuId | fname | lname | credits | +--------+-------+-------+---------+ | 100000 | Tony | Hawk | 40 | | 100001 | Mary | Hawk | 35 | +--------+-------+-------+---------+ 2 rows in set (0.001 sec) +--------+-------+-------+---------+ | stuId | fname | lname | credits | +--------+-------+-------+---------+ | 100000 | Tony | Hawk | 40 | | 100001 | Mary | Hawk | 35 | +--------+-------+-------+---------+ 2 rows in set (0.002 sec) +---------+---------+---------+-------+ | fname | lname | classId | grade | +---------+---------+---------+-------+ | Tony | Hawk | 10000 | A | | Tony | Hawk | 10001 | A | | Tony | Hawk | 10002 | B+ | | Tony | Hawk | 10003 | C | | Tony | Hawk | 10004 | A- | | Tony | Hawk | 11001 | D | | Mary | Hawk | 10000 | NULL | | Mary | Hawk | 10001 | A- | | David | Hawk | 10000 | B- | | David | Hawk | 10002 | B+ | | David | Hawk | 10003 | D | | Lillian | Johnson | 10004 | C+ | | Lillian | Johnson | 10005 | A | +---------+---------+---------+-------+ 13 rows in set (0.002 sec) +---------+---------+---------+-------------------------------------+-------+ | fname | lname | classId | Course Title | grade | +---------+---------+---------+-------------------------------------+-------+ | Tony | Hawk | 10000 | Data Structures | A | | Tony | Hawk | 10001 | Design of Database Systems | A | | Tony | Hawk | 10002 | DBMS | B+ | | Tony | Hawk | 10003 | Introduction to Information Systems | C | | Tony | Hawk | 10004 | Web Application Development | A- | | Tony | Hawk | 11001 | Design of Database Systems | D | | Mary | Hawk | 10000 | Data Structures | | | Mary | Hawk | 10001 | Design of Database Systems | A- | | David | Hawk | 10000 | Data Structures | B- | | David | Hawk | 10002 | DBMS | B+ | | David | Hawk | 10003 | Introduction to Information Systems | D | | Lillian | Johnson | 10004 | Web Application Development | C+ | | Lillian | Johnson | 10005 | Database Development | A | +---------+---------+---------+-------------------------------------+-------+ 13 rows in set (0.004 sec) +-----------------+---------+-------------------------------------+---------------+-------+ | student | classId | Course Title | instructor | grade | +-----------------+---------+-------------------------------------+---------------+-------+ | Tony Hawk | 10000 | Data Structures | Paul Smith | A | | Tony Hawk | 10001 | Design of Database Systems | Paul Smith | A | | Tony Hawk | 10002 | DBMS | Mary Tran | B+ | | Tony Hawk | 10003 | Introduction to Information Systems | Sharon Mannes | C | | Tony Hawk | 10004 | Web Application Development | Sharon Mannes | A- | | Tony Hawk | 11001 | Design of Database Systems | Mary Tran | D | | Mary Hawk | 10000 | Data Structures | Paul Smith | | | Mary Hawk | 10001 | Design of Database Systems | Paul Smith | A- | | David Hawk | 10000 | Data Structures | Paul Smith | B- | | David Hawk | 10002 | DBMS | Mary Tran | B+ | | David Hawk | 10003 | Introduction to Information Systems | Sharon Mannes | D | | Lillian Johnson | 10004 | Web Application Development | Sharon Mannes | C+ | | Lillian Johnson | 10005 | Database Development | Daniel Kim | A | +-----------------+---------+-------------------------------------+---------------+-------+ 13 rows in set (0.001 sec) +-----------------+------------------------------+-------------+ | student | major | advsior | +-----------------+------------------------------+-------------+ | Tony Hawk | Computer Science | Paul Smith | | Mary Hawk | Computer Science | Paul Smith | | David Hawk | Computer Science | Mary Tran | | Linda Johnson | Computer Information Systems | Daniel Kim | | Lillian Johnson | Computer Information Systems | Andrew Byre | +-----------------+------------------------------+-------------+ 5 rows in set (0.003 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.fanme, s.lname -> FROM Student AS s INNER JOIN Enroll AS e ON (s.stuId = e.stuId) -> WHERE e.classId = 10003; ERROR 1054 (42S22): Unknown column 's.fanme' in 'field list' MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.fname, s.lname -> FROM Student AS s INNER JOIN Enroll AS e ON (s.stuId = e.stuId) -> WHERE e.classId = 10003; +-------+---------+ | fname | lname | +-------+---------+ | Tony | Hawk | | David | Hawk | | Larry | Johnson | | Linda | Johnson | +-------+---------+ 4 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.fname, s.lname, d.deptName -> FROM Student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> INNER JOIN enroll AS e ON (s.stuId = e.stuId) -> INNER JOIN class AS c ON (e.classId = c.classId) -> INNER JOIN faculty AS f (c.facId = f.facId) -> WHERE e.grade = 'A' AND f.deptCode = 'CSCI'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(c.facId = f.facId) WHERE e.grade = 'A' AND f.deptCode = 'CSCI'' at line 5 MariaDB [toyu]> SELECT DISTINCT s.fname, s.lname, d.deptName -> FROM Student AS s INNER JOIN department AS d ON (s.major = d.deptCode) -> INNER JOIN enroll AS e ON (s.stuId = e.stuId) -> INNER JOIN class AS c ON (e.classId = c.classId) -> INNER JOIN faculty AS f ON (c.facId = f.facId) -> WHERE e.grade = 'A' AND f.deptCode = 'CSCI'; +-------+---------+------------------------+ | fname | lname | deptName | +-------+---------+------------------------+ | Tony | Hawk | Computer Science | | Larry | Johnson | Information Technology | +-------+---------+------------------------+ 2 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT e.stuId -> FROM enroll; ERROR 1054 (42S22): Unknown column 'e.stuId' in 'field list' MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT e.stuId -> FROM enroll AS e; +--------+ | stuId | +--------+ | 100000 | | 100001 | | 100002 | | 100004 | | 100005 | | 100006 | | 100007 | | 100008 | +--------+ 8 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, s.fname, s.lname -> FROM STudent AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId); +--------+---------+---------+ | stuId | fname | lname | +--------+---------+---------+ | 100000 | Tony | Hawk | | 100001 | Mary | Hawk | | 100002 | David | Hawk | | 100004 | Larry | Johnson | | 100005 | Linda | Johnson | | 100006 | Lillian | Johnson | | 100007 | Ben | Zico | | 100008 | Bill | Ching | +--------+---------+---------+ 8 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, s.fname, s.lname -> FROM STudent AS s INNER JOIN enroll AS e ON (s.stuId = e.stuId) -> INNER JOIN enroll AS e ON (s.stuId = e.stuId); ERROR 1066 (42000): Not unique table/alias: 'e' MariaDB [toyu]> SELECT DISTINCT s.stuId, s.fname, s.lname -> FROM STudent AS s INNER JOIN enroll AS e1 ON (s.stuId = e1.stuId) -> INNER JOIN enroll AS e2 ON (s.stuId = e2.stuId); +--------+---------+---------+ | stuId | fname | lname | +--------+---------+---------+ | 100000 | Tony | Hawk | | 100001 | Mary | Hawk | | 100002 | David | Hawk | | 100004 | Larry | Johnson | | 100005 | Linda | Johnson | | 100006 | Lillian | Johnson | | 100007 | Ben | Zico | | 100008 | Bill | Ching | +--------+---------+---------+ 8 rows in set (0.001 sec) MariaDB [toyu]> SELECT DISTINCT s.*, e1.*, e2.* -> FROM STudent AS s INNER JOIN enroll AS e1 ON (s.stuId = e1.stuId) -> INNER JOIN enroll AS e2 ON (s.stuId = e2.stuId); +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+--------+---------+-------+----------+ | stuId | fname | lname | major | minor | ach | advisor | stuId | classId | grade | n_alerts | stuId | classId | grade | n_alerts | +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | 100000 | 11001 | D | 4 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | 100000 | 11001 | D | 4 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | 100000 | 11001 | D | 4 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | 100000 | 11001 | D | 4 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | 100000 | 11001 | D | 4 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | 100001 | 10001 | A- | 0 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | 100002 | 10003 | D | 4 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10002 | B+ | 2 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10002 | B+ | 2 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10002 | B+ | 2 | 100002 | 10003 | D | 4 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10003 | D | 4 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10003 | D | 4 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10003 | D | 4 | 100002 | 10003 | D | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10003 | A | 0 | 100004 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10003 | A | 0 | 100004 | 10004 | B+ | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10004 | B+ | NULL | 100004 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10004 | B+ | NULL | 100004 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | 100005 | 10006 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | 100005 | 10006 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | 100005 | 10006 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | 100005 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10004 | C+ | NULL | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10004 | C+ | NULL | 100006 | 10005 | A | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10005 | A | NULL | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10005 | A | NULL | 100006 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10007 | F | 4 | 100007 | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10007 | F | 4 | 100007 | 10008 | A- | 0 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10008 | A- | 0 | 100007 | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10008 | A- | 0 | 100007 | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100008 | 10007 | C- | 0 | 100008 | 10007 | C- | 0 | +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+--------+---------+-------+----------+ 78 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT DISTINCT s.stuId, s.fname, s.lname -> FROM STudent AS s INNER JOIN enroll AS e1 ON (s.stuId = e1.stuId) -> INNER JOIN enroll AS e2 ON (s.stuId = e2.stuId) -> WHERE e1.classId <> e2.classId; +--------+---------+---------+ | stuId | fname | lname | +--------+---------+---------+ | 100000 | Tony | Hawk | | 100001 | Mary | Hawk | | 100002 | David | Hawk | | 100004 | Larry | Johnson | | 100005 | Linda | Johnson | | 100006 | Lillian | Johnson | | 100007 | Ben | Zico | +--------+---------+---------+ 7 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> noitee -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'noitee' at line 1 MariaDB [toyu]> notee