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)
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)