Databases

CREATE TABLE auto(
	autoID int not null Primary Key identity(1,1),
	regNumber char(6) unique,
	markID int,
	varv varchar(20),
	aasta int,
	kaigukastID int,
	km decimal(6,2),
);
SELECT * from auto

CREATE table mark(
	markID int not null Primary key identity(1,1),
	autoMark varchar(30) unique
);
insert into mark(autoMark) values ('Ziguli');
insert into mark(autoMark) values ('Tesla');
insert into mark(autoMark) values ('Toyota');
insert into mark(autoMark) values ('BMW');
insert into mark(autoMark) values ('Ford');
Select * from mark

CREATE table kaigukast(
	kaigukastID int not null Primary key identity(1,1),
	kaigukast varchar(30) unique
);
insert into kaigukast(kaigukast) values ('Automaatne');
insert into kaigukast(kaigukast) values ('Manual');
Select * from kaigukast

ALter table auto
Add foreign key (markID) references mark(markID);

Alter table auto
Add foreign key (kaigukastID) references kaigukast(kaigukastID)

insert into auto(regNumber,markID,varv,aasta,kaigukastID,km) 
values ('164jhf',1,'sinine',2002,1,3000.0);
insert into auto(regNumber,markID,varv,aasta,kaigukastID,km) 
values ('777luv',2,'roheline',2000,2,6000.0);
insert into auto(regNumber,markID,varv,aasta,kaigukastID,km) 
values ('852asd',3,'punane',2010,2,3000.0);
insert into auto(regNumber,markID,varv,aasta,kaigukastID,km) 
values ('019bbd',4,'must',2018,1,9000.0);
insert into auto(regNumber,markID,varv,aasta,kaigukastID,km) 
values ('626gjh',5,'valge',2020,2,2500.0);
select * from auto

select * from auto, mark, kaigukast
where mark.markID=auto.markID and kaigukast.kaigukastID=auto.kaigukastID;

Create table klient(
	klientID int not null primary key identity(1,1),
	kliendiNimi varchar(50),
	telefon varchar(20),
	aadress varchar(50),
	soiduKogemus int
)
insert into klient(kliendiNimi,telefon,aadress,soiduKogemus)
values ('Jaan','58157210','Oismae tee 76',13);
insert into klient(kliendiNimi,telefon,aadress,soiduKogemus)
values ('Nastja','95174862','Mustamae tee 112',20);
insert into klient(kliendiNimi,telefon,aadress,soiduKogemus)
values ('Sofia','78943216','Aastangu tee',16);
insert into klient(kliendiNimi,telefon,aadress,soiduKogemus)
values ('Daniil','78453256','Lasnamae tee 76',32);
insert into klient(kliendiNimi,telefon,aadress,soiduKogemus)
values ('Erik','54963547','Mustamae tee 44',1);
select * from klient

create table tootaja(
	tootajaID int not null primary key identity(1,1),
	tootajaNimi varchar(50)
);
insert into tootaja(tootajaNimi) values('Nikita');
insert into tootaja(tootajaNimi) values('Mihkel');
insert into tootaja(tootajaNimi) values('Gianluka');
insert into tootaja(tootajaNimi) values('Kirill');
insert into tootaja(tootajaNimi) values('Martin');
select * from tootaja

create table rendiLeping(
	lepingID int not null primary key identity(1,1),
	rendiAlgus date,
	rendiLopp date,
	klientID int,
	regNumber int,
	rendiKestvus int,
	hindKokku decimal(5,2),
	tootajaID int,
	foreign key (klientID) references klient(klientID),
	foreign key (regNumber) references auto(autoID),
	foreign key (tootajaID) references tootaja(tootajaID),
);
insert into rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
values('2020-03-14','2023-03-14',2,12,3,400.0,2);
insert into rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
values('2015-06-21','2018-06-21',3,13,3,578.0,3);
insert into rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
values('2005-10-25','2009-10-25',4,14,4,100.0,4);
insert into rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
values('1999-04-07','2000-04-07',5,16,1,724.0,5);
select * from rendiLeping

create table reviews(
	reviewID int not null primary key identity(1,1),
	review varchar(100)
);
insert into reviews(review) values('Väga hea auto');
insert into reviews(review) values('Hea auto odavaga hinnage');
insert into reviews(review) values('Halb auto');
insert into reviews(review) values('Rikkade inimeste auto');
insert into reviews(review) values('See töötab');
select * from reviews

alter table auto
Add reviewID int

alter table auto
Add foreign key (reviewID) references reviews(reviewID)

Exec lisaMark @markNimi='lambo'

Create procedure lisaKlient @klientNimi varchar(20), @telefon varchar(20), @aadress varchar(50), @kogemus int
as
insert into klient(kliendiNimi,telefon,aadress,soiduKogemus)
values (@klientNimi,@telefon,@aadress,@kogemus);

exec lisaKlient @klientNimi='Sven',@telefon='78523258',@aadress='Kopli 75', @kogemus=4

Create procedure deleteLeping @lepingID int
as
Delete from rendiLeping
Where rendiLeping.lepingID=@lepingID;

exec deleteLeping @lepingID=11

create procedure agreements @kestvus int
as
Select * from rendiLeping
where rendiLeping.rendiKestvus>@kestvus

exec agreements @kestvus=2