· 6 years ago · Nov 29, 2019, 10:34 AM
1--ISHOD 1
2--1—
3select p .Naziv, count(s.Kolicina) as kolicina
4from Proizvod as p
5inner join Stavka as s on p.IDProizvod=s.ProizvodID
6inner join Racun as r on r.IDRacun=s.RacunID
7inner join kupac as k on r.KupacID=k.IDKupac
8inner join Grad as g on k.GradID=g.IDGrad
9inner join Drzava as d on d.IDDrzava=g.DrzavaID
10where d.Naziv='Hrvatska' and k.Telefon like '[1,2,3]%'
11group by p.Naziv
12order by kolicina desc
13--2---
14
15select * from kupac where gradid=16
16
17insert into Kupac(Ime, Prezime, Email, Telefon, GradID)
18values('Đuro','Đurić',NULL,Null,16)
19
20insert into Drzava
21values('Japan')
22
23insert into Grad(Naziv, DrzavaID)
24values('Yokohama', 6)
25--3---
26create table NajmanjeProdavaniProizvod1
27(
28IDProizvod int,
29Naziv nvarchar(150),
30ProdaniKomadi int
31)
32
33select * from NajmanjeProdavaniProizvod
34
35insert into NajmanjeProdavaniProizvod1
36select p.IDProizvod,p.Naziv, sum(s.kolicina) as kolicina
37from Proizvod as p
38inner join stavka as s on s.ProizvodID=p.IDProizvod
39group by p.IDProizvod,kolicina,p.Naziv
40having sum(s.kolicina)<10
41
42
43-- verzija sa ubacivanjem i onih proizvoda koji nikad nisu prodani
44insert into NajmanjeProdavaniProizvodi (IDProizvod, Naziv, ProdanihKomada)
45select p.IDProizvod,
46 p.Naziv,
47 isnull((select sum(Kolicina) from Stavka where ProizvodID = p.IDProizvod),0)
48from Proizvod as p
49where (select sum(Kolicina) from Stavka where ProizvodID = p.IDProizvod) < 10
50or (select sum(Kolicina) from Stavka where ProizvodID = p.IDProizvod) IS NULL
51
52---4 -- ddl naredbe za kreiranje tablica, stupaca i ograničenja (itd...)
53
54create table Student (
55 IDStudent int identity,
56 Ime nvarchar(50) not null,
57 Prezime nvarchar(50) not null,
58 DatumRodjenja date not null,
59 JMBAG nvarchar(15) not null,
60 CijenaSkolarine money not null,
61 RedovniStudent bit not null,
62 constraint PK_Student primary key(IDStudent),
63 constraint UQ_Student unique(JMBAG)
64)
65
66create table Kolegij (
67 IDKolegij int identity,
68 Naziv nvarchar(50) not null,
69 Ects tinyint not null,
70 constraint PK_Kolegij primary key(IDKolegij),
71 constraint CH_Kolegij check(Ects between 1 and 7)
72)
73
74create table Upis (
75 IDUpis int identity,
76 StudentID int not null,
77 KolegijID int not null,
78 DatumUpisa date not null default getdate(),
79 constraint PK_Upis primary key(IDUpis),
80 constraint FK_UpisStudent foreign key(StudentID) references Student(IDStudent),
81 constraint FK_UpisKolegij foreign key(KolegijID) references Kolegij(IDKolegij)
82)
83
84drop table Student
85drop table Kolegij
86drop table Upis
87
88
89
90---ISHOD 2----
91s WITH CHECK OPTION takvi se retci ne smiju umetati/mijenjati: CREATE/ALTER VIEW naziv_pogleda AS SELECT_naredba WITH CHECK OPTION
92
93Opcija WITH SCHEMABINDING u pogledu brani promjenu dizajna tablice koju koristi pogled: CREATE VIEW naziv_pogleda WITH SCHEMABINDING AS SELECT_naredba
94
95• Korištenjem opcije WITH ENCRYPTION moguće je onemogućiti pregled sadržaja pogleda: CREATE VIEW naziv_pogleda WITH ENCRYPTION AS SELECT_naredba
96
97
98-- 5 -- pogled koji vraća sve komercijaliste sa bar jednom prodajom napravit
99-- iskoristit pogled za stupce: ime, prezime, godinu izdavanja te koliko računa te godine
100
101-- stvaranje pogleda
102create view vwKomercijalist
103as
104select *
105from Komercijalist as k
106inner join Racun as r on r.KomercijalistID = k.IDKomercijalist
107
108select * from vwKomercijalist
109
110-- korištenje pogleda za dohvatit detaljnije
111select
112 Prezime + ' ' + Ime as Komercijalist,
113 year(DatumIzdavanja) as GodinaIzdavanja,
114 count(*) as UkupnoRacuna
115from vwKomercijalist
116group by Prezime + ' ' + Ime, year(DatumIzdavanja)
117
118drop view vwKomercijalist
119
120---2--- pogled koji vraća države sa najmanje 3 grada
121-- promijeniti pogled tako da vraća i točan broj gradova
122create view pzad2
123as
124select d.Naziv
125from drzava as d
126inner join Grad as g on g.DrzavaID=d.IDDrzava
127group by d.Naziv
128having count(g.DrzavaID) >2
129
130
131alter view pzad2
132as
133select d.Naziv,count(g.DrzavaID)
134from drzava as d
135inner join Grad as g on g.DrzavaID=d.IDDrzava
136group by d.Naziv
137having count(g.DrzavaID) >2
138
139
140
141-- 7 -- pogled vraća sve proizvode koji imaju definiranu boju
142-- sprječiti da se ubaci nešto što kroz pogled neće biti dohvatljivo
143create view vwProizvod
144as
145select *
146from Proizvod
147where Boja IS NOT NULL
148
149select * from Proizvod
150select * from vwProizvod
151
152alter view vwProizvod
153as
154select *
155from Proizvod
156where Boja IS NOT NULL
157with check option
158
159--- 8 -- pogled koji dohvaća godinu i mjesec te koliko MasterCard kartica istječe tad
160-- iskoristiti pogled za 10 mjeseci sa najviše kartica koje istječu
161-- sprječiti da netko može potrgat pogled brisanjem tablice KreditnaKartica
162create view vwKreditnaKartica
163as
164select
165 cast(IstekGodina as nvarchar(4)) + '-' + cast(IstekMjesec as nvarchar(2)) as DatumIsteka,
166 count(*) as KolicinaKartica
167from KreditnaKartica
168where Tip = 'MasterCard'
169group by cast(IstekGodina as nvarchar(4)) + '-' + cast(IstekMjesec as nvarchar(2))
170
171select top 10
172*
173from vwKreditnaKartica
174order by KolicinaKartica desc
175
176alter view vwKreditnaKartica
177with schemabinding
178as
179select
180 cast(IstekGodina as nvarchar(4)) + '-' + cast(IstekMjesec as nvarchar(2)) as DatumIsteka,
181 count(*) as KolicinaKartica
182from dbo.KreditnaKartica
183where Tip = 'MasterCard'
184group by cast(IstekGodina as nvarchar(4)) + '-' + cast(IstekMjesec as nvarchar(2))
185
186--ISHOD 3
187DBCC TRACEON(3604)
188DBCC IND('naziv_baze', 'naziv_tablice', -1)
189DBCC PAGE('naziv_baze', FID, PID, 3) WITH TABLERESULTS
190
191SET STATISTICS IO ON
192CREATE NONCLUSTERED INDEX naziv ON tablica(stupac1,…)
193INCLUDE (stupac1, …)
194DROP INDEX indeks ON tablica
195
196
197-- 9 -- maknite sve nk indekse sa dbo.Proizvod, pa pitanja
198-- P: na koliko stranica su smješteni podaci iz tablice Proizvod?
199-- O: na 6 podatkovnih stranica (i ima jedna indeks stranica)
200dbcc traceon(3604)
201dbcc ind([AdventureWorksOBP], [Proizvod], -1)
202-- P: na prvoj podatkovnoj stranici, koji je IDProizvod za 1 i zadnji proizvod na 1. stranici?
203-- O: 1. je 1, zadnji je 425
204dbcc page(AdventureWorksOBP, 1, 178, 3) with tableresults
205-- P: na kojoj stranici se nalazi proizvod s ID-jem 777?
206-- O: nalazi se na stranici 183 (slot 70)
207dbcc page(AdventureWorksOBP, 1, 183, 3) with tableresults
208-- P: umetnite novi proizvod, na koju je stranicu dodan?
209-- O: budući da se sve sprema prema primarnom ključu koji se sam generira inkrementom, proizvod
210-- je dodan na zadnju stranicu (189) u slot 63
211insert into Proizvod (Naziv, BrojProizvoda, Boja, MinimalnaKolicinaNaSkladistu, CijenaBezPDV)
212values ('Mačje Oko', 'PR0907','Roza', 2, 12)
213dbcc page(AdventureWorksOBP, 1, 189, 3) with tableresults
214
215-- 10 -- Optimizirati upit
216set statistics io on
217
218select
219 IDKreditnaKartica,
220 Broj,
221 Tip
222from KreditnaKartica
223where Tip like 'Master%'
224 and IstekGodina = 2007
225-- Scan count 1, logical reads 164
226
227--create nonclustered index index1 on KreditnaKartica(Tip)
228--include(Broj, IstekGodina)
229---- Scan count 1, logical reads 43
230
231--drop index index1 on KreditnaKartica
232
233--create nonclustered index index2 on KreditnaKartica(Broj)
234--include(Tip, IstekGodina)
235---- Scan count 1, logical reads 155
236
237--drop index index2 on KreditnaKartica
238
239create nonclustered index index3 on KreditnaKartica(IstekGodina)
240include(Tip, Broj)
241-- Scan count 1, logical reads 42
242
243-- INDEX 3 JE NAJJAČI
244
245
246-- 11 -- procedura koja prima ime i prezime komercijalista i ubacuje novoga, koroz izlazni parametar
247-- vraća novonastali id, odnosno -1 ukoliko isti već postoji (pa ga se ni ne mora ubacivat)create proc p41
248@ime nvarchar(50),
249@prezime nvarchar(50),
250@iid int output
251as
252if exists (select IDKomercijalist from Komercijalist where Ime = @ime and Prezime = @prezime)
253begin
254 set @iid = -1
255end
256else
257begin
258 insert into Komercijalist (Ime, Prezime)
259 values (@ime, @prezime)
260 set @iid = SCOPE_IDENTITY()
261end
262
263declare @idnov int
264exec p41'ante','basa',@idnov output
265print @idnov
266
267-- 12 -- procedura prima ID proizvoda, provjerava postoji li i je li kad prodan, preko return mora vratit
268-- sljedeće vrijednosti -1 (ne postoji), 0 (postoji, nikad prodan) 1 (postoji i prodavan n puta)
269
270create proc ProvjeriProizvod
271 @idProizvod int
272as
273if not exists (select Naziv from Proizvod where IDProizvod = @idProizvod) begin
274 return -1
275end
276if @idProizvod IN (select
277 p.IDProizvod
278 from Proizvod as p
279 where (select sum(Kolicina) from Stavka where ProizvodID = p.IDProizvod) IS NULL)
280begin
281 return 0
282end
283 return 1
284go
285
286
287declare @ajdi int = 1001
288declare @rezultat int
289-- postojeći, nikad prodani proizvod
290exec @rezultat = p123 @ajdi
291print @rezultat
292-- postojeći prodavani proizvod
293set @ajdi = 707
294exec @rezultat = ProvjeriProizvod @ajdi
295print @rezultat
296-- nepostojeći proizvod
297set @ajdi = 20000
298exec @rezultat = ProvjeriProizvod @ajdi
299print @rezultat
300go
301
302select * from proizvod
303where (select sum(Kolicina) from Stavka where ProizvodID = IDProizvod) IS NOT NULL
304
305-- 13 -- implementirajte CRUD na Potkategorija s 2 procedure (C,U) i (R,D)
306-- napravite umetanje, izmjenu, dohvaćanje i brisanje jedne potkategorije
307
308create proc cu131
309@idpotkategorija int output,
310@naziv nvarchar(100),
311@kategorijaid int
312as
313if exists (select * from Potkategorija where IDPotkategorija=@idpotkategorija)
314begin
315 update Potkategorija
316 set KategorijaID=@kategorijaid,
317 Naziv=@naziv
318end
319else
320begin
321 insert into Potkategorija (Naziv, KategorijaID)
322 values (@naziv, @kategorijaid)
323 set @idPotkategorija = SCOPE_IDENTITY()
324end
325
326
327-- demonstracija, prvo napravi pa updatea
328declare @noviAjdi int
329exec cu131 @noviAjdi output, 'ante',1
330print @noviAjdi
331select * from Potkategorija where IDPotkategorija = @noviAjdi
332
333exec cu131 @noviajdi output, 'anssste' ,1
334select * from Potkategorija where IDPotkategorija = @noviAjdi
335
336CREATE PROC RD13
337@IDPOTKATEGORIJA INT,
338@ODABIR CHAR(1)
339AS
340IF @ODABIR='R'
341BEGIN
342SELECT * FROM Potkategorija WHERE Potkategorija.IDPotkategorija=@IDPOTKATEGORIJA
343END
344ELSE IF @ODABIR='D'
345BEGIN
346DELETE FROM Potkategorija WHERE Potkategorija.IDPotkategorija=@IDPOTKATEGORIJA
347END
348
349
350EXEC RD13 41,R
351EXEC RD13 41,D