HackerRank的Basic Select部分有哪些基础查询技巧？

本文共计2249个文字，预计阅读时间需要9分钟。

CITY表：字段类型ID- Field: VARCHAR2(17)- Type: VARCHAR2(3)- IDnumber: VARCHAR2(17)- NAME: VARCHAR2(17)

查询条件：- 国家代码为美国- 人口大于100000

查询结果示例：SELECT * FROM CITYWHERE COUNTRYCODE='USA' AND POPULATION > 100000;

CITY表： Field Type ID number NAME VARCHAR2（17） COUNTRYCODE VARCHAR2（3） DISTRICT VARCHAR2（20） POPULATION number

1.Query all columns for all American cities in theCITYtable with populations larger than100000. TheCountryCodefor America isUSA.

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

2.Query theNAMEfield for all American cities in theCITYtable with populations larger than120000. TheCountryCodefor America isUSA.

select Name from CITY where COUNTRYCODE ='USA' and POPULATION>=120000; 3.Query all columns (attributes) for every row in theCITYtable.

select * from CITY; 4.Query all columns for a city inCITYwith theID1661.

select * from CITY where ID =1661;

5.Query all attributes of every Japanese city in theCITYtable. TheCOUNTRYCODEfor Japan isJPN.

select * from CITY where COUNTRYCODE='JPN'; 6.Query the names of all the Japanese cities in theCITYtable. TheCOUNTRYCODEfor Japan isJPN.

select NAME from CITY where COUNTRYCODE='JPN' 7.Query acountof the number of cities inCITYhaving aPopulationlarger than 10,000.

select count(NAME) from CITY where POPULATION>100000; 8.Query the total population of all cities inCITYwhereDistrictisCalifornia.

select sum(POPULATION) from CITY where DISTRICT ='California'; 9.Query the average population of all cities inCITYwhereDistrictisCalifornia.

select avg(POPULATION) from CITY where DISTRICT='California'; 10.Query the average population for all cities inCITY, roundeddownto the nearest integer.

select ROUND(avg(POPULATION),0) from CITY;

11.Query the sum of the populations for all Japanese cities inCITY. TheCOUNTRYCODEfor Japan isJPN.

select sum(POPULATION) from CITY where COUNTRYCODE='JPN';

12.Query the difference between the maximum and minimum populations inCITY.

select max(POPULATION)-min(POPULATION) from CITY;

13.

STATION表：

Field Type ID number CITY VARCHAR2（21） STATE VARCHAR2（2） LAT_N number LONG_W number 1.Query a list ofCITYandSTATEfrom theSTATIONtable.

select CITY,STATE from STATION; 2.Query a list ofCITYnames fromSTATIONfor cities that have an evenIDnumber. 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 ofCITYentries in the table and the number of distinctCITYentries in the table.

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

select COUNT(CITY)-COUNT(DISTINCT(CITY)) from STATION; 4.Query the two cities inSTATIONwith the shortest and longestCITYnames, 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 ofCITYnames starting with vowels (i.e.,a,e,i,o, oru) fromSTATION. Your resultcannotcontain 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 ofCITYnames ending with vowels (a, e, i, o, u) fromSTATION. Your resultcannotcontain 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 ofCITYnames fromSTATIONwhich have vowels (i.e.,a,e,i,o, andu) as both their firstandlast 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 ofCITYnames fromSTATIONthatdo not startwith 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 ofCITYnames fromSTATIONthatdo not endwith 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 ofCITYnames fromSTATIONthat 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 ofCITYnames fromSTATIONthatdo not startwith vowels anddo not endwith 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');

12.Query the following two values from the STATION table:

The sum of all values in LAT_N rounded to a scale of 2 decimal places.
The sum of all values in LONG_W rounded to a scale of 2 decimal places.

select ROUND(sum(LAT_N),2),ROUND(sum(LONG_W),2) from STATION;

13.Query the sum ofNorthern Latitudes(LAT_N) fromSTATIONhaving values greater than 38.7880and less than137.2345. Truncate your answer to 4decimal places.

select ROUND(sum(LAT_N),4) from STATION where LAT_N>38.7880 and LAT_N<137.2345;

14.Query the greatest value of theNorthern Latitudes(LAT_N) fromSTATIONthat is less than137.2345. Truncate your answer to 4decimal places.

select ROUND(max(LAT_N),4) from STATION where LAT_N<137.2345;

15.Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.3245. Round your answer to 4 decimal places.

select ROUND(LONG_W,4) from STATION where LAT_N<137.2345 order by LAT_N desc limit 1;

16.Query the smallestNorthern Latitude(LAT_N) fromSTATIONthat is greater than 38.7880. Round your answer to 4decimal places.

STUDENTS表： Field Type ID INTEGER Name String Maks INTEGER 1.Query theNameof any student inSTUDENTSwho scored higher thanMarks. Order your output by thelast three charactersof 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 ascendingID.

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

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

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

Employee表： Field Type employee_id INTEGER name String months INTEGER salary INTEGER

1.Write a query that prints a list of employee names (i.e.: thenameattribute) from theEmployeetable in alphabetical order.

select name from Employee order by name;

2.Write a query that prints a list of employee names (i.e.: thenameattribute) for employees inEmployeehaving a salary greater thanper month who have been employees for less thanmonths. Sort your result by ascendingemployee_id.

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

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

3.We define an employee'stotal earningsto be their monthly salary*monthsworked, and themaximum total earningsto be the maximum total earnings for any employee in theEmployeetable. Write a query to find themaximum total earningsfor all employees as well as the total number of employees who have maximum total earnings. Then print these values as2 space-separated integers.

我们将员工的总收入定义为他们的月薪*工作月数，最大总收入定义为 Employee 表中任何员工的最大总收入。 编写查询以查找所有员工的最大总收入以及拥有最大总收入的员工总数。 然后将这些值打印为 2 个空格分隔的整数。

select salary*months as earning,count(name) from Employee group by earning order by earning desc limit 1;

4.