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 itWHERE name LIKE ‘B%‘to find the country that started with B.
Percent is the million character, can be used to represent any word end.

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 include 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" was started with C, and IA was the end. There are two countries in the same country.

Find all the countries whose names start with C and IA ends.

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 the countries whose names include 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 include 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  name from  World where  name 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 nations whose names have two letters O, separated by two other 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 national names are displayed, and their capitals and national names are the same.

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

12.

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

The names of all the countries, their capitals and the name of the country plus "city" are displayed.

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