The Bike Shop – SQL Task

As a newly appointed shop assistant for your local bike shop, you have been asked to help customers find their dream bike: a bike that meets all of their requirements. Your shop has access to a large variety of mountain bikes, road bikes and hybrid bikes and hence you should be able to find a bike for every customer!

Each of these bikes has its own specifications such as frame type (crossbar or low-edge frame), frame size (from XS to XL), wheels size, number of gears and so on.

To assist you in helping find the perfect bikes for your customers, you have been given access to an online database of all the bikes available in stock. You can access and query this online database using our online SQL Editor:
Open in new windowAccess Online Database / SQL Editor

Your job is to:

  • Pick a customer and read through their requirements (see tabs below),
  • Write an SQL query to interrogate the database to identify the bike that meets their requirements,
  • Repeat this process for each customer.

Note that each one of your query should return one and only one bike that fully satisfies the customer’s demands.

Here are some examples of SQL queries based on the bikes table:

SELECT * FROM bikes WHERE type="Mountain Bike" AND frame_size="L" AND electric=TRUE AND weight<10;
SELECT * FROM bikes WHERE type="Hybrid Bike" AND (frame_size="L" OR frame_size="XL") AND Hydraulic_Brakes=True;
SELECT * FROM bikes WHERE type="Road Bike" AND (price>=300 AND price<=500);

Customer A: Milly SpokeCustomer B: Tim SaddleCustomer C: Ayaz BearingCustomer D: Nikky HelmetCustomer E: Fred TyreCustomer F: Hamza ChainstayCustomer G: Joe CrossbarCustomer H: Lin DynamoCustomer I: Marek HubcapCustomer J: Anna Fork
Milly Spoke is looking for a hybrid bike to commute every day to her college. She would like a bike in the price range of £150 to £250. Ideally her bike should not be too heavy so she is looking at a bike of less than 15kg. Based on her height, she was recommended to consider an M frame size. She is not too fussy about technical characteristics such as number of gears or types of brakes!


SQL Query:

Recommended Bike:

Tim Saddle is a keen cyclist and would like to improve his performance when taking part in bike races. He is very specific in his requirements and is looking for a state of the art road bike with at least 27 gears, hydraulic brakes, disc brakes with a very light frame (<8 kg), 20 inches wheels and a large frame size (L). He has no limits on how much he can spend for this bike!


SQL Query:

Recommended Bike:

Ayaz Bearing is looking for an electric Mountain Bike to go mountain biking during the holidays. He would like a Crossbar frame with at least 21 gears. He is not too sure about the size so would like to find out if you have any bike in stock with either an L frame or XL frame.


SQL Query:

Recommended Bike:

Nikky Helmet is looking for either a mountain bike or hybrid bike to go through cycling paths in the forest. She is looking for an L frame, low-step frame and would like the bike to be fitted with hydraulic brakes.


SQL Query:

Recommended Bike:

Fred Tyre works in the city and is commuting to work everyday by bike. He is looking for a hybrid e-bike (electric). He needs an M frame with hydraulic disc brakes and has a budget of £500 to £700.


SQL Query:

Recommended Bike:

Hamza Chainstay is looking for a road bike, with a crossbar frame size M and wheels of 24, 26 or 27 inches. He would like the bike to weight no more than 12kg.


SQL Query:

Recommended Bike:

Joe Crossbar is looking for a mountain bike, with a crossbar frame size M or L. He would like hydraulic brakes but does not want the bike to be fitted with disc brakes. He is not interested in electric bikes.


SQL Query:

Recommended Bike:

Lin Dynamo is looking for an electric hybrid bike to commute to work every day. She would like a low-step frame but is not too sure of the size she needs. She would like to see if you have either an S or an M frame in stock. She would also like to spend less than £600 on her bike.


SQL Query:

Recommended Bike:

Marek Hubcap would like a road bike within a budget of £200 to £300 and at least 24 gears. He would like to have disc brakes and a light frame e.g. less than 10 kg.


SQL Query:

Recommended Bike:

Anna Fork would like a non-electric mountain bike, frame M with 21 or 24 gears, hydraulic and disc brakes. She has a budget of £300 to £400 for this bike.


SQL Query:

Recommended Bike:

Extension Tasks:

Warning: When completing the following extension tasks, note that, on this online system, Boolean values are actually stored as string containing the values “TRUE” and “FALSE”. This means that, in your queries, you will need to use “speechmarks” when storing the values “TRUE” and “FALSE” as string instead of Boolean values.

Price ListNew Bike in StockDiscount on Electric BikesDiscontinued Range
The shop manager would like you to produce a price list, only listing the name, type, frame size and price of all the Mountain bikes in stock, in descending order of price (from the most expensive bike to the cheapest bike).

SQL Query:

A new bike has been delivered to the shop and needs to be added to the database. Run a query to add this bike to the bikes table. The characteristics of this new bike are as follows:

Name Urban Explorer X-200
Type Hybrid
Frame Type Crossbar
Electric
Frame Size M
Wheel Size 24 inches
Gears 21
Weight 14.2Kg
Hydraulic Brakes
Disc Brakes
Price £650

Warning: In this “bikes” table the ID field is not set as an AUTO_INCREMENT field. Which means that you will have to provide a value for the ID field within your INSERT query.

SQL Query:

The shop would like to apply a 10% discount on all electric bikes. Use a query to update the reduce the price of all electric bikes by 10%.

SQL Query:

The shop has decided to stop selling bikes from the “Road Rider” series. Use a query to remove all the bikes from the bikes table where the name of the bike starts with “Road Rider”.

SQL Query:

unlock-access

Solution...

The solution for this challenge is available to full members!
Find out how to become a member:
➤ Members' Area

Did you like this challenge?

Click on a star to rate it!

Average rating 3.9 / 5. Vote count: 94

No votes so far! Be the first to rate this post.

As you found this challenge interesting...

Follow us on social media!

Tagged with: