AUTORENT DATABASE / SQL SERVER

Создание таблиц и их привязка

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

CREATE TABLE klient(
klientID int not null primary key identity(1,1),
kliendiNimi varchar(50),
telefon varchar(20),
aadress varchar(50),
soiduKogemus varchar(30)
)

CREATE TABLE rendiLeping(
lepingID int not null primary key identity(1,1),
rendiAlgus date,
rendiLopp date,
klientID int,
regNumber varchar(6),
rendiKestvus int,
hindKokku decimal(5,2),
tootajaID int
)

CREATE TABLE tootaja(
tootajaID int not null primary key identity(1,1),
tootajaNimi varchar(50),
ametID int)

ALTER TABLE rendiLeping add FOREIGN KEY (klientID) REFERENCES klient(klientID);
ALTER TABLE rendiLeping add FOREIGN KEY (tootajaID) REFERENCES tootaja(tootajaID);
ALTER TABLE rendileping add FOREIGN KEY (regNumber) REFERENCES auto(regNumber);

CREATE TABLE Amet(
ametID int not null PRIMARY KEY identity(1,1),
ametNimi varchar(20))
ALTER TABLE tootaja add FOREIGN KEY (ametID) REFERENCES amet(ametID)

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

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-05-02' and '2022-05-12')

SELECT COUNT(auto.autoID) AS Autod, SUM(rendileping.hindKokku) AS Hind from rendiLeping, auto WHERE rendiAlgus between '2022-05-01' and '2022-10-01'

Задание создание процедур

Use autorentRozhkov;
GO
CREATE Procedure uusAutoMark
@autoMark varchar(30)
AS
INSERT INTO mark(autoMark)
VALUES (@autoMark);
 
SELECT * FROM mark;

use autorentRozhkov;
GO
CREATE PROCEDURE EsimeseTaht
@taht VARCHAR(1)
AS
BEGIN
SELECT * FROM mark
WHERE autoMark LIKE @taht + '%';
END;

use autorentRozhkov;
GO
CREATE PROCEDURE del
@deleteID int
AS
BEGIN
DELETE FROM mark WHERE markID=@deleteID;
SELECT * FROM mark;
END

use autorentRozhkov;
GO
CREATE PROCEDURE uuenda
@updateID int,
@uusMark NVARCHAR(20)
AS
BEGIN
UPDATE mark SET autoMark=@uusMark
WHERE markID=@updateID;
SELECT * FROM mark;
END

use autorentRozhkov;
GO
CREATE PROCEDURE kustutaLeping
@kustutaID int
AS
BEGIN
DELETE FROM rendiLeping WHERE lepingID=@kustutaID;
SELECT * FROM rendiLeping
END

use autorentRozhkov;
GO
CREATE PROCEDURE lisajauuendadaAmet
@lisaAmet varchar(30),
@uuendada varchar(30),
@id int
AS
BEGIN
INSERT INTO amet(ametNimi)
VALUES (@lisaAmet);
UPDATE amet SET ametNimi=@uuendada WHERE ametID=@id;
SELECT * FROM amet;
END

Задание создание пользователя и выдача ему прав

INSERT INTO rendileping (lepingID,rendiAlgus,rendiLopp,klientID,regNumber,rendiKestvus,hindKokku,tootajaID)
VALUES (‘5’, ‘2022-05-19′,’2022-05-20′,’3′,’321zxc’,’5′,’300′,’1′);