제품, 고객, 판매 연습을 위한 테이블 생성

 

 

data 연습

CREATE DATABASE OnlineShopDB;
go

CREATE TABLE customersTbl
(
	custId		varchar(8) primary key,
	custName	nvarchar(10) not null,
	birthDate	date not null,
	phone		varchar(11) not null,
	addr		nvarchar(5) not null,
	regDate		date not null,
	grade		char(1) default null
);

CREATE TABLE goodsTbl
(
	goodsId		nvarchar(12) primary key,
	goodsName	nvarchar(20) not null,
	supplier	nvarchar(20) not null
);

CREATE TABLE salesTbl
(
	seqNo		int identity primary key,	--시퀀스 자동증가
	custId		varchar(8) foreign key
	references	customersTbl(custId),
	goodsId		nvarchar(12)
	foreign key references goodsTbl(goodsId),
	saleDate	date not null,
	unitPrice	int not null,
	quantity	int not null,
	sales		as unitPrice * quantity
);
go

exec sp_help "goodsTbl"

exec sp_help "customersTbl"

exec sp_help "salesTbl"

select * from goodsTbl



--INSERT DATA

use OnlineShopDB;
go
insert into customersTbl values('aaaaaaaa', '홍길동', '1975-01-01', '01012345678', '서울', '2018-05-05', default);
insert into customersTbl values('cccccccc', '김유신', '1970-03-03', '01022223333', '경기', '2018-03-12', default);
insert into customersTbl values('jjjjjjjj', '김선달', '1972-10-10', '01033334444', '부산', '2018-04-20', default);
insert into customersTbl values('dddddddd', '박문수', '1968-06-02', '01044445555', '광주', '2017-11-05', default);
insert into customersTbl values('kkkkkkkk', '김철수', '1982-02-25', '01055556666', '대전', '2018-01-10', default);
insert into customersTbl values('bbbbbbbb', '이영희', '1981-04-11', '01066667777', '서울', '2018-08-07', default);
insert into customersTbl values('rrrrrrrr', '이말년', '1973-07-05', '01077778888', '경기', '2017-11-05', default);
insert into customersTbl values('mmmmmmmm', '신문손', '1984-05-11', '01088889999', '강원', '2018-06-17', default);
insert into customersTbl values('ffffffff', '차범금', '1976-12-11', '01099990000', '대구', '2017-10-28', default);
insert into customersTbl values('eeeeeeee', '박지송', '1983-05-07', '01025255656', '부산', '2018-02-16', default);
insert into customersTbl values('oooooooo', '이영자', '1983-05-07', '01035352727', '청주', '2018-02-16', default);
insert into customersTbl values('pppppppp', '류현진', '1983-05-07', '01010103030', '수원', '2018-02-16', default);
insert into customersTbl values('qqqqqqqq', '박찬호', '1983-05-07', '01067672929', '부산', '2018-02-16', default);
insert into customersTbl values('ssssssss', '나대로', '1983-05-07', '01089897272', '강릉', '2018-02-16', default);
insert into customersTbl values('tttttttt', '무대리', '1983-05-07', '01071714949', '경주', '2018-02-16', default);
go

use OnlineShopDB;
go
insert into goodsTbl values('snack001', '양파깡', '농심');
insert into goodsTbl values('snack002', '새우깡', '농심');
insert into goodsTbl values('snack003', '콘??', '크라운');
insert into goodsTbl values('biscuit001', '빼빼로', '롯데');
insert into goodsTbl values('biscuit002', '제크', '롯데');
insert into goodsTbl values('biscuit003', '산도', '크라운');
insert into goodsTbl values('pie001', '초코파이', '오리온');
insert into goodsTbl values('pie002', '몽쉘', '롯데');
insert into goodsTbl values('pie003', '프렌치파이', '해태');
insert into goodsTbl values('snack004', '나초', '롯데');
insert into goodsTbl values('biscuit004', '참크래커', '크라운');
go

use OnlineShopDB;
go
insert into salesTbl values('aaaaaaaa', 'biscuit001', '2018-09-01', 500, 5);
insert into salesTbl values('eeeeeeee', 'snack003', '2018-09-02', 800, 3);
insert into salesTbl values('ffffffff', 'biscuit003', '2018-09-02', 1000, 10);
insert into salesTbl values('kkkkkkkk', 'pie002', '2018-09-03', 2000, 3);
insert into salesTbl values('bbbbbbbb', 'snack002', '2018-09-03', 600, 4);
insert into salesTbl values('dddddddd', 'biscuit002', '2018-09-04', 700, 3);
insert into salesTbl values('jjjjjjjj', 'snack001', '2018-09-04', 500, 2);
insert into salesTbl values('cccccccc', 'pie001', '2018-09-05', 2500, 2);
insert into salesTbl values('mmmmmmmm', 'snack002', '2018-09-05', 550, 10);
insert into salesTbl values('rrrrrrrr', 'pie001', '2018-09-06', 2500, 1);
insert into salesTbl values('rrrrrrrr', 'pie002', '2018-09-06', 2000, 2);
insert into salesTbl values('kkkkkkkk', 'biscuit003', '2018-09-06', 1000, 5);
insert into salesTbl values('eeeeeeee', 'biscuit001', '2018-09-07', 400, 10);
insert into salesTbl values('ffffffff', 'pie002', '2018-09-07', 1500, 3);
go





--Back Up DATA

use tempdb;
go

backup database OnlineShopDB to disk
-- = 'D:\SQL\Backup\OnlineShopDB.back' with init;
= 'C:\SQLQuery\OnlineShopDB.back' with init;






--DATA CONFIRM/CHECK

use OnlineShopDB

select * from customersTbl;

select * from salesTbl;

select * from goodsTbl;

 

 

 

 

 

 

 

 

 

 

 

기본설정

CREATE DATABASE OnlineShopDB;
go

CREATE TABLE customersTbl
(
	custId		varchar(8) primary key,
	custName	nvarchar(10) not null,
	birthDate	date not null,
	phone		varchar(11) not null,
	addr		nvarchar(5) not null,
	regDate		date not null,
	grade		char(1) default null
);

CREATE TABLE goodsTbl
(
	goodsId		nvarchar(12) primary key,
	goodsName	nvarchar(20) not null,
	supplier	nvarchar(20) not null
);

CREATE TABLE salesTbl
(
	seqNo		int identity primary key,	--시퀀스 자동증가
	custId		varchar(8) foreign key
	references	customersTbl(custId),
	goodsId		nvarchar(12)
	foreign key references goodsTbl(goodsId),
	saleDate	date not null,
	unitPrice	int not null,
	quantity	int not null, 
	sales		as unitPrice * quantity
);
go

exec sp_help "goodsTbl"

exec sp_help "customersTbl"

exec sp_help "salesTbl"





--INSERT DATA

use OnlineShopDB;
go
insert into customersTbl values('aaaaaaaa', '홍길동', '1975-01-01', '01012345678', '서울', '2018-05-05', default);
insert into customersTbl values('cccccccc', '김유신', '1970-03-03', '01022223333', '경기', '2018-03-12', default);
insert into customersTbl values('jjjjjjjj', '김선달', '1972-10-10', '01033334444', '부산', '2018-04-20', default);
insert into customersTbl values('dddddddd', '박문수', '1968-06-02', '01044445555', '광주', '2017-11-05', default);
insert into customersTbl values('kkkkkkkk', '김철수', '1982-02-25', '01055556666', '대전', '2018-01-10', default);
insert into customersTbl values('bbbbbbbb', '이영희', '1981-04-11', '01066667777', '서울', '2018-08-07', default);
insert into customersTbl values('rrrrrrrr', '이말년', '1973-07-05', '01077778888', '경기', '2017-11-05', default);
insert into customersTbl values('mmmmmmmm', '신문손', '1984-05-11', '01088889999', '강원', '2018-06-17', default);
insert into customersTbl values('ffffffff', '차범금', '1976-12-11', '01099990000', '대구', '2017-10-28', default);
insert into customersTbl values('eeeeeeee', '박지송', '1983-05-07', '01025255656', '부산', '2018-02-16', default);
insert into customersTbl values('oooooooo', '이영자', '1983-05-07', '01035352727', '청주', '2018-02-16', default);
insert into customersTbl values('pppppppp', '류현진', '1983-05-07', '01010103030', '수원', '2018-02-16', default);
insert into customersTbl values('qqqqqqqq', '박찬호', '1983-05-07', '01067672929', '부산', '2018-02-16', default);
insert into customersTbl values('ssssssss', '나대로', '1983-05-07', '01089897272', '강릉', '2018-02-16', default);
insert into customersTbl values('tttttttt', '무대리', '1983-05-07', '01071714949', '경주', '2018-02-16', default);
go

use OnlineShopDB;
go
insert into goodsTbl values('snack001', '양파깡', '농심');
insert into goodsTbl values('snack002', '새우깡', '농심');
insert into goodsTbl values('snack003', '콘…', '크라운');
insert into goodsTbl values('biscuit001', '빼빼로', '롯데');
insert into goodsTbl values('biscuit002', '제크', '롯데');
insert into goodsTbl values('biscuit003', '산도', '크라운');
insert into goodsTbl values('pie001', '초코파이', '오리온');
insert into goodsTbl values('pie002', '몽쉘', '롯데');
insert into goodsTbl values('pie003', '프렌치파이', '해태');
insert into goodsTbl values('snack004', '나초', '롯데');
insert into goodsTbl values('biscuit004', '참크래커', '크라운');
go

use OnlineShopDB;
go
insert into salesTbl values('aaaaaaaa', 'biscuit001', '2018-09-01', 500, 5);
insert into salesTbl values('eeeeeeee', 'snack003', '2018-09-02', 800, 3);
insert into salesTbl values('ffffffff', 'biscuit003', '2018-09-02', 1000, 10);
insert into salesTbl values('kkkkkkkk', 'pie002', '2018-09-03', 2000, 3);
insert into salesTbl values('bbbbbbbb', 'snack002', '2018-09-03', 600, 4);
insert into salesTbl values('dddddddd', 'biscuit002', '2018-09-04', 700, 3);
insert into salesTbl values('jjjjjjjj', 'snack001', '2018-09-04', 500, 2);
insert into salesTbl values('cccccccc', 'pie001', '2018-09-05', 2500, 2);
insert into salesTbl values('mmmmmmmm', 'snack002', '2018-09-05', 550, 10);
insert into salesTbl values('rrrrrrrr', 'pie001', '2018-09-06', 2500, 1);
insert into salesTbl values('rrrrrrrr', 'pie002', '2018-09-06', 2000, 2);
insert into salesTbl values('kkkkkkkk', 'biscuit003', '2018-09-06', 1000, 5);
insert into salesTbl values('eeeeeeee', 'biscuit001', '2018-09-07', 400, 10);
insert into salesTbl values('ffffffff', 'pie002', '2018-09-07', 1500, 3);
go