dua2etAu

1go
2
3drop table if exists LineItems
4drop table if exists Prices
5drop table if exists Products
6drop table if exists Categories
7drop table if exists Sales
8
9create table Sales(
10    ID int identity(1001,1) not null primary key,
11    CreationDate datetime,
12    SoldAtBar bit
13)
14
15create table Categories(
16    ID int identity(1001,1) not null primary key,
17    Title char(30) not null,
18)
19
20create table Products(
21    ID int identity(1001,1) not null primary key,
22    Title char(30),
23    PurchasableAtBar bit not null,
24    CategoryID int not null foreign key references Categories(ID)
25)
26
27create table LineItems(
28    ID int identity(1001,1) not null primary key,
29    Quantity int not null,
30    Discount int not null,
31    SaleID int not null foreign key references Sales(ID),
32    ProductID int foreign key references Products(ID) on delete cascade,
33)
34
35create table Prices(
36    ID int identity(1001,1) not null primary key,
37    Amount int not null,
38    ProductID int foreign key references Products(ID) on delete cascade,
39    PurchasedAtBar bit not null
40)
41
42insert into Sales values ('2018-04-25 18:32:00', 1)
43insert into Sales values ('2018-04-26 18:32:30', 0)
44insert into Categories values ('Øl')
45insert into Categories values ('Snack')
46insert into Categories values ('Spiritus')
47insert into Products values ('Klosterbryg', 1, 1001)
48insert into Products values ('Sommerbryg', 1, 1001)
49insert into Products values ('IPA', 0, 1001)
50insert into Products values ('Chips', 1, 1002)
51insert into Products values ('Whisky', 1, 1003)
52insert into LineItems values (1, 10, 1001, 1001)
53insert into LineItems values (10, 10, 1001, 1002)
54insert into LineItems values (2, 0, 1002, 1003)
55insert into LineItems values (1, 5, 1002, 1004)
56insert into LineItems values (7, 20, 1002, 1001)
57insert into Prices values (50, 1001, 1)
58insert into Prices values (35, 1001, 0)
59insert into Prices values (50, 1002, 1)
60insert into Prices values (35, 1002, 0)
61insert into Prices values (50, 1003, 1)
62insert into Prices values (35, 1003, 0)
63insert into Prices values (25, 1004, 1)
64insert into Prices values (20, 1004, 0)
65insert into Prices values (100,1005, 1)
66
67GO
68/*priser for et produkt i forskellige salgssituationer*/
69select Pd.ID as Produktnr, Pr.BarPrice as BarPrice, Pr.Amount as Beløb
70from Products Pd, Prices Pr
71where Pd.ID = Pr.ProductID
72
73GO
74/*Udregner rabatten for et givet salg*/
75select S.ID as SaleID, sum(L.Discount * L.Quantity) as Discount
76from LineItems L, Sales S
77where S.ID = L.SaleID
78group by S.ID
79
80GO
81/*Udregner den endelige pris*/
82select S.ID as SaleID, P.BarPrice as BarPrice, sum(P.Amount * L.Quantity) - sum(L.Discount * L.Quantity) as result
83from Sales S, LineItems L, Prices P
84where S.ID = L.SaleID and L.ProductID = P.ProductID
85group by S.ID, P.BarPrice
86
87GO
88/*Udregner samlede antal solgte produkter i en given måned*/
89select sum(L.Quantity) as Count, Month(S.CreationDate) as Month
90from LineItems L, Sales S
91where S.ID = L.SaleID and L.Quantity > 5
92group by Month(S.CreationDate)
93
94GO
95/*Navne på produkter, der ikke er tilhørende i fredagsbaren*/
96select pro.title as Produkt, C.title as Categori
97from Products pro, Categories C
98where C.ID = pro.CategoryID and pro.PurchasableAtBar = 0
99
100/*View*/
101go
102drop view view1
103
104go
105create view View1 as
106select C.Title as Category, P.Title as Product, count(L.SaleID) as Quantity
107from Categories C, Products P
108left join LineItems L on L.ProductID = P.ID
109left join Sales on Sales.ID = l.SaleID
110where C.ID = P.CategoryID
111group by C.title, P.title
112
113go 
114select * from View1
115
116/*1. procedure*/
117go
118drop proc proc1
119
120go
121create proc proc1
122@PurchasedAtBar bit
123as
124select p.Title, pr.PurchasedAtBar, sum(pr.Amount) - sum(l.Discount) as result
125from Products p, Prices pr, LineItems l
126where p.ID = pr.ProductID and p.ID = l.ProductID and pr.PurchasedAtBar = @PurchasedAtBar
127group by p.title, pr.PurchasedAtBar
128
129go
130exec proc1 @PurchasedAtBar = 1
131
132go
133drop proc proc2
134
135go
136create proc proc2
137@DiscountAmount int,
138@CategoryTitle nvarchar(30)
139as 
140update LineItems set Discount = @DiscountAmount where ProductID in (
141    select p.ID
142    from Products p, Categories c
143    where p.CategoryID = c.ID and c.title = @CategoryTitle
144)
145
146go
147exec proc2 @DiscountAmount = 20, @CategoryTitle = 'Øl'
148
149select * from LineItems
150
151go
152drop trigger deleteCategory
153
154go
155create trigger deleteCategory
156on Products
157after delete 
158as
159delete from Categories where (select count(*) from products where CategoryID = Categories.ID) = 0
160
161select * from Categories
162delete from Products where title = 'Whisky'
163select * from Categories
164
165GO
166select sum(l.Quantity * (pp.Amount - l.Discount)) as Price, s.CreationDate as 'Sales Date' from  LineItems l 
167join Sales s on s.ID = l.SaleID
168join Products p on l.ProductID = p.ID
169join Prices pp on p.ID = pp.ProductID 
170WHERE DAY(s.CreationDate) = '25' 
171AND p.Title = 'Klosterbryg'
172GROUP BY s.CreationDate
173
174GO
175
176go
177drop proc proc3
178
179go
180create proc proc3
181@Product char(30),
182@SoldAt datetime
183as
184select pd.Title, s.CreationDate, sum(pr.Amount * l.Quantity - l.Discount * l.Quantity) as price
185from Products pd, Sales s, Prices pr, LineItems l
186where 
187    l.SaleID = s.ID and 
188    l.ProductID = pd.ID and 
189    pr.ProductID = pd.ID and 
190    pd.title = @Product and 
191    s.CreationDate = @SoldAt and
192    s.SoldAtBar = pr.PurchasedAtBar
193group by pd.title, s.CreationDate, pr.Amount * l.Quantity - l.Discount * l.Quantity
194
195go
196exec proc3 @Product = 'Chips', @SoldAt = '2018-04-26 18:32:30'