|
-- 사람
Create Table Members
(
MemberID Int Identity(1, 1) Primary Key, -- 일련번호
Name VarChar(25) Not Null -- 이름
)
Go |
-- 메뉴
Create Table Menu
(
MenuID Int Identity(1, 1) Primary Key, -- 일련번호
MenuName VarChar(50) Not Null, -- 메뉴명
Price Int Not Null -- 가격
)
Go |
-- 주문
Create Table Orders
(
ItemID Int Identity(1, 1) Primary Key, -- 일련번호
MemberID Int References Members(MemberID), -- 회원번호
MenuID Int References Menu(MenuID), -- 메뉴번호
Status TinyInt Default(0), -- 옵션 : 0(기본), 1(곱빼기)
Quantity TinyInt, -- 수량
OrderedDate SmallDateTIme Default(GetDate()), -- 주문일시
Etc VarChar(4000) Null -- 기타
)
Go |
-- 6개 예시문 연습
Insert Members Values('홍길동')
Go
Insert Members Values('백두산')
Go
Insert Members Values('한라산')
Go
Select * From Members Order By MemberID Desc
Go
Select * From Members Where MemberID = 1
Go
Update Members Set Name = '임꺽정' Where Name = '홍길동'
Go
Delete Members Where MemberID = 1 And (1 <> 1)
Go
Select * From Members Where Name Like '홍%'
Go |
--[!] 메뉴
nsert Into Menu Values('돈까스', '3000')
Go
Insert Into Menu Values('돈까스, 밥빼기', '2500')
Go
Insert Into Menu Values('돈까스, 반찬추가', '3500')
Go |
--[!] 주문
Insert Into Orders Values(2, 1, 0, 1, GETDATE(), '빨리 가져다 주세요.')
Go
Insert Into Orders Values(1, 3, 0, 1, GETDATE(), '빨리 가져다 주세요.')
Go
Insert Into Orders Values(3, 1, 0, 1, GETDATE(), '빨리 가져다 주세요.')
Go |
--[뷰] : 주문자 목록
-- 홍길동, 돈까스, 3000, 1개, 2009-09-21
Create View [주문자목록]
With Encryption
As
SELECT
Members.Name, Menu.MenuName, Menu.Price, Orders.Quantity,
Orders.OrderedDate, Orders.Etc
FROM
Members INNER JOIN Orders
ON Members.MemberID = Orders.MemberID
INNER JOIN Menu
ON Orders.MenuID = Menu.MenuID
--Order By OrderedDate Desc
Go |
--[1] 저장 프로시저
-- 18개 저장 프로시저 만드세요~~~
-- 입력(Add, Insert, Write), 출력, 상세, 수정, 삭제, 검색
Create Proc AddMember
@Name VarChar(25)
As
Insert Members Values(@Name)
Go |
Create Proc GetMembers
As
Select * From Members Order By MemberID Desc
Go |
Create Proc GetMemberByMemberID
@MemberID Int
As
Select * From Members Where MemberID = @MemberID
Go |
Create Proc UpdateMember
@Name VarChar(25),
@MemberID Int
As
Update Members Set Name = @Name Where MemberID = @MemberID
Go |
Create Proc DeleteMember
@MemberID Int
As
Delete Members Where MemberID = @MemberID
Go |
Create Proc SearchMember
@SearchQuery VarChar(50)
As
Declare @sql VarChar(500) -- 홍 -> ' + 변수 + '
Set @sql = 'Select * From Members Where Name Like ''' +
@SearchQuery + '%'''
Exec(@sql)
Go
SearchMember '한라'
Go |
--[!] 메뉴 관련 저장 프로시저 6개 만들어보세요...
Create Proc AddMenu
@MenuName VarChar(25),
@Price Int
As
Insert Menu Values(@MenuName, @Price)
Go
Create Proc GetMenus
As
Select * From Menu Order By MenuID Desc
Go
Create Proc GetMenuByMenuID
@MenuID Int
As
Select * From Menu Where MenuID = @MenuID
Go |
Create Proc UpdateMenu
@MenuName VarChar(25),
@Price Int,
@MenuID Int
As
Update Menu Set MenuName = @MenuName, Price = @Price Where
MenuID = @MenuID
Go
Create Proc DeleteMenu
@MenuID Int
As
Delete Menu Where MenuID = @MenuID
Go
Create Proc SearchMenu
@SearchQuery VarChar(50)
As
Declare @sql VarChar(500) -- 홍 -> ' + 변수 + '
Set @sql = 'Select * From Menu Where MenuName Like ''' + @SearchQuery + '%'''
Exec(@sql)
Go |
--[!] 주문 관련 저장 프로시저 6개
Create Proc AddOrder
@MemberID Int,
@MenuID Int,
@Status TinyInt,
@Quantity TinyInt,
@Etc VarChar(4000)
As
Insert Into Orders(MemberID, MenuID, Status, Quantity, Etc)
Values(@MemberID, @MenuID, @Status, @Quantity, @Etc)
Go
Create Proc GetOrders
As
Select * From Orders Order By OrderedDate Desc
Go
Create Proc GetOrder
@ItemID Int
As
Select * From Orders Where ItemID = @ItemID
Go
|
-- 일별 주문 상세
Create Proc GetOrdersByDate
@Year Int,
@Month Int,
@Day Int
As
Select m.MenuName, Sum(o.Quantity), SUM(o.Quantity * m.Price)
From Menu m, Orders o
Where m.MenuID = o.MenuID
And @Year = YEAR(GetDate())
And @Month = Month(GetDate())
And @Day = Day(GetDate())
Group By m.MenuName
Go
GetOrdersByDate 2009, 09, 21
Go
Create Proc UpdateOrder
@MemberID Int,
@MenuID Int,
@Status Bit,
@Quantity TinyInt,
@OrderedDate SmallDateTime,
@Etc VarChar(4000),
@ItemID Int
As
Update Orders
Set MemberID = @MemberID, MenuID = @MenuID, Status = @Status,
Quantity = @Quantity, OrderedDate = @OrderedDate, Etc = @Etc
Where ItemID = @ItemID
Go
|
|
bob.sql