SQL语言学习-SQLZOO练习笔记

2023-01-30 333 阅读3分钟

此文章为SQLZOO网站学习记录，将随学习进度不断更新。在此给出一些题目的个人解答，同时将逐步给出英文题目和参考资料的翻译。

0 SELECT basics

Some simple queries to get you started

一些简单的查询可以带你开始SQL的学习。

World

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
....

Introducing the world table of countries

引入世界国家表

The example uses a WHERE clause to show the population of 'France'. Note that strings (pieces of text that are data) should be in 'single quotes';

这个示例使用了一条WHERE语句来显示法国（France）的人口（population）。注意字符串要放在单引号内。
```
SELECT population FROM world
	WHERE name = 'Germany'
```
Modify it to show the population of Germany

修改示例代码使其显示德国（Germany）的人口
```
SELECT population FROM world
	WHERE name = 'France'
```

1 SELECT name

Some pattern matching queries

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
```
SELECT name FROM world
	WHERE name LIKE 'Y%'
```

Find the countries that end with y

SELECT name FROM world
	WHERE name LIKE '%y'

Luxembourg has an x - so does one other country. List them both.

Find the countries that contain the letter x
```
SELECT name FROM world
	WHERE name LIKE '%x%'
```
Iceland, Switzerland end with land - but are there others?

Find the countries that end with land
```
SELECT name FROM world
	WHERE name LIKE '%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
```
SELECT name FROM world
	WHERE name LIKE 'C%ia'
```
Greece has a double e - who has a double o?

Find the country that has oo in the name
```
SELECT name FROM world
	WHERE name LIKE '%oo%'
```
Bahamas has three a - who else?

Find the countries that have three or more a in the name
```
SELECT name FROM world
	WHERE name LIKE '%a%a%a%'
```
India and Angola have an n as the second character. You can use the underscore as a single character wildcard.
```
SELECT name FROM world
	WHERE name LIKE '_n%'
	ORDER BY name
```
Find the countries that have "t" as the second character.
```
SELECT name FROM world
	WHERE name LIKE '_t%'
	ORDER BY name
```
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.
```
SELECT name FROM world
	WHERE name LIKE '%o__o%'
```
Cuba and Togo have four characters names.

Find the countries that have exactly four characters.

SELECT name FROM world
	WHERE name LIKE '____'

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.
```
SELECT name FROM world
	WHERE name LIKE 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 FROM world
	WHERE capital LIKE 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, '%') AND capital <> name
```
For Monaco-Ville the name is Monaco and the extension is -Ville.

Show the name and the extension where the capital is an 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, '%') AND capital > name
```