Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --------------------------------------
- ---- KOLOKWIUM TRIGGERY I FUNKCJE ----
- --------------------------------------
- /********************/
- --- ZADANIE 1 - 8 ----
- ------ TRIGGERY ------
- ----------------------
- --- ZADANIE 9 - 12 ---
- ----- PORCEDURY ------
- ----------------------
- --- ZADANIE 13 - 18 --
- ------- FUNKCJE ------
- /********************/
- /*********************/
- ----------------------
- ------ Triggery ------
- ----------------------
- /*********************/
- /*
- -------------
- --Zadanie 1--
- -------------
- Do tabeli działy dopisz kolumnę data_przyjęcia.
- Przy tworzeniu nowego pracownika z ustawionym działem
- ustawiaj wartość kolumny data_przyjęcia dla zadanego działu
- wpisując w nią komunikat z obecną datą oraz imieniem i nazwiskiem pracownika.
- Format komunikatu:
- 'Ostatni zatrudniony pracownik Jan Kowalski: Dec 18 2006 9:01PM'
- */
- select * from dzialy
- alter table dzialy add data_przyjecia varchar(200)
- alter table dzialy drop column data_przyjecia
- create trigger zadanie1 on pracownicy
- for insert
- as
- declare @imie varchar(30)
- declare @nazwisko varchar(30)
- declare @data datetime
- set @imie = (select imie from inserted)
- set @nazwisko = (select nazwisko from inserted)
- set @data = getdate()
- begin
- update dzialy set data_przyjecia = 'Ostatni zatrudniony pracownik '+@imie+' '+@nazwisko+': '+cast(@data as varchar(30))
- where dzialID = (select dzialID from inserted)
- end
- insert into pracownicy(imie,nazwisko,dzialID) values('Jan','Kowalski',3)
- delete from dzialy where dzialID > 4
- drop trigger zadanie1
- /*
- -------------
- --Zadanie 2--
- -------------
- Do tabeli pracownicy dodaj kolumnę tekstową last_sallary.
- Przy tworzeniu nowego wpisu w tabeli zarobki ustawiaj wartość nowo dodanej kolumny
- dla pracownika, który otrzymał wynagrodzenie wpisując w nią komunikat z obecną datą.
- Wymagany format komunikatu:
- 'Data ostatniej wypłaty: Jan 03 2006 10:11PM'
- */
- alter table pracownicy add last_sallary varchar(200)
- alter table pracownicy drop column last_sallary
- create trigger zadanie2 on zarobki
- for insert
- as
- begin
- update pracownicy set last_sallary = 'Data ostatniej wypłaty: '+cast(getdate() as varchar(30))
- where pracID = (select pracID from inserted)
- end
- select * from pracownicy
- select * from zarobki
- drop trigger zadanie2
- insert into zarobki(brutto,pracID) values (12345,6)
- /*
- ------------- ----------------------------------------------------------------- NO IDEA, zadanko z ubiegłych lat, na jakiejś innej bazie chyba
- --Zadanie 3--
- -------------
- Stworzyć wyzwalacz (trigger) na tabeli – zamowienia – który uruchomiony jest po wstawieniu rekordu do tej tabeli.
- Wyzwalacz ma wstawić do tabeli – dane – dane klienta z tabeli – klienci – oraz zaktualizować kolumny w tabeli – zamowienia:
- - numer zamówienia – zwiększyć o 1 ostatnią wartość;
- - data realizacji zamówienia – dodać 7 dni do daty dodania zamówienia.
- */
- -- KOD Z ZAJĘC
- CREATE TRIGGER test
- ON zamowienia
- AFTER INSERT
- AS
- DECLARE @ID BIGINT, @ID_K BIGINT;
- DECLARE @NR INT, @data DATETIME;
- SELECT @ID = [ID_zamowienia], @ID_k = [ID_klienta] FROM [inserted];
- SELECT @NR = MAX([numer_zamowienia]) FROM [zamowienia];
- SELECT @data = [data_zamowienia] FROM [zamowienia] WHERE [ID_zamowienia] = @ID;
- BEGIN
- IF(@ID IS NOT NULL) AND (@ID_k IS NOT NULL)
- BEGIN
- INSERT INTO [dane](ID_zamowienia, imie, nazwisko, adres, miasto, kod_pocztowy)
- (SELECT @ID, imie, nazwisko, adres, miasto, kod_pocztowy FROM klienci WHERE [ID_klienta] = @ID_k);
- IF @NR IS NULL
- BEGIN
- SET @NR = 0;
- END
- UPDATE [zamowienia] SET numer_zamowienia = (@NR + 1) , [data_realizacji] =
- DATEADD(DAY, 7, @data) WHERE ID_zamowienia = @ID;
- END
- END;
- /*
- ------------- ----------------------------------------------------------------- NO IDEA, zadanko z ubiegłych lat, na jakiejś innej bazie chyba, ponoć nie mielismy takich zagadnień na zajęciach
- --zadanie 4--
- -------------
- Utwórz wyzwalacz na tabeli – zamowienia_pozycje – który uruchamiany jest zamiast INSERT INTO.
- Wyzwalacz ma sprawdzić różnicę ilości w magazynie oraz ilości sztuk zamawianych produktu. Ich różnica ma być większa bądź równa 0 i jest to warunek.
- - Jeśli spełniony jest warunek wyzwalacz ma obliczyć wartość netto, brutto oraz VAT-u następnie wstawić rekord do tabeli – zamowienia_pozycje – uzupełniając
- wszystkie pola danymi z odpowiedniej tabeli oraz wynikami z wyliczeń. Zaktualizować w tabeli – produkty – ilość w magazynie o wartość z różnicy.
- - Jeśli nie jest spełniony warunek wyzwalacz ma przypisać 0 do wartości netto, brutto oraz VAT-u, a w uwadze dodać informację: „!!UWAGA!! Zbyt mało towaru,
- aby zamówić” następnie wstawić rekord do tabeli – zamowienia_pozycje – uzupełniając wszystkie pola danymi z odpowiedniej tabeli.
- Obliczyć sumę wartości netto, brutto oraz VAT-u na podstawie tabeli – zamowienia_pozycje – następnie zaktualizować w tabeli – zamowienia – odpowiednie pola
- o wyniki z wyliczeń.
- */
- /*
- -------------
- --Zadanie 5--
- -------------
- Do tabeli pracownicy dodaj kolumne tekstowa email o dlugosci 100 znakow.
- Podczas tworzenia pracownika lub jego modyfikacji uzupełnij tą kolumnę
- emailem skladającym się z:
- pierwszych trzech liter imienia, kropki,
- pierwszych pięciu liter nazwiska oraz ciągu '@p.lodz.pl'
- Pamiętaj, aby usunąc ewentualne zewnętrzne białe znaki oraz
- aby zamienic wewnętrzne spacje na znak podkreslenia.
- */
- select * from pracownicy
- alter table pracownicy add email varchar(100)
- create trigger email on pracownicy
- for insert, update
- as
- begin
- declare @imie varchar(30)
- declare @nazwisko varchar(30)
- declare @email varchar(100)
- set @imie = (select imie from inserted)
- set @nazwisko = (select nazwisko from inserted)
- set @email = (SUBSTRING(@imie,1,3) +'.'+ SUBSTRING(@nazwisko, 1,5) +'@p.lodz.pl')
- update pracownicy set email = SUBSTRING(@imie,1,3) +'.'+ SUBSTRING(@nazwisko, 1,5) +'@p.lodz.pl'
- where pracID = (select pracID from inserted)
- end
- drop trigger email
- update pracownicy set imie = 'Janusz', nazwisko = 'Moooras' where pracID = 2;
- /*
- ------------- ----------------------------------------------------------------- NO IDEA, a chuj to wie jak to zrobic
- --Zadanie 6--
- -------------
- stwórz triggera który przenosi dane dotyczące zarobków
- do tabeli historycznej będącej dokładną kopią tabeli
- zarobki bez kolumny aktualny. Uwzględnij w tej tabeli datę
- przenosin oraz użytkownika który kasował dane
- */
- select * from zarobki
- /*
- -------------
- --Zadanie 7--
- -------------
- Dodaj kolumnę tekstową login1 do tabeli pracownicy.
- Przy tworzeniu nowego pracownika lub modyfikacji istniejącego
- w tabeli pracownicy ustawiaj wartość kolumny login1
- na pierwszą i ostatnią literę z imienia i pierwszą i ostatnią literą nazwiska. W tak stworzonej
- wartości zastosuj wielkie litery.
- Przykład dla osoby: Tomasz Pawlak login1 będzie skłądał się z TZPK
- */
- alter table pracownicy add login1 varchar(200)
- create trigger login1 on pracownicy
- for insert, update
- as
- declare @imie varchar(30)
- declare @nazwisko varchar(30)
- declare @login varchar(200)
- set @imie = (select imie from inserted)
- set @nazwisko = (select nazwisko from inserted)
- set @login = upper(left(@imie,1))+upper(right(@imie,1))+upper(left(@nazwisko,1))+upper(right(@nazwisko,1))
- begin
- update pracownicy set login1 = @login
- where pracid = (select pracID from inserted)
- end
- insert into pracownicy(imie, nazwisko) values('Kurdeco','Dzialaj')
- drop trigger login1
- /*
- --------------
- --Zadanie 8--
- --------------
- Do tabeli pracownicy dodaj kolumne tekstowa poczta o dlugosci 200 znakow.
- Podczas dodawania i modyfikacji osoby ustaw tą kolumnę ciągiem złożonym z:
- pierwszych 5 liter nazwiska, myślnika,
- pierwszych trzech liter imienia oraz ciągu '@wp.pl'
- Pamiętaj, aby usunąc ewentualne zewnętrzne białe znaki oraz
- aby zamienic wewnętrzne spacje na znak '_'.
- */
- select * from pracownicy
- alter table pracownicy add poczta varchar(200)
- create trigger email on pracownicy
- for insert, update
- as
- declare @imie varchar(30)
- declare @nazwisko varchar(30)
- declare @email varchar(200)
- set @imie = (select imie from inserted)
- set @nazwisko = (select nazwisko from inserted)
- begin
- update pracownicy set poczta = SUBSTRING(@nazwisko,1,5)+ '_' +substring(@imie,1,3)+ '@wp.pl'
- where pracid = (select pracID from inserted)
- end
- drop trigger email
- insert into pracownicy(imie, nazwisko) values('Imie', 'Nazwisko')
- select * from pracownicy
- /**********************/
- -----------------------
- ------ PROCEDURY ------
- -----------------------
- /**********************/
- /*
- -------------
- --Zadanie 9--
- -------------
- Utwórz procedurę , która wyświetli w formie tekstowej (w oknie messages) wszystkich pracowników i liczbę ich wypłat. Pod uwagę bierz tylko tych pracowników,
- którzy posiadają więcej niż X (gdzie X to pierwszy parametr procedury) wpisów w tabeli zarobki. Dodatkowo wybierz tylko te wpisy, które zostały stworzone po
- dacie podanej w drugim parametrze. W przypadku nie podania w wywołaniu procedury drugiego parametru nie uwzględniaj kryterium daty.
- Przykładowy wynik wywołania:
- 'Pracownik Jan Kowalski od dnia Jan 1 2001 12:00AM otrzymał 5 wypłat'
- lub
- 'Pracownik Jan Kowalski otrzymał 5 wypłat'
- */
- select p.pracID, imie, nazwisko, count(zarID) as ile from pracownicy p
- join zarobki z on z.pracID = p.pracID
- where od > '01-01-2006'
- group by imie, nazwisko, p.pracID
- select * from pracownicy
- select * from zarobki
- select * from zarobki
- where od > '01-01-2006'
- select max(pracID) from pracownicy
- select pracID from zarobki
- alter procedure Zadanie9
- @X int,
- @od_kiedy datetime = ''
- as
- begin
- declare @imie varchar(50)
- declare @nazwisko varchar(50)
- declare @ile_wyplat int
- declare @od DATETIME
- declare @i int = 1
- declare @pracID int
- declare @y int
- select @y = max(pracID) from zarobki
- while @i <= @y
- begin
- select @imie = imie, @nazwisko = nazwisko, @ile_wyplat = count(zarID), @pracID = z.pracID from pracownicy p
- join zarobki z on z.pracID = p.pracID
- where p.pracID = @i and od > @od_kiedy
- group by imie, nazwisko, z.pracID
- if @i = @pracID
- begin
- if @ile_wyplat > @X
- begin
- print ' Pracownik nr.' + cast(@i as varchar) +' '+ cast(@imie as varchar) + ' ' + cast(@nazwisko as varchar) +' otrzymal '+ cast(@ile_wyplat as varchar) + ' wyplat'
- end
- end
- set @i = @i + 1
- end
- end
- exec Zadanie9 0, '01-01-2006'
- /*
- --------------
- --Zadanie 10--
- --------------
- Napisz procedurę która zwróć adres w posatci
- Piotrkowska
- 123/23
- m.30
- 90-123
- Łódź
- */
- create PROC Zadanie10
- @caly_adres VARCHAR(100)
- AS
- BEGIN
- DECLARE @dl_ul INTEGER
- DECLARE @pozycja INTEGER
- DECLARE @reszta VARCHAR(100)
- SET @reszta = @caly_adres
- SET @pozycja = CHARINDEX(' ', @reszta)
- PRINT SUBSTRING(@reszta,0,@pozycja+1)
- WHILE(LEN(@reszta)>2)
- BEGIN
- SET @reszta = SUBSTRING(@reszta, @pozycja+1, LEN(@reszta))
- SET @pozycja = CHARINDEX(' ', @reszta)
- PRINT SUBSTRING(@reszta,0,@pozycja+1)
- END
- END
- EXEC Zadanie10 'Piotrkowska 123/23 m.30 90-123 Łódź '
- /*
- --------------
- --Zadanie 11--
- --------------
- Stwórz procedurę , która wyświetli w formie tekstowej (w oknie messages)
- co trzeciego pracownika licząc od konca, którego wiek jest pomiędzy 2 parametrami zadanymi w definicji procedury.
- W przypadku nie podania parametru pierwszego domyślnie ustaw 18 a w przypadku nie podania drugiego
- ustaw 30.
- Przykłąd wyniku:
- 'Osoba Tomasz Kozak 20 lat'
- 'Osoba Piotr Wozniak 19 lat'
- 'Osoba Anna Wozniak 36 lat'
- */
- select * from pracownicy
- where wiek >= 18 and wiek <= 30
- alter procedure Zadanie11
- @X int = 18,
- @Y int = 30
- as
- begin
- declare @imie varchar(30)
- declare @nazwisko varchar(30)
- declare @i int = 1
- declare @j int = 1
- declare @ilu int
- declare @pracID int
- select @ilu = max(pracID) from pracownicy
- while @i <= @ilu
- begin
- select @imie = imie, @nazwisko = nazwisko, @pracID =pracID from pracownicy
- where wiek >= @X and wiek <= @Y
- and pracID = @i
- if @i = @pracID
- begin
- if @j % 3 = 1
- begin
- print ' Pracownik nr. '+ cast(@i as varchar) +' '+ cast(@imie as varchar) + ' ' + cast(@nazwisko as varchar)
- end
- set @j = @j +1
- end
- set @i = @i + 1
- end
- end
- exec Zadanie11 @y = 50
- /*
- --------------
- --Zadanie 12--
- --------------
- Stwórz procedurę, która wyświetli w formie tekstowej (w oknie messages) co drugiego pracownika
- pracownika, którego nazwisko zaczyna się na literę podaną w parametrze. W przypadku nie podania
- parametru uwzględniaj tylko osby o nazwisku na literę 'Z'. W przypadku podania w parametrze wartosci
- null nie uwzględniaj kryterium.
- Przykłąd wyniku:
- 'Pracownik nr #1 Jan Kowalski 25 lat'
- 'Pracownik nr #3 Tomasz Kowalski 26 lat'
- 'Pracownik nr #5 Piotr Kowalski 29 lat'
- */
- select * from pracownicy
- where nazwisko like 'N%'
- create procedure Zadanie12
- @X varchar(5) = 'Z%'
- as
- begin
- declare @imie varchar(30)
- declare @nazwisko varchar(30)
- declare @i int = 1
- declare @j int = 1
- declare @ilu int
- declare @pracID int
- select @ilu = max(pracID) from pracownicy
- while @i <= @ilu
- begin
- select @imie = imie, @nazwisko = nazwisko, @pracID =pracID from pracownicy
- where nazwisko like @X
- and pracID = @i
- if @i = @pracID
- begin
- if @j % 2 = 1
- begin
- print ' Pracownik nr. '+ cast(@i as varchar) +' '+ cast(@imie as varchar) + ' ' + cast(@nazwisko as varchar)
- end
- set @j = @j +1
- end
- set @i = @i + 1
- end
- end
- exec Zadanie12 'N%'
- drop procedure Zadanie12
- /************************/
- -------------------------
- -------- FUNKCJE --------
- -------------------------
- /************************/
- /*
- --------------
- --Zadanie 13--
- --------------
- Dodaj funkcję fun_kol, która zwróci tekstową nazwę dnia tygodnia powstałą
- z daty powstałej z dodania X dni (gdzie X to drugi parametr) do daty podanej
- w pierwszym parametrze.
- W przypadku ustawienia null w parametrze X dodaj 10 dni. Przykładowy wynik wywołania funkcji:
- 'Otrzymany dzień to: Sunday'
- */
- create function fun_kol(@data datetime, @ileDni int)
- returns varchar(100)
- as
- begin
- declare @wynik datetime
- if @ileDni is null
- begin
- set @wynik = dateadd(day,10,@data)
- return cast(datename(weekday, @wynik) as varchar(50))
- end
- set @wynik = dateadd(day,@ileDni,@data)
- return cast(datename(WEEKDAY,@wynik) as varchar(50))
- end
- select dbo.fun_kol('09/09/2017',null)
- /*
- --------------
- --Zadanie 14--
- --------------
- Utwórz funkcje, która będzie zwracać powtórzony ciag znaków podany w parametrze tyle razy
- ile podane to będzie w następnym parametrze. Co drugie powielenie ciągu wejściowego ma mieć
- odwróconą kolejność znaków poczynając od pierwszego. Pomiedzy powieleniami dodaj podkreslnik.
- Przykładowe wywołanie dla ciągu 'XYZ' i powielenia 3 - 'ZYX_XYZ_ZYX'.
- */
- create function Zadanie14(@ciag varchar(50), @ileRazy int) returns varchar(200)
- as
- begin
- declare @wynik varchar(200)
- declare @licznik int
- set @licznik = 0
- set @wynik = ''
- while @licznik < @ileRazy
- begin
- if @licznik % 2 = 0
- begin
- set @wynik = @wynik+reverse(@ciag);
- set @licznik = @licznik + 1
- end
- else
- begin
- set @wynik = @wynik+@ciag;
- set @licznik = @licznik + 1
- end
- if @licznik <> @ileRazy
- begin
- set @wynik = @wynik + '_'
- end
- end
- return @wynik
- end
- select dbo.Zadanie14('XYZ',3)
- /*
- --------------
- --Zadanie 15--
- --------------
- Utwórz funkcję, która będzie zwracać komunikat o różnicy w dniach
- między datą bieżącą a datą podaną w parametrze.
- W przypadku tego samego dnia zwróć komunikat o równości dat.
- Przykład:
- 'Między datą obecną a datą Dec 12 2012 12:00AM jest XXX dni różnicy.'
- lub
- 'Daty data podana w parametrze jest datą dzisiejszą.'
- */
- create function Zadanie15(@data datetime) returns varchar(100)
- as
- begin
- declare @wynik int
- declare @dzis datetime
- declare @opis varchar(100)
- set @dzis = getdate()
- set @wynik = datediff(day,@dzis,@data)
- if @wynik = 0
- begin
- set @opis = 'Podana data jest data dzisiejsza!'
- end
- else
- begin
- set @opis = 'Miedzy data obecną a datą '+cast((@data) as varchar(20))+' jest '+cast((@wynik) as varchar(20))+' dni roznicy!'
- end
- return @opis
- end
- select dbo.Zadanie15('10/10/2017')
- /*
- --------------
- --Zadanie 16--
- --------------
- STWÓRZ FUNKCJĘ, KTÓRA BĘDZIE PRZYJMOWAĆ 2 ARGUMENTY BĘDĄCE CIĄGAMI ZNAKÓW(MAX. DO 100 ZNAKÓW)
- I ZWRÓCI CIĄGI ZNAKÓW POWSTAŁE Z WYMIESZANIA NA PRZEMIAN ZNAKÓW Z CIĄGU PIERWSZEGO Z CIĄGIEM DRUGIM.
- DODATKOWO FUNKCJA TA MA ZMIENIAĆ KOLEJNOŚĆ ZNAKÓW.
- PRZYKLAD:
- - funkcja('ABCDE', '12345')
- - ZWRÓCI: '5E4D3C2B1A'
- W PRZYPADKU NIEZGODNOŚCI DŁUGOŚCI CIĄGÓW WEJŚCIOWYCH WYŚWIETL KOMUNIKAT TYPU:
- - 'Blad dlugosci znakow: X, Y'
- GDZIE 'X' I 'Y' TO DŁUGOŚCI CIĄGÓW WEJŚCIOWYCH
- */
- create FUNCTION Zadanie16(@X VARCHAR(100), @Y VARCHAR(100))
- RETURNS VARCHAR(200)
- AS
- BEGIN
- IF(LEN(@X) != LEN(@Y))
- RETURN 'Błąd długości znaków: ' + CAST(LEN(@X) AS VARCHAR) + ' <> ' + CAST(LEN(@Y) AS VARCHAR)
- ELSE
- DECLARE @licznik INT;
- DECLARE @result VARCHAR(200);
- SET @licznik = 0;
- SET @result = '';
- WHILE(@licznik <= LEN(@X))
- BEGIN
- SET @licznik = @licznik + 1;
- SET @result = @result + SUBSTRING(@X, @licznik-1, 1)
- SET @result = @result + SUBSTRING(@Y, @licznik-1, 1)
- END
- RETURN REVERSE(@result)
- END
- SELECT dbo.Zadanie16('ABCDE', '12345')
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- tworzyć tabele o nazwie: zarobki_audyt.
- Tabela ta zawiera kolumny:
- - identyfikator główny (auto inkrementacja);
- - identyfikator zarobku (z tabeli zarobki - brak relacji);
- - data (z tabeli zarobki);
- - brutto (z tabeli zarobki);
- - id_pracownicy (z tabeli zarobki - brak relacji);
- - operacja;
- - data operacji.
- Ustawić na kolumnie - operacja - ograniczenie, które przyjmuje tylko
- nazwy INSERT, UPDATE i DELETE.
- Na tabeli - zarobki - utworzyć trigger o nazwie: audyt - który
- wywoływany jest po operacjach wstawiania, aktualizacji i usuwania.
- Wyzwalacz ten ma wstawiać odpowiedni rekord do tabeli
- - zarobki_audyt - w zależności od wykonywanej operacji.
- */
- Stworzyć wyzwalacz (trigger) na tabeli – zamowienia – który uruchomiony
- jest po wstawieniu rekordu do tej tabeli.
- Wyzwalacz ma wstawić do tabeli – dane – dane klienta z tabeli –
- klienci – oraz zaktualizować kolumny w tabeli – zamowienia:
- - numer zamówienia – zwiększyć o 1 ostatnią wartość;
- - data realizacji zamówienia – dodać 7 dni do daty dodania
- zamówienia.
- Stwórz procedurę , która wyświetli w formie tekstowej (w oknie messages)
- co trzeciego pracownika licząc od konca, którego wiek jest pomiędzy 2 parametrami zadanymi w definicji procedury.
- W przypadku nie podania parametru pierwszego domyślnie ustaw 18 a w przypadku nie podania drugiego
- ustaw 30.
- Przykłąd wyniku:
- 'Osoba Tomasz Kozak 20 lat'
- 'Osoba Piotr Wozniak 19 lat'
- 'Osoba Anna Wozniak 36 lat'
- */
- create proc procedurakolokwium
- @wiekA int,
- @wiekB int,
- @licznik int,
- @temp int
- as
- begin
- if @wiekA is null
- begin
- set @wiekA = 18;
- end
- if @wiekB is null
- begin
- set @wiekB = 30;
- end
- set @licznik = (select count(nazwisko) from pracownicy);
- set @temp = 1;
- while @temp <= @licznik
- begin
- print ('Osoba' + (select imie, nazwisko, wiek from pracownicy where wiek >= @wiekA and wiek <= @wiekB))
- set @temp=(@temp + 1);
- end
- end
- Zadanie 1
- Dodaj kolumnę tekstową login1 do tabeli pracownicy.
- Przy tworzeniu nowego pracownika lub modyfikacji istniejącego
- w tabeli pracownicy ustawiaj wartość kolumny login1
- na pierwszą i ostatnią literę z imienia i pierwszą i ostatnią literą nazwiska. W tak stworzonej
- wartości zastosuj wielkie litery.
- Przykład dla osoby: Tomasz Pawlak login1 będzie skłądał się z TZPK
- ALTER TABLE pracownicy
- ADD login1 varchar(100)
- select * from pracownicy
- alter TRIGGER triggerek
- ON pracownicy
- AFTER UPDATE, INSERT
- AS
- DECLARE @imie varchar(100);
- DECLARE @nazwisko varchar(100);
- DECLARE @1stname varchar;
- DECLARE @2ndname varchar;
- DECLARE @1stsurname varchar;
- DECLARE @2ndsurname varchar;
- DECLARE @position int;
- DECLARE @position1 int;
- DECLARE @str varchar(100);
- BEGIN
- SET @imie = (SELECT imie
- from inserted)
- SET @nazwisko = (SELECT nazwisko
- from inserted)
- SET @position = LEN(@imie)
- SET @position1 = LEN(@nazwisko)
- SET @1stname = substring(@imie, 0, 2)
- SET @2ndname = substring(@imie, @position, @position+1)
- SET @1stsurname = substring(@nazwisko, 0, 2)
- SET @2ndsurname = substring(@nazwisko, @position1, @position1+1)
- set @str = @1stname + @2ndname + @1stsurname + @2ndsurname
- UPDATE pracownicy
- SET login1 = @str
- END
- Zadanie 2
- Do tabeli działy dopisz kolumnę data_przyjęcia.
- Przy tworzeniu nowego pracownika z ustawionym działem
- ustawiaj wartość kolumny data_przyjęcia dla zadanego działu
- wpisując w nią komunikat z obecną datą oraz imieniem i nazwiskiem pracownika.
- Format komunikatu:
- 'Ostatni zatrudniony pracownik Jan Kowalski: Dec 18 2006 9:01PM'
- ALTER TABLE dzialy
- ADD data_przyjęcia varchar(100)
- select * from dzialy
- select * from pracownicy
- ALTER TRIGGER trig
- ON pracownicy
- AFTER INSERT
- AS
- BEGIN
- declare @imie varchar(40)
- declare @nazwisko varchar(40)
- declare @dzialIDh int
- declare @data date
- declare @strout varchar(200)
- set @dzialIDh = (SELECT dzialID FROM INSERTED)
- set @imie = (SELECT imie from INSERTED)
- set @nazwisko = (SELECT nazwisko from INSERTED)
- set @strout = 'Ostatni zatrudniony pracownik ' + @imie + ' ' + @nazwisko + ': ' + cast(getdate() as varchar)
- UPDATE dzialy
- set data_przyjęcia = @strout
- where dzialID = @dzialIDh
- END
- insert into dbo.pracownicy(nazwisko,imie,wiek,dzialID,wzrost,dousuniecia2) values('Kowalik','Janek',30,3, 2, 3)
- zadanie 3
- Utwórz procedurę , która wyświetli w formie tekstowej (w oknie messages)
- wszystkich pracowników i liczbę ich wypłat. Pod uwagę bierz tylko tych pracowników,
- którzy posiadają więcej niż X (gdzie X to pierwszy parametr procedury)
- wpisów w tabeli zarobki. Dodatkowo wybierz tylko te wpisy, które zostały stworzone po
- dacie podanej w drugim parametrze. W przypadku nie podania w wywołaniu procedury
- drugiego parametru nie uwzględniaj kryterium daty.
- Przykładowy wynik wywołania:
- 'Pracownik Jan Kowalski od dnia Jan 1 2001 12:00AM otrzymał 5 wypłat'
- lub
- 'Pracownik Jan Kowalski otrzymał 5 wypłat'
- select * from pracownicy
- select * from zarobki
- create procedure procka2
- @wpisy int,
- @data date = null
- AS
- BEGIN
- declare @table table
- (
- ID INT IDENTITY(1,1),
- Imie varchar(50),
- Nazwisko varchar(50),
- Ilosc_wyplat int
- )
- insert into @table
- select imie, nazwisko, count(brutto) as ilosc
- from pracownicy
- join zarobki ON pracownicy.pracID = zarobki.pracID
- group by pracownicy.pracid, pracownicy.imie, pracownicy.nazwisko
- having count(brutto) > @wpisy
- declare @imie varchar(50)
- declare @nazwisko varchar(50)
- declare @ilosc int
- declare @i int
- declare @licznik int
- declare @str varchar(50)
- set @licznik = 0
- set @i = (SELECT COUNT(*) FROM @table)
- while(@licznik < @i)
- begin
- set @imie = ( SELECT imie
- from @table
- where ID = @licznik+1 )
- set @nazwisko = ( SELECT nazwisko
- from @table
- where ID = @licznik+1 )
- set @ilosc = ( SELECT Ilosc_wyplat
- from @table
- where ID = @licznik+1 )
- set @str = 'Pracownik ' + @imie + ' ' + @nazwisko + ' otrzymal ' + cast(@ilosc as varchar) + ' wplat'
- print @str
- set @licznik = @licznik + 1
- end
- END
- exec procka2 2
- zadanie 4
- Stwórz procedurę , która wyświetli w formie tekstowej (w oknie messages)
- co trzeciego pracownika licząc od konca, którego wiek jest pomiędzy 2 parametrami zadanymi w definicji procedury.
- W przypadku nie podania parametru pierwszego domyślnie ustaw 18 a w przypadku nie podania drugiego
- ustaw 30.
- Przykłąd wyniku:
- 'Osoba Tomasz Kozak 20 lat'
- 'Osoba Piotr Wozniak 19 lat'
- 'Osoba Anna Wozniak 36 lat'
- select * from pracownicy
- alter PROCEDURE procka
- @1st int = 18,
- @2nd int = 30
- AS BEGIN
- DECLARE @tabelka table
- (
- ajdi int not null identity(1,1),
- imie varchar(100) not null,
- nazwisko varchar(100) not null,
- wiek int not null
- )
- INSERT INTO @tabelka
- SELECT imie, nazwisko, wiek
- from pracownicy
- where wiek > @1st AND wiek < @2nd
- declare @imie varchar(40)
- declare @nazwisko varchar(40)
- declare @wiek int
- declare @strout varchar(100)
- declare @i int
- set @i = 1
- declare @size int
- set @size = (SELECT COUNT(*) FROM @tabelka)
- while(@i < @size)
- BEGIN
- if(@i % 3 = 0)
- BEGIN
- set @imie = (SELECT imie from @tabelka where ajdi = @i )
- set @nazwisko = (SELECT nazwisko from @tabelka where ajdi = @i )
- set @wiek = (SELECT wiek from @tabelka where ajdi = @i )
- set @strout = 'Osoba ' + @imie + ' ' + @nazwisko + ' ' + cast(@wiek as varchar) + ' lat'
- print @strout
- END
- set @i = @i + 1
- END
- END
- procka 20, 40
- -- Zadanie 1
- --Dodaj kolumnę tekstową login1 do tabeli pracownicy.
- --Przy tworzeniu nowego pracownika lub modyfikacji istniejącego
- --w tabeli pracownicy ustawiaj wartość kolumny login1
- --na pierwszą i ostatnią literę z imienia i pierwszą i ostatnią literą nazwiska. W tak stworzonej
- --wartości zastosuj wielkie litery.
- --Przykład dla osoby: Tomasz Pawlak login1 będzie skłądał się z TZPK
- alter table pracownicy add login1 varchar(200)
- select * from pracownicy
- create trigger zad1 on pracownicy
- for insert, update
- as
- begin
- declare @imie varchar(50)=(select imie from inserted)
- declare @nazwisko varchar(50)=(select nazwisko from inserted)
- declare @wynik varchar(50)=''
- set @wynik=LEFT(@imie,1)+UPPER((RIGHT(@imie,1)))+LEFT(@nazwisko,1)+UPPER((RIGHT(@nazwisko,1)))
- update pracownicy set login1=@wynik where imie=(select imie from inserted) and nazwisko=(select nazwisko from inserted)
- end
- insert into pracownicy(imie,nazwisko) values('Tomasz','Pawlak')
- --Zadanie 2
- --Do tabeli działy dopisz kolumnę data_przyjęcia.
- --Przy tworzeniu nowego pracownika z ustawionym działem
- --ustawiaj wartość kolumny data_przyjęcia dla zadanego działu
- --wpisując w nią komunikat z obecną datą oraz imieniem i nazwiskiem pracownika.
- --Format komunikatu:
- --Ostatni zatrudniony pracownik Jan Kowalski: Dec 18 2006 9:01PM'
- alter table dzialy add data_przyjęcia date
- alter trigger zad3 on dzialy
- for insert, update
- as
- begin
- declare @imie varchar(50)=(select imie from pracownicy)
- declare @data date
- declare @nazwisko varchar(50)=(select nazwisko from pracownicy)
- declare @dzial int=(select dzialID from pracownicy)
- declare @wynik varchar(100)=''
- set @data=getDate()
- set @wynik='Ostatni zatrudniony pracownik '+@imie+@nazwisko+': '+cast(@data as varchar(100))
- end
- insert into pracownicy(imie,nazwisko) values('Jasio','Kotek')
- select * from pracownicy
- select * from dzialy
- --zadanie 3
- --Utwórz procedurę , która wyświetli w formie tekstowej (w oknie messages)
- --wszystkich pracowników i liczbę ich wypłat. Pod uwagę bierz tylko tych pracowników,
- --którzy posiadają więcej niż X (gdzie X to pierwszy parametr procedury)
- --wpisów w tabeli zarobki. Dodatkowo wybierz tylko te wpisy, które zostały stworzone po
- --dacie podanej w drugim parametrze. W przypadku nie podania w wywołaniu procedury
- --drugiego parametru nie uwzględniaj kryterium daty.
- --Przykładowy wynik wywołania:
- --'Pracownik Jan Kowalski od dnia Jan 1 2001 12:00AM otrzymał 5 wypłat'
- --lub
- --'Pracownik Jan Kowalski otrzymał 5 wypłat'
- --brak
- --zadanie 4
- --Stwórz procedurę , która wyświetli w formie tekstowej (w oknie messages)
- --co trzeciego pracownika licząc od konca, którego wiek jest pomiędzy 2 parametrami zadanymi w definicji procedury.
- --W przypadku nie podania parametru pierwszego domyślnie ustaw 18 a w przypadku nie podania drugiego
- --ustaw 30.
- --Przykłąd wyniku:
- --'Osoba Tomasz Kozak 20 lat'
- --'Osoba Piotr Wozniak 19 lat'
- --'Osoba Anna Wozniak 36 lat'
- create procedure zadanie4
- @a int, @b int
- as
- begin
- if @a is NULL
- begin
- set @a=18
- end
- if @b is NULL
- begin
- set @b=30
- end
- end
- exec zadanie4 3
- --Zadanie 3
- --Utwórz procedurę , która wyświetli w formie tekstowej (w oknie messages)
- --wszystkich pracowników i liczbę ich wypłat. Pod uwagę bierz tylko tych pracowników,
- --którzy posiadają więcej niż X (gdzie X to pierwszy parametr procedury)
- --wpisów w tabeli zarobki. Dodatkowo wybierz tylko te wpisy, które zostały stworzone po
- --dacie podanej w drugim parametrze. W przypadku nie podania w wywołaniu procedury
- --drugiego parametru nie uwzględniaj kryterium daty.
- --Przykładowy wynik wywołania:
- --'Pracownik Jan Kowalski od dnia Jan 1 2001 12:00AM otrzymał 5 wypłat'
- alter procedure zadanie3 @X int, @data datetime
- as
- declare @opis varchar(100)
- declare @licznik int
- declare @ileWyplat int
- declare @ilePracownikow int
- declare @pierwszaWyplata datetime
- set @licznik = 1
- set @ilePracownikow = (select count(iloscWyplat) as 'ilePracownikow' from
- (select count(pracid) as 'iloscWyplat' from zarobki group by pracid) as t1)
- begin
- while @licznik <= @ilePracownikow
- begin
- if @data = ''
- begin
- set @ileWyplat = (select count(pracID) from zarobki where pracID=@licznik)
- if @ileWyplat > @X
- begin
- set @pierwszaWyplata = (select min(od) from zarobki where pracID = @licznik)
- set @opis = (select 'Pracownik '+imie+' '+nazwisko+' od dnia '+cast((@pierwszaWyplata) as varchar(15))+
- ' otrzymal '+cast((@ileWyplat) as varchar(5))+' wyplat' from pracownicy
- where pracID=@licznik)
- print @opis
- end
- end
- else
- begin
- set @ileWyplat = (select count(pracID) from zarobki where pracID = @licznik and od > @data)
- if @ileWyplat > @X
- begin
- set @opis = (select 'Pracownik '+imie+' '+nazwisko+' od dnia '+cast((@data) as varchar(15))+
- ' otrzymal '+cast((@ileWyplat) as varchar(5))+' wyplat' from pracownicy
- where pracID=@licznik)
- print @opis
- end
- end
- set @licznik = @licznik + 1
- end
- end
- exec zadanie3 2, ''
- exec zadanie3 1, '01/01/2006'
- --Zadanie 1
- --Dodaj kolumnę tekstową login1 do tabeli pracownicy.
- --Przy tworzeniu nowego pracownika lub modyfikacji istniejącego
- --w tabeli pracownicy ustawiaj wartość kolumny login1
- --na pierwszą i ostatnią literę z imienia i pierwszą i ostatnią literą nazwiska. W tak stworzonej
- --wartości zastosuj wielkie litery.
- --Przykład dla osoby: Tomasz Pawlak login1 będzie skłądał się z TZPK
- --Nie uwzględniaj liczb
- alter table pracownicy add login1 varchar(100)
- create trigger zadanie1 on pracownicy
- for insert, update
- as
- begin
- declare @login varchar(10)
- declare @imie varchar(20)
- declare @nazwisko varchar(20)
- set @imie = (select imie from inserted)
- set @nazwisko = (select nazwisko from inserted)
- set @login = left(@imie,1)+right(@imie,1)+left(@nazwisko,1)+right(@nazwisko,1)
- set @login = upper(@login)
- update pracownicy set login1 = @login
- where @login NOT LIKE '%[^a-z]%'and pracID=(select pracID from inserted)
- end
- insert into pracownicy(imie, nazwisko) values('Jerzy', 'Killer') -- przypadek pierwszy
- insert into pracownicy(imie, nazwisko) values('Jerzy', 'Killer234') -- przypadek drugi
- select * from pracownicy
- drop trigger zadanie1
- --Zadanie 2
- --Do tabeli działy dopisz kolumnę data_przyjęcia.
- --Przy tworzeniu nowego pracownika z ustawionym działem
- --ustawiaj wartość kolumny data_przyjęcia dla zadanego działu
- --wpisując w nią komunikat z obecną datą oraz imieniem i nazwiskiem pracownika.
- --Format komunikatu:
- --Ostatni zatrudniony pracownik Jan Kowalski: Dec 18 2006 9:01PM'
- alter table dzialy add data_przyjecia varchar(100)
- select * from dzialy
- create trigger zadanie2 on pracownicy
- for insert, update
- as
- begin
- declare @imie varchar(20)
- declare @nazwisko varchar(20)
- declare @komunikat varchar(100)
- set @imie = (select imie from inserted)
- set @nazwisko = (select nazwisko from inserted)
- set @komunikat = 'Ostatni zatrudniony pracownik: '+@imie +' '+@nazwisko+' '+cast((getdate()) as varchar(20))
- update dzialy set data_przyjecia = @komunikat
- where dzialID = (select dzialID from inserted)
- end
- insert into pracownicy(imie, nazwisko, dzialID) values('Jerzy', 'Killer', 1)
- drop trigger zadanie2
- select * from pracownicy
- --Zadanie 4
- --Stwórz procedurę , która wyświetli w formie tekstowej (w oknie messages)
- --co trzeciego pracownika licząc od konca, którego wiek jest pomiędzy 2 parametrami zadanymi w definicji procedury.
- --W przypadku nie podania parametru pierwszego domyślnie ustaw 18 a w przypadku nie podania drugiego
- --ustaw 30.
- create procedure zadanie4 @param1 int, @param2 int
- as
- if @param1 = ''
- set @param1 = 18
- if @param2 = ''
- set @param2 = 30
- declare @wiersze int
- declare @opis varchar(50)
- begin
- declare @tabela table(ID int identity(1,1), imie varchar(20), nazwisko varchar(20))
- insert into @tabela
- select imie, nazwisko from pracownicy where wiek between @param1 and @param2
- set @wiersze = (select count(ID) from @tabela)
- while @wiersze >= 3
- begin
- set @opis = (select imie+' '+nazwisko from @tabela where ID = @wiersze)
- print @opis
- set @wiersze = @wiersze - 3
- end
- end
- exec zadanie4 '', ''
- --lub exec zadanie4 15 30
- select * from pracownicy
- /*
- zadanie 1
- Do tabeli pracownicy dodaj kolumne tekstowa poczta o dlugosci 200 znakow.
- Podczas dodawania i modyfikacji osoby ustaw tą kolumnę ciągiem złożonym z:
- pierwszych 5 liter nazwiska, myślnika,
- pierwszych trzech liter imienia oraz ciągu '@wp.pl'
- Pamiętaj, aby usunąc ewentualne zewnętrzne białe znaki oraz
- aby zamienic wewnętrzne spacje na znak '_'.
- */
- ALTER TABLE pracownicy ADD poczta varchar (200)
- CREATE TRIGGER zadanie1
- ON Pracownicy
- AFTER INSERT, UPDATE
- AS
- BEGIN
- DECLARE @nazwisko varchar(100), @mail varchar (100), @imie varchar(100), @id BIGINT
- SET @nazwisko = (select Nazwisko from inserted);
- SET @imie = (select Imię from inserted);
- set @imie = ltrim(rtrim(@imie));
- set @imie = replace(@imie, ' ', '_');
- set @nazwisko = replace(@nazwisko, ' ', '_');
- SET @id = select ( IDPracownika from inserted);
- SET @mail = Substring(@nazwisko,1,5) + '-' + Substring(@imie,1,3) + '@wp.pl'
- UPDATE [pracownicy] SET poczta = @mail WHERE IDpracownika = @id
- END
- INSERT INTO Pracownicy (IDpracownika,Imię, Nazwisko) values(91,'Tom ', 'Ziom');
- INSERT INTO Pracownicy (IDpracownika,Imię, Nazwisko) values(92,'Tom ', 'Zomo');
- INSERT INTO Pracownicy (IDpracownika,Imię, Nazwisko) values(93,'Tom ', 'Ziemo');
- INSERT INTO Pracownicy (IDpracownika,Imię, Nazwisko) values(94,'Tom ', 'Złom');
- UPDATE [Pracownicy] SET Imię ='Andrzej', Nazwisko = 'Nowakowski'
- WHERE IDpracownika = 9;
- DELETE FROM Pracownicy WHERE Nazwisko like 'Kowalski '
- select * from Pracownicy order by Nazwisko
- /*
- zadanie 2
- Stwórz procedurę, która wyświetli w formie tekstowej (w oknie messages) co drugiego pracownika
- pracownika, którego nazwisko zaczyna się na literę podaną w parametrze. W przypadku nie podania
- parametru uwzględniaj tylko osby o nazwisku na literę 'Z'. W przypadku podania w parametrze wartosci
- null nie uwzględniaj kryterium.
- */
- ALTER PROCEDURE proc2
- @parametr varchar (2) = 'Z'
- AS
- BEGIN
- DECLARE @id int;
- DECLARE @i int;
- DECLARE @pracownik varchar (100);
- DECLARE @tabela table(ID int identity(1,1), imie varchar(10), nazwisko varchar(10))
- SET @id = 1;
- IF (@parametr IS NOT NULL)
- BEGIN
- INSERT INTO @tabela (imie, nazwisko)
- (SELECT Imię, Nazwisko from pracownicy where SUBSTRING(nazwisko,1,1) = @parametr)
- SET @i = 1;
- WHILE (@i <= (Select max(id) from @tabela) )
- BEGIN
- SET @pracownik = (SELECT imie from @tabela where ID = @i) + (SELECT nazwisko from @tabela where ID = @i);
- print @pracownik;
- SET @i = @i + 2;
- END
- END
- ELSE
- BEGIN
- INSERT INTO @tabela (imie, nazwisko)
- (SELECT Imię, Nazwisko from pracownicy)
- SET @i = 1;
- WHILE (@i <= (Select max(id) from @tabela) )
- BEGIN
- SET @pracownik = (SELECT imie from @tabela where ID = @i) + (SELECT nazwisko from @tabela where ID = @i);
- print @pracownik;
- SET @i = @i + 1;
- END
- END
- END
- exec proc2 null
- SELECT * from Pracownicy
- DELETE ROW FROM Pracownicy WHERE Imię = 'Aleksander'
- /*
- zadanie 3
- Utwórz funkcję, która będzie zwracać komunikat o różnicy w dniach
- między datą bieżącą a datą podaną w parametrze.
- W przypadku tego samego dnia zwróć komunikat o równości dat.
- Przykład:
- 'Między datą obecną a datą Dec 12 2012 12:00AM jest XXX dni różnicy.'
- lub
- 'Daty data podana w parametrze jest datą dzisiejszą.'
- */
- ALTER FUNCTION zad3 (@data DATETIME) RETURNS varchar(100)
- AS
- BEGIN
- DECLARE @data2 DATETIME
- DECLARE @wynik varchar(100)
- SET @data2 = GETDATE()
- IF (@data IS NULL)
- SET @wynik = 'Nieprawidłowa data'
- ELSE IF ( DATEDIFF (day,@data,@data2) <> 0 )
- SET @wynik = 'Między obecną datą a datą ' + cast(@data as varchar) + ' jest ' + cast (DATEDIFF(day,@data2,@data) as varchar) + ' dni różnicy'
- ELSE
- SET @wynik = 'Data podana w parametrze jest datą dzisiejszą.'
- return @wynik
- END
- select dbo.zad3 ('06/25/2017') from pracownicy
- print cast('06/25/2017' as datetime)
- SELECT DATEDIFF(day, '2004-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
- /*
- zadanie 4
- Stworzyć wyzwalacz (trigger) na tabeli – zamowienia – który uruchomiony jest po wstawieniu rekordu do tej tabeli.
- Wyzwalacz ma wstawić do tabeli – dane – dane klienta z tabeli – klienci – oraz zaktualizować kolumny w tabeli – zamowienia:
- - numer zamówienia – zwiększyć o 1 ostatnią wartość;
- - data realizacji zamówienia – dodać 7 dni do daty dodania zamówienia.
- */
- ALTER TRIGGER zad4
- ON zamowienia
- AFTER INSERT
- AS
- DECLARE @id INT, @klient INT;
- DECLARE @data DATETIME;
- SELECT @id = [ID_zamowienia], @klient = [Klient] FROM [inserted];
- SELECT @data = [data_zam] FROM [zamowienia] WHERE [ID_zamowienia] = @id;
- BEGIN
- IF(@id IS NOT NULL) AND (@klient IS NOT NULL)
- BEGIN
- INSERT INTO [dane](ID_zamowienia, imie, nazwisko, adres, miasto, kod_pocztowy)
- (SELECT @id, imie, nazwisko, adres, miasto, kod_pocztowy FROM klienci WHERE [ID_klienta] = @klient);
- UPDATE [zamowienia] SET ID_zamowienia = (@id + 1) , [data_dos] =
- DATEADD(DAY, 7, @data) WHERE ID_zamowienia = @id;
- END
- END;
- Zadanie 1
- stwórz triggera który przenosi dane dotyczące zarobków
- do tabeli historycznej będącej dokładną kopią tabeli
- zarobki bez kolumny aktualny. Uwzględnij w tej tabeli datę
- przenosin oraz użytkownika który kasował dane
- Zadanie 2
- Stworzyć wyzwalacz (trigger) na tabeli – zamowienia – który uruchomiony jest po wstawieniu rekordu do tej tabeli.
- Wyzwalacz ma wstawić do tabeli – dane – dane klienta z tabeli – klienci – oraz zaktualizować kolumny w tabeli – zamowienia:
- - numer zamówienia – zwiększyć o 1 ostatnią wartość;
- - data realizacji zamówienia – dodać 7 dni do daty dodania zamówienia.
- zadanie 3
- Do tabeli pracownicy dodaj kolumne tekstowa poczta o dlugosci 200 znakow.
- Podczas dodawania i modyfikacji osoby ustaw tą kolumnę ciągiem złożonym z:
- pierwszych 5 liter nazwiska, myślnika,
- pierwszych trzech liter imienia oraz ciągu '@p.lodz.pl'
- Pamiętaj, aby usunąc ewentualne zewnętrzne białe znaki oraz
- aby zamienic wewnętrzne spacje na znak '_'.
- zadanie 4
- Stwórz procedurę, która wyświetli w formie tekstowej (w oknie messages) co drugiego pracownika
- pracownika, którego nazwisko zaczyna się na literę podaną w parametrze. W przypadku nie podania
- parametru uwzględniaj tylko osby o nazwisku na literę 'Z'. W przypadku podania w parametrze wartosci
- null nie uwzględniaj kryterium.
- Przykłąd wyniku:
- 'Pracownik nr #1 Jan Kowalski 25 lat'
- 'Pracownik nr #3 Tomasz Kowalski 26 lat'
- 'Pracownik nr #5 Piotr Kowalski 29 lat
- zadanie z pocztą
- ALTER TABLE pracownicy ADD poczta varchar (200)
- CREATE TRIGGER zadanie1
- ON Pracownicy
- AFTER INSERT, UPDATE
- AS
- BEGIN
- DECLARE @nazwisko varchar(100), @mail varchar (100), @imie varchar(100), @id BIGINT
- SET @nazwisko = (select Nazwisko from inserted);
- SET @imie = (select Imię from inserted);
- set @imie = ltrim(rtrim(@imie));
- set @imie = replace(@imie, ' ', '_');
- set @nazwisko = replace(@nazwisko, ' ', '_');
- SET @id = select ( IDPracownika from inserted);
- SET @mail = Substring(@nazwisko,1,5) + '-' + Substring(@imie,1,3) + '@p.lodz.pl'
- UPDATE [pracownicy] SET poczta = @mail WHERE IDpracownika = @id
- END
- zad 4
- create procedure pr
- @lit varchar(1) ='Z'
- as
- begin
- declare @pracownik varchar(120)
- declare @i int, @rtoprocess int
- declare @tabl table(id int IDENTITY ,nazw varchar(50), im varchar(50), wiek int )
- if @lit is not null
- begin
- insert into @tabl
- select nazwisko, imie, wiek from pracownicy where nazwisko LIKE @lit+'%'
- select * from @tabl
- end
- else
- insert into @tabl
- select nazwisko, imie, wiek from pracownicy
- set @i=1
- select @rtoprocess = count(nazw) from @tabl
- while @i < @rtoprocess
- begin
- if @i % 2 =0
- begin
- select @pracownik = nazw from @tabl where id = @i
- set @pracownik = @pracownik + ' '
- select @pracownik = @pracownik + im from @tabl where id = @i
- set @pracownik = @pracownik + ' '
- if (select wiek from @tabl where id = @i) is null
- set @pracownik = @pracownik + ' niewiadomo ile lat'
- else
- begin
- select @pracownik = @pracownik + convert(varchar(4),wiek) from @tabl where id = @i
- set @pracownik = @pracownik + ' lat'
- end
- print('Pracownik nr' +' '+ convert(varchar(4),@i) +' '+@pracownik )
- end
- set @i = @i +1
- end
- end
- zad 2
- CREATE TRIGGER zad2
- ON zamowienia
- for INSERT
- AS
- DECLARE @id INT, @klient INT;
- DECLARE @data DATETIME;
- SELECT @id = [ID_zamowienia], @klient = [Klient] FROM [inserted];
- SELECT @data = [data_zam] FROM [zamowienia] WHERE [ID_zamowienia] = @id;
- BEGIN
- IF(@id IS NOT NULL) AND (@klient IS NOT NULL)
- BEGIN
- INSERT INTO [dane](ID_zamowienia, imie, nazwisko, adres, miasto, kod_pocztowy)
- (SELECT @id, imie, nazwisko, adres, miasto, kod_pocztowy FROM klienci WHERE [ID_klienta] = @klient);
- UPDATE [zamowienia] SET ID_zamowienia = (@id + 1) , [data_dos] =
- DATEADD(DAY, 7, @data) WHERE ID_zamowienia = @id;
- END
- END
- zadanie 1.
- create table zarobki_historia(
- zarID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
- od datetime,
- brutto money,
- pracID int FOREIGN KEY REFERENCES pracownicy(pracID),
- data_przenosin date,
- uzytkownik varchar(20)
- )
- ZADANIE 5
- drop trigger zadanie5
- create trigger zadanie5 on zarobki
- for delete
- as
- begin
- insert into zarobki_historia(zarID, od, brutto, pracID) select * from deleted
- update zarobki_historia set data_przenosin = getdate() where zarID = (select zarID from deleted)
- update zarobki_historia set uzytkownik = user_name() where zarID = (select zarID from deleted)
- end
- SET IDENTITY_INSERT zarobki_historia ON
- delete from zarobki where zarID=3;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement