후기

2월을 시작하며 빠르게 배웠던 SQL.
강사님께서는 현업에서 다루는 수준보다 훨씬 어려운 수준까지 알려 주셨다.
그래서인지 따라가는 것 반이었고 놓치는 것 반이라서 아쉬운 마음이 크다.
기초가 탄탄해야 앞으로 코딩 테스트든 현업이든 뭔가를 작성할 수 있을 것 같다는 판단이 들어, 한 달 전에 사둔 [백문이불여일타] SQL 초급 강의를 들으며 기초 문법을 다시 복습했다.
앞으로는 더 복잡하고 긴 쿼리를 작성할 수 있기를 바란다.
그러기 위해서는 꾸준히! 열심히!

Weather Observation Station 1

Query a list of CITY and STATE from the STATION table.
The STATION table is described as follows:
where LAT_N is the northern latitude and LONG_W is the western longitude.

풀이

SELECT CITY, STATE
FROM STATION

Revising the Select Query I

Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.
The CITY table is described as follows:

풀이

SELECT *
FROM city
WHERE population > 100000 AND countrycode = 'USA'

Solved Select By ID

Query all columns for a city in CITY with the ID 1661.
The CITY table is described as follows:

SELECT *
FROM city
WHERE ID = 1661

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.
Input Format
The STATION table is described as follows:

풀이

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 12

Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.
Input Format
The STATION table is described as follows:

풀이

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'

Solved Employee Names

Write a query that prints a list of employee names (i.e.: the name attribute) from the Employee table in alphabetical order.
Input Format
The Employee table containing employee data for a company is described as follows:
where employee_id is an employee's ID number, name is their name, months is the total number of months they've been working for the company, and salary is their monthly salary.
Sample Input
Sample Output
Angela
Bonnie
Frank
Joe
Kimberly
Lisa
Michael
Patrick
Rose
Todd

풀이

SELECT name
FROM Employee
ORDER BY name ASC

Solved 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 per month who have been employees for less than months. Sort your result by ascending employee_id.
Input Format
The Employee table containing employee data for a company is described as follows:
where employee_id is an employee's ID number, name is their name, months is the total number of months they've been working for the company, and salary is the their monthly salary.
Sample Input
Sample Output
Angela
Michael
Todd
Joe
Explanation
Angela has been an employee for month and earns per month.
Michael has been an employee for months and earns per month.
Todd has been an employee for months and earns per month.
Joe has been an employee for months and earns per month.
We order our output by ascending employee_id.

풀이

SELECT name
FROM Employee
WHERE salary > 2000 AND months < 10
ORDER BY employee_id ASC

Higher Than 75 Marks

Query the Name of any student in STUDENTS who scored higher than 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.
Input Format
The STUDENTS table is described as follows: The Name column only contains uppercase (A-Z) and lowercase (a-z) letters.
Sample Input
Sample Output
Ashley
Julia
Belvet
Explanation
Only Ashley, Julia, and Belvet have Marks > . If you look at the last three characters of each of their names, there are no duplicates and 'ley' < 'lia' < 'vet'.

풀이

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

Weather Observation Station 15

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than . Round your answer to decimal places.
Input Format
The STATION table is described as follows:
where LAT_N is the northern latitude and LONG_W is the western longitude.

풀이

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