MariaDB [(none)]> use toyu
Database changed
MariaDB [toyu]> UPDATE Student
-> SET major = 'ITEC'
-> WHERE StuId = 100000;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [toyu]> UPDATE Student
-> SET major = 'CSCI';
Query OK, 9 rows affected (0.008 sec)
Rows matched: 11 Changed: 9 Warnings: 0
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.000 sec)
Query OK, 8 rows affected (0.125 sec)
Query OK, 1 row affected (0.002 sec)
Database changed
Query OK, 0 rows affected, 1 warning (0.000 sec)
Query OK, 0 rows affected, 1 warning (0.000 sec)
Query OK, 0 rows affected, 1 warning (0.000 sec)
Query OK, 0 rows affected, 1 warning (0.000 sec)
Query OK, 0 rows affected, 1 warning (0.000 sec)
Query OK, 0 rows affected, 1 warning (0.000 sec)
Query OK, 0 rows affected, 1 warning (0.001 sec)
Query OK, 0 rows affected, 1 warning (0.000 sec)
Query OK, 0 rows affected, 1 warning (0.017 sec)
Query OK, 0 rows affected, 1 warning (0.027 sec)
Query OK, 0 rows affected, 1 warning (0.032 sec)
Query OK, 0 rows affected, 1 warning (0.035 sec)
Query OK, 0 rows affected, 1 warning (0.037 sec)
Query OK, 0 rows affected, 1 warning (0.034 sec)
Query OK, 0 rows affected, 1 warning (0.038 sec)
Query OK, 0 rows affected, 1 warning (0.043 sec)
Query OK, 0 rows affected (0.014 sec)
Query OK, 0 rows affected (0.012 sec)
Query OK, 0 rows affected (0.013 sec)
Query OK, 0 rows affected (0.012 sec)
Query OK, 0 rows affected (0.011 sec)
Query OK, 15 rows affected (0.006 sec)
Records: 15 Duplicates: 0 Warnings: 0
Query OK, 4 rows affected (0.005 sec)
Records: 4 Duplicates: 0 Warnings: 0
Query OK, 7 rows affected (0.015 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.014 sec)
Records: 11 Duplicates: 0 Warnings: 0
Query OK, 14 rows affected (0.014 sec)
Records: 14 Duplicates: 0 Warnings: 0
Query OK, 11 rows affected (0.005 sec)
Records: 11 Duplicates: 0 Warnings: 0
Query OK, 22 rows affected (0.012 sec)
Records: 22 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
MariaDB [toyu]> 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]> 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.000 sec)
MariaDB [toyu]> -- a
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]>
MariaDB [toyu]> -- b
MariaDB [toyu]> SELECT DISTINCT *
-> FROM Student
-> WHERE ach >= 35
-> AND ach <= 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]>
MariaDB [toyu]> -- student in selected majors
MariaDB [toyu]> SELECT DISTINCT *
-> FROM Student
-> WHERE major IN ('CSCI', 'CINF', 'ITEC');
+--------+-----------+---------+-------+-------+------+---------+
| 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 |
+--------+-----------+---------+-------+-------+------+---------+
7 rows in set (0.001 sec)
MariaDB [toyu]> SELECT DISTINCT *
-> FROM Student AS s
-> WHERE EXISTS
-> (SELECT * -- a subquery
-> FROM Enroll AS e
-> WHERE e.stuId = s.stuId);
+--------+---------+---------+-------+-------+------+---------+
| 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 |
| 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 |
+--------+---------+---------+-------+-------+------+---------+
8 rows in set (0.002 sec)
MariaDB [toyu]> -- or
MariaDB [toyu]> SELECT DISTINCT s.*
-> FROM Student AS s INNER JOIN Enroll AS e USING (stuId);
+--------+---------+---------+-------+-------+------+---------+
| 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 |
| 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 |
+--------+---------+---------+-------+-------+------+---------+
8 rows in set (0.002 sec)
MariaDB [toyu]> -- students wiht a 'k' in their last name.
MariaDB [toyu]> SELECT DISTINCT s.*
-> FROM student AS s
-> WHERE s.lname LIKE '%k%';
+--------+-------+-------+-------+-------+------+---------+
| 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 |
| 100009 | Linda | King | ARTS | CSCI | 125 | 1018 |
+--------+-------+-------+-------+-------+------+---------+
4 rows in set (0.001 sec)
MariaDB [toyu]> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.000 sec)
MariaDB [toyu]> SELECT s.stuId, d.deptName AS department
-> FROM Student AS s INNER JOIN department ON (s.major = d.deptCode);
ERROR 1054 (42S22): Unknown column 'd.deptName' in 'field list'
MariaDB [toyu]>
MariaDB [toyu]> SELECT s.stuId, d.deptName AS department
-> FROM Student AS s INNER JOIN department AS d ON (s.major = d.deptCode);
+--------+------------------------------+
| stuId | department |
+--------+------------------------------+
| 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.001 sec)
MariaDB [toyu]> SELECT s.stuId, d.deptCode, d.deptName AS department
-> FROM Student AS s INNER JOIN department AS d ON (s.major = d.deptCode);
+--------+----------+------------------------------+
| stuId | deptCode | department |
+--------+----------+------------------------------+
| 100008 | ARTS | Arts |
| 100009 | ARTS | Arts |
| 100005 | CINF | Computer Information Systems |
| 100006 | CINF | Computer Information Systems |
| 100000 | CSCI | Computer Science |
| 100001 | CSCI | Computer Science |
| 100002 | CSCI | Computer Science |
| 100003 | ITEC | Information Technology |
| 100004 | ITEC | Information Technology |
+--------+----------+------------------------------+
9 rows in set (0.001 sec)
MariaDB [toyu]>
MariaDB [toyu]> SELECT s.stuId, d.deptCode, d.deptName AS department
-> FROM Student AS s LEFT JOIN department AS d ON (s.major = d.deptCode);
+--------+----------+------------------------------+
| stuId | deptCode | department |
+--------+----------+------------------------------+
| 100007 | NULL | NULL |
| 100111 | NULL | NULL |
| 100008 | ARTS | Arts |
| 100009 | ARTS | Arts |
| 100005 | CINF | Computer Information Systems |
| 100006 | CINF | Computer Information Systems |
| 100000 | CSCI | Computer Science |
| 100001 | CSCI | Computer Science |
| 100002 | CSCI | Computer Science |
| 100003 | ITEC | Information Technology |
| 100004 | ITEC | Information Technology |
+--------+----------+------------------------------+
11 rows in set (0.001 sec)
MariaDB [toyu]>
MariaDB [toyu]> SELECT s.stuId, d.deptCode, d.deptName AS department
-> FROM department AS d RIGHT JOIN Student AS s ON (s.major = d.deptCode);
+--------+----------+------------------------------+
| stuId | deptCode | department |
+--------+----------+------------------------------+
| 100007 | NULL | NULL |
| 100111 | NULL | NULL |
| 100008 | ARTS | Arts |
| 100009 | ARTS | Arts |
| 100005 | CINF | Computer Information Systems |
| 100006 | CINF | Computer Information Systems |
| 100000 | CSCI | Computer Science |
| 100001 | CSCI | Computer Science |
| 100002 | CSCI | Computer Science |
| 100003 | ITEC | Information Technology |
| 100004 | ITEC | Information Technology |
+--------+----------+------------------------------+
11 rows in set (0.001 sec)
MariaDB [toyu]> -- subqueries in the WHERE course
MariaDB [toyu]> -- students not enrolled in any class.
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.001 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]> -- 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.002 sec)
MariaDB [toyu]>
MariaDB [toyu]> -- subqueries as derived tables.
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.001 sec)
MariaDB [toyu]> SELECT DISTINCT MAX(ach) AS max FROM student;
+------+
| max |
+------+
| 125 |
+------+
1 row in set (0.000 sec)
MariaDB [toyu]> notee