Last login: Wed Apr 2 11:53:52 on ttyp1 Welcome to Darwin! web019159:~ perkins$ web019159:~ perkins$ ssh lab2-2.cs.mcgillc.a ssh: lab2-2.cs.mcgillc.a: No address associated with nodename web019159:~ perkins$ ssh lab2-2.cs.mcgill.ca Password: Last login: Wed Apr 2 11:55:20 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 198206 Server version: 5.0.22 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW TABLES; +--------------------------------+ | Tables_in_2008Winter364perkins | +--------------------------------+ | Cultures | | GoodStudents | | Movies | | Presidents | | Stu2 | | Students | | Students2 | +--------------------------------+ 7 rows in set (0.01 sec) mysql> DROP Stu2; 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 'Stu2' at line 1 mysql> DROP TABLE Stu2; Query OK, 0 rows affected (0.00 sec) mysql> /* Copy of Students2 */ mysql> CREATE TABLE Stu2 SELECT * FROM Students2; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM Stu2; +--------+-------+-------+-------+-------+ | 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> /* Deleting George */ mysql> DELETE FROM Stu2 WHERE Name='George'; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM Stu2; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | Zoe | 70 | 92 | 81 | 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 | +-------+-------+-------+-------+-------+ 7 rows in set (0.00 sec) mysql> /* Delete low performers */ mysql> DELETE FROM Stu2 WHERE Final<70; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM Stu2; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | Zoe | 70 | 92 | 81 | NULL | | Smith | 99 | 99 | 99 | NULL | | Carla | 90 | 89 | 88 | NULL | | Noah | 97 | 87 | 76 | NULL | | Tommy | 70 | NULL | 84 | NULL | +-------+-------+-------+-------+-------+ 6 rows in set (0.00 sec) mysql> /* Fill in Tommy's Quiz2 grade */ mysql> UPDATE Stu2 SET Quiz2=84 WHERE Name='Tommy'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM Stu2; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | NULL | | Zoe | 70 | 92 | 81 | NULL | | Smith | 99 | 99 | 99 | NULL | | Carla | 90 | 89 | 88 | NULL | | Noah | 97 | 87 | 76 | NULL | | Tommy | 70 | 84 | 84 | NULL | +-------+-------+-------+-------+-------+ 6 rows in set (0.00 sec) mysql> /* Calculate final grades */ mysql> UPDATE Stu2 SET Grade=0.3*Quiz1+0.3*Quiz2+0.4*Final; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> SELECT * FROM Stu2; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Bob | 80 | 82 | 90 | 84.6 | | Zoe | 70 | 92 | 81 | 81 | | Smith | 99 | 99 | 99 | 99 | | Carla | 90 | 89 | 88 | 88.9 | | Noah | 97 | 87 | 76 | 85.6 | | Tommy | 70 | 84 | 84 | 79.8 | +-------+-------+-------+-------+-------+ 6 rows in set (0.00 sec) mysql> /* Add 3 points to Quiz1 */ mysql> UPDATE Stu2 SET Quiz1 = Quiz1 + 3; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> /* Add 3 points to Quiz1 */ mysql> SELECT * FROM Stu2; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Bob | 83 | 82 | 90 | 84.6 | | Zoe | 73 | 92 | 81 | 81 | | Smith | 102 | 99 | 99 | 99 | | Carla | 93 | 89 | 88 | 88.9 | | Noah | 100 | 87 | 76 | 85.6 | | Tommy | 73 | 84 | 84 | 79.8 | +-------+-------+-------+-------+-------+ 6 rows in set (0.00 sec) mysql> UPDATE Stu2 SET Grade=0.3*Quiz1+0.3*Quiz2+0.4*Final; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> SELECT * FROM Stu2; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Bob | 83 | 82 | 90 | 85.5 | | Zoe | 73 | 92 | 81 | 81.9 | | Smith | 102 | 99 | 99 | 99.9 | | Carla | 93 | 89 | 88 | 89.8 | | Noah | 100 | 87 | 76 | 86.5 | | Tommy | 73 | 84 | 84 | 80.7 | +-------+-------+-------+-------+-------+ 6 rows in set (0.00 sec) mysql> SELECT AVG(Grade) FROM Stu2; +-----------------+ | AVG(Grade) | +-----------------+ | 87.383333333333 | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT MAX(Grade) FROM Stu2; +------------+ | MAX(Grade) | +------------+ | 99.9 | +------------+ 1 row in set (0.00 sec) mysql> SELECT MIN(Grade), AVG(Grade), MAX(Grade) FROM Stu2; +------------+-----------------+------------+ | MIN(Grade) | AVG(Grade) | MAX(Grade) | +------------+-----------------+------------+ | 80.7 | 87.383333333333 | 99.9 | +------------+-----------------+------------+ 1 row in set (0.00 sec) mysql> SELECT AVG(Quiz1), AVG(Quiz2), MAX(Final) FROM Stu2; +------------+------------+------------+ | AVG(Quiz1) | AVG(Quiz2) | MAX(Final) | +------------+------------+------------+ | 87.3333 | 88.8333 | 99 | +------------+------------+------------+ 1 row in set (0.00 sec) mysql> SELECT Grade, AVG(Grade) FROM Stu2; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause mysql> SELECT Grade FROM Stu2; +-------+ | Grade | +-------+ | 85.5 | | 81.9 | | 99.9 | | 89.8 | | 86.5 | | 80.7 | +-------+ 6 rows in set (0.00 sec) mysql> /* Students with above average Grade */ mysql> SELECT AVG(Grade) FROM Stu2; +-----------------+ | AVG(Grade) | +-----------------+ | 87.383333333333 | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM Stu2 WHERE Grade>=87.383333333; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Smith | 102 | 99 | 99 | 99.9 | | Carla | 93 | 89 | 88 | 89.8 | +-------+-------+-------+-------+-------+ 2 rows in set (0.00 sec) mysql> SELECT COUNT(Grade) FROM Stu2; +--------------+ | COUNT(Grade) | +--------------+ | 6 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM Stu2; +-------+-------+-------+-------+-------+ | Name | Quiz1 | Quiz2 | Final | Grade | +-------+-------+-------+-------+-------+ | Bob | 83 | 82 | 90 | 85.5 | | Zoe | 73 | 92 | 81 | 81.9 | | Smith | 102 | 99 | 99 | 99.9 | | Carla | 93 | 89 | 88 | 89.8 | | Noah | 100 | 87 | 76 | 86.5 | | Tommy | 73 | 84 | 84 | 80.7 | +-------+-------+-------+-------+-------+ 6 rows in set (0.00 sec) mysql> SELECT COUNT(Grade) FROM Stu2 WHERE Grade>=87.383333333; +--------------+ | COUNT(Grade) | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) mysql> SHOW TABLES -> ; +--------------------------------+ | Tables_in_2008Winter364perkins | +--------------------------------+ | Cultures | | GoodStudents | | Movies | | Presidents | | Stu2 | | Students | | Students2 | +--------------------------------+ 7 rows in set (0.00 sec) mysql> SELECT * FROM Presidents; +-----------+----------+-----------+---------+ | Firstname | Lastname | StartYear | EndYear | +-----------+----------+-----------+---------+ | Bill | Clinton | 1992 | 2000 | | George | Bush | 1988 | 1992 | | Ronald | Reagan | 1980 | 1988 | | George | Bush Jr. | 2000 | 2008 | | Who | Knows? | 2008 | NULL | +-----------+----------+-----------+---------+ 5 rows in set (0.02 sec) mysql> /* Is any president a Jr.? */ mysql> SELECT * FROM Presidents WHERE INSTR(Lastname,'Jr.');+-----------+----------+-----------+---------+ | Firstname | Lastname | StartYear | EndYear | +-----------+----------+-----------+---------+ | George | Bush Jr. | 2000 | 2008 | +-----------+----------+-----------+---------+ 1 row in set (0.00 sec) mysql> SELECT * FROM Presidents WHERE LENGTH(Firstname)>4; +-----------+----------+-----------+---------+ | Firstname | Lastname | StartYear | EndYear | +-----------+----------+-----------+---------+ | George | Bush | 1988 | 1992 | | Ronald | Reagan | 1980 | 1988 | | George | Bush Jr. | 2000 | 2008 | +-----------+----------+-----------+---------+ 3 rows in set (0.00 sec) mysql> /* Length of all president's names */ mysql> SELECT LENGTH(Name) FROM Presidents; ERROR 1054 (42S22): Unknown column 'Name' in 'field list' mysql> SELECT LENGTH(Firstname) FROM Presidents; +-------------------+ | LENGTH(Firstname) | +-------------------+ | 4 | | 6 | | 6 | | 6 | | 3 | +-------------------+ 5 rows in set (0.00 sec) mysql> SELECT CONCAT(Firstname,Lastname) FROM Presidents; +----------------------------+ | CONCAT(Firstname,Lastname) | +----------------------------+ | BillClinton | | GeorgeBush | | RonaldReagan | | GeorgeBush Jr. | | WhoKnows? | +----------------------------+ 5 rows in set (0.00 sec) mysql> SELECT CONCAT(Firstname,' ',Lastname) FROM Presidents; +--------------------------------+ | CONCAT(Firstname,' ',Lastname) | +--------------------------------+ | Bill Clinton | | George Bush | | Ronald Reagan | | George Bush Jr. | | Who Knows? | +--------------------------------+ 5 rows in set (0.00 sec) mysql> SHOW TABLES; +--------------------------------+ | Tables_in_2008Winter364perkins | +--------------------------------+ | Cultures | | GoodStudents | | Movies | | Presidents | | Stu2 | | Students | | Students2 | +--------------------------------+ 7 rows in set (0.00 sec) mysql> SELECT * FROM Cultures; +------+------+------+------+ | ID | Init | T1 | T2 | +------+------+------+------+ | 1 | 10 | 12.2 | 35 | | 2 | 13.4 | 14.2 | 15.2 | | 3 | 8 | 13 | 18.2 | | 4 | 11.1 | 14.6 | 30.2 | | 5 | 15.1 | 16.6 | 12.2 | | 6 | 13 | 19 | 81 | | 7 | 11 | 10 | 0.1 | +------+------+------+------+ 7 rows in set (0.00 sec) mysql> SELECT * FROM Cultures WHERE T2>T1; +------+------+------+------+ | ID | Init | T1 | T2 | +------+------+------+------+ | 1 | 10 | 12.2 | 35 | | 2 | 13.4 | 14.2 | 15.2 | | 3 | 8 | 13 | 18.2 | | 4 | 11.1 | 14.6 | 30.2 | | 6 | 13 | 19 | 81 | +------+------+------+------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM Cultures WHERE T2>T1+5; +------+------+------+------+ | ID | Init | T1 | T2 | +------+------+------+------+ | 1 | 10 | 12.2 | 35 | | 3 | 8 | 13 | 18.2 | | 4 | 11.1 | 14.6 | 30.2 | | 6 | 13 | 19 | 81 | +------+------+------+------+ 4 rows in set (0.00 sec) mysql> /* Did it grow both times? */ mysql> SELECT * FROM Cultures WHERE (T1>Init) AND (T2>T1); +------+------+------+------+ | ID | Init | T1 | T2 | +------+------+------+------+ | 1 | 10 | 12.2 | 35 | | 2 | 13.4 | 14.2 | 15.2 | | 3 | 8 | 13 | 18.2 | | 4 | 11.1 | 14.6 | 30.2 | | 6 | 13 | 19 | 81 | +------+------+------+------+ 5 rows in set (0.00 sec) mysql> help ALTER TABLE Name: 'ALTER TABLE' Description: Syntax: ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (column_definition,...) | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) | ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...) | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | table_option ... ALTER TABLE enables you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and type of the table. mysql> ALTER TABLE Cultures ADD COLUMN PredT1 real; Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM Cultures; +------+------+------+------+--------+ | ID | Init | T1 | T2 | PredT1 | +------+------+------+------+--------+ | 1 | 10 | 12.2 | 35 | NULL | | 2 | 13.4 | 14.2 | 15.2 | NULL | | 3 | 8 | 13 | 18.2 | NULL | | 4 | 11.1 | 14.6 | 30.2 | NULL | | 5 | 15.1 | 16.6 | 12.2 | NULL | | 6 | 13 | 19 | 81 | NULL | | 7 | 11 | 10 | 0.1 | NULL | +------+------+------+------+--------+ 7 rows in set (0.00 sec) mysql> DESCRIBE Cultures; +--------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+-------+ | ID | int(11) | YES | | NULL | | | Init | double | YES | | NULL | | | T1 | double | YES | | NULL | | | T2 | double | YES | | NULL | | | PredT1 | double | YES | | NULL | | +--------+---------+------+-----+---------+-------+ 5 rows in set (0.01 sec) mysql> UPDATE Cultures SET PredT1 = Init*EXP(0.1*1); Query OK, 7 rows affected (0.04 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> SELECT * FROM Cultures; +------+------+------+------+------------------+ | ID | Init | T1 | T2 | PredT1 | +------+------+------+------+------------------+ | 1 | 10 | 12.2 | 35 | 11.0517091807565 | | 2 | 13.4 | 14.2 | 15.2 | 14.8092903022137 | | 3 | 8 | 13 | 18.2 | 8.84136734460518 | | 4 | 11.1 | 14.6 | 30.2 | 12.2673971906397 | | 5 | 15.1 | 16.6 | 12.2 | 16.6880808629423 | | 6 | 13 | 19 | 81 | 14.3672219349834 | | 7 | 11 | 10 | 0.1 | 12.1568800988321 | +------+------+------+------+------------------+ 7 rows in set (0.00 sec) mysql>