HackerRank- Weather Observation Station 10,11,12,Higher Than 75 Marks,Employee Names

Weather Observation Station 10📝

https://www.hackerrank.com/challenges/weather-observation-station-10/problem?isFullScreen=true 

 

➡️ STATION 테이블, 조건 : a,e,i,o,u로 끝나지 않는 city 이름 -> 출력 : 중복되지 않는 city 

-> Weather Observation Station 7번(이전게시물) 에 not 또는 ^을 붙인다.

Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

SELECT DISTINCT city
FROM station
WHERE NOT (city LIKE '%a'
      OR city LIKE '%e'
      OR city LIKE '%i'
      OR city LIKE '%o'
      OR city LIKE '%u');

SELECT DISTINCT city
FROM station
WHERE city REGEXP '[^aeiou]$';

 

---

 

Weather Observation Station 11📝

https://www.hackerrank.com/challenges/weather-observation-station-11/problem?isFullScreen=true 

 

➡️ STATION 테이블, 조건 : a,e,i,o,u로 끝나지 않는 city 이름 -> 출력 : 중복되지 않는 city

-> Weather Observation Station 8번(이전게시물) 에 ^을 붙인다.

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.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

SELECT DISTINCT city 
FROM station 
WHERE city REGEXP '^[^aeiou]' OR city REGEXP '[^aeiou]$';

 

---

 

Weather Observation Station 12📝

https://www.hackerrank.com/challenges/weather-observation-station-12/problem?isFullScreen=true

 

➡️ STATION 테이블, 조건 : a,e,i,o,u로 끝나지 않는 city 이름 -> 출력 : 중복되지 않는 city

-> Weather Observation Station 8번(이전게시물) 에 ^을 붙인다.위와 다른 점은 and, or의 차이다. 

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:

where LAT_N is the northern latitude and LONG_W is the western longitude.

SELECT DISTINCT city 
FROM station 
WHERE city REGEXP '^[^aeiou].*[^aeiou]$';

 

---

 

Higher Than 75 Marks📝

https://www.hackerrank.com/challenges/more-than-75-marks/problem?isFullScreen=true 

 

➡️ STUDENTS 테이블,조건 : marks>75보다 큰 값 출력 -> 출력 시 : 뒤에 세글자로 정렬 (ex.Ashley,Julia,Belvet ),2순위 정렬 조건 : ID

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

#MySQL
SELECT name 
FROM students 
WHERE marks>75 
ORDER BY RIGHT(name,3),ID;--mysql에서는 SUBSTRING,RIGHT,LEFT

SELECT name 
FROM students 
WHERE marks>75 
ORDER BY SUBSTRING(name,-3),ID;

#Oracle
SELECT name
FROM students
WHERE marks>75
ORDER BY SUBSTR(name,-3),ID;--오라클에서는 SUBSTR,오라클에서는 left,right가 존재하지 않음

 

---

 

Employee Names📝

https://www.hackerrank.com/challenges/name-of-employees/problem?isFullScreen=true 

 

➡️ Employee 테이블-> 출력 : name column 출력, ORDER BY name column

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;