Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE banertilbooking
- DROP TABLE booking
- DROP TABLE bane
- DROP TABLE banepris
- DROP TABLE kunde
- DROP TABLE firma
- CREATE TABLE firma
- (
- firmaid INT IDENTITY PRIMARY KEY,
- navn VARCHAR (50),
- opsparettimer INT
- )
- CREATE TABLE kunde
- (
- kundeid INT IDENTITY PRIMARY KEY NONCLUSTERED,
- navn VARCHAR (50),
- email VARCHAR (50),
- telefonnummer CHAR(8),-- non clustered
- firmaid INT FOREIGN KEY REFERENCES firma(firmaid),
- )
- CREATE NONCLUSTERED INDEX bigindex
- ON kunde(telefonnummer)
- include (navn, email)
- CREATE TABLE banepris
- (
- baneprisid INT IDENTITY PRIMARY KEY,
- tid TIME,
- pris INT
- )
- CREATE TABLE bane
- (
- baneid INT IDENTITY PRIMARY KEY,
- beskrivelse VARCHAR(50),
- )
- CREATE TABLE booking
- (
- bookingid INT IDENTITY PRIMARY KEY NONCLUSTERED,
- dato DATETIME,-- clustered index
- antalpersoner INT,
- gratis BIT,
- kundeid INT FOREIGN KEY REFERENCES kunde(kundeid) NOT NULL,
- baneprisid INT FOREIGN KEY REFERENCES banepris(baneprisid) NOT NULL,
- )
- CREATE CLUSTERED INDEX bigindex
- ON booking(dato)
- CREATE NONCLUSTERED INDEX nonindex
- ON booking(kundeid)
- CREATE TABLE banertilbooking
- (
- banertilbookingid INT IDENTITY PRIMARY KEY NONCLUSTERED,
- bookingid INT FOREIGN KEY REFERENCES booking(bookingid) NOT NULL,
- baneid INT FOREIGN KEY REFERENCES bane(baneid) NOT NULL
- -- clustered index
- )
- CREATE CLUSTERED INDEX baneindex
- ON banertilbooking(baneid)
- -- Insert
- INSERT INTO firma
- VALUES ('Lego A/S',
- 0),
- ('EAAA',
- 0)
- INSERT INTO kunde
- VALUES ('Jesper Petersen',
- 'jesper_petersen@mail.com',
- '12345678',
- 1),
- ('Sandy Vu',
- 'sandy_vu@mail.com',
- '22345678',
- 1),
- ('Lærke Sejr',
- 'lærke_sejr@mail.com',
- '32345678',
- 1)
- INSERT INTO banepris
- VALUES ('11:00',
- 50),
- ('12:00',
- 50),
- ('13:00',
- 50),
- ('14:00',
- 50),
- ('15:00',
- 50),
- ('16:00',
- 70),
- ('17:00',
- 70),
- ('18:00',
- 100),
- ('19:00',
- 100),
- ('20:00',
- 100),
- ('21:00',
- 100)
- DECLARE @i INT
- SELECT @i = 1
- WHILE @i < 33
- BEGIN
- IF ( @i < 7
- OR @i > 24 )
- BEGIN
- INSERT INTO bane
- VALUES ('Lille hal')
- END
- ELSE
- BEGIN
- INSERT INTO bane
- VALUES ('Stor hal')
- END
- SELECT @i = @i + 1
- END
- INSERT INTO booking
- VALUES ('2017-06-10 12:00:00',
- 5,
- 1,
- 1,
- 2),
- ('2017-06-10 12:00:00',
- 4,
- 1,
- 1,
- 2),
- ('2017-06-10 12:00:00',
- 3,
- 1,
- 2,
- 2),
- ('2017-06-10 18:00:00',
- 3,
- 1,
- 3,
- 8),
- ('2017-06-10 18:00:00',
- 3,
- 1,
- 3,
- 8)
- INSERT INTO banertilbooking
- VALUES (1,
- 7),
- (1,
- 11),
- (2,
- 8),
- (3,
- 9),
- (4,
- 10),
- (5,
- 7)
- -- Opgave 3
- DROP TRIGGER inserttrig
- go
- CREATE TRIGGER inserttrig
- ON booking
- FOR INSERT
- AS
- IF EXISTS (SELECT dato
- FROM inserted
- WHERE dato < Getdate())
- OR EXISTS (SELECT dato
- FROM inserted
- WHERE dato > Dateadd(mm, 1, Getdate()))
- BEGIN
- PRINT 'Ikke gyldig booking'
- ROLLBACK TRAN
- END
- go
- -- Opgave 4
- go
- DROP VIEW statistik
- go
- CREATE VIEW statistik
- AS
- SELECT Datename(dw, t1.dato) AS UgeDag,
- Cast(t1.dato AS TIME) AS StartTid,
- Sum(baner) AS UdlejedeBaner,
- Sum(indt�gt + skopris) AS Indtægt
- FROM (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
- WHERE bp.tid = Cast (b.dato AS TIME)
- AND b.bookingid = bb.bookingid
- GROUP BY Datename(dw, b.dato),
- Cast(b.dato AS TIME),
- b.bookingid,
- b.gratis,
- b.dato) AS t1
- JOIN (SELECT b.bookingid,
- Sum(b.antalpersoner) * 15 AS skopris
- FROM booking b
- GROUP BY b.bookingid) AS t2
- ON t1.bookingid = t2.bookingid
- GROUP BY Datename(dw, t1.dato),
- Cast(t1.dato AS TIME)
- go
- SELECT *
- FROM statistik
- -- Opgave 5
- DROP PROC tildeltimer
- go
- CREATE PROC Tildeltimer
- AS
- SELECT *
- INTO #temp
- FROM (SELECT f.firmaid AS id,
- Sum(bp.pris) AS omsætning
- FROM kunde k,
- firma f,
- booking b,
- banepris bp
- WHERE f.firmaid = k.firmaid
- AND k.kundeid = b.kundeid
- AND b.baneprisid = bp.baneprisid
- AND Datepart(yyyy, b.dato) = Datepart(yyyy, Getdate()) - 1
- GROUP BY f.firmaid
- HAVING Count(b.bookingid) > 2) AS x
- SELECT *
- FROM #temp
- DECLARE @Id INT
- WHILE (SELECT Count(*)
- FROM #temp) > 0
- BEGIN
- SELECT TOP 1 @Id = id
- FROM #temp
- UPDATE firma
- SET opsparettimer = (SELECT TOP 1 oms�tning
- FROM #temp) / 2500
- WHERE firmaid = @Id
- DELETE #temp
- WHERE id = @Id
- END
- DROP TABLE #temp
- go
- EXEC Tildeltimer
- -- Opgave 8
- -- Backup
- RESTORE DATABASE eksamensdb FROM dbdev WITH FILE = 3
- -- Differential backup
- RESTORE DATABASE eksamensdb FROM dbdev WITH FILE = 5
- -- Logs
- RESTORE log eksamensdb FROM logdev WITH FILE = 1
- RESTORE log eksamensdb FROM logdev WITH FILE = 2
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement