[8주차]해커랭크 Basic Select 15

1. Basic Select > Revising the Select Query I

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

 

 

2. Basic Select > Revising the Select Query II

select NAME
from CITY
where POPULATION > 120000 and COUNTRYCODE = "USA"

 

 

3. Basic Select > Select All

SELECT *
FROM CITY

 

 

4. Basic Select > Select By ID

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

SELECT *
FROM CITY
WHERE ID = 1661

 

 

5. Basic Select > Japanese Cities' Attributes

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

select *
from CITY
where COUNTRYCODE = "JPN"

 

 

6. Basic Select > Japanese Cities'  Names

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"

 

 

7. Basic Select > Weather Observation Station 1

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

SELECT CITY,
       STATE
FROM STATION

 

 

8. Basic Select > Weather Observation Station 3

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.

SELECT DISTINCT CITY
FROM STATION
WHERE ID % 2 = 0

 

 

9. Basic Select > Weather Observation Station 4

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

SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
FROM STATION

 

 

10. Basic Select > 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.

(SELECT CITY,
       LENGTH(CITY) AS LEN
FROM STATION
ORDER BY LEN, CITY
LIMIT 1)
UNION 
(SELECT CITY,
       LENGTH(CITY) AS LEN2
FROM STATION
ORDER BY LEN2 DESC, CITY
LIMIT 1)

문자열 길이를 추출하는 함수는 LENGTH이다. 

 

 

11. Basic Select > 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.

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) IN ("A", "E", "I", "O", "U")

 

 

12. Basic Select > 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.

SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY,1) IN ("a", "e", "i", "o", "u")

 

 

 

13. Basic Select > 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.

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) IN ("A", "E", "I", "O", "U") AND RIGHT(CITY,1) IN ("a", "e", "i", "o", "u")

 

 

14. Basic Select > Weather Observation Station 9

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

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) NOT IN ("A", "E", "I", "O", "U")

 

 

15. Basic Select > Weather Observation Station 10

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

SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY,1) NOT IN ("a", "e", "i", "o", "u")