CREATE DATABASE A18_Auto_Plac;
GO

USE A18_Auto_Plac;

CREATE TABLE Proizvodjac( 
	ProizvodjacID INT PRIMARY KEY, 
	Naziv VARCHAR(255)
	);
CREATE TABLE Boja(
	BojaID INT PRIMARY KEY,
	Naziv VARCHAR(255),
	Opis VARCHAR(255)
	);
CREATE TABLE Oprema(
	OpremaID INT PRIMARY KEY,
	Naziv VARCHAR(255),
	Opis VARCHAR(255)
	);
CREATE TABLE Gorivo(
	GorivoID INT PRIMARY KEY,
	Naziv VARCHAR(255),
	Opis VARCHAR(255)
	);	
GO

CREATE TABLE Model(		
	ModelID INT PRIMARY KEY,
	Naziv VARCHAR(255),
	ProizvodjacID INT,
	CONSTRAINT fk_Model_proizv FOREIGN KEY (ProizvodjacID) REFERENCES Proizvodjac(ProizvodjacID)
	);
GO

CREATE TABLE Vozilo(		
	VoziloID INT PRIMARY KEY IDENTITY,
	Registracija VARCHAR(25),
	GodinaProizvodnje INT,
	PredjenoKM decimal(12,2),
	ModelID INT,
	BojaID INT,
	GorivoID INT,
	Cena decimal(12,2),
	CONSTRAINT fk_vozilo_model FOREIGN KEY (ModelID) REFERENCES Model(ModelID),
	CONSTRAINT fk_vozilo_boja FOREIGN KEY (BojaID) REFERENCES Boja(BojaID),
	CONSTRAINT fk_vozilo_gorivo FOREIGN KEY (GorivoID) REFERENCES Gorivo(GorivoID)
	);
GO

CREATE TABLE Auto_Oprema(
	VoziloID INT,
	OpremaID INT, 
	CONSTRAINT fk_vozilo_AO FOREIGN KEY (VoziloID) REFERENCES Vozilo(VoziloID),
	CONSTRAINT fk_OPREMA_AO FOREIGN KEY (OpremaID) REFERENCES Oprema(OpremaID),
	CONSTRAINT PK_AO PRIMARY KEY (VoziloID,OpremaID)
	);
GO	

INSERT INTO Gorivo(GorivoID,Naziv) VALUES
(2,'Benzin'), (1,'Evrodizel'), (3,'Auto gas'), (6,'Dizel'), (5,'Elektro');

INSERT INTO Boja(BojaID,Naziv) VALUES
(1, 'Smeđa'),(2, 'Crvena'),(3, 'Crna'),(4, 'Siva'),(5, 'Plava'),(6, 'Zelena'),(7, 'Žuta'),(8, 'Bordo'),(9, 'Bela'),(10, 'Orange'),(11, 'Zuta');


INSERT INTO Proizvodjac( ProizvodjacID, Naziv)
VALUES (1,'Audi'), (2, 'BMW'),(33, 'Volvo'), (4,'Ford'), (5,'Honda'),(6, 'Ferrari'),
(7,'Jeep'),(8, 'Citroen'), (9,'Lada'), (17,'Mazda'),
(24, 'Opel'), (35, 'Volkswagen'),(37, 'Yugo');

INSERT INTO Proizvodjac( ProizvodjacID, Naziv)
VALUES (3, 'Renault');

INSERT INTO Model(ModelID,Naziv,ProizvodjacID) VALUES
(10, 'Jetta', 35), (81, 'Passat', 35), (3, 'Beetle', 35),(4, 'Sharan', 35),(5, 'Golf', 35),(6, 'Polo', 35),(7, 'Caddy', 35),
(8, 'Astra', 24), (9, 'Corsa', 24),(11, 'Vectra', 24),(12, 'Caddet', 24),
(13, 'Yugo Sana', 37),(2, 'Yugo 45', 37),(15, 'Yugo 55', 37),
( 23, 'Focus',4 ),( 27, 'Taurus',4 ),( 28, 'Fiesta',4 ),
( 29, 'Mondeo',4 ),( 31, 'Sierra',4 ),( 33, 'Escort',4 ),
( 107, 'Jazz',5 ),(111,'Volvo Buses',33),(666, 'Ferrari Enzo', 6),(888, 'Citroen C3', 8),(333, 'Audi Q7', 1), (444, 'Audi A2', 1),

( 113, 'Renegade',7 ),( 131, 'Cherokee',7 ),( 47, 'Wrangler',7 ),( 48, 'Grand Cherokee',7 ),( 49, 'Compass',7 ),( 50, 'Commander',7 ),
( 83, 'Niva',9 ),( 58, 'Kalina',9 ),( 59, 'Vesta',9 ),( 60, 'Largus',9 ),( 61, 'Samara',9 ),

( 121, 'Mazda 323',17),( 134, 'Mazda RX-8',17),( 66, 'Mazda 3 Sport',17);

INSERT INTO Model(ModelID,Naziv,ProizvodjacID) VALUES (1, 'Kadjar', 3);

INSERT INTO Oprema(OpremaID,Naziv) VALUES
(1, 'Parking senzor'),(2,'kamera za voznju u rikverc'),(3,'panoramski krov'),(4,'head-up displej'),(5,'sportski branici');

INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('WP-069-VG', 2006, 22394.88,81,9,1,15072.38),	
		('MF-308-CS', 1998, 138470.31,121,10,2,16711.86),
		('XS-403-XH', 2010, 134160.05,113,2,5,15824.41),
		('IE-388-UA', 2002, 177556.65,83,11,1,12046.90),
		('AR-752-VF', 1995, 146662.65,31,3,5,5708.60),
		('DW-759-VV', 2006, 122610.74,83,2,3,12059.93),
		('LE-836-YZ', 2008, 33164.14,134,11,6,18308.49),
		('WT-238-FP', 2011, 88860.59,66,9,1,9959.20);
		
INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('SD-169-VA', 2005, 32394.88,107,9,1,55089.67),		
		('CV-269-AG', 2005, 42394.23,107,2,1,15089.67),	
		('ER-369-VS', 2004, 24394.34,107,3,1,15089.67),	
		('RE-469-SG', 2004, 90394.45,107,4,1,15089.67),	
		('DF-569-VD', 2003, 100394.56,107,3,1,15089.67),	
		('FD-669-DG', 2003, 200394.67,107,4,1,15089.67),	
		('DC-769-VF', 2002, 222294.67,107,5,1,15089.67),	
		('CX-869-FG', 2001, 332394.78,107,6,1,15089.67),	
		('SS-969-VH', 2000, 122394.89,107,7,1,5089.67);	

INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('VF-158-NQ', 2013, 232442.94069,333,9,1,23000),		
		('NG-629-TD', 2013, 20463.8868,333,2,1,44000),	
		('VE-327-AL', 1996, 275231.03927,333,3,1,33000.34);

INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('AA-111-VA', 2005, 22392.18,81,9,1,15089.67),		
		('SS-222-AG', 2005, 12321.23,81,2,1,15089.67),	
		('DD-333-VS', 2004, 24392.33,81,3,1,15089.67),	
		('FF-444-SG', 2004, 10391.45,81,4,1,15089.67),	
		('GG-555-VD', 2003, 35394.56,81,3,1,15089.67),	
		('HH-666-DG', 2003, 63394.67,81,4,1,15089.67),	
		('JJ-777-VF', 2002, 61294.67,81,5,1,15089.67),	
		('KK-888-FG', 2001, 45394.78,81,6,1,15089.67),	
		('LL-999-VH', 2000, 56394.89,81,7,1,5089.67);	
	
INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('QW-123-VA', 2005, 13114.18,10,9,1,5089.67),		
		('WE-234-AG', 2005, 21324.23,10,2,1,15089.67),	
		('ER-345-VS', 2004, 33914.33,10,3,1,15089.67),	
		('RT-456-SG', 2004, 43194.45,10,4,1,15089.67),	
		('TZ-569-VD', 2003, 50314.56,10,3,1,15089.67),	
		('ZU-678-DG', 2003, 60341.67,10,4,1,15089.67),	
		('UI-789-VF', 2002, 62124.67,10,5,1,15089.67),	
		('IO-890-FG', 2001, 42314.78,10,6,1,65089.67),	
		('OP-012-VH', 2000, 52134.89,10,7,1,9089.67);	
		
INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('VV-129-VA', 2009, 13114.18,3,9,1,50819.67),		
		('BB-294-AG', 2009, 21324.23,3,2,1,51089.67),	
		('NN-349-VS', 2008, 33914.33,3,3,1,50189.67),	
		('MM-496-SG', 2007, 43194.45,3,4,1,51089.67),	
		('LL-969-VD', 2006, 50314.56,3,3,1,15089.67),	
		('JJ-698-DG', 2005, 60341.67,3,4,1,51089.67),	
		('YY-989-VF', 2004, 62124.67,3,5,1,10089.67),	
		('XX-899-FG', 2003, 42314.78,3,6,1,15089.67),	
		('CC-912-VH', 2002, 52134.89,3,7,1,9089.67);	
		
INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('DF-129-VA', 2019, 83114.18,4,9,1,59819.67),		
		('DF-294-AG', 2019, 81324.23,4,2,1,59089.67),	
		('DF-349-VS', 2018, 38914.33,4,3,1,59189.67),	
		('DF-496-SG', 2017, 48194.45,4,4,1,59089.67),	
		('DF-969-VD', 2016, 58314.56,5,3,1,19089.67),	
		('DF-698-DG', 2015, 68341.67,5,4,1,59089.67),	
		('DF-989-VF', 2014, 82124.67,5,5,1,19089.67),	
		('DF-899-FG', 2013, 82314.78,5,6,1,19089.67),	
		('DF-912-VH', 2012, 58134.89,5,7,1,89089.67);									
		
INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('DF-189-VA', 2019, 33114.18,8,9,1,19819.67),		
		('DF-894-AG', 2019, 31324.23,8,2,1,52089.67),	
		('DF-849-VS', 2018, 33914.33,8,3,1,52189.67),	
		('DF-896-SG', 2017, 43194.45,8,4,1,19089.67),	
		('DF-869-VD', 2016, 53314.56,8,3,1,19089.67),	
		('DF-898-DG', 2015, 38341.67,8,4,1,52089.67),	
		('DF-889-VF', 2014, 83124.67,8,5,1,12089.67),	
		('DF-889-FG', 2013, 32314.78,8,6,1,12089.67),	
		('DF-982-VH', 2012, 38134.89,8,7,1,19089.67);			
		
INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('DA-109-PA', 2013, 53114.18,9,9,1,52819.67),		
		('DA-094-AP', 2013, 51324.23,9,2,1,19089.67),	
		('DA-309-VP', 2014, 58914.33,9,3,1,19189.67),	
		('DA-490-SP', 2014, 58194.45,9,4,1,52089.67),	
		('DA-960-VP', 2013, 58314.56,9,3,1,19089.67),	
		('DA-098-DP', 2014, 58341.67,9,4,1,52089.67),	
		('DA-909-VP', 2013, 52124.67,9,5,1,12089.67),	
		('DA-809-PG', 2014, 52314.78,9,6,1,19089.67),	
		('DA-012-PH', 2013, 58134.89,9,7,1,19089.67);			
		
INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('AA-109-PA', 2004, 93114.18,11,9,1,12819.67),		
		('AA-094-AP', 2005, 91324.23,11,2,1,11089.67),	
		('AA-309-VP', 2004, 98914.33,11,3,1,11189.67),	
		('AA-490-SP', 2003, 98194.45,11,4,1,12089.67),	
		('AA-960-VP', 2002, 98314.56,11,3,1,19089.67),	
		('AA-098-DP', 2001, 98341.67,12,4,1,12089.67),	
		('AA-909-VP', 2002, 92124.67,12,5,1,11089.67),	
		('AA-809-PG', 2003, 92314.78,12,6,1,19089.67),	
		('AA-012-PH', 2001, 98134.89,12,7,1,19089.67);			
		
		
		
INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES

('PO-909-VP', 2012, 19805.47,1,5,1,19805.47),	
('PA-902-VP', 2012, 19805.47,1,5,1,19805.47),
('PX-903-VP', 2012, 19805.47,1,5,1,19805.47),
('DZ-098-DP', 2013, 58341.67,1,4,1,19815.55),
('DC-091-DP', 2013, 58341.67,1,4,1,19815.55),
('ED-292-AP', 2014, 58341.67,1,4,1,19822.41),
('CC-598-CP', 2015, 58341.67,1,4,1,10223.49);


INSERT INTO Vozilo(Registracija,GodinaProizvodnje,PredjenoKM,ModelID,BojaID,GorivoID,Cena) VALUES
		('NS-165-MM', 2020, 10000,444,9,1,23000),		
		('NS-555-MM', 2021, 12000,666,2,1,49000),	
		('NS-234-NS', 2021, 100,111,2,1,25000.34),
		('NS-555-NS', 2021, 100000,888,2,1,35000);