MariaDB [(none)]> use toyu; Database changed MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> MariaDB [toyu]> SELECT s.*, e.* -> FROM student AS s CROSS JOIN enroll AS e; +--------+-----------+----------+-------+-------+------+---------+--------+---------+-------+----------+ | stuId | fname | lname | major | minor | ach | advisor | stuId | classId | grade | n_alerts | +--------+-----------+----------+-------+-------+------+---------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100000 | 10000 | A | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100000 | 10000 | A | 0 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100000 | 10000 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100000 | 10000 | A | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100000 | 10000 | A | 0 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100000 | 10000 | A | 0 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100000 | 10000 | A | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100000 | 10000 | A | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100000 | 10000 | A | 0 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100001 | 10000 | NULL | NULL | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100001 | 10000 | NULL | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100001 | 10000 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100001 | 10000 | NULL | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100001 | 10000 | NULL | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100001 | 10000 | NULL | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100001 | 10000 | NULL | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100001 | 10000 | NULL | NULL | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100001 | 10000 | NULL | NULL | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100002 | 10000 | B- | 3 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100002 | 10000 | B- | 3 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100002 | 10000 | B- | 3 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100002 | 10000 | B- | 3 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100002 | 10000 | B- | 3 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100002 | 10000 | B- | 3 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100002 | 10000 | B- | 3 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100002 | 10000 | B- | 3 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100002 | 10000 | B- | 3 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100000 | 10001 | A | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100000 | 10001 | A | 2 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100000 | 10001 | A | 2 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100000 | 10001 | A | 2 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100000 | 10001 | A | 2 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100000 | 10001 | A | 2 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100000 | 10001 | A | 2 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100000 | 10001 | A | 2 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100000 | 10001 | A | 2 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100001 | 10001 | A- | 0 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100001 | 10001 | A- | 0 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100001 | 10001 | A- | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100001 | 10001 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100001 | 10001 | A- | 0 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100001 | 10001 | A- | 0 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100001 | 10001 | A- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100001 | 10001 | A- | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100001 | 10001 | A- | 0 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100001 | 10001 | A- | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100000 | 10002 | B+ | 1 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100000 | 10002 | B+ | 1 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100000 | 10002 | B+ | 1 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100000 | 10002 | B+ | 1 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100000 | 10002 | B+ | 1 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100000 | 10002 | B+ | 1 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100000 | 10002 | B+ | 1 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100000 | 10002 | B+ | 1 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100000 | 10002 | B+ | 1 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100002 | 10002 | B+ | 2 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10002 | B+ | 2 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100002 | 10002 | B+ | 2 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100002 | 10002 | B+ | 2 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100002 | 10002 | B+ | 2 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100002 | 10002 | B+ | 2 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100002 | 10002 | B+ | 2 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100002 | 10002 | B+ | 2 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100002 | 10002 | B+ | 2 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100002 | 10002 | B+ | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100000 | 10003 | C | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100000 | 10003 | C | 0 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100000 | 10003 | C | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100000 | 10003 | C | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100000 | 10003 | C | 0 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100000 | 10003 | C | 0 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100000 | 10003 | C | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100000 | 10003 | C | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100000 | 10003 | C | 0 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100002 | 10003 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100002 | 10003 | D | 4 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10003 | D | 4 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100002 | 10003 | D | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100002 | 10003 | D | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100002 | 10003 | D | 4 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100002 | 10003 | D | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100002 | 10003 | D | 4 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100002 | 10003 | D | 4 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100002 | 10003 | D | 4 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100002 | 10003 | D | 4 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100004 | 10003 | A | 0 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100004 | 10003 | A | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100004 | 10003 | A | 0 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100004 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10003 | A | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100004 | 10003 | A | 0 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100004 | 10003 | A | 0 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100004 | 10003 | A | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100004 | 10003 | A | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100004 | 10003 | A | 0 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100004 | 10003 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100005 | 10003 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100005 | 10003 | NULL | NULL | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100005 | 10003 | NULL | NULL | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100005 | 10003 | NULL | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100005 | 10003 | NULL | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100005 | 10003 | NULL | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100005 | 10003 | NULL | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100005 | 10003 | NULL | NULL | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100005 | 10003 | NULL | NULL | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100000 | 10004 | A- | 1 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100000 | 10004 | A- | 1 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100000 | 10004 | A- | 1 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100000 | 10004 | A- | 1 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100000 | 10004 | A- | 1 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100000 | 10004 | A- | 1 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100000 | 10004 | A- | 1 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100000 | 10004 | A- | 1 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100000 | 10004 | A- | 1 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100004 | 10004 | B+ | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100004 | 10004 | B+ | NULL | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100004 | 10004 | B+ | NULL | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100004 | 10004 | B+ | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100004 | 10004 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100004 | 10004 | B+ | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100004 | 10004 | B+ | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100004 | 10004 | B+ | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100004 | 10004 | B+ | NULL | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100004 | 10004 | B+ | NULL | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100005 | 10004 | A- | 0 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100005 | 10004 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100005 | 10004 | A- | 0 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100005 | 10004 | A- | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100005 | 10004 | A- | 0 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100005 | 10004 | A- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100005 | 10004 | A- | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100005 | 10004 | A- | 0 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100005 | 10004 | A- | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100006 | 10004 | C+ | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100006 | 10004 | C+ | NULL | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100006 | 10004 | C+ | NULL | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100006 | 10004 | C+ | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100006 | 10004 | C+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10004 | C+ | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100006 | 10004 | C+ | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100006 | 10004 | C+ | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100006 | 10004 | C+ | NULL | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100006 | 10004 | C+ | NULL | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100005 | 10005 | A- | 0 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100005 | 10005 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100005 | 10005 | A- | 0 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100005 | 10005 | A- | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100005 | 10005 | A- | 0 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100005 | 10005 | A- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100005 | 10005 | A- | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100005 | 10005 | A- | 0 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100005 | 10005 | A- | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100006 | 10005 | A | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100006 | 10005 | A | NULL | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100006 | 10005 | A | NULL | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100006 | 10005 | A | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100006 | 10005 | A | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100006 | 10005 | A | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100006 | 10005 | A | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100006 | 10005 | A | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100006 | 10005 | A | NULL | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100006 | 10005 | A | NULL | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100005 | 10006 | B+ | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100005 | 10006 | B+ | NULL | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100005 | 10006 | B+ | NULL | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100005 | 10006 | B+ | NULL | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100005 | 10006 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100005 | 10006 | B+ | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100005 | 10006 | B+ | NULL | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100005 | 10006 | B+ | NULL | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100005 | 10006 | B+ | NULL | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100005 | 10006 | B+ | NULL | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100007 | 10007 | F | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100007 | 10007 | F | 4 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100007 | 10007 | F | 4 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100007 | 10007 | F | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100007 | 10007 | F | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100007 | 10007 | F | 4 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100007 | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10007 | F | 4 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100007 | 10007 | F | 4 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100007 | 10007 | F | 4 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100007 | 10007 | F | 4 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100008 | 10007 | C- | 0 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100008 | 10007 | C- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100008 | 10007 | C- | 0 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100008 | 10007 | C- | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100008 | 10007 | C- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100008 | 10007 | C- | 0 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100008 | 10007 | C- | 0 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100008 | 10007 | C- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100008 | 10007 | C- | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100008 | 10007 | C- | 0 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100008 | 10007 | C- | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100007 | 10008 | A- | 0 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100007 | 10008 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100007 | 10008 | A- | 0 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100007 | 10008 | A- | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100007 | 10008 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100007 | 10008 | A- | 0 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100007 | 10008 | A- | 0 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100007 | 10008 | A- | 0 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100007 | 10008 | A- | 0 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100007 | 10008 | A- | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100000 | 11001 | D | 4 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100000 | 11001 | D | 4 | | 100003 | Catherine | Lim | ITEC | CINF | 20 | NULL | 100000 | 11001 | D | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100000 | 11001 | D | 4 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100000 | 11001 | D | 4 | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100000 | 11001 | D | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100000 | 11001 | D | 4 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100000 | 11001 | D | 4 | | 100009 | Linda | King | ARTS | CSCI | 125 | 1018 | 100000 | 11001 | D | 4 | | 100111 | Cathy | Johanson | NULL | NULL | 0 | 1018 | 100000 | 11001 | D | 4 | +--------+-----------+----------+-------+-------+------+---------+--------+---------+-------+----------+ 242 rows in set (0.018 sec) MariaDB [toyu]> MariaDB [toyu]> -- Simulating equi-join in MySQL. MariaDB [toyu]> SELECT s.*, e.* -> FROM student AS s CROSS JOIN enroll AS e USING(stuId); +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | stuId | fname | lname | major | minor | ach | advisor | stuId | classId | grade | n_alerts | +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 100000 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 100001 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 100002 | 10003 | D | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 100004 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 100005 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 100006 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 100007 | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 100008 | 10007 | C- | 0 | +--------+---------+---------+-------+-------+------+---------+--------+---------+-------+----------+ 22 rows in set (0.003 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT s.*, e.classId, e.grade, e.n_alerts -> FROM student AS s CROSS JOIN enroll AS e USING(stuId); +--------+---------+---------+-------+-------+------+---------+---------+-------+----------+ | stuId | fname | lname | major | minor | ach | advisor | classId | grade | n_alerts | +--------+---------+---------+-------+-------+------+---------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10003 | D | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 10007 | C- | 0 | +--------+---------+---------+-------+-------+------+---------+---------+-------+----------+ 22 rows in set (0.001 sec) MariaDB [toyu]> SELECT * -> FROM student AS s NATURAL JOIN enroll AS e; +--------+---------+---------+-------+-------+------+---------+---------+-------+----------+ | stuId | fname | lname | major | minor | ach | advisor | classId | grade | n_alerts | +--------+---------+---------+-------+-------+------+---------+---------+-------+----------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10000 | A | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10001 | A | 2 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10002 | B+ | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10003 | C | 0 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 10004 | A- | 1 | | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | 11001 | D | 4 | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 10000 | NULL | NULL | | 100001 | Mary | Hawk | CSCI | CINF | 35 | 1011 | 10001 | A- | 0 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10000 | B- | 3 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10002 | B+ | 2 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | 10003 | D | 4 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 10003 | A | 0 | | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | 10004 | B+ | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10003 | NULL | NULL | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10004 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10005 | A- | 0 | | 100005 | Linda | Johnson | CINF | ENGL | 13 | 1015 | 10006 | B+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 10004 | C+ | NULL | | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | 10005 | A | NULL | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 10007 | F | 4 | | 100007 | Ben | Zico | NULL | NULL | 16 | NULL | 10008 | A- | 0 | | 100008 | Bill | Ching | ARTS | NULL | 90 | NULL | 10007 | C- | 0 | +--------+---------+---------+-------+-------+------+---------+---------+-------+----------+ 22 rows in set (0.001 sec) MariaDB [toyu]> SELECT 1 UNION SELECT 2; +---+ | 1 | +---+ | 1 | | 2 | +---+ 2 rows in set (0.001 sec) MariaDB [toyu]> EXPLAIN SELECT * -> FROM student AS s NATURAL JOIN enroll AS e; +------+-------------+-------+------+-----------------+-----------------+---------+--------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+-----------------+-----------------+---------+--------------+------+-------+ | 1 | SIMPLE | s | ALL | PRIMARY | NULL | NULL | NULL | 11 | | | 1 | SIMPLE | e | ref | Enroll_stuId_fk | Enroll_stuId_fk | 4 | toyu.s.stuId | 1 | | +------+-------------+-------+------+-----------------+-----------------+---------+--------------+------+-------+ 2 rows in set (0.002 sec) MariaDB [toyu]> SET @sql = "SELECT * FROM toyu.student"; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.001 sec) Statement prepared MariaDB [toyu]> EXECUTE stmt; +--------+-----------+----------+-------+-------+------+---------+ | 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]> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SELECT @sql; +----------------------------+ | @sql | +----------------------------+ | SELECT * FROM toyu.student | +----------------------------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> -- with placeholders. MariaDB [toyu]> SET @sql = "SELECT * FROM toyu.student WHERE major = ? AND ach >= ?"; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.001 sec) Statement prepared MariaDB [toyu]> SET @major = 'CSCI'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @ach = 38; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+-------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+-------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | +--------+-------+-------+-------+-------+------+---------+ 2 rows in set (0.004 sec) MariaDB [toyu]> MariaDB [toyu]> EXECUTE stmt USING 'CSCI', 38; +--------+-------+-------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+-------+-------+-------+------+---------+ | 100000 | Tony | Hawk | CSCI | CINF | 40 | 1011 | | 100002 | David | Hawk | CSCI | ITEC | 66 | 1012 | +--------+-------+-------+-------+-------+------+---------+ 2 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @major = 'CINF'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @ach = 15; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+---------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+---------+---------+-------+-------+------+---------+ | 100006 | Lillian | Johnson | CINF | ITEC | 18 | 1016 | +--------+---------+---------+-------+-------+------+---------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @major = 'ITEC'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> SET @ach = 25; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> EXECUTE stmt USING @major, @ach; +--------+-------+---------+-------+-------+------+---------+ | stuId | fname | lname | major | minor | ach | advisor | +--------+-------+---------+-------+-------+------+---------+ | 100004 | Larry | Johnson | ITEC | NULL | 66 | 1017 | +--------+-------+---------+-------+-------+------+---------+ 1 row in set (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT s.schoolCode, s.schoolName, -> COUNT(d.deptCode) AS n_departments -> FROM school AS s LEFT JOIN department AS d ON (s.schoolCode = d.schoolCode) -> GROUP BY s.schoolCode, s.schoolName; +------------+-------------------------------+---------------+ | schoolCode | schoolName | n_departments | +------------+-------------------------------+---------------+ | BUS | Business | 1 | | CSE | Science and Engineering | 4 | | EDU | Education | 0 | | HSH | Human Sciences and Humanities | 2 | +------------+-------------------------------+---------------+ 4 rows in set (0.002 sec) MariaDB [toyu]> CREATE OR REPLACE VIEW school_summary( -> schoolCode, schoolName, n_departments) AS -> SELECT s.schoolCode, s.schoolName, -> COUNT(d.deptCode) AS n_departments -> FROM school AS s LEFT JOIN department AS d ON (s.schoolCode = d.schoolCode) -> GROUP BY s.schoolCode, s.schoolName; Query OK, 0 rows affected (0.009 sec) MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE VIEW school_summary; +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | school_summary | CREATE ALGORITHM=UNDEFINED DEFINER=`yue`@`localhost` SQL SECURITY DEFINER VIEW `school_summary` AS select `s`.`schoolCode` AS `schoolCode`,`s`.`schoolName` AS `schoolName`,count(`d`.`deptCode`) AS `n_departments` from (`school` `s` left join `department` `d` on(`s`.`schoolCode` = `d`.`schoolCode`)) group by `s`.`schoolCode`,`s`.`schoolName` | cp850 | cp850_general_ci | +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.001 sec) MariaDB [toyu]> -- Note something like "ALGORITHM=UNDEFINED DEFINER=`yue`@`localhost` SQL SECURITY DEFINER" MariaDB [toyu]> -- (default values) may be added. MariaDB [toyu]> DESC school_summary; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | schoolCode | char(3) | NO | | NULL | | | schoolName | varchar(30) | NO | | NULL | | | n_departments | bigint(21) | NO | | 0 | | +---------------+-------------+------+-----+---------+-------+ 3 rows in set (0.011 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM school_summary -> WHERE n_departments > 0; +------------+-------------------------------+---------------+ | schoolCode | schoolName | n_departments | +------------+-------------------------------+---------------+ | BUS | Business | 1 | | CSE | Science and Engineering | 4 | | HSH | Human Sciences and Humanities | 2 | +------------+-------------------------------+---------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> DROP VIEW school_summary; Query OK, 0 rows affected (0.004 sec) MariaDB [toyu]> CREATE OR REPLACE VIEW school_summary( -> schoolCode, schoolName, n_departments) AS -> SELECT s.schoolCode, s.schoolName, -> COUNT(d.deptCode) AS n_departments -> FROM school AS s LEFT JOIN department AS d ON (s.schoolCode = d.schoolCode) -> GROUP BY s.schoolCode, s.schoolName; Query OK, 0 rows affected (0.010 sec) MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE VIEW school_summary; +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | school_summary | CREATE ALGORITHM=UNDEFINED DEFINER=`yue`@`localhost` SQL SECURITY DEFINER VIEW `school_summary` AS select `s`.`schoolCode` AS `schoolCode`,`s`.`schoolName` AS `schoolName`,count(`d`.`deptCode`) AS `n_departments` from (`school` `s` left join `department` `d` on(`s`.`schoolCode` = `d`.`schoolCode`)) group by `s`.`schoolCode`,`s`.`schoolName` | cp850 | cp850_general_ci | +----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.003 sec) MariaDB [toyu]> -- Note something like "ALGORITHM=UNDEFINED DEFINER=`yue`@`localhost` SQL SECURITY DEFINER" MariaDB [toyu]> -- (default values) may be added. MariaDB [toyu]> DESC school_summary; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | schoolCode | char(3) | NO | | NULL | | | schoolName | varchar(30) | NO | | NULL | | | n_departments | bigint(21) | NO | | 0 | | +---------------+-------------+------+-----+---------+-------+ 3 rows in set (0.011 sec) MariaDB [toyu]> MariaDB [toyu]> SELECT * -> FROM school_summary -> WHERE n_departments > 0; +------------+-------------------------------+---------------+ | schoolCode | schoolName | n_departments | +------------+-------------------------------+---------------+ | BUS | Business | 1 | | CSE | Science and Engineering | 4 | | HSH | Human Sciences and Humanities | 2 | +------------+-------------------------------+---------------+ 3 rows in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> -- A very simple stored procedure. MariaDB [toyu]> -- Redefine the delimiter to end the procedure. MariaDB [toyu]> DELIMITER // MariaDB [toyu]> MariaDB [toyu]> CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT) -> BEGIN -> -- Display some information. -> SELECT d.deptName, d.SchoolCode, t1.n_majors, t2.n_minors -> FROM department AS d INNER JOIN -> (SELECT COUNT(stuId) AS n_majors -> FROM student -> WHERE major = dCode) AS t1 INNER JOIN -> (SELECT COUNT(stuId) AS n_minors -> FROM student -> WHERE minor = dCode) AS t2 -> WHERE d.deptCode = dCode; -> -> -- MySQL does not direct sending output to console. -> -- It is necessary to use a SQL statement. -> SELECT 'Debuggin comment can be put here.'; -> SELECT CONCAT('Faculty in the department: ', dCode) AS faculty; -> -> SELECT * -> FROM faculty AS f -> WHERE f.deptCode = dCode; -> -> SELECT COUNT(f.facId) INTO numFaculty -> FROM faculty AS f -> WHERE f.deptCode = dCode; -> END // Query OK, 0 rows affected (0.010 sec) MariaDB [toyu]> MariaDB [toyu]> DELIMITER ; MariaDB [toyu]> MariaDB [toyu]> SHOW CREATE PROCEDURE deptInfo; +-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | deptInfo | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` PROCEDURE `deptInfo`(IN dCode VARCHAR(4), OUT numFaculty INT) BEGIN SELECT d.deptName, d.SchoolCode, t1.n_majors, t2.n_minors FROM department AS d INNER JOIN (SELECT COUNT(stuId) AS n_majors FROM student WHERE major = dCode) AS t1 INNER JOIN (SELECT COUNT(stuId) AS n_minors FROM student WHERE minor = dCode) AS t2 WHERE d.deptCode = dCode; SELECT 'Debuggin comment can be put here.'; SELECT CONCAT('Faculty in the department: ', dCode) AS faculty; SELECT * FROM faculty AS f WHERE f.deptCode = dCode; SELECT COUNT(f.facId) INTO numFaculty FROM faculty AS f WHERE f.deptCode = dCode; END | cp850 | cp850_general_ci | utf8mb4_general_ci | +-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @numFaculty = 0; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> SET @dCode = 'CSCI'; Query OK, 0 rows affected (0.000 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); +------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------+------------+----------+----------+ | Computer Science | CSE | 3 | 1 | +------------------+------------+----------+----------+ 1 row in set (0.005 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.106 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: CSCI | +---------------------------------+ 1 row in set (0.222 sec) +-------+--------+--------+----------+---------------------+ | facId | fname | lname | deptCode | rank | +-------+--------+--------+----------+---------------------+ | 1011 | Paul | Smith | CSCI | Professor | | 1012 | Mary | Tran | CSCI | Associate Professor | | 1013 | David | Love | CSCI | NULL | | 1014 | Sharon | Mannes | CSCI | Assistant Professor | +-------+--------+--------+----------+---------------------+ 4 rows in set (0.316 sec) Query OK, 1 row affected (0.445 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | CSCI | 4 | +--------+-------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> SET @dCode = 'ITEC'; Query OK, 0 rows affected (0.001 sec) MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty); +------------------------+------------+----------+----------+ | deptName | SchoolCode | n_majors | n_minors | +------------------------+------------+----------+----------+ | Information Technology | CSE | 2 | 2 | +------------------------+------------+----------+----------+ 1 row in set (0.001 sec) +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ | Debuggin comment can be put here. | +-----------------------------------+ 1 row in set (0.090 sec) +---------------------------------+ | faculty | +---------------------------------+ | Faculty in the department: ITEC | +---------------------------------+ 1 row in set (0.165 sec) +-------+----------+-------+----------+-----------+ | facId | fname | lname | deptCode | rank | +-------+----------+-------+----------+-----------+ | 1017 | Deborah | Gump | ITEC | Professor | | 1019 | Benjamin | Yu | ITEC | Lecturer | +-------+----------+-------+----------+-----------+ 2 rows in set (0.280 sec) Query OK, 1 row affected (0.411 sec) MariaDB [toyu]> SELECT @dCode, @numFaculty; +--------+-------------+ | @dCode | @numFaculty | +--------+-------------+ | ITEC | 2 | +--------+-------------+ 1 row in set (0.001 sec) MariaDB [toyu]> MariaDB [toyu]> DROP PROCEDURE deptInfo; Query OK, 0 rows affected (0.015 sec) MariaDB [toyu]> notee