The Computer Shop – SQL Task

Working for an online computer shop, you have been asked to assist customers in finding the best computer deal to match their requirements and needs. Your shop has access to a large variety of laptop and desktop computers and hence you should be able to recommend a computer for every customer!

To make the right recommendation to your customers, you have been given access to an online database of all the computers 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 computer that meets their requirements,
  • Repeat this process for each customer.

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

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

SELECT * FROM computers WHERE type="Laptop" AND touchscreen=TRUE AND screenSize>=15;
SELECT * FROM computers WHERE type="Desktop" AND (HDD>1000 OR SSD>1000);
SELECT * FROM computers WHERE type="Laptop" AND RAM>=8 AND operatingSystem="Windows" AND (price>=300 AND price<=400);

Customer A:Customer B:Customer C:Customer D:Customer E:
Customer A is looking for a laptop computer to work on the train when commuting to work. They need a Windows laptop that is easy to carry around so would like a screen size of less than 16 inches. They would like the laptop to be touchscreen and would need an SSD drive of at least 512GB. They can spend up to £500 on this laptop.


SQL Query:

Recommended Computer:

Customer B is looking for a multimedia desktop computer to watch movies and stream music online. They would like a dual core or a quad core CPU with a clock speed of at least 2.5Ghz. They would like a HDD drive of at least 2TB (2000GB). They would like a large screen of at least 16 inches.


SQL Query:

Recommended Computer:

Customer C is a graphic designer who would like a desktop computer to use graphic editing and photo-editing software. They are looking for a MacOS desktop computer with a touchscreen of 17 inches. They would need an SSD drive of at least 256GB and a secondary hard drive to backup their work with at least 1TB (1000GB). They are happy to spend up to £600 on their computer.


SQL Query:

Recommended Computer:

Customer D is a student who needs a laptop to take notes during lectures. They have a small budget (up to £350) and would like a computer with at 6GB or 8GB of RAM, a screen size between 14 to 16 inches, a secondary drive either HDD or SSD of at least 512GB and the Windows operating system already installed.


SQL Query:

Recommended Computer:

Customer E is looking for a laptop to be used by his family around the house. They would prefer it to be a dual core laptop with at least 8GB of RAM. They would like a touchscreen of 15.6 inches and an SSD drive of either 256GB or 512GB.


SQL Query:

Recommended Computer:

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 Computer in StockDiscount on Windows LaptopsDiscontinued Range
The shop manager would like you to produce a price list, only listing the name, type, operating system and price of all the Laptops in stock, in descending order of price (from the most expensive computer to the cheapest computer).

SQL Query:

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

Name Notebook NB-450
Type Laptop
Number of Cores 4 (Quad Core)
Clock Speed 3.5GHz
RAM 16GB
Hard Disk Drive 1TB
Solid State Drive 256GB
Screen Size 16
Touchscreen
Operating System MacOS
Price £550

Warning: In this computers 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 20% discount on all Windows laptops. Use a query to update the reduce the price of all windows laptops by 20%.

SQL Query:

The shop has decided to stop selling computers from the “Smartpad” series and from the “Smartbook” series. Use a query to remove all the computers from the computers table where the name of the computer starts with either “Smartpad” or “Smartbook”.

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.3 / 5. Vote count: 117

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

As you found this challenge interesting...

Follow us on social media!

Tagged with: