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