We will work up to the primary table we want. Lets start with something a little bit less complex first. When working on these, keep in mind there are many ways to write equivalent SELECT
statements, so your solution may be different from the one given.
Twitter Table
See if you can re-create the following table with a SELECT
statement:
name | |
---|---|
Greg Benson | @gregorydbenson |
David Brizan | @davidguybrizan |
Sophie Engle | @sjengle |
Alark Joshi | @alark |
Matthew Malensek | @MatthewMalensek |
David Wolber | @wolberd |
Beste Yuksel | @BesteFYuksel |
See Answer.
Courses Table
Next, lets see if we can create a list of courses and who teaches them. It is easier to start by just counting the number of people that teach each course:
course | professors |
---|---|
CS 107 | 3 |
CS 110 | 5 |
CS 112 | 4 |
CS 220 | 1 |
CS 221 | 2 |
CS 245 | 3 |
CS 272 | 2 |
CS 315 | 2 |
CS 326 | 2 |
CS 336 | 1 |
CS 345 | 1 |
CS 360 | 2 |
CS 462 | 1 |
CS 463 | 1 |
CS 490 | 3 |
See Answer.
Now, we can move to the full table:
course | professors |
---|---|
CS 107 | Jeffrey Johnson, David Wolber, Beste Yuksel |
CS 110 | Jeffrey Johnson, Alark Joshi, Michael Kremer, David Wolber, Beste Yuksel |
CS 112 | Alark Joshi, EJ Jung, Olga Karpenko, David Wolber |
CS 220 | Matthew Malensek |
CS 221 | Phil Peterson, Vahab Pournaghshband |
CS 245 | David Brizan, EJ Jung, Olga Karpenko |
CS 272 | Sophie Engle, Olga Karpenko |
CS 315 | Greg Benson, Phil Peterson |
CS 326 | Greg Benson, Matthew Malensek |
CS 336 | Vahab Pournaghshband |
CS 345 | Mehmet Emre |
CS 360 | Sophie Engle, Alark Joshi |
CS 462 | Christopher Brooks |
CS 463 | David Brizan |
CS 490 | Jeffrey Johnson, Olga Karpenko, Beste Yuksel |
See Answer.
Faculty Table
Next, we can try and combine everything together. As before, I suggest you first work on a query that just gives counts:
name | courses | ||
---|---|---|---|
Greg Benson | benson@usfca.edu | 1 | 2 |
David Guy Brizan | dgbrizan@usfca.edu | 1 | 2 |
Christopher Brooks | cbrooks@usfca.edu | 0 | 1 |
Mehmet Emre | memre@usfca.edu | 0 | 1 |
Sophie Engle | sjengle@usfca.edu | 1 | 2 |
Jeffrey Johnson | jajohnson9@usfca.edu | 0 | 3 |
Alark Joshi | apjoshi@usfca.edu | 1 | 3 |
EJ Jung | ejung2@usfca.edu | 0 | 2 |
Olga Karpenko | okarpenko@usfca.edu | 0 | 4 |
Michael Kremer | mkremer@usfca.edu | 0 | 1 |
Matthew Malensek | mmalensek@usfca.edu | 1 | 2 |
Phil Peterson | phpeterson@usfca.edu | 0 | 2 |
Vahab Pournaghshband | vpournaghshband@usfca.edu | 0 | 2 |
David Wolber | wolberd@usfca.edu | 1 | 3 |
Beste Yuksel | byuksel@usfca.edu | 1 | 3 |
See Answer.
Now, we can work on creating the final version of our output:
name | courses | ||
---|---|---|---|
Greg Benson | benson@usfca.edu | @gregorydbenson | CS 315, CS 326 |
David Guy Brizan | dgbrizan@usfca.edu | @davidguybrizan | CS 245, CS 463 |
Christopher Brooks | cbrooks@usfca.edu | CS 462 | |
Mehmet Emre | memre@usfca.edu | CS 345 | |
Sophie Engle | sjengle@usfca.edu | @sjengle | CS 272, CS 360 |
Jeffrey Johnson | jajohnson9@usfca.edu | CS 107, CS 110, CS 490 | |
Alark Joshi | apjoshi@usfca.edu | @alark | CS 110, CS 112, CS 360 |
EJ Jung | ejung2@usfca.edu | CS 112, CS 245 | |
Olga Karpenko | okarpenko@usfca.edu | CS 112, CS 245, CS 272, CS 490 | |
Michael Kremer | mkremer@usfca.edu | CS 110 | |
Matthew Malensek | mmalensek@usfca.edu | @MatthewMalensek | CS 220, CS 326 |
Phil Peterson | phpeterson@usfca.edu | CS 221, CS 315 | |
Vahab Pournaghshband | vpournaghshband@usfca.edu | CS 221, CS 336 | |
David Wolber | wolberd@usfca.edu | @wolberd | CS 107, CS 110, CS 112 |
Beste Yuksel | byuksel@usfca.edu | @BesteFYuksel | CS 107, CS 110, CS 490 |