Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop table klippekort
- drop table ordrelinje
- drop table prisfraliste
- drop table prisliste
- drop table vare
- drop table bestilling
- drop table udlejningsprodukt
- drop table kunde
- drop table rabat
- create table rabat(
- rabatID int primary key NOT NULL,
- pris int
- )
- Create table kunde(
- kundeID int primary key NOT NULL,
- navn varchar(30),
- adresse varchar(30),
- telefonnr int,
- email varchar(30),
- rabatID int foreign key references rabat(rabatID)
- )
- create table klippekort(
- klippekortID int primary key NOT NULL,
- antalklip int,
- pris int,
- kundeID int foreign key references kunde(KundeID)
- )
- create table bestilling(
- bestillingID int primary key NOT NULL,
- købsdato date default getdate(),
- betalingstype varchar(30),
- kundeID int foreign key references kunde(KundeID)
- )
- create table udlejningsprodukt(
- udlejningsproduktID int primary key NOT NULL,
- udltype varchar(30),
- navn varchar(30),
- pris int,
- udlejet date default getdate(),
- tilbagelevering date,
- ledig bit default 1 -- Når den er 1 = ledig
- )
- create table vare(
- vareID int primary key NOT NULL,
- varetype varchar(30),
- udløbsdato date CHECK (udløbsdato >= getDATE() ),
- volume int,
- navn varchar(30)
- )
- create table prisliste(
- prislisteID int primary key NOT NULL,
- navn varchar(30)
- )
- create table prisfraliste(
- prisfralisteID int primary key NOT NULL,
- pris int,
- vareID int foreign key references vare(vareID) NOT NULL,
- prislisteID int foreign key references prisliste(prislisteID) NOT NULL
- )
- create table ordrelinje(
- ordrelinjeID int primary key NOT NULL,
- antal int,
- bestillingID int foreign key references bestilling(bestillingID),
- udlejningsproduktID int foreign key references udlejningsprodukt(udlejningsproduktID),
- prisfralisteID int foreign key references prisfraliste(prisfralisteID),
- )
- --RabatID, pris
- insert into rabat values
- (1, 50),
- (2, 100),
- (3, 150),
- (4, 200),
- (5, 250),
- (6, 300)
- --KundeID, navn , adresse, telefonnr, email, rabatID
- insert into kunde values
- (1, 'Daniel', '8900 Randersvej 100', 20202020, 'daniel@gmail.com', 1),
- (2, 'Simon', '8210 Gellerupvej 200', 20204020, 'simon@gmail.com', 2),
- (3, 'Dennis', '8230 Silkeborgvej 300', 20205020, 'dennis@gmail.com', 3)
- -- vareID, varetype, udløbsdato, volume, navn
- insert into vare values
- (1, 'flasker', '2017-06-06', 75, 'Klosterbryg'),
- (2, 'fustage', '2017-06-06', 75, 'Sweet Georgia Brown'),
- (3, 'flasker', '2017-06-06', 75, 'Extra Pilsner'),
- (4, 'flasker', '2017-06-06', 75, 'Celebration')
- --prislisteID, navn
- insert into prisliste values
- (1, 'fredagsbar'),
- (2, 'butik')
- --prisfralisteID, pris, fremmedenøgle(vareID), fremmedenøgle(prislisteID)
- insert into prisfraliste values
- (1, 50, 1, 1),
- (2, 36, 1, 2),
- (3, 50, 2, 1),
- (5, 50, 3, 1)
- -- bestillingID, købsdato, betalingstype, fremmedenøgle(kundeID)
- insert into bestilling values
- (1, '2017-06-06', 'Dankort', 1),
- (2, '2017-06-06', 'Mobilpay', 2),
- (3, '2017-06-06', 'Kontant', 3)
- -- ordrelinjeID, antal, fremmedenøgle(bestillingID), fremmedenøgle(udlejningsproduktID), fremmedenøgle(prisfralisteID)
- insert into ordrelinje values
- (1, 1, 1, null, 1), -- Dankort, 50 kr.
- (2, 2, 2, null, 2), -- Mobilepay, 36 kr.
- (3, 4, 3, null, 3), -- kontant 50 kr.
- (4, 1, 3, null, 5) -- kontant 50 kr.
- drop proc vare_navn
- -- Opgave 2A
- create proc vare_navn
- @vareID int,
- @vareType varchar(30),
- @udløbsdato date,
- @volume int,
- @navn varchar(30),
- @pris int,
- @prislistenavn varchar(30)
- as
- Declare @prisfralisteID int
- Declare @prislisteID int
- Declare @tempID4PrisListe int -- TEMP BRUGES KUN VED NY PRISLISTE
- Select @prislisteID = prisliste.prislisteID
- from prisliste
- if exists (select (prisliste.navn) from prisliste where prisliste.navn = @prislistenavn)
- begin
- set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
- insert into vare values
- (@vareID, @vareType, @udløbsdato, @volume, @navn)
- insert into prisfraliste values
- (@prisfralisteID, @pris, @vareID, @prislisteID)
- END
- else
- begin
- set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
- set @tempID4PrisListe = (select MAX(prisliste.prislisteID)+1 from prisliste)
- insert into prisliste values
- (@tempID4PrisListe, @prislistenavn)
- insert into vare values
- (@vareID, @vareType, @udløbsdato, @volume, @navn)
- insert into prisfraliste values
- (@prisfralisteID, @pris, @vareID, @prislisteID)
- END
- exec vare_navn @vareID = 14, @vareType = 'flasker', @udløbsdato = '2017-06-06', @volume = 10,
- @navn = 'blondie', @pris = 100, @prislistenavn = 'butik'
- -- start på test til Opgave 2A
- Select prisfraliste.prislisteID
- from prisfraliste, prisliste
- where prisfraliste.prislisteID = prisliste.prislisteID
- Select *
- from vare
- select prisfraliste.vareID
- from prisfraliste, vare
- where prisfraliste.vareID = vare.vareID
- Select *
- from prisliste
- -- Slut på test til opgave 2A
- -- opgave 2B del 1
- create procedure salgpaadagen
- @dato date
- as
- select prisfraliste.pris
- from prisfraliste
- full join ordrelinje
- on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
- full join bestilling
- on ordrelinje.bestillingID = bestilling.bestillingID
- where bestilling.købsdato = @dato
- exec salgpaadagen @dato = '2017-06-06'
- -- opgave 2B del 2
- Create proc salgprgruppe
- @dato date,
- @type varchar(30)
- as
- select prisfraliste.pris
- from prisfraliste
- full join vare
- on vare.vareID = prisfraliste.vareID
- full join ordrelinje
- on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
- full join bestilling
- on ordrelinje.bestillingID = bestilling.bestillingID
- where bestilling.købsdato = @dato and vare.varetype = @type
- drop proc salgprgruppe
- exec salgprgruppe @dato = '2017-06-06', @type = 'flasker'
- --opgave 3A Løsning 1
- create proc visVarePrisliste
- @vare as varchar(30),
- @type as varchar(30)
- as
- select vare.navn, prisfraliste.pris, prisliste.navn
- from prisfraliste
- full join vare
- on vare.vareID = prisfraliste.vareID
- full join prisliste
- on prisliste.prislisteID = prisfraliste.prislisteID
- where vare.navn = @vare and vare.varetype = @type
- exec visVarePrisliste @vare = 'Klosterbryg', @type = 'flasker'
- drop proc visVarePrisliste
- --opgave 3A Løsning 2
- create proc visVarePrisliste2
- @vareID as varchar(30)
- as
- select vare.navn, prisfraliste.pris, prisliste.navn
- from prisfraliste
- full join vare
- on vare.vareID = prisfraliste.vareID
- full join prisliste
- on prisliste.prislisteID = prisfraliste.prislisteID
- where prisfraliste.vareID = @vareID
- exec visVarePrisliste2 @vareID = 3
- drop proc visVarePrisliste2
- --opgave 3A Løsning 3
- create proc visVarePrisliste3
- @vare as varchar(30),
- @type as varchar(30)
- as
- Declare @vareID int
- set @vareID =(Select vare.vareID from vare where vare.navn = @vare and vare.varetype = @type)
- select vare.navn, prisfraliste.pris, prisliste.navn
- from prisfraliste
- full join vare
- on @vareID = prisfraliste.vareID
- full join prisliste
- on prisliste.prislisteID = prisfraliste.prislisteID
- where vare.navn = @vare and vare.varetype = @type
- exec visVarePrisliste3 @vare = 'Klosterbryg', @type = 'flasker'
- drop proc visVarePrisliste3
- --opgave 3B
- create proc pris
- @id int
- as
- select SUM(prisfraliste.pris * ordrelinje.antal)
- as total
- from prisfraliste
- full join ordrelinje
- on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
- full join bestilling
- on ordrelinje.bestillingID = bestilling.bestillingID
- where bestilling.bestillingID = @id
- group by prisfraliste.pris
- exec pris @id = 3
- drop proc pris
- --opgave 3C Løsning 1 - Hent rabatten for en bestemt bestilling
- create proc getRabat
- @bestillingId int
- as
- select rabat.pris
- from rabat
- full join kunde
- on rabat.rabatID = kunde.rabatID
- full join bestilling
- on kunde.kundeID = bestilling.bestillingID
- where bestilling.bestillingID = @bestillingId
- exec getRabat @bestillingId = 2
- drop proc getRabat
- --opgave 3C Løsning 2 - pris for bestillingen minus(-) rabatten
- create proc totalpris
- @id int
- as
- select sum(prisfraliste.pris*ordrelinje.antal) - rabat.pris as Pris
- from prisfraliste
- full join ordrelinje
- on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
- full join bestilling
- on ordrelinje.bestillingID = bestilling.bestillingID
- full join kunde
- on bestilling.kundeID = kunde.kundeID
- full join rabat
- on kunde.rabatID = rabat.rabatID
- where bestilling.bestillingID = @id
- group by prisfraliste.pris, rabat.pris
- exec totalpris @id = 3
- drop proc totalpris
- --Opgave3D
- create proc opg3D
- @prisliste varchar(30),
- @vareNavn varchar(30)
- as
- Select prisliste.navn, prisfraliste.pris
- from prisfraliste
- full join prisliste
- on prisfraliste.prislisteID = prisliste.prislisteID
- full join vare
- on prisfraliste.vareID = vare.vareID
- where prisliste.navn = @prisliste and vare.navn = @vareNavn
- exec opg3D @prisliste = 'fredagsbar', @vareNavn = 'Celebration'
- drop proc opg3D
- --opgave 4 // Opdatere varene
- create proc opdatereVare
- @vareID int,
- @varetype varchar(30),
- @udløbsdato date,
- @volume int,
- @navn varchar(30)
- as
- update vare
- set
- vare.varetype = @varetype,
- vare.udløbsdato = @udløbsdato,
- vare.volume = @volume,
- vare.navn = @navn
- where vare.vareID = @vareID
- exec opdatereVare @vareid = 1, @varetype = 'flasker', @udløbsdato = '2017-06-10', @volume = 5000, @navn = 'DanielBryg'
- drop proc opdatereVare
- select *
- from vare
- --set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
- -- opgave 4 // Slet vare
- create proc sletVare
- @vareid int
- as
- delete ordrelinje
- from prisfraliste
- where ordrelinje.prisfralisteID = prisfraliste.prisfralisteID and prisfraliste.vareID = @vareid
- delete prisfraliste
- where prisfraliste.vareID = (@vareid)
- delete vare
- where vare.vareID = @vareid
- exec sletVare @vareid = 1
- exec sletVare @vareid = 2
- exec sletVare @vareid = 3
- exec sletVare @vareid = 14
- drop proc sletVare
- select *
- from vare
- select *
- from bestilling
- select *
- from ordrelinje
- select *
- from prisliste
- --opgave 5
- create trigger tjekpåsletvare on vare for delete
- as
- begin
- delete prisliste
- from prisliste
- where prisliste.prislisteID NOT IN (Select prisfraliste.prislisteID from prisfraliste)
- end
- go
- drop trigger tjekpåsletvare
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement