릴레이션이 이루어지는 컬럼은 형식이 같아야 한다.
테이블 구분은 중복되는 데이터가 있는지 없는지를 보고 테이블이 중복된다면 부 데이터이고 테이블이 중복되지 않는것이 주데이터가 된다.
----------------------------2012. 04. 02-1교시-----------------------------------
-- tblStaff, tblProject
-- tblMember, tblGenre, tblVideo, tblRent
select * from tblStaff; -- 직원정보
select * from tblProject; -- 프로젝트(담당자- tblStaff의직원)
use sqlex;
--1. 신입사원입사
insert into tblStaff (name, salary, addr)
values ('홍길동',200,'서울시')
--2. 김기문사원퇴사(문제있음!!)
-- : 담당중이었던tblProject 에서담당자유령~
-- : 사전에프로젝트를위임하거나프로젝트를삭제를한뒤..퇴사
-- 해당스탭테이블에서는명단이삭제되면,
-- 프로젝트테이블에맡고있던테이블이존재해선안된다.
delete from tblStaff where name = '김기문'; --김기문140 서울시
--s-1
--s-5
--3.김수동사원퇴사(정상적인퇴사)
--3.1 김수동이담당하던프로젝트를아무개에게위임
update tblProject set
staff = '김기문'
where staff = '김수동';
--3.2 김수동퇴사
delete from tblStaff where name = '김수동';
--4. 신규프로젝트발주(정상)
insert into tblProject (prjName, staff)
values ('고객센터설립','김기문');
--5. 신규프로젝트발주(존재하지않는직원이담당자로배임..문제있음)
insert into tblProject (prjName, staff)
values ('주차장유료화','하하하');
--s-6
--문제점: 프로젝트담당자는반드시tblStaff에존재하는인물이어야한다.
-- MS-SQL : 관계형데이터베이스(Relation Data Base)
--관계(Relation)
-- : 테이블과테이블간의관계(컬럼과컬럼의관계)
-- : 데이터무결성을유지하기위해..
drop table tblStaff;
drop table tblProject;
create table tblStaff
(
name nvarchar(10) not null primary key, --직원명
salary int not null, --월급
addr nvarchar(20) not null -- 주소
)
create table tblProject
(
prjID int identity(1,1) not null primary key, -- 프로젝트번호
prjName nvarchar(30) not null, --프로젝트명
staff nvarchar(10) not null --담당직원명
references tblStaff(name) -- 외래키(Foreign Key)가참조하는것은, 반드시PK로지정되어있어야한다.
)
drop table tblMember;
drop table tblGenre;
drop table tblVideo;
drop table tblRent;
---비디오대여점
---회원테이블
create table tblMember
(
pk int identity(1,1) not null primary key,-- 회원번호
name nchar(5) not null, -- 회원명
grade int not null, --회원등급(1=준, 2=정, 3=우수)
bYear int not null, --생년(성인)
tel varchar(15) not null, --연락처
addr nvarchar(50) null, -- 주소
[money] int not null -- 예시금
)
--비디오장르테이블(장르별대여가격이다름)
create table tblGenre
(
name nchar(5) not null primary key, --장르명
price int not null, --대여가격
period int not null -- 대영기간
)
--비디오테이블(상품)
create table tblVideo
(
pk int identity(1,1) not null primary key, -- 테잎번호
name nvarchar(30) not null, --제목
num int not null, -- 보유갯수
company nvarchar(20) null, --제작사
director nvarchar(20) null, --감독
major nvarchar(20) null, -- 주연배우
genre nchar(5) not null references tblGenre(name) --장르
)
-- 대여테이블
create table tblRent
(
pk int identity(1,1) not null primary key,--대여번호
who int not null references tblMember(pk), -- 대여회원번호
what int not null references tblVideo(pk), --비디오번호
rentDate datetime not null default(getdate()),--대여날짜
retDate datetime null -- 반납날짜
)
--상품테이블
create table tblProduct
(
product nvarchar(20) not null, -- 상품명
company nvarchar(20) not null -- 제조사
references tblCompany(company),
price int not null -- 가격
)
--제조사테이블
create table tblCompany
(
company nvarchar(20) not null primary key, --제조사명
addr nvarchar(50) not null, -- 주소
tel varchar(15) not null -- 연락처
)
insert into tblCompany (company, addr, tel)
values ('농심','서울시','02-123-4567');
insert into tblCompany (company, addr, tel)
values ('오리온','인천시','032-123-4567');
insert into tblProduct (product, company, price)
values ('신라면','농심',800);
insert into tblProduct (product, company, price)
values ('초코파이','오리온',500);
insert into tblProduct (product, company, price)
values ('너구리','농심',700);
select * from tblProduct;
select * from tblCompany;
-----------------------------------2012.04.02 2, 3교시------------------------------------------
--고객테이블+ 판매테이블
--고객명연락처주소판매상품개수판매일
create table tblJoinCustomer
(
name nvarchar(5) not null primary key, --고객명
tel nchar(15) not null, -- 전화번호
addr nvarchar(50) not null -- 주소
)
create table tblJoinSales
(
orderNo int identity(1,1) not null primary key,
customer nvarchar(5) not null
references tblJoinCustomer(name), -- 구매고객명(***)
item nchar(20) not null, -- 판매물품
num int not null, --판매갯수
[date] datetime not null default(getdate()) -- 판매시간
)
-- tblJoinCustomer
INSERT INTO tblJoinCustomer VALUES ('송골매', '123-4567', '서울시');
INSERT INTO tblJoinCustomer VALUES ('코요테', '111-1111', '인천시');
INSERT INTO tblJoinCustomer VALUES ('핑클', '222-2222', '대전시');
INSERT INTO tblJoinCustomer VALUES ('핫', '333-3333', '부산시');
INSERT INTO tblJoinCustomer VALUES ('세스', '444-4444', '광주시');
-- tblJoinSales
INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('송골매', '카세트', 1);
INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('핑클', '팥빙수', 4);
INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('핑클', '머리핀', 4);
INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('코요테', '컴퓨터', 2);
INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('베이비복스', '애기상자', 5);
INSERT INTO tblJoinSales (Customer, Item, Num) VALUES ('터보', '자외선차단크림', 2);
select * from tblJoinSales;
select * from tblJoinCustomer;
--테이블들이관계가짐!!
--한쪽(PK) <-> 한쪽(FK)
--조인(Join)
--: 최종결과셋이2개이상의테이블들의컬럼으로부터생성
--서브쿼리
-- : 최종결과셋이1개의테이블의컬럼으로부터생성
--조인의종류
--1. 단순Join(Cross Join) : X
--2. 내부Join(Inner Join) : ***
--3. 외부Join(Outer Join) : ****
--4. 셀프Join(Self Join) : 드물게...사용할수밖에없는상황에서만..
--상품테이블
create table tblProductCompany
(
product nvarchar(20) not null primary key, -- 상품명
company nvarchar(20) not null, -- 제조사
price int not null, -- 가격
addr nvarchar(50) not null, -- 주소
tel varchar(15) not null -- 연락처
)
insert into tblProductCompany (product, company, price, addr, tel)
values ('새우깡','농심',700,'서울시','02-585-8585');
insert into tblProductCompany (product, company, price, addr, tel)
values ('신라면','농심',800,'서울시','02-585-8585');
select * from tblProductCompany;
select * from tblCompany;
select * from tblProduct;
--신라면이얼마입니까?
select price from tblProduct where product = '신라면';
--s-1
select price from tblProductCompany where product = '신라면';
--s-2
--농심의연락처?
select tel from tblCompany where company = '농심';
--s-3
select tel from tblProductCompany where company = '농심';
--s-4
--신라면을판매하는회사의연락처?
select company from tblProduct
where product = '신라면';
--s-5
select tel from tblCompany
where company = '농심';
--s-6
select tel from tblCompany
where company = (select company from tblProduct
where product = '신라면');
--s-6
--아래이것은훨씬위에보다간편하지만, DB 특성상겹치는자료들을최대한줄이는것이
--더욱더관리하기효율적이기때문에아래의간편한문구때문에테이블을합친다는것은
--말이안되는행위이다.
select tel from tblProductCompany where product = '신라면';
--s-7
--신라면(조건)의가격(출력)과판매회사의지역(출력)?
-- : 800, 서울시
select price, tel from tblProductCompany
where product = '신라면';
--inner join
--서로다른테이블의컬럼을하나의결과값으로출력해줄수있게해주는도구.
select * from tblCompany
inner join tblProduct
on tblCompany.company = tblProduct.company;
--s-9
select * from tblProductCompany;
select tblCompany.company, tblProduct.price ,tblCompany.addr from tblCompany
inner join tblProduct
on tblCompany.company = tblProduct.company
where tblProduct.product = '신라면';
--s-8
select * from tblCompany;
--s-11
select * from tblproduct;
--s-12
--1. 단순Join
select * from tblCompany, tblProduct;
select * from tblCompany
cross join tblProduct;
--s-10
--2. 내부Join
-- : 내부Join은어떤테이블이먼저와도상관이없다.
-- : join을맺을때는사전에테이블끼리foreign키로묶여있어야함.
-- : inner join은전제조건이단순조인이지만, 조건을걸어서일부레코드만보는것이다.
-- : 그일부조건은, on 이라는명령으로처리할수있다.
-- : 단순Join에서, 의미있는데이터만가져온것이바로inner Join이다.
select tblProduct.product, tblProduct.price, tblCompany.tel from tblCompany
inner join tblProduct
on tblCompany.company = tblProduct.company;
--s-13
select * from tblCompany, tblProduct;
--s-14
select p.product, p.price, c.tel from tblCompany as c
inner join tblProduct as p
on c.company = p.company;
--s-15
--판단기준?
-- : 최종원하는데이터가1개테이블안에..? 2개테이블이상?
--춘천시(tblStaff)에사는직원이담당한프로젝트명(tblProject)?
-- : 서브쿼리
select prjName from tblProject
where staff = (select name from tblstaff
where addr = '춘천시');
--s-17
--춘천시(tblStaff)에사는직원이담당한프로젝트명(tblProject)과그직원월급(tblStaff)?
-- : 조인
select * from tblProject as p
inner join tblStaff as s
on s.name = p.staff
--s-16
select p.prjname, s.salary from tblProject as p
inner join tblStaff as s
on s.name = p.staff
where s.addr = '춘천시';
--s-18
select * from tblStaff;
select * from tblProject;
-----------------------------------2012.04.02 4교시----------------------------------------
-- 대여료(tblGenre)가2000원인비디오의제목(tblVideo)과대여기간(tblGenre)을출력
select * from tblVideo as v
inner join tblGenre as g
on v.genre = g.name;
use pubs;
select * from titles;
select * from publishers;
--책의제목과출판사명을동시에출력하시오.
select * from titles as t
inner join publishers as p
on t.pub_id = p.pub_id;
--3개테이블Join
--titles - titleauthor - authors
--책의제목(titles)과저자명(authors)을동시에출력하시오.
select t.title,a.au_fname from titles as t
inner join titleauthor as ta
on t.title_id = ta.title_id
inner join authors as a
on a.au_id = ta.au_id;
-----------------------------2012. 04. 02-----오후5교시-------------------------------------
use sqlex
--땡칠이라는주연이맡은비디오는어떤것? 빌리는데얼마?
select v.name, g.price from tblVideo as v
inner join tblGenre as g
on v.genre = g.name
where v.major = '땡칠이';
--누가? 무엇을빌려갔는지? : 사람이름, 테잎이름출력
select * from tblrent;
select m.name, v.name, r.rentDate from tblMember as m
inner join tblRent as r
on m.pk = r.who
inner join tblVideo as v
on r.what = v.pk
--outer join
--외부조인
-- : inner join + a
-- : 2개이상의테이블을inner join 한결과에
-- : 둘중1개의테이블의모든레코드를합한결과를반환
-- : 방향이있음(left, right)
insert into tblStaff (name, salary, addr)
values ('가가가',200,'서울시');
insert into tblStaff (name, salary, addr)
values ('나나나',200,'서울시');
select * from tblStaff
select * from tblProject;
--1. 단순join
select * from tblstaff, tblProject
--2. inner join
-- : 직원들중현재프로젝트를진행하고있는직원과,
-- 그프로젝트정보를가져오시오.
select * from tblstaff as s
inner join tblProject as p
on s.name = p.staff
--3. outer join
--모든직원의정보와, 그중진행되고있는담당자의프로젝트정보까지가져오시오.
--방향이중요하다!!
--left 사용시outer join 기준으로왼쪽,left가왼쪽테이블을이야기한다.
--right 사용시outer join 기준으로오른쪽,right는오른쪽테이블을이야기한다.
-- : tblStaff과tblProject의inner join 결과+ tblStaff의모든레코드
select * from tblstaff as s
left outer join tblProject as p
on s.name = p.staff
--가지고있는비디오테잎의제목을출력하고그테잎들이속한장르명을출력하시오.
select * from tblVideo as v
inner join tblGenre g
on v.genre = g.name
select * from tblVideo as v
right outer join tblGenre g
on v.genre = g.name
--tblGenre의모든행(outer)은출력!! + tblVideo 데이터도같이출력(inner)
select * from tblVideo as v
right outer join tblGenre g
on v.genre = g.name
--s-20
--물건을구매한이력이있는회원정보와구매정보를출력하시오. (inner)
--물건을구매한이력이있거나, 없어도회원정보와구매정보를출력하시오. (outer)
--셀프join
-- : 자신이자신과Join
create table tblemployee
(
name nchar(3) not null primary key, -- 직원명
manager nchar(3) null, -- 자신의담당자
department nchar(3) not null --소속부서
)
insert into tblEmployee values('나사장',null,'사장');
insert into tblEmployee values('김부장','나사장','총무부');
insert into tblEmployee values('김과장','김부장','총무부');
insert into tblEmployee values('김대리','김과장','총무부');
insert into tblEmployee values('이부장','나사장','영업부');
insert into tblEmployee values('이과장','이부장','영업부');
insert into tblEmployee values('이사원','이과장','영업부');
select * from tblEmployee
select * from tblEmployee as e1
inner join tblEmployee as e2
on e1.manager = e2.name;
--문제
--1. tblStaff, tblProject에서서울시에사는직원을
-- 제외한나머지직원들의이름, 월급, 담당프로젝트명출력
use sqlex
select s.name, s.salary,p.prjName from tblStaff as s
inner join tblProject as p
on s.name = p.staff
where s.addr <> '서울시';
--name salary prjName
------------ ----------- ------------------------------
--김상형 150 홍콩수출건
--김상형 150 TV 광고건
--김상형 150 매출분석건
--(3개행이영향을받음)
--2. tblJoinCustomer, tblJoinSales에서종류에상관없이상품을2개이상구매한회원의
-- 연락처, 이름, 구매상품명, 수량을출력하시오.
select c.tel, c.name, s.item, s.num from tblJoinCustomer as c
inner join tbljoinsales as s
on c.name = s.customer
where s.num >= 2
--tel name item num
----------------- ----- -------------------- -----------
--222-2222 핑클 팥빙수 4
--222-2222 핑클 머리핀 4
--111-1111 코요테 컴퓨터 2
--(3개행이영향을받음)
--3. 비디오에서모든비디오제목, 수량, 대여가격을출력하시오.
use sqlex
select v.name, v.num, g.price from tblVideo as v
inner join tblGenre as g
on v.genre = g.name
--name num price
-------------------------------- ----------- -----------
--영구와땡칠이 5 1000
--어쭈구리 5 1000
--털미네이터 3 1500
--육복성 3 2000
--뽀뽀할까요 6 2000
--우정과영혼 2 2000
--주라기유원지 1 1500
--타이거킹 4 1000
--텔미에브리딩 10 2000
--동무 7 1500
--공동경쟁구역 2 1500
--(11개행이영향을받음)
--4. 비디오에서2007년2월에대여된구매내역을출력하시오. (대여회원명, 비디오명, 대여가격)
select * from tblrent
select * from tblGenre
select * from tblVideo
--m.name, v.name, g.price, r.rentDate
select m.name, v.name, g.price, r.rentDate from tblRent as r
inner join tblMember as m
on r.who = m.pk
inner join tblVideo as v
on r.what = v.pk
inner join tblGenre as g
on v.genre = g.name
where r.rentDate >= '2007-02-01' and r.rentDate < '2007-03-01'
--name name money rentDate
------- ------------------------------ ----------- -----------------------
--강감찬 어쭈구리 0 2007-02-02 00:00:00.000
--유관순 털미네이터 20000 2007-02-03 00:00:00.000
--이율곡 털미네이터 15000 2007-02-04 00:00:00.000
--신숙주 뽀뽀할까요 0 2007-02-05 00:00:00.000
--김유신 어쭈구리 10000 2007-02-10 00:00:00.000
--(5개행이영향을받음)
--5. 비디오에서현재반납을안한회원과그테잎제목을출력하시오.(retDate컬럼이null)
select m.name, v.name from tblrent as r
inner join tblmember as m
on r.who = m.pk
inner join tblVideo as v
on r.what = v.pk
where retDate is null
--name name
------- ------------------------------
--김유신 영구와땡칠이
--유관순 털미네이터
--신숙주 뽀뽀할까요
--김유신 어쭈구리
--(4개행이영향을받음)
--6. pubs에서'trad_cook'과'mod_cook'에속한책들의
--title id, title 과저자(lname,fname)과저자의연락처
use pubs;
select * from titles
select * from titleauthor
select * from authors
select t.title_id, t.title, a.au_lname, a.au_fname, a.phone from titles as t
inner join titleauthor as ta
on t.title_id = ta.title_id
inner join authors as a
on ta.au_id = a.au_id
where t.[type] in ('trad_cook','mod_cook')
--title_id title au_lname au_fname phone
---------- -------------------------------------------------------------------------------- ---------------------------------------- -------------------- ------------
--MC2222 Silicon Valley Gastronomic Treats del Castillo Innes 615 996-8275
--MC3021 The Gourmet Microwave DeFrance Michel 219 547-9982
--MC3021 The Gourmet Microwave Ringer Anne 801 826-0752
--TC3218 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean Panteley Sylvia 301 946-8853
--TC4203 Fifty Years in Buckingham Palace Kitchens Blotchet-Halls Reginald 503 745-6402
--TC7777 Sushi, Anyone? O'Leary Michael 408 286-2428
--TC7777 Sushi, Anyone? Gringlesby Burt 707 938-6445
--TC7777 Sushi, Anyone? Yokomoto Akiko 415 935-4228
--(8개행이영향을받음)
--7. 비디오에서모든테잎의제목과제작사를출력하고,
-- 그중빌려간이력이있는테잎은누가빌려갔었는지같이출력
use sqlex
select * from tblrent
select v.name, v.company, m.name from tblvideo as v
left outer join tblrent as r
on v.pk = r.what
left outer join tblMember as m
on r.who = m.pk
--name company name
-------------------------------- -------------------- -----
--영구와땡칠이 영구필름 김유신
--어쭈구리 에로프로덕션 강감찬
--어쭈구리 에로프로덕션 김유신
--털미네이터 파라마운트 유관순
--털미네이터 파라마운트 이율곡
--육복성 대만영화사 NULL
--뽀뽀할까요 뽀뽀사 신숙주
--우정과영혼 파라마운트 NULL
--주라기유원지 NULL NULL
--타이거킹 Walt NULL
--텔미에브리딩 영구필름 NULL
--동무 부산필름 NULL
--공동경쟁구역 뽀뽀사 NULL
--(13개행이영향을받음)
-------------------------2012 04 02----------오후6교시----------------------------------
--union
--join : 두테이블이상의특정컬럼들을묶어서하나의결과셋
--union : 두개이상의결과셋을하나로묶어서하나의결과셋
use sqlex
select * from tblVideo where genre = '액션';
--s-22
select * from tblVideo where genre = '코미디';
--s-23
select * from tblVideo where genre in ('액션','코미디');
--s-24
select * from tblVideo where genre = '액션' union
select * from tblVideo where genre = '코미디';
--s-25
--기업, 부서별게시판(영업부게시판, 총무부게시판...)
create table tblBoard1 -- 영업부게시판
(
seq int identity(1,1) not null primary key, -- 글번호
title nvarchar(50) not null, -- 제목
name nvarchar(10) not null, -- 글쓴이
content varchar(1000) not null, -- 내용
regDate datetime not null default(getdate()) -- 글쓴시간
)
create table tblBoard2 --총무부게시판
(
seq int identity(1,1) not null primary key, -- 글번호
title nvarchar(50) not null, -- 제목
name nvarchar(10) not null, -- 글쓴이
content varchar(1000) not null, -- 내용
regDate datetime not null default(getdate()) -- 글쓴시간
)
insert into tblBoard1 values
('영업부게시물입니다~~1','영업부과장','글내용', default),
('영업부게시물입니다~~2','영업부과장','글내용', default),
('영업부게시물입니다~~3','영업부과장','글내용', default),
('영업부게시물입니다~~4','영업부과장','글내용', default),
('영업부게시물입니다~~5','영업부과장','글내용', default)
insert into tblBoard2 values
('총무부게시물입니다~~1','총무부과장','글내용', default),
('총무부게시물입니다~~2','총무부과장','글내용', default),
('총무부게시물입니다~~3','총무부과장','글내용', default),
('총무부게시물입니다~~4','총무부과장','글내용', default),
('총무부게시물입니다~~5','총무부과장','글내용', default)
--영업부직원이자기부서게시판을보고싶엉~!
select * from tblBoard1
--총무부직원이자기부서게시판을보고싶엉~!
select * from tblBoard2
--s-26
--사장님이모든부서의게시판내용을다봅니다.
select * from tblBoard1 union
select * from tblBoard2
--s-27
--싸이월드방명록-> 2011년이전게시물보기...
--=why? 년도마다테이블을나누어놔서.(실제로이런지는잘모른다.)
-- tblGuest2005
-- tblGuest2006
-- tblGuest2007
-- tblGuest2008
-- tblGuest2009
-- tblGuest2010
-- tblGuest2011
-- tblGuest2012
--view(뷰)
-- : 임시테이블, 뷰테이블, 가상테이블
-- : 뷰는원본테이블데이터를복사, 저장하는것이아니라,
-- 원본테이블로사용할SQL 쿼리를저장하는객체
-- : 뷰를생성한이후에편집이되는모든데이터도뷰에반영이됨.
-- : 뷰를대상으로update, insert, delete는금지!!!!
-- : 뷰는오로지select 전용
--1. 뷰객체생성
create view vwVideo
as
select * from tblVideo;
--2. 뷰접근(사용) : 테이블사용법과동일
select * from vwVideo;
-- 뷰를생성하는목적
-- : 수평뷰, 수직뷰
create view vwVideoVertical
as
select name, genre from tblVideo;
select * from vwVideoVertical
use pubs
select * from titles;
select * from titles;
--pubdate만빼고셀릭트할일이잦은일이있을때!! 아래와같이쓸것인가.
select title_id, title, [type], pub_id, price,advance, royalty, ytd_sales,notes from titles;
--이럴때뷰로만들어두면편하다.
create view vwTitles
as
select title_id, title, [type], pub_id, price,advance, royalty, ytd_sales,notes from titles;
select * from vwTitles;
--가격이15불이상서적의모든데이터가져오기
select * from titles where price >= 15;
create view vwTitleHorizontal
as
select * from titles where price >= 15;
--vwTitleHorizontal : 15불이상인책들이들어있는테이블
select * from vwTitleHorizontal
use sqlex
create view testView
as
select m.name as [회원명], v.name[비디오제목], g.price, r.rentDate from tblRent as r
inner join tblMember as m
on r.who = m.pk
inner join tblVideo as v
on r.what = v.pk
inner join tblGenre as g
on v.genre = g.name
where r.rentDate >= '2007-02-01' and r.rentDate < '2007-03-01'
select * from testView;
--tblStaff
create view vwStaff
as
select * from tblStaff where salary > 150;
select * from vwStaff;
insert into tblStaff values ('다다다',250,'부산시');
--150-809 서울영등포구당산동가 311-1 동화빌딩4층401호
---뷰에insert, update, delete 했을때
create view vwStaff2
as
select salary from tblStaff;
select * from vwStaff2
--아래는안된다!!!!!! 원래tblStaff라는테이블은
--name, salary, addr이있기때문에
--데이터를하나만넣은상황이된다.
insert into vwStaff2 (salary) values (300);