Last login: Fri Apr 4 10:32:36 on ttyp1 Welcome to Darwin! web019118:~ perkins$ web019118:~ perkins$ ssh lab2-2.cs.mcgill.ca Password: Last login: Fri Apr 4 10:32:36 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][~] ./gosql 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 1423 Server version: 5.0.22 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show tables -> ; +--------------------------------+ | Tables_in_2008Winter364perkins | +--------------------------------+ | C2 | | Contracts | | Cultures | | GeneExpts | | GeneInfo | | GeneInfoHW5 | | GeneSeqsHW5 | | GoodStudents | | Grades | | Movies | | Presidents | | Salaries | | Stu2 | | StudentInfo | | Students | | Students2 | | Temp | +--------------------------------+ 17 rows in set (0.00 sec) mysql> SELECT * FROM GeneExpts; +-------+--------+-----------+ | ExpID | GeneID | Expressed | +-------+--------+-----------+ | E101 | G103 | Yes | | E102 | G104 | No | | E103 | G102 | Yes | | E104 | G107 | Yes | | E105 | G106 | No | | E106 | G104 | Yes | | E107 | G105 | No | | E108 | G101 | YES | +-------+--------+-----------+ 8 rows in set (0.00 sec) mysql> SELECT * FROM GeneInfo; +--------+-----------+-------------+ | GeneID | Location | Function | +--------+-----------+-------------+ | G101 | Nucleus | Regulation | | G102 | Cytoplasm | Metabolism | | G103 | Nucleus | Degradation | | G104 | Lysosome | Degradation | | G105 | Cytoplasm | Signaling | | G106 | Cytoplasm | Metabolism | | G107 | Membrane | Signaling | +--------+-----------+-------------+ 7 rows in set (0.00 sec) mysql> SHOW TABLES; +--------------------------------+ | Tables_in_2008Winter364perkins | +--------------------------------+ | C2 | | Contracts | | Cultures | | GeneExpts | | GeneInfo | | GeneInfoHW5 | | GeneSeqsHW5 | | GoodStudents | | Grades | | Movies | | Presidents | | Salaries | | Stu2 | | StudentInfo | | Students | | Students2 | | Temp | +--------------------------------+ 17 rows in set (0.00 sec) mysql> SELECT * FROM Contracts; +----------+------+--------+ | Company | Year | Amount | +----------+------+--------+ | ABC Inc. | 2006 | 15000 | | XYZ Co. | 2006 | 11000 | | ABC Inc. | 2007 | 8000 | | DEF Ltd. | 2007 | 20000 | | ABC Inc. | 2007 | 1000 | | XYZ Co. | 2008 | 15000 | | DEF Ltd. | 2008 | 10000 | +----------+------+--------+ 7 rows in set (0.00 sec) mysql> SELECT SUM(Amount) FROM Contracts; +-------------+ | SUM(Amount) | +-------------+ | 80000 | +-------------+ 1 row in set (0.00 sec) mysql> SELECT Company, SUM(Amount) FROM Contracts GROUP BY Company; +----------+-------------+ | Company | SUM(Amount) | +----------+-------------+ | ABC Inc. | 24000 | | DEF Ltd. | 30000 | | XYZ Co. | 26000 | +----------+-------------+ 3 rows in set (0.00 sec) mysql> /* reporting more group functions */ mysql> SELECT Company, SUM(Amount), MAX(Year) FROM Contracts GROUP BY Company; +----------+-------------+-----------+ | Company | SUM(Amount) | MAX(Year) | +----------+-------------+-----------+ | ABC Inc. | 24000 | 2007 | | DEF Ltd. | 30000 | 2008 | | XYZ Co. | 26000 | 2008 | +----------+-------------+-----------+ 3 rows in set (0.00 sec) mysql> /* Total amounts in each year. */SELECT Year, SUM(Amount) FROM Contracts GROUP BY Year; +------+-------------+ | Year | SUM(Amount) | +------+-------------+ | 2006 | 26000 | | 2007 | 29000 | | 2008 | 25000 | +------+-------------+ 3 rows in set (0.00 sec) mysql> SELECT SUM(Amount) FROM Contracts GROUP BY Year;+-------------+ | SUM(Amount) | +-------------+ | 26000 | | 29000 | | 25000 | +-------------+ 3 rows in set (0.00 sec) mysql> SELECT Amount>=1000, SUM(Amount) FROM Contracts GROUP BY Amount>=1000; +--------------+-------------+ | Amount>=1000 | SUM(Amount) | +--------------+-------------+ | 1 | 80000 | +--------------+-------------+ 1 row in set (0.00 sec) mysql> SELECT Amount>=10000, SUM(Amount) FROM Contracts GROUP BY Amount>=10000; +---------------+-------------+ | Amount>=10000 | SUM(Amount) | +---------------+-------------+ | 0 | 9000 | | 1 | 71000 | +---------------+-------------+ 2 rows in set (0.00 sec) mysql> create table List1 (Gene varchar(100)); Query OK, 0 rows affected (0.01 sec) mysql> create table List2 (Gene varchar(100)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO List1 VALUES ('G10'),('G14'),('G12'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO List2 VALUES ('G9'),('G12'),('G16'),('G10'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM List1; +------+ | Gene | +------+ | G10 | | G14 | | G12 | +------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM List2; +------+ | Gene | +------+ | G9 | | G12 | | G16 | | G10 | +------+ 4 rows in set (0.00 sec) mysql> CREATE TABLE Union (Gene varchar(100)); 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 'Union (Gene varchar(100))' at line 1 mysql> CREATE TABLE U (Gene varchar(100)); Query OK, 0 rows affected (0.00 sec) mysql> /* Put List1 and List2 into U */ mysql> INSERT INTO U SELECT * From List1; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO U SELECT * From List2; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM U; +------+ | Gene | +------+ | G10 | | G14 | | G12 | | G9 | | G12 | | G16 | | G10 | +------+ 7 rows in set (0.00 sec) mysql> SELECT * FROM U GROUP BY Gene; +------+ | Gene | +------+ | G10 | | G12 | | G14 | | G16 | | G9 | +------+ 5 rows in set (0.00 sec) mysql> CREATE TABLE Int (Gene varchar(100), Count int); 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 'Int (Gene varchar(100), Count int)' at line 1 mysql> CREATE TABLE I (Gene varchar(100), Count int); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO I SELECT * FROM U; ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> help insert into Nothing found Please try to run 'help contents' for a list of all accessible topics mysql> help INSERT Name: 'INSERT' Description: Syntax: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] Or: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] Or: INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] INSERT inserts new rows into an existing table. The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. INSERT ... SELECT is discussed further in [insert-select]. mysql> INSERT INTO I (Gene) SELECT * FROM U; Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM I; +------+-------+ | Gene | Count | +------+-------+ | G10 | NULL | | G14 | NULL | | G12 | NULL | | G9 | NULL | | G12 | NULL | | G16 | NULL | | G10 | NULL | +------+-------+ 7 rows in set (0.00 sec) mysql> UPDATE I SET Count=1; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> SELECT * FROM I; +------+-------+ | Gene | Count | +------+-------+ | G10 | 1 | | G14 | 1 | | G12 | 1 | | G9 | 1 | | G12 | 1 | | G16 | 1 | | G10 | 1 | +------+-------+ 7 rows in set (0.00 sec) mysql> SELECT Gene, COUNT(Count) FROM I GROUP BY Gene; +------+--------------+ | Gene | COUNT(Count) | +------+--------------+ | G10 | 2 | | G12 | 2 | | G14 | 1 | | G16 | 1 | | G9 | 1 | +------+--------------+ 5 rows in set (0.00 sec) mysql> CREATE TABLE Temp SELECT Gene, COUNT(Count) FROM I GROUP BY Gene; ERROR 1050 (42S01): Table 'Temp' already exists mysql> drop Temp; 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 'Temp' at line 1 mysql> drop Temp; 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 'Temp' at line 1 mysql> CREATE TABLE Temp2 SELECT Gene, COUNT(Count) FROM I GROUP BY Gene; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * From Temp2; +------+--------------+ | Gene | COUNT(Count) | +------+--------------+ | G10 | 2 | | G12 | 2 | | G14 | 1 | | G16 | 1 | | G9 | 1 | +------+--------------+ 5 rows in set (0.00 sec) mysql> SELECT Gene FROM Temp2 WHERE COUNT(Count)=2; ERROR 1111 (HY000): Invalid use of group function mysql> SELECT Gene FROM Temp2 WHERE 'COUNT(Count)'=2; Empty set, 1 warning (0.00 sec) mysql> CREATE TABLE Temp3 (Gene varchar(100), Count int);Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Temp3 SELECT Gene, COUNT(Count) FROM I GROUP BY Gene; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * Temp3; 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 'Temp3' at line 1 mysql> SELECT * FROM Temp3; +------+-------+ | Gene | Count | +------+-------+ | G10 | 2 | | G12 | 2 | | G14 | 1 | | G16 | 1 | | G9 | 1 | +------+-------+ 5 rows in set (0.00 sec) mysql> /* Intersection */ mysql> SELCT Gene FROM Temp3 WHERE Count=2; 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 'SELCT Gene FROM Temp3 WHERE Count=2' at line 1 mysql> SELECT Gene FROM Temp3 WHERE Count=2; +------+ | Gene | +------+ | G10 | | G12 | +------+ 2 rows in set (0.00 sec) mysql> /* Symmetric difference */ mysql> SELECT Gene FROM Temp3 WHERE Count=1; +------+ | Gene | +------+ | G14 | | G16 | | G9 | +------+ 3 rows in set (0.00 sec) mysql> SHOW TABLES; +--------------------------------+ | Tables_in_2008Winter364perkins | +--------------------------------+ | C2 | | Contracts | | Cultures | | GeneExpts | | GeneInfo | | GeneInfoHW5 | | GeneSeqsHW5 | | GoodStudents | | Grades | | I | | List1 | | List2 | | Movies | | Presidents | | Salaries | | Stu2 | | StudentInfo | | Students | | Students2 | | Temp | | Temp2 | | Temp3 | | U | +--------------------------------+ 23 rows in set (0.00 sec) mysql> SELECT * FROM StudentInfo; +------+---------+---------+ | ID | Name | Country | +------+---------+---------+ | 1001 | Bob | Canada | | 1002 | Fran | USA | | 1003 | Giselle | Canada | | 1004 | Bob | UK | | 1005 | Zoe | Canada | +------+---------+---------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM Grades; +------+---------+-------+ | ID | Name | Grade | +------+---------+-------+ | 1002 | Fran | A | | 1003 | Giselle | A | | 1005 | Zoe | B | +------+---------+-------+ 3 rows in set (0.01 sec) mysql> SELECT StudentInfo.ID, StudentInfo.Name -> FROM StudentInfo, Grades -> WHERE StudentInfo.Country = 'Canada' -> AND Grades.Grade = 'A' -> AND StudentInfo.ID = Grades.ID; +------+---------+ | ID | Name | +------+---------+ | 1003 | Giselle | +------+---------+ 1 row in set (0.00 sec) mysql>