Schema and sample data
| customer_id | name | city | signup_date |
|---|---|---|---|
| 1 | Amelia Clarke | London | 2023-01-14 |
| 2 | Bruno Adeyemi | Manchester | 2023-02-02 |
| 3 | Chloe Watson | London | 2023-03-19 |
| 4 | Diego Ramirez | Bristol | 2023-04-08 |
| 5 | Ewan MacLeod | London | 2023-05-27 |
| 6 | Farah Hassan | Leeds | 2023-06-11 |
Show setup SQL (copy to run)
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50),
signup_date DATE
);
INSERT INTO Customers (customer_id, name, city, signup_date) VALUES
(1, 'Amelia Clarke', 'London', '2023-01-14'),
(2, 'Bruno Adeyemi', 'Manchester', '2023-02-02'),
(3, 'Chloe Watson', 'London', '2023-03-19'),
(4, 'Diego Ramirez', 'Bristol', '2023-04-08'),
(5, 'Ewan MacLeod', 'London', '2023-05-27'),
(6, 'Farah Hassan', 'Leeds', '2023-06-11');From the Customers table, return the name and city of every customer who lives in the city London. Sort the result by name in ascending (A to Z) order.
Expected result
| name | city |
|---|---|
| Amelia Clarke | London |
| Chloe Watson | London |
| Ewan MacLeod | London |
Show hint
You only need a single WHERE clause that tests city for equality against the text value, then an ORDER BY on name. Wrap the city text in straight single quotes.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT name, city
FROM Customers
WHERE city = 'London'
ORDER BY name;The WHERE city = 'London' filter keeps only the rows whose city exactly matches the text London. Three customers qualify: Amelia Clarke, Chloe Watson, and Ewan MacLeod. The other three live in Manchester, Bristol, and Leeds, so they are removed before anything is returned.
ORDER BY name then sorts the surviving rows alphabetically. The default direction is ascending, so name and name ASC mean the same thing here; you would only add DESC to reverse it. The result is Amelia, then Chloe, then Ewan.
One subtlety: whether the equality is case sensitive depends on the column collation, not the query. On a typical case insensitive collation (the SQL Server default, such as SQL_Latin1_General_CP1_CI_AS, where CI means case insensitive), 'London', 'london', and 'LONDON' all match. Under a case sensitive collation (a CS suffix) only an exact case match is returned. If you need to force one behaviour regardless of the column setting, apply COLLATE, for example WHERE city = 'London' COLLATE Latin1_General_CS_AS. A common mistake is using LIKE with wildcards for what is really an exact match; a plain = is clearer and lets an index on city help.