mysql> /* Tables for the Student problem */
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.00 sec)

mysql> /* Finding all Canadian A-students */
mysql> SELECT StudentInfo.Name FROM StudentInfo, Grades WHERE StudentInfo.Country='Canada' AND Grades.Grade='A' AND StudentInfo.ID=Grades.ID;
+---------+
| Name    |
+---------+
| Giselle | 
+---------+
1 row in set (0.00 sec)

mysql> /* Showing all possible combinations */
mysql> SELECT * FROM StudentInfo, Grades;
+------+---------+---------+------+---------+-------+
| ID   | Name    | Country | ID   | Name    | Grade |
+------+---------+---------+------+---------+-------+
| 1001 | Bob     | Canada  | 1002 | Fran    | A     | 
| 1001 | Bob     | Canada  | 1003 | Giselle | A     | 
| 1001 | Bob     | Canada  | 1005 | Zoe     | B     | 
| 1002 | Fran    | USA     | 1002 | Fran    | A     | 
| 1002 | Fran    | USA     | 1003 | Giselle | A     | 
| 1002 | Fran    | USA     | 1005 | Zoe     | B     | 
| 1003 | Giselle | Canada  | 1002 | Fran    | A     | 
| 1003 | Giselle | Canada  | 1003 | Giselle | A     | 
| 1003 | Giselle | Canada  | 1005 | Zoe     | B     | 
| 1004 | Bob     | UK      | 1002 | Fran    | A     | 
| 1004 | Bob     | UK      | 1003 | Giselle | A     | 
| 1004 | Bob     | UK      | 1005 | Zoe     | B     | 
| 1005 | Zoe     | Canada  | 1002 | Fran    | A     | 
| 1005 | Zoe     | Canada  | 1003 | Giselle | A     | 
| 1005 | Zoe     | Canada  | 1005 | Zoe     | B     | 
+------+---------+---------+------+---------+-------+
15 rows in set (0.00 sec)

mysql> /* Restricting by matching IDs */
mysql> SELECT * FROM StudentInfo, Grades WHERE StudentInfo.ID=Grades.ID;      
+------+---------+---------+------+---------+-------+
| ID   | Name    | Country | ID   | Name    | Grade |
+------+---------+---------+------+---------+-------+
| 1002 | Fran    | USA     | 1002 | Fran    | A     | 
| 1003 | Giselle | Canada  | 1003 | Giselle | A     | 
| 1005 | Zoe     | Canada  | 1005 | Zoe     | B     | 
+------+---------+---------+------+---------+-------+
3 rows in set (0.00 sec)

mysql> /* Adding Canada restriction */
mysql> SELECT * FROM StudentInfo, Grades WHERE StudentInfo.ID=Grades.ID AND StudentInfo.Country='Canada';
+------+---------+---------+------+---------+-------+
| ID   | Name    | Country | ID   | Name    | Grade |
+------+---------+---------+------+---------+-------+
| 1003 | Giselle | Canada  | 1003 | Giselle | A     | 
| 1005 | Zoe     | Canada  | 1005 | Zoe     | B     | 
+------+---------+---------+------+---------+-------+
2 rows in set (0.00 sec)

mysql> /* Adding A-student restriction */
mysql> SELECT * FROM StudentInfo, Grades WHERE StudentInfo.ID=Grades.ID AND StudentInfo.Country='Canada' AND Grades.Grade='A';
+------+---------+---------+------+---------+-------+
| ID   | Name    | Country | ID   | Name    | Grade |
+------+---------+---------+------+---------+-------+
| 1003 | Giselle | Canada  | 1003 | Giselle | A     | 
+------+---------+---------+------+---------+-------+
1 row in set (0.00 sec)

mysql> /* The gene examples */
mysql> /* The tables: */
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.02 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 GeneReg;
+-----------+-----------+
| Regulator | Regulatee |
+-----------+-----------+
| G101      | G102      | 
| G101      | G105      | 
| G101      | G106      | 
+-----------+-----------+
3 rows in set (0.02 sec)

mysql> /* Return genes that are expressed? */
mysql> SELECT GeneID FROM GeneExpts WHERE Expressed='Yes';
+--------+
| GeneID |
+--------+
| G103   | 
| G102   | 
| G107   | 
| G104   | 
| G101   | 
+--------+
5 rows in set (0.00 sec)

mysql> /* Return genes that are involved in metabolism? */
mysql> SELECT GeneID FROM GeneInfo WHERE Function='Metabolism';
+--------+
| GeneID |
+--------+
| G102   | 
| G106   | 
+--------+
2 rows in set (0.00 sec)

mysql> /* Return genes that are expressed and involvedin metabolism. */
mysql> SELECT GeneInfo.GeneID FROM GeneInfo, GeneExpts WHERE GeneInfo.GeneID=GeneExpts.GeneID AND GeneInfo.Function='Metabolism' AND GeneExpts.Expressed='Yes';
+--------+
| GeneID |
+--------+
| G102   | 
+--------+
1 row in set (0.00 sec)

mysql> /* Return locations of all expressed genes. */
mysql> SELECT DISTINCT GeneInfo.Location FROM GeneInfo,GeneExpts WHERE GeneExpts.Expressed='Yes' AND GeneExpts.GeneID=GeneInfo.GeneID;
+-----------+
| Location  |
+-----------+
| Nucleus   | 
| Cytoplasm | 
| Membrane  | 
| Lysosome  | 
+-----------+
4 rows in set (0.00 sec)

mysql> /* Without DISTINCT */
mysql> SELECT GeneInfo.Location FROM GeneInfo,GeneExpts WHERE GeneExpts.Expressed='Yes' AND GeneExpts.GeneID=GeneInfo.GeneID;
+-----------+
| Location  |
+-----------+
| Nucleus   | 
| Cytoplasm | 
| Membrane  | 
| Lysosome  | 
| Nucleus   | 
+-----------+
5 rows in set (0.00 sec)

mysql> /* Return Experiment IDs where gene expressed and gene acts in nucleus. */
mysql> SELECT * FROM GeneInfo,GeneExpts WHERE GeneInfo.GeneID=GeneExpts.GeneID;
+--------+-----------+-------------+-------+--------+-----------+
| GeneID | Location  | Function    | ExpID | GeneID | Expressed |
+--------+-----------+-------------+-------+--------+-----------+
| G103   | Nucleus   | Degradation | E101  | G103   | Yes       | 
| G104   | Lysosome  | Degradation | E102  | G104   | No        | 
| G102   | Cytoplasm | Metabolism  | E103  | G102   | Yes       | 
| G107   | Membrane  | Signaling   | E104  | G107   | Yes       | 
| G106   | Cytoplasm | Metabolism  | E105  | G106   | No        | 
| G104   | Lysosome  | Degradation | E106  | G104   | Yes       | 
| G105   | Cytoplasm | Signaling   | E107  | G105   | No        | 
| G101   | Nucleus   | Regulation  | E108  | G101   | Yes       | 
+--------+-----------+-------------+-------+--------+-----------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM GeneInfo,GeneExpts WHERE GeneInfo.GeneID=GeneExpts.GeneID AND GeneExpts.Expressed='Yes';
+--------+-----------+-------------+-------+--------+-----------+
| GeneID | Location  | Function    | ExpID | GeneID | Expressed |
+--------+-----------+-------------+-------+--------+-----------+
| G103   | Nucleus   | Degradation | E101  | G103   | Yes       | 
| G102   | Cytoplasm | Metabolism  | E103  | G102   | Yes       | 
| G107   | Membrane  | Signaling   | E104  | G107   | Yes       | 
| G104   | Lysosome  | Degradation | E106  | G104   | Yes       | 
| G101   | Nucleus   | Regulation  | E108  | G101   | Yes       | 
+--------+-----------+-------------+-------+--------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM GeneInfo,GeneExpts WHERE GeneInfo.GeneID=GeneExpts.GeneID AND GeneExpts.Expressed='Yes' AND GeneInfo.Location='Nucleus';
+--------+----------+-------------+-------+--------+-----------+
| GeneID | Location | Function    | ExpID | GeneID | Expressed |
+--------+----------+-------------+-------+--------+-----------+
| G103   | Nucleus  | Degradation | E101  | G103   | Yes       | 
| G101   | Nucleus  | Regulation  | E108  | G101   | Yes       | 
+--------+----------+-------------+-------+--------+-----------+
2 rows in set (0.00 sec)

mysql>