MariaDB [toyu]> CREATE SCHEMA tinker;
Query OK, 1 row affected (0.005 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SHOW TABLE;
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 '' at line 1
MariaDB [toyu]> SHOW TABLES;
+----------------+
| Tables_in_toyu |
+----------------+
| class          |
| course         |
| department     |
| enroll         |
| faculty        |
| grade          |
| s1             |
| s3             |
| school         |
| student        |
+----------------+
10 rows in set (0.004 sec)

MariaDB [toyu]> use tinker;
Database changed
MariaDB [tinker]> SHOW TABLES;
Empty set (0.002 sec)

MariaDB [tinker]> CREATE TABLE p
    -> ( A CHAR(2));
Query OK, 0 rows affected (0.057 sec)

MariaDB [tinker]> CREATE TABLE r IF NOT EXISTS 
    -> ( A CHAR(2) NOT NULL);
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 'IF NOT EXISTS 
( A CHAR(2) NOT NULL)' at line 1
MariaDB [tinker]> CREATE TABLE s2
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.046 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s2;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [tinker]> 
MariaDB [tinker]> CREATE TEMPORARY TABLE s3
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.007 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s3;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [tinker]> 
MariaDB [tinker]> CREATE TABLE s4 LIKE toyu.student;
Query OK, 0 rows affected (0.045 sec)

MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s4;
Empty set (0.002 sec)

MariaDB [tinker]> 
MariaDB [tinker]> INSERT INTO s4
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.005 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s4;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.002 sec)

MariaDB [tinker]> 
MariaDB [tinker]> SHOW TABLES;
+------------------+
| Tables_in_tinker |
+------------------+
| p                |
| s2               |
| s4               |
+------------------+
3 rows in set (0.002 sec)

MariaDB [tinker]> 
MariaDB [tinker]> -- Note that keys and constraints of student are missing in s2 and S3.
MariaDB [tinker]> DESC student;
ERROR 1146 (42S02): Table 'tinker.student' doesn't exist
MariaDB [tinker]> DESC s2;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   |     | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  |     | NULL    |       |
| minor   | char(4)         | YES  |     | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  |     | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.023 sec)

MariaDB [tinker]> DESC s3;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   |     | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  |     | NULL    |       |
| minor   | char(4)         | YES  |     | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  |     | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.012 sec)

MariaDB [tinker]> DESC s4;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   | PRI | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  | MUL | NULL    |       |
| minor   | char(4)         | YES  | MUL | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  | MUL | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.021 sec)

MariaDB [tinker]> 
MariaDB [tinker]> DROP TABLE s2;
Query OK, 0 rows affected (0.037 sec)

MariaDB [tinker]> DROP TABLE s3;
Query OK, 0 rows affected (0.003 sec)

MariaDB [tinker]> DROP TABLE s4;
Query OK, 0 rows affected (0.022 sec)

MariaDB [tinker]> 
MariaDB [tinker]> SHOW TABLES;
+------------------+
| Tables_in_tinker |
+------------------+
| p                |
+------------------+
1 row in set (0.002 sec)

MariaDB [tinker]> 
MariaDB [tinker]> 
MariaDB [tinker]> CREATE TABLE s2
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.043 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> -- create a permanent table in the MySQL server.
MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s2;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [tinker]> 
MariaDB [tinker]> CREATE TEMPORARY TABLE s3
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.005 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> -- create as a temporate table in the current client session; like a session variable.
MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s3;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [tinker]> 
MariaDB [tinker]> CREATE TABLE s4 LIKE toyu.student;
Query OK, 0 rows affected (0.040 sec)

MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s4;
Empty set (0.003 sec)

MariaDB [tinker]> 
MariaDB [tinker]> INSERT INTO s4
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.008 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s4;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [tinker]> 
MariaDB [tinker]> SHOW TABLES;
+------------------+
| Tables_in_tinker |
+------------------+
| s2               |
| s4               |
+------------------+
2 rows in set (0.004 sec)

MariaDB [tinker]> 
MariaDB [tinker]> -- Note that keys and constraints of student are missing in s2 and S3.
MariaDB [tinker]> DESC student;
ERROR 1146 (42S02): Table 'tinker.student' doesn't exist
MariaDB [tinker]> DESC s2;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   |     | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  |     | NULL    |       |
| minor   | char(4)         | YES  |     | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  |     | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.021 sec)

MariaDB [tinker]> DESC s3;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   |     | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  |     | NULL    |       |
| minor   | char(4)         | YES  |     | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  |     | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.022 sec)

MariaDB [tinker]> DESC s4;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   | PRI | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  | MUL | NULL    |       |
| minor   | char(4)         | YES  | MUL | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  | MUL | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.035 sec)

MariaDB [tinker]> 
MariaDB [tinker]> DROP TABLE s2;
Query OK, 0 rows affected (0.021 sec)

MariaDB [tinker]> DROP TABLE s3;
Query OK, 0 rows affected (0.003 sec)

MariaDB [tinker]> DROP TABLE s4;
Query OK, 0 rows affected (0.021 sec)

MariaDB [tinker]> 
MariaDB [tinker]> SHOW TABLES;
Empty set (0.001 sec)

MariaDB [tinker]> 
MariaDB [tinker]> 
MariaDB [tinker]> CREATE TABLE s2
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.067 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> -- create a permanent table in the MySQL server.
MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s2;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [tinker]> 
MariaDB [tinker]> CREATE TEMPORARY TABLE s3
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.008 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> -- create as a temporate table in the current client session; like a session variable.
MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s3;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [tinker]> 
MariaDB [tinker]> CREATE TABLE s4 LIKE toyu.student;
Query OK, 0 rows affected (0.035 sec)

MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s4;
Empty set (0.002 sec)

MariaDB [tinker]> 
MariaDB [tinker]> INSERT INTO s4
    -> SELECT * FROM toyu.student;
Query OK, 11 rows affected (0.007 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [tinker]> 
MariaDB [tinker]> SELECT *
    -> FROM s4;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [tinker]> 
MariaDB [tinker]> SHOW TABLES;
+------------------+
| Tables_in_tinker |
+------------------+
| s2               |
| s4               |
+------------------+
2 rows in set (0.002 sec)

MariaDB [tinker]> SELECT * FROM s3;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [tinker]> SET @x = 10;
Query OK, 0 rows affected (0.000 sec)

MariaDB [tinker]> SELECT @x;
+------+
| @x   |
+------+
|   10 |
+------+
1 row in set (0.001 sec)

MariaDB [tinker]> desc s2;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   |     | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  |     | NULL    |       |
| minor   | char(4)         | YES  |     | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  |     | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.018 sec)

MariaDB [tinker]> desc toyu.student;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| stuId   | int(11)         | NO   | PRI | NULL    |       |
| fname   | varchar(30)     | NO   |     | NULL    |       |
| lname   | varchar(30)     | NO   |     | NULL    |       |
| major   | char(4)         | YES  | MUL | NULL    |       |
| minor   | char(4)         | YES  | MUL | NULL    |       |
| ach     | int(3) unsigned | YES  |     | 0       |       |
| advisor | int(11)         | YES  | MUL | NULL    |       |
+---------+-----------------+------+-----+---------+-------+
7 rows in set (0.023 sec)

MariaDB [tinker]> DROP SCHEMA IF EXISTS tinker;
Query OK, 2 rows affected (0.066 sec)

MariaDB [(none)]> UPDATE Student
    -> SET major = 'ITEC'
    -> WHERE StuId = 100000;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> use toyu;
Database changed
MariaDB [toyu]> UPDATE Student
    -> SET major = 'ITEC'
    -> WHERE StuId = 100000;
Query OK, 1 row affected (0.010 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [toyu]> UPDATE Student
    -> SET major = 'ITEC';
Query OK, 8 rows affected (0.008 sec)
Rows matched: 11  Changed: 8  Warnings: 0

MariaDB [toyu]> sourse createtoyu.sql
    -> ;
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 'sourse createtoyu.sql' at line 1
MariaDB [toyu]> source createtoyu.sql
Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 8 rows affected (0.173 sec)

Query OK, 1 row affected (0.004 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.002 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.002 sec)

Query OK, 0 rows affected, 1 warning (0.001 sec)

Query OK, 0 rows affected, 1 warning (0.028 sec)

Query OK, 0 rows affected, 1 warning (0.029 sec)

Query OK, 0 rows affected, 1 warning (0.037 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.033 sec)

Query OK, 0 rows affected, 1 warning (0.037 sec)

Query OK, 0 rows affected, 1 warning (0.032 sec)

Query OK, 0 rows affected (0.020 sec)

Query OK, 0 rows affected (0.014 sec)

Query OK, 0 rows affected (0.013 sec)

Query OK, 0 rows affected (0.016 sec)

Query OK, 0 rows affected (0.010 sec)

Query OK, 15 rows affected (0.008 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.008 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.007 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.018 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 14 rows affected (0.018 sec)
Records: 14  Duplicates: 0  Warnings: 0

Query OK, 11 rows affected (0.006 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query OK, 22 rows affected (0.015 sec)
Records: 22  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT *
    -> FROM Student
    -> WHERE credits BETWEEN 30 AND 70;
ERROR 1054 (42S22): Unknown column 'credits' in 'where clause'
MariaDB [toyu]> use toyu;
Database changed
MariaDB [toyu]> SELECT DISTINCT *
    -> FROM Student
    -> WHERE ach BETWEEN 30 AND 70;
+--------+-------+---------+-------+-------+------+---------+
| stuId  | fname | lname   | major | minor | ach  | advisor |
+--------+-------+---------+-------+-------+------+---------+
| 100000 | Tony  | Hawk    | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary  | Hawk    | CSCI  | CINF  |   35 |    1011 |
| 100002 | David | Hawk    | CSCI  | ITEC  |   66 |    1012 |
| 100004 | Larry | Johnson | ITEC  | NULL  |   66 |    1017 |
+--------+-------+---------+-------+-------+------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT DISTINCT *
    -> FROM Student
    -> WHERE ach BETWEEN 35 AND 70;
+--------+-------+---------+-------+-------+------+---------+
| stuId  | fname | lname   | major | minor | ach  | advisor |
+--------+-------+---------+-------+-------+------+---------+
| 100000 | Tony  | Hawk    | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary  | Hawk    | CSCI  | CINF  |   35 |    1011 |
| 100002 | David | Hawk    | CSCI  | ITEC  |   66 |    1012 |
| 100004 | Larry | Johnson | ITEC  | NULL  |   66 |    1017 |
+--------+-------+---------+-------+-------+------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT *
    -> FROM Student
    -> WHERE ach BETWEEN 35 AND 66;
+--------+-------+---------+-------+-------+------+---------+
| stuId  | fname | lname   | major | minor | ach  | advisor |
+--------+-------+---------+-------+-------+------+---------+
| 100000 | Tony  | Hawk    | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary  | Hawk    | CSCI  | CINF  |   35 |    1011 |
| 100002 | David | Hawk    | CSCI  | ITEC  |   66 |    1012 |
| 100004 | Larry | Johnson | ITEC  | NULL  |   66 |    1017 |
+--------+-------+---------+-------+-------+------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT *
    -> FROM Student
    -> WHERE ach >= 30 AND 
    -> ach <= 70;
+--------+-------+---------+-------+-------+------+---------+
| stuId  | fname | lname   | major | minor | ach  | advisor |
+--------+-------+---------+-------+-------+------+---------+
| 100000 | Tony  | Hawk    | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary  | Hawk    | CSCI  | CINF  |   35 |    1011 |
| 100002 | David | Hawk    | CSCI  | ITEC  |   66 |    1012 |
| 100004 | Larry | Johnson | ITEC  | NULL  |   66 |    1017 |
+--------+-------+---------+-------+-------+------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.001 sec)

MariaDB [toyu]> SELECT s.stuId, d.deptName AS major
    -> FROM Student AS s INNER JOIN department AS f 
    ->     ON (s.major = d.deptCode);
ERROR 1054 (42S22): Unknown column 'd.deptName' in 'field list'
MariaDB [toyu]> SELECT s.stuId, d.deptName AS major
    -> FROM Student AS s INNER JOIN department AS d 
    ->     ON (s.major = d.deptCode);
+--------+------------------------------+
| stuId  | major                        |
+--------+------------------------------+
| 100008 | Arts                         |
| 100009 | Arts                         |
| 100005 | Computer Information Systems |
| 100006 | Computer Information Systems |
| 100000 | Computer Science             |
| 100001 | Computer Science             |
| 100002 | Computer Science             |
| 100003 | Information Technology       |
| 100004 | Information Technology       |
+--------+------------------------------+
9 rows in set (0.005 sec)

MariaDB [toyu]> SELECT s.stuId, d.deptName AS major
    -> FROM Student AS s LEFT JOIN department AS d 
    ->     ON (s.major = d.deptCode);
+--------+------------------------------+
| stuId  | major                        |
+--------+------------------------------+
| 100007 | NULL                         |
| 100111 | NULL                         |
| 100008 | Arts                         |
| 100009 | Arts                         |
| 100005 | Computer Information Systems |
| 100006 | Computer Information Systems |
| 100000 | Computer Science             |
| 100001 | Computer Science             |
| 100002 | Computer Science             |
| 100003 | Information Technology       |
| 100004 | Information Technology       |
+--------+------------------------------+
11 rows in set (0.010 sec)

MariaDB [toyu]> SELECT s.stuId, IF(d.deptName IS NULL, 'No major', d.deptName) AS major
    -> FROM Student AS s LEFT JOIN department AS d 
    ->     ON (s.major = d.deptCode);
+--------+------------------------------+
| stuId  | major                        |
+--------+------------------------------+
| 100007 | No major                     |
| 100111 | No major                     |
| 100008 | Arts                         |
| 100009 | Arts                         |
| 100005 | Computer Information Systems |
| 100006 | Computer Information Systems |
| 100000 | Computer Science             |
| 100001 | Computer Science             |
| 100002 | Computer Science             |
| 100003 | Information Technology       |
| 100004 | Information Technology       |
+--------+------------------------------+
11 rows in set (0.003 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.stuId, IFNULL(d.deptName, 'No major') AS major
    -> FROM Student AS s LEFT JOIN department AS d 
    ->     ON (s.major = d.deptCode);
+--------+------------------------------+
| stuId  | major                        |
+--------+------------------------------+
| 100007 | No major                     |
| 100111 | No major                     |
| 100008 | Arts                         |
| 100009 | Arts                         |
| 100005 | Computer Information Systems |
| 100006 | Computer Information Systems |
| 100000 | Computer Science             |
| 100001 | Computer Science             |
| 100002 | Computer Science             |
| 100003 | Information Technology       |
| 100004 | Information Technology       |
+--------+------------------------------+
11 rows in set (0.004 sec)

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 *
    -> FROM student AS s
    -> WHERE s.stuId NOT IN (SELECT DISTINCT e.stuID FROM enroll AS e);
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
3 rows in set (0.007 sec)

MariaDB [toyu]> SELECT DISTINCT s.stuId,
    ->    CONCAT(s.fname, ' ', s.lname) AS student,
    ->    s.ach AS credits
    -> FROM student AS s INNER JOIN
    ->    (SELECT DISTINCT MAX(ach) AS max FROM student) AS m -- an alias is required.
    -> WHERE s.ach + 60 >= m.max;
+--------+---------------+---------+
| stuId  | student       | credits |
+--------+---------------+---------+
| 100002 | David Hawk    |      66 |
| 100004 | Larry Johnson |      66 |
| 100008 | Bill Ching    |      90 |
| 100009 | Linda King    |     125 |
+--------+---------------+---------+
4 rows in set (0.004 sec)

MariaDB [toyu]> SELECT DISTINCT MAX(ach) AS max FROM student;
+------+
| max  |
+------+
|  125 |
+------+
1 row in set (0.001 sec)

MariaDB [toyu]> -- student within 60 credits of the maximum number of ach any student may have.
MariaDB [toyu]> SELECT DISTINCT s.stuId,
    ->    CONCAT(s.fname, ' ', s.lname) AS student,
    ->    s.ach AS credits
    -> FROM student AS s
    -> WHERE s.ach + 60 >=
    ->    (SELECT DISTINCT MAX(ach) FROM student);
+--------+---------------+---------+
| stuId  | student       | credits |
+--------+---------------+---------+
| 100002 | David Hawk    |      66 |
| 100004 | Larry Johnson |      66 |
| 100008 | Bill Ching    |      90 |
| 100009 | Linda King    |     125 |
+--------+---------------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> SELECT DISTINCT MAX(ach) FROM student;
+----------+
| MAX(ach) |
+----------+
|      125 |
+----------+
1 row in set (0.001 sec)

MariaDB [toyu]> WITH m AS
    ->     (SELECT DISTINCT MAX(ach) AS max FROM student)
    -> SELECT DISTINCT s.stuId,
    ->    CONCAT(s.fname, ' ', s.lname) AS student,
    ->    s.ach AS credits
    -> FROM student AS s, m
    -> WHERE s.ach + 60 >= m.max;
+--------+---------------+---------+
| stuId  | student       | credits |
+--------+---------------+---------+
| 100002 | David Hawk    |      66 |
| 100004 | Larry Johnson |      66 |
| 100008 | Bill Ching    |      90 |
| 100009 | Linda King    |     125 |
+--------+---------------+---------+
4 rows in set (0.001 sec)

MariaDB [toyu]> -- multiple common tables (not efficient; used as demonstration.)
MariaDB [toyu]> WITH t1 AS
    ->    (SELECT MAX(ach) AS max FROM student),
    -> t2 AS
    ->    (SELECT s.stuId,
    ->       s.ach AS `ach credits`,
    ->       t1.max - s.ach AS diff,
    ->       s.major
    ->     FROM student AS s, t1)
    -> SELECT t2.stuId, t2.`ach credits`,
    ->    t2.diff AS `diff from max credits of all`,
    ->    d.deptName AS department
    -> FROM t2 LEFT JOIN department d ON (t2.major = d.deptCode)
    -> ORDER BY t2.`ach credits` DESC;
+--------+-------------+------------------------------+------------------------------+
| stuId  | ach credits | diff from max credits of all | department                   |
+--------+-------------+------------------------------+------------------------------+
| 100009 |         125 |                            0 | Arts                         |
| 100008 |          90 |                           35 | Arts                         |
| 100002 |          66 |                           59 | Computer Science             |
| 100004 |          66 |                           59 | Information Technology       |
| 100000 |          40 |                           85 | Computer Science             |
| 100001 |          35 |                           90 | Computer Science             |
| 100003 |          20 |                          105 | Information Technology       |
| 100006 |          18 |                          107 | Computer Information Systems |
| 100007 |          16 |                          109 | NULL                         |
| 100005 |          13 |                          112 | Computer Information Systems |
| 100111 |           0 |                          125 | NULL                         |
+--------+-------------+------------------------------+------------------------------+
11 rows in set (0.001 sec)

MariaDB [toyu]> SELECT MAX(ach) AS max FROM student;
+------+
| max  |
+------+
|  125 |
+------+
1 row in set (0.001 sec)

MariaDB [toyu]> WITH t1 AS
    ->    (SELECT MAX(ach) AS max FROM student),
    -> SELECT s.stuId,
    ->       s.ach AS `ach credits`,
    ->       t1.max - s.ach AS diff,
    ->       s.major
    ->     FROM student AS s, t1;
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 'SELECT s.stuId,
      s.ach AS `ach credits`,
      t1.max - s.ach AS diff,
 ...' at line 3
MariaDB [toyu]> WITH t1 AS
    ->    (SELECT MAX(ach) AS max FROM student)
    -> SELECT s.stuId,
    ->       s.ach AS `ach credits`,
    ->       t1.max - s.ach AS diff,
    ->       s.major
    ->     FROM student AS s, t1;
+--------+-------------+------+-------+
| stuId  | ach credits | diff | major |
+--------+-------------+------+-------+
| 100000 |          40 |   85 | CSCI  |
| 100001 |          35 |   90 | CSCI  |
| 100002 |          66 |   59 | CSCI  |
| 100003 |          20 |  105 | ITEC  |
| 100004 |          66 |   59 | ITEC  |
| 100005 |          13 |  112 | CINF  |
| 100006 |          18 |  107 | CINF  |
| 100007 |          16 |  109 | NULL  |
| 100008 |          90 |   35 | ARTS  |
| 100009 |         125 |    0 | ARTS  |
| 100111 |           0 |  125 | NULL  |
+--------+-------------+------+-------+
11 rows in set (0.001 sec)

MariaDB [toyu]> WITH t1 AS
    ->    (SELECT MAX(ach) AS max FROM student),
    -> t2 AS
    ->    (SELECT s.stuId,
    ->       s.ach AS `ach credits`,
    ->       t1.max - s.ach AS diff,
    ->       s.major
    ->     FROM student AS s, t1)
    -> SELECT t2.stuId, t2.`ach credits`,
    ->    t2.diff AS `diff from max credits of all`,
    ->    d.deptName AS department
    -> FROM t2 LEFT JOIN department d ON (t2.major = d.deptCode)
    -> ORDER BY t2.`ach credits` DESC;
+--------+-------------+------------------------------+------------------------------+
| stuId  | ach credits | diff from max credits of all | department                   |
+--------+-------------+------------------------------+------------------------------+
| 100009 |         125 |                            0 | Arts                         |
| 100008 |          90 |                           35 | Arts                         |
| 100002 |          66 |                           59 | Computer Science             |
| 100004 |          66 |                           59 | Information Technology       |
| 100000 |          40 |                           85 | Computer Science             |
| 100001 |          35 |                           90 | Computer Science             |
| 100003 |          20 |                          105 | Information Technology       |
| 100006 |          18 |                          107 | Computer Information Systems |
| 100007 |          16 |                          109 | NULL                         |
| 100005 |          13 |                          112 | Computer Information Systems |
| 100111 |           0 |                          125 | NULL                         |
+--------+-------------+------------------------------+------------------------------+
11 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.*, s.major
    -> FROM student AS s;
+--------+-----------+----------+-------+-------+------+---------+-------+
| stuId  | fname     | lname    | major | minor | ach  | advisor | major |
+--------+-----------+----------+-------+-------+------+---------+-------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 | CSCI  |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 | CSCI  |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 | CSCI  |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL | ITEC  |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 | ITEC  |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 | CINF  |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 | CINF  |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL | NULL  |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL | ARTS  |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 | ARTS  |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 | NULL  |
+--------+-----------+----------+-------+-------+------+---------+-------+
11 rows in set (0.002 sec)

MariaDB [toyu]> stuSELECT s.stuId, s.major -- [3]
    -> FROM student AS s -- [1]
    -> WHERE ach > 0; -- [2]
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 'stuSELECT s.stuId, s.major 
FROM student AS s 
WHERE ach > 0' at line 1
MariaDB [toyu]> SELECT s.stuId, s.major -- [3]
    -> FROM student AS s -- [1]
    -> WHERE ach > 0; -- [2]
+--------+-------+
| stuId  | major |
+--------+-------+
| 100000 | CSCI  |
| 100001 | CSCI  |
| 100002 | CSCI  |
| 100003 | ITEC  |
| 100004 | ITEC  |
| 100005 | CINF  |
| 100006 | CINF  |
| 100007 | NULL  |
| 100008 | ARTS  |
| 100009 | ARTS  |
+--------+-------+
10 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.stuId, s.major -- [4] construct output columns
    -> FROM student AS s -- [1] big raw table: old raw rows
    -> WHERE ach > 0 -- [2] filter out the rows.
    -> GROUP BY s.major; -- [3] From group with the same group by column: s.major; each value of s.major defines a group. New group -> new row.
+--------+-------+
| stuId  | major |
+--------+-------+
| 100007 | NULL  |
| 100008 | ARTS  |
| 100005 | CINF  |
| 100000 | CSCI  |
| 100003 | ITEC  |
+--------+-------+
5 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `no of major` -- [4] construct output columns
    -> FROM student AS s -- [1] big raw table: old raw rows
    -> WHERE ach > 0 -- [2] filter out the rows.
    -> GROUP BY s.major; -- [3] From group with the same group by column: s.major; each value of s.major defines a group. New group -> new row.
+-------+-------------+
| major | no of major |
+-------+-------------+
| NULL  |           1 |
| ARTS  |           2 |
| CINF  |           2 |
| CSCI  |           3 |
| ITEC  |           2 |
+-------+-------------+
5 rows in set (0.002 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.stuId, s.major -- [3] construct columns
    -> FROM student AS s -- [1] big raw table: raw rows
    -> WHERE ach > 0; -- [2] filter out the rows.
+--------+-------+
| stuId  | major |
+--------+-------+
| 100000 | CSCI  |
| 100001 | CSCI  |
| 100002 | CSCI  |
| 100003 | ITEC  |
| 100004 | ITEC  |
| 100005 | CINF  |
| 100006 | CINF  |
| 100007 | NULL  |
| 100008 | ARTS  |
| 100009 | ARTS  |
+--------+-------+
10 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `no of major` -- [4] construct output columns
    -> FROM student AS s -- [1] big raw table: old raw rows
    -> WHERE ach > 0 -- [2] filter out the rows.
    -> GROUP BY s.major; -- [3] From group with the same group by column: s.major; each value of s.major defines a group. New group -> new row.
+-------+-------------+
| major | no of major |
+-------+-------------+
| NULL  |           1 |
| ARTS  |           2 |
| CINF  |           2 |
| CSCI  |           3 |
| ITEC  |           2 |
+-------+-------------+
5 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT s.major, COUNT(s.stuId) AS `no of major` -- [5] construct output columns
    -> FROM student AS s -- [1] big raw table: old raw rows
    -> WHERE ach > 0 -- [2] filter out the rows.
    -> GROUP BY s.major -- [3] From group with the same group by column: s.major; each value of s.major defines a group. New group -> new row.
    -> HAVING `no of major`  > 1;  [4] filter out the group/new row.
+-------+-------------+
| major | no of major |
+-------+-------------+
| ARTS  |           2 |
| CINF  |           2 |
| CSCI  |           3 |
| ITEC  |           2 |
+-------+-------------+
4 rows in set (0.001 sec)

    -> ;
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 '[4] filter out the group/new row.' at line 1
MariaDB [toyu]> notee