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