use master
create database sqlDB
go
use sqlDB
CREATE TABLE userTbl -- 사용자테이블
( userID nchar(8) NOT NULL PRIMARY KEY, -- 사용자아이디
name nvarchar(10) NOT NULL, -- 이름
birthYear int NOT NULL, -- 출생년도
addr nchar(4) NOT NULL, -- 지역(경기,서울,경남,전북식으로2글자만입력)
mobile1 nchar(3), -- 휴대폰의국번(011, 016, 017, 018, 019, 010 등)
mobile2 nchar(8), -- 휴대폰의나머지전화번호(하이픈제외)
height smallint -- 신장
)
GO
CREATE TABLE buyTbl -- 사용자구매테이블
( num int IDENTITY NOT NULL PRIMARY KEY, -- 순번(PK)
userid nchar(8) NOT NULL --아이디(FK)
FOREIGN KEY REFERENCES userTbl(userid),
prodName nchar(6) NOT NULL, -- 물품명
groupName nchar(4) , -- 분류
price int NOT NULL, -- 단가
amount smallint NOT NULL -- 수량
)
GO
INSERT INTO userTbl VALUES(N'PJS', N'박지성', 1983, N'서울', N'011', N'1111111', 181)
INSERT INTO userTbl VALUES(N'PJY', N'박주영', 1986, N'경기', N'011', N'2222222', 178)
INSERT INTO userTbl VALUES(N'JJJ', N'조재진', 1986, N'충북', N'019', N'3333333', 179)
INSERT INTO userTbl VALUES(N'LCS', N'이천수', 1983, N'인천', N'011', N'4444444', 179)
INSERT INTO userTbl VALUES(N'AJH', N'안정환', 1979, N'강원', NULL , NULL , 182)
INSERT INTO userTbl VALUES(N'KNI', N'김남일', 1977, N'경북', N'016', N'6666666', 183)
INSERT INTO userTbl VALUES(N'LYP', N'이영표', 1983, N'전북', NULL , NULL , 178)
INSERT INTO userTbl VALUES(N'SKH', N'설기현', 1978, N'서울', N'011', N'8888888', 182)
INSERT INTO userTbl VALUES(N'SJK', N'송종국', 1979, N'경기', N'018', N'9999999', 178)
INSERT INTO userTbl VALUES(N'CJC', N'최진철', 1975, N'제주', N'019', N'0000000', 185)
GO
INSERT INTO buyTbl VALUES(N'PJY', N'운동화', NULL , 30, 2)
INSERT INTO buyTbl VALUES(N'PJY', N'노트북', N'전자', 1000, 1)
INSERT INTO buyTbl VALUES(N'LCS', N'모니터', N'전자', 200, 1)
INSERT INTO buyTbl VALUES(N'CJC', N'모니터', N'전자', 200, 5)
INSERT INTO buyTbl VALUES(N'PJY', N'청바지', N'의류', 50, 3)
INSERT INTO buyTbl VALUES(N'CJC', N'메모리', N'전자', 80, 10)
INSERT INTO buyTbl VALUES(N'AJH', N'책' , N'서적', 15, 5)
INSERT INTO buyTbl VALUES(N'SKH', N'책' , N'서적', 15, 2)
INSERT INTO buyTbl VALUES(N'SKH', N'청바지', N'의류', 50, 1)
INSERT INTO buyTbl VALUES(N'CJC', N'운동화', NULL , 30, 2)
INSERT INTO buyTbl VALUES(N'SKH', N'책' , N'서적', 15, 1)
INSERT INTO buyTbl VALUES(N'CJC', N'운동화', NULL , 30, 2)
GO
select * from userTbl
go
select * from dbo.buyTbl
go
select name, birthyear, addr from dbo.userTbl
where name='박지성'
select * from SQLEXPRESS.sqlDB.dbo.userTbl
where birthyear > 1980 and height >180
--1980년 이후에 출생을 했거나,
--신장이 180이상인 사람의 아이디와 이름을 조회
select userid, name from userTbl
where birthyear > 1980 or height >= 180
--키가 180~ 183 사이인 사람에 아이디와 이름
select userid, name from usertbl
where height >=180 and height <= 183
select userid, name from usertbl
where height between 180 and 183
--키가 정확하게 178인사람 또는 180인 사람 또는 182인 사람
select userid, name,height from usertbl
where height = 178 or height = 180 or height = 182
select userid, name, height from usertbl
where height in(178, 180, 182)
use sqlDB
-- 박지성보다 키가큰 사람의 이름과 키를 출력해보자
select * from usertbl where height > 181
select name, height from usertbl where height >
(select height from usertbl where name='박지성')
-- 휴대폰 국번이 019인 사람의 키보다, 키가 크거나 같은 사람을 출력
select height from usertbl
select name, height from usertbl
where height >= (select height from usertbl where mobile1='019')
select name, height from usertbl where mobile1='019'
-- any 서브쿼리 여러개의 결과중에 한가지만 만족하더라도 결과 출력
select name, height from usertbl
where height >= any(select height from usertbl where mobile1='019')
-- all 서브쿼리 여러개의 결과를 모두 만족해야 결과를 출력
select name, height from usertbl
where height >= all(select height from usertbl where mobile1='019')
--ORDER BY DESC//ORDER BY ASC
select * from buyTbl order by num desc
select * from buyTbl order by num asc
select * from buyTbl
select * from buytbl order by price asc
-- 회원 거주지 주소를 출력해보자
select addr from usertbl
select addr from usertbl order by addr
-- 중복요소 제거 DISTINCT
select distinct addr from usertbl order by addr
select top(5) * from usertbl
use master
exec sp_attach_db @dbname=N'AdventureWorks',
@filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf',
@filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf'
use AdventureWorks
select * from Sales.CreditCard
select top(select count(*)/100 from Sales.CreditCard) CreditCardID
from Sales.CreditCard
where CardType = 'Vista'
order by ExpYear, ExpMonth
select count(*)/100 from Sales.CreditCard
select top(1)percent CreditCardID from Sales.CreditCard
where CardType = 'Vista'
order by ExpYear, ExpMonth
select count(*)/100 from Sales.CreditCard
use sqlDB
select top(1) * from usertbl
--select into
--SELECT 복사할열 INTO 새로운 테이블 FROM 기존테이블
select * into usertbl2 from usertbl
--* GROUP BY 및 HAVING 그리고 집계함수
select userid, amount from buytbl order by userid
-- AJH 5, CJC 19, LCS 1, PJY 6, SKH 4
select userid, sum(amount) from buytbl group by userid
-- 별칭을 사용해본다.
select userid as [사용자 아이디],
SUM(amount) AS [총 구매 개수]
from buytbl group by userid
--집계함수 AVG() : 평균을 구한다
--MIN() : 최소값을 구한다
--MAX() : 최대값을 구한다
--COUNT() : 행의 개수를 센다
SELECT AVG(amount) as [평균구매개수] from buytbl
select * from buytbl
--25/12
--having
select userid as [사용자], sum(price*amount) as [총구매금액]
from buytbl
group by userid
having sum(price*amount)>1000
-- compute, compute by, rollup, cube, grouping() 함수
-- select 결과에 대한 요약의 형태를 보여주는 구문
select *, price*amount as[가격] from buytbl
compute sum(price * amount)
compute avg(price * amount)
select *, price*amount as[가격] from buytbl
order by groupName
compute sum(price * amount) by groupName
compute avg(price * amount) by groupName
select * from buytbl
select num, groupName, sum(price * amount) as [비용]
from buytbl
group by groupName, num
with rollup
select groupName, sum(price * amount) as [비용]
, grouping(groupName) as [추가행여부]
from buytbl
group by groupName
with rollup
