Submit your your queries to the following prompts as a .sql file, with comments, on the course Inquire site.
Entertainment Agency Database
Display agents who haven’t booked an entertainer.
+---------+--------------+-------------+ | AgentID | AgtFirstName | AgtLastName | +---------+--------------+-------------+ | 9 | Daffy | Dumbwit | +---------+--------------+-------------+ (1 row)List customers with no bookings.
+------------+---------------+--------------+ | CustomerID | CustFirstName | CustLastName | +------------+---------------+--------------+ | 10008 | Darren | Gehring | | 10011 | Joyce | Bonnicksen | +------------+---------------+--------------+ (2 rows)List all entertainers and any engagements they have booked.
+---------------+--------------------------+------------------+------------+------------+ | EntertainerID | EntStageName | EngagementNumber | StartDate | CustomerID | +---------------+--------------------------+------------------+------------+------------+ | 1001 | Carol Peacock Trio | 12 | 2012-09-18 | 10014 | | 1001 | Carol Peacock Trio | 24 | 2012-10-01 | 10001 | | 1001 | Carol Peacock Trio | 35 | 2012-10-14 | 10005 | | 1001 | Carol Peacock Trio | 43 | 2012-10-21 | 10001 | | 1001 | Carol Peacock Trio | 49 | 2012-11-13 | 10014 | | 1001 | Carol Peacock Trio | 70 | 2012-12-23 | 10010 | . . . (112 rows)
School Scheduling Database
Display subjects with no faculty assigned.
+-----------+-------------+----------------------+ | SubjectID | SubjectCode | SubjectName | +-----------+-------------+----------------------+ | 29 | JRN 104 | College Publications | +-----------+-------------+----------------------+ (1 row)List students not currently enrolled in any classes. (Hint: You need to find which students have an enrolled class status in student schedules and then find the students who are not in this set.)
+-----------+---------------+--------------+ | StudentID | StudFirstName | StudLastName | +-----------+---------------+--------------+ | 1002 | David | Hamilton | | 1010 | Marianne | Wier | +-----------+---------------+--------------+ (2 rows)Display all faculty and the classes they are scheduled to teach.
+--------------------+--------------------------------------------+---------+-------------+-----------+----------+ | StaffName | SubjectName | ClassID | ClassRoomID | StartTime | Duration | +--------------------+--------------------------------------------+---------+-------------+-----------+----------+ | Viescas, Suzanne | Art History | 1031 | 1231 | 14:00:00 | 50 | | Viescas, Suzanne | Art History | 4031 | 1231 | 14:00:00 | 50 | | Viescas, Suzanne | Composition - Intermediate | 1183 | 3415 | 13:00:00 | 50 | | Viescas, Suzanne | Composition - Intermediate | 4183 | 3415 | 13:00:00 | 50 | | Hallmark, Gary | Financial Accounting Fundamentals II | 2510 | 3307 | 13:00:00 | 80 | | Hallmark, Gary | Financial Accounting Fundamentals II | 5510 | 3307 | 13:00:00 | 80 | . . . (135 rows)
Recipes Database
Display missing types of recipes.
+------------------------+ | RecipeClassDescription | +------------------------+ | Soup | +------------------------+ (1 row)Show me all ingredients and any recipes they’re used in.
+-------------------------------+----------+-------------------------------------------------------+ | IngredientName | RecipeID | RecipeTitle | +-------------------------------+----------+-------------------------------------------------------+ | Beef | 1 | Irish Stew | | Beef | 9 | Roast Beef | | Onion | 1 | Irish Stew | | Onion | 2 | Salsa Buena | | Onion | 3 | Machos Nachos | | Onion | 11 | Huachinango Veracruzana (Red Snapper, Veracruz style) | | Onion | 13 | Tourtière (French-Canadian Pork Pie) | . . . (108 rows)List the salad, soup, and main course categories and any recipes.
+-------------+-------------------------------------------------------+ | ClassName | RecipeTitle | +-------------+-------------------------------------------------------+ | Main course | Irish Stew | | Main course | Fettuccini Alfredo | | Main course | Pollo Picoso | | Main course | Roast Beef | | Main course | Huachinango Veracruzana (Red Snapper, Veracruz style) | | Main course | Tourtière (French-Canadian Pork Pie) | | Main course | Salmon Filets in Parchment Paper | | Salad | Mike's Summer Salad | | Soup | NULL | +-------------+-------------------------------------------------------+ (9 rows)