Submit your your queries to the following prompts as a .sql file, with comments, on the course Inquire site.
Entertainment Agency Database
Show each agent’s name, the sum of the contract price for the engagements booked, and the agent’s total commission for agents whose total commission is more than $1,000. (Hint: You must multiply the sum of the contract prices by the agent’s commission. Be sure to group on the commission rate as well!)
+--------------+-------------+--------------------+--------------------+ | AgtFirstName | AgtLastName | SumOfContractPrice | Commission | +--------------+-------------+--------------------+--------------------+ | Carol | Viescas | 24800.00 | 1240.0000184774399 | | John | Kennedy | 24435.00 | 1466.0999672301114 | | Karen | Smith | 18595.00 | 1022.7249944582582 | | Marianne | Wier | 22635.00 | 1018.575040474534 | +--------------+-------------+--------------------+--------------------+ (4 rows)
School Scheduling Database
Display by category the category name and the count of classes offered. (Hint: Use COUNT and group on category name.)
+------------------------------+------------+ | CategoryDescription | ClassCount | +------------------------------+------------+ | Accounting | 6 | | Art | 16 | | Biology | 6 | | Chemistry | 8 | | Computer Information Systems | 6 | | Computer Science | 4 | | Economics | 12 | | English | 14 | | Geography | 4 | | History | 8 | | Journalism | 2 | | Math | 24 | | Music | 8 | | Physics | 8 | | Political Science | 6 | +------------------------------+------------+ (15 rows)List each staff member and the count of classes each is scheduled to teach. ( Hint: Use COUNT and group on staff name.)
+--------------+-------------+------------+ | StfFirstName | StfLastname | ClassCount | +--------------+-------------+------------+ | Alaina | Hallmark | 8 | | Alastair | Black | 6 | | Ann | Patterson | 8 | | Caleb | Viescas | 8 | | Carol | Viescas | 4 | | David | Smith | 6 | | Deb | Waldal | 10 | | Gary | Hallmark | 4 | | Jim | Glynn | 6 | | Jim | Wilson | 6 | | Joyce | Bonnicksen | 4 | | Katherine | Ehrlich | 4 | | Kirk | DeGrasse | 4 | | Liz | Keyser | 10 | | Luke | Patterson | 2 | | Maria | Patterson | 8 | | Mariya | Sergienko | 4 | | Michael | Hernandez | 2 | | Peter | Brehm | 4 | | Robert | Brown | 10 | | Sam | Abolrous | 8 | | Suzanne | Viescas | 4 | +--------------+-------------+------------+ (22 rows)
Recipes Database
If I want to cook all the recipes in my cookbook, how much of each ingredient must I have on hand? (Hint: Use SUM and group on ingredient name and measurement description.)
+-------------------------------+------------------------+-------------+ | IngredientName | MeasurementDescription | SumOfAmount | +-------------------------------+------------------------+-------------+ | Asparagus | Pound | 1 | | Balsamic Vinaigrette Dressing | Tablespoon | 3 | | Beef | Pound | 5 | | Beef drippings | Teaspoon | 4 | | Bird's Custard Powder | Package | 1 | | Black Olives | Cup | 0.25 | | Black Pepper (ground) | Tablespoon | 1 | | Black Pepper (ground) | Teaspoon | 4 | | Bread Crumbs | Cup | 0.5 | | Butter | Tablespoon | 14 | | Canned tomatoes | Can | 2 | | Carrot | Piece | 2 | | Carrot | Whole | 6 | | Cayenne Pepper, Ground | To Taste | 0 | | Cheddar Cheese | Cup | 1 | | Chicken Leg | Piece | 2 | | Chicken Thigh | Piece | 2 | | Cinnamon | Tablespoon | 1 | | Cinnamon | Teaspoon | 0.5 | | Colored sugar sprinkles | Teaspoon | 1 | | Cucumber | Whole | 1 | | Eggs | Piece | 2 | | Fettuccini Pasta | Ounce | 16 | | Flour | Cup | 2 | | Garlic | Clove | 15 | | Green Beans | Pound | 0.5 | | Green Olives | Piece | 8 | | Ground Clove | Teaspoon | 0.5 | | Ground Pork | Pound | 2 | | Guinness Beer | Ounce | 12 | | Heavy Cream | Cup | 0.25 | | Jalapeno | Whole | 10 | | Leek | Piece | 1 | | Lemon | Piece | 1 | | Lemon Juice | Tablespoon | 2 | | Lemon Sorbet | Scoop | 2 | | Lime Juice | Teaspoon | 2 | | Milk | Cup | 0.5 | | Mushrooms | Piece | 12 | | Nutmeg | Teaspoon | 0.25 | | Olive Oil | Ounce | 4 | | Olive Oil | Tablespoon | 1 | | Onion | Cup | 0.333 | | Onion | Whole | 4 | | Parmesan Cheese | Ounce | 6 | | Parsley | Sprig | 2 | | Pie dough for 2-crust pie | sticks | 2 | | Potato | Whole | 4 | | Radishes | Bunch | 1 | | Raspberry Jam | Jar | 1 | | Raspberry Jello | Package | 1 | | Red Bell Pepper | Piece | 1 | | Red Snapper | Pound | 2 | | Romaine Lettuce | Head | 1 | | Salmon | filets | 4 | | Salt | Teaspoon | 8.75 | | Sponge Cake | Package | 1 | | Tomato | Whole | 3 | | Tortilla Chips | Bag | 0.5 | | Vegetable Oil | Tablespoon | 1 | | Vodka | Tablespoon | 2 | | Water | Cup | 1 | | Water | Quarts | 4 | | White Pepper (ground) | To Taste | 0 | | White Wine | Tablespoon | 2 | +-------------------------------+------------------------+-------------+ (65 rows)For what class of recipe do I have two or more recipes? (Hint: JOIN recipe classes with recipes, count the result, and keep the ones with two or more with a HAVING clause.
+------------------------+-----------------+ | RecipeClassDescription | CountOfRecipeID | +------------------------+-----------------+ | Dessert | 2 | | Hors d'oeuvres | 2 | | Main course | 7 | | Vegetable | 2 | +------------------------+-----------------+ (4 rows)