HackerRank第一趴--Basic Select

阅读原文时间：2023年07月10日阅读：2

CITY表：

Field

Type

number

NAME

VARCHAR2（17）

COUNTRYCODE

VARCHAR2（3）

DISTRICT

VARCHAR2（20）

POPULATION

number

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';

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;

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

select * from CITY;

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

select * from CITY
where ID =1661;

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

select * from CITY
where COUNTRYCODE='JPN';

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'

select count(NAME)
from CITY
where POPULATION>100000;

select sum(POPULATION)
from CITY
where DISTRICT ='California';

select avg(POPULATION)
from CITY
where DISTRICT='California';

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

11.Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.

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

12.Query the difference between the maximum and minimum populations in CITY.

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

13.Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

select sum(c.POPULATION) from CITY c
left join COUNTRY c1
on c.COUNTRYCODE=c1.CODE
where c1.CONTINENT ='Asia';

14.Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

select c.NAME
from CITY c
left join COUNTRY c1
on c.COUNTRYCODE=c1.CODE
where c1.CONTINENT='Africa';

15.

STUDENTS表：

Field

Type

INTEGER

Name

String

Maks

INTEGER

查询 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

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;

3.We define an employee's total earnings to be their monthly salary*months worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as 2 space-separated integers.

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

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

------------恢复内容开始------------

CITY表：

Field

Type

number

NAME

VARCHAR2（17）

COUNTRYCODE

VARCHAR2（3）

DISTRICT

VARCHAR2（20）

POPULATION

number

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

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

select * from CITY;

select * from CITY
where ID =1661;

select * from CITY
where COUNTRYCODE='JPN';

select NAME
from CITY
where COUNTRYCODE='JPN'

select count(NAME)
from CITY
where POPULATION>100000;

select sum(POPULATION)
from CITY
where DISTRICT ='California';

select avg(POPULATION)
from CITY
where DISTRICT='California';

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

11.Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.

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

12.Query the difference between the maximum and minimum populations in CITY.

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

13.Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

select sum(c.POPULATION) from CITY c
left join COUNTRY c1
on c.COUNTRYCODE=c1.CODE
where c1.CONTINENT ='Asia';

14.Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

select c.NAME
from CITY c
left join COUNTRY c1
on c.COUNTRYCODE=c1.CODE
where c1.CONTINENT='Africa';

15.Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

给定 CITY 和 COUNTRY 表，查询所有大洲的名称 (COUNTRY.Continent) 及其各自的平均城市人口 (CITY.Population)，四舍五入到最接近的整数。

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

select c.Continent,FLOOR(avg(c1.population))
from COUNTRY c
join CITY c1
on c.CODE=c1.CountryCode
group by 1;

STATION表：

Field

Type

number

CITY

VARCHAR2（21）

STATE

VARCHAR2（2）

LAT_N

number

LONG_W

number

select CITY,STATE
from STATION;

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

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

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

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

用最短和最长的 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);

解题思路：两个表组合。

从 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%';

从 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';

从 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%');

从 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%';

从 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';

从 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');

从 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 of Northern Latitudes (LAT_N) from STATION having values greater than 38.7880 and less than 137.2345. Truncate your answer to 4 decimal 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 the Northern Latitudes (LAT_N) from STATION that is less than137.2345 . Truncate your answer to 4 decimal 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 smallest Northern Latitude (LAT_N) from STATION that is greater than 38.7880. Round your answer to 4 decimal places.

select ROUND(LAT_N,4)
from STATION
where LAT_N>38.7880
order by 1
limit 1;

17.

Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7880. Round your answer to decimal places.

select ROUND(LONG_W,4)
from STATION
where LAT_N>38.7880
order by LAT_N
limit 1;

Consider p1(a,b) and p2(c,d) to be two points on a 2D plane.

a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
b happens to equal the minimum value in Western Longitude (LONG_W in STATION).
c happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
d happens to equal the maximum value in Western Longitude (LONG_W in STATION).

Query the Manhattan Distance between points and and round it to a scale of 4 decimal places.

select ROUND(max(LAT_N)-min(LAT_N)+max(LONG_W)-min(LONG_W),4)
from STATION;

Consider p1 and p2 to be two points on a 2D plane where a,c are the respective minimum and maximum values of Northern Latitude (LAT_N) and b,d are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION.

将 p1 和 p2 视为 2D 平面上的两个点，其中 a,c 是北纬 (LAT_N) 的相应最小值和最大值，b,d 是 STATION 中西经 (LONG_W) 的相应最小值和最大值。

Query the following two values from the STATION table:

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

2.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;

19.

STUDENTS表：

Field

Type

INTEGER

Name

String

Maks

INTEGER

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

select name
from Employee
order by name;

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

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

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

------------恢复内容结束------------

手机扫一扫

移动阅读更方便

你可能感兴趣的文章