RTcm2L4Q

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