코드카타

---

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;