Last login: Mon Mar 31 10:48:52 on ttyp1 Welcome to Darwin! web019142:~ perkins$ web019142:~ perkins$ ssh lab2-2.cs.mcgill.ca Password: Last login: Mon Mar 31 10:49:04 2008 from xenix.mcb.mcgill.ca The JVM for this session is: sun-jdk-1.6 The JVM for this session is: sun-jdk-1.6 [perkins][lab2-2][~] mysql -h mysql.cs.mcgill.ca -p 2008Winter364perkins Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 182351 Server version: 5.0.22 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT * FROM Students2; +--------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +--------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | Zoe | 70 | 92 | 81 | NULL | | George | 64 | 88 | 89 | NULL | | Smith | 99 | 99 | 99 | NULL | | Carla | 90 | 89 | 88 | NULL | | Noah | 97 | 87 | 76 | NULL | | Tara | 57 | 37 | 66 | NULL | | Tommy | 70 | NULL | 84 | NULL | +--------+-------+-------+-------+-------+ 8 rows in set (0.00 sec) mysql> /* Students with Quiz1 score >= 90 */ mysql> SELECT * FROM Students2 WHERE Quiz1 >= 90; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Smith | 99 | 99 | 99 | NULL | | Carla | 90 | 89 | 88 | NULL | | Noah | 97 | 87 | 76 | NULL | +-------+-------+-------+-------+-------+ 3 rows in set (0.00 sec) mysql> /* Just their names... */ mysql> SELECT Name FROM Students2 WHERE Quiz1 >= 90; +-------+ | Name | +-------+ | Smith | | Carla | | Noah | +-------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM Students2; +--------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +--------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | Zoe | 70 | 92 | 81 | NULL | | George | 64 | 88 | 89 | NULL | | Smith | 99 | 99 | 99 | NULL | | Carla | 90 | 89 | 88 | NULL | | Noah | 97 | 87 | 76 | NULL | | Tara | 57 | 37 | 66 | NULL | | Tommy | 70 | NULL | 84 | NULL | +--------+-------+-------+-------+-------+ 8 rows in set (0.00 sec) mysql> /* Did well on both quizzes */ mysql> SELECT * FROM Students2 WHERE (Quiz1 >= 90) AND (Quiz2 >= 90); +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Smith | 99 | 99 | 99 | NULL | +-------+-------+-------+-------+-------+ 1 row in set (0.00 sec) mysql> mysql> /* Improving students... */ mysql> SELECT * FROM Students2 WHERE (Quiz2 >= Quiz1) AND (Final >= Quiz2); +--------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +--------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | George | 64 | 88 | 89 | NULL | | Smith | 99 | 99 | 99 | NULL | +--------+-------+-------+-------+-------+ 3 rows in set (0.01 sec) mysql> SELECT * FROM Students2; +--------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +--------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | Zoe | 70 | 92 | 81 | NULL | | George | 64 | 88 | 89 | NULL | | Smith | 99 | 99 | 99 | NULL | | Carla | 90 | 89 | 88 | NULL | | Noah | 97 | 87 | 76 | NULL | | Tara | 57 | 37 | 66 | NULL | | Tommy | 70 | NULL | 84 | NULL | +--------+-------+-------+-------+-------+ 8 rows in set (0.00 sec) mysql> /* Finding a particular student */ mysql> SELECT * FROM Students2 WHERE Name='Carla'; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Carla | 90 | 89 | 88 | NULL | +-------+-------+-------+-------+-------+ 1 row in set (0.00 sec) mysql> /* Positive score on quiz2? *? /*> */ mysql> SELECT * FROM Students2 WHERE Quiz2>0; +--------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +--------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | Zoe | 70 | 92 | 81 | NULL | | George | 64 | 88 | 89 | NULL | | Smith | 99 | 99 | 99 | NULL | | Carla | 90 | 89 | 88 | NULL | | Noah | 97 | 87 | 76 | NULL | | Tara | 57 | 37 | 66 | NULL | +--------+-------+-------+-------+-------+ 7 rows in set (0.01 sec) mysql> SELECT * FROM Students2 WHERE NOT(Quiz2>0); Empty set (0.00 sec) mysql> mysql> /* Who didn't take quiz2? */ mysql> SELECT * FROM Students2 WHERE Quiz2 IS NULL; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Tommy | 70 | NULL | 84 | NULL | +-------+-------+-------+-------+-------+ 1 row in set (0.00 sec) mysql> /* Who didn't take quiz1? */ mysql> SELECT * FROM Students2 WHERE Quiz1 IS NULL; Empty set (0.00 sec) mysql> SELECT * FROM Students2 WHERE Quiz1 IS NOT NULL; +--------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +--------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | Zoe | 70 | 92 | 81 | NULL | | George | 64 | 88 | 89 | NULL | | Smith | 99 | 99 | 99 | NULL | | Carla | 90 | 89 | 88 | NULL | | Noah | 97 | 87 | 76 | NULL | | Tara | 57 | 37 | 66 | NULL | | Tommy | 70 | NULL | 84 | NULL | +--------+-------+-------+-------+-------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM Students2 WHERE Quiz2 IS NOT NULL; +--------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +--------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | Zoe | 70 | 92 | 81 | NULL | | George | 64 | 88 | 89 | NULL | | Smith | 99 | 99 | 99 | NULL | | Carla | 90 | 89 | 88 | NULL | | Noah | 97 | 87 | 76 | NULL | | Tara | 57 | 37 | 66 | NULL | +--------+-------+-------+-------+-------+ 7 rows in set (0.00 sec) mysql> CREATE TABLE GoodStudents SELECT * Students2 WHERE Final>=90; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Students2 WHERE Final>=90' at line 1 mysql> CREATE TABLE GoodStudents SELECT * FROM Students2 WHERE Final>=90; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM GoodStudents -> ; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | Smith | 99 | 99 | 99 | NULL | +-------+-------+-------+-------+-------+ 2 rows in set (0.00 sec) mysql> DELETE FROM GoodStudents; Query OK, 2 rows affected (0.00 sec) mysql> SELECT * FROM GoodStudents; Empty set (0.00 sec) mysql>