[hackerrank][SQL][Basic]

HACKERRANK _ SQL

https://www.hackerrank.com/domains/sql?filters%5Bskills%5D%5B%5D=SQL%20%28Basic%29 

 

Solve SQL Code Challenges

SKILLS (BASIC  / INTERMEDIATE / Advanced ) 中 BASIC !

Revising the Select Query I

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

Revising the Select Query II

select NAME
from CITY
where POPULATION > 120000 and CountryCode='USA'

Japanese Cities' Names

select NAME 
from CITY
where COUNTRYCODE = "JPN"

Weather Observation Station 4

select count(CITY) - count(distinct CITY)
from STATION

Weather Observation Station 5

/* minimum value */
select CITY, LENGTH(CITY) from STATION 
order by LENGTH(CITY) asc, CITY
limit 1;

/* maximum value */
select CITY, LENGTH(CITY) from STATION
order by LENgTH(CITY) desc, CITY
limit 1;

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%"

/* another solution */
select distinct CITY
from STATION 
where SUBSTR(CITY,1,1) in ('a','e','i','o','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 SUBSTR(CITY,1,1) in ('a','e','i','o','u')
    and SUBSTR(CITY, length(CITY),1) in ('a','e','i','o','u')

/* another solution that using regular expression */
SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP '^[aeiou].*[aeiou]$'
/* ^:start, $:end, .:any, *:repeated */

Weather Observation Station 9

select distinct CITY
from STATION 
where SUBSTR(CITY,1,1) not in ('a','e','i','o','u')

Weather Observation Station 10

select distinct CITY
from STATION 
where substr(CITY, length(CITY),1) not in ("a","e","i","o","u");

Weather Observation Station 11

select distinct CITY
from STATION 
where left(CITY,1) not in ('a','e','i','o','u')
    or right(CITY, 1) not in ('a','e','i','o','u')

/* another solution that using regular expression */
SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP '^[^aeiou]|[^aeiou]$';

Weather Observation Station 12

select distinct CITY
from STATION 
where left(CITY,1) not in ('a','e','i','o','u')
and right(CITY, 1) not in ('a','e','i','o','u')

Higher Than 75 Marks

select NAME
from STUDENTS
where MARKS > 75
order by right(NAME,3) asc, ID asc

☆ Type of Triangle

select
    case 
        when A+B<=C or A+C<=B or B+C<=A then "Not A Triangle" 
        when A=B and B=C then "Equilateral"
        when A=B or B=C or C=A then "Isosceles"
        else "Scalene"
    end
from TRIANGLES

☆ The PADS

select concat(NAME, "(", left(OCCUPATION,1), ")" )
from OCCUPATIONS
order by NAME;

select concat("There are a total of ", CNT, " ", lower(OCCUPATION),"s.")
from (select OCCUPATION, count(*) CNT from OCCUPATIONS group by OCCUPATION) sub1
order by cnt ;

The Blunder

select round(avg(Salary),0) - round(avg(replace(Salary,0,"")),0)
from EMPLOYEES

Top Earners

select salary*months Earnings, count(*)
from EMPLOYEE
group by Earnings
order by Earnings desc
limit 1;

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 18

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

Weather Observation Station 19

select round(power(
	power(max(lat_n)-min(lat_n),2) 
    + power(max(long_w)-min(long_w),2)
    ,1/2),4) 
from station

Average Population of Each Continent

select COUNTRY.CONTINENT, TRUNCATE(AVG(CITY.POPULATION),0)
from COUNTRY inner join CITY on CITY.COUNTRYCODE = COUNTRY.CODE
group by COUNTRY.CONTINENT

truncate : 버림

round : 반올림