실습 쿼리문
use sqldb;
CREATE TABLE stdTbl
( stdName nvarchar(10) NOT NULL PRIMARY KEY,
addr nchar(4) NOT NULL
);
CREATE TABLE clubTbl
( clubName nvarchar(10) NOT NULL PRIMARY KEY,
roomNo nchar(4) NOT NULL
);
CREATE TABLE stdclubTbl
( num int IDENTITY NOT NULL PRIMARY KEY,
stdName nvarchar(10) NOT NULL
FOREIGN KEY REFERENCES stdTbl(stdName),
clubName nvarchar(10) NOT NULL
FOREIGN KEY REFERENCES clubTbl(clubName),
);
INSERT INTO stdTbl VALUES (N'김범수',N'경남'), (N'성시경',N'서울'), (N'조용필',N'경기'), (N'은지원',N'경북'),(N'바비킴',N'서울');
INSERT INTO clubTbl VALUES (N'수영',N'101호'), (N'바둑',N'102호'), (N'축구',N'103호'), (N'봉사',N'104호');
INSERT INTO stdclubTbl VALUES (N'김범수',N'바둑'), (N'김범수',N'축구'), (N'조용필',N'축구'), (N'은지원',N'축구'), (N'은지원',N'봉사'), (N'바비킴',N'봉사');
select * from stdTbl; select * from clubTbl; select * from stdclubTbl;
-- 3개의 테이블 JOIN연습
select * from stdTbl; select * from clubTbl; select * from stdclubTbl;
/* 학생 테이블, 동아리 테이블, 학생_동아리 테이블을 이용해서 학생을 기준으로
학생이름/지역/가입한 동아리/동아리방번호를 출력
동아리 가입현황을 하악하시오. 학생이름, 주소, 동아리이름, 동아리방(동아리식) */
SELECT S.stdName, S.addr, C.clubName, C.roomNo FROM stdTbl S
INNER JOIN stdclubTbl SC ON S.stdName = SC.stdName
INNER JOIN clubTbl C ON SC.clubName = C.clubName ORDER BY S.stdName;
-- 동아리를 기준으로 가입한 학생의 목록을 출력.(클럽명, 방번호, 이름, 지역)
SELECT C.clubName, C.roomNo, S.stdName, S.addr FROM stdTbl S
INNER JOIN stdclubTbl SC ON S.stdName = SC.stdName
INNER JOIN clubTbl C ON SC.clubName = C.clubName ORDER BY C.clubName;
-- OUTER JOIN으로 동아리에 가입하지 않은 학생도 출력되도록(이름, 지역, 클럽명, 방번호)
SELECT S.stdName, S.addr, C.clubName, C.roomNo FROM stdTbl S
LEFT OUTER JOIN stdclubTbl SC ON S.stdName = SC.stdName
LEFT OUTER JOIN clubTbl C ON SC.clubName = C.clubName ORDER BY S.stdName;
-- 동아리를 기준으로 가입된 학생을 출력하되, 가입 학생이 하나도 없는 동아리도 출력되도록(클럽명, 방번호, 이름, 지역)
SELECT C.clubName, C.roomNo, S.stdName, S.addr FROM stdTbl S LEFT OUTER JOIN stdclubTbl SC ON SC.stdName = S.stdName RIGHT OUTER JOIN clubTbl C ON SC.clubName = C.clubName ORDER BY C.clubName ;
/* 학생 테이블 기준 동아리에 가입하지 않은 학생도 출력되고, 학생이 한명도 없는 동아리도 출력
이름, 주소, 동아리이름, 동아리실*/
SELECT S.stdName, S.addr, C.clubName, C.roomNo FROM stdTbl S
FULL OUTER JOIN stdclubTbl SC ON S.stdName = SC.stdName
FULL OUTER JOIN clubTbl C ON SC.clubName = C.clubName ORDER BY S.stdName;
-- 사용자별로 구매 총액이 높은 사람 순으로 ID와 구매 총액을 구하시오.
SELECT userid, sum(price*amount) AS [총구매액] FROM buyTbl
GROUP BY userid ORDER BY sum(price*amount) DESC;
-- 사용자별로 구매 총액이 높은 사람 순으로 아이디,이름,주소,전화번호,구매총액을 출력 WITH CTE
WITH CTE_Q2(userid, total)
AS
(SELECT userid, SUM(price*amount) FROM buyTbl GROUP BY userid)
SELECT A.userid [아이디], U.name [이름], U.addr [주소],
(U.mobile1+'-'+U.mobile2) [전화번호], A.total [구매총액]
FROM CTE_Q2 A JOIN userTbl U ON A.userid = U.userid ORDER BY total DESC;
-- 사용자별로 구매 총액이 500원 이상인 사람의 아이디, 이름, 주소, 전화번호, 구매 총액을 출력(금액높은순)
WITH CTE_Q2(userid, total) AS (SELECT userid, SUM(price*amount) FROM buyTbl GROUP BY userid)
SELECT A.userid [아이디], U.name [이름], U.addr [주소], (U.mobile1+'-'+U.mobile2) [전화번호], A.total [구매총액]
FROM CTE_Q2 A JOIN userTbl U ON A.userid = U.userid WHERE A.total >= 500 ORDER BY total DESC;
|
CROSS JOIN(상호조인) - 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능을 한다. - CROSS JOIN의 결과 개수는 두 테이블 개수를 곱한 개수가 된다. - 이러한 CROSS JOIN을 카티션곱(Cartesian Product)이라고도 부른다. - 테스트로 사용할 많은 용량의 데이터를 생성할 경우에 사용함.
|