SQL 문제풀이 복습
문제 링크
cross join + where절
SELECT t1.team_name AS "home_team",
t2.team_name AS "away_team"
FROM Teams t1,
Teams t2
WHERE t1.team_name <> t2.team_name;
문제 링크
window 함수 + join + 집계함수.
서브쿼리 중 제일 마지막 하나는 없어도 되지만
가독성을 생각하면 들어가는 게 나을 듯.
window함수의 연산 결과를 바로 계산에 쓰면 헷갈리니.
SELECT student_id,
department_id,
IFNULL(Round(100.0 * ( ranking - 1 ) / ( cnt - 1 ), 2), 0) AS
"percentage"
FROM (SELECT student_id,
s.department_id,
RANK()
OVER(
PARTITION BY s.department_id
ORDER BY mark DESC) AS "ranking",
cnt
FROM Students s
JOIN (SELECT department_id,
Count(*) AS "cnt"
FROM Students
GROUP BY 1) a
ON s.department_id = a.department_id) result;
문제 링크
걍 집계함수.
SELECT teacher_id,
COUNT(DISTINCT subject_id) AS "cnt"
FROM Teacher
GROUP BY 1;