Wildcards
Scenario
A famous company in Japan supplies .
Customers can log into the company's website and order devices. The stock is managed on a database on the company's server.
Here is a sample table of data in that databse, tblStock:
What's the Problem?
When customers search for peripheral devices, sql queries are run on the server.
What if the customer wants to search for all devices manufactured by Logitech?
A query run on the server could be:
SELECT * from tblStock WHERE 'Description' = "Logitech Mouse M220" OR 'Description' = "Logitech Keyboard MX" OR 'Description' = "Logitech Keyboard Ergo" OR 'Description' = "Logitech Keyboard K600" OR 'Description' = "Logitech Mevo Webcam" OR 'Description' = "Logitech Stream Webcam" OR 'Description' = "Logitech C922 Webcam"
This is a bit awkward.
What if a new Logitech device is added to the stock? We would need to rewrite the query which is a little inconvenient.
We can solve this problem by using WILDCARDS!
A wildcard character is used to substitute one or more characters in a string.
For example, in the Description field in the above table, if we are looking for any Description that contains the word Logitech then we can write this query:
SELECT * from tblStock WHERE 'Description' LIKE "Logitech%" ORDER BY 'Description'
% is a wildcard character representing all text that will appear after the word Logitech.
Instead of using =, use when using wildcards.
So this query will select all records where the Description field starts with the word Logitech.
If we wanted to select all records where the word Logitech is at the end of the string, we could write this query:
SELECT * from tblStock WHERE 'Description' LIKE "%Logitech" ORDER BY 'Description'
What if we want to query records that have the word Logitech somewhere in the string, but not necessarily at the beginning or end?
Task 1 - Import
Download the csv file about peripheral devices. Save it in a project folder.
Import the data into a XAMPP database table.
Remember to ask yourself a couple of questions:
- Does the first row contain field names?
- Is there a field which makes every record unique? If so, set it to be the Primary Key field. If not, add a new field called id which auto-increments.
Challenge 1 - More complex queries
Design-Test-Redesign the following queries.
Hint! Your queries should produce the same number of records as indicated.
| select all records about monitors | 3 Records |
| select all records about keyboards cheaper than $50. | 1 record |
| select all records which do not relate to Logitech devices and cost between $50 and $100 inclusive. The result is sorted in order of price. | 2 records |
Note: the third query may require some research. Can you figure it out?
Paste a screenshot of the query in the worksheet document where indicated.
Challenge 2 - A business letter
You are Head of Sales at NatsuBashi Corp.
A letter needs to be sent to one of the clients. It will list in a table the results of the 3rd query.
The text to include in the letter is given below.
Also, here is the information about the client:
| Name | Address |
|---|---|
| Mr Jacob Morley | 123 West Lane Chicago CH3 1AE |
Here is the letter specification
- Your name, position and address at the top right corner of the letter.
- Today's date under your address
- On the next line, the client's name and address on the left side of the letter
- On the next line, a salutation eg Dear Mr Morley,
- body text (copy/paste from below): 1.5 line spacing, justified alignment
- Closing phrase, eg Yours sincerely,
- Print your name and add you signature underneath it
Here is the body text to include in the document.
Submission
Submit your letter in pdf format along with your worksheet.
Food For Thought
And and Or are magical words in Database Technology. They allow you to create more complex queries. Experience will teach you how to use them properly.
