SQL 서브쿼리, GROUP BY, ORDER BY, LIMIT 간단

서브쿼리

SELECT D.name AS Department_Name, C.title AS Course_Title, C.credits

FROM Courses C

JOIN Departments D ON C.department_id = D.department_id

WHERE C.credits IN (

SELECT MAX(C2.credits)

FROM Courses C2

WHERE C2.department_id = C.department_id

GROUP BY C2.department_id

)

ORDER BY D.name;

 

 

GROUP BY

ORDER BY

LIMIT

SELECT d.name

FROM Doctors d

JOIN Appointments a

ON a.doctor_id = d.doctor_id

GROUP BY d.name

ORDER BY COUNT(a.doctor_id) DESC

LIMIT 1;

 

오늘자 탈모유발자 기록

 

SELECT t.table_id, COUNT(r.reservation_id) as cnt

FROM Tables t

JOIN Reservations r

ON r.table_id = t.table_id

WHERE DATE(r.reservation_time) = '2024-05-10'

GROUP BY t.table_id

HAVING cnt = (

SELECT MAX(cnt2) FROM (

SELECT COUNT(r.reservation_id) as cnt2

FROM Reservations r

GROUP BY table_id

) as sq

);

 

서브쿼리 추가 (동일 최대값이 여럿일때 동시 출력 서브쿼리)