AUTORENT andmebaas / ülesanded

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

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

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

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

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

Create Table klient(
klientiID int not null Primary key IDENTITY(1,1),
kliendiNimi varchar(50),
telefon varchar(20),
aadress varchar(50),
soiduKogemus varchar(30),
)

Minu tabel:

Create table amet(
ametiID int not null Primary key IDENTITY(1,1),
ametinimi varchar(50),
)
SELECT * FROM amet;

Create table tootaja (
totajaID int not null Primary key IDENTITY(1,1),
tootajaNimi varchar (50),
ametiId int,
FOREIGN KEY (ametiId) REFERENCES amet(ametiId),
)

Create Table RendiLeping(
lepingID int not null Primary key IDENTITY(1,1),
rendiAlgus date,
rendiLopp date,
klientiID int,
regNumber
char(6),
rendikestvus int,
hindKokku decimal(5,2),
tootajaId int,	
FOREIGN KEY (klientiId) REFERENCES klient(klientiId),
FOREIGN KEY (tootajaId) REFERENCES tootaja(totajaID),
Foreign Key (regNumber) References auto(regNumber),
)
SELECT * FROM RendiLeping;

INSERT.

INSERT INTO auto (regNumber, markID, varv, v_aasta,kaigukastID,km)
VALUES ('AAA777',1,'Must',2005,1,5000); 
INSERT INTO auto (regNumber, markID, varv, v_aasta,kaigukastID,km)
VALUES ('BBB777',2,'Valge',2002,2,2500); 
INSERT INTO auto (regNumber, markID, varv, v_aasta,kaigukastID,km)
VALUES ('CCC777',2,'Punane',1995,2,500); 
INSERT INTO auto (regNumber, markID, varv, v_aasta,kaigukastID,km)
VALUES ('DDD777',1,'Rohelene',2015,1,1500); 
INSERT INTO auto (regNumber, markID, varv, v_aasta,kaigukastID,km)
VALUES ('GGG777',3,'Hele',2025,1,1000); 
SELECT * FROM auto;

INSERT INTO RendiLeping(rendiAlgus, rendiLopp, klientiID, regNumber, rendikestvus, hindKokku, tootajaId)
VALUES ('2022-12-20', '2022-10-02', 1, 'GGG777', 10, 120, 1);
INSERT INTO RendiLeping(rendiAlgus, rendiLopp, klientiID, regNumber, rendikestvus, hindKokku, tootajaId)
VALUES ('2022-12-21', '2022-10-02', 3, 'AAA77', 10, 120, 2);
INSERT INTO RendiLeping(rendiAlgus, rendiLopp, klientiID, regNumber, rendikestvus, hindKokku, tootajaId)
VALUES ('2022-12-22', '2022-10-02', 2, 'BBB777', 10, 120, 3);
INSERT INTO RendiLeping(rendiAlgus, rendiLopp, klientiID, regNumber, rendikestvus, hindKokku, tootajaId)
VALUES ('2022-12-23', '2022-10-02', 4, 'CCC777', 10, 120, 4);
INSERT INTO RendiLeping(rendiAlgus, rendiLopp, klientiID, regNumber, rendikestvus, hindKokku, tootajaId)
VALUES ('2022-12-24', '2022-10-02', 5, 'DDD777', 10, 120, 5);


INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
Values('Artem','5533226655','Kodus','Hea');
INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
Values('Daniil','554856416','Kodus','Hea');
INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
Values('Ekaterina','58964353134','Kodus','Hea');
INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
Values('Andrey','585624314','Kodus','Hea');
INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
Values('Semen','5222222','Kodus','Hea');

Insert Into tootaja(tootajaNimi,ametiId)
Values('Vlad',1);
Insert Into tootaja(tootajaNimi,ametiId)
Values('Andrey',2);
Insert Into tootaja(tootajaNimi,ametiId)
Values('Marija',3);
Insert Into tootaja(tootajaNimi,ametiId)
Values('Oksana',4);
Insert Into tootaja(tootajaNimi,ametiId)
Values('Vlad',5);

Insert Into amet(ametinimi)
Values('Driver')
Insert Into amet(ametinimi)
Values('Driver')
Insert Into amet(ametinimi)
Values('Driver')
Insert Into amet(ametinimi)
Values('Driver')
Insert Into amet(ametinimi)
Values('Driver')

Задания 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, rendileping.lepingId, auto.regNumber
FROM rendileping
INNER JOIN klient ON rendileping.klientiId=klient.klientiId
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.totajaId=rendileping.tootajaId and rendileping.regNumber=auto.regNumber


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

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

Найди общее количество машин отданных в аренду в определенный промежуток времени и их суммарную стоимость.
--Найди общее количество машин отданных в аренду в определенный промежуток времени и их суммарную стоимость.
SELECT COUNT(auto.autoId) AS kogus, SUM(RendiLeping.hindKokku) AS summa
FROM auto, RendiLeping
WHERE RendiLeping.lepingID = auto.autoId
AND RendiLeping.rendiAlgus BETWEEN '2021-01-10' AND '2023-03-12'

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

--Посчитать всех пользователей с отзывом Hea
SELECT COUNT(klient.klientiID) AS kogus
FROM klient
WHERE klient.soiduKogemus = 'Hea'

Пользователи.
-- Предоставление прав доступа к базе данных
GRANT SELECT, INSERT ON [RendiLeping] TO [tootaja];

Процедуры.
  1. Составь процедуру для добавления новых марок машин и новых клиентов.
  2. Составь процедуру для удаления договора по его id.
  3. Свою процедуру на действие, отличное от ранее созданных.

1.

CREATE PROCEDURE lisamark
    @markName varchar(50)
AS
BEGIN
    INSERT INTO mark (autoMark)
    VALUES (@markName);

    SELECT * FROM mark;
END;
 
Exec lisamark @markName='Audi'

go
create procedure lisakasutaja
@firstName varchar(50),
    @email varchar(100),
    @phoneNumber varchar(20),
	@soiduKogemus varchar(20)
As
Begin
INSERT INTO klient (kliendiNimi,telefon,aadress,soiduKogemus)
    VALUES (@firstName,@phoneNumber  , @email, @soiduKogemus);
Select * from klient;
End
 
Exec lisakasutaja @firstName='Grisha',@phoneNumber='55555555'  , @email='Grisha@gmail.com', @soiduKogemus='Ei ole hea'

2.

CREATE PROCEDURE lepingeemaldus2
    @mlepingID INT
AS
BEGIN
SELECT * FROM RendiLeping; 
DELETE FROM RendiLeping
WHERE lepingID = @mlepingID; 
SELECT * FROM RendiLeping; 
END

3.

CREATE PROCEDURE ShowAllCars
AS
BEGIN
SELECT regNumber, markID, varv, v_aasta, kaigukastID, km
FROM auto;
END;

Exec ShowAllCars