mysql> select * from teaches;
+-------+-----------+--------+----------+------+
| id | course_id | sec_id | semester | year |
+-------+-----------+--------+----------+------+
| 10101 | CS-101 | 1 | Fall | 2009 |
| 10101 | CS-315 | 1 | Spring | 2010 |
| 10101 | CS-347 | 1 | Fall | 2009 |
| 12121 | FIN-201 | 1 | Spring | 2010 |
| 15151 | MU-199 | 1 | Spring | 2010 |
| 22222 | PHY-101 | 1 | Fall | 2009 |
| 32343 | HIS-351 | 1 | Spring | 2010 |
| 45565 | CS-101 | 1 | Spring | 2010 |
| 45565 | CS-319 | 1 | Spring | 2010 |
| 76766 | BIO-101 | 1 | Summer | 2009 |
| 76766 | BIO-301 | 1 | Summer | 2010 |
| 83821 | CS-190 | 1 | Spring | 2009 |
| 83821 | CS-190 | 2 | Spring | 2009 |
| 83821 | CS-319 | 2 | Spring | 2010 |
| 98345 | EE-181 | 1 | Spring | 2009 |
+-------+-----------+--------+----------+------+
15 rows in set (0.00 sec)
mysql> select id from teaches where semester='Fall' and year=2010;
Empty set (0.00 sec)
CHANGED YEAR TO 2009 TO MAKE IT MORE INTERESTING
mysql> select id from teaches where semester='Fall' and year=2009;
+-------+
| id |
+-------+
| 10101 |
| 10101 |
| 22222 |
+-------+
3 rows in set (0.00 sec)
mysql> select name from instructor natural join teaches where semester='Fall' and year = 2009;
+------------+
| name |
+------------+
| Srinivasan |
| Srinivasan |
| Einstein |
+------------+
3 rows in set (0.01 sec)
mysql> select distinct name from instructor natural join teaches where semester='Fall' and year = 2009;
+------------+
| name |
+------------+
| Srinivasan |
| Einstein |
+------------+
2 rows in set (0.00 sec)
mysql> select distinct name from instructor natural join teaches where semester='Fall' and year = 2009 and salary>62000;
+------------+
| name |
+------------+
| Srinivasan |
| Einstein |
+------------+
2 rows in set (0.00 sec)
mysql> select * from instructor;
+-------+------------+------------+----------+
| id | name | dept_name | salary |
+-------+------------+------------+----------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 |
| 12121 | Wu | Finance | 90000.00 |
| 15151 | Mozart | Music | 40000.00 |
| 22222 | Einstein | Physics | 95000.00 |
| 32343 | El Said | History | 60000.00 |
| 33456 | Gold | Physics | 87000.00 |
| 45565 | Katz | Comp. Sci. | 75000.00 |
| 58583 | Califieri | History | 62000.00 |
| 76543 | Singh | Finance | 80000.00 |
| 76766 | Crick | Biology | 72000.00 |
| 83821 | Brandt | Comp. Sci. | 92000.00 |
| 98345 | Kim | Elec. Eng. | 80000.00 |
+-------+------------+------------+----------+
12 rows in set (0.00 sec)
mysql> select a.course_id, b.course_id from prereq a join prereq b on a.course_id!=b.course_id and a.prereq_id = b.prereq_id;
+-----------+-----------+
| course_id | course_id |
+-----------+-----------+
| BIO-399 | BIO-301 |
| BIO-301 | BIO-399 |
| CS-315 | CS-190 |
| CS-319 | CS-190 |
| CS-347 | CS-190 |
| CS-190 | CS-315 |
| CS-319 | CS-315 |
| CS-347 | CS-315 |
| CS-190 | CS-319 |
| CS-315 | CS-319 |
| CS-347 | CS-319 |
| CS-190 | CS-347 |
| CS-315 | CS-347 |
| CS-319 | CS-347 |
+-----------+-----------+
14 rows in set (0.01 sec)
mysql> select count(course_id) from course where dept_name =
-> 'Biology';
+------------------+
| count(course_id) |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)
mysql> select name, title from instructor natural join teaches natural join course;
+------------+----------------------------+
| name | title |
+------------+----------------------------+
| Srinivasan | Intro. to Computer Science |
| Srinivasan | Robotics |
| Srinivasan | Database System Concepts |
| Wu | Investment Banking |
| Mozart | Music Video Production |
| Einstein | Physical Principles |
| El Said | World History |
| Katz | Intro. to Computer Science |
| Katz | Image Processing |
| Crick | Intro. to Biology |
| Crick | Genetics |
| Brandt | Game Design |
| Brandt | Game Design |
| Brandt | Image Processing |
| Kim | Intro. to Digital Systems |
+------------+----------------------------+
15 rows in set (0.00 sec)