CREATE TABLE auto(
autoID int not null Primary key IDENTITY(1,1),
regNumber
char(6) UNIQUE,
markID int,
varv varchar(20),
v_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
);
select * from mark

INSERT INTO mark(autoMark)
VALUES ('Ziguli');
INSERT INTO mark(autoMark)
VALUES ('Lambordzini');
INSERT INTO mark(autoMark)
VALUES ('BMW');
SELECT * FROM mark;

CREATE TABLE kaigukast(
kaigukastID int not null Primary key IDENTITY(1,1),
kaigukast varchar(30) UNIQUE
);
INSERT INTO kaigukast(kaigukast)
VALUES ('Automaat');
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);

create table klient(
klientID int not null Primary key IDENTITY(1,1),
kliendiNimi varchar(50),
telefon varchar(20),
aadress varchar(50),
soiduKogemus varchar(30)
);
select * from klient

create table amet(
ametID int not null Primary key IDENTITY(1,1),
ametiNimi varchar(30),
);
select * from amet

INSERT INTO amet(ametiNimi)
VALUES ('Myyja');
INSERT INTO amet(ametiNimi)
VALUES ('Esimees');
INSERT INTO amet(ametiNimi)
VALUES ('Konsultant');
select * from amet

create table tootaja(
tootajaID int not null Primary key IDENTITY(1,1),
tootajaNimi varchar(50),
ametID int FOREIGN KEY REFERENCES amet(ametID)
);
select * from tootaja

create table rendiLeping(
lepingID int not null Primary key IDENTITY(1,1),
rendiAlgus date,
rendiLopp date,
klientID int FOREIGN KEY REFERENCES klient(klientID),
regNumber char(6) FOREIGN KEY REFERENCES auto(regNumber),
rendiKestvus int,
hindKokku decimal(5,2),
tootajaID int FOREIGN KEY REFERENCES tootaja(tootajaID)
);
select * from rendiLeping

insert into auto(regNumber,markID,varv,v_aasta,kaigukastID,km)
values('777AAA',3,'sinine','1999',1,'7500')
insert into auto(regNumber,markID,varv,v_aasta,kaigukastID,km)
values('650ABA',2,'punane','2023',1,'50')
insert into auto(regNumber,markID,varv,v_aasta,kaigukastID,km)
values('999KLM',3,'must','2013',2,'8500')
insert into auto(regNumber,markID,varv,v_aasta,kaigukastID,km)
values('333MMM',1,'kolane','2006',1,'9990')

insert into klient(kliendiNimi, telefon, aadress,soiduKogemus)
values ('Matvei','55530826','Mustamäe tee 200','Meeldis')
insert into klient(kliendiNimi, telefon, aadress,soiduKogemus)
values ('Yaroslav','56530826','Siili 6','Ei meeldinud')
insert into klient(kliendiNimi, telefon, aadress,soiduKogemus)
values ('Denis','56530726','Aiandi 15','Meeldis')

insert into tootaja (tootajaNimi, ametID)
values ('Pavel', 1)
insert into tootaja (tootajaNimi, ametID)
values ('Aleksander', 3)
insert into tootaja (tootajaNimi, ametID)
values ('Darja', 2)

insert into rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
values ('2023-05-26','2023-06-26',1,'777AAA',30,500,2)
insert into rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
values ('2023-06-15','2023-06-20',2,'650ABA',15,750,3)
insert into rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
values ('2023-06-20','2023-06-22',3,'333MMM',2,50,1)

select * from auto, mark, kaigukast
where mark.markID=auto.markID and kaigukast.kaigukastID=auto.kaigukastID
---или 
select * from auto
INNER JOIN mark ON mark.markID=auto.markID
INNER JOIN kaigukast ON kaigukast.kaigukastID=auto.kaigukastID

SELECT auto.regNumber, kaigukast.kaigukast
FROM auto, kaigukast
Where kaigukast.kaigukastID=auto.kaigukastID
---или
SELECT auto.regNumber, mark.autoMark
FROM auto
INNER JOIN mark ON mark.markID=auto.markID

SELECT klient.kliendiNimi, rendileping.lepingId, auto.regNumber
FROM rendileping
INNER JOIN klient ON rendileping.klientId=klient.klientId
INNER JOIN auto ON rendileping.regNumber=auto.regNumber

SELECT kaigukast.kaigukast, Count(auto.autoId) AS kogus
FROM auto, kaigukast
Where auto.kaigukastId=kaigukast.kaigukastId
GROUP by  kaigukast.kaigukast

SELECT auto.regNumber, tootaja.tootajaNimi 
from auto, tootaja, rendileping
where tootaja.tootajaId=rendileping.tootajaId and rendileping.regNumber=auto.regNumber

SELECT auto.regNumber, tootaja.tootajaNimi 
from auto, tootaja, rendileping
where tootaja.tootajaId=rendileping.tootajaId and rendileping.regNumber=auto.regNumber
and (rendileping.rendiAlgus between '2022-01-10' and '2022-03-12')

select count(lepingID) as lepingud, sum(hindKokku) as kõigestLepingutestTulek
from rendiLeping
where (rendileping.rendiAlgus between '2023-01-10' and '2024-01-10')

---выдаем права на просмотр и добавление
use AutorentKemppi;
grant select to tootaja;
grant insert on rendiLeping to tootaja;

select * from auto

insert into rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
values('2023-06-15','2023-06-20',2,'650ABA',15,'650',3)

drop table auto

update auto set varv= 'sinine'
WHERE autoID = 2;

---lisamine tabelisse mark
use AutorentKemppi;
go
create procedure lisaAutod
@uusautoMark varchar(30)
as
begin
insert into mark(autoMark)
values(@uusautoMark);
select * from mark;
end
 
exec lisaAutod @uusautoMark='Bentley'

---lisamine tabelisse klient
use AutorentKemppi;
go
create procedure lisaKliendid
@uusklient varchar(50),
@uustelefon varchar(20),
@uusaadress varchar(50),
@uusSoiduKogemus varchar(30)
as
begin
insert into klient(kliendiNimi, telefon, aadress,soiduKogemus)
values(@uusklient,@uustelefon,@uusaadress,@uusSoiduKogemus);
select * from klient;
end
 
exec lisaKliendid @uusklient = 'Denis' ,@uustelefon = '56827832' ,@uusaadress = 'Mustamäe tee 170' ,@uusSoiduKogemus = 'Meeldis' 

---kustuta lepping ID-ga
use AutorentKemppi;
go
create procedure LepinguKustutamine
@kustutaID int
as
begin
select * from rendiLeping;
 
delete from rendiLeping
where lepingID=@kustutaID;
 
select * from rendiLeping;
end
 
exec LepinguKustutamine @kustutaID=3;

---Näita tulemused mis on suurem kui
use AutorentKemppi;
go
create procedure suuremKui
@hind int
as
begin
select * from rendiLeping
where hindKokku > @hind;
end
exec suuremKui @hind = 250