(실습) 도시락 프로그램(SQL Server레벨에서...)

a. bob 새 데이터베이스 생성

b. bob 새 로그인 생성

c .bob데이터베이스 선택 후 새 쿼리 생성

-- 사람

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

 

--[!] 메뉴

Insert 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

Select * From [주문자목록] Order By OrderedDate Desc

Go

 

-- 주문서작성: 전화걸어서

-- 돈까스, 10개, 30000

-- 돈까스스, 2개, 7000

-- 치킨마요, 1개, 2500

Select m.MenuName, Sum(o.Quantity), Sum(m.Price)

From Menu m, Orders o

Where m.MenuID = o.MenuID

       And YEAR(o.OrderedDate) = YEAR(GetDate())

       And Month(o.OrderedDate) = Month(GetDate())

       And Day(o.OrderedDate) = Day(GetDate())

Group By m.MenuName

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