Bazy

//1
SET DATEFORMAT ymd;
GO

--1A.1
--Etap 1
CREATE TABLE customer(
	id_klient		INT				IDENTITY(1,1) PRIMARY KEY,
	imie			VARCHAR(20)		NOT NULL CHECK(LEN(imie)>0),
	nazwisko		VARCHAR(30)		NOT NULL CHECK(LEN(nazwisko)>0),
	data_urodzenia	DATETIME		NOT NULL CHECK(data_urodzenia>'1945-01-01'),
	pesel			VARCHAR(11)		NOT NULL CHECK(LEN(PESEL)=11),
);
GO

INSERT INTO customer(imie,nazwisko,data_urodzenia,pesel) VALUES
('Jan','Kowalski','1970-05-12','70051212345'),
('Paweł','Nowak','1975-06-13','75061312345'),
('Mariusz','Kolokwium','1980-07-14','80071412345'),
('Mateusz','Baza','1985-08-15','85081512345'),
('Tomasz','Danych','1990-09-16','90091612345'),
('Ireneusz','Uniwerek','1990-01-10','90010112345');
GO

DROP TABLE customer;
SELECT * FROM customer;

--Etap 2
DROP FUNCTION ilu_w_danym_roku;

CREATE FUNCTION ilu_w_danym_roku(
@p_rok_urodzenia INT
) RETURNS INT
BEGIN
RETURN (SELECT COUNT(*) FROM customer WHERE DATEPART(YEAR,data_urodzenia)  = @p_rok_urodzenia)
END;
GO

--Etap 3
SELECT dbo.ilu_w_danym_roku(1990) AS ilosc

--1A.2
--Etap 1
DROP PROC add_customer

CREATE PROC add_customer
	@imie			VARCHAR(20),
	@nazwisko		VARCHAR(30),
	@data_urodzenia	DATETIME,
	@pesel			VARCHAR(11)
AS
	INSERT INTO customer(imie,nazwisko,data_urodzenia,pesel)
	VALUES (@imie,@nazwisko,@data_urodzenia,@pesel)
GO

--Etap2
EXEC add_customer 'Mateusz','Soroka','1996-12-01','96120112345'

SELECT * FROM customer;

--1A.3
--Etap 1
DROP TRIGGER one_pesel

CREATE TRIGGER one_pesel ON customer
AFTER INSERT AS
DECLARE @pesel VARCHAR(11)
SELECT @pesel=pesel FROM inserted
IF (SELECT COUNT(*) FROM customer WHERE @pesel=pesel)>1
BEGIN
	RAISERROR('Nie wolno dodać klientów o tym samym numerze PESEL',1,2)
	ROLLBACK
END
GO

--Etap 2
INSERT INTO customer(imie,nazwisko,data_urodzenia,pesel) VALUES
('Jan','TEST','1970-05-12','70051212345');
GO

SELECT * FROM customer WHERE pesel='70051212345';



//2
SET DATEFORMAT ymd;
GO

--1B.1
--Etap 1
DROP TABLE filia;

CREATE TABLE filia(
id_filia		INT				IDENTITY(1,1) PRIMARY KEY,
nazwa			VARCHAR(30)		CHECK(LEN(nazwa)>0),
miasto			VARCHAR(40)		CHECK(LEN(miasto)>0),
data_zalozenia	DATETIME		CHECK(data_zalozenia>='2010-01-01')
);
GO

INSERT INTO filia(nazwa,miasto,data_zalozenia) VALUES
('Intel','Gdańsk','2011-01-01'),
('AMD','Warszawa','2012-02-02'),
('Nvidia','Poznań','2013-03-03'),
('Asus','Szczecin','2013-04-04'),
('ASRock','Kraków','2015-05-05'),
('Dell','Łódź','2016-06-06');
GO

--Etap 2
DROP FUNCTION ile_w_roku;

CREATE FUNCTION ile_w_roku(
@p_rok_zalozenia INT
) RETURNS INT
BEGIN
RETURN (SELECT COUNT(*) FROM filia WHERE DATEPART(YEAR,data_zalozenia)  = @p_rok_zalozenia)
END;
GO

--Etap 3
SELECT dbo.ile_w_roku(2013) AS ilosc

--1B.2
--Etap 1
DROP PROC dodaj_filie

CREATE PROC dodaj_filie
	@p_nazwa			VARCHAR(30),
	@p_miasto			VARCHAR(40),
	@p_data_zalozenia	DATETIME
AS
INSERT INTO filia(nazwa,miasto,data_zalozenia)
VALUES (@p_nazwa,@p_miasto,@p_data_zalozenia)
GO

--Etap 2
EXEC dodaj_filie 'UG','Gdańsk','2017-01-01'

SELECT * FROM filia WHERE nazwa='UG'

--1B.3
--Etap 1
DROP TRIGGER same_name

CREATE TRIGGER same_name ON filia
AFTER INSERT AS
DECLARE @nazwa VARCHAR(30)
SELECT @nazwa=nazwa FROM inserted
IF (SELECT COUNT(*) FROM filia WHERE @nazwa=nazwa)>1
BEGIN
	RAISERROR('Nie może być filii o tych samych nazwach',1,2)
	ROLLBACK
END
GO

--Etap 2
INSERT INTO filia(nazwa,miasto,data_zalozenia) VALUES
('UG','TEST','2011-01-01');
GO

SELECT * FROM filia WHERE nazwa='UG';



//3
--Gr. AN-8:30a
--Data: 18-05-2017
--Imie: Karol
--Nazwisko: Tomczyk
--Semestr: II

DROP TABLE telefon
DROP PROCEDURE zmiana_rabatu
DROP FUNCTION dbo.zmiana_ceny

--1. Dana jest tabela telefon(id_tel,marka,cena,rabat).
--Napisa� funkcj� posiadaj�c� jeden parametr p_cena i zwracaj�c� ilo�� telefon�w
--o cenie ni�szej ni� warto�� parametru.
--(warto�� kolumny id_tel ma by� nadawana automatycznie)
--Etap 1: Utw�rz tabel� telefon i dodaj 7 przyk�adowych rekord�w.

CREATE TABLE telefon(
id_tel INT IDENTITY(1,1) PRIMARY KEY,
marka VARCHAR(30),
cena FLOAT,
rabat FLOAT
);
GO

INSERT INTO telefon(marka,cena,rabat) VALUES ('tel1',700,0.01),('tel2',800,0.02),('tel3', 900, 0.03),('tel4', 1000, 0.04),
											('tel5', 1100, 0.05),('tel6', 1200, 0.06),('tel7', 1300, 0.07)
GO
--Etap 2: Utw�rz funkcj�.

CREATE FUNCTION dbo.zmiana_ceny (@p_cena INT) RETURNS TABLE AS
RETURN (SELECT * FROM telefon WHERE cena<@p_cena)
GO
--Etap 3: Udowodnij, �e utworzona funkcja dzia�a.

SELECT * FROM dbo.zmiana_ceny(1000)
GO

--2. Napisa� procedur� posiadajc� dwa parametry b�d�ce cenami:
--p_cena_od i p_cena_do.
--Procedura ma modyfikowa� tabel� telefon ustawiaj�c warto�� rabatu na 4%
--dla telefon�w z zakresu cen od p_cena_od do p_cena_do.
--Etap 1: Utw�rz procedur�.

CREATE PROCEDURE zmiana_rabatu @p_cena_od INT, @p_cena_do INT AS
BEGIN
UPDATE telefon SET rabat=0.04 WHERE cena>=@p_cena_od AND cena<=@p_cena_do
END
GO

--Etap 2: Udowodnij, �e utworzona procedura dzia�a.

EXEC zmiana_rabatu 500, 1000
SELECT * FROM telefon WHERE cena>=500 and cena<=1000
GO

--3 Napisa� wyzwalacz podwy�szaj�cy cen� dodawanego telefonu o 23%.

--Etap 1: Utw�rz wyzwalacz.

CREATE TRIGGER zwiekszenie_ceny ON telefon
AFTER INSERT AS
BEGIN
UPDATE telefon SET cena=(SELECT cena FROM inserted) * 1.23
WHERE id_tel=(SELECT id_tel FROM inserted)
END
GO

--Etap 2: Udowodnij, �e utworzony wyzwalacz dzia�a.

INSERT INTO telefon(marka,cena,rabat) VALUES ('tel8', 1800, 0.4)
SELECT * FROM telefon WHERE marka='tel8'