gistfile1.sql

-- This was for a DB class. Don't judge me.

mysql> SELECT * FROM Project;
+-----+-----------+------+
| pid | startyear | cid  |
+-----+-----------+------+
|   1 |      2007 |    5 | 
|   2 |      2004 |   29 | 
|   3 |      2004 |    1 | 
|   4 |      2003 |   14 | 
|   5 |      2009 |    3 | 
|   6 |      2008 |   16 | 
|   7 |      2007 |    3 | 
|   8 |      2009 |   15 | 
|   9 |      2006 |   20 | 
|  10 |      2005 |   24 | 
+-----+-----------+------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM Employee;
+-----+---------------------+---------------+------+
| eid | ename               | location      | sid  |
+-----+---------------------+---------------+------+
|   1 | Teagan Predovic     | Toronto       |    0 | 
|   2 | Santos Hilpert      | Montreal      |    0 | 
|   3 | Emily Schultz       | Vancouver     |    0 | 
|   4 | Hector Rolfson      | San Francisco |    3 | 
|   5 | Keven Bradtke       | Toronto       |    1 | 
|   6 | Shana Dickens       | New York      |    2 | 
|   7 | Beryl Gottlieb      | New York      |    5 | 
|   8 | Palma Ledner V      | Vancouver     |    3 | 
|   9 | Colten Toy          | Toronto       |    2 | 
|  10 | Vivianne Wiza       | New York      |    9 | 
|  11 | Lavinia Bogan       | Montreal      |   10 | 
|  12 | Catherine Watsica   | San Francisco |    2 | 
|  13 | Kirstin Balistreri  | San Francisco |    1 | 
|  14 | Marian Abbott III   | Toronto       |   11 | 
|  15 | Ciara Mills         | Montreal      |    0 | 
|  16 | Earline Johns       | New York      |   12 | 
|  17 | Juvenal Goyette     | Montreal      |   13 | 
|  18 | Aaron Waelchi       | New York      |   12 | 
|  19 | Jonathan Cormier    | San Francisco |   18 | 
|  20 | Cortney Dooley DDS  | Vancouver     |   16 | 
|  21 | Cade O'Connell III  | Toronto       |    9 | 
|  22 | Javonte Thiel       | New York      |   18 | 
|  23 | Lourdes McDermott   | New York      |   17 | 
|  24 | Jerrold Harber      | San Francisco |    8 | 
|  25 | Bianka Marvin       | Vancouver     |    1 | 
|  26 | Chandler Bernhard   | Vancouver     |   19 | 
|  27 | Furman Okuneva      | New York      |   21 | 
|  28 | Dorthy Luettgen     | Toronto       |    2 | 
|  29 | Louvenia Mayer      | San Francisco |   20 | 
|  30 | Dillan Pfannerstill | Toronto       |   28 | 
+-----+---------------------+---------------+------+
30 rows in set (0.00 sec)

mysql> SELECT * FROM WorksFor;
+------+------+-------+
| pid  | eid  | since |
+------+------+-------+
|    2 |    1 |  2006 | 
|    3 |    2 |  2005 | 
|    7 |    3 |  2007 | 
|    3 |    4 |  2007 | 
|    5 |    5 |  2006 | 
|    5 |    6 |  2006 | 
|    8 |    7 |  2004 | 
|    3 |    8 |  2006 | 
|    7 |   10 |  2007 | 
|    8 |   11 |  2005 | 
|    3 |   12 |  2006 | 
|    3 |   13 |  2007 | 
|    1 |   14 |  2008 | 
|    6 |   15 |  2005 | 
|    7 |   16 |  2004 | 
|   10 |   17 |  2004 | 
|    1 |   18 |  2009 | 
|    9 |   19 |  2008 | 
|    3 |   20 |  2003 | 
|    5 |   21 |  2003 | 
|    5 |   22 |  2007 | 
|    1 |   23 |  2005 | 
|   10 |   24 |  2006 | 
|    6 |   25 |  2004 | 
|    2 |   26 |  2006 | 
|    2 |   27 |  2006 | 
|    1 |   28 |  2009 | 
|    9 |   29 |  2007 | 
|    1 |   30 |  2006 | 
+------+------+-------+
29 rows in set (0.00 sec)

mysql> -- Question 15.
mysql> SELECT P.pid, COUNT(WF.eid) FROM Project P LEFT OUTER JOIN WorksFor WF ON WF.pid =
    -> P.pid GROUP BY WF.pid;
+-----+---------------+
| pid | COUNT(WF.eid) |
+-----+---------------+
|   4 |             0 | 
|   1 |             5 | 
|   2 |             3 | 
|   3 |             6 | 
|   5 |             4 | 
|   6 |             2 | 
|   7 |             3 | 
|   8 |             2 | 
|   9 |             2 | 
|  10 |             2 | 
+-----+---------------+
10 rows in set (0.00 sec)

mysql> CREATE VIEW Temp(pid, number) AS
    -> SELECT W.pid,COUNT(*) AS number
    -> FROM WorksFor W
    -> GROUP BY W.pid;
Query OK, 0 rows affected (0.12 sec)

mysql> SELECT T.pid,T.number FROM Temp T
    -> WHERE T.number = (SELECT MAX(T2.number) FROM Temp T2);
+------+--------+
| pid  | number |
+------+--------+
|    3 |      6 | 
+------+--------+
1 row in set (0.00 sec)

mysql> SELECT pid FROM WorksFor GROUP BY pid
    -> HAVING COUNT(eid) = (SELECT MAX(employee_count) FROM (SELECT COUNT(WF.eid) AS
    -> employee_count FROM WorksFor WF GROUP BY WF.pid) MaxCount);
+------+
| pid  |
+------+
|    3 | 
+------+
1 row in set (0.00 sec)