Home » AUTORENT andmebaas

AUTORENT andmebaas

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

Создал таблицу auto

1
2
3
4
5
6
CREATE TABLE mark(
markID int not null Primary key IDENTITY(1,1),
autoMark varchar(30) UNIQUE
);
 
SELECT * FROM mark;

Создал таблицу mark

1
2
3
4
5
6
7
INSERT INTO mark(autoMark)
VALUES ('Ziguli');
INSERT INTO mark(autoMark)
VALUES ('Lambordzini');
INSERT INTO mark(autoMark)
VALUES ('BMW');
SELECT * FROM mark;

Заполнил таблицу mark

1
2
3
4
5
6
7
8
9
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;

Создал таблицу kaigukast и заполнил его

1
2
3
4
ALTER TABLE auto
ADD FOREIGN KEY (markID) REFERENCES mark(markID);
ALTER TABLE auto
ADD FOREIGN KEY (kaigukastID) REFERENCES kaigukast(kaigukastID);

Создал вторичные ключи для таблиц auto – mark и auto – kaigukast

1
2
3
4
5
6
7
CREATE TABLE klient(
klientID int not null PRIMARY KEY IDENTITY(1,1),
kliendiNimi varchar(50),
telefon varchar(20),
aadress varchar(50),
soiduKogemus varchar(30)
);

Создал таблицу klient

1
2
3
CREATE TABLE amet(
ametID int not null PRIMARY KEY IDENTITY(1,1),
ametNimi varchar(50))

Создал таблицу amet

1
2
3
4
5
6
CREATE TABLE tootaja(
tootajaID int not null PRIMARY KEY IDENTITY(1,1),
tootajaNimi varchar(50),
ametID int,
FOREIGN KEY (ametID) REFERENCES amet(ametID)
);

Создал таблицу tootaja с вторичным ключом amet(ametID)

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE rendLeping(
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)
);

Создал таблицу rendLeping с вторичными ключами klient(klientID), auto(regNumber), tootaja(tootajaID)

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

отображаю данные из трех таблиц с учетом вторичного ключа или связи между таблицами.

1
2
3
4
SELECT auto.regNumber, kaigukast.kaigukast
FROM auto, kaigukast
 
Where kaigukast.kaigukastID=auto.kaigukastID

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

1
2
3
4
SELECT auto.regNumber, mark.autoMark
FROM auto
 
INNER JOIN mark ON mark.markID=auto.markID

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

1
2
3
4
SELECT klient.kliendiNimi, rendLeping.lepingId, auto.regNumber
FROM rendLeping
INNER JOIN klient ON rendLeping.klientId=klient.klientId
INNER JOIN auto ON rendLeping.regNumber=auto.regNumber

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

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

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

1
2
3
SELECT auto.regNumber, tootaja.tootajaNimi
from auto, tootaja, rendLeping
where tootaja.tootajaId=rendLeping.tootajaId and rendLeping.regNumber=auto.regNumber

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

1
2
3
4
SELECT auto.regNumber, tootaja.tootajaNimi
from auto, tootaja, rendLeping
where tootaja.tootajaId=rendLeping.tootajaId and rendLeping.regNumber=auto.regNumber
and (rendLeping.rendiAlgus between '2022-01-10' and '2022-03-12')

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

Создал пользователя

1
2
grant select to tootaja;
grant insert on object::rendLeping to tootaja;
1
2
3
4
5
select * from amet;
insert into rendLeping(rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
values('2023-1-1','2023-2-2',1,'123aAA',1,1,1);
create table test(
test int);

проверяю права пользователя

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--
USE autorentDenisenko
go
CREATE PROCEDURE liza_mark_klient
@mark varchar(30),
@kliendinimi varchar(50),
@telefon varchar(20),
@aadress varchar(50),
@soiduKogemus varchar(30)
AS
BEGIN
INSERT INTO mark(autoMark)
VALUES (@mark);
SELECT * FROM mark;
INSERT INTO klient(kliendiNimi,telefon,aadress,soiduKogemus)
values(@kliendinimi,@telefon,@aadress,@soiduKogemus);
SELECT * FROM klient;
END
--
EXEC liza_mark_klient @mark='a',@kliendinimi='a',@telefon='a',@aadress='a',@soiduKogemus='a';

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

// клиент немного залагал

1
2
3
4
5
6
7
8
9
10
11
12
13
USE autorentDenisenko
go
CREATE PROCEDURE kustutamine_dogovor
@kustutaId int
AS
BEGIN
SELECT * FROM rendLeping ;
DELETE FROM rendLeping
WHERE lepingID=@kustutaId;
SELECT * FROM rendLeping;
END
--
EXEC kustutamine_dogovor 1

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

1
2
3
4
5
6
7
8
9
10
11
12
--
USE autorentDenisenko
go
CREATE PROCEDURE otsing_klient
@taht char(1)
AS
BEGIN
SELECT * FROM klient
WHERE kliendiNimi LIKE @taht+'%';
END
--
EXEC otsing_klient '1'

Создал свою процедуру для поиска клиентов по первой букве их имени.