USE GrafikUczelniZmiana;
GO

CREATE PROCEDURE R15_skoryguj_kody_pocztowe_pracownikow
AS 
UPDATE    Pracownicy
SET              NumKierPracownika = '360'
WHERE     (KodPocztowyPracownika IN ('98270', '98271'));
GO

CREATE VIEW R15_skoryguj_kody_pocztowe_pracownikow_zapytanie
AS 
SELECT  NumKierPracownika, '360' As NowyNumKierunk
FROM    Pracownicy
WHERE     (KodPocztowyPracownika IN ('98270', '98271'));
GO

CREATE PROCEDURE R15_skoryguj_numer_kierunkowy_studenta
AS 
UPDATE    Studenci
SET              NumKierStudenta = '360'
WHERE     (KodPocztowyStudenta IN ('98270', '98271'));
GO

CREATE VIEW R15_skoryguj_numer_kierunkowy_studenta_zapytanie
AS 
SELECT  IDStudenta, ImieStudenta, NazwiskoStudenta, NumKierStudenta,  '360' AS NowyNumKierunk
FROM    Studenci
WHERE     (KodPocztowyStudenta IN ('98270', '98271'));
GO

CREATE PROCEDURE R15_podwyzki_dla_pelnoetatowych_wykladowcow
AS 
UPDATE    Pracownicy
SET              Wynagrodzenie = ROUND(Wynagrodzenie * 1.05, 0)
WHERE     (IDPracownika IN
                          (SELECT     IDPracownika
                            FROM          Wykladowcy
                            WHERE      Wykladowcy.Status = 'Pełny wymiar' AND Wykladowcy.Etat = 1));
GO

CREATE VIEW R15_podwyzki_dla_pelnoetatowych_wykladowcow_zapytanie
AS 
SELECT   Wynagrodzenie, ROUND(Wynagrodzenie * 1.05, 0) AS NoweWynagrodzenie
FROM    Pracownicy
WHERE     (IDPracownika IN
                          (SELECT     IDPracownika
                            FROM          Wykladowcy
                            WHERE      Wykladowcy.Status = 'Pełny wymiar' AND Wykladowcy.Etat = 1));
GO

CREATE PROCEDURE R15_zaktualizuj_srednia_ocen
AS 
UPDATE    Studenci
SET              SredniaOcenStudenta =
                          ISNULL((SELECT     Round(SUM(Zajecia.PunktyEdukacyjne * Grafik_studenta.Ocena) / SUM(Zajecia.PunktyEdukacyjne), 3)
                            FROM          Zajecia INNER JOIN
                                                   Grafik_studenta ON Zajecia.IDZajec = Grafik_studenta.IDZajec
                            WHERE      (Grafik_studenta.StatusZajec = 2) AND (Grafik_studenta.IDStudenta = Studenci.IDStudenta)), 0);
GO

CREATE VIEW R15_zaktualizuj_srednia_ocen_zapytanie
AS 
SELECT   IDStudenta, ImieStudenta, NazwiskoStudenta, SredniaOcenStudenta, 
                          ISNULL((SELECT     Round(SUM(Zajecia.PunktyEdukacyjne * Grafik_studenta.Ocena) / SUM(Zajecia.PunktyEdukacyjne), 3)
                            FROM          Zajecia INNER JOIN
                                                   Grafik_studenta ON Zajecia.IDZajec = Grafik_studenta.IDZajec
                            WHERE      (Grafik_studenta.StatusZajec = 2) AND (Grafik_studenta.IDStudenta = Studenci.IDStudenta)), 0) AS NowaSredniaStudenta
FROM    Studenci;
GO

CREATE PROCEDURE R16_dodaj_dziedzine
AS 
INSERT INTO Dziedziny
SELECT     'ITA' AS IDDziedziny, 'Włoski' AS OpisDziedziny, IDWydzialu
FROM         Wydzialy
WHERE     (NazwaWydzialu = 'Nauki humanistyczne');
GO

CREATE VIEW R16_dodaj_dziedzine_zapytanie
AS 
SELECT     'ITA' AS IDDziedziny, 'Włoski' AS OpisDziedziny, IDWydzialu
FROM         Wydzialy
WHERE     (NazwaWydzialu = 'Nauki humanistyczne');
GO

CREATE PROCEDURE R16_dodaj_nowe_zajecia_z_ksiegowosci
AS 
SET IDENTITY_INSERT Zajecia ON
INSERT INTO Zajecia
                      (IDZajec, IDPrzedmiotu, IDSali, PunktyEdukacyjne, DataRozpoczecia, GodzinaRozpoczecia, CzasTrwania, PlanNaWtorek, PlanNaCzwartek)
VALUES     (6699, 4, 3315, 5, '2014-01-14', '15:00:00', 80, 1, 1);
SET IDENTITY_INSERT Zajecia OFF
GO

CREATE VIEW R16_dodaj_nowe_zajecia_z_ksiegowosci_zapytanie
AS
SELECT 6699 As IDZajec, 4 As IDPrzedmiotu, 3315 As IDSali, 5 As PunktyEdukacyjne, '2014-01-14' As DataRozpoczecia, '15:00:00' As GodzinaRozpoczecia, 80 As CzasTrwania, -1 As PlanNaWtorek, -1 As PlanNaCzwartek
FROM Zajecia
WHERE IDZajec = 1000;
GO

CREATE PROCEDURE R16_dodaj_studenta
AS 
INSERT INTO Studenci
                      (ImieStudenta, NazwiskoStudenta, AdresStudenta, MiastoStudenta, StanZamStudenta, KodPocztowyStudenta, NumKierStudenta, TelefonStudenta)
SELECT     'Angel' AS ImieStudenta, NazwiskoStudenta, AdresStudenta, MiastoStudenta, StanZamStudenta, KodPocztowyStudenta, NumKierStudenta, TelefonStudenta
FROM         Studenci
WHERE     (ImieStudenta = 'John') AND (NazwiskoStudenta = 'Kennedy');
GO

CREATE VIEW R16_dodaj_studenta_zapytanie
AS 
SELECT     'Angel' AS ImieStudenta, NazwiskoStudenta, AdresStudenta, MiastoStudenta, StanZamStudenta, KodPocztowyStudenta, NumKierStudenta, TelefonStudenta
FROM         Studenci
WHERE     (ImieStudenta = 'John') AND (NazwiskoStudenta = 'Kennedy');
GO

CREATE PROCEDURE R16_przyjmij_pracownika_na_studia
AS 
INSERT INTO Studenci
                      (ImieStudenta, NazwiskoStudenta, AdresStudenta, MiastoStudenta, StanZamStudenta, KodPocztowyStudenta, NumKierStudenta, TelefonStudenta)
SELECT     ImiePracownika, NazwiskoPracownika, AdresPracownika, MiastoPracownika, StanZamPracownika, KodPocztowyPracownika, NumKierPracownika, TelefonPracownika
FROM         Pracownicy
WHERE     (ImiePracownika = 'Tim') AND (NazwiskoPracownika = 'Smith');
GO

CREATE VIEW R16_przyjmij_pracownika_na_studia_zapytanie
AS 
SELECT     ImiePracownika, NazwiskoPracownika, AdresPracownika, MiastoPracownika, StanZamPracownika, KodPocztowyPracownika, NumKierPracownika, TelefonPracownika
FROM         Pracownicy
WHERE     (ImiePracownika = 'Tim') AND (NazwiskoPracownika = 'Smith');
GO

CREATE PROCEDURE R17_usun_zajecia_bez_studentow_1
AS 
DELETE FROM Zajecia_wykladowcow
WHERE     (IDZajec NOT IN
                          (SELECT     IDZajec
                            FROM          Grafik_studenta));
GO

CREATE VIEW R17_usun_zajecia_bez_studentow_1_zapytanie
AS 
SELECT * 
FROM Zajecia_wykladowcow
WHERE     (IDZajec NOT IN
                          (SELECT     IDZajec
                            FROM          Grafik_studenta));
GO

CREATE PROCEDURE R17_usun_zajecia_bez_studentow_2
AS 
DELETE FROM Zajecia
WHERE     (IDZajec NOT IN
                          (SELECT     IDZajec
                            FROM          Grafik_studenta));
GO

CREATE VIEW R17_usun_zajecia_bez_studentow_2_zapytanie
AS 
SELECT * 
FROM Zajecia
WHERE     (IDZajec NOT IN
                          (SELECT     IDZajec
                            FROM          Grafik_studenta));
GO

CREATE PROCEDURE R17_usun_studentow_nie_uczeszczajacych_na_zajecia
AS 
DELETE FROM Studenci
WHERE     (IDStudenta NOT IN
                          (SELECT     IDStudenta
                            FROM          Grafik_studenta));
GO

CREATE VIEW R17_usun_studentow_nie_uczeszczajacych_na_zajecia_zapytanie
AS 
SELECT *  
FROM Studenci
WHERE     (IDStudenta NOT IN
                          (SELECT     IDStudenta
                            FROM          Grafik_studenta));
GO

CREATE PROCEDURE R17_usun_przedmioty_bez_zajec_1
AS 
DELETE FROM Przedmioty_wykladowcow
WHERE     (IDPrzedmiotu NOT IN
                          (SELECT     IDPrzedmiotu
                            FROM          Zajecia));
GO

CREATE VIEW R17_usun_przedmioty_bez_zajec_1_zapytanie
AS 
SELECT * 
FROM Przedmioty_wykladowcow
WHERE     (IDPrzedmiotu NOT IN
                          (SELECT     IDPrzedmiotu
                            FROM          Zajecia));
GO

CREATE PROCEDURE R17_usun_przedmioty_bez_zajec_2
AS 
DELETE FROM Przedmioty
WHERE     (IDPrzedmiotu NOT IN
                          (SELECT     IDPrzedmiotu
                            FROM          Zajecia));
GO

CREATE VIEW R17_usun_przedmioty_bez_zajec_2_zapytanie
AS 
SELECT * 
FROM Przedmioty
WHERE     (IDPrzedmiotu NOT IN
                          (SELECT     IDPrzedmiotu
                            FROM          Zajecia));
GO

