데이터베이스 SQL 기초 문법

2011. 7. 9. 23:44

--(1) Database 생성
CREATE DATABASE 재고관리
ON                                   
    ( NAME = 재고관리_D,     
      FILENAME = 'c:\Data\재고관리_D.mdf',
      SIZE = 3,                               
      MAXSIZE = 20,          
      FILEGROWTH = 10%  
    )
LOG ON					        
   ( NAME = 재고관리_L,
      FILENAME = 'c:\Data\재고관리_L.ldf',
      SIZE = 3,
      MAXSIZE = 10,
      FILEGROWTH = 10%
    )

--(2) Table 생성
USE 재고관리
GO 
 
CREATE TABLE 고객
( 고객ID		Char(4)		Primary Key,
  성명		Varchar(10)	NOT NULL,
  전화번호	Varchar(16),
  주소		Varchar(50)
)

CREATE TABLE 제품
( 제품ID		Char(4)		Primary Key,
  제품명	Varchar(20)	NOT NULL,
  유형		Char(1),
  단가		Int
)

CREATE TABLE 창고
( 창고ID		Char(4)		Primary Key,
  창고명	Varchar(20)	NOT NULL,
  전화번호	Varchar(16),
  주소		Varchar(50)
)

CREATE TABLE 창고제품
( 창고ID		Char(4)                NOT NULL,		
  제품ID		Char(4)		NOT NULL,
  재고량		Int,
  안전재고량	Int,
  Constraint	PK_CJ	Primary Key(창고ID,제품ID),
  Constraint       FK_C	Foreign Key(창고ID)
                                          References 창고(창고ID),
  Constraint       FK_CJ	Foreign Key(제품ID)
                                          References 제품(제품ID)
)

CREATE TABLE 공장
( 공장ID		Char(4)		Primary Key,
  공장명	Varchar(20)	NOT NULL,
  주소		Varchar(50)
)

CREATE TABLE 공장제품
( 공장ID		Char(4)                NOT NULL,		
  제품ID		Char(4)		NOT NULL,
  생산량		Int,
  Constraint	PK_GJ	Primary Key(공장ID,제품ID),
  Constraint       FK_GO	Foreign Key(공장ID)
                                          References 공장(공장ID),
  Constraint       FK_GJ	Foreign Key(제품ID)
                                          References 제품(제품ID)
)

--(3) 자료입력
Insert into 제품 values('J001', 'TV', 'A', 3000)
Insert into 제품 values('J002', 'TV', 'B', 2000)
Insert into 제품 values('J003', 'Radio','A', 500)
Insert into 제품 values('J004', 'Audio', 'A', 3000)
Insert into 제품 values('J005', 'Audio', 'B', NULL)

Insert into 고객 values('K001', '홍길동', '(051) 123-1234', '부산')
Insert into 고객 values('K002', '김철수', '(051) 234-1235', '부산시남구용당동123')
Insert into 고객 values('K003', '박영수', '( 02) 1234-2345', '서울특별시영등포구')
Insert into 고객 values('K004', '박순자', '(053) 456-2347', '대구직할시북구산격동')

Insert into 공장 values('G001', '제공장', '부산')
Insert into 공장 values('G002', '제공장', '대구')
Insert into 공장 values('G003', '제공장', '대전시')
Insert into 공장 values('G004', '제공장', '경남창원시')
Insert into 공장 values('G005', '제공장', '부산시남구용당동')
Insert into 공장 values('G006', '제공장', '서울시')
Insert into 공장 values('G007', '제공장', '울산')

Insert into 창고 values('C001', '제창고', '(051) 123-5555', '부산')
Insert into 창고 values('C002', '제창고', '( 02) 3123-6666', '서울')
Insert into 창고 values('C003', '제창고', '(053) 123-7777', '대구')

Insert into 창고제품 values('C001', 'J001',20 ,10)
Insert into 창고제품 values('C001', 'J002',12 ,30)
Insert into 창고제품 values('C002', 'J001',20 ,30)
Insert into 창고제품 values('C002', 'J003',15 ,5)
Insert into 창고제품 values('C002', 'J004',30 ,35)
Insert into 창고제품 values('C003', 'J001',23 ,20)
Insert into 창고제품 values('C003', 'J003',15 ,15)
Insert into 창고제품 values('C003', 'J004',20 ,16)

Insert into 공장제품 values('G001', 'J001', 20)
Insert into 공장제품 values('G001', 'J002', 30)
Insert into 공장제품 values('G001', 'J003', 13)
Insert into 공장제품 values('G002', 'J002', 30)
Insert into 공장제품 values('G002', 'J003', 20)
Insert into 공장제품 values('G002', 'J004', 16)
Insert into 공장제품 values('G003', 'J003', 20)
Insert into 공장제품 values('G003', 'J004', 24)
Insert into 공장제품 values('G004', 'J002', 20)
Insert into 공장제품 values('G004', 'J004', 15)
Insert into 공장제품 values('G005', 'J001', 20)
Insert into 공장제품 values('G005', 'J004', 30)
Insert into 공장제품 values('G006', 'J001', 20)
Insert into 공장제품 values('G006', 'J003', 10)
go

--(4) Select 문연습

select * from 제품
go

select * from 고객
go

select * from 창고
go

select * from 창고제품
go

select * from 공장
go

select * from 공장제품
go

--1. 제품의제품명과유형그리고단가를구하고단가에는단위를명시하시오
SELECT 제품명,유형,단가, '천원' AS 단위FROM 제품
go

--2. 제품의제품명과유형그리고단가를구하고단가에는원단위를명시하시오
select 제품명,유형,단가*1000 AS 단가FROM 제품
go

--3. 제품의제품명을구하시오
SELECT DISTINCT 제품명FROM 제품
go

--4, TV  A형인제품의단가를구하시오
select 단가From 제품Where 제품명='TV' AND 유형='A'
go

--5. 단가가2000이하인제품의제품명과유형은무엇인가?
select  제품명, 유형From 제품Where 단가<= 2000
go

--6. 단가가2000이상인제품의제품명과유형은무엇인가?
select  제품명, 유형From 제품Where 단가>= 2000
go

--?	비교술어
--     =(등호),  <(미만),  >(초가),  <>(부등호),  >=(이상),  <=(이하)

--7. 단가가500이상2000이하인제품의제품명과유형은무엇인가?
select 제품명,유형From 제품
Where 단가BETWEEN 500 AND 2000
go

--8. 단가가500미만2000초가인제품의제품명과유형은무엇인가?
select 제품명,유형From 제품
Where 단가NOT BETWEEN 500 AND 2000
go

--?	 BETWEEN 술어
--     [NOT] BETWEEN 값식AND 값식

--9. 주소가부산인고객의정보는?
select * From 고객Where 주소Like '부산%'
go

--10. 남구에사는고객의성명,전화번호,주소는?
select 성명,전화번호,주소From 고객Where 주소Like '%남구%'
go

--11. 주소의두번째글자가‘산’인 고객의정보는?
select * From 고객Where 주소Like '_산%'
go

--12. 제품명의첫자가R이고끝두자가io인다섯자리로된제품에대한정보는?
select * From 제품Where 제품명Like 'R__io'
go

--?	 LIKE 술어
--    필드명[NOT] LIKE 패턴  
--    % : 임의의n자
--    _ : 임의의한자

--13. 제품의유형이‘A’또는‘B’형인제품에대한정보는?
select * From 제품Where 유형IN ('A','B')
go

--14. 제품명이‘T’로시작하고, 유형이‘A’또는‘B’형인제품에대한정보는?
select * From 제품Where 제품명Like 'T%' AND 유형IN ('A','B')
go

--?	 IN 술어
--    필드명[NOT]  IN (값지정)

--15. 단가가미정인제품에대한정보는?
select * From 제품Where 단가IS NULL
go

--15. 단가가정해진제품에대한정보는?
select * From 제품Where 단가IS NOT NULL
go

--?	 NULL 술어
--     필드명IS [NOT] NULL

--16. 재고량이안전재고량보다적은제품에대해서창고ID와제품ID 그리고부족한숫자를구하라
select 창고ID,제품ID,안전재고량-재고량AS 부족분
From 창고제품Where 재고량<안전재고량
go

--17. 제품의개수를구하라
select COUNT(*) From 제품
go

--18. 제품명이다른제품의개수를구하라
Select COUNT(DISTINCT 제품명) From 제품
go

--19. 제품ID가‘J002’인제품의전체생산량을구하라
Select SUM(생산량) From 공장제품Where 제품ID = 'J002'
go

--20. 공장ID가‘G001’인공장에서생산량의최대값을구하라
select MAX(생산량) From 공장제품Where 공장ID='G001'
go

--21. 공장전체에서생산하는제품별생산량구하라
Select 제품ID, Sum(생산량) From 공장제품Group By 제품ID
go

--22. 공장전체에서생산하는제품에대해서생산량이50이상인제품에대한
--      제품별생산량구하라
Select 제품ID, Sum(생산량) From 공장제품Group By 제품ID
Having  Sum(생산량) >=50
go

--23. 공장과공장제품테이블로부터공장명,주소, 제품ID, 생산량을구하라
Select 공장명,주소, 제품ID, 생산량
From 공장,공장제품
Where 공장.공장ID =공장제품.공장ID
go

Select 공장명,주소, 제품ID, 생산량
From 공장Inner Join 공장제품
On 공장.공장ID =공장제품.공장ID
go

-- <Table Alias를사용한경우: Inner Join>
Select g.공장명"공장명",g.주소"주     소",
j.제품ID "제품ID",j.생산량"생산량" 
from 공장g, 공장제품j
Where g.공장ID=j.공장ID
go

Select g.공장명"공장명",g.주소"주     소",
          j.제품ID "제품ID", j.생산량"생산량" 
from 공장g Inner Join 공장제품j 
On g.공장ID=j.공장ID
go

-- <Table Alias를사용한경우: Outer Join(Right)>
Select g.공장명"공장명",g.주소"주     소",
j.제품ID "제품ID",j.생산량"생산량" 
from 공장g Right Outer Join 공장제품j 
On g.공장ID=j.공장ID
go

-- <Table Alias를사용한경우: Outer Join(Left)>
Select g.공장명"공장명",g.주소"주     소",
j.제품ID "제품ID",j.생산량"생산량" 
from 공장g Left Outer Join 공장제품j 
On g.공장ID=j.공장ID
go

--24. 공장ID가‘G001’인공장에서생산되고있는각제품의생산량의차를구하라(self Join)
select 공장제품.제품ID,'-',gj.제품ID,'=',
       공장제품.생산량-gj.생산량"생산량의차" 
From 공장제품, 공장제품gj
Where 공장제품.제품ID < gj.제품ID
      AND 공장제품.공장ID='G001'
      AND gj.공장ID='G001'
go

--25. 공장과공장제품그리고제품테이블로부터
--     공장명,주소, 제품ID, 제품명, 유형,생산량을구하라
Select 공장명,주소, 공장제품.제품ID,제품명,유형,생산량
From 공장,공장제품,제품
Where 공장.공장ID =공장제품.공장ID And 공장제품.제품ID =제품.제품ID
go

--26. 공장과공장제품그리고제품테이블로부터부산에있는공장에대해서
--     공장명,주소, 제품ID, 제품명, 유형,생산량을구하라
Select 공장명,주소, 공장제품.제품ID,제품명,유형,생산량
From 공장,공장제품,제품
Where 공장.공장ID =공장제품.공장ID And 공장제품.제품ID =제품.제품ID 
      AND 공장.주소Like '부산%'
go

--27. 부산이외의공장에서생산되고있는각제품에대해서제품ID와최대생산량을구하라
Select 제품ID, MAX(생산량) 
From 공장,공장제품
Where 공장.공장ID =공장제품.공장ID  
      AND 주소Not Like '부산%'
Group By 제품ID
go

--27. 부산이외의공장에서생산되고있는각제품에대해서제품ID별생산량을구하라
Select 제품ID, sum(생산량) 
From 공장,공장제품
Where 공장.공장ID =공장제품.공장ID  
      AND 주소Not Like '부산%'
Group By 제품ID
go

--28. 부산이외의공장에서생산되고있는각제품에대해서생산량이30 인상인제품에대해서제품ID별생산량을구하라
Select 제품ID, sum(생산량) 
From 공장,공장제품
Where 공장.공장ID =공장제품.공장ID  
      AND 주소<> '부산%'
Group By 제품ID  Having sum(생산량) > 30
go

--29. 단가가평균단가이상인제품에대해서제품명과유형단가를구하라
Select 제품명,유형,단가From 제품
Where 단가>= (Select AVG(단가) From 제품)
go

--30. 부산의공장에서생산하고있는제품에대해서제품명유형단가를구하라
Select 제품명,유형,단가From 제품
Where 제품ID 
  IN(Select 제품ID From 공장,공장제품
     where 공장.공장ID=공장제품.공장ID
           AND 주소like '부산%')
go

실행 결과

저작자표시 비영리 변경금지 (새창열림)

'데이터베이스 > SQL문법' 카테고리의 다른 글

데이터베이스 SQL 기초 문법: 뷰(View) (0)	2011.07.10
데이터베이스 SQL 기초 문법: 데이터 정의 언어 (DDL, Data Definition Language) (0)	2011.07.10
도서대여.mdb: 시험 삼아 만들어 본 데이터베이스 (0)	2011.07.09