SQL 코드카타
제출 내역
'경제' 카테고리에 속하는 도서들의 도서 ID, 저자명, 출판일 리스트를 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요.
- 도서 ID, 저자명 다른 테이블 : 조인
- 출판일 (년-월-일) : date_format( ,'%Y-%m-%d')
- 경제 카테고리 : category = '경제'
- 오름차순 :order by asc
SELECT B.BOOK_ID, A.AUTHOR_NAME, DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK B
JOIN AUTHOR A
ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY B.PUBLISHED_DATE ASC;
Employees Whose Manager Left the Company
Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company.
When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_id set to the manager that left.
- less than $30000 : salary < 30000
- When a manager leaves the company, their information is deleted from the Employees table : manger_id not in (서브쿼리)
SELECT employee_id
FROM Employees
WHERE manager_id not in (select employee_id from employees) and salary < 30000
order by employee_id
Weather Observation Station 17
Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780.
Round your answer to 4 decimal places.
- Round your answer to 4 decimal places : round( ,4)
- (LAT_N) in STATION is greater than 38.7780. : lat_n > 38.7780
select round(long_w, 4)
from station
where lat_n > 38.7780
order by lat_n asc
limit 1;