HackerRank DB퀴즈 BASIC select / ADVENCED(Oracle)

쿼리 독해력이 부족하여 찾아본 hackerRank

https://www.hackerrank.com/

HackerRank

1. Weather Observation Station 8

. Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

STAION 테이블에서 CITY name이 모음으로 시작하고 모음으로 끝나는 것을 찾는 쿼리 문제

 

 

 

 

개노답 내 풀이

select distinct city from station where city like 'A%' AND city like '%a' 
union 
select distinct city from station where city like 'A%' AND city like '%e'
union 
select distinct city from station where city like 'A%' AND city like '%i' 
union 
select distinct city from station where city like 'A%' AND city like '%o' 
union 
select distinct city from station where city like 'A%' AND city like '%u'
union 
select distinct city from station where city like 'E%' AND city like '%a'
union 
select distinct city from station where city like 'E%' AND city like '%e'
union 
select distinct city from station where city like 'E%' AND city like '%i'
union
select distinct city from station where city like 'E%' AND city like '%o'
union 
select distinct city from station where city like 'E%' AND city like '%u' 
union 
select distinct city from station where city like 'I%' AND city like '%a' 
union 
select distinct city from station where city like 'I%' AND city like '%e' 
union 
select distinct city from station where city like 'I%' AND city like '%i' 
union 
select distinct city from station where city like 'I%' AND city like '%o' 
union 
select distinct city from station where city like 'I%' AND city like '%u' 
union 
select distinct city from station where city like 'O%' AND city like '%a' 
union 
select distinct city from station where city like 'O%' AND city like '%e' 
union 
select distinct city from station where city like 'O%' AND city like '%i' 
union 
select distinct city from station where city like 'O%' AND city like '%o' 
union 
select distinct city from station where city like 'O%' AND city like '%u' 
union 
select distinct city from station where city like 'U%' AND city like '%a' 
union 
select distinct city from station where city like 'U%' AND city like '%e' 
union 
select distinct city from station where city like 'U%' AND city like '%i' 
union s
elect distinct city from station where city like 'U%' AND city like '%o' 
union
select distinct city from station where city like 'U%' AND city like '%u';

 

팔이 너무 아파서 이게 아니다 싶어 줄여달라고 chat gpt에 부탁하니 이렇게 줄여줬다.

 

 

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');

나는 왜 OR 쓰면 안된다고 생각한 거지  

 

 

---

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

STUDENTS 테이블에서 75점 이상인 학생의 이름을 출력 정렬 조건으로 이름의 마지막 3글자로 정렬하고

3자가 같은 경우에는 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

 

내 풀이

select name from students where marks > 75
order by substr(name, length(name)-2), id asc;

where조건이 쉽길래 개꿀 했는데 정렬조건에서 헤맴

length(name) -3  해서 틀림. 이거 좀 헷갈렸는데

5자리라고 하면 5 - 3 = 2, 2번째부터 출력되니까 2, 3, 4, 5로  4자리가 나옴 머리가 굳은 건지 몬가 함정에 빠진 기분 

 

---

3. TYPE for Triangle

Write a query identifying the type of each record in the TRIANGLES table using its three side lengths. Output one of the following statements for each record in the table:

• Equilateral: It's a triangle with  sides of equal length.
• Isosceles: It's a triangle with  sides of equal length.
• Scalene: It's a triangle with  sides of differing lengths.
• Not A Triangle: The given values of A, B, and C don't form a triangle

세 변의 길이를 이용하여 삼각형 종류 나누기 

정삼각형, 이등변삼각형, 그냥 삼각형, 삼각형 아님

Input Format

The TRIANGLES table is described as follows:

Each row in the table denotes the lengths of each of a triangle's three sides.

Sample Input

내 풀이

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 a=c then 'Isosceles'
when a <> b and b<> c then 'Scalene'
end tri
from triangles
;

CASE WHEN 최근에 공부해서 써먹어 보았다. 신기하게 잘 나옴

---

4. Binary Tree Nodes

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

• Root: If node is root node.
• Leaf: If node is leaf node.
• Inner: If node is neither root nor leaf node.

트리 문제  루트/ 가지/ 잎으로 구분하기 

n칼럼과 이름을 보여주고 N 오른 차순으로 정렬

Sample Input

Sample Output

 

내 풀이

select n,
case when p is null then 'Root' 
when n in (select p from bst) then 'Inner'
else 'Leaf'
end tree
from bst
order by n asc;

 

n in p로 작성하니까 적용이 안돼서 p 전체를 불러오고 싶어 select p from bst 했다. 이게 되나 싶었는데 된다

case when 또 써먹어서 신남 

 

지금은 효율이고 나발이고 그저 화면에 출력만 하기에도 버겁지만 조금씩 풀어가다 보면 언젠가는 늘겠지

갈 길이 멀다