Andmebaasisüsteemide alused SQL töö (0)

SQL Kodutöö
Katrin Kello D23
Loon 3 tabelit CREATE TABLE käsuga:
create table HUVIRINGI JUHENDAJA (
HUVIRINGI_JUHENDAJA_ID int not null,
EESNIMI VARCHAR(100) not null,
PERENIMI varchar(100) not null,
SÜNNIAEG date not null,
ISIKUKOOD VARCHAR(20),
SUGU CHAR (1) not null,
KOMMENTAAR VARCHAR(255),
PRIMARY KEY(HUVIRINGI_JUHENDAJA_ID)
Script Output: Table HUVIRINGI_JUHENDAJA created.
create table TÖÖ_HUVIRINGI_JUHENDAJANA(
TÖÖ_HUVIRINGI_JUHENDAJANA_ID integer not null,
HUVIRINGI_JUHENDAJA_ID integer not null,
HUVIRING_ID integer not null,
ALATES TIMESTAMP not null,
KUNI date,
KOMMENTAAR varchar(255),
PRIMARY KEY (TÖÖ_HUVIRINGI_JUHENDAJANA_ID)
FOREIGN KEY(HUVIRIGNI_JUHENDAJA_ID) REFERENCES ISIK(HUVIRINGI_JUHENDAJA_ID)
FOREIGN KEY(HUVIRING_ID) REFERENCES RIIK(HUVIRING_ID)
Script Output: Table TÖÖ_HUVIRINGI_JUHENDAJANA created.
create table HUVIRING (
HUVIRING_ID int not null,
NIMETUS VARCHAR (60),
ASUKOHT VARCHAR(55) not null,
ALATES TIMESTAMP not null,
KUNI DATE,
KOMMENTAAR VARCHAR(255),
PRIMARY KEY(HUVIRING_ID)
Script Output: Table HUVIRING created.
Lisan tabelitesse kirjed :
HUVIRINGI_JUHENDAJA tabelisse 8 rida, TÖÖ_HUVIRINGI_JUHENDAJAINA tabelisse 15 ja HUVIRING tabeli 9 rida.
Tabeli HUVIRINGI_JUHENDAJA lisan 8 kirjet :
insert into HUVIRINGI_JUHENDAJA VALUES (1,'Mart','Lill',to_date('1985/07/14','YYYY/MM/DD'),'38507141234','M',' Geoloogia huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(2,'Priit','Org',to_date('1989/11/15','YYYY/MM/DD'),'38912345678','M','Loomasõprade huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(3,' Jaanus ','Kool', to_date('1969/01/15','YYYY/MM/DD'),'36945678987','M','Loodussõbraliku ehituse huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(4,'Mari',' Kask ',to_date('1983/03/09','YYYY/MM/DD'),'48356789087','N','Geoloogia huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(5,'Liisi','Kaks',to_date('1989/01/15','YYYY/MM/DD'),'48976543241','N','Geoloogia huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(6,'Anne',' Pintsel ',to_date('1964/08/12','YYYY/MM/DD'),'46464683454','N','Kunsti huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(7,'Kaia','Õige',to_date('1982/01/01','YYYY/MM/DD'),'48245677896','N','Loomasõprade huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(8,'Juulia',' Kuusk ',to_date('1996/06/03','YYYY/MM/DD'),'49667541234','N','Loodussõbraliku ehituse huviringi juhendaja');
Peale iga sisestust andis teate: 1 row inserted.
Lisan tabelisse HUVIRING 9 kirjet:
INSERT INTO HUVIRING VALUES(1,'1-4 klass','Kunsti huviring',to_date('2008/03/10','YYYY/MM/DD'),'','Kunsti huviring');
INSERT INTO HUVIRING VALUES(2,'1-5 klass','Loomasõprade huviring',to_date('2010/03/10','YYYY/MM/DD'),to_date('2015/01/10','YYYY/MM/DD'),'Loomasõprade huviring');
INSERT INTO HUVIRING VALUES(3,'6-9 klass','Loodussõbraliku ehituse huviring',to_date('2007/10/20','YYYY/MM/DD'),'','Loodussõbraliku ehituse huviring');
INSERT INTO HUVIRING VALUES(4,'10-12 klass','Loodussõbraliku ehituse huviring',to_date('2002/03/10','YYYY/MM/DD'),'','Loodussõbraliku ehituse huviring');
INSERT INTO HUVIRING VALUES(5,'1-5 klass','Kunsti huviring',to_date('2001/01/09','YYYY/MM/DD'),'','Kunsti huviring');
INSERT INTO HUVIRING VALUES(6,'10-12 klass','Geoloogia huviring',to_date('2002/01/29','YYYY/MM/DD'),to_date(' 2017 /11/6','YYYY/MM/DD'),'Geoloogia huviring');
INSERT INTO HUVIRING VALUES(7,'10-12 klass','Loomasõprade huviring',to_date('1978/03/11','YYYY/MM/DD'),'','Loomasõprade huviring ');
INSERT INTO HUVIRING VALUES(8,'6-9 klass','Geoloogia huviring',to_date('2010/03/10','YYYY/MM/DD'),to_date('2017/03/10','YYYY/MM/DD'),'Geoloogia huviring');
INSERT INTO HUVIRING VALUES(9,'6-9 klass','Loomasõprade huviring',to_date('2001/11/10','YYYY/MM/DD'),'','Loomasõprade huviring');
Peale iga sisestust andis teate: 1 row inserted.
Lisan tabelisse TÖÖ_HUVIRINGI_JUHENDAJANA 15 kirjet:
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(1,'1','1', to_date('2002-01-11','YYYY-MM-DD'), to_date('','YYYY-MM-DD'),'Aasta parim juhendaja');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(2,'1','3', to_date('2000-01-11','YYYY-MM-DD'), to_date('2001-01-11','YYYY-MM-DD'),'Praktikal juhendajana');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(3,'1','9', to_date('2014-11-28','YYYY-MM-DD'), to_date('','YYYY-MM-DD'),'Juhendab huviringi');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(4,'2','4', to_date('1991-02-09','YYYY-MM-DD'), to_date('','YYYY-MM-DD'),'Tartu linna parim huviringi juhendaja);
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(5,'3','5', to_date('2000-07-07','YYYY-MM-DD'), to_date('2007-06-07','YYYY-MM-DD'),'Kunagine geoloog');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(6,'4','1', to_date('2008-01-11','YYYY-MM-DD'), to_date('2010-03-06','YYYY-MM-DD'),'Õpetaja kunstikoolis');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(7,'4','8', to_date('2010-03-18','YYYY-MM-DD'), to_date('','YYYY-MM-DD'),'Õpetaja keskkoolis ');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(8,'5','3', to_date('2013-04-05','YYYY-MM-DD'), to_date('2014-06-07','YYYY-MM-DD'),' Praktikant ');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(9,'5','6', to_date('2014-10-10','YYYY-MM-DD'), to_date('','YYYY-MM-DD'),'Juhendaja abi');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(10,'6','2', to_date('2015-01-07','YYYY-MM-DD'), to_date('2017-01-01','YYYY-MM-DD'),'Juhendaja abi');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(11,'6','7', to_date('2017-01-11','YYYY-MM-DD'), to_date('','YYYY-MM-DD'),'Juhendaja');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(12,'7','5', to_date('2005-08-19','YYYY-MM-DD'), to_date('2006-06-06','YYYY-MM-DD'),'Praktikant');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(13,'7','4', to_date('2009-12-26','YYYY-MM-DD'), to_date('','YYYY-MM-DD'),'Huvikooli direktor ');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(14,'8','5', to_date(' 2016 -10-05','YYYY-MM-DD'), to_date('','YYYY-MM-DD'),'Juhendaja');
insert into TÖÖ_HUVIRINGI_JUHENDAJANA VALUES(15,'4','6', to_date('2016-09-09','YYYY-MM-DD'), to_date('','YYYY-MM-DD'),'Juhendaja');
Peale iga sisestust andis teate: 1 row inserted.
select * from HUVIRINGI JUHENDAJA;
select * from HUVIRING;
select * from TÖÖ_HUVIRINGI_JUHENDAJANA;
Näen kõiki kolme tabelit.
Commit;
Commit succesful
Muudan tabelis kirjeid:

• Ühes kirjes:
  

UPDATE HUVIRINGI_JUHENDAJA SET sugu=’M’ WHERE HUVIRINGI_JUHENDAJA_ID=3;
3. Juhendaja muudab sugu;

• Mitmes kirjes korraga:
  

UPDATE HUVIRINGI_JUHENDAJA SET sugu=’M’ WHERE sugu=’N’;
Kõik mehed tegid soovahetuse naisteks;

• Ühe tabeli andmed kõigis kirjetes:
  

UPDATE HUVIRINGI_JUHENDAJA SET sugu=’N’;
Kõik mehed on naised.
Kustutan tabelitest kirjeid:

Ühest tabelist üks kirje:
DELETE FROM HUVIRINGI_JUHENDAJA where HUVIRINGI_JUHENDAJA_ID=1;

Ühest tabelist mitu kirjet:
DELETE FROM HUVIRINGI_JUHENDAJA where HUVIRINGI_JUHENDAJA_ID=3 OR HUVIRINGI_JUHENDAJA_ID=2;

Ühest tabelist kõik kirjed:
DELETE FROM HUVIRINGI_JUHENDAJA;
Kirjete taastamine:
insert into HUVIRINGI_JUHENDAJA VALUES(1,'Mart','Lill',to_date('1985/07/14','YYYY/MM/DD'),'38507141234','M','Geoloogia huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(2,'Priit','Org',to_date('1989/11/15','YYYY/MM/DD'),'38912345678','M','Loomasõprade huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(3,'Jaanus','Kool', to_date('1969/01/15','YYYY/MM/DD'),'32345678987','M','Loodussõbraliku ehituse huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(4,'Mari','Kask',to_date('1983/03/09','YYYY/MM/DD'),'48356789087','N','Geoloogia huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(5,'Liisi','Kaks',to_date('1989/01/15','YYYY/MM/DD'),'48976543241','N','Geoloogia huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(6,'Anne','Pintsel',to_date('1964/08/12','YYYY/MM/DD'),'46464683454','N','Kunsti huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(7,'Kaia','Õige',to_date('1982/01/01','YYYY/MM/DD'),'48245677896','N','Loomasõprade huviringi juhendaja.');
insert into HUVIRINGI_JUHENDAJA VALUES(8,'Juulia','Kuusk',to_date('1996/06/03','YYYY/MM/DD'),'49667541234','N','Loodussõbraliku ehituse huviringi juhendaja');
Päring ühte tabelit kasutades

SELECT * FROM HUVIRINGI_JUHENDAJA;
Näeb tervet huviringi_juhendaja tabelit.

SELECT * FROM HUVIRINGI_JUHENDAJA WHERE SUGU=’N’;
Näeb kõiki kirjeid kus sugu on N ehk naine.

SELECT SÜNNIAEG, SUGU FROM HUVIRINGI_JUHENDAJA;
Näeb huviringi_juhendaja tabelis kõiki sugu ja sünniaja kirjeid.
Miinimum MIN(), maksimum MAX() ja loendusandmeid COUNT():

• SELECT MIN(ISIKUKOOD) FROM HUVIRINGI_JUHENDAJA;
  

Ja tulemuseks tuleb et kõige väiksem isikukood on 36945678987, see on see on Jaanus Kool isikukood.

• SELECT MAX(ISIKUKOOD) FROM HUVIRINGI_JUHENDAJA;
  

Kõige suurem isikukood tabelist huviringi juhendaja on 49667541234 ja see on Juulia Kuusk isikukood.
Kuna meeste isikukood algab 3-ga, naistel 4-ga, siis võib väita et naistel tuleb alati suurem number ning meestel kõige väiksem

• COUNT() käsk annab teada palju kirjeid on:
  

SELECT COUNT(ISIKUKOOD) FROM HUVIRINGI_JUHENDAJA WHERE HUVIRINGI_JUHENDAJA_ID=1 OR HUVIRINGI_JUHENDAJA_ID=2;
Loeb kokku kui palju teevad isikukoodid kokku esimesel ja teisel huviringi juhendajal

SELECT COUNT(ISIKUKOOD) FROM HUVIRINGI_JUHENDAJA;
Võtab kokku kõik isikukoodid tabelis
Päring läbi kolme tabeli:
a. Väjastatakse piiranguteta kõik read:
select huviringi_juhendaja.huviringi_juhendaja_id,
töö_huviringi_juhendajana.huviringi_juhendaja_id,
töö_huviringi_juhendajana.huviring _id,
huviring .huviring _id,
from huviringi_juhendaja
join töö_huviringi_juhendajana
on huviringi_juhendaja.huviringi_juhendaja_id=töö.huviringi_juhendajaina.huviringi_juhendaja_id
join huviring
On huviring .huviring _id=töö_huviringi_juhendajana.huviring _id
b. piirangu WHERE lisamine
select huviringi_juhendaja.huviringi_juhendaja_id,
töö_huviringi_juhendajana.huviringi_juhendaja_id,
töö_huviringi_juhendajana.huviring _id,
huviring .huviring _id,
from huviringi_juhendaja, töö_huviringi_juhendajana, huviring
where(huviringi_juhendaja.huviringi_juhendaja.id=töö_huviringi_juhendajana_id=töö_huviringi_juhendajana.huviringi_juhendaja_id) and (huviring _id=huviring _id) and (huviringi_juhendaja_id=1)
order by huviringi_juhendaja_id;
Näeb kõiki kolm tabelit, kus on veerul huviringi_juhendaja_id=1.

Muutke piirangut selliselt , et väljastaks mitu rida, aga mitte kõiki ridu
select huviringi_juhendaja.huviringi_juhendaja_id,
töö_huviringi_juhendajana.huviringi_juhendaja_id,
töö_huviringi_juhendajana.huviring _id,
huviring .huviring _id,
from huviringi_juhendaja
join töö_huviringi_juhendajana
on huviringi_juhendaja.huviringi_juhendaja_id=töö_huviringi_juhendajana.huviringi_juhendaja_id
join huviring
on huviring .huviring _id=töö_huviringi_juhendajana.huviring _id
where huviringi_juhendaja.huviringi_juhendaja_id=2 or huviringi_juhendaja.huviringi_juhendaja_Id=3;
Näen kõik kolm tabelit, mis on seotud huviringi_juhendaja_id 2 ja 3-ga.
Muudatuse salvestamine
Commit;
Kui midagi valesti läks
Rollback;
Kui on vaja kogu tabeli kustutada
Drop table HUVIRINGI_JUHENDAJA;