· 7 years ago · Sep 21, 2018, 03:46 PM
1create database ddd
2go
3use ddd
4go
5CREATE TABLE modele
6(
7 marka VARCHAR(40),
8 model VARCHAR(60) PRIMARY KEY,
9)
10
11CREATE TABLE samochody
12(
13 nr_rejestracyjny VARCHAR(20) PRIMARY KEY,
14 model VARCHAR(60) CONSTRAINT FK_samochody FOREIGN KEY (model)
15 REFERENCES modele(model),
16 kolor VARCHAR(40),
17 typ_lakieru VARCHAR(40),
18 pojemnosc_silnika INT,
19 wypadkowosc BIT,
20 il_poprz_wlascicieli INT,
21 rocznik INT,
22 przebieg INT,
23 paliwo VARCHAR(20),
24 na_stanie BIT NOT NULL,
25)
26
27CREATE TABLE osoby
28(
29 PESEL VARCHAR(11) PRIMARY KEY,
30 imie VARCHAR(100) NOT NULL,
31 nazwisko VARCHAR(100) NOT NULL,
32 adres VARCHAR(1000) NOT NULL,
33 email VARCHAR(100),
34 nr_telefonu1 BIGINT NOT NULL,
35 nr_telefonu2 BIGINT,
36)
37
38CREATE TABLE klienci
39(
40 PESEL VARCHAR(11) PRIMARY KEY REFERENCES osoby(PESEL),
41)
42
43CREATE TABLE transakcje
44(
45 ID int IDENTITY(1,1) PRIMARY KEY,
46 nr_rejestracyjny VARCHAR(20),
47 CONSTRAINT FK_transakcje FOREIGN KEY (nr_rejestracyjny)
48 REFERENCES samochody(nr_rejestracyjny),
49 klient varchar(11),
50 cena_sprzedazy MONEY,
51 data DATE,
52)
53
54CREATE TABLE pracownicy
55(
56 PESEL VARCHAR(11) PRIMARY KEY REFERENCES osoby(PESEL),
57 stanowisko VARCHAR(20) NOT NULL,
58 placa MONEY
59)
60
61CREATE TABLE przynaleznosci
62(
63 nr_rejestracyjny VARCHAR(20)
64 CONSTRAINT FK_przynaleznosci FOREIGN KEY (nr_rejestracyjny)
65 REFERENCES samochody(nr_rejestracyjny),
66 PESEL VARCHAR(11)
67 CONSTRAINT FK_przynaleznosci2 FOREIGN KEY (PESEL)
68 REFERENCES osoby(PESEL),
69)
70
71
72
73INSERT INTO modele VALUES
74('Volkswagen','Golf III'),
75('Fiat', 'Uno'),
76('Volkswagen', 'Passat B5'),
77('BMW', 'E36'),
78('BMW', 'E46'),
79('Fiat', 'Cinquecento'),
80('Ford', 'Focus')
81
82INSERT INTO samochody VALUES
83('PZL 12IU', 'Golf III', 'Czerwony', 'Metalic', 1600 , 0, 2,1999, 134000, 'benzyna', 1),
84('PZL 145U', 'Uno', 'Srebrny', 'Mat', 900, 0, 3,1995, 212300, 'benzyna', 1),
85('PO T8R8' , 'Passat B5' , 'Czarny', 'Metalic', 1900, 0, 1,1997, 180000, 'diesel', 1),
86('PO HY21' , 'E36' , 'biały', 'Metalic', 2500, 0, 1,2007, 90000, 'benzyna', 0),
87('PP 1234', 'Focus', 'biały', 'Metalic', 1200, 0, 1, 2004, 120000, 'benzyna', 0),
88('PP 433A', 'Cinquecento', 'czerwony', 'Metalic', 900, 0, 3, 1992, 230000, 'beznyna', 0)
89
90INSERT INTO osoby VALUES
91(92140622244, 'Katarzyna' , 'Zawadzka' , 'Złotów, ul. Zwycięstwa 10','katzaw@wp.pl', 560987487, NULL),
92(70081529855, 'Maciej', 'Ciastkowski', 'Warszawa, ul. Bohaterów Westerplatte 45/3' ,'ciasto@gmail.com', 726743990, NULL),
93(92090929288, 'Agnieszka', 'Kowalska', 'Poznań, al. Niepodległości 26', 'akowalska21@tlen.pl', 809990772, 501432988),
94(90021084365, 'Janusz', 'Wajs', 'Mieczewo 23' , 'wajsssssu@wp.pl', 601822092, NULL),
95(76020212312, 'Jan', 'Gaj', 'Warszawa, ul. Wojska Polskiego 123/2', 'jangaj@o2.pl', 601765345, NULL),
96(95121012100, 'Marian', 'Kowalski', 'Piła, ul. Średnia 4', 'jagnajagna@gmail.com', 554362833, 991727287),
97(89042764530, 'Andrzej', 'Nowicki', 'Rogoźno Wielkopolskie, ul. Zgodności Gospodarczej 34', 'snowdogan@gmail.com', 512632098, NULL)
98
99INSERT INTO przynaleznosci VALUES
100('PZL 12IU', 92140622244 ),
101('PO T8R8' , 92140622244 ),
102('PZL 145U', 76020212312 )
103
104
105
106INSERT INTO pracownicy VALUES
107(90021084365, 'sprzedawca', 2000.00),
108(92090929288, 'księgowa', 2500.00),
109(95121012100, 'pracownik obsługi', 2000.00)
110
111
112
113INSERT INTO transakcje VALUES
114('PO HY21', 70081529855, 10000 , '04-21-2016'),
115('PP 1234', 70081529855, 25000, '04-24-2016'),
116('PP 433A', 89042764530, 5000, '04-27-2016' )
117
118go
119create view pokaz_osoby_w_firmie as
120(
121select nazwisko as 'Nazwisko',
122imie as 'ImiÄ™',
123nr_telefonu1 as 'Telefon główny'
124from osoby
125)
126go
127create view wlasciciele_aut as
128(
129select distinct nazwisko as 'Nazwisko',
130imie as 'ImiÄ™',
131nr_rejestracyjny as 'Numer rejestracyjny'
132from
133przynaleznosci inner join osoby on przynaleznosci.PESEL=osoby.PESEL
134)
135go
136create view ostatnio_sprzedane_samochody as
137(
138select top 10
139nr_rejestracyjny as 'Numer rejestracyjny',
140data as 'Data sprzedaży',
141cena_sprzedazy/10+100 as 'Zysk na sprzedaży',
142nazwisko as 'Nazwisko'
143from
144transakcje inner join osoby on transakcje.klient=osoby.PESEL
145order by data DESC
146)
147go
148select * from transakcje
149go
150create procedure sredni_zysk as
151begin
152declare @zm money
153select @zm=avg(cena_sprzedazy/100+100) from transakcje
154select @zm
155end
156
157go
158create procedure suma_plac_pracownikow
159as begin
160declare @zm money
161select @zm=sum(placa) from pracownicy
162select @zm
163end
164
165go
166
167create procedure samochody_nowsze_od
168@r int
169as
170select
171nr_rejestracyjny as 'Numer rejestracyjny',
172model as 'Model',
173rocznik as 'Rocznik'
174from samochody
175where rocznik >= @r
176
177go
178--exec dodawanie_osoby_do_bazy @PESEL_k='93012034322', @imie_k='Sławomir', @nazwisko_k='Lisicki', @adres_k='Nużyce 2', @nr_telefonu1_k=726880331
179--exec samochody_nowsze_od @r=1997
180--exec suma_plac_pracownikow
181--execute sredni_zysk
182--select * from ostatnio_sprzedane_samochody
183--select * from wlasciciele_aut
184--select * from wyszukiwanie_klienta('Ciastkowski')
185
186
187create procedure dodawanie_osoby_do_bazy
188@PESEL_k varchar(11),
189@imie_k varchar(100),
190@nazwisko_k varchar(100),
191@adres_k varchar(1000),
192@nr_telefonu1_k bigint
193as
194insert into osoby values (@PESEL_k, @imie_k, @nazwisko_k, @adres_k, NULL, @nr_telefonu1_k, null)
195
196
197go
198create function wyszukiwanie_klienta
199(@nazwisko_klienta varchar(20))
200returns table as return
201(
202select
203PESEL,
204imie as 'ImiÄ™',
205nazwisko as 'Nazwisko',
206nr_telefonu1 as 'Numer Główny'
207from osoby
208where nazwisko=@nazwisko_klienta
209)
210go
211create function szacowany_zysk
212(@cena_pojazdu money)
213returns money as
214begin
215declare @zm as money
216set @zm=(@cena_pojazdu/100)+100
217return @zm
218end
219go
220create function wyszukiwanie_modelu
221(@zm varchar(60)) returns table as return
222(
223select * from samochody where model = @zm
224)
225go
226
227create trigger pensja_nie_mniejsza_niz_0
228on pracownicy
229after update
230as
231begin
232 if exists (select * from pracownicy where placa<0)
233 begin
234 rollback
235 print('Pracownik nie moze miec ujemnej pensji')
236 end
237end
238
239--update pracownicy set placa=-23 where PESEL=90021084365
240
241go
242create trigger nowa_transakcja_nie_ze_zla_data
243on transakcje
244after insert
245as
246begin
247 declare @data date
248 set @data = getdate()
249 if exists (select data from inserted where data<@data)
250 begin
251 rollback
252 print('Nie można ustawiać daty transakcji na datę w przeszłości')
253 end
254end
255
256--insert into transakcje values ('PO HY21', 70081529855, 10000,'04-25-2016')
257
258
259go
260create trigger usuwanie_transakcji
261on transakcje
262after delete
263as
264begin
265 rollback
266 print('Nie można usuwać transakcji z bazy')
267end
268
269
270--delete transakcje
271
272
273create database ddd
274go
275use ddd
276go
277CREATE TABLE modele
278(
279 marka VARCHAR(40),
280 model VARCHAR(60) PRIMARY KEY,
281)
282
283CREATE TABLE samochody
284(
285 nr_rejestracyjny VARCHAR(20) PRIMARY KEY,
286 model VARCHAR(60) CONSTRAINT FK_samochody FOREIGN KEY (model)
287 REFERENCES modele(model),
288 kolor VARCHAR(40),
289 typ_lakieru VARCHAR(40),
290 pojemnosc_silnika INT,
291 wypadkowosc BIT,
292 il_poprz_wlascicieli INT,
293 rocznik INT,
294 przebieg INT,
295 paliwo VARCHAR(20),
296 na_stanie BIT NOT NULL,
297)
298
299CREATE TABLE osoby
300(
301 PESEL VARCHAR(11) PRIMARY KEY,
302 imie VARCHAR(100) NOT NULL,
303 nazwisko VARCHAR(100) NOT NULL,
304 adres VARCHAR(1000) NOT NULL,
305 email VARCHAR(100),
306 nr_telefonu1 BIGINT NOT NULL,
307 nr_telefonu2 BIGINT,
308)
309
310CREATE TABLE klienci
311(
312 PESEL VARCHAR(11) PRIMARY KEY REFERENCES osoby(PESEL),
313)
314
315CREATE TABLE transakcje
316(
317 ID int IDENTITY(1,1) PRIMARY KEY,
318 nr_rejestracyjny VARCHAR(20),
319 CONSTRAINT FK_transakcje FOREIGN KEY (nr_rejestracyjny)
320 REFERENCES samochody(nr_rejestracyjny),
321 klient varchar(11),
322 cena_sprzedazy MONEY,
323 data DATE,
324)
325
326CREATE TABLE pracownicy
327(
328 PESEL VARCHAR(11) PRIMARY KEY REFERENCES osoby(PESEL),
329 stanowisko VARCHAR(20) NOT NULL,
330 placa MONEY
331)
332
333CREATE TABLE przynaleznosci
334(
335 nr_rejestracyjny VARCHAR(20)
336 CONSTRAINT FK_przynaleznosci FOREIGN KEY (nr_rejestracyjny)
337 REFERENCES samochody(nr_rejestracyjny),
338 PESEL VARCHAR(11)
339 CONSTRAINT FK_przynaleznosci2 FOREIGN KEY (PESEL)
340 REFERENCES osoby(PESEL),
341)
342
343
344
345INSERT INTO modele VALUES
346('Volkswagen','Golf III'),
347('Fiat', 'Uno'),
348('Volkswagen', 'Passat B5'),
349('BMW', 'E36'),
350('BMW', 'E46'),
351('Fiat', 'Cinquecento'),
352('Ford', 'Focus')
353
354INSERT INTO samochody VALUES
355('PZL 12IU', 'Golf III', 'Czerwony', 'Metalic', 1600 , 0, 2,1999, 134000, 'benzyna', 1),
356('PZL 145U', 'Uno', 'Srebrny', 'Mat', 900, 0, 3,1995, 212300, 'benzyna', 1),
357('PO T8R8' , 'Passat B5' , 'Czarny', 'Metalic', 1900, 0, 1,1997, 180000, 'diesel', 1),
358('PO HY21' , 'E36' , 'biały', 'Metalic', 2500, 0, 1,2007, 90000, 'benzyna', 0),
359('PP 1234', 'Focus', 'biały', 'Metalic', 1200, 0, 1, 2004, 120000, 'benzyna', 0),
360('PP 433A', 'Cinquecento', 'czerwony', 'Metalic', 900, 0, 3, 1992, 230000, 'beznyna', 0)
361
362INSERT INTO osoby VALUES
363(92140622244, 'Katarzyna' , 'Zawadzka' , 'Złotów, ul. Zwycięstwa 10','katzaw@wp.pl', 560987487, NULL),
364(70081529855, 'Maciej', 'Ciastkowski', 'Warszawa, ul. Bohaterów Westerplatte 45/3' ,'ciasto@gmail.com', 726743990, NULL),
365(92090929288, 'Agnieszka', 'Kowalska', 'Poznań, al. Niepodległości 26', 'akowalska21@tlen.pl', 809990772, 501432988),
366(90021084365, 'Janusz', 'Wajs', 'Mieczewo 23' , 'wajsssssu@wp.pl', 601822092, NULL),
367(76020212312, 'Jan', 'Gaj', 'Warszawa, ul. Wojska Polskiego 123/2', 'jangaj@o2.pl', 601765345, NULL),
368(95121012100, 'Marian', 'Kowalski', 'Piła, ul. Średnia 4', 'jagnajagna@gmail.com', 554362833, 991727287),
369(89042764530, 'Andrzej', 'Nowicki', 'Rogoźno Wielkopolskie, ul. Zgodności Gospodarczej 34', 'snowdogan@gmail.com', 512632098, NULL)
370
371INSERT INTO przynaleznosci VALUES
372('PZL 12IU', 92140622244 ),
373('PO T8R8' , 92140622244 ),
374('PZL 145U', 76020212312 )
375
376
377
378INSERT INTO pracownicy VALUES
379(90021084365, 'sprzedawca', 2000.00),
380(92090929288, 'księgowa', 2500.00),
381(95121012100, 'pracownik obsługi', 2000.00)
382
383
384
385INSERT INTO transakcje VALUES
386('PO HY21', 70081529855, 10000 , '04-21-2016'),
387('PP 1234', 70081529855, 25000, '04-24-2016'),
388('PP 433A', 89042764530, 5000, '04-27-2016' )
389
390go
391create view pokaz_osoby_w_firmie as
392(
393select nazwisko as 'Nazwisko',
394imie as 'ImiÄ™',
395nr_telefonu1 as 'Telefon główny'
396from osoby
397)
398go
399create view wlasciciele_aut as
400(
401select distinct nazwisko as 'Nazwisko',
402imie as 'ImiÄ™',
403nr_rejestracyjny as 'Numer rejestracyjny'
404from
405przynaleznosci inner join osoby on przynaleznosci.PESEL=osoby.PESEL
406)
407go
408create view ostatnio_sprzedane_samochody as
409(
410select top 10
411nr_rejestracyjny as 'Numer rejestracyjny',
412data as 'Data sprzedaży',
413cena_sprzedazy/10+100 as 'Zysk na sprzedaży',
414nazwisko as 'Nazwisko'
415from
416transakcje inner join osoby on transakcje.klient=osoby.PESEL
417order by data DESC
418)
419go
420select * from transakcje
421go
422create procedure sredni_zysk as
423begin
424declare @zm money
425select @zm=avg(cena_sprzedazy/100+100) from transakcje
426select @zm
427end
428
429go
430create procedure suma_plac_pracownikow
431as begin
432declare @zm money
433select @zm=sum(placa) from pracownicy
434select @zm
435end
436
437go
438
439create procedure samochody_nowsze_od
440@r int
441as
442select
443nr_rejestracyjny as 'Numer rejestracyjny',
444model as 'Model',
445rocznik as 'Rocznik'
446from samochody
447where rocznik >= @r
448
449go
450--exec dodawanie_osoby_do_bazy @PESEL_k='93012034322', @imie_k='Sławomir', @nazwisko_k='Lisicki', @adres_k='Nużyce 2', @nr_telefonu1_k=726880331
451--exec samochody_nowsze_od @r=1997
452--exec suma_plac_pracownikow
453--execute sredni_zysk
454--select * from ostatnio_sprzedane_samochody
455--select * from wlasciciele_aut
456--select * from wyszukiwanie_klienta('Ciastkowski')
457
458
459create procedure dodawanie_osoby_do_bazy
460@PESEL_k varchar(11),
461@imie_k varchar(100),
462@nazwisko_k varchar(100),
463@adres_k varchar(1000),
464@nr_telefonu1_k bigint
465as
466insert into osoby values (@PESEL_k, @imie_k, @nazwisko_k, @adres_k, NULL, @nr_telefonu1_k, null)
467
468
469go
470create function wyszukiwanie_klienta
471(@nazwisko_klienta varchar(20))
472returns table as return
473(
474select
475PESEL,
476imie as 'ImiÄ™',
477nazwisko as 'Nazwisko',
478nr_telefonu1 as 'Numer Główny'
479from osoby
480where nazwisko=@nazwisko_klienta
481)
482go
483create function szacowany_zysk
484(@cena_pojazdu money)
485returns money as
486begin
487declare @zm as money
488set @zm=(@cena_pojazdu/100)+100
489return @zm
490end
491go
492create function wyszukiwanie_modelu
493(@zm varchar(60)) returns table as return
494(
495select * from samochody where model = @zm
496)
497go
498
499create trigger pensja_nie_mniejsza_niz_0
500on pracownicy
501after update
502as
503begin
504 if exists (select * from pracownicy where placa<0)
505 begin
506 rollback
507 print('Pracownik nie moze miec ujemnej pensji')
508 end
509end
510
511--update pracownicy set placa=-23 where PESEL=90021084365
512
513go
514create trigger nowa_transakcja_nie_ze_zla_data
515on transakcje
516after insert
517as
518begin
519 declare @data date
520 set @data = getdate()
521 if exists (select data from inserted where data<@data)
522 begin
523 rollback
524 print('Nie można ustawiać daty transakcji na datę w przeszłości')
525 end
526end
527
528--insert into transakcje values ('PO HY21', 70081529855, 10000,'04-25-2016')
529
530
531go
532create trigger usuwanie_transakcji
533on transakcje
534after delete
535as
536begin
537 rollback
538 print('Nie można usuwać transakcji z bazy')
539end
540
541
542--delete transakcje