Sqlzoo exercise answer -- SELECTnames/zh

Sqlzoo exercise answer -- SELECTnames/zh

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

Name: Country name
Continent: continent

1,

You can use WHERE name LIKE 'B %' to find the country that begins with B.
% Is a token character, which can be used to represent any word.

Find the country that begins with Y.

SELECT name FROM world  WHERE name LIKE 'Y%'

2, Find the country ending with Y.

SELECT name FROM world  WHERE name LIKE '%Y'

3,

There is an x letter in "Luxembourg" and a country name contains x. List the two countries.

Find all countries whose names include letters x.

SELECT name FROM world  WHERE name LIKE '%x%'

4,

"Iceland ice cream" and "Switzerland" are both named "land. Is there anything else?

Find all countries whose names end with land.

SELECT name FROM world  WHERE name LIKE '%land'

5,

Columbia is started with C and ended with ia. Two other countries share the same status.

Find all countries whose names start with C and end with ia.

SELECT name FROM world  WHERE name LIKE 'C%ia'

6,

"Greece Greek" contains the letter e. Which country has the o word?

Find all countries whose names include the letter oo.

SELECT name FROM world  WHERE name LIKE '%oo%'

7,

There are three a members in "Bahamas bahamma", are there?

Find out all countries whose names include three or more.

SELECT name FROM world  WHERE name LIKE '%a%a%a%'

8,

The second letter of "India" and "Angola" is n.
You can use the base delimiter_A single character.

SELECT name FROM world WHERE name LIKE '_n%'ORDER BY name

Find all countries whose names are t as the second letter.

SELECT name FROM world WHERE name LIKE '_t%'ORDER BY name

9,

"Lesotho yaoyun" and "Moldova modemova" both have two letters, o, separated by the other two letters.

Find out all countries whose names are separated by two letters.

SELECT name FROM world WHERE name LIKE '%o__o%'

10,

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

Find out all countries whose names are 4 letters.

SELECT name FROM world WHERE name LIKE '____'

11,

Capital in the capital of "Luxembourg" is also called "Luxembourg ".

It indicates the names of all countries. The names of the capital and country are the same.

SELECT name FROM world WHERE name = concat(capital, '')

12,

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

Shows the names of all countries, including the capital and City ".

SELECT name FROM world WHERE capital = concat(name, ' City')

13. Find out the names of all the capitals and their countries, and the names of the capitals should appear.

select capital,name from world where capital Like concat('%',name,'%')

14. Find out all the capitals and their country names, and the capital is an extension of the national name.
You should show the City of Mico because it is longer than its country name Mico.
You should not display 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 the United States "Monaco" and the extension word "-Ville ".

Shows the name of a country and its extension words. For example, the capital represents an extension of the name of a country.

You can use SQL function REPLACE or MID.

select name,replace(capital, name, '') from world where capital Like concat(name,'%_')