Customers who live in Delhi

Easy

Question

The customers table has a city that people typed by hand. Some values use different capital letters (DELHI, delhi) and some have extra spaces at the start or end. All of these mean the city Delhi.

Other cities have names that contain the word Delhi, such as New Delhi and Delhi NCR. They are different places in this report and must not be returned. A spelling mistake such as Dehli is also not Delhi: do not try to guess what the customer meant. A customer with no city (NULL) is not returned either.

Return id and name of every customer who lives in Delhi. Sort by id from low to high.

The order of the rows matters for this question.

string
like
trim
lower
exact match
where
null

Read first: Lesson 42, SQL basics: SELECT, WHERE, ORDER BY and LIMIT

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

customers3 columns · 11 rowsnot run yet

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT);

Example rows of the customers table
id	name	city
1	Asha	Delhi
2	Ravi	delhi
3	Meena	DELHI
4	Sam	New Delhi
5	Tara	Delhi
6	Umesh	Mumbai
7	Vani	Delhi NCR
8	Wasim	NULL
9	Kabir	Dehli
10	Leela	Delhi Cantt
11	Nikhil	dELHI

About this SQL

In SQLite, = on text is case-sensitive ('Delhi' = 'delhi' is false), but LIKE ignores case for plain English letters. LIKE without % or _ in the pattern is an exact match that ignores case, and a space at the end still counts as a character. In MySQL both = and LIKE usually ignore case, and in Postgres neither does (it has ILIKE). A % in a LIKE pattern means any number of characters, including none.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready