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 NVARCHAR (15) NOT NULL, -- 전화번호
Addr NVARCHAR (50) NULL, -- 주소
Money INT NOT NULL, -- 예치금
);
select *
from tblMember
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('김유신',1,1970,'123-4567','12-3번지 301호',10000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('강감찬',1,1978,'111-1111','777-2번지 101호',0);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('유관순',1,1978,'222-2222','86-9번지',20000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('이율곡',1,1982,'333-3333',NULL,15000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('신숙주',1,1988,'444-4444','조선 APT 1012호',0);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('안중근',1,1981,'555-5555','대한빌라 102호',1000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('윤봉길',1,1981,'666-6666','12-1번지',0);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('이순신',1,1981,'777-7777',NULL,1500);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('김부식',1,1981,'888-8888','73-6번지',-1000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('박지원',1,1981,'999-9999','조선 APT 902호',1200);
select *
from tblMember;
CREATE TABLE tblGenre
(
Name NCHAR (5) NOT NULL PRIMARY KEY, -- 이름
Price INT NOT NULL, -- 대여 가격
Period INT NOT NULL, -- 대여 날짜
);
-- 비디오 유형 데이터
INSERT INTO tblGenre VALUES ('액션',1500,2)
INSERT INTO tblGenre VALUES ('에로',1000,1)
INSERT INTO tblGenre VALUES ('어린이',1000,3)
INSERT INTO tblGenre VALUES ('코미디',2000,2)
INSERT INTO tblGenre VALUES ('멜로',2000,1)
INSERT INTO tblGenre VALUES ('기타',1800,2)
-- 비디오 테이블
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), -- 분류.
);
-- 비디오 데이터
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('영구와 땡칠이',5,'영구필름','심영래','땡칠이','어린이');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('어쭈구리',5,'에로 프로덕션','김감독','박에로','에로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('털미네이터',3,'파라마운트','James','John','액션');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('육복성',3,'대만영화사','홍군보','생룡','코미디');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('뽀뽀할까요',6,'뽀뽀사','박감독','최지후','멜로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('우정과 영혼',2,'파라마운트','James','Mike','멜로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('주라기 유원지',1,NULL,NULL,NULL,'액션');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('타이거 킹',4,'Walt','Kebin','Tiger','어린이');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('텔미 에브리 딩',10,'영구필름','강감독','심으나','멜로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('동무',7,'부산필름','박감독','장동근','액션');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('공동경쟁구역',2,'뽀뽀사','박감독','이병흔','액션');
-- 대여 테이블
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, -- 반납한 날짜
Remark NVARCHAR (256) NULL
);
-- 대여 데이터
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (1,1,'2007-01-01',NULL);
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (2,2,'2007-02-02','2001-02-03');
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (3,3,'2007-02-03',NULL);
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (4,3,'2007-02-04','2001-02-08');
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (5,5,'2007-02-05',NULL);
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (1,2,'2007-02-10',NULL);
select * from tblMember;
select * from tblGenre;
select * from tblVideo;
select * from tblRent;
-- 하위쿼리, 서브쿼리
-- : 기존의 쿼리에 또 다른 select가 삽입된 형태의 쿼리
-- 나이가 젤적은 사람들의 이름
select 이름
from 주소록
where 나이 = (select min(나이) from 주소록);
-- 평균나이보다 많은사람들의 이름
select 이름, 나이
from 주소록
where 나이 >= (select avg(나이)from 주소록);
-- 가장 많이 가지고 있는 테잎은 제목?
select Num, Name
from tblVideo
where Num = (select max(Num)
from tblvideo);
-- 대여 가격이 2,000원인 테잎의 제목?
select *
from tblVideo
where Genre in (select Name from tblGenre where Price = 2000);
-- "뽀뽀할까요" 테잎을 빌려간 사람의 이름?
select Pk from tblVideo where name = '뽀뽀할까요'; -- 5번 테잎
select Who from tblRent where What = 5; -- 5번 회원
select Name from tblMember where Pk = 5;
select Name from tblMember where Pk in (select Who from tblRent where What = (select Pk from tblvideo where name = '털미네이터'));
-- 대여점에서 가장 테잎을 많이 대여해간 회원의 이름?(우수고객)
SELECT count(Who), Who
FROM tblRent
GROUP BY Who;
SELECT top 1 Who
FROM tblRent
GROUP BY Who
ORDER BY count(Who) DESC;
SELECT Name
FROM tblMember
WHERE Pk = (SELECT top 1 Who
FROM tblRent
GROUP BY Who
ORDER BY count(Who) DESC);
-- union
-- : 두개 이상의 select 결과값(ResultSet)을 통합해서 하나의 테이블로 만드는 구문
SELECT *
FROM tblVideo
WHERE genre = '액션'
UNION
SELECT *
FROM tblVideo
WHERE genre = '멜로';
-- 기업 게시판
-- 부서별 게시판(영업부, 총무부, 기획부)
CREATE TABLE 영업부
(
seq int not null primary key,
title varchar(100) not null,
name varchar(20) not null,
content varchar(1000) not null,
regDate datetime not null
);
CREATE TABLE 총무부
(
seq int not null primary key,
title varchar(100) not null,
name varchar(20) not null,
content varchar(1000) not null,
regDate datetime not null
);
CREATE TABLE 기획부
(
seq int not null primary key,
title varchar(100) not null,
name varchar(20) not null,
content varchar(1000) not null,
regDate datetime not null
);
INSERT INTO 영업부(seq, title, name, content, regDate)
values('1','영업부게시물~','영업부과장','테스트',getDate());
INSERT INTO 영업부(seq, title, name, content, regDate)
values('2','영업부게시물~','영업부대리','테스트',getDate());
INSERT INTO 영업부(seq, title, name, content, regDate)
values('3','영업부게시물~','영업부사원','테스트',getDate());
INSERT INTO 총무부(seq, title, name, content, regDate)
values('1','총무부게시물~','총무부과장','테스트',getDate());
INSERT INTO 총무부(seq, title, name, content, regDate)
values('2','총무부게시물~','총무부대리','테스트',getDate());
INSERT INTO 총무부(seq, title, name, content, regDate)
values('3','총무부게시물~','총무부사원','테스트',getDate());
INSERT INTO 기획부(seq, title, name, content, regDate)
values('1','기획부게시물~','기획부과장','테스트',getDate());
INSERT INTO 기획부(seq, title, name, content, regDate)
values('2','기획부게시물~','기획부대리','테스트',getDate());
INSERT INTO 기획부(seq, title, name, content, regDate)
values('3','기획부게시물~','기획부사원','테스트',getDate());
SELECT * FROM 영업부
UNION
SELECT * FROM 총무부
UNION
SELECT * FROM 기획부;
-- 사장님.. 모든 부서의 게시물 보고 싶음!!
SELECT *
FROM tblVideo
union
SELECT *
FROM tblMember;
-- 싸이월드
-- 게시판(2010년 이전 게시물보기..)
--서울시 거주자 + 나이가 28살 이상
select * from 주소록 where 주소 = '서울시'
union
select * from 주소록 where 나이 >= 28;
-- 20~23세와 28세~30세의 테이블 리스트
select * from 주소록 where 나이 between 20 and 23
union
select * from 주소록 where 나이 between 28 and 30;
-- view객체
-- 테이블과 유사한 객체
-- 뷰 테이블
-- 가상 테이블
-- 테이블의 복사본(반드시 현재 존재하는 테이블이 기반이 되어 뷰가 생성)
-- 1. view 객체 생성
CREATE VIEW 주소록뷰
AS
SELECT * FROM 주소록;
-- 2. 뷰 호출(접근) : 테이블 사용법과 동일
SELECT *
FROM 주소록뷰
WHERE 주소 = '서울시';
-- '서울시' 사는 사람을 대상으로 일이 반복!!!!!
-- 자주쓰는 셀렉트문을 모아 임시문을 만들어 놓기 위해서다.
SELECT COUNT(*)
FROM 주소록뷰
WHERE 주소 = '서울시';
SELECT AVG(나이)
FROM 주소록뷰
WHERE 주소 = '서울시';
CREATE VIEW 서울시민
AS
SELECT * FROM 주소록 WHERE 주소 = '서울시';
SELECT AVG(나이)
FROM 서울시민;
-- 뷰 : 기본뷰, 수직뷰, 수평뷰
CREATE VIEW 주소록수직뷰
AS
SELECT 이름,주소 FROM 주소록;
SELECT *
FROM 주소록수직뷰;
CREATE VIEW 주소록수평뷰
AS
SELECT TOP 10 * FROM 주소록 ORDER BY 나이 DESC;
SELECT *
FROM 주소록수평뷰;
--****************************************************************************************
USE SQLEx
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 --담당직원명(tblStaff 직원명)
)
--비디오대여점
--회원테이블
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(50) null, --주소
addr nvarchar(50) null,
[money] int not null --예치금
)
DROP TABLE TBLMEMBER
--비디오장르테이블(장르별대여가격이다름)
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 --장르
)
--대여테이블
CREATE TABLE tblRent
(
pk int identity(1,1) not null primary key, --대여번호
who int not null, --대여회원번호
what int not null, --비디오번호
rentDate DateTime not null default(GETDATE()), --대여날짜
retDate datetime null --반납날짜
)
-- tblMember Data
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('김유신',1,1970,'123-4567','12-3번지301호',10000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('강감찬',1,1978,'111-1111','777-2번지101호',0);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('유관순',1,1978,'222-2222','86-9번지',20000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('이율곡',1,1982,'333-3333',NULL,15000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('신숙주',1,1988,'444-4444','조선APT 1012호',0);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('안중근',1,1981,'555-5555','대한빌라102호',1000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('윤봉길',1,1981,'666-6666','12-1번지',0);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('이순신',1,1981,'777-7777',NULL,1500);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('김부식',1,1981,'888-8888','73-6번지',-1000);
INSERT INTO tblMember (Name,Grade,Byear,Tel,Addr,Money) VALUES ('박지원',1,1981,'999-9999','조선APT 902호',1200);
-- tblGenre Data
INSERT INTO tblGenre VALUES ('액션',1500,2)
INSERT INTO tblGenre VALUES ('에로',1000,1)
INSERT INTO tblGenre VALUES ('어린이',1000,3)
INSERT INTO tblGenre VALUES ('코미디',2000,2)
INSERT INTO tblGenre VALUES ('멜로',2000,1)
INSERT INTO tblGenre VALUES ('기타',1800,2)
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('영구와땡칠이',5,'영구필름','심영래','땡칠이','어린이');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('어쭈구리',5,'에로프로덕션','김감독','박에로','에로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('털미네이터',3,'파라마운트','James','John','액션');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('육복성',3,'대만영화사','홍군보','생룡','코미디');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('뽀뽀할까요',6,'뽀뽀사','박감독','최지후','멜로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('우정과영혼',2,'파라마운트','James','Mike','멜로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('주라기유원지',1,NULL,NULL,NULL,'액션');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('타이거킹',4,'Walt','Kebin','Tiger','어린이');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('텔미에브리딩',10,'영구필름','강감독','심으나','멜로');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('동무',7,'부산필름','박감독','장동근','액션');
INSERT INTO tblVideo (Name, Num, Company, Director, Major, Genre) VALUES ('공동경쟁구역',2,'뽀뽀사','박감독','이병흔','액션');
-- tblRent Data
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (1,1,'2007-01-01',NULL);
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (2,2,'2007-02-02','2001-02-03');
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (3,3,'2007-02-03',NULL);
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (4,3,'2007-02-04','2001-02-08');
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (5,5,'2007-02-05',NULL);
INSERT INTO tblRent (Who, What, Rentdate, Retdate) VALUES (1,2,'2007-02-10',NULL);
-- 하위쿼리, 서브쿼리
-- : 기존의쿼리내에또다른select가포함된형태의쿼리
select * from tblVideo;
-- 가장많은재고를보유한테잎의갯수?
select max(num) from tblVideo; --10개
-- 가장많은재고를보유한테잎의제목?
select name from tblVideo
where num = 10;
--서브쿼리사용
select name from tblVideo
where num = (select max(num) from tblVideo);
-- 가장적은재고를보유한테잎의제목?
select name, num from tblVideo
where num = (select min(num) from tblVideo);
-- 유럽에서가장인구수가많은국가명?
select max(popu) from tblCountry; --120660
select name from tblCountry where popu = 120660;
select name, popu from tblCountry
where popu =
(select max(popu) from tblCountry
where cont = 'EU');
-- 영구와땡칠이(tblVideo)의대여가격(tblGenre)?
select genre from tblVideo
where name = '영구와땡칠이'; --어린이
select price from tblGenre
where name = '어린이'; --어린이장르는1000원입니다..
select price, period from tblGenre
where name = (select genre from tblVideo
where name = '영구와땡칠이');
select * from tblStaff;
select * from tblProject;
-- 서울시에사는직원이무슨프로젝트를담당하는지궁금
select name from tblStaff
where addr = '서울시';
select prjName from tblProject
where staff = '김기문';
select prjName from tblProject
where staff = (select name from tblStaff
where addr = '서울시');
