Using SQL:

1. Prepare a report that identifies the five most expensive bicycles (based on selling price). The report should list the bicycle name from most expensive to least expensive, the quantity on hand for each, and the record level for each bicycle.

2. Prepare a report that lists each supplier (company name), its products (product_type, product_name), the quantities on hand, and associated reorder levels. The report should be sorted alphabetically by supplier company name in ascending order. For each supplier, the products should be sorted alphabetically in descending order.

3. Prepare a report listing only the bicycles (product name, category, purchase cost, quantity on hand) that are low in stock (quantity on hand is less than 5) and need to be reordered. The report should provide supplier information (supplier company name, street address, city, state, zip) also for the items identified.

4. Prepare a report showing Supplier company name, city, and number of bikes which they supply. (Clue: Inner join doesnt work here as some suppliers do not have any products in the product table).

Suppliers table

(? Homework_Assignment_3_suppliers id Supplier_Number Company Name Street_Address City State zip_Code 1 1 Bicyclist's Choice 1459 Washington Avenue Sioux Falls SD 57106 2 2 Bike-One 2000 Oceanside Lane San Diego CA 92110 3 3 Run-Up Bikes 1155 Fifth Avenue Long Branch NJ 7521 4 4 Simpson's Bike Supply 107 Piedmont Tulsa OK 74114 5 5 The Bike Path 11937 1999 Stephen Hands Path East Hampton NY 2345 cordova st Cambridge MA 6 6 Cambridge Bikes 23409 7 7 Road runner bikes 84 Main St Pleasanton CA 94565 ID 7 Product_Number Product_Category Product_Name Purchase_Cost Selling Price Supplier_Number Quantity_on_Hand Reorder_Level 7 Hybrid Runblend 2000 180.95 255.99 3 0 5 20 Road Twist & Shout 490.50 635.70 4 0 2 11 Leisure Starlight 100.47 140.66 4 1 2 20 11 6 6 Mtn. Eagle 1 410.01 574.01 2 1 2 24 24 Mtn. Jetty Breaker 455.95 649.95 5 1 2 5 5 Mtn. Eagle 2 401.11 561.54 2 2 2 9 9 Hybrid Tea for Two 429.02 609.00 4 3 2 12 12 Leisure Supreme 350 50.00 70.00 1 3 5 23 23 Mtn. Bluff Breaker 375.00 495.00 5 3 3 21 21 Leisure Breeze 89.95 130.95 5 4 2 17 17 Children Green Rider 95.47 133.66 4 4 5 13 13 Leisure Blue Moon 75.29 105.41 4 4 2 4 4 Mtn. Eagle 3 350.52 490.73 2 5 2 22 22 Leisure Breeze LE 109.95 149.95 5 5 4 19 19 Road Runroad 5000 450.95 599.99 3 5 5 10 10 Leisure Runcool 3000 85.95 135.99 3 5 5 18 18 Road Runroad 4000 390.95 495.99 3 5 5 16 16 Children Coolest 100 69.99 97.98 1 6 5 1 1 Road Classic 109 207.49 290.49 1 7 2 2 2 Road Elegant 210 281.52 394.13 1 7 2 15 15 Children Red Rider 15.00 25.50 4 8 5 3 3 Road Runroad 1000 250.95 350.99 3 8 5 8 8 Hybrid 150.89 211.46 2 9 2 Eagle 7 Runkidder 100 14 14 Children 50.95 75.99 3 10 5