AUTORENT andmebaas / ülesanded

Работа в SQL Server

CREATE DATABASE AutoRent_Maksim_Dotskin


use AutoRent_Maksim_Dotskin;
go
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 (
  klientiID INT NOT NULL PRIMARY KEY,
  kliendiNimi VARCHAR(50),
  telefon VARCHAR(20),
  adress VARCHAR(50),
  soideKogemus VARCHAR(30)
);


CREATE TABLE rendiLeping (
  lepingID INT NOT NULL PRIMARY KEY,
  rendiAlgus DATE,
  rendiLopp DATE,
  klientiID INT,
  regNumber VARCHAR(6),
  rendiKestvus INT,
  hindKokku DECIMAL(5, 2),
  tootajaID INT,
  FOREIGN KEY (klientiID) REFERENCES klient(klientiID),
  FOREIGN KEY (tootajaID) REFERENCES tootaja(tootajaID)
);



CREATE TABLE tootaja (
  tootajaID INT NOT NULL PRIMARY KEY,
  tootajaNimi VARCHAR(50),
  ametiID INT
);

klient
ALTER TABLE rendiLeping
ADD FOREIGN KEY (klientiID) REFERENCES klient(klientiID);

tootaja
ALTER TABLE rendiLeping
ADD FOREIGN KEY (tootajaID) REFERENCES tootaja(tootajaID);

CREATE TABLE amet (
  ametiID INT NOT NULL PRIMARY KEY,
  ametiNimi VARCHAR(50)
);

ALTER TABLE tootaja
ADD FOREIGN KEY (ametiID) REFERENCES amet(ametiID);

use AutoRent_Maksim_Dotskin;
go



ALTER TABLE rendiLeping
ADD FOREIGN KEY (autoID) REFERENCES auto(autoID);

Заполнение таблиц


INSERT INTO klient (klientiID, kliendiNimi, telefon, adress, soideKogemus) VALUES
(1, 'Mari Mets', '51234567', 'Tallinn, Estonia', 'Kogenud'),
(2, 'Peeter Pärn', '56789123', 'Tartu, Estonia', 'Algaja'),
(3, 'Liisa Lõhmus', '51231234', 'Pärnu, Estonia', 'Kogenud'),
(4, 'Jaanika Jõesaar', '52345678', 'Viljandi, Estonia', 'Kogenud'),
(5, 'Tõnis Tamm', '53456789', 'Rakvere, Estonia', 'Algaja');


INSERT INTO rendiLeping (lepingID, rendiAlgus, rendiLopp, klientiID, regNumber, rendiKestvus, hindKokku, tootajaID) VALUES
(1, '2023-01-01', '2023-01-05', 1, '123ABC', 5, 150.00, 1),
(2, '2023-02-10', '2023-02-15', 2, '456DEF', 6, 180.00, 2),
(3, '2023-03-20', '2023-03-25', 3, '789GHI', 5, 150.00, 3),
(4, '2023-04-05', '2023-04-10', 4, 'ABC123', 6, 180.00, 1),
(5, '2023-05-15', '2023-05-20', 5, 'DEF456', 5, 150.00, 2);

INSERT INTO tootaja (tootajaID, tootajaNimi, ametiID) VALUES
(1, 'Andres Anderson', 1),
(2, 'Karin Karu', 2),
(3, 'Marko Mägi', 1),
(4, 'Laura Lepik', 2),
(5, 'Martin Mets', 1);

INSERT INTO amet (ametiID, ametiNimi) VALUES
(1, 'Mehaanik'),
(2, 'Klienditeenindaja');

INSERT INTO amet(ametiID,ametiNimi)
values(3,'Opetaja');
INSERT INTO amet(ametiID,ametiNimi)
values(4,'Doktor');
INSERT INTO amet(ametiID,ametiNimi)
values(5,'Juhataja');

select запросы

SELECT auto.regNumber, kaigukast.kaigukast
FROM auto, kaigukast

Покажи в какой машине - какая коробка передач.
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.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-01-10' and '2022-03-12')

Нет таких

Найди общее количество машин отданных в аренду в определенный промежуток времени и их суммарную стоимость.
SELECT COUNT(*) AS TotalCars, SUM(hindKokku) AS TotalCost
FROM rendiLeping
WHERE rendiAlgus >= '2023-01-01' AND rendiLopp <= '2023-12-31';

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

CREATE LOGIN tootaja WITH PASSWORD = 'StrongPassword123!';
CREATE USER tootaja FOR LOGIN tootaja;


GRANT SELECT, INSERT ON Rendileping TO tootaja;


GRANT SELECT ON klient TO tootaja;
GRANT SELECT ON tootaja TO tootaja;
GRANT SELECT ON mark TO tootaja;



EXECUTE AS USER = 'tootaja';

REVERT;

Проверка просмотра

добавление записей в таблицу Rendileping

проверка отсуствия права добавление записей в другие таблицы

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

1

use [AutoRent_Maksim_Dotskin];
go

CREATE PROCEDURE AddNewData
    @autoMark VARCHAR(30),
    @klientiNimi VARCHAR(50),
    @telefon VARCHAR(20),
    @adress VARCHAR(50),
    @soideKogemus VARCHAR(30)
AS
BEGIN
    SET NOCOUNT ON;


    INSERT INTO mark (autoMark)
    VALUES (@autoMark);

   
    DECLARE @markID INT;
    SET @markID = SCOPE_IDENTITY();

 
    INSERT INTO klient (klientiID, kliendiNimi, telefon, adress, soideKogemus)
    VALUES (@markID, @klientiNimi, @telefon, @adress, @soideKogemus);

 
    SELECT @markID AS NewMarkID, @markID AS NewClientID;
END

2 процедура

CREATE PROCEDURE DeleteRendiLepingById
    @lepingID INT
AS
BEGIN
    DELETE FROM rendiLeping
    WHERE lepingID = @lepingID
END

Вызов

EXEC DeleteRendiLepingById @lepingID = 123

3 процедура

процедура расставляет работников в алфавитном порядке 

CREATE PROCEDURE SortTootajaTableByName
AS
BEGIN
    SELECT *
    FROM tootaja
    ORDER BY tootajaNimi ASC
END

Вызов

EXEC SortTootajaTableByName

Leave a Reply

Sinu e-postiaadressi ei avaldata. Nõutavad väljad on tähistatud *-ga