CPSC340A
Database Systems

Activity 8

Grouping

Sales Orders Database

  1. 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)
  2. 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)
  3. 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

  1. 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

  1. 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)
  2. 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

  1. 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)
  2. 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

  1. 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)
  2. 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)