저번에 업로드한 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