use testA
CREATE TABLE userTbl
( userID char(8) NOT NULL PRIMARY KEY,
name nvarchar(10) NOT NULL,
birthYear int NOT NULL,
addr nchar(2) NOT NULL,
mobile1 char(3),
mobile2 char(8),
height smallint,
mDate datetime
);
use testA
CREATE TABLE buyTbl
( num int IDENTITY NOT NULL PRIMARY KEY,
userid char(8) NOT NULL
FOREIGN KEY REFERENCES userTbl(userid),
prodName nchar(6) NOT NULL,
groupName nchar(4),
price int NOT NULL,
amount smallint NOT NULL
);
INSERT INTO userTbl VALUES('YJS', '유재삭', 1972, '서울', '011', '1111111', 178, '2008-8-8');
INSERT INTO userTbl VALUES('KHD', '강후동', 1970, '경북', '011', '2222222', 182, '2007-7-7');
INSERT INTO userTbl VALUES('KKJ', '김국징', 1965, '서울', '019', '3333333', 171, '2009-9-9');
INSERT INTO userTbl VALUES('KYM', '김용먼', 1967, '서울', '011', '4444444', 177, '2005-5-5');
INSERT INTO userTbl VALUES('KJD', '김제둥', 1974, '경남', NULL , NULL , 173, '2003-3-3');
INSERT INTO userTbl VALUES('NHS', '남히석', 1971, '충남', '016', '6666666', 180, '2004-4-4');
INSERT INTO userTbl VALUES('SDY', '신동업', 1971, '경기', NULL , NULL , 176, '2010-10-10');
INSERT INTO userTbl VALUES('LHJ', '이휘저', 1972, '서울', '011', '8888888', 180, '2009-4-4');
INSERT INTO userTbl VALUES('LKK', '이강규', 1960, '경남', '018', '9999999', 170, '2008-8-8');
INSERT INTO userTbl VALUES('PSH', '박수흥', 1970, '서울', '019', '0000000', 183, '2011-5-5');
INSERT INTO buyTbl VALUES('KHD', '운동화', NULL , 30, 2);
INSERT INTO buyTbl VALUES('KHD', '노트북', '전자', 1000, 1);
INSERT INTO buyTbl VALUES('KYM', '모니터', '전자', 200, 1);
INSERT INTO buyTbl VALUES('PSH', '모니터', '전자', 200, 5);
INSERT INTO buyTbl VALUES('KHD', '청바지', '의류', 50, 3);
INSERT INTO buyTbl VALUES('PSH', '메모리', '전자', 80, 10);
INSERT INTO buyTbl VALUES('KJD', '책' , '서적', 15, 5);
INSERT INTO buyTbl VALUES('LHJ', '청바지', '의류', 50, 1);
INSERT INTO buyTbl VALUES('PSH', '운동화', NULL, 30, 2);
INSERT INTO buyTbl VALUES('LHJ', '책', '서적', 15, 1);
INSERT INTO buyTbl VALUES('PSH', '운동화', NULL, 30, 2);
INSERT INTO buyTbl VALUES('LHJ', '책' , '서적', 15, 1);
INSERT INTO buyTbl VALUES('PSH', '운동화', NULL , 30, 2);
SELECT * FROM userTbl;
SELECT * FROM buyTbl;
select * from usertbl where name='김국징';
select userid, name from usertbl where birthyear >=1970 and height>=182;
select name, mobile1 + '-' + mobile2 from usertbl where (addr='서울' or addr='경기') and height>=180;
--키가 180이상이고 서울인 사람, 키가 170이상이고 경남인 사람?
--김제동, 이휘저, 이강규, 박수흥
select name from usertbl
where (height>=180 and addr='서울') or (height>=170 and addr='경남');
select name, height from usertbl where height in (179, 180, 182);
select name from usertbl where name like '김%';
select name, height, mobile1 from usertbl
where height in (select height from usertbl where mobile1='019');
--03.27
select * from usertbl;
select * from buytbl;
select top(50)percent name, mdate, height from usertbl order by height desc, mdate;
--회원들이 사는 지역이 어디 어디가 있는가?
select distinct addr from usertbl;
select * into abc from usertbl;
select * from kkk;
select userid, name, addr into xyz from usertbl;
select *into kkk from usertbl where height >=180;
--서울에 사는 사람들 중 키가 첫번째 두번째 큰 사람의 이름과 전화번호와 키를 구하시오.
select top(2) name 이름, height 키, mobile1 + '-' + mobile2 as 연락처
from usertbl
where addr = '서울'
order by height desc
select userid, sum(amount)
from buytbl
group by userid;
--각 지역별 키가 제일 큰 회원의 이름과 키
select addr, max(height) '최대지', min(height) '최소치'
from usertbl
group by addr;
select top(1) userid, sum(price*amount)
from buytbl
group by userid
order by sum(price*amount) desc;
--각 회원별 1회 구매시 평균 구매 수량
select userid, avg(cast(amount as decimal(10,2)))
from buytbl
group by userid
select count(mobile1) from usertbl;
select userid, sum(price*amount)
from buytbl
group by userid
having sum(price*amount)>1000
-----------------------------샘플2-----------------------------------
CREATE TABLE empTbl (emp NCHAR(3), manager NCHAR(3), department NCHAR(3));
INSERT INTO empTbl VALUES('나사장',NULL,NULL);
INSERT INTO empTbl VALUES('김재무','나사장','재무부');
INSERT INTO empTbl VALUES('김부장','김재무','재무부');
INSERT INTO empTbl VALUES('이부장','김재무','재무부');
INSERT INTO empTbl VALUES('우대리','이부장','재무부');
INSERT INTO empTbl VALUES('지사원','이부장','재무부');
INSERT INTO empTbl VALUES('이영업','나사장','영업부');
INSERT INTO empTbl VALUES('한과장','이영업','영업부');
INSERT INTO empTbl VALUES('최정보','나사장','정보부');
INSERT INTO empTbl VALUES('윤차장','최정보','정보부');
INSERT INTO empTbl VALUES('이주임','윤차장','정보부');
select * from emptbl
--우대리의 직속상관의 부서??
select emp, department
from emptbl
where emp in
(
select manager
from emptbl
where emp='우대리'
)
--------------------------4월3일------------------------------------
select * from usertbl, buytbl;--조인. 그냥 두개의 테이블을 갖다붙인다. 상관성은 상관없음
--조인 시키는 방법은 그냥 테이블을 두개 쓰면 된다.
select count(*) from usertbl, buytbl; --조인된 튜플의 갯수!!
select count(*) from usertbl;
select count(*) from buytbl;
select * from usertbl, buytbl where usertbl.userid=buytbl.userid
--where절 주목. 윗 예제는 단순히 조인만 시키지만 이것은 주키와 외래키가 일치하는
--의미있는 정보를 뽑을때 사용한다. 여기서 외래키는 userid이다. p297 참고할것.
--where절을 보면 테이블.속성으로 되어 있는데 이 쩜(.)은 튜플이름이 같을때 구분하기 위해.
--고객의 이름과 고객이 구매한 물품명을 출력하시오.
select name, prodname from usertbl, buytbl where usertbl.userid=buytbl.userid
----서울에 살고 있는 고객의 이름과 고객이 구매한 물품명을 출력하시오.
select name, addr, prodname
from usertbl, buytbl
where usertbl.userid=buytbl.userid and addr='서울'
--join절은 기본적으로 '비용이 큰 연산'이므로 튜플수가 많을때는 손실이 크다.
--하지만 각 테이블이 튜플 수가 적을 경우에는 사용해도 좋은듯....(?)
----★★★★★★★★새로운 예제. emp는 지웠다가 다시 복붙했다.★★★★★★
CREATE TABLE empTbl (emp NCHAR(3), manager NCHAR(3), department NCHAR(3));
INSERT INTO empTbl VALUES('나사장',NULL,NULL);
INSERT INTO empTbl VALUES('김재무','나사장','재무부');
INSERT INTO empTbl VALUES('김부장','김재무','재무부');
INSERT INTO empTbl VALUES('이부장','김재무','재무부');
INSERT INTO empTbl VALUES('우대리','이부장','재무부');
INSERT INTO empTbl VALUES('지사원','이부장','재무부');
INSERT INTO empTbl VALUES('이영업','나사장','영업부');
INSERT INTO empTbl VALUES('한과장','이영업','영업부');
INSERT INTO empTbl VALUES('최정보','나사장','정보부');
INSERT INTO empTbl VALUES('윤차장','최정보','정보부');
INSERT INTO empTbl VALUES('이주임','윤차장','정보부');
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 ('박지성','서울');
INSERT INTO stdTbl VALUES ('박주영','경기');
INSERT INTO stdTbl VALUES ('조재진','충북');
INSERT INTO stdTbl VALUES ('이천수','인천');
INSERT INTO stdTbl VALUES ('안정환','강원');
INSERT INTO clubTbl VALUES ('수영','101호');
INSERT INTO clubTbl VALUES ('바둑','102호');
INSERT INTO clubTbl VALUES ('축구','103호');
INSERT INTO clubTbl VALUES ('봉사','104호');
INSERT INTO stdclubTbl VALUES ('박지성','바둑');
INSERT INTO stdclubTbl VALUES ('박지성','축구');
INSERT INTO stdclubTbl VALUES ('조재진','축구');
INSERT INTO stdclubTbl VALUES ('이천수','축구');
INSERT INTO stdclubTbl VALUES ('이천수','봉사');
INSERT INTO stdclubTbl VALUES ('안정환','봉사')
--서울에 사는 학생들이 가입한 동아리들
select s.stdname, clubname, addr
from stdtbl sc, stdclubtbl s
where sc.stdname=s.stdname and addr='서울'
--만약 두 테이블 이상에 같은 속성이 있다면 꼭 .을 쓸것.p키 f키는 =관계 꼭 넣어^^
--from절에 보면 stbtbl과 stdclubtbl뒤에 sc, s가 있지? 이것은 별칭이다. as는 안쓴다.
--102호를 출입하는 학생들의 이름과 동아리명
select stdname, clubtbl.clubname
from clubtbl, stdclubtbl
where roomno='102호' and stdclubtbl.clubname=clubtbl.clubname
--서울에 사는 회원들이 출입하는 동아리 방은 몇호들
select roomno, s.stdname, c.clubname
from stdtbl s, stdclubtbl sc, clubtbl c
where s.stdname=sc.stdname and sc.clubname=c.clubname and addr='서울'
--다시 한번 강조: p키와 f키는 닥치고 '='연산자 넣으면 됨.
--서울에 사는 회원들에 대하여 사용자별로 구매양의 총합을 구하고 이름과 총구매양을 이름순으로
select name, sum(b.amount)as 총구매양 --b는 생략가능. 이 속성은 buy테이블에 있어서 굳이 명시할 필요가..
from usertbl u, buytbl b
where u.userid=b.userid and u.addr='서울'
group by u.userid, name --그룹by에 있는 컬럼만 셀렉트에 쓸 수 있다. 그래서 name을 쓴다.
--↑userid는 동명이인이 있을까봐. u.suerid는 생략 가능
order by name
--P.S. 10개 이상을 구입한 사람만 구해보려면(위치는 그룹바이랑 오더바이 중간에 써야한다.)
having sum(b.amount)>=10
select *
from usertbl inner join buytbl
on usertbl.userid=buytbl.userid
where buytbl.userid='KYM' --조인을 시키는 또다른 방법. inner~on을 이용
select *
from usertbl, buytbl
where usertbl.userid=buytbl.userid and buytbl.userid='KYM' --같은 거다.
select distinct u.name, u.addr
from usertbl u, buytbl b
where u.userid=b.userid
select name, addr
from usertbl u
where exists(
select *
from buytbl b
where u.userid=b.userid)-- 한번이라도 구매한 기록이 있는 사람 출력
select name, addr
from usertbl u
where not exists(
select *
from buytbl b
where u.userid=b.userid)--한번도 구매한적이 없는 사람(not만 붙이면 됨)