Submit your your queries to the following prompts as a .sql file, with comments, on the course Inquire site.
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)
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)