· 6 years ago · Jun 23, 2019, 03:42 PM
1DROP TABLE IF EXISTS Sprzedaz;
2DROP TABLE IF EXISTS Samochod_osobowy;
3DROP TABLE IF EXISTS Samochod_dealer;
4DROP TABLE IF EXISTS Samochod_ciezarowy;
5DROP TABLE IF EXISTS Samochod_DodatkoweWyposazenie;
6DROP TABLE IF EXISTS Model_TypSilnika;
7DROP TABLE IF EXISTS Model_Dealer;
8DROP TABLE IF EXISTS Samochod;
9DROP TABLE IF EXISTS Model;
10DROP TABLE IF EXISTS Marka;
11DROP TABLE IF EXISTS Typ_silnika;
12DROP TABLE IF EXISTS Klient;
13DROP TABLE IF EXISTS Dealer;
14DROP TABLE IF EXISTS Dodatkowe_Wyposazenie;
15
16CREATE TABLE Marka
17(
18nazwa VARCHAR(50) PRIMARY KEY CHECK(nazwa IN ('Volkswagen','Ford','Renault','Peugeot','Opel','Mercedes','Audi','BMW','Fiat','Skoda','Tesla')) ,
19start_dzialanosci DATE NOT NULL
20);
21
22CREATE TABLE Model
23(
24identyfikator INT PRIMARY KEY IDENTITY,
25nazwa VARCHAR(50) NOT NULL,
26rokstartu DATE NOT NULL,
27markanazwa VARCHAR(50) REFERENCES Marka(nazwa),
28idgeneracjaaa INT NULL REFERENCES Model(identyfikator)
29);
30CREATE TABLE Typ_silnika
31(
32identyfikator INT PRIMARY KEY IDENTITY,
33copali VARCHAR(50) NOT NULL,
34opisparametrow VARCHAR(50) NOT NULL
35);
36CREATE TABLE Samochod_Ciezarowy
37(
38model_id INT PRIMARY KEY REFERENCES Model(identyfikator),
39ilemoznazapkowac BIGINT NOT NULL CHECK(ilemoznazapkowac IN ('200','400','600','900','950'))
40);
41
42CREATE TABLE Samochod_osobowy
43(
44model_id INT PRIMARY KEY REFERENCES Model(identyfikator),
45ilosccosob INT NOT NULL,
46ilosctowaru INT NOT NULL
47);
48CREATE TABLE Samochod
49(
50vin VARCHAR(50) PRIMARY KEY,
51przebieg_km INT NOT NULL,
52skrzyniabiegow INT NOT NULL,
53krajpochodzenia VARCHAR(50),
54rokprodukcji DATE,
55model_id INT REFERENCES Model(identyfikator),
56Typ_silnika_ID INT REFERENCES Typ_silnika(identyfikator),
57);
58CREATE TABLE Dealer
59(
60nazwa VARCHAR(50) PRIMARY KEY,
61adres VARCHAR(50) NOT NULL
62);
63
64CREATE TABLE Samochod_Dealer
65(
66vin VARCHAR(50) PRIMARY KEY REFERENCES Samochod(vin),
67dealernazwa VARCHAR(50) REFERENCES Dealer(nazwa)
68);
69
70CREATE TABLE Dodatkowe_wyposazenie
71(
72nazwa VARCHAR(50) PRIMARY KEY
73);
74
75
76CREATE TABLE Klient (
77ID INT PRIMARY KEY IDENTITY,
78imie VARCHAR(50) NOT NULL,
79nazwisko VARCHAR(50) NOT NULL,
80nr_telefon BIGINT NOT NULL
81);
82
83CREATE TABLE Sprzedaz
84(
85data_sprzedazy DATE PRIMARY KEY,
86koszt MONEY CHECK(koszt >= 100 AND koszt <= 51000),
87sam_vin VARCHAR(50) REFERENCES Samochod(vin),
88dealernazwa VARCHAR(50) REFERENCES Dealer(nazwa),
89klient_id INT REFERENCES Klient(ID)
90);
91
92CREATE TABLE Model_TypSilnika
93(
94model_id INT PRIMARY KEY REFERENCES Model(identyfikator),
95typsilnika_id INT REFERENCES Typ_silnika(identyfikator)
96);
97
98CREATE TABLE Model_Dealer
99(
100model_id INT PRIMARY KEY REFERENCES Model(identyfikator),
101dealernazwa VARCHAR(50) REFERENCES Dealer(nazwa)
102);
103
104CREATE TABLE Samochod_DodatkoweWyposazenie
105(
106vin VARCHAR(50) PRIMARY KEY REFERENCES Samochod(vin),
107nazwa_wyposazenia VARCHAR(50) REFERENCES Dodatkowe_wyposazenie(nazwa)
108);
109--CREATE UNIQUE INDEX CUI
110--ON Model(idgeneracjaaa)
111--WHERE idgeneracjaaa IS NOT NULL;
112
113
114INSERT INTO dbo.Marka(nazwa,start_dzialanosci) VALUES
115('Volkswagen', '1937'),
116('Ford', '1903'),
117('Renault', '1898'),
118('Peugeot', '1896'),
119('Opel', '1862'),
120('Mercedes', '1926'),
121('Audi', '1909'),
122('BMW', '1916'),
123('Fiat', '1899'),
124('Skoda', '1895')
125
126INSERT INTO dbo.Model(nazwa,rokstartu,markanazwa,idgeneracjaaa) VALUES
127('Vol v1', '2001', 'Volkswagen', 1),
128('Vol v2', '2002', 'Volkswagen', 2),
129('Vol v3', '2003', 'Volkswagen', 3),
130('Ford v1', '2004', 'Ford', 4),
131('Ford v2', '2005', 'Ford', 5),
132('Ford v3', '2006', 'Ford', 6),
133('Rena v1', '2007', 'Renault', 7),
134('Rena v2', '2008', 'Renault', 8),
135('Rema v3', '2009', 'Renault', 9),
136('Fiat v1', '2010', 'Fiat', 10)
137
138INSERT INTO dbo.Typ_silnika(copali,opisparametrow) VALUES
139('woda', 'szybko i sprawnie'),
140('wodka', 'megadopalanie'),
141('gas', 'drogi ale dobry'),
142('woda', 'na deszczowce tez jezdzi'),
143('prad', 'duzo je ale za to sie oplaca'),
144('gas', 'drogi ale dobry'),
145('gas', 'drogi ale dobry'),
146('prad', 'nie ma go nie jade'),
147('95', 'standardowo'),
148('slonce', 'najlepsze')
149INSERT INTO dbo.Samochod_Ciezarowy(model_id,ilemoznazapkowac) VALUES
150(1, 200),
151(2, 400),
152(3, 600),
153(4, 900),
154(5, 950),
155(6, 950),
156(7, 400),
157(8, 600),
158(9, 900),
159(10, 900)
160INSERT INTO dbo.Samochod_osobowy (model_id,ilosccosob,ilosctowaru) VALUES
161(1, 4, 200),
162(2, 4, 200),
163(3, 5, 200),
164(4, 5, 200),
165(5, 6, 200),
166(6, 2, 400),
167(7, 2, 400),
168(8, 2, 400),
169(9, 4, 450),
170(10,8, 400)
171INSERT INTO dbo.Samochod ( vin, przebieg_km, skrzyniabiegow,krajpochodzenia,rokprodukcji,model_id,Typ_silnika_ID) VALUES
172('1GCEK14H6DS161890', 100, '1', 'Wloch', '2001', 1, 1),
173('2T1BU4EEXBC622932', 1000, '2', 'Polak', '2002', 2, 2),
174('3N1AB7AP5DL710655', 10000, '3', 'Frnacuz', '2003', 3, 3),
175('1FBJS31L6VHA87909', 2500, '4', 'Niemiec', '2004', 4, 4),
176('1HGCP26869A183852', 9999, '5', 'Angol', '2005', 5, 5),
177('2FMGK5C88EBD11829', 7777, '6', 'Angol', '2006', 6, 1),
178('1C6RR7FT9ES155382', 45500, '7', 'Angol', '2007', 7, 2),
179('5XYZWDLA8FG257248', 14585, '8', 'Frnacuz', '2008', 8, 3),
180('1C4RJFAG4EC150198', 52852, '9', 'Niemiec', '2009', 9, 4),
181('5NPEB4AC6CH463090', 52895, '10', 'Polak', '2010', 10, 5)
182INSERT INTO dbo.Dealer (nazwa, adres) VALUES
183('Wazniak', 'Warszawa ulica Wazniak 1 '),
184('Osilek', 'Warszawa ulica Osilek 1 '),
185('Lasuch', 'Warszawa ulica Lasuch 1'),
186('Maruda', 'Warszawa ulica Maruda 1'),
187('Ciamajda ', 'Warszawa ulica Ciamajda 1'),
188('Lalus', 'Warszawa ulica Lalus 1'),
189('Spioch', 'Warszawa ulica Spioch 1'),
190('Pracus', 'Warszawa ulica Pracus 1'),
191('Smerfetka', 'Warszawa ulica Smerfetka 1'),
192('Zgrywus', 'Warszawa ulica Zgrywus 1')
193INSERT INTO dbo.Samochod_Dealer (vin,dealernazwa) VALUES
194('1GCEK14H6DS161890', 'Wazniak'),
195('2T1BU4EEXBC622932', 'Osilek'),
196('3N1AB7AP5DL710655', 'Lasuch'),
197('1FBJS31L6VHA87909', 'Maruda'),
198('1HGCP26869A183852', 'Ciamajda'),
199('2FMGK5C88EBD11829', 'Lalus'),
200('1C6RR7FT9ES155382', 'Spioch'),
201('5XYZWDLA8FG257248', 'Pracus'),
202('1C4RJFAG4EC150198', 'Smerfetka'),
203('5NPEB4AC6CH463090', 'Zgrywus')
204INSERT INTO dbo.Dodatkowe_wyposazenie (nazwa) VALUES
205('apteczka'),
206('zapasowe kolo'),
207('zapasowa zarowka'),
208('zestaw narzedzi'),
209('kamilzelka odblaskowa'),
210('szyby antynapadowe'),
211('budzik'),
212('tempomat'),
213('bagaznik na rowery'),
214('naklejka zapachowa')
215INSERT INTO dbo.Klient (imie,nazwisko,nr_telefon) VALUES
216('Maja', 'Nowak', 1758746079622),
217('Lena', 'Podolska', 17403175202),
218('Oliwia', 'Witczak', 17147447034),
219('Alicja', 'Nowijska', 174141144284),
220('Oliwia', 'Znizka', 1726787009597),
221('Antoni', 'Mekoszts', 17347942084),
222('Aleksander', 'Nowosilcow', 17092811188),
223('Filip', 'Kaczynski', 1776085725175),
224('Mikołaj', 'Nowy', 1769176996615),
225('Kacper', 'Witowski', 1736614762310)
226INSERT INTO dbo.Sprzedaz(data_sprzedazy,koszt,sam_vin,dealernazwa,klient_id) VALUES
227('2001', 10000, '1GCEK14H6DS161890','Osilek', 1),
228('2002', 11000, '1GCEK14H6DS161890', 'Osilek', 2),
229('2003', 20000, '3N1AB7AP5DL710655', 'Maruda', 3),
230('2004', 21000, '1GCEK14H6DS161890', 'Wazniak', 4),
231('2005', 22000, '3N1AB7AP5DL710655', 'Spioch', 5),
232('2006', 30000, '1C4RJFAG4EC150198', 'Spioch', 6),
233('2007', 33000, '2FMGK5C88EBD11829', 'Zgrywus', 7),
234('2008', 40000, '1C4RJFAG4EC150198', 'Smerfetka', 8),
235('2009', 42000, '1C6RR7FT9ES155382', 'Smerfetka', 9),
236('2010', 50000, '1C6RR7FT9ES155382', 'Smerfetka', 10)
237INSERT INTO dbo.Model_TypSilnika (model_id,typsilnika_id) VALUES
238(01, 1),
239(02, 2),
240(03, 3),
241(04, 4),
242(05, 5),
243(06, 6),
244(07, 1),
245(08, 2),
246(09, 3),
247(10, 4)
248INSERT INTO dbo.Model_Dealer (model_id,dealernazwa) VALUES
249(01, 'Wazniak'),
250(02, 'Osilek'),
251(03, 'Lasuch'),
252(04, 'Maruda'),
253(05, 'Ciamajda '),
254(06, 'Lalus'),
255(07, 'Spioch'),
256(08, 'Pracus'),
257(09, 'Smerfetka'),
258(10, 'Zgrywus')
259
260SELECT * FROM Samochod_Dealer
261SELECT * FROM Model
262SELECT * FROM Samochod_osobowy
263SELECT * FROM Samochod
264SELECT * FROM Sprzedaz
265SELECT * FROM Klient
266SELECT * FROM Model_TypSilnika
267SELECT * FROM Typ_silnika
268SELECT * FROM Dealer
269SELECT * FROM Samochod_Ciezarowy
270SELECT * FROM Dodatkowe_wyposazenie
271SELECT * FROM Model_Dealer
272SELECT * FROM Marka
273SELECT * FROM Samochod_DodatkoweWyposazenie
274
275
276
277
278
279
280
281-- PORCEDURA WSTAWIJACA DANE
282CREATE PROCEDURE wstawianie_danych_Tesla_Marka
283 @nazwa VARCHAR(50),
284 @start_dzialanosci DATE
285AS
286BEGIN
287INSERT INTO Marka
288(
289nazwa,
290start_dzialanosci
291)
292VALUES
293(@nazwa, @start_dzialanosci)
294END;
295--WYWOŁANIE PROCEDURY WSTAWIAJĄCEJ DANE TESLA
296GO
297DECLARE @name varchar(50)= 'Tesla'
298DECLARE @begin DATE = '2003'
299EXECUTE wstawianie_danych_Tesla2_Marka
300 @name,
301 @begin
302SELECT * FROM Marka
303
304
305
306-- PORCEDURA USUWAJĄCA DANE
307CREATE PROCEDURE usuwanie_danych_Tesla_Marka
308 @nazwa VARCHAR(50),
309 @start_dzialanosci DATE
310AS
311BEGIN
312DELETE FROM Marka
313WHERE nazwa = @nazwa
314END;
315GO
316--WYWOŁANIE PROCEDURY USUWAJĄCEJ DANE TESLA
317DECLARE @name varchar(50)= 'Tesla'
318DECLARE @begin DATE = '2003'
319EXECUTE usuwanie_danych_Tesla_Marka
320 @name,
321 @begin
322SELECT * FROM Marka
323
324
325-- PORCEDURA UPDATE DANE
326CREATE PROCEDURE update_data_Klient
327@imie VARCHAR(50),
328@ID int
329AS
330BEGIN
331UPDATE Klient
332SET imie = @imie
333where ID = @ID
334END;
335GO
336--WYWOŁANIE PROCEDURY UPDATE DANE
337DECLARE @imie VARCHAR(50) = 'Dominik'
338DECLARE @ID int = 1
339EXECUTE update_data_Klient
340@imie,
341@ID
342SELECT * FROM Klient
343
344
345-- PORCEDURA SPRZEDAJĄCA SAMOCHÓD
346CREATE PROCEDURE sold_this_car
347@VIN varchar(50)
348AS
349BEGIN
350DELETE FROM Sprzedaz
351WHERE @VIN = Sam_VIN
352END;
353GO
354--WYWOŁANIE PORCEDURY SPRZEDAJĄCA SAMOCHÓD
355DECLARE @VIN varchar(50) = '2FMGK5C88EBD11829'
356EXECUTE sold_this_car
357@VIN
358SELECT * FROM Sprzedaz
359
360
361CREATE PROCEDURE reportproc
362 @Klient_ID INT,
363 @imie VARCHAR(50) OUTPUT,
364 @zakupionedobra INT OUTPUT
365AS
366BEGIN
367SELECT
368@imie = Klient.imie,
369@zakupionedobra = COUNT(Sprzedaz.Klient_ID)
370FROM Sprzedaz LEFT OUTER JOIN Klient
371ON Sprzedaz.klient_ID = @Klient_ID
372GROUP BY Klient.imie
373END;
374GO
375
376
377DECLARE @iloscsprzedanychaut INT;
378DECLARE @daneklienta VARCHAR(50)
379EXECUTE reportproc
380 6,
381 @daneklienta OUTPUT,
382 @iloscsprzedanychaut OUTPUT;
383PRINT @iloscsprzedanychaut
384PRINT @daneklienta;
385
386
387CREATE TRIGGER trigeradminondata225
388
389ON Klient
390INSTEAD OF INSERT, UPDATE
391AS
392PRINT 'User !!! ' + USER_NAME() + ' try to add something. Sent report to operation chef !!!'
393GO
394
395UPDATE Klient
396SET imie='Piotrowicz'
397INSERT INTO dbo.Klient (imie,nazwisko,nr_telefon) VALUES
398('Nowak2', 'Jankowiak2', 125485225)
399SELECT * FROM Klient