Sqlzoo Practice Answer--select Names/zh

name	Continent
Afghanistan	Asia
Albania	Europe
Algeria	Africa
Andorra	Europe
Angola	Africa
....

Name: Country name
Continent: Chau

1.

You can use it to WHERE name LIKE ‘B%‘ find the country that started with B.
Percent is the million character, can be used to represent no matter what word ends.

Find the country that started with Y.

SELECT name from World  WHERE name like ' Y% '

2. find the country that ends with Y.

SELECT name from World  WHERE name like '%Y '

3.

There is an X-letter in Luxembourg, and there is an X in the name of a country. List these two nations.

Find all the countries whose names contain the letter x.

SELECT name from World  WHERE name like '%x% '

4.

The names of "Iceland Ice Island" and "Switzerland Switzerland" are all ended with "land". Is there anything else?

Find all nations, whose names end with land.

SELECT name from World  WHERE name like '%land '

5.

"Columbia" is the beginning of C, IA for the end. And two countries, too.

Find all the countries whose names begin with C and IA end.

SELECT name from World  WHERE name like ' C%ia '

6.

"Greece Greek roast" has a double e word. Which country has a double o word?

Find all nations whose names contain the Alphabet Oo.

SELECT name from World  WHERE name like '%oo% '

7.

There are three aces in the Bahamas Baha ' I, are there?

Find all countries whose names contain three or more of a.

SELECT name from World  WHERE name like '%a%a%a% '

8.

The second letter of "India India" and "Angola Angola" is N.
You can use the bottom line character _ when a single letter of the million characters.

SELECT  from WHERE  like ' _n% ' ORDER  by name

Find all nations, whose names are the second letter of T.

SELECT name from World WHERE name "_t%" ORDER by name

9.

"Lesotho" and "Moldova" have two letters O, separated by the other two letters.

Find all the countries whose names have two letters O, separated by the other two letters.

SELECT name from World WHERE name like '%o__o% '

10.

"Cuba" and "Togo Togo" are all 4 letters.

Find all the nations, whose names are 4 letters.

SELECT name from World WHERE name like ' ____ '

11.

The capital of "Luxembourg Fort" is called "Luxembourg".

All the names of the countries, the capitals and the names of the nations are the same.

SELECT name from world WHERE name = Concat ("capital,")

12.

The capital of "Mexico Mexico" is "Mexico City".

It shows all the names of the country, its capital and the name of the country plus "City".

SELECT name from world WHERE Capital = concat (name, ' City ')

13. find out the names of all capitals and their countries, and the capital will have a country name.

Select Capital,name from world where capital like concat ('% ', name, '% ')

14. find out the names of all capitals and their nations, and the capital is an extension of the name of the country.

You should show Mexico city because it is Mexico than the name of the country.
You should not show Luxembourg, because its capital and country name are the same.

Select Name,capital from world where capital like concat ('% ', name, '% ') and capital! = Name

15.

"Monaco-ville" is the name of "Monaco" and the extension "-ville" in the merged country.

The name of the country, and its extension, such as the capital is the extension of the name of the country.

You can use SQL function REPLACE or MID.

Select Name,replace (capital, Name, ") from the world where capital like Concat (name, '%_ ')

Sqlzoo Practice Answer--select Names/zh