SELECT names
| Language: Project:Language policy | English • 日本語 • 中文 |
|---|
| name | continent |
|---|---|
| Afghanistan | Asia |
| Albania | Europe |
| Algeria | Africa |
| Andorra | Europe |
| Angola | Africa |
| .... | |
Pattern Matching Strings
This tutorial uses the LIKE operator to check names. We will be using the SELECT command on the table world:
You can use WHERE name LIKE 'B%' to find the countries that start with "B".
- The % is a wild-card it can match any characters
Find the country that start with Y
SELECTnameFROMworld WHEREnameLIKE'F%'
SELECTnameFROMworld WHEREnameLIKE'Y%'
Find the countries that end with y
SELECTnameFROMworld WHEREnameLIKE'T%'
SELECTnameFROMworld WHEREnameLIKE'%y'
Luxembourg has an x - so does one other country. List them both.
Find the countries that contain the letter x
SELECTnameFROMworld WHEREnameLIKE'T%'
SELECTnameFROMworld WHEREnameLIKE'%x%'
Iceland, Switzerland end with land - but are there others?
Find the countries that end with land
SELECTnameFROMworld WHEREnameLIKE'T%'
SELECTnameFROMworld WHEREnameLIKE'%land'
Columbia starts with a C and ends with ia - there are two more like this.
Find the countries that start with C and end with ia
SELECTnameFROMworld WHEREnameLIKE'T%'
SELECTnameFROMworld WHEREnameLIKE'C%ia'
Greece has a double e - who has a double o?
Find the country that has oo in the name
SELECTnameFROMworld WHEREnameLIKE'%ee%'
SELECTnameFROMworld WHEREnameLIKE'%oo%'
Bahamas has three a - who else?
Find the countries that have three or more a in the name
SELECTnameFROMworld WHEREnameLIKE'T%'
SELECTnameFROMworld WHEREnameLIKE'%a%a%a%'
India and Angola have an n as the second character. You can use the underscore as a single character wildcard.
SELECTnameFROMworld WHEREnameLIKE'_n%' ORDERBYname
Find the countries that have "t" as the second character.
SELECTnameFROMworld WHEREnameLIKE'_n%' ORDERBYname
SELECTnameFROMworld WHEREnameLIKE'_t%' ORDERBYname
Lesotho and Moldova both have two o characters separated by two other characters.
Find the countries that have two "o" characters separated by two others.
SELECTnameFROMworld WHEREnameLIKE'_n%'
SELECTnameFROMworld WHEREnameLIKE'%o__o%'
Cuba and Togo have four characters names.
Find the countries that have exactly four characters.
SELECTnameFROMworld WHEREnameLIKE'Cu%'
SELECTnameFROMworld WHEREnameLIKE'____'
Play Find the Duplicates
Harder Questions
Well done for getting this far.
The next questions are optional and only for students who are finding the basic questions too easy.
The capital of Luxembourg is Luxembourg. Show all the countries where the capital is the same as the name of the country
Find the country where the name is the capital city.
SELECTname,capital,continent FROMworld WHEREnameLIKE'%x%'
SELECTnameFROMworld WHEREname=capital
The capital of Mexico is Mexico City. Show all the countries where the capital has the country together with the word "City".
Find the country where the capital is the country plus "City".
The function concat is short for concatenate - you can use it to combine two or more strings.
SELECT name, concat(name, 'town') FROM world WHERE name LIKE '%ina%'
SELECT name FROM world WHERE capital = concat(name, ' City')
Find the capital and the name where the capital includes the name of the country.
SELECT capital, name FROM world
WHERE capital LIKE concat('%', name, '%')Find the capital and the name where the capital is an extension of name of the country.
You should include Mexico City as it is longer than Mexico. You should not include Luxembourg as the capital is the same as the country.
SELECT capital,name FROM world WHERE capital LIKE concat(name, '_%')
The capital of Monaco is Monaco-Ville: this is the name Monaco and the extension is -Ville.
Show the name and the extension where the capital is a proper (non-empty) extension of name of the country.
You can use the SQL function REPLACE.
select name, replace(capital,name,'') from world where capital LIKE concat(name,'_%')