Sales Orders Database
Show me each vendor and the average by vendor of the number of days to deliver products. (Hint: Use the AVG aggregate function and group on vendor.)
+------------------------+-------------+ | VendName | AvgDelivery | +------------------------+-------------+ | Armadillo Brand | 13.1667 | | Big Sky Mountain Bikes | 10.8182 | | Dog Ear | 10.5556 | | Kona, Incorporated | 8.0000 | | Lone Star Bike Supply | 10.8667 | | Nikoma of America | 4.0000 | | ProFormance | 6.0000 | | Shinoman, Incorporated | 2.0000 | | Sun Sports Suppliers | 11.6000 | | Viscount | 3.0000 | +------------------------+-------------+ (10 rows)Display for each product the product name and the total sales. (Hint: Use SUM with a calculation of quantity times price and group on product name.)
+---------------------------------------+------------+ | ProductName | TotalSales | +---------------------------------------+------------+ | AeroFlo ATB Wheels | 75458.25 | | Clear Shade 85-T Glasses | 14581.35 | | Cosmic Elite Road Warrior Wheels | 63803.85 | | Cycle-Doc Pro Repair Stand | 62157.04 | | Dog Ear Aero-Flow Floor Pump | 36029.40 | | Dog Ear Cyclecomputer | 2238.75 | | Dog Ear Helmet Mount Mirrors | 767.73 | | Dog Ear Monster Grip Gloves | 2779.50 | | Eagle FS-3 Mountain Bike | 124002.00 | | Eagle SA-120 Clipless Pedals | 41613.90 | | Glide-O-Matic Cycling Helmet | 56286.25 | | GT RTS-2 Mountain Bike | 1109542.50 | | HP Deluxe Panniers | 15984.54 | | King Cobra Helmet | 57572.41 | | Kool-Breeze Rocket Top Jersey | 9528.96 | | Kryptonite Advanced 2000 U-Lock | 5999.50 | | Nikoma Lok-Tight U-Lock | 12488.85 | | Pro-Sport 'Dillo Shades | 20336.82 | | ProFormance ATB All-Terrain Pedal | 14792.96 | | ProFormance Toe-Klips 2G | 777.62 | | Road Warrior Hitch Pack | 64277.50 | | Shinoman 105 SC Brakes | 2656.30 | | Shinoman Deluxe TX-30 Pedal | 19885.50 | | Shinoman Dura-Ace Headset | 25254.15 | | StaDry Cycling Pants | 17468.73 | | TransPort Bicycle Rack | 9442.44 | | Trek 9000 Mountain Bike | 2192616.00 | | True Grip Competition Gloves | 7465.70 | | Turbo Twin Tires | 7590.46 | | Ultimate Export 2G Car Rack | 64728.00 | | Ultra-2K Competition Tire | 10747.74 | | Viscount C-500 Wireless Bike Computer | 18046.70 | | Viscount CardioSport Sport Watch | 27954.43 | | Viscount Microshell Helmet | 4219.20 | | Viscount Mountain Bike | 400678.65 | | Viscount Tru-Beat Heart Transmitter | 17720.41 | | Wonder Wool Cycle Socks | 6326.24 | | X-Pro All Weather Tires | 6911.04 | +---------------------------------------+------------+ (38 rows)List all vendors and the count of products sold by each.
+------------------------+----------------------+ | VendName | CountOfProductNumber | +------------------------+----------------------+ | Armadillo Brand | 6 | | Big Sky Mountain Bikes | 22 | | Dog Ear | 9 | | Kona, Incorporated | 1 | | Lone Star Bike Supply | 30 | | Nikoma of America | 5 | | ProFormance | 3 | | Shinoman, Incorporated | 3 | | Sun Sports Suppliers | 5 | | Viscount | 6 | +------------------------+----------------------+ (10 rows)
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)
Bowling League Database
Display for each bowler the bowler name and the average of the bowler’s raw game scores. (Hint: Use the AVG aggregate function and group on bowler name.)
+---------------------+---------------+ | BowlerFullName | AvgOfRawScore | +---------------------+---------------+ | Black, Alastair | 150.4524 | | Cunningham, David | 159.5714 | | Ehrlich, Zachary | 147.8095 | | Fournier, Barbara | 148.6190 | | Fournier, David | 156.6905 | | Hallmark, Alaina | 157.6667 | | Hallmark, Bailey | 149.7857 | | Hallmark, Elizabeth | 151.8810 | | Hallmark, Gary | 156.9762 | | Hernandez, Kendra | 149.4286 | | Hernandez, Michael | 157.0238 | | Kennedy, Angel | 163.3571 | | Kennedy, John | 165.6190 | | Patterson, Ann | 149.2619 | | Patterson, Kathryn | 161.9048 | | Patterson, Megan | 150.2857 | | Patterson, Neil | 158.4286 | | Patterson, Rachel | 156.6905 | | Pundt, Steve | 162.8810 | | Rosales, Joe | 141.8333 | | Sheskey, Richard | 142.3333 | | Sheskey, Sara | 141.9762 | | Thompson, Mary | 157.1667 | | Thompson, Sarah | 169.0476 | | Thompson, William | 166.6667 | | Viescas, Caleb | 164.4762 | | Viescas, Carol | 142.4524 | | Viescas, David | 167.6667 | | Viescas, John | 168.1190 | | Viescas, Michael | 142.6429 | | Viescas, Stephanie | 142.4524 | | Viescas, Suzanne | 143.0714 | +---------------------+---------------+ (32 rows)Display for each bowler the bowler name and the average of the bowler’s raw game scores for bowlers whose average is greater than 155. (Hint: You need a simple HAVING clause comparing the AVG to a numeric literal.)
+-----------------+----------------+----------+ | BowlerFirstName | BowlerLastName | AvgScore | +-----------------+----------------+----------+ | Alaina | Hallmark | 157.6667 | | Angel | Kennedy | 163.3571 | | Caleb | Viescas | 164.4762 | | David | Cunningham | 159.5714 | | David | Fournier | 156.6905 | | David | Viescas | 167.6667 | | Gary | Hallmark | 156.9762 | | John | Kennedy | 165.6190 | | John | Viescas | 168.1190 | | Kathryn | Patterson | 161.9048 | | Mary | Thompson | 157.1667 | | Michael | Hernandez | 157.0238 | | Neil | Patterson | 158.4286 | | Rachel | Patterson | 156.6905 | | Sarah | Thompson | 169.0476 | | Steve | Pundt | 162.8810 | | William | Thompson | 166.6667 | +-----------------+----------------+----------+ (17 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)