Andmebaasid 2023 sql näide

/*Kood PostgreSQL (ver 16) jaoks.
Iga amet on seotud null või rohkem töötajaga.
Iga töötaja töötab täpselt ühes ametis.*/

createdb tootajad
/*Andmebaasi loomine.

Alternatiivina serveris andmebaasi loomisele saaks seda koodi prooviks käivitada siin:
https://www.db-fiddle.com/
*/

CREATE TABLE Amet (amet_kood SMALLINT NOT NULL,
nimetus VARCHAR(100) NOT NULL,
palk DECIMAL(10,2) NOT NULL,
CONSTRAINT pk_amet PRIMARY KEY (amet_kood),
CONSTRAINT ak_amet_nimetus UNIQUE (nimetus),
CONSTRAINT chk_amet_palk CHECK (palk>0));
/*Luuakse baastabel e tabel. See on nimega tabel, mis EI OLE defineeritud teiste 
tabelite põhjal. Tabeli loomisel deklareeritakse piirangud e kitsendused andmetele
ja andmeid kontrollitakse nende kitsenduste vastu kohe kirjutamisel - andmemuudatused,
mis üritavad andmebaasi lisada kitsendustele mittevastavaid andmeid, ebaõnnestuvad.*/

INSERT INTO Amet (amet_kood, nimetus, palk) 
VALUES (1, 'Juhataja', 3000), (2, 'Raamatupidaja', 2800), (3, 'Koristaja', 2000);
/*Korraga saab tabelisse lisada mitu rida.*/

INSERT INTO Amet (amet_kood, nimetus, palk) 
VALUES (300000, 'IT-administraator', 1500);
/*Ebaõnnestub, sest väärtus 300000 ei kuulu tüüpi SMALLINT.*/

INSERT INTO Amet (amet_kood, nimetus, palk) 
VALUES (3, 'IT-administraator', 2500);
/*Ebaõnnestub, sest selline amet_kood on juba registreeritud.*/

INSERT INTO Amet (amet_kood, nimetus, palk) 
VALUES (4, 'Koristaja', 2500);
/*Ebaõnnestub, sest sellise nimetusega amet on juba registreeritud.*/

INSERT INTO Amet (amet_kood, nimetus) 
VALUES (4, 'IT-administraator');
/*Ebaõnnestub, sest palk puudub.*/

INSERT INTO Amet (amet_kood, nimetus, palk) 
VALUES (4, 'IT-administraator', -5);
/*Ebaõnnestub, sest palk on negatiivne.*/

CREATE TABLE Tootaja (tootaja_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
e_meil VARCHAR(254) NOT NULL,
eesnimi VARCHAR(50),
perenimi VARCHAR (50),
amet_kood SMALLINT NOT NULL DEFAULT 2,
CONSTRAINT pk_tootaja PRIMARY KEY (tootaja_id),
CONSTRAINT chk_tootaja_eesnimi_perenimi CHECK (eesnimi IS NOT NULL OR perenimi IS NOT NULL),
CONSTRAINT chk_tootaja_e_meil CHECK (e_meil LIKE '%@%'),
CONSTRAINT fk_tootaja_amet FOREIGN KEY (amet_kood) 
REFERENCES Amet(amet_kood) ON UPDATE CASCADE);

/*
Nimed võivad olla pikad: https://en.wikipedia.org/wiki/Hubert_Blaine_Wolfeschlegelsteinhausenbergerdorff_Sr.
Nime osad võivad puududa: https://digikogu.taltech.ee/et/Item/ab7e8f7e-ce28-4cc1-bb78-40c624d19bf6
Meiliaadress võib olla kuni 254 märki: https://blog.moonmail.io/what-is-the-maximum-length-of-a-valid-email-address-f712c6c4bc93
*/

/*Meiliaadress peab olema tõstutundetult unikaalne.
Näide, et SQL-andmebaasisüsteemid võivad pakkuda võimalusi, mida 
SQL standard ette ei näe.*/
ALTER TABLE Tootaja ADD CONSTRAINT
ak_tootaja_e_meil EXCLUDE
(Upper(e_meil) WITH =);

INSERT INTO Tootaja (e_meil, eesnimi, perenimi, amet_kood)
VALUES ('karl.kask@online.ee','Karl','Kask',1),
('karin.kadakas@mail.ee','Karin','Kadakas',2),
('liis.lehis@mail.ee','Liis','Lehis',2)
RETURNING tootaja_id, e_meil;
/*Lasen tagastada genereeritud identifikaatorid.*/

INSERT INTO Tootaja (e_meil, eesnimi, perenimi, amet_kood)
VALUES ('Karl.Kask@online.ee','Karl','Kask',1);
/*Lisamine ebaõnnestub, sest tõstundetult vaadates on selline meiliaadress juba registreeritud.*/

INSERT INTO Tootaja (e_meil, eesnimi, perenimi, amet_kood)
VALUES ('toomas.tuisk@hot.ee','Toomas','Tuisk',100);
/*Lisamine ebaõnnestub, sest sellise koodiga ametit pole registreeritud.
Andmebaasisüsteem kontrollib viidete terviklikkuse reeglit - referential integrity rule.*/

UPDATE Amet SET amet_kood=100 WHERE amet_kood=1;
/*Muudatus õnnestub, muudetakse ameti koode ka tabelis Tootaja.*/

SELECT * FROM Tootaja;

DELETE FROM Amet WHERE amet_kood=2;
/*Kustutamine ebaõnnestub, sest sellise koodiga ametis töötab vähemalt üks töötaja.*/

DELETE FROM Amet WHERE amet_kood=4;
/*Kustutamine õnnestub, sest sellise koodiga ametis ei tööta ühtegi töötajat.*/

SELECT *
FROM Tootaja 
WHERE perenimi LIKE 'K%';

WITH tootajad_k AS (SELECT *
FROM Tootaja 
WHERE perenimi LIKE 'K%')
SELECT Count(*) AS arv
FROM tootajad_k;

/*SQL defineerib hulga lugemisoperaatoreid, mis võimaldavad olemasolevate tabelite põhjal tuletada uusi tabeleid.
Olemasolevatest faktidest tuletatakse uusi.
Iga sellise operatsiooni sisendiks on üks või mitu tabelit ning tulemuseks samuti tabel, 
mis võib omakorda olla sisendiks järgnevale operatsioonile.
Eelnevad SELECT laused annavad tulemuseks NIMETU tabeli -
vastandina NIMEGA tabelile, mis tekib näiteks CREATE TABLE lause tulemusena.
*/

START TRANSACTION;
DELETE FROM Tootaja;
DELETE FROM Amet;
SELECT * FROM Tootaja;
SELECT * FROM Amet;
ROLLBACK;
SELECT * FROM Tootaja;
/*Andmebaasikeele lauseid saab koondada ühte loogilisse tervikusse e transaktsiooni
e tehingusse. Tähendab, et need laused täidetakse kas kõik või
jäetakse kõik täitmata.*/

SELECT amet_kood, nimetus AS amet_nimetus, palk, e_meil, eesnimi, perenimi
FROM Tootaja INNER JOIN Amet USING (amet_kood)
ORDER BY amet_kood, e_meil;
/*Leia töötajate ja nende ametite andmed.*/

SELECT amet_kood, nimetus
FROM Amet
WHERE NOT EXISTS (SELECT * 
FROM Tootaja
WHERE Tootaja.amet_kood=Amet.amet_kood);

SELECT amet_kood, nimetus
FROM Amet
WHERE amet_kood NOT IN (SELECT amet_kood
FROM Tootaja);
/*Leia ametid, kus ei tööta ühtki töötajat.
NOT IN päring täidetakse suurte andmehulkade korral PostgreSQLis palju aeglasemalt kui NOT EXISTS päring.
Näide sellest, kuidas "abstraktsioon tilgub läbi" e süsteemi hingeelu probleemid mõjutavad
selle kasutamist.*/

ANALYZE;
/*Värskenda andmebaasi statistikat.*/

EXPLAIN ANALYZE SELECT amet_kood, nimetus
FROM Amet
WHERE amet_kood NOT IN (SELECT amet_kood
FROM Tootaja);
/*SELECT lause on deklaratiivne - öeldaks, mida on vaja leida.
Selle alusel koostab andmebaasisüsteem enda sisestest operatsioonidest (näiteks loe indeksit, loe tabeli plokke, eemalda kordused räsiväärtuste 
leidmise kaudu) koosneva protseduuri - sammude jada. Seda protseduuri nimetatakse täitmisplaaniks.
Andmebaasisüsteem üritab koostada võimalikult optimaalse täitmisplaani.
Selle lausega saab vaadata lause täitmise plaani ja täitmise aega.*/

CREATE OR REPLACE VIEW tootajateta_amet WITH (security_barrier) AS
SELECT amet_kood, nimetus
FROM Amet
WHERE NOT EXISTS (SELECT * 
FROM Tootaja
WHERE Tootaja.amet_kood=Amet.amet_kood);
/*Luuakse virtuaalne tabel e vaade. See on nimega tabel, mis ON defineeritud teiste 
tabelite põhjal (antud juhul Amet ja Tootaja).*/

SELECT *
FROM tootajateta_amet;
/*Vaate põhjal saab teha päringuid e SELECT lauseid ning
läbi lihtsamate vaadete ka muuta andmeid baastabelites.*/

DROP TABLE Tootaja CASCADE;
DROP TABLE Amet CASCADE;
/*Tabelite kustutamine.
CASCADE tähendab, et kustutatakse ka sõltuvad andmebaasiobjektid -
antud juhul vaade.*/

dropdb tootajad
/*Andmebaasi kustutamine.*/