Sales Orders Database
Show me customers who have never ordered a helmet. (Hint: This is another request where you must first build an INNER JOIN to find all orders containing helmets and then do an OUTER JOIN with Customers.)
+------------+---------------+--------------+ | CustomerID | CustFirstName | CustLastName | +------------+---------------+--------------+ | 1011 | Alaina | Hallmark | | 1023 | Julia | Schnebly | | 1028 | Jeffrey | Tirekicker | +------------+---------------+--------------+ (3 rows)Display customers who have no sales rep (employees) in the same ZIP Code.
+------------+---------------+--------------+-------------+ | CustomerID | CustFirstName | CustLastName | CustZipCode | +------------+---------------+--------------+-------------+ | 1004 | Robert | Brown | 77201 | | 1007 | Mariya | Sergienko | 97208 | | 1008 | Neil | Patterson | 92199 | | 1010 | Angel | Kennedy | 78710 | | 1011 | Alaina | Hallmark | 98072 | | 1013 | Rachel | Patterson | 92199 | | 1014 | Sam | Abolrous | 92263 | | 1015 | Darren | Gehring | 95926 | | 1016 | Jim | Wilson | 97301 | | 1017 | Manuela | Seidel | 97501 | | 1018 | David | Smith | 94538 | | 1019 | Zachary | Ehrlich | 91209 | | 1021 | Estella | Pundt | 75260 | | 1022 | Caleb | Viescas | 90809 | | 1023 | Julia | Schnebly | 99837 | | 1024 | Mark | Rosales | 79915 | | 1025 | Maria | Patterson | 79915 | | 1027 | Luke | Patterson | 97208 | +------------+---------------+--------------+-------------+ (18 rows)List all products and the dates for any orders.
+---------------+---------------------------------------+------------+ | ProductNumber | ProductName | OrderDate | +---------------+---------------------------------------+------------+ | 1 | Trek 9000 Mountain Bike | 2012-09-01 | | 1 | Trek 9000 Mountain Bike | 2012-09-02 | | 1 | Trek 9000 Mountain Bike | 2012-09-03 | | 1 | Trek 9000 Mountain Bike | 2012-09-04 | | 1 | Trek 9000 Mountain Bike | 2012-09-05 | | 1 | Trek 9000 Mountain Bike | 2012-09-06 | | 1 | Trek 9000 Mountain Bike | 2012-09-07 | | 1 | Trek 9000 Mountain Bike | 2012-09-08 | | 1 | Trek 9000 Mountain Bike | 2012-09-09 | | 1 | Trek 9000 Mountain Bike | 2012-09-10 | | 1 | Trek 9000 Mountain Bike | 2012-09-12 | | 1 | Trek 9000 Mountain Bike | 2012-09-13 | | 1 | Trek 9000 Mountain Bike | 2012-09-14 | | 1 | Trek 9000 Mountain Bike | 2012-09-15 | | 1 | Trek 9000 Mountain Bike | 2012-09-18 | | 1 | Trek 9000 Mountain Bike | 2012-09-19 | | 1 | Trek 9000 Mountain Bike | 2012-09-20 | | 1 | Trek 9000 Mountain Bike | 2012-09-21 | | 1 | Trek 9000 Mountain Bike | 2012-09-22 | | 1 | Trek 9000 Mountain Bike | 2012-09-23 | | 1 | Trek 9000 Mountain Bike | 2012-09-24 | | 1 | Trek 9000 Mountain Bike | 2012-09-25 | | 1 | Trek 9000 Mountain Bike | 2012-09-26 | | 1 | Trek 9000 Mountain Bike | 2012-09-27 | | 1 | Trek 9000 Mountain Bike | 2012-09-29 | | 1 | Trek 9000 Mountain Bike | 2012-09-30 | | 1 | Trek 9000 Mountain Bike | 2012-10-01 | | 1 | Trek 9000 Mountain Bike | 2012-10-02 | . . . (2681 rows)
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)
Bowling League Database
Display matches with no game data.
+---------+-----------+-------------+--------------+ | MatchID | TourneyID | OddLaneTeam | EvenLaneTeam | +---------+-----------+-------------+--------------+ | 57 | 11 | MintJuleps | Huckleberrys | +---------+-----------+-------------+--------------+ (1 row)Display all tournaments and any matches that have been played.
+-----------+-------------+--------------------+---------+------------+------------+ | TourneyID | TourneyDate | TourneyLocation | MatchID | GameNumber | Winner | +-----------+-------------+--------------------+---------+------------+------------+ | 1 | 2012-09-04 | Red Rooster Lanes | 1 | 2 | Sharks | | 1 | 2012-09-04 | Red Rooster Lanes | 3 | 1 | Dolphins | | 1 | 2012-09-04 | Red Rooster Lanes | 4 | 3 | Manatees | | 1 | 2012-09-04 | Red Rooster Lanes | 2 | 1 | Terrapins | | 1 | 2012-09-04 | Red Rooster Lanes | 3 | 3 | Dolphins | | 1 | 2012-09-04 | Red Rooster Lanes | 4 | 2 | Swordfish | | 1 | 2012-09-04 | Red Rooster Lanes | 1 | 1 | Marlins | | 1 | 2012-09-04 | Red Rooster Lanes | 2 | 3 | Terrapins | | 1 | 2012-09-04 | Red Rooster Lanes | 3 | 2 | Orcas | . . . (174 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)Display all recipe classes and any recipes.
+------------------------+-------------------------------------------------------+ | RecipeClassDescription | 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 | . . . (16 rows)