· 9 years ago · Jun 12, 2017, 10:34 AM
1drop table banertilbooking
2drop table booking
3drop table bane
4drop table banepris
5drop table kunde
6drop table firma
7
8create table Firma
9(
10firmaid int identity primary key,
11navn varchar (50),
12opsparetTimer int
13)
14
15create table Kunde
16(
17kundeid int identity primary key nonclustered,
18navn varchar (50),
19email varchar (50),
20telefonnummer char(8), -- non clustered
21firmaid int foreign key references Firma(firmaid),
22)
23
24create nonclustered index bigindex on kunde(telefonnummer) include (navn, email)
25
26create table Banepris
27(
28baneprisid int identity primary key,
29tid time,
30pris int
31)
32
33create table Bane
34(
35baneid int identity primary key,
36beskrivelse varchar(50),
37)
38
39create table Booking
40(
41bookingid int identity primary key nonclustered,
42dato datetime, -- clustered index
43antalpersoner int,
44gratis bit,
45kundeid int foreign key references kunde(kundeid) not null,
46baneprisid int foreign key references Banepris(baneprisid) not null,
47)
48
49create clustered index bigindex on booking(dato)
50create nonclustered index nonindex on booking(kundeid)
51
52create table Banertilbooking
53(
54banertilbookingid int identity primary key nonclustered,
55bookingid int foreign key references booking(bookingid) not null,
56baneid int foreign key references bane(baneid) not null-- clustered index
57)
58
59create clustered index baneindex on banertilbooking(baneid)
60
61-- Insert
62insert into Firma values
63('Lego A/S', 0),
64('EAAA', 0)
65
66insert into Kunde values
67('Jesper Petersen', 'jesper_petersen@mail.com', '12345678',1),
68('Sandy Vu', 'sandy_vu@mail.com', '22345678',1),
69('Lærke Sejr', 'lærke_sejr@mail.com', '32345678',1)
70
71insert into Banepris values
72('11:00', 50),
73('12:00', 50),
74('13:00', 50),
75('14:00', 50),
76('15:00', 50),
77('16:00', 70),
78('17:00', 70),
79('18:00', 100),
80('19:00', 100),
81('20:00', 100),
82('21:00', 100)
83
84declare @i int
85select @i = 1
86while @i < 33
87begin
88 if (@i < 7 or @i > 24)
89 begin
90 insert into Bane values ('Lille hal')
91 end
92 else
93 begin
94 insert into Bane values ('Stor hal')
95 end
96 select @i = @i + 1
97end
98
99insert into Booking values
100('2017-06-10 12:00:00', 5, 1, 1, 2),
101('2017-06-10 12:00:00', 4, 1, 1, 2),
102('2017-06-10 12:00:00', 3, 1, 2, 2),
103('2017-06-10 18:00:00', 3, 1, 3, 8),
104('2017-06-10 18:00:00', 3, 1, 3, 8)
105
106insert into Banertilbooking values
107(1, 7),
108(1, 11),
109(2, 8),
110(3, 9),
111(4, 10),
112(5, 7)
113
114
115-- Opgave 3
116drop trigger insertTrig
117GO
118CREATE TRIGGER insertTrig
119ON Booking
120FOR INSERT
121AS
122IF EXISTS (SELECT dato FROM inserted WHERE dato < GETDATE()) OR EXISTS (SELECT dato FROM inserted WHERE dato > DATEADD(mm,1,GETDATE()))
123BEGIN
124 PRINT 'Ikke gyldig booking'
125 ROLLBACK TRAN
126END
127GO
128
129
130-- Opgave 4
131go
132drop view statistik
133go
134CREATE VIEW statistik AS
135SELECT DATENAME(dw,t1.dato) AS UgeDag, CAST(t1.dato AS TIME) AS StartTid, SUM(baner) AS UdlejedeBaner,
136SUM(indtægt+skopris) AS Indtægt FROM
137(SELECT b.bookingid, SUM(bp.pris)*b.gratis AS indtægt, COUNT(bb.bookingid) AS baner, b.dato FROM booking b, Banepris bp, Banertilbooking bb
138WHERE bp.tid = CAST (b.dato AS TIME) and b.bookingid = bb.bookingid
139GROUP BY DATENAME(dw,b.dato), CAST(b.dato AS TIME), b.bookingid, b.gratis, b.dato) AS t1
140JOIN
141(SELECT b.bookingid, SUM(b.antalpersoner)*15 AS skopris FROM booking b
142GROUP BY b.bookingid) AS t2
143ON t1.bookingid = t2.bookingid
144GROUP BY DATENAME(dw,t1.dato), CAST(t1.dato AS TIME)
145go
146
147select * from statistik
148
149
150-- Opgave 5
151drop proc tildelTimer
152go
153create proc tildelTimer
154as
155select * into #temp from (
156select f.firmaid as id, SUM(bp.pris) AS omsætning
157from kunde k, firma f, booking b, banepris bp
158where f.firmaid = k.firmaid and k.kundeid = b.kundeid and b.baneprisid = bp.baneprisid
159and DATEPART(yyyy,b.dato) = DATEPART(yyyy,GETDATE())-1
160group by f.firmaid
161having count(b.bookingid)>2
162) as x
163select * from #temp
164Declare @Id int
165While (Select Count(*) From #Temp) > 0
166Begin
167 Select Top 1 @Id = Id From #Temp
168 UPDATE firma set opsparetTimer = (select top 1 omsætning from #temp)/2500 where firmaid = @Id
169 Delete #Temp Where Id = @Id
170End
171drop table #temp
172go
173
174exec tildelTimer
175
176
177-- Opgave 8
178-- Backup
179RESTORE DATABASE EksamensDB FROM dbdev WITH FILE = 3
180-- Differential backup
181RESTORE DATABASE EksamensDB FROM dbdev WITH FILE = 5
182-- Logs
183RESTORE log EksamensDB FROM logdev WITH FILE = 1
184RESTORE log EksamensDB FROM logdev WITH FILE = 2