[해커랭크] SQL (Basic) 모음

저번에 업로드한 MySQL 레벨4 모음에 이어서 해커랭크의 문제 모음을 올린다. Basic 단계부터 시작한다.

해커랭크 Revising the Select Query I

SELECT *
FROM CITY
WHERE population > 100000 AND countrycode LIKE '%USA%'

해커랭크 Revising the Select Query II

SELECT NAME
FROM city
where population > 120000 AND countrycode LIKE '%USA%'

해커랭크 Select All

SELECT *
FROM city

해커랭크 Select By ID

SELECT *
FROM city
WHERE ID = 1661

해커랭크 Japanese Cities' Attributes

SELECT *
FROM city
WHERE countrycode LIKE '%JPN%'

해커랭크 Japanese Cities' Names

SELECT name
FROM city
WHERE countrycode LIKE '%JPN%'

해커랭크 Weather Observation Station 1

SELECT city,state
FROM station

해커랭크 Weather Observation Station 3

SELECT DISTINCT city
FROM station
WHERE ID%2 = 0

해커랭크 Weather Observation Station 4

FROM STATION;

해커랭크 Weather Observation Station 6

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

해커랭크 Weather Observation Station 7

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

해커랭크 Weather Observation Station 8

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

해커랭크 Weather Observation Station 9

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

해커랭크 Weather Observation Station 10

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

해커랭크 Weather Observation Station 11

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

해커랭크 Weather Observation Station 12

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

해커랭크 Higher Than 75 Marks

SELECT name
FROM students
WHERE marks > 75
ORDER BY RIGHT(name, 3), id;

해커랭크 Employee Names

SELECT name
FROM employee
ORDER BY name

해커랭크 Type of Triangle

SELECT 
    IF(A + B > C AND B + C > A AND A + C > B,
    IF(A = B AND B = C, 'Equilateral', 
    IF(A = B OR B = C OR A = C, 'Isosceles', 'Scalene'))
    ,'Not A Triangle')
FROM triangles;

해커랭크 The PADS

SELECT CONCAT(name,'(',LEFT(occupation,1),')')
FROM occupations
ORDER BY name;
SELECT CONCAT('There are total ', COUNT(*), ' ' , LOWER(occupation), 's.')
FROM occupations
GROUP BY occupation
ORDER BY COUNT(*), occupation;

해커랭크 Revising Aggregations - The Count Function

SELECT count(*)
FROM city
WHERE population > 100000

해커랭크 Revising Aggregations - The Sum Function

SELECT sum(population)
FROM city
WHERE district LIKE '%california%'

해커랭크 Average Population

SELECT ROUND(AVG(population))
FROM city

해커랭크 Japan Population

SELECT SUM(population)
FROM city
WHERE countrycode LIKE '%JPN%'

해커랭크 Population Density Difference

SELECT MAX(population)-MIN(population)
FROM city

해커랭크 The Blunder

SELECT CEILING(AVG(salary)-AVG(REPLACE(salary,'0','')))
FROM employees

해커랭크 Top Earners

SELECT salary*months AS earnings, COUNT(*)
FROM employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1

해커랭크 Weather Observation Station 2

SELECT ROUND(SUM(lat_n),2),ROUND(SUM(long_w),2)
FROM station

해커랭크 Weather Observation Station 13

SELECT ROUND(SUM(lat_n),4)
FROM station
WHERE lat_n > 38.7880 AND lat_n < 137.2345

해커랭크 Weather Observation Station 14

SELECT ROUND(MAX(lat_n),4)
FROM station
WHERE lat_n < 137.2345

해커랭크 Weather Observation Station 15

SELECT ROUND(long_w,4)
FROM station
WHERE lat_n < 137.2345
ORDER BY lat_n DESC
LIMIT 1

해커랭크 Weather Observation Station 16

SELECT ROUND(MIN(lat_n),4)
FROM station
WHERE lat_n > 38.7780

해커랭크 Weather Observation Station 17

SELECT ROUND(long_w,4)
FROM station
WHERE lat_n > 38.7780
ORDER BY lat_n
LIMIT 1

해커랭크 Weather Observation Station 18

SELECT ROUND(ABS(MAX(lat_n)-MIN(lat_n))+ABS(MAX(long_w)-MIN(long_w)),4)
FROM station

해커랭크 Weather Observation Station 19

SELECT ROUND(SQRT(POWER(MAX(lat_n)-MIN(lat_n),2)+POWER(MAX(long_w)-MIN(long_w),2)),4)
FROM station

해커랭크 Population Census

SELECT SUM(city.population)
FROM city
    JOIN country
    ON city.countrycode = country.code
WHERE country.continent LIKE '%Asia%'

해커랭크 African Cities

SELECT city.name
FROM city
    JOIN country
    ON city.countrycode = country.code
WHERE country.continent LIKE '%africa%'

해커랭크 Average Population of Each Continent

SELECT country.continent, FLOOR(AVG(city.population))
FROM city
    JOIN country
    ON city.countrycode = country.code
GROUP BY country.continent