--loo tabel kliendiAuto
use epood_Kaasik;
create table kliendiAuto (
id INT primary key,
first_name VARCHAR(255),
last_name VARCHAR(255),
email VARCHAR(255),
car_make VARCHAR(255),
car_model VARCHAR(255),
car_year VARCHAR(255),
car_price decimal(6,2)
);
-- Mine lehele https://www.mockaroo.com ja genereeri samad andmed 100tk ja impordi need oma andmebaasi.
use epood_Kaasik;
use epood_Kaasik;
Select * from kliendiAuto;
-- Muuda enda lisatud automargiks Zaporožets
use epood_Kaasik;
update kliendiAuto
set car_make='Zaporožets'
where first_name='Aleks' and last_name='Kaasik';
select * from kliendiAuto
where first_name='Aleks' and last_name='Kaasik';
-- Muuda kõik Cadillac margid Citroën’iks
use epood_Kaasik;
select * from kliendiAuto
where car_make = 'Cadillac';
update kliendiAuto
set car_make = 'Citroën'
where car_make = 'Cadillac';
select * from kliendiAuto
where car_make = 'Citroën';
-- Kustuta ära read, mille ID on 5, 10 ja 15
use epood_Kaasik;
select count(*) from kliendiAuto;
delete from kliendiAuto
where id = 5 or id = 10 or id = 15;
-- või where id in (5,10,15)
select count(*) from kliendiAuto;
-- Kuva read, kus on ainult ID, perenimi, automark ja aasta, sorteeri ID järgi kasvavalt
use epood_Kaasik;
select id, last_name, car_make, car_year from kliendiAuto;
-- Kuva 10 rida autosid, kus kliendi perenimi hakkab sama algustähega, mis sul, sorteeri autod aasta järgi kahanevalt
use epood_Kaasik;
select top 10 id, last_name, car_make, car_year from kliendiAuto
where left(last_name,1) = 'K'
order by car_year desc;
-- Kuva 2 vabalt valitud automarki sisaldavad read ja ületavad mingit summat
use epood_Kaasik;
select * from kliendiAuto
where car_make in ('Oldsmobile','Honda')
and car_price > 6000
use epood_Kaasik;
-- Mõtle välja oma tabeli, mida saab siduda kliendiAuto tabeliga.
create table AutoLinn(
id int primary key identity(1,1),
linn varchar(255)
);
--Kasuta https://www.mockaroo.com, et genereerida tabelisse andmed vähemalt 100tk
insert into AutoLinn(linn)
values
('Tallinn'),
('Tartu'),
('Parnu'),
('Narva');
-- Seosta tabelid oma vahel.
alter table kliendiAuto
add linn int;
alter table kliendiAuto
add FOREIGN KEY (id) REFERENCES AutoLinn(id)
--1. Tee kasutaja nimega direktor, kes saab luua trigereid (vähemalt 2) ning jälgida nende töö
(direktor näeb tabeli logi, aga ei saa logi tabelisse midagi lisada ning kustutada. Muud tabelid direktor ei näe).
-- loo triggerid
create trigger LogInsertKA
on KliendiAuto after insert
as begin
insert into LogAuto(toiming, aeg, autoAndmed, kasutaja)
values ('Kliendi - Uus andmed on lisatud: ',GETDATE(),' ', user );
end;
create trigger LogInsertAL
on AutoLinn after insert
as begin
insert into LogAuto(toiming, aeg, autoAndmed, kasutaja)
values ('Linn - Uus andmed on lisatud: ',GETDATE(),' ', user );
end;
create trigger LogDeleteKA
on KliendiAuto after delete
as begin
insert into LogAuto(toiming, aeg, autoAndmed, kasutaja)
values ('Kliendi - Andmed on kustutatud: : ',GETDATE(),' ', user );
end;
create trigger LogDeleteAL
on AutoLinn after delete
as begin
insert into LogAuto(toiming, aeg, autoAndmed, kasutaja)
values ('Linn - Andmed on kustutatud: ', GETDATE(),' ', user );
end;
-- direktor näeb tabeli logi, aga ei saa logi tabelisse midagi lisada ning kustutada. Muud tabelid direktor ei näe
grant select on dbo.LogAuto to direktorKaasik
--Trigeri tegevus määra iseseisvalt.
--Kontrolli kasutaja direktor tööd.
--2. Tee 2 protseduuri (funktsiooni), mis lihtsustavad ükskõik milline töö andmebaasis.
--1
create procedure KuvaIlusTeaveKliendis
AS
BEGIN
select ka.id, ka.first_name, ka.email, ka.car_make, ka.car_model, ka.car_year, ka.car_price, al.linn
from kliendiAuto ka inner join AutoLinn al
on ka.linn = al.id
END;
-- 2
create procedure What_mode_was_used_in_logs
@test CHAR
as
begin
DECLARE @mode varchar(32);
IF(lower(@test) = 'l')
begin
set @mode = '%Uus andmed on lisatud: %'
end
IF(lower(@test) = 'k')
begin
set @mode = '%Andmed on kustutatud: %'
end
IF(lower(@test) = 'u')
begin
set @mode = '%muudatud%'
end
select * from LogAuto where toiming like @mode;
end
