⚾️운동부(야구팀) 테이블 예제

운동부 TABLE을 작성하라.

TEAM : 팀 아이디, 지역, 팀 명, 개설 날짜, 전화번호, 홈페이지

PLAYER : 선수번호, 선수 명, 등록일, 포지션, 키, 팀 아이디

TEAM을 두 개만 등록합니다.

두 개의 TEAM에 선수를 각각 3명씩 등록(추가)합니다.

선수를 입력하면 그 선수의 팀 명과 전화번호, 홈페이지가 출력되도록 합니다.

 

 

도식 -

 

풀이 -

 

 

--팀
CREATE TABLE TEAM(
TEAMID NUMBER(8) CONSTRAINT PK_TEAM_ID PRIMARY KEY,
RESIONNAME VARCHAR2(10),
TEAMNAME VARCHAR2(30) NOT NULL,
STARTDATE DATE,
TEL VARCHAR2(20),
HOMEPAGE VARCHAR2(30)
);
 
--플레이어
CREATE TABLE PLAYER(
PLAYERID NUMBER CONSTRAINT PK_PLAYER PRIMARY KEY,
PLAYERNAME VARCHAR2(20),
JOINDATE DATE,
POSITION VARCHAR2(20),
HEIGHT NUMBER(5, 1),
TEAMID NUMBER(8)
);
 
ALTER TABLE PLAYER 
ADD
CONSTRAINT FK_TEAM_ID FOREIGN KEY(TEAMID)
REFERENCES TEAM(TEAMID);
 
 
-- 팀 두개 등록
 
INSERT INTO TEAM(TEAMID, RESIONNAME, TEAMNAME, STARTDATE, TEL, HOMEPAGE)
VALUES(10,'서울','베어즈',SYSDATE,'02-123-4567','http://www.bears.com');
 
INSERT INTO TEAM(TEAMID, RESIONNAME, TEAMNAME, STARTDATE, TEL, HOMEPAGE)
VALUES(20,'대구','라이온즈',SYSDATE,'031-234-5678','http://www.lions.com');
 
-- 두개의 팀에 선수를 각각 3명씩 등록해 본다.
INSERT INTO player(PLAYERID, PLAYERNAME, JOINDATE, POSITION, HEIGHT, TEAMID)
VALUES(100, '홍길동', '02/05/23', '투수', 181.1, 20)
 
INSERT INTO player(PLAYERID, PLAYERNAME, JOINDATE, POSITION, HEIGHT, TEAMID)
VALUES(101, '일지매', '04/07/16', '타자', 185.1, 10)
 
INSERT INTO player(PLAYERID, PLAYERNAME, JOINDATE, POSITION, HEIGHT, TEAMID)
VALUES(102, '성춘향', '05/09/12', '투수', 176.5, 20)
 
INSERT INTO player(PLAYERID, PLAYERNAME, JOINDATE, POSITION, HEIGHT, TEAMID)
VALUES(103, '정수동', '01/11/13', '투수', 179.3, 10)
 
-- 선수를 입력하면 그 선수의 팀 명과 전화번호, 홈페이지가 출력되도록 합니다.
SELECT p.playername, t.teamname, t.tel, t.homepage
FROM player p, team t
WHERE p.teamid = t.teamid
AND p.playername = '성춘향';