CITY表：

1、Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.

select * from CITY
where POPULATION>=100000 and COUNTRYCODE=\'USA\';

2、Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.

select Name from CITY
where COUNTRYCODE =\'USA\' and POPULATION>=120000;

3、Query all columns (attributes) for every row in the CITY table.

select * from CITY;

 4、Query all columns for a city in CITY with the ID 1661.

select * from CITY
where ID =1661;

5、Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.

select * from CITY
where COUNTRYCODE=\'JPN\';

6、Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.

select NAME
from CITY
where COUNTRYCODE=\'JPN\'

STATION表：

1、Query a list of CITY and STATE from the STATION table.

select CITY,STATE
from STATION;

2、Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.

从 STATION 查询具有偶数 ID 号的城市的 CITY 名称列表。 以任意顺序打印结果，但从答案中排除重复项。

select DISTINCT(CITY)
from STATION
where mod(ID,2)=0;

3、Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

求表中 CITY 条目总数与表中不同 CITY 条目数之间的差值。

select COUNT(CITY)-COUNT(DISTINCT(CITY))
from STATION;

4、Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

用最短和最长的 CITY 名称查询 STATION 中的两个城市，以及它们各自的长度（即：名称中的字符数）。 如果有多个最小或最大的城市，请选择按字母顺序排列的第一个城市。

方法1: where子句+limit

select CITY,length(CITY) from STATION
where length(CITY)>=ALL(select length(CITY) from STATION) or length(CITY)<=ALL(select length(CITY) from STATION)
order by 2 desc,1
limit 2;

解题思路：

方法2:union+limit

(select CITY,length(CITY) from STATION
order by 2 desc,1 desc
limit 1)
union
(select CITY,length(CITY) from STATION
order by 2,1
limit 1);

解题思路：两个表组合。

5、Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

从 STATION 查询以元音开头的 CITY 名称列表（即 a、e、i、o 或 u）。 您的结果不能包含重复项。

select DISTINCT(CITY)
from STATION
where CITY LIKE\'a%\' or CITY LIKE\'e%\' or CITY LIKE\'i%\' or CITY LIKE\'o%\' or CITY LIKE\'u%\';

6、Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

从 STATION 查询以元音结尾的 CITY 名称列表（即 a、e、i、o 或 u）。 您的结果不能包含重复项。

select DISTINCT(CITY)
from STATION
where CITY LIKE \'%a\' or CITY LIKE \'%e\' or CITY LIKE \'%i\' or CITY LIKE \'%o\' or CITY LIKE \'%u\';

7、Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

从 STATION 查询以元音开头和结尾的 CITY 名称列表（即 a、e、i、o 或 u）。 您的结果不能包含重复项。

select DISTINCT(CITY)
from STATION
where (CITY LIKE \'%a\' or CITY LIKE \'%e\' or CITY LIKE \'%i\' or CITY LIKE \'%o\' or CITY LIKE \'%u\') and (CITY LIKE\'a%\' or CITY LIKE\'e%\' or CITY LIKE\'i%\' or CITY LIKE\'o%\' or CITY LIKE\'u%\');

8、Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

从 STATION 查询不能以元音开头的 CITY 名称列表（即 a、e、i、o 或 u）。 您的结果不能包含重复项。

select DISTINCT(CITY)
from STATION
where CITY NOT LIKE \'a%\' and CITY NOT LIKE \'e%\' and CITY NOT LIKE \'i%\' and CITY NOT LIKE \'o%\' and CITY NOT LIKE \'u%\';

9、Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

从 STATION 查询不能以元音结尾的 CITY 名称列表（即 a、e、i、o 或 u）。 您的结果不能包含重复项。

select DISTINCT(CITY)
from STATION
where CITY NOT LIKE \'%a\' and CITY NOT LIKE \'%e\' and CITY NOT LIKE \'%i\' and CITY NOT LIKE \'%o\' and CITY NOT LIKE \'%u\';

10、Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

从 STATION 查询不以元音开头或不以元音结尾的 CITY 名称列表。 您的结果不能包含重复项。

select DISTINCT(CITY)
from STATION
where (CITY NOT LIKE \'a%\' and CITY NOT LIKE \'e%\' and CITY NOT LIKE \'i%\' and CITY NOT LIKE \'o%\' and CITY NOT LIKE \'u%\') or (CITY NOT LIKE \'%a\' and CITY NOT LIKE \'%e\' and CITY NOT LIKE \'%i\' and CITY NOT LIKE \'%o\' and CITY NOT LIKE \'%u\');

11、Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.

从 STATION 查询不以元音开头和不以元音结尾的 CITY 名称列表。 您的结果不能包含重复项。

select DISTINCT(CITY)
from STATION
where (CITY NOT LIKE \'a%\' and CITY NOT LIKE \'e%\' and CITY NOT LIKE \'i%\' and CITY NOT LIKE \'o%\' and CITY NOT LIKE \'u%\') and (CITY NOT LIKE \'%a\' and CITY NOT LIKE \'%e\' and CITY NOT LIKE \'%i\' and CITY NOT LIKE \'%o\' and CITY NOT LIKE \'%u\');

STUDENTS表：

1、Query the Name of any student in STUDENTS who scored higher than  Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

查询 STUDENTS 中任何得分高于 75 的学生的姓名。 按每个名称的最后三个字符对输出进行排序。 如果两个或多个学生的名字都以相同的最后三个字符结尾（即：Bobby、Robby 等），则按 ID 升序对他们进行二次排序。

select Name from STUDENTS
where Marks>75
order by right(Name,3),ID;

解题思路，按照每个名字的最后三个字符排序，order by 和right函数结合使用。

Employee表：

1、Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.

select name 
from Employee
order by name;

2、

Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than  per month who have been employees for less than  months. Sort your result by ascending employee_id.

编写一个查询，打印员工姓名列表（即：名称属性），用于 Employee 中薪水大于每月且工作时间少于几个月的员工。 按employee_id 升序对结果进行排序。

select name
from Employee
where salary>2000 and months<10
order by employee_id;

来源：https://www.cnblogs.com/ruoli-121288/p/16335207.html
本站部分图文来源于网络，如有侵权请联系删除。