1. Päringud

1.Koosta päring, mis kuvab kõik tooted koos nende kategooriate nimedega.

2. Leia kõige kallim toode igast kategooriast

3.Kuvada kõik kategooriad ja nende toodete arv

4. Leia toodete keskmine hind kategooria kaupa.

5.Kuvada ainult need kategooriad, kus pole ühtegi toodet.

6.Leia kõik tooted, mille hind on suurem kui tabeli keskmine hind

2. Vaated (VIEW)

1.Koosta päring, mis kuvab kõik tooted koos nende kategooriate nimedega.

create view toode_nimijahind as 
 select toodenimetus,hind from toode

 --kuuvame vaade
select * from toode_nimijahind

2.Loo vaade, mis näitab kõiki tooteid koos kategooria nimega.

create view tooded_kategooria
as 
select t.toodenimetus, t.hind, tk.toodekategooria from toode t 
inner join toodekategooria tk on tk.toodekategooriaId = t.toodekategooriaId

3.Loo vaade, mis koondab info: kategooria nimi, toodete arv, minimaalne ja maksimaalne hind.

Alter table toode 
add aktiivne bit
update toode set aktiivne = 1
select * from toode 
update toode set aktiivne = 0
where toodeId = 2

Create view saadav_toode
as 
select * from toode
where aktiivne = 1

4.Loo vaade, mis koondab info: kategooria nimi, toodete arv, minimaalne ja maksimaalne hind.

create view KatefooriaInfo as
	select toodekategooria,
			COUNT(*) as 'toodete arv',
			cast(min(t.hind) as decimal (5,1)) as 'min hind',
			cast (max(t.hind) as decimal (5,1)) as 'max hind'
			from toodekategooria tk
			inner join toode t on t.toodekategooriaId= tk.toodekategooriaId group by tk.toodekategooria;

5.Loo vaade, mis arvutab toode käibemaksu (24%) ja iga toode hind käibemaksuga.

Create view toode_kaibemaksuga as
	select toodenimetus, hind,
	CAST ((hind * 0.24) as decimal (5,2)) as 'Käibemaks',
	CAST ((hind * 1.24) as decimal (5,2)) as 'Hind_käibemaksuga'

3. protseduurid

1.Loo protseduur, mis lisab uue toote (sisendparameetrid: tootenimi, hind, kategooriaID).

create procedure toodesse_panna
@toodeNimetus varchar(200),
@hind int,
@toodeKategooriaID int
as
begin
insert into toode (toodenimetus,hind, toodekategooriaId)
values(@toodeNimetus,@hind,@toodeKategooriaID);
select * from toode
end

2 Loo protseduur, mis uuendab toote hinda vastavalt tooteID-le.

create procedure update_hind
@toodeId int,
@hind decimal(5,2)
as
begin
select * from toode;
update toode set hind=@hind
where toodeId=@toodeId;
select * from toode;
end

3 Loo protseduur, mis kustutab toote ID järgi.

create procedure KustutaToode 
@toodeID int
as
begin
delete from toode
where toodeID = @toodeID;
select * from toode 
end;

4 Loo protseduur, mis tagastab kõik tooted valitud kategooriaID järgi.

create procedure LeiaToodeKategooriaJargi
@kategooria varchar(30)
as
begin
select toodekategooria, toodeNimetus, hind
from toodekategooria tk
inner join toode t  on t.toodekategooriaId=tk.toodekategooriaId
where tk.toodekategooria = @kategooria
end;

5 Loo protseduur, mis tõstab kõigi toodete hindu kindlas kategoorias kindla protsendi võrra.

create procedure Protsenti
	@kategooriaId int
as
begin
	update toode 
	set hind = hind * (1+ 15/ 100)
	where toodekategooriaId = @kategooriaId
	select * from toode;
end;

6 Loo protseduur, mis kuvab kõige kallima toote kogu andmebaasis.

create procedure KallimHind
as 
begin
	select top 1 *
	from toode 
	order by hind desc
end

3. KASUTAJAD ja ÕIGUSED

1.Loo kasutaja tootehaldur – ta saab lisada, muuta ja kustutada ainult tooted tabeli kirjeid.

GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.toode TO tootehaldur;

2.Loo kasutaja kataloogihaldur – ta saab muuta ainult toodekategooriad tabelit (INSERT, UPDATE, SELECT).

GRANT SELECT, INSERT, UPDATE ON dbo.toodekategooria TO kataloogihaldur;

3.Loo kasutaja vaataja – ta saab vaadata mõlema tabeli andmeid (SELECT), aga mitte muuta.

GRANT SELECT ON dbo.toode TO vaataja;
GRANT SELECT ON dbo.toodekategooria TO vaataja;

kasutaja kinnitus

1.tootehaldur

2.kataloogihaldur

3.vaataja

Lisa eraldi õigusraport

SELECT 
    CONCAT(table_schema, '.', table_name) AS scope,
    grantee,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee IN ('tootehaldur', 'kataloogihaldur', 'vaataja');

lisaülesanne – transaktsioonide kordamine

1 Lisa uus toode turvaliselt

2 Uuenda toote hinda turvaliselt