hackerrank-SQL语句练习(Basic Select)

2017-05-25 留言

SQL语句练习

hackerrank是一个在线OJ平台，提供了在线练习编程技术的环境和问题。
发现有SQL语句的练习，就拿来练习下:), 使用MySQL语法。

Revising the Select Query I(https://www.hackerrank.com/challenges/revising-the-select-query)

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

简单的where子句查询

1
2
3
4
5
6
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION
    from city 
        where COUNTRYCODE='USA' 
                and POPULATION > 100000;

Revising the Select Query II(https://www.hackerrank.com/challenges/revising-the-select-query-2)

Query the names of all American cities in CITY with populations larger than 120000. The CountryCode for America is USA.

简单的where子句查询

1
2
3
4
5
6
select NAME
    from CITY 
        where COUNTRYCODE='USA' 
                and POPULATION > 120000;

Select All(https://www.hackerrank.com/challenges/select-all-sql)

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

简单的select子句查询

1
2
3
4
select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION
    FROM CITY;

Select By ID(https://www.hackerrank.com/challenges/select-by-id)

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

简单的where子句查询

1
2
3
4
5
select * 
    from city
        where id=1661;

Japanese Cities’ Attributes(https://www.hackerrank.com/challenges/japanese-cities-attributes)

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

简单的where子句查询

1
2
3
4
5
select *
    from CITY
        where COUNTRYCODE='JPN';

Japanese Cities’ Names(https://www.hackerrank.com/challenges/japanese-cities-name)

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

简单的where子句查询

1
2
3
4
5
select NAME
    from city
        where COUNTRYCODE='JPN';

Weather Observation Station 1(https://www.hackerrank.com/challenges/weather-observation-station-1)

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

简单的SQL语句

1
2
3
4
select CITY,STATE
    from STATION;

Weather Observation Station 3(https://www.hackerrank.com/challenges/weather-observation-station-3)

Query a list of CITY names from STATION with even ID numbers only. You may print the results in any order, but must exclude duplicates from your answer.

使用distinct去重

1
2
3
4
5
6
select DISTINCT CITY
    from STATION
         where ID % 2 = 0
            order by CITY;

Weather Observation Station 4(https://www.hackerrank.com/challenges/weather-observation-station-4)

Let be the number of CITY entries in STATION, and let be the number of distinct CITY names in STATION; query the value of from STATION. In other words, find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

还是distinct去重

1
2
3
4
select count(CITY) - count(DISTINCT CITY)
    from STATION ;

Weather Observation Station 5(https://www.hackerrank.com/challenges/weather-observation-station-5)

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.

可以写成两句SQL

1
2
3
4
5
6
7
8
9
select city, length(city)
    from STATION 
        where length(city) = (select min(length(city)) from STATION) order by city limit 1;
select city, length(city)
    from STATION 
        where length(city) = (select max(length(city)) from STATION) order by city limit 1;

Weather Observation Station 6(https://www.hackerrank.com/challenges/weather-observation-station-6)

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

简单的where子句查询,使用left函数

1
2
3
4
5
select distinct city
    from STATION 
        where left(city, 1) in ('a','e','i','o','u');

Weather Observation Station 7(https://www.hackerrank.com/challenges/weather-observation-station-7)

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

简单的where子句查询，使用right函数

1
2
3
4
5
select distinct city
    from STATION 
        where right(city, 1) in ('a','e','i','o','u');

Weather Observation Station 8(https://www.hackerrank.com/challenges/weather-observation-station-8)

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.

1
2
3
4
5
6
7
select distinct city
    from STATION 
        where right(city, 1) in ('a','e','i','o','u')
            and
                left(city, 1) in ('a','e','i','o','u');

Weather Observation Station 9(https://www.hackerrank.com/challenges/weather-observation-station-9)

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

1
2
3
4
5
select distinct city
    from STATION 
        where left(city, 1) not in ('a','e','i','o','u');

Weather Observation Station 10(https://www.hackerrank.com/challenges/weather-observation-station-10)

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

1
2
3
4
5
select distinct city
    from STATION 
        where right(city, 1) not in ('a','e','i','o','u');

Weather Observation Station 11(https://www.hackerrank.com/challenges/weather-observation-station-11)

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.

1
2
3
4
5
6
7
select distinct city
    from STATION 
        where right(city, 1) not in ('a','e','i','o','u')
          or
              left(city, 1) not in ('a','e','i','o','u');

Weather Observation Station 12(https://www.hackerrank.com/challenges/weather-observation-station-12)

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.

1
2
3
4
5
6
7
select distinct city
    from STATION 
        where right(city, 1) not in ('a','e','i','o','u')
          and
              left(city, 1) not in ('a','e','i','o','u');

Higher Than 75 Marks(https://www.hackerrank.com/challenges/more-than-75-marks)

Query the Name of any student in STUDENTS who scored higher than 75 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.

order by可以加多个字段

1
2
3
4
5
6
select name
    from STUDENTS 
        where marks > 75
            order by right(name, 3), id;

Employee Names(https://www.hackerrank.com/challenges/name-of-employees)

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

1
2
3
4
5
select name 
    from Employee 
        order by name;

Employee Salaries(https://www.hackerrank.com/challenges/salary-of-employees)

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.

1
2
3
4
5
6
7
select name
    from Employee 
        where salary > 2000
            and months < 10
                order by employee_id;
#MySQL