16일차.

---

-- SOCCER_SQL_010
-- 모든 선수들 중
-- 포지션을 배정받지 못한 선수들의 팀과  이름
-- 팀명, 사람명 오름차순

SELECT 
    t.team_name AS 팀명,
    p.player_name AS 선수명
FROM player p
JOIN team t ON p.team_id = t.team_id
WHERE p.position IS NULL
ORDER BY t.team_name, p.player_name;

 

---

-- SOCCER_SQL_011
-- 팀과 스타디움을 조인하여
-- 팀이름, 스타디움 이름 출력

SELECT 
    t.team_name AS 팀이름,
    s.stadium_name AS 스타디움
FROM team t
JOIN stadium s ON t.stadium_id = s.stadium_id;

 

SELECT 
    t.team_name AS 팀이름,
    s.stadium_name AS 스타디움

    t.region _name, COALESCE( t.region_name, '연고지 없음') AS 연고지

FROM stadium s

JOIN team t ON s.hometeam_id = t.team_id

SELECT s.stadium_name, COALESCE(t.team_name, '홈팀 없음') AS 홈팀

 

SELECT 
    t.team_name AS 팀이름,
    s.stadium_name AS 스타디움,
COALESCE(t.team_name, '홈팀 없음') AS 홈팀
FROM stadium s
LEFT JOIN team t ON s.hometeam_id = t.team_id
ORDER BY t.team_name COLLATE korean ASC;

 

sellect * from stadium

 

<!--br {mso-data-placement:same-cell;}-->

쿼리기준 테이블결과 수

1번	team t 기준	팀이 있는 경기장만
2번	stadium s 기준	경기장이 있는 팀만

-- stadium 테이블
stadium_id | stadium_name     | hometeam_id
-----------|------------------|-------------
C02        | 광주월드컵경기장 | K05
C04        | 대구스타디움     | K04
...        | ...              | NULL (홈팀 없음)

-- team 테이블
team_id | team_name | stadium_id (없음!)
--------|-----------|-------------------
K05     | 광주FC    | (없음)

→ team에는 stadium_id 없고, → stadium에는 hometeam_id 있음!

 

ORDER BY t.team_name COLLATE korean ASC ;

 COLLATE korean ASC ; 한국어 순으로 정렬

끝에만 ; -마지막으로 붙여주는 것

---

-- SOCCER_SQL_012
-- 팀과 스타디움, 스케줄을 조인하여
-- 2012년 3월 17일에 열린 각 경기의
-- 팀이름, 스타디움, 어웨이팀 이름 출력
-- 다중테이블 join 을 찾아서 해결하시오.

 

ORDER BY h.team_name COLLATE korean ASC; 추가함!

SELECT 
    h.team_name AS 홈팀,
    s.stadium_name AS 스타디움,
    a.team_name AS 어웨이팀,

sch.sche_date AS 경기일정

 

* 경기일정 넣어주고 싶으면  , (쉼표!) 넣어줘야 돌아감

FROM schedule sch
JOIN team h ON sch.hometeam_id = h.team_id
JOIN team a ON sch.awayteam_id = a.team_id
JOIN stadium s ON sch.stadium_id = s.stadium_id

* 경기일정이 같은 것끼리 연결시켜줘야 하니까 schedule 일정표에서 id(고유번호) 가져옴

WHERE sch.sche_date = '2012-03-17'

ORDER BY h.team_name COLLATE korean ASC;

 

---

-- SOCCER_SQL_013
-- 2012년 3월 17일 경기에
-- 포항 스틸러스 소속 골키퍼(GK)
-- 선수, 포지션,팀명 (연고지포함),
-- 스타디움, 경기날짜를 구하시오
-- 연고지와 팀이름은 간격을 띄우시오

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'team' 
ORDER BY ordinal_position;
***컬럼명 알아보는 방법*****(내가 궁금해서 해본 것)

SELECT 
    t.region_name || ' ' || t.team_name AS "연고지와 팀명",

 연고지와 팀이름은 간격을 띄우시오
    p.player_name AS 선수명,
    p.position AS 포지션,
    s.stadium_name AS 스타디움,
    sch.sche_date AS 경기날짜
FROM player p
JOIN team t ON p.team_id = t.team_id
JOIN schedule sch ON (sch.hometeam_id = t.team_id OR sch.awayteam_id = t.team_id)
JOIN stadium s ON sch.stadium_id = s.stadium_id
WHERE t.team_name = '스틸러스'
  AND p.position = 'GK'
  AND sch.sche_date = '2012-03-17';

 

---

-- SOCCER_SQL_014
-- 홈팀이 3점이상 차이로 승리한 경기의
-- 경기장 이름, 경기 일정
-- 홈팀 이름과 원정팀 이름을
-- 구하시오

 

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'schedule' 
ORDER BY ordinal_position;

-컬럼 네임 알아보는 방법

SELECT 
    s.stadium_name AS 경기장,
    sch.sche_date AS 경기일정,
    h.team_name AS 홈팀,
    a.team_name AS 원정팀,

 

sch.home_score AS 홈팀점수,
sch.away_score AS 원정팀점수

*점수 추가

 

SELECT → "이거 보여줘!"

FROM schedule sch

 

FROM schedule sch → "어디서 찾을까?"

 

schedule 표에서 180건으로 젤 많음 그래서 여기서 찾았음

 

JOIN team h ON sch.hometeam_id = h.team_id
JOIN team a ON sch.awayteam_id = a.team_id
JOIN stadium s ON sch.stadium_id = s.stadium_id

 

연결 조건: sch.hometeam_id = h.team_id

"일정표에 적힌 홈팀 번호(K02)가, 팀 테이블에 있는 팀 번호(K02)와 같으면 연결해줘!"

 

이름의미away영어로 "떨어져 있다", "원정"awayteam_id원정팀 번호 (집이 아닌 팀)

 

1. schedule (경기 일정)
   ┌────────────────────┐
   │ hometeam_id: K02   │ → team h → 삼성블루윙즈
   │ awayteam_id: K09   │ → team a → FC서울
   │ stadium_id: B01    │ → stadium s → 수원월드컵경기장
   │ home_score: 5      │
   │ away_score: 1      │
   └────────────────────┘

2. JOIN 후 결과
   경기장: 수원월드컵경기장
   홈팀: 삼성블루윙즈
   원정팀: FC서울
   점수 차이: 4 → 포함!

 

 

WHERE sch.home_score - sch.away_score >= 3;

---

-- SOCCER_SQL_015
-- STADIUM 에 등록된 운동장 중에서
-- 홈팀이 없는 경기장까지 전부 나오도록
-- 카운트 값은 20

 

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'team' 
ORDER BY ordinal_position;

 

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = ' stadium ' 
ORDER BY ordinal_position;

SELECT 

    s.stadium_name,
    s.stadium_id,
    t.team_name AS 홈팀,
s.seat_count,
hometeam_id,
t.e_team_name

 

FROM stadium s
LEFT JOIN team t ON s.stadium_id = t.stadium_id
ORDER BY s.stadium_id;

-- 1. 목록 보기
SELECT s.stadium_name, COALESCE(t.team_name, '홈팀 없음') AS 홈팀
FROM stadium s
LEFT JOIN team t ON s.stadium_id = t.stadium_id;

 

COALESCE = "NULL이면 이걸로 바꿔줘!" → 보기 좋고, 이해 쉬움

 

---

핵심 차이: COALESCE(t.team_name, '홈팀 없음')

 

함수의미

COALESCE(a, b)	a가 NULL이면 b를 출력
t.team_name	원래 값 (있으면 출력)
'홈팀 없음'	대체 값 (없으면 이걸 출력)

비유로 이해하기: "친구랑 짝꿍 만들기"

상상해보세요. 왼쪽 반 (stadium) 에 4명의 학생이 있어요. 오른쪽 반 (team) 에 2명의 학생이 있어요. "같은 번호표 가진 사람끼리 짝꿍 만들기" 라는 규칙!

---

학생 명단

 

왼쪽 반 (stadium)번호오른쪽 반 (team)번호

수원월드컵경기장	B01	삼성블루윙즈	B01
서울월드컵경기장	B02	FC서울	B02
경기장6	S06	(없음)
경기장7	S07	(없음)

INNER JOIN = "짝꿍 있는 사람만 발표"

규칙: 짝꿍이 있어야만 무대에 올라가요!

 

발표자 (결과)

수원월드컵경기장 + 삼성블루윙즈

서울월드컵경기장 + FC서울

→ 경기장6, 경기장7은 짝꿍 없어서 탈락!

---

LEFT JOIN = "왼쪽 반은 무조건 무대에 올리고, 짝꿍 있으면 같이 올리기"

규칙:

• 왼쪽 반 (stadium) 은 무조건 무대에 올라감
• 짝꿍 있으면 같이 올라가고, 없으면 혼자 올라감

 

무대에 오른 사람 (결과)

수원월드컵경기장 + 삼성블루윙즈

서울월드컵경기장 + FC서울

경기장6 + (짝꿍 없음)

경기장7 + (짝꿍 없음)

→ 짝꿍 없으면 NULL로 표시 → 이게 바로 "홈팀 없는 경기장"

-- 2. 개수 세기
SELECT COUNT(*) AS 카운트
FROM stadium s
LEFT JOIN team t ON s.stadium_id = t.stadium_id;

 

COUNT(*) → "무대에 오른 사람 수" 를 셈

 

---

---

강사님한테 코드 보낼때 방법