Baza Danych „E-Ultrasshop” - Skrypt SQL

--IF DB_ID('Ultrasshop') IS NULL -- Jeśli baza Ultrasshop nie istnieje to

--CREATE DATABASE Ultrasshop -- utwórz bazę danych

IF OBJECT_ID('Klient','U') IS NULL -- Jeśli tabela Klient nie istnieje

CREATE TABLE Klient ( -- to utwórz tabelę Klient

ID INT IDENTITY(1,1) PRIMARY KEY,

Imie VARCHAR(13),

Nazwisko VARCHAR(30),

Kod_Pocztowy VARCHAR(7),

Adres VARCHAR(30),

Miejscowosc VARCHAR(20),

Telefon INT,

Email VARCHAR(30)

)

IF OBJECT_ID('Zamowienie','U') IS NULL -- Jeśli tabela Zamowienie nie istnieje

CREATE TABLE Zamowienie ( -- to utwórz tabelę

ID INT IDENTITY(1,1) PRIMARY KEY,

Status VARCHAR(15),

Data DATETIME,

ID_Klient INT CONSTRAINT FK_ID_klienta REFERENCES Klient(ID),

)

CREATE TABLE Kategoria ( -- to utwórz tabelę Kategoria

ID INT IDENTITY (1,1) PRIMARY KEY,

Nazwa VARCHAR(15)

)

IF OBJECT_ID('Podkategoria','U') IS NULL -- Jeśli tabela Podkategoria nie istnieje

CREATE TABLE Podkategoria ( -- to utwórz tabelę Podkategoria

ID INT IDENTITY (1,1) PRIMARY KEY,

ID_Kategoria INT CONSTRAINT FK_ID_kategoria REFERENCES Kategoria(ID),

Nazwa VARCHAR(15)

)

IF OBJECT_ID('Produkt','U') IS NULL -- Jeśli tabela Produkt nie istnieje

CREATE TABLE Produkt ( -- to utwórz tabelę Produkt

ID INT IDENTITY (1,1) PRIMARY KEY,

Nazwa VARCHAR(15),

Opis TEXT,

Cena MONEY,

ID_Kategoria INT CONSTRAINT FK_ID_kategoria_in_produkt REFERENCES Kategoria(ID),

ID_Podkategoria INT CONSTRAINT FK_ID_Podkategoria_in_produkt REFERENCES Podkategoria(ID)

)

IF OBJECT_ID('Cenniki','U') IS NULL -- Jeśli tabela Cenniki nie istnieje

CREATE TABLE Cenniki ( -- to utwórz tabelę Cenniki

ID_Produktu INT CONSTRAINT FK_ID_Produktu_in_cenniki REFERENCES Produkt(ID),

Data_pocz DATETIME,

Data_kon DATETIME,

Ilosc_min INT,

Cena MONEY

)

IF OBJECT_ID('Zamowiony_produkt','U') IS NULL -- Jeśli tabela Zamowiony_produkt nie istnieje

CREATE TABLE Zamowiony_produkt ( -- to utwórz tabelę Zamowiony_produkt

ID INT IDENTITY (1,1) PRIMARY KEY,

ID_Zamowienie INT CONSTRAINT FK_ID_Zamowienie_in_zam_prod REFERENCES Zamowienie(ID),

ID_Produkt INT CONSTRAINT FK_ID_Produkt_in_zam_prod REFERENCES Produkt(ID),

Ilosc_szt INT,

Cena MONEY

)

--Wstawianie wartości do tabeli klient

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Piotr','Kowalski' ,'62-010', 'Kościuszki 32', 'Pobiedziska', 669819811, '[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Bartosz', 'Kaczmarek', '62-012', 'Lubelska 13','Luboń',618812711,'[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Kamil', 'Nowak', '41-234', 'Kwiecista 43','Poznań',724111543,'[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Piotr', 'Jerzyński', '35-123', 'Górnicza 16', 'Poznań',693420541, '[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Daria', 'Polakowska', '53-020', 'Piastowska 10', 'Łódź',781112211, '[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Donald', 'Kaczor', '21-900', 'Różana 3', 'Warszawa',501211313, '[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Filip', 'Szulc', '62-010', 'Szucha 1', 'Kraków',721151735, '[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Mateusz', 'Gibski', '21-938', 'Kościuszki 12', 'Białystok',697564392, '[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Kamil', 'Rydz', '62-015', 'Piątkowska 11', 'Poznań',508267331 , '[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Bartosz', 'Filar', '54-012', 'Nowy Świat 3', 'Warszawa', 511748322, '[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Piotr', 'Olejniczak', '12-331', 'Łąkowa 445', 'Łódź', 693455544, '[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Sławek', 'Dworski', '30-812', 'Robocza 543', 'Kraków', 723680453, '[e-mail]');

INSERT INTO Klient (Imie, Nazwisko, Kod_Pocztowy, Adres, Miejscowosc, Telefon, Email)

VALUES ('Michał', 'Kular', '43-009', 'Strusia 32', 'Białystok', 669722112, '[e-mail]');

--Wstawianie wartości do tabeli Zamowienie

INSERT INTO Zamowienie (Status, Data, ID_Klient) VALUES ('Zrealizowane', '2012-11-04' , 4);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2011-03-21', 'Zrealizowane', 2);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-11-29', 'Oczekuje', 5);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-10-13', 'Zrealizowane', 2);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-11-30', 'Oczekuje', 5);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-03-27', 'Zrealizowane', 6);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-10-28', 'Oczekuje', 3);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-11-11', 'Oczekuje', 1);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-09-06', 'Zrealizowane', 4);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2011-12-09', 'Zrealizowane', 7);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2011-08-23', 'Zrealizowane', 13);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2011-11-09', 'Zrealizowane', 8);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-04-11', 'Zrealizowane', 11);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-11-04', 'Oczekuje', 9);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-11-30', 'Oczekuje', 12);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-03-30', 'Zrealizowane', 10);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-12-01', 'Oczekuje', 2);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2011-07-27', 'Zrealizowane', 12);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2011-11-29', 'Zrealizowane', 9);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-01-09', 'Zrealizowane', 4);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2012-10-30', 'Oczekuje', 7);

INSERT INTO Zamowienie (Data, Status, ID_Klient) VALUES ('2011-05-12', 'Zrealizowane', 11);

--Wstawianie wartości do tabeli Kategoria

INSERT INTO Kategoria (Nazwa) VALUES ('Choreografie');

INSERT INTO Kategoria (Nazwa) VALUES ('Pirotechnika');

INSERT INTO Kategoria (Nazwa) VALUES ('Odziez');

--Wstawianie wartości do tabeli Podkategoria

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 1,'Farby Spray');

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 1,'Flagi na kiju');

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 2,'Race');

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 2,'Świece Dymne');

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 2,'Stroboskopy');

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 2,'Petardy');

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 2,'Wulkany');

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 3,'T-shirty');

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 3,'Szale');

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 3,'Czapki Zimowe');

INSERT INTO Podkategoria (ID_Kategoria, Nazwa) VALUES ( 3,'Chusty');

--Wstawianie wartości do tabeli Produkt

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('MONTANA', 'Czarna farba w sprayu, 600 ml', 20.00, 1, 1);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('MONTANA', 'Czarna farba w sprayu, 400 ml', 15.00, 1, 1);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('PANED', 'Flaga na kiju w kratę, 2m x 1m',30.00 , 1, 2);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('PATTERN', 'Flaga na kiju, z wybranym wzorem, 2m x 1m', 40.00, 1, 2);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('COMET', 'Czerwona raca morska', 35.00, 2, 3);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('MARINE', 'Czerwona flara morska', 20.00, 2, 3);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('SMOKE', 'Czarna, wojskowa świeca dymna, 250g', 40.00, 2, 4);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('SMBOMB', 'Ręczne świece dymne w wybranych kolorach, 80 g', 12.00, 2, 4);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('STROBOMASTER', 'Bialy stroboskop, 100 g',5.00 , 2, 5);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('STROBO', 'Biały stroboskop, 30 g', 2.50, 2, 5);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('ACHTUNG', 'Petarda hukowa „Achtung”', 15.00, 2, 6);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('FP3', 'Petarda hukowa „FP3”', 20.00, 2, 6);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('BVOLCANO', 'Duży wulkan – 1 kg', 40.00, 2, 7);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('SVOLCANO', 'Mały wulkan – 400 g', 20.00, 2, 7);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('T-SHIRT LECH', 'Koszulka Lecha Poznań', 35.00, 3, 8);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('T-SHIRT ARKA', 'Koszulka Arki Gdynia', 35.00, 3, 8);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('SZAL LECH', 'Szal tkany Lecha Poznań', 25.00, 3, 9);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('SZAL CRACOVIA', 'Szal tkany Cracovii Kraków', 25.00, 3, 9);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('CZAPKA KSZO', 'Czapka zimowa KSZO Ostrowiec', 20.00, 3, 10);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('CZAPKA ARKA', 'Czapka zimowa Arki Gdynia', 20.00, 3, 11);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('CHUSTA NSNP', 'Bandana „Nigdy Się Nie Poddawaj”', 15.00, 3, 11);

INSERT INTO Produkt (Nazwa, Opis, Cena, ID_Kategoria, ID_Podkategoria)

VALUES ('CHUSTA KKS', 'Bandana „Kibolski Klub Sportowy”', 15.00, 3, 10);

--Wstawianie wartości do tabeli Cenniki

INSERT INTO Cenniki VALUES (1,'2012-12-30','2013-01-31', 50, 15 );

INSERT INTO Cenniki VALUES (2,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (3,'2012-01-01', '2012-04-01', 20, 25);

INSERT INTO Cenniki VALUES (4,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (5,'2011-12-30', '2012-12-31', 20, 30);

INSERT INTO Cenniki VALUES (6,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (7,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (8,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (9,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (10,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (11,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (12,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (13,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (14,'2012-09-30', '2013-01-31', 20, 15);

INSERT INTO Cenniki VALUES (15,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (16,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (17,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (18,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (19,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (20,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (21,NULL, NULL, NULL, NULL);

INSERT INTO Cenniki VALUES (22,NULL, NULL, NULL, NULL);

--Wstawianie wartości do tabeli Zamowiony_Produkt

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 1, 5, 22, 330);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 3, 1, 60, 900);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 2, 4, 4, 160);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 4, 6, 20, 400);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 5, 14, 25, 375);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 9, 12, 1, 20);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 6, 3, 33, 825);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 8, 17, 1, 25);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 7, 3, 3, 90);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 10,7, 5, 200);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 14, 2, 12, 180);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 13, 4, 41, 1640);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 22, 6, 23, 460);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 11, 6, 29, 580);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 15, 12, 11, 220);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 12, 5, 40, 1400);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 20, 11, 20, 300);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 21, 7 ,2, 80);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 16, 6, 30, 600);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 19,8 , 3, 36);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 17, 5, 72, 2160);

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena) VALUES ( 18, 5, 25, 875);

--Tworzenie widoku "Wszystkie_zamowienia"

IF OBJECT_ID('Wszystkie_zamowienia','V') IS NOT NULL DROP VIEW Wszystkie_zamowienia

GO

CREATE VIEW Wszystkie_zamowienia AS

SELECT k.ID Klient_ID, k.Imie, k.Nazwisko, k.Kod_Pocztowy, k.Adres, k.Miejscowosc, k.Telefon, k.Email,

z.ID ID_Zamowienie, z.Status Status_Zamowienia, z.Data Data_Zamowienia,

zam_pro.ID ID_Zamowiony_Produkt, zam_pro.Ilosc_szt Ilosc_zamowionych_szt, zam_pro.Cena Cena_zamowienia,

p.ID ID_Produkt, p.Nazwa Nazwa_produktu, p.Opis Opis_Produktu, p.Cena Cena_za_szt,

kat.ID ID_Kategorii, kat.Nazwa Nazwa_Kategorii, pkat.ID ID_Podkategorii, pkat.Nazwa Nazwa_Podkategorii,

c.Data_pocz Promocja_od, c.Data_kon Promocja_do, c.Ilosc_min, c.Cena Cena_w_promocji

FROM Zamowienie z

INNER JOIN Zamowiony_Produkt zam_pro ON z.ID = zam_pro.ID_Zamowienie

INNER JOIN Klient k ON z.ID_Klient = k.ID

INNER JOIN Produkt p ON p.ID = zam_pro.ID_Produkt

INNER JOIN Kategoria kat ON p.ID_Kategoria = kat.ID

INNER JOIN Podkategoria pkat ON p.ID_Podkategoria = pkat.ID

INNER JOIN Cenniki c ON c.ID_Produktu = p.ID

GO

--Zestawienie ilości sprzedanych produktów z kategorii "Pirotechnika" wg miejscowości'

IF OBJECT_ID('Ile_w_kategorii','P') IS NOT NULL

DROP PROCEDURE Ile_w_kategorii

GO

CREATE PROCEDURE Ile_w_kategorii (

@Kategoria INT = 2,

@Miejscowosc VARCHAR(100)

) AS

SELECT k.Miejscowosc, SUM(zam_pro.Ilosc_Szt) Ilosc

FROM Zamowienie z

INNER JOIN Zamowiony_Produkt zam_pro ON z.ID = zam_pro.ID_Zamowienie

INNER JOIN Klient k ON z.ID_Klient = k.ID

INNER JOIN Produkt p ON p.ID = zam_pro.ID_Produkt

WHERE p.ID_Kategoria = @Kategoria AND k.Miejscowosc = @Miejscowosc

GROUP BY k.Miejscowosc

ORDER BY Ilosc ASC

GO

BEGIN TRAN

PRINT 'Zestawienie ilości sprzedanych produktów z wybranej kategorii wg miejscowości'

EXEC Ile_w_kategorii 2, 'Poznań'

ROLLBACK

-- Wykaz dla kategorii "Pirotechnika" we wszystkich miastach

SELECT k.Miejscowosc, SUM(zam_pro.Ilosc_Szt) Ilosc

FROM Zamowienie z

INNER JOIN Zamowiony_Produkt zam_pro ON z.ID = zam_pro.ID_Zamowienie

INNER JOIN Klient k ON z.ID_Klient = k.ID

INNER JOIN Produkt p ON p.ID = zam_pro.ID_Produkt

WHERE p.ID_Kategoria = 2

GROUP BY k.Miejscowosc

ORDER BY Ilosc ASC

--2) Ile wydali uzytkownicy danej witryny w e-sklepie

IF OBJECT_ID('P_Email','P') IS NOT NULL

DROP PROCEDURE P_Email

GO

CREATE PROCEDURE P_Email (

@Witryna VARCHAR(20)

) AS

SELECT k.Nazwisko, MAX(k.Email) email, SUM(zam_pro.Cena) Wplywy

FROM Klient k

LEFT JOIN Zamowienie z ON z.ID_Klient = k.ID

LEFT JOIN Zamowiony_Produkt zam_pro

ON z.ID = zam_pro.ID_Zamowienie

WHERE Email LIKE @Witryna

GROUP BY Nazwisko

GO

PRINT 'Zestawienie wydatków klientów w e-sklepie wg domeny adresu e-mail'

EXEC P_Email '%wp%';

--3) Procedura dodania nowego klienta

IF OBJECT_ID('Nowy_Klient','P') IS NOT NULL

DROP PROCEDURE Nowy_Klient

GO

CREATE PROCEDURE Nowy_Klient (

@Imie VARCHAR(30),

@Nazwisko VARCHAR(30),

@Kod_Pocztowy VARCHAR(9),

@Adres VARCHAR(30),

@Miejscowosc VARCHAR(30),

@Telefon VARCHAR(10),

@Email VARCHAR(30)

) AS

INSERT INTO Klient (Imie,Nazwisko,Kod_Pocztowy,Adres,Miejscowosc,Telefon,Email)

VALUES (@Imie, @Nazwisko, @Kod_Pocztowy, @Adres, @Miejscowosc, @Telefon, @Email)

GO

PRINT 'Dodanie nowego klienta'

EXEC Nowy_Klient 'Beata', 'Komandor', '62-123', 'Dmowskiego 12', 'Poznań', '668232331', '[e-mail]'

SELECT *

FROM Klient

ORDER BY ID DESC

--4) Procedura dodania nowego zamówienia

IF OBJECT_ID('Nowe_Zamowienie','P') IS NOT NULL

DROP PROCEDURE Nowe_Zamowienie

GO

CREATE PROCEDURE Nowe_Zamowienie (

@ID_Klient INT,

@ID_Produkt INT,

@Ilosc_Sztuk INT,

@Data DATETIME = NULL,

@Status VARCHAR(30) = 'Oczekuje'

) AS

DECLARE @ID_Zamowienie INT

DECLARE @Cena MONEY

SET @Data=ISNULL(@Data,GETDATE()) --Wstawiam aktualną datę

--Jeżeli Cenniki.Cena != NULL i ilosc sztuk zamowionego towaru Cenniki.Ilosc_szt

IF ((SELECT Cena FROM Cenniki WHERE ID_Produktu=@ID_Produkt) IS NOT NULL) AND (SELECT Ilosc_min FROM Cenniki WHERE

ID_Produktu=@ID_Produkt)<=@Ilosc_sztuk

SET @Cena=@Ilosc_sztuk*(SELECT Cena FROM Cenniki WHERE ID_Produktu=@ID_Produkt)

ELSE

SET @Cena=@Ilosc_sztuk*(SELECT Cena FROM Produkt WHERE ID=@ID_Produkt)

--

INSERT INTO Zamowienie (Status, Data, ID_Klient)

VALUES (@Status, @Data, @ID_Klient)

SET @ID_Zamowienie=(SELECT MAX(ID) FROM Zamowienie)

INSERT INTO Zamowiony_Produkt (ID_Zamowienie, ID_Produkt, Ilosc_Szt, Cena)

VALUES (@ID_Zamowienie, @ID_Produkt, @Ilosc_Sztuk, @Cena)

GO

PRINT 'Nowe zamowienie Kamila Rydz na race Comet w ilosci 50 szt (promocyjna cena)'

EXEC Nowe_Zamowienie 9, 5, 50

SELECT *

FROM Wszystkie_Zamowienia

ORDER BY ID_Zamowiony_Produkt DESC

--5) Zestawienie pięciu najlepszych zamówień

SELECT TOP 5 Cena_zamowienia, Nazwisko Nazwisko_klienta, Miejscowosc,

Status_Zamowienia,Ilosc_zamowionych_szt, Cena_zamowienia,

Nazwa_produktu, Opis_Produktu,

Nazwa_Podkategorii,

Cena_w_promocji

FROM Wszystkie_zamowienia

ORDER BY Wszystkie_zamowienia.Cena_zamowienia DESC