AUTORENT andmebaas / SQL Server – Timofei Aaron Topolja portfoolio

Создание таблиц

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 (7,2)
)
create table klient
(
klientId int PRIMARY KEY identity(1,1,),
kliendiNimi varchar(50),
telefon varchar(20),
aadress varchar(50),
soiduKogemus varchar(30)
);
 
create table redniLeping
(
lepingId int PRIMARY KEY identity(1,1,),
rendiAlgus date,
klientId int,
regNumber char(6),
rendiKestvus int,
hindKokku decimal(5,2),
tootajaId int
);
 
create table tootaja2
(
tootajaId int PRIMARY KEY identity(1,1,),
tootajaNimi varchar(50),
ametiId int
);
 
ALTER TABLE rednileping
ADD FOREIGN KEY (klientId) REFERENCES klient(klientId);
ALTER TABLE rednileping
ADD FOREIGN KEY (regNumber) REFERENCES auto(regNumber);
ALTER TABLE rednileping
ADD FOREIGN KEY (tootajaId) REFERENCES tootaja2(tootajaId);
 
create table amet
(
ametId int PRIMARY KEY AUTO_INCREMENT,
amet varchar(50),
keskminePalk decimal (7,2)
);
 
ALTER TABLE rednileping
add ametId int;
ALTER TABLE rednileping
ADD FOREIGN KEY (ametId) REFERENCES amet(ametId);
create table mark
(
markId int primary key identity(1,1),
autoMark varchar(50)
)
create table kaigukast
(
kaigukastId int primary key identity(1,1),
kaigukast varchar(50)
)
ALTER TABLE auto
ADD FOREIGN KEY (kaigukastId) REFERENCES kaigukast(kaigukastId);

Задания SELECT запросы

#Покажи в какой машине - какая коробка передач.
 
select auto.regNumber, kaigukast.kaigukast from auto, kaigukast
where kaigukast.kaigukastID=auto.kaigukastID

#Покажи в какой машине - какая марка машины, используй INNER JOIN
 
SELECT auto.regNumber, mark.autoMark from auto
INNER JOIN mark ON mark.markID=auto.markID

#Отобрази по каждому клиенту все договора аренды машин и машины, которые были взяты в арендую
 
SELECT klient.kliendiNimi, rednileping.lepingId, auto.regNumber from rednileping
INNER JOIN klient on rednileping.klientId=klient.klientId
INNER JOIN auto on rednileping.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, tootaja2.tootajaNimi from auto, tootaja2, rednileping
where tootaja2.tootajaId=rednileping.tootajaId AND rednileping.regNumber=auto.regNumber

#Отобрази какие машины - какой работник отдал в аренду в выбранный промежуток времени.
 
select auto.regNumber, tootaja2.tootajaNimi from auto, tootaja2, rednileping
where tootaja2.tootajaId=rednileping.tootajaId AND rednileping.regNumber=auto.regNumber and (rednileping.rendiAlgus BETWEEN '2022-01-10' and '2022-07-12')

#Найди общее количество машин отданных в аренду в определенный промежуток времени и их суммарную стоимость.
 
select count(rednileping.regNumber) as autoKogus, sum(rednileping.hindKokku) as hindKokku from rednileping
where (rednileping.rendiAlgus BETWEEN '2022-05-10' and '2022-05-30')

#Найди цена проданных машин за определённый промежуток времени
 
select avg(rednileping.hindKokku) as keskmineHind from rednileping
where (rednileping.rendiAlgus BETWEEN '2022-05-10' and '2022-05-30')

Добавление пользователя

Добавить пользователя tootaja, с паролем 123456 и правами просмотра и добавления записей в таблицу Rendileping и правом просмотра остальных таблиц. Проверь права пользователя.

Процедуры

Составь процедуру для добавления новых марок машин и новых клиентов.

create procedure uusAutoMarkJaUusKlient
 
@uusAutoMark varchar (50),
@uusKlient varchar (50)
as
begin
INSERT INTO mark(autoMark)
VALUES (@uusAutoMark);
INSERT INTO klient(kliendiNimi)
VALUES (@uusKlient);
end;
#проверка
execute uusAutoMarkJaUusKlient @uusAutoMark='Mazda',@uusKlient='Boris';

Составь процедуру для удаления договора по его id.

create procedure rendilepinguKustutamine
 
@id int
as
begin
delete from rednileping
where lepingId=@id;
select * from rednileping;
end;
#проверка
execute rendilepinguKustutamine @id=4;

Свою процедуру на действие, отличное от ранее созданных.
‘Просмотреть все договора с определённого по определённый срок.’

create procedure lepinguVaatamine
 
@millest date,
@milleni date
as
begin
select * from rednileping
where (rendiAlgus BETWEEN @millest and @milleni);
end;
#проверка
execute lepinguVaatamine @millest='2022-05-03',@milleni='2022-05-19';