AUTORENT ülesanne -

1.Ülesanne

2. САМОСТОЯТЕЛЬНОЕ СОСТАВЛЕНИЕ SQL ЗАПРОСОВ

2.1

CREATE TABLE klient(
klientID int not null Primary key IDENTITY(1,1),
kliendiNimi varchar(50),
telefon varchar(20),
aadress varchar(50),
soiduKogemus varchar(30)
);
INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
VALUES ('Matvei','telefon1','aadress1','soiduKogemus1');
INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
VALUES ('Anna','telefon2','aadress2','soiduKogemus2');
INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
VALUES ('Ilja','telefon3','aadress3','soiduKogemus3');
INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
VALUES ('Daniel','telefon4','aadress4','soiduKogemus4');
INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
VALUES ('Martin','telefon5','aadress5','soiduKogemus5');
SELECT * FROM klient

2.2

CREATE TABLE rendiLeping(
lepingID int not null Primary key IDENTITY(1,1),
rendiAlgus date,
rendiLopp date,
klientID int,
FOREIGN KEY (klientID) REFERENCES klient(klientID),
regNumber char(6),
FOREIGN KEY (regNumber) REFERENCES auto(regNumber),
rendiKestvus int,
hindKokku decimal(5,2),
tootajaID int,
FOREIGN KEY (tootajaID) REFERENCES tootaja(tootajaID)
);
INSERT INTO rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
VALUES ('2015-9-21','2017-9-21',1,'KNA216',3,143,1);
INSERT INTO rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
VALUES ('2015-12-16','2017-4-16',2,'MFA215',2,235,2);
INSERT INTO rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
VALUES ('2015-1-2','2017-12-26',3,'MSF153',3,342,3);
INSERT INTO rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
VALUES ('2015-2-25','2017-11-14',4,'KAF252',5,725,4);
INSERT INTO rendiLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
VALUES ('2015-5-12','2017-4-19',5,'AEG252',1,136,5);

2.3

CREATE TABLE tootaja(
tootajaID int not null Primary key IDENTITY(1,1),
tootajaNimi varchar(50),
ametID int,
FOREIGN KEY (ametID) REFERENCES amet(ametID),
);
INSERT INTO tootaja(tootajaNimi,ametID)
VALUES ('Maksim',1);
INSERT INTO tootaja(tootajaNimi,ametID)
VALUES ('Darja',2);
INSERT INTO tootaja(tootajaNimi,ametID)
VALUES ('Aleksandr',3);
INSERT INTO tootaja(tootajaNimi,ametID)
VALUES ('Pavel',2);
INSERT INTO tootaja(tootajaNimi,ametID)
VALUES ('Katerina',3);

2.4

CREATE TABLE amet(
ametID int not null Primary key IDENTITY(1,1),
amet varchar(30)
);
INSERT INTO amet(amet)
VALUES ('amet1');
INSERT INTO amet(amet)
VALUES ('amet2');
INSERT INTO amet(amet)
VALUES ('amet3');
SELECT * FROM amet

При помощи запросов Select выведи данные из связанных таблиц.

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 запросы.

4.1.

Покажи в какой машине - какая коробка передач.

SELECT auto.regNumber, kaigukast.kaigukast
FROM auto, kaigukast
Where kaigukast.kaigukastID=auto.kaigukastID

4.2.

Покажи в какой машине - какая марка машины, используй INNER JOIN

SELECT auto.regNumber, mark.autoMark
FROM auto
INNER JOIN mark ON mark.markID=auto.markID

4.3.

Отобрази по каждому клиенту все договора аренды машин и машины, которые были взяты в арендую

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

4.4.

Покажи сколько машин с автом коробкой, сколько с мануальной.

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

4.5.

Отобрази какие машины - какой работник отдал в аренду.

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

4.6.

Отобрази какие машины - какой работник отдал в аренду в выбранный промежуток времени.

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')

У меня нет аренд в это время(2022-01-10 and 2022-03-12)

4.7.

Найди общее количество машин отданных в аренду в определенный промежуток времени и их суммарную стоимость.

SELECT COUNT(auto.regNumber) as kogus,sum(rendileping.hindKokku) as summa
FROM auto,tootaja,rendileping
WHERE tootaja.tootajaID=rendileping.tootajaID and rendileping.regNumber=auto.regNumber

4.8.

свой запрос - текст, sql, ответ

SELECT auto.regNumber, auto.varv
FROM auto
WHERE auto.varv=auto.varv

Пользователи.

5.1.

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

GRANT SELECT TO tootaja
GRANT INSERT ON rendiLeping TO tootaja

Процедуры

6.1.

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

GO
Create Procedure addKlient_Mark
	@kliendiNimi VARCHAR(50),
    @telefon varchar(20),
	@aadres varchar(50),
    @soidukogemus varchar(30),
	@autoMark VARCHAR(30)
AS
BEGIN
INSERT INTO klient(kliendiNimi, telefon, aadress, soidukogemus) 
VALUES(@kliendiNimi, @telefon, @aadres, @soidukogemus)
INSERT INTO mark(autoMark) 
VALUES(@autoMark);
END
EXEC addKlient_Mark @kliendiNimi='Denis',@telefon='telefon6',@aadres='aadress6',@soidukogemus='soiduKogemus6',@autoMark='Opel';
Select * from klient
Select * from mark

6.2.

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

GO
Create Procedure delete_dogovor
@id int
AS
BEGIN
DELETE FROM rendiLeping WHERE lepingID=@id;
END
EXEC delete_dogovor @id = 1;

6.3.

Свою процедуру на действие, отличное от ранее созданных.

O
Create Procedure dobavlenie_stolbika
AS
BEGIN
ALTER TABLE tootaja
ADD AGE int;
END
EXEC dobavlenie_stolbika

CARRENT task