lab5/lab5.sql

USE w65567_FIRMA;

-- 0. przygotuj komunikat 'Jest X sprzedawców'
DECLARE @b int
SELECT @b=count(*) FROM Pracownicy WHERE STANOWISKO='Sprzedawca'
PRINT 'Jest '+cast(@b AS varchar)+' sprzedawcow'

-- 1.	Zarobki poniżej 3 tys należy zwiększyć o 20%, a zarobki powyżej 3 tys
--      należy obniżyć o 10%
UPDATE Pracownicy
SET PENSJA=(
	CASE 
		WHEN PENSJA < 3000 THEN PENSJA*1.2
		WHEN PENSJA > 3000 THEN PENSJA*0.9
		ELSE PENSJA
	END
)

SELECT * FROM Pracownicy


-- 2.	Stwórz procedurę która wypisze najwcześniej zatrudnione osoby, w każdym
--      departamencie. Identyfikator departamentu powinien być przekazanym jako
--      argument procedur. Natomiast data zatrudnienia przekazywana jest przez
--      drugi argument, który jest zadeklarowany jako wyjściowy.
CREATE PROC zad2 (
	@d int,
	@data date OUTPUT
) AS
SELECT @data=min(data_zatrud) FROM Pracownicy
WHERE KOD_dzialu=@d


DECLARE @data DATE
EXEC zad2 10, @data OUTPUT
PRINT @data

CREATE PROC ilosc
AS
DECLARE @i int
SELECT @i=count(identyfikator) FROM Pracownicy
RETURN @i

DECLARE @z int
EXEC @z=ilosc
PRINT @z




USE w65567_FIRMA;

-- 0. przygotuj komunikat 'Jest X sprzedawców'
DECLARE @b int
SELECT @b=count(*) FROM Pracownicy WHERE STANOWISKO='Sprzedawca'
PRINT 'Jest '+cast(@b AS varchar)+' sprzedawcow'

-- 1.	Zarobki poniżej 3 tys należy zwiększyć o 20%, a zarobki powyżej 3 tys
--      należy obniżyć o 10%
UPDATE Pracownicy
SET PENSJA=(
	CASE 
		WHEN PENSJA < 3000 THEN PENSJA*1.2
		WHEN PENSJA > 3000 THEN PENSJA*0.9
		ELSE PENSJA
	END
)

SELECT * FROM Pracownicy


-- 2.	Stwórz procedurę która wypisze najwcześniej zatrudnione osoby, w każdym
--      departamencie. Identyfikator departamentu powinien być przekazanym jako
--      argument procedur. Natomiast data zatrudnienia przekazywana jest przez
--      drugi argument, który jest zadeklarowany jako wyjściowy.
CREATE PROC zad2 (
	@d int,
	@data date OUTPUT
) AS
SELECT @data=min(data_zatrud) FROM Pracownicy
WHERE KOD_dzialu=@d


DECLARE @data DATE
EXEC zad2 10, @data OUTPUT
PRINT @data

CREATE PROC ilosc
AS
DECLARE @i int
SELECT @i=count(identyfikator) FROM Pracownicy
RETURN @i

DECLARE @z int
EXEC @z=ilosc
PRINT @z


-- triggery
CREATE TRIGGER tr1
ON PRACOWNICY
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
	SELECT * FROM inserted
	SELECT * FROM deleted
END

INSERT INTO PRACOWNICY (nazwisko, imie) VALUES ('Zdzichowski', 'Zdzichu')
DELETE FROM PRACOWNICY WHERE IDENTYFIKATOR=16

UPDATE PRACOWNICY SET PENSJA = 3000 WHERE IDENTYFIKATOR=17

DROP TRIGGER tr1

-- 4.	Zdefiniuj wyzwalacz, który zabroni zmniejszania pensji pracownikom
CREATE TRIGGER tr2
ON PRACOWNICY
AFTER UPDATE
AS
BEGIN
	IF UPDATE(PENSJA)
	BEGIN
		DECLARE @nowa DECIMAL(10,2 ), @stara DECIMAL(10, 2)
		SELECT @nowa = PENSJA FROM inserted
		SELECT @stara = PENSJA FROM deleted
		IF @nowa < @stara
			ROLLBACK
	END
END

SELECT * FROM PRACOWNICY
UPDATE PRACOWNICY SET PENSJA=4000 WHERE IDENTYFIKATOR=17

CREATE TRIGGER tr3
ON PRACOWNICY
AFTER UPDATE
AS
BEGIN
	IF UPDATE(PENSJA)
	BEGIN
		UPDATE PRACOWNICY SET PENSJA=deleted.PENSJA
		FROM PRACOWNICY INNER JOIN deleted
		ON PRACOWNICY.IDENTYFIKATOR = deleted.IDENTYFIKATOR
		WHERE PRACOWNICY.PENSJA < deleted.PENSJA
	END
END


-- 5.	Dla relacji Pracownicy stwórz wyzwalacz, który w przypadku braku prowizji
--      zamieni wartość NULL na 10.
CREATE TRIGGER tr5
ON PRACOWNICY 
AFTER INSERT, UPDATE
AS
BEGIN
	IF UPDATE(PROWIZJA)
	BEGIN
		DECLARE @test DECIMAL(10, 2) 
		SELECT @test = PROWIZJA FROM inserted
		IF @test IS NULL
		BEGIN
			UPDATE PRACOWNICY SET PROWIZJA=10
			FROM PRACOWNICY
			INNER JOIN inserted
			ON Pracownicy.IDENTYFIKATOR = inserted.IDENTYFIKATOR
		END
	END
END

INSERT INTO PRACOWNICY (nazwisko, imie) VALUES ('Zdzichowski', 'Zdzichu')
UPDATE PRACOWNICY SET PROWIZJA=null WHERE IDENTYFIKATOR=13


-- 6.	Zapisz w tabeli LOG informacje o tym kto skasował którego klienta.
CREATE TABLE REJESTR (
	kto VARCHAR(30),
	kogo VARCHAR(30),
	kiedy DATE
)

CREATE TRIGGER TR_REJESTR
ON PRACOWNICY
AFTER DELETE 
AS
BEGIN
	INSERT INTO REJESTR VALUES (suser_name(), (SELECT nazwisko FROM deleted), getdate())
END

SELECT * FROM REJESTR
SELECT * FROM PRACOWNICY

DELETE FROM PRACOWNICY WHERE IDENTYFIKATOR=17

-- 7.	Korzystając z tabeli produkty stwórz transakcję, która przeniesie wszystkie
--      produkty z magazynu nr 1 do magazynu nr 2. Maksymalna pojemność magazynu
--      nr 2 wynosi 500. Jeżeli powyższa operacja spowoduje przepełnienie magazynu
--      należy ją wycofać i przenieść produkty do magazynu nr 3.
CREATE TABLE PRODUKTY (
	ID_PRODUKT INT IDENTITY(1,1),
	ILOSC DECIMAL(8, 2),
	NR_MAGAZYNU INT
)

--                           ILOSC NR_MAGAZYNU
INSERT INTO PRODUKTY VALUES (250,  1)
INSERT INTO PRODUKTY VALUES (250,  2)
INSERT INTO PRODUKTY VALUES (250,  3)

SELECT * FROM PRODUKTY

BEGIN TRANSACTION zmiana
	SAVE TRANSACTION p1
	UPDATE PRODUKTY SET NR_MAGAZYNU=2 WHERE NR_MAGAZYNU=1
	IF (SELECT sum(ILOSC) FROM PRODUKTY WHERE NR_MAGAZYNU=2) > 500
		ROLLBACK TRANSACTION p1
	UPDATE PRODUKTY SET NR_MAGAZYNU=3 WHERE NR_MAGAZYNU=1
COMMIT TRANSACTION