USE AgencjaArtystycznaZmiana;
CREATE PROCEDURE R15_przelicz_wartosci_kontraktow()
UPDATE  Imprezy
SET        WartoscKontraktu = ROUND(1.15 * (DateDiff(DzienZakonczenia, DzienRozpoczecia) + 1) *
             (SELECT     StawkaDziennaWykonawcy
              FROM          Wykonawcy
              WHERE      Wykonawcy.IDWykonawcy = Imprezy.IDWykonawcy), 0);

CREATE VIEW R15_przelicz_wartosci_kontraktow_zapytanie
AS 
SELECT NumerImprezy, DzienRozpoczecia, DzienZakonczenia, WartoscKontraktu, ROUND(1.15 * (DateDiff(DzienZakonczenia, DzienRozpoczecia) + 1) *
             (SELECT     StawkaDziennaWykonawcy
              FROM          Wykonawcy
              WHERE      Wykonawcy.IDWykonawcy = Imprezy.IDWykonawcy), 0)
AS NowaWartoscKontraktu, IDWykonawcy
FROM Imprezy;

CREATE PROCEDURE R15_znizka_dla_dobrych_klientow_z_pazdziernika()
UPDATE    Imprezy
SET              WartoscKontraktu = ROUND(WartoscKontraktu * 0.98, 0)
WHERE     (IDKlienta IN
              (SELECT     Imprezy.IDKlienta
               FROM          Imprezy
               WHERE      (Imprezy.DzienRozpoczecia <= CAST('2012-10-31' AS Date)) 
                 AND (Imprezy.DzienZakonczenia >= CAST('2012-10-1' AS Date))
               GROUP BY Imprezy.IDKlienta
               HAVING      SUM(Imprezy.WartoscKontraktu) > 3000));

CREATE VIEW R15_znizka_dla_dobrych_klientow_z_pazdziernika_zapytanie
AS
SELECT      WartoscKontraktu, ROUND(WartoscKontraktu * 0.98, 0) AS NowaWartoscKontraktu
FROM Imprezy 
WHERE     (IDKlienta IN
              (SELECT     Imprezy.IDKlienta
               FROM          Imprezy
               WHERE      (Imprezy.DzienRozpoczecia <= CAST('2012-10-31' AS Date)) 
                 AND (Imprezy.DzienZakonczenia >= CAST('2012-10-1' AS Date))
               GROUP BY Imprezy.IDKlienta
               HAVING      SUM(Imprezy.WartoscKontraktu) > 3000)) ;

CREATE PROCEDURE R15_daj_agentom_6_procentowa_podwyzke()
UPDATE    Agenci
SET          Wynagrodzenie = ROUND(Wynagrodzenie * 1.06, 0);

CREATE VIEW R15_daj_agentom_6_procentowa_podwyzke_zapytanie
AS 
SELECT         IDAgenta, ImieAgenta, NazwiskoAgenta, Wynagrodzenie, ROUND(Wynagrodzenie * 1.06, 0) As NoweWynagrodzenie 
FROM    Agenci;

CREATE PROCEDURE R15_wynagrodz_dobrych_agentow()
UPDATE    Agenci
SET          Premia = Premia + 0.005
WHERE     (IDAgenta IN
            (SELECT     IDAgenta
             FROM          Imprezy
             GROUP BY IDAgenta
             HAVING      SUM(WartoscKontraktu) > 20000));

CREATE VIEW R15_wynagrodz_dobrych_agentow_zapytanie
AS 
SELECT      Premia, Premia + 0.005 AS NowaPremia
FROM   Agenci
WHERE     (IDAgenta IN
            (SELECT     IDAgenta
             FROM          Imprezy
             GROUP BY IDAgenta
             HAVING      SUM(WartoscKontraktu) > 20000));

CREATE PROCEDURE R16_dodaj_klienta()
INSERT INTO Klienci
                      (ImieKlienta, NazwiskoKlienta, AdresKlienta, MiastoKlienta, StanZamKlienta, KodPocztowyKlienta, TelefonKlienta)
VALUES     ('Kendra', 'Hernandez', '457 211th St NE', 'Bothell', 'WA', '98200', '555-3945');

CREATE VIEW R16_dodaj_klienta_zapytanie
AS 
SELECT 'Kendra' As ImieKlienta, 'Hernandez' As NazwiskoKlienta, '457 211th St NE' As AdresKlienta, 'Bothell' As MiastoKlienta, 'WA' As StanZamKlienta, '98200' As KodPocztowyKlienta, '555-3945' As TelefonKlienta
FROM Klienci
WHERE IDKlienta = 10001;

CREATE PROCEDURE R16_dodaj_impreze()
INSERT INTO Imprezy
                      (IDKlienta, IDWykonawcy, DzienRozpoczecia, DzienZakonczenia, GodzinaRozpoczecia, GodzinaZakonczenia, WartoscKontraktu, IDAgenta)
SELECT     Klienci.IDKlienta, Wykonawcy.IDWykonawcy, '2013-8-15' AS DzienRozpoczecia, '2013-8-16' AS DzienZakonczenia, '19:00:00' AS GodzinaRozpoczecia, '23:00:00' AS GodzinaZakonczenia, 
                      ROUND(Wykonawcy.StawkaDziennaWykonawcy * 2 * 1.15, 0) AS WartoscKontraktu, Agenci.IDAgenta
FROM         Klienci CROSS JOIN
                      Wykonawcy CROSS JOIN
                      Agenci
WHERE     (Klienci.ImieKlienta = 'Matt') AND (Klienci.NazwiskoKlienta = 'Berg') AND (Wykonawcy.NazwaScenicznaWykonawcy = 'Jazz Persuasion') AND 
                      (Agenci.ImieAgenta = 'Karen') AND (Agenci.NazwiskoAgenta = 'Smith');

CREATE VIEW R16_dodaj_impreze_zapytanie
AS 
SELECT     Klienci.IDKlienta, Wykonawcy.IDWykonawcy, '2013-8-15' AS DzienRozpoczecia, '2013-8-16' AS DzienZakonczenia, '19:00:00' AS GodzinaRozpoczecia, '23:00:00' AS GodzinaZakonczenia, 
                      ROUND(Wykonawcy.StawkaDziennaWykonawcy * 2 * 1.15, 0) AS WartoscKontraktu, Agenci.IDAgenta
FROM         Klienci CROSS JOIN
                      Wykonawcy CROSS JOIN
                      Agenci
WHERE     (Klienci.ImieKlienta = 'Matt') AND (Klienci.NazwiskoKlienta = 'Berg') AND (Wykonawcy.NazwaScenicznaWykonawcy = 'Jazz Persuasion') AND 
                      (Agenci.ImieAgenta = 'Karen') AND (Agenci.NazwiskoAgenta = 'Smith');

CREATE PROCEDURE R16_dodaj_styl()
INSERT INTO Style_muzyczne
                      (NazwaStylu)
VALUES     ('New Age');

CREATE VIEW R16_dodaj_styl_zapytanie
AS
SELECT 'New Age' As NazwaStylu
FROM Style_muzyczne
WHERE IDStylu = 1;

CREATE PROCEDURE R16_zarchiwizuj_imprezy()
INSERT INTO Imprezy_archiwum 
         (NumerImprezy, DzienRozpoczecia, DzienZakonczenia, GodzinaRozpoczecia, GodzinaZakonczenia, WartoscKontraktu, 
          IDKlienta, IDAgenta, IDWykonawcy) 
SELECT Imprezy.NumerImprezy, Imprezy.DzienRozpoczecia, Imprezy.DzienZakonczenia, 
Imprezy.GodzinaRozpoczecia, Imprezy.GodzinaZakonczenia, Imprezy.WartoscKontraktu, Imprezy.IDKlienta, 
Imprezy.IDAgenta, Imprezy.IDWykonawcy
FROM Imprezy
WHERE Imprezy.DzienZakonczenia < CAST('2013-01-01' AS Date);

CREATE VIEW R16_zarchiwizuj_imprezy_zapytanie
AS 
SELECT Imprezy.NumerImprezy, Imprezy.DzienRozpoczecia, Imprezy.DzienZakonczenia, 
Imprezy.GodzinaRozpoczecia, Imprezy.GodzinaZakonczenia, Imprezy.WartoscKontraktu, Imprezy.IDKlienta, 
Imprezy.IDAgenta, Imprezy.IDWykonawcy
FROM Imprezy
WHERE Imprezy.DzienZakonczenia < CAST('2013-01-01' AS Date);

CREATE PROCEDURE R16_skopiuj_agenta_jako_klienta()
INSERT INTO Klienci
                      (ImieKlienta, NazwiskoKlienta, AdresKlienta, MiastoKlienta, StanZamKlienta, KodPocztowyKlienta, TelefonKlienta)
SELECT     ImieAgenta, NazwiskoAgenta, AdresAgenta, MiastoAgenta, StanZamAgenta, KodPocztowyAgenta, TelefonAgenta
FROM         Agenci
WHERE     (ImieAgenta = 'Marianne') AND (NazwiskoAgenta = 'Wier');

CREATE VIEW R16_skopiuj_agenta_jako_klienta_zapytanie
AS 
SELECT     ImieAgenta, NazwiskoAgenta, AdresAgenta, MiastoAgenta, StanZamAgenta, KodPocztowyAgenta, TelefonAgenta
FROM         Agenci
WHERE     (ImieAgenta = 'Marianne') AND (NazwiskoAgenta = 'Wier');

CREATE PROCEDURE R16_powiel_impreze()
INSERT INTO Imprezy
                      (DzienRozpoczecia, DzienZakonczenia, GodzinaRozpoczecia, GodzinaZakonczenia, WartoscKontraktu, IDKlienta, IDAgenta, IDWykonawcy)
SELECT     '2013-08-01' AS DzienRozpoczecia, '2013-08-04' AS DzienZakonczenia, Imprezy.GodzinaRozpoczecia, Imprezy.GodzinaZakonczenia, Imprezy.WartoscKontraktu, 
                      Imprezy.IDKlienta, Imprezy.IDAgenta, Imprezy.IDWykonawcy
FROM         Klienci INNER JOIN
                      Imprezy ON Klienci.IDKlienta = Imprezy.IDKlienta
WHERE     (Klienci.ImieKlienta = 'Doris') AND (Klienci.NazwiskoKlienta = 'Hartwig') AND (Imprezy.DzienRozpoczecia = CAST('2012-01-12' AS Date));

CREATE VIEW R16_powiel_impreze_zapytanie
AS 
SELECT     '2013-08-01' AS DzienRozpoczecia, '2013-08-04' AS DzienZakonczenia, Imprezy.GodzinaRozpoczecia, Imprezy.GodzinaZakonczenia, Imprezy.WartoscKontraktu, 
                      Imprezy.IDKlienta, Imprezy.IDAgenta, Imprezy.IDWykonawcy
FROM         Klienci INNER JOIN
                      Imprezy ON Klienci.IDKlienta = Imprezy.IDKlienta
WHERE     (Klienci.ImieKlienta = 'Doris') AND (Klienci.NazwiskoKlienta = 'Hartwig') AND (Imprezy.DzienRozpoczecia = CAST('2012-01-12' AS Date));

CREATE PROCEDURE R17_usun_klientow_bez_rezerwacji()
DELETE FROM Klienci
WHERE     (IDKlienta NOT IN
                          (SELECT     IDKlienta
                            FROM          Imprezy));

CREATE VIEW R17_usun_klientow_bez_rezerwacji_zapytanie
AS 
SELECT * 
FROM Klienci
WHERE     (IDKlienta NOT IN
                          (SELECT     IDKlienta
                            FROM          Imprezy));

CREATE PROCEDURE R17_usun_niepopularnych_wykonawcow_1()
DELETE FROM Czlonkowie_zespolu
WHERE     (IDWykonawcy NOT IN
                          (SELECT     IDWykonawcy
                            FROM          Imprezy));

CREATE VIEW R17_usun_niepopularnych_wykonawcow_1_zapytanie
AS 
SELECT * 
FROM Czlonkowie_zespolu
WHERE     (IDWykonawcy NOT IN
                          (SELECT     IDWykonawcy
                            FROM          Imprezy));

CREATE PROCEDURE R17_usun_niepopularnych_wykonawcow_2()
DELETE FROM Wykonawcy
WHERE     (IDWykonawcy NOT IN
                          (SELECT     IDWykonawcy
                            FROM          Imprezy));

CREATE VIEW R17_usun_niepopularnych_wykonawcow_2_zapytanie
AS 
SELECT * 
FROM Wykonawcy
WHERE     (IDWykonawcy NOT IN
                          (SELECT     IDWykonawcy
                            FROM          Imprezy));

CREATE PROCEDURE R17_usun_czlonkow_nie_nalezacych_do_zespolu()
DELETE FROM Czlonkowie
WHERE     (IDCzlonka NOT IN
                          (SELECT     IDCzlonka
                            FROM          Czlonkowie_zespolu));

CREATE VIEW R17_usun_czlonkow_nie_nalezacych_do_zespolu_zapytanie
AS 
SELECT * 
FROM Czlonkowie
WHERE     (IDCzlonka NOT IN
                          (SELECT     IDCzlonka
                            FROM          Czlonkowie_zespolu));

CREATE PROCEDURE R17_usun_style_ktorych_nikt_nie_gra()
DELETE FROM Style_muzyczne
WHERE     (IDStylu NOT IN
                          (SELECT     IDStylu
                            FROM          Style_wykonawcow));

CREATE VIEW R17_usun_style_ktorych_nikt_nie_gra_zapytanie
AS 
SELECT * 
FROM Style_muzyczne
WHERE     (IDStylu NOT IN
                          (SELECT     IDStylu
                            FROM          Style_wykonawcow));

CREATE PROCEDURE R17_usun_zarchiwizowane_imprezy()
DELETE FROM Imprezy
WHERE     (NumerImprezy IN
                          (SELECT     NumerImprezy
                            FROM          Imprezy_archiwum));

CREATE VIEW R17_usun_zarchiwizowane_imprezy_zapytanie
AS 
SELECT * 
FROM Imprezy
WHERE     (NumerImprezy IN
                          (SELECT     NumerImprezy
                            FROM          Imprezy_archiwum));
