문제 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. Sample Input Sample Out..
문제 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 lowerca..
문제 Generate the following two result sets: Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S). Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in asc..
문제 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..
문제 Query the list of CITY names from STATION that do not start 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. 풀이 -- LEFT SELECT DISTINCT CITY FROM STATION WHERE LEFT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u'); -- REGEXP SELECT DISTINCT CITY FROM STATION WHERE CITY..
문제 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: 문제 요약 CITY 앞글자가 모음인 경우를 추출하라 풀이 SELECT CITY FROM STATION WHERE CITY REGEXP "^[aeiou].*"; 배운점 REGEXP : LIKE 검색과 달리 정규식을 이용한 검색방식이다. WHERE NAME REGEXP '가' > '가'를 포함한 모든 레코드 WHERE NAME REGEXP '가|나|다|라' > '가' 또는 '나'..
문제 Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically. The STATION table is described as follows: where LAT_N is the northern latitude and LONG_W is the western longitude. Sample Input ..
문제 2017년 1월 한 달 동안 발생한 주문의 배송 예측이 정확했는지 분석을 하려고 합니다. 고객의 구매 일자별로 배송 예정 시각 안에 고객에게 도착한 주문과, 배송 예정 시각이 지나서 고객에게 도착한 주문을 각각 집계하는 쿼리를 작성해주세요. 배송 완료 또는 배송 예정 시각 데이터가 없는 경우는 계산에서 제외합니다. 계산 결과는 구매 날짜를 기준으로 오름차순 정렬되어야 한다. 문제 요약 - 예정 배송 시각 안(예정 배송일>배송 도착일) 에 도착한 주문과 예정 배송 시각을 초과한 주문(예정 배송일
문제 1 2021년 1월 한 달간 총 주행 거리가 50km 이상인 자전거의 ID를 출력하는 쿼리를 작성 문제 2 첫 주문과 마지막 주문 일자 풀이 1 SELECT bike_id FROM rental_history WHERE STRFTIME('%Y%m%d', rent_at) BETWEEN '20210101' AND '20210131' AND DISTANCE >= 50000; 풀이 2 SELECT MIN(STRFTIME('%Y-%m-%d', order_purchase_timestamp)) AS first_order_date, MAX(STRFTIME('%Y-%m-%d', order_purchase_timestamp)) AS last_order_date FROM olist_orders_dataset; 배운 점 ..
