[9주차]해커랭크 16 ~ 25(Basic Select, Aggregation)

16. Weather Observation Station11

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.

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

 

 

17. Weather Observation Station12

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.

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

 

 

 

18. Higher 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.

SELECT Name
FROM STUDENTS
WHERE Marks > 75
ORDER BY RIGHT(Name,3), ID

 

 

 

19. Employee Names

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

select name
from Employee
order by name

 

 

 

 

20. Employee Salaries

Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than  $2000 per month who have been employees for less than  10 months. Sort your result by ascending employee_id.

select name
from Employee
where salary > 2000 and months < 10
order by employee_id

 

 

 

21. Revising Aggregations - The Count Function

Query a count of the number of cities in CITY having a Population larger than 1000000

select count(ID)
from CITY
where POPULATION > 100000

 

 

 

22. Revising Agrgregation - The Sum Function

Query the total population of all cities in CITY where District is California.

Input Format

select sum(POPULATION)
from CITY
where DISTRICT  = "California"

 

 

 

23. Revising Agrgregation - Averages

Query the average population of all cities in CITY where District is California.

Input Format

select avg(POPULATION)
from CITY
WHERE DISTRICT = "California"

 

 

 

 

24. Average Population

Query the average population for all cities in CITY, rounded down to the nearest integer.

select round(avg(POPULATION),0)
from CITY

 

 

 

 

25. Japan Population

Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.

select sum(POPULATION)
from CITY
where COUNTRYCODE = "JPN"