zx2BWNPs

1"GRANT INSERT, UPDATE ON studenti TO student13"
2"GRANT SELECT, INSERT ON ispit TO djelatnik22" 
3"GRANT UPDATE (ocjena) ON ispit TO djelatnik21"
4"REVOKE INSERT, DELETE ON ispit TO student13"
5"REVOKE ALL TO student13"
6
7BEGIN TRANSACTION
8	ALTER TABLE ispit ADD bodovv INT
9	ALTER TABLE ispit DROP COLUMN objasnjenje
10	INSERT INTO ispit (sifra_pr, sifra_st, bodovi, ocjena) VALUES (12,'a352',76,4)
11	IF (SELECT bodovi FROM ispit WHERE sifra_pr = 12) IS NULL
12		ROLLBACK TRANSACTION
13COMMIT TRANSACTION
14
15CREATE FUNCTION funkcija(@n INT)
16RETURNS INT
17BEGIN
18	DECLARE @rez INT = 1
19	IF @n < 50 
20		BEGIN 
21		WHILE @n>0
22			BEGIN
23			SET @rez = @rez*@n
24			SET @n = @n - 1
25			END
26		END
27	IF @n > 50
28		SET  @rez = @n*@n
29	RETURN @rez
30END
31
32select dbo.funkcija(10)
33
34CREATE FUNCTION BMI (@m DECIMAL(4,1), @h DECIMAL(3,2))
35RETURNS DECIMAL (4,2)
36BEGIN
37	DECLARE @bmi DECIMAL (4,2)
38	SET @bmi = @m/(@h*@h)
39	RETURN @bmi
40END
41
42select dbo.BMI(78.3, 1.85)
43
44-- Zadaci sa ploče
45CREATE TABLE KORISNICI(
46Å¡ifra INT PRIMARY KEY,
47ime VARCHAR (30), 
48prezime VARCHAR (30),
49adresa VARCHAR (60),
50
51);
52CREATE TABLE STANOVI (
53Å¡ifra INT PRIMARY KEY,
54tip VARCHAR (30),
55kvadrata SMALLINT, 
56kat TINYINT
57);
58CREATE TABLE ZGRADE (
59Å¡ifra INT PRIMARY KEY,
60naziv VARCHAR (30),
61br_katova TINYINT,
62Å¡ifra_lok INT FOREIGN KEY REFERENCES LOKACIJE (Å¡ifra)
63);
64CREATE TABLE LOKACIJE (
65Å¡ifra INT PRIMARY KEY,
66naziv VARCHAR (30),
67adresa VARCHAR (30),
68br_dozvola INT
69);
70CREATE TABLE NAJMOVI (
71Å¡ifra INT PRIMARY KEY,
72dat_poč DATETIME, 
73dat_kraj DATETIME,
74Å¡ifra_kor INT FOREIGN KEY REFERENCES KORISNICI (Å¡ifra),
75Å¡ifra_stan INT FOREIGN KEY REFERENCES STANOVI (Å¡ifra),
76Å¡ifra_zgr INT FOREIGN KEY REFERENCES ZGRADE (Å¡ifra)
77);
78select * from najmovi
79
80insert into stanovi VALUES (0123, 'garsonjera', 34, 2)
81insert into stanovi VALUES (0763, 'jednosobni', 38, 1)
82insert into stanovi VALUES (0033, 'dvosobni', 42, 3)
83insert into stanovi VALUES (0990, 'trosobni', 56, 4)
84insert into stanovi VALUES (1100, 'trosobni', 102, 3)
85
86
87insert into lokacije values (4420, 'nazivlokacije', 'ADRESA', 55)
88insert into zgrade values (33012, 'Zgrada Plan B', 4, 4420)
89insert into najmovi values (8567, '1.1.2013', '2.2.2014', 1123, 1100, 33012, 2400)
90
91
92CREATE VIEW BROJ_STANOVA     --PRVI
93AS
94SELECT ime AS IME, prezime AS PREZIME, COUNT (Å¡ifra_stan) AS [Broj unajmljenih stanova] 
95FROM KORISNICI AS K, NAJMOVI AS N 
96WHERE N.Å¡ifra_kor = K.Å¡ifra
97GROUP BY ime, prezime
98HAVING COUNT (Å¡ifra_stan) > 5
99
100SELECT DISTINCT Å¡ifra_zgr AS [Å ifra zgrade], naziv AS Naziv, Å¡ifra_stan AS [Å ifra stana], kvadrata AS [Broj kvadrata], cijena AS CIJENA     --DRUGI
101FROM ZGRADE AS Z, STANOVI AS S, NAJMOVI AS N 
102WHERE Å¡ifra_zgr = Z.Å¡ifra AND Å¡ifra_stan = S.Å¡ifra AND cijena > 1500 AND kvadrata > 40
103
104SELECT šifra_stan AS [Šifra stana], tip AS [Tip stana], COUNT (šifra_stan) AS [Broj iznajmljivanja]  -- TREĆI
105FROM STANOVI AS S, NAJMOVI AS N
106WHERE S.Å¡ifra = N.Å¡ifra_stan
107GROUP BY Å¡ifra_stan, tip
108HAVING COUNT(Å¡ifra_stan) > (SELECT AVG(sub.izn) FROM (SELECT Å¡ifra_stan, COUNT(*) AS izn FROM NAJMOVI GROUP BY Å¡ifra_stan) AS sub)
109
110select * from stanovi
111
112SELECT DISTINCT Z.Å¡ifra, naziv, S.Å¡ifra, tip
113FROM ZGRADE AS Z, STANOVI AS S, NAJMOVI AS N
114WHERE S.Å¡ifra_zgr = Z.Å¡ifra AND NOT EXISTS (SELECT Å¡ifra_stan FROM NAJMOVI WHERE Å¡ifra_stan = S.Å¡ifra)
115
116CREATE FUNCTION Inicijali (@ime VARCHAR (30), @prezime VARCHAR (30))
117RETURNS CHAR(4)
118BEGIN
119	DECLARE @rez CHAR(4) = ''
120	SET @rez = SUBSTRING (@ime, 1,1) + '.' + SUBSTRING (@prezime,1,1) + '.'
121	RETURN @rez
122END
123
124CREATE PROC Initials @jmbg  INT,
125					 @izlaz CHAR(4) OUT
126AS 
127	DECLARE @ime VARCHAR (30) = (SELECT ime FROM KORISNICI WHERE Å¡ifra = @jmbg),
128			@prezime VARCHAR (30) = (SELECT prezime FROM KORISNICI WHERE Å¡ifra = @jmbg)
129	SET @izlaz = dbo.Inicijali (@ime, @prezime)
130	PRINT @izlaz
131
132drop proc Initials
133
134BEGIN
135declare @rez char(4)
136exec Initials 1123, @rez OUTPUT
137select @rez
138END