Sales Orders Database
List the customers who ordered a helmet together with the vendors who provide helmets. (Hint: This involves creating a UNION of two complex JOINs.)
+------------------------+------------------------------+----------+ | FullName | ProductName | RowID | +------------------------+------------------------------+----------+ | Viescas, Suzanne | Viscount Microshell Helmet | Customer | | Viescas, Suzanne | Glide-O-Matic Cycling Helmet | Customer | | Viescas, Suzanne | King Cobra Helmet | Customer | | Thompson, William | Glide-O-Matic Cycling Helmet | Customer | | Thompson, William | Dog Ear Helmet Mount Mirrors | Customer | | Thompson, William | King Cobra Helmet | Customer | | Hallmark, Gary | Glide-O-Matic Cycling Helmet | Customer | | Hallmark, Gary | King Cobra Helmet | Customer | | Hallmark, Gary | Viscount Microshell Helmet | Customer | | Hallmark, Gary | Dog Ear Helmet Mount Mirrors | Customer | | Brown, Robert | Glide-O-Matic Cycling Helmet | Customer | | Brown, Robert | Viscount Microshell Helmet | Customer | | Brown, Robert | King Cobra Helmet | Customer | | McCrae, Dean | Glide-O-Matic Cycling Helmet | Customer | | McCrae, Dean | King Cobra Helmet | Customer | | McCrae, Dean | Viscount Microshell Helmet | Customer | | McCrae, Dean | Dog Ear Helmet Mount Mirrors | Customer | | Viescas, John | King Cobra Helmet | Customer | | Viescas, John | Glide-O-Matic Cycling Helmet | Customer | | Sergienko, Mariya | Dog Ear Helmet Mount Mirrors | Customer | | Sergienko, Mariya | Viscount Microshell Helmet | Customer | | Sergienko, Mariya | King Cobra Helmet | Customer | | Sergienko, Mariya | Glide-O-Matic Cycling Helmet | Customer | | Patterson, Neil | Viscount Microshell Helmet | Customer | | Patterson, Neil | King Cobra Helmet | Customer | | Patterson, Neil | Glide-O-Matic Cycling Helmet | Customer | | Patterson, Neil | Dog Ear Helmet Mount Mirrors | Customer | | Cencini, Andrew | King Cobra Helmet | Customer | | Cencini, Andrew | Glide-O-Matic Cycling Helmet | Customer | | Cencini, Andrew | Dog Ear Helmet Mount Mirrors | Customer | . . . (91 rows)
Entertainment Agency Database
Display a combined list of customers and entertainers. (Hint: Be careful to create an expression for one of the names so that you have the same number of columns in both SELECT statements.)
+--------------------------+-------------+ | Name | Type | +--------------------------+-------------+ | Hartwig, Doris | Customer | | Waldal, Deb | Customer | | Brehm, Peter | Customer | | McCrae, Dean | Customer | | Hallmark, Elizabeth | Customer | | Berg, Matt | Customer | | Keyser, Liz | Customer | | Gehring, Darren | Customer | | Thompson, Sarah | Customer | | Ehrlich, Zachary | Customer | | Bonnicksen, Joyce | Customer | | Patterson, Kerry | Customer | | Pundt, Estella | Customer | | Rosales, Mark | Customer | | Viescas, Carol | Customer | | Carol Peacock Trio | Entertainer | | Topazz | Entertainer | | JV & the Deep Six | Entertainer | | Jim Glynn | Entertainer | | Jazz Persuasion | Entertainer | | Modern Dance | Entertainer | | Coldwater Cattle Company | Entertainer | | Country Feeling | Entertainer | | Katherine Ehrlich | Entertainer | | Saturday Revue | Entertainer | | Julia Schnebly | Entertainer | | Susan McLain | Entertainer | | Caroline Coie Cuartet | Entertainer | +--------------------------+-------------+ (28 rows)Produce a list of customers who like contemporary music together with a list of entertainers who play contemporary music. (Hint: You need to UNION two complex JOINs to solve this one.)
+-----------------------+-------------+ | Name | Type | +-----------------------+-------------+ | Doris Hartwig | Customer | | Darren Gehring | Customer | | Kerry Patterson | Customer | | Carol Peacock Trio | Entertainer | | Caroline Coie Cuartet | Entertainer | +-----------------------+-------------+ (5 rows)
School Scheduling Database
Create a mailing list for students and staff, sorted by ZIP Code.
| Name | StudStreetAddress | StudCity | StudState | StudZipCode | +--------------------+-------------------------+--------------+-----------+-------------+ | Robert Brown | 672 Lamont Ave | Houston | TX | 77201 | | Kerry Patterson | 9877 Hacienda Drive | San Antonio | TX | 78284 | | Kirk DeGrasse | 455 West Palm Ave | San Antonio | TX | 78284 | | Sarah Thompson | 2222 Springer Road | Lubbock | TX | 79402 | | Steve Pundt | 2500 Rosales Lane | Dallas | TX | 79915 | | Maria Patterson | 3445 Cheyenne Road | El Paso | TX | 79993 | | Caleb Viescas | 4501 Wetland Road | Long Beach | CA | 90809 | | Brannon Jones | 777 Fenexet Blvd | Long Beach | CA | 90809 | | Betsy Stadick | 611 Alpine Drive | Palm Springs | CA | 92263 | | Sam Abolrous | 611 Alpine Drive | Palm Springs | CA | 92263 | | Jeffrey Smith | 30301 - 166th Ave. N.E. | Fremont | CA | 94538 | | David Smith | 311 20th Ave. N.E. | Fremont | CA | 94538 | | Luke Patterson | 877 145th Ave SE | Portland | OR | 97208 | | Mariya Sergienko | 901 Pine Avenue | Portland | OR | 97208 | | John Kennedy | 16679 NE 41st Court | Portland | OR | 97208 | | Sara Sheskey | 16679 NE 41st Court | Portland | OR | 97208 | . . . (45 rows)
Bowling League Database
Find the bowlers who had a raw score of 165 or better at Thunderbird Lanes combined with bowlers who had a raw score of 150 or better at Bolero Lanes. (Hint: This can also be solved with a UNION or a single SELECT statement and a complex WHERE clause.)
+-------------------+----------------+-----------------+----------+ | TourneyLocation | BowlerLastName | BowlerFirstName | RawScore | +-------------------+----------------+-----------------+----------+ | Thunderbird Lanes | Kennedy | John | 181 | | Thunderbird Lanes | Cunningham | David | 167 | | Thunderbird Lanes | Kennedy | John | 175 | | Thunderbird Lanes | Cunningham | David | 178 | | Thunderbird Lanes | Patterson | Neil | 179 | | Thunderbird Lanes | Viescas | David | 181 | | Thunderbird Lanes | Patterson | Kathryn | 180 | | Thunderbird Lanes | Viescas | David | 183 | | Thunderbird Lanes | Patterson | Kathryn | 177 | | Thunderbird Lanes | Viescas | David | 178 | | Thunderbird Lanes | Viescas | John | 189 | | Thunderbird Lanes | Thompson | Mary | 180 | | Thunderbird Lanes | Thompson | William | 167 | | Thunderbird Lanes | Viescas | John | 176 | | Thunderbird Lanes | Thompson | Mary | 175 | | Thunderbird Lanes | Thompson | William | 178 | | Thunderbird Lanes | Thompson | Sarah | 168 | | Thunderbird Lanes | Patterson | Rachel | 179 | | Thunderbird Lanes | Viescas | Caleb | 193 | | Thunderbird Lanes | Thompson | Sarah | 178 | . . . (129 rows)
Recipes Database
Display a list of all ingredients and their default measurement amounts together with ingredients used in recipes and the measurement amount for each recipe. (Hint: You need one simple JOIN and one complex JOIN to solve this.)
+-------------------------------+------------------------+------------+ | IngredientName | MeasurementDescription | Type | +-------------------------------+------------------------+------------+ | Beef | Ounce | Ingredient | | Water | Ounce | Ingredient | | Guinness Beer | Ounce | Ingredient | | Vinegar | Ounce | Ingredient | | Olive Oil | Ounce | Ingredient | | Fettuccini Pasta | Ounce | Ingredient | | Heavy Cream | Ounce | Ingredient | | Spaghetti | Ounce | Ingredient | | Mustard, Regular | Ounce | Ingredient | | Mustard, Dijon | Ounce | Ingredient | | Ketchup | Ounce | Ingredient | | Salsa | Ounce | Ingredient | | Parmesan Cheese | Ounce | Ingredient | | Blue Cheese | Ounce | Ingredient | | White Pepper (ground) | Ounce | Ingredient | | Cayenne Pepper, Ground | Ounce | Ingredient | | Balsamic Vinaigrette Dressing | Ounce | Ingredient | | Red Snapper | Ounce | Ingredient | | Potato | Cup | Ingredient | | Carrot | Cup | Ingredient | | Jalapeno | Cup | Ingredient | . . . (144 rows)