Andmebaasid (0)

Andmebaasid  
 
Laenutamine 
CREATE TABLE LAENUTAMINE ( 
Laenamise_id INT not null auto_increment, 
Kuupäev date , 
Raamatu_ID INT, 
Lugeja_ID INT, 
Tähtaeg date, 
Tagastamine date, 
PRIMARY  KEY (Laenamise_id), 
FOREIGN  KEY (Lugeja_ID) REFERENCES LUGEJA(Lugeja_ID), 
FOREIGN KEY (Raamatu_id) REFERENCES Raamat(Raamatu_ID)); 
Lisa 10 kirjet.  
INSERT  INTO LAENUTAMINE(Kuupäev,Lugeja_ID,Raamatu_ID,Tähtaeg) 
VALUES (curdate(),1,3,adddate(curdate(), interval 21 day)); 
RAAMAT 
CREATE TABLE RAAMAT (        
Raamatu_ID INT AUTO_INCREMENT,        
Pealkiri VARCHAR(150) NOT NULL,       
Autor VARCHAR(100) NOT NULL,        
Aasta  YEAR  NOT NULL,        
Koht VARCHAR(50),       
Lk_arv INT,        
Keel VARCHAR(50),        
Hind FLOAT,        
PRIMARY KEY (Raamatu_ID)); 
 
Kirjed :  
INSERT INTO RAAMAT (Pealkiri, Autor, Aasta, Koht, Lk_arv, Keel, Hind) VALUES('Asum', ' Isaac Asimov', 
1976,  'Eesti kirjastus', 193, 
'eesti',  35.00), ('Ajaseemned',  'Isaac Asimov',  1978,  'Eesti 
kirjastus', 258,  'eesti',  38.00),('Frankestein',  ' Mary  Shelly',  1968,  'Eesti kirjastus',189, 
'eesti', 
29.00),('Tõde ja õigus',  'A.H. Tammsaare ', 
1920,  'Tallinn', 
459, 
'eesti', 
235.00),('Kevade', 
' Oskar   Luts ', 
1945,  'Tallinn', 
268, 
'eesti',  46.00),('Daam  sinises ', 
'T.Trubetski', 
1986,  'Tallinn', 
137, 
'eesti',  25.00),('Mina ja George', 
'T.Trubetski', 
1988,  'Tallinn', 
153, 
'eesti',  38.00),('Käitumise anatoomia',  'Sigmund  Freud ', 
1997, 
'Tallinn', 
5, 
'eesti',  395.00),(' Konstitutsioon ', 
'J.Stalin', 
1944,  'Tallinn',  287, 
'eesti',  56.00),('Rahvasõbrad',  'V.I.Lenin', 
1951,  'Tallinn', 
176, 
'eesti', 
23.00),('Leninismi alustest', 
'J.Stalin', 
1945,  'Tallinn', 
357, 
'eesti', 
65.00),(' Pimeduse   pahem  käsi', 'U.K.LeQuine',  1975,  'Tallinn', 
198, 
'eesti',  55.00),('Mis 
teha',  'J.K.Ibin', 
1950,  'Tallinn', 
175, 
'eesti',  43.00),('Surnud 
hinged ', 
'Nikolai 
Kogol ',  1977,  'Tallinn', 
298, 
'eesti',  55.00),('Kalevipoeg', 
'A.Kivirähk', 
1997, 
'Tallinn', 
2, 
'eesti',  60.00),('Massi ebajumal', 
'V.Läcis', 
1989,  'Tallinn',  376, 
'eesti',  75.00),('Inimesed maskides', 
'V.Läcis', 
1989,  'Tallinn', 
245, 
'eesti', 
130.00),(' Hiline kevad',  'V.Läcis', 
1989,  'Tallinn', 
298, 
'eesti',  89.00),(' Testament ', 
'Rex  Stout ', 
1986,  'Tallinn', 
187, 
'eesti',  115.00),(' Pioneerid ', 
'J.F.Cooper', 
1973, 
'Tallinn', 
398, 
'eesti',  1.00),('Surma  ratsanikud ', 
'M.Kivistik', 
1992,  'Tallinn',  287, 
'eesti',  1.00),('Kana ja muna',  'Kait Marandi',  1996,  'Tartu Helmes',  2, 
'jaapani', 
450.00),(' Naksitrallid ',  ' Ellen   Niit ', 
1980,  'Tartu',  150, 
'eesti',  45.00),('Arvuti A ja B',  ' Rein  
Hanson', 
1978,  'USA',  7, 
'inglise', 
23.00),('Kasuema', 
' Silvia  
Rannamaa', 
1923,  'Valga', 145, 
'eesti',  45.00),('Kunksmoor', 
'Aino Perg', 
1966,  'Tartu',  67, 
'eesti', 
75.00),('Arabella', 
'Aino Pervik',  1985,  'Tallinn', 
187, 
'eesti',  876.00),(' Bullerby  
lapsed', ' Astrid  Lindgren', 
1982,  'Rootsi',199, 
'rootsi', 26.00),('Muumitrollid',  ' Tove   Janson', 
1998,  'Norra', 4, 
'norra', 77.00),('Ja päike tõuseb', 
'Hernst  Hemingway ', 
1938,  'USA', 
234, 
'inglise', 
327.00),(' Vanamees  ja meri', 
'Hernst Hemingway', 
1964,  'USA',  312, 
'inglise', 
83.00),('10 aastat hiljem', 
'Alexander  Dumas ', 
1955,  'Pariis', 183, 
'prantsuse', 
821.00),(' Nukitsamees ', 'Oskar Luts', 
1977,  'Tallinn', 
87, 
'eesti', 
32.00),('Soo',  'Oskar Luts', 
1971 ,  'Tartu',  56, 
'eesti',  45.00),('Vari',  'Juhan Liiv', 
1928, 
'Tartu',  287, 
'eesti',  63.00),('Vares', 'Jüri Tuulik ', 
1948,  'Tallinn', 
253, 
'eesti', 
72.00),('Kevade', 
'Oskar Luts', 
1997,  'Tartu',  188, 
'eesti',  33.00),('Suvi',  'Oskar  Luts', 
1988,  'Narva', 162, 
'eesti',  79.00),('Sõda ja rahu',  'Lev Tolstoi ', 
1963,  ' Moskva ', 
645, 
'vene',  143.00),('Kuritöö ja  karistus ',  ' Dostojevski ',  1973,  'Moskva', 
487, 
'vene', 
52.00),('Saladuslik saar', 
'Jules Verne',  1956,  'Pariis', 682, 
'eesti',62.00); 
 
Lugeja 
CREATE TABLE LUGEJA (        
Lugeja_ID INT AUTO_INCREMENT ,        
Eesnimi VARCHAR(50) NOT NULL,        
Perenimi  VARCHAR(50) NOT NULL,        
Isikukood   CHAR (11) NOT NULL,        
Aadress VARCHAR(100),        
Telefon VARCHAR(15),        
email  VARCHAR(255),        
Amet VARCHAR(50),        
Asutus VARCHAR(50),        
PRIMARY KEY (Lugeja_ID)); 
KIRJED: 
INSERT INTO LUGEJA (isikukood, eesnimi, perenimi, telefon, aadress, email, amet,asutus) 
VALUES('38910262497','Kalle',' Kohin ','56789576','Tartu Pärna 23-12', '[email protected]', 'õpilane', 
'Tartu KHK'),('49011292497',' Tiia ',' 
Tuisk ','50468977','Tartu Riia 
126','[email protected]','õpetaja','Meie kool'),('49110302488','Pille','Pill','51689789','Tartu 
Jalaka 12-3', 'pillekas@ gmail .com','õpilane','Meie kool'),('49010162558','Kati','
 
Kask ','53459878','Tartu Kopli 1','[email protected]', 'juhiabi','AS 
Töö'),('48910012697','Malle',' Moos ','56423789','Tartu Võru 54-21', '', 'koristaja','Oü Kord ja 
Puhtus'),('48810242787','Mari',' 
Maasikas','50458977','Tartu Aida 3-
14','','',''),('38810222497',' Joosep ',' Jalakas ','556457575','Tartu ','[email protected]',' lektor ','Tartu 
ülikool'),('38712062497','Tiit','  Tikk','547799133','Tartu','[email protected]','juhataja','As 
Tiit'),('38911212491',' Ragnar ','Roos','7715478','Tartu','[email protected]','õpilane','Uus 
kool'),('38806062494','Robert','Rohi','588787362','Tartu Võru 36',' robi @uuuskool.ee','õpetaja','Uus 
kool'),('39006022497',' Kevin ','Kivi','59791547','Tartu Turu 5-12','[email protected]','õpilane','Uus 
kool'),('49010262577',' Sille ','Siil','454667993','Tartu Võru 69','[email protected]','üliõpilane','Tartu 
ülikool'),('48910222497','Lilli','Lill','487541247','Tartu Pärna 
34','[email protected]','kodune',''),('49112162497','Luisa','Tuul','558787887','Tartu Vanemuise 56-
23','tuul@ puhub .ee','',''),('49012292493','Sandra','Saar','589456534','Tartu Narva mnt 34-
12','','',''),('48910212197','Kadri',' Kade ','','Tartu Võru 89','kati@ kokk .ee','kokk','AS Söök ja 
jook '),('38811202495','Vaiko',' Kook ','7689898','Tartu Pikk 56-
10','',' autojuht ','Oüauto'),('38710022497',' Veiko ','Vesi','5877693','Tartu Lai 76-
11','[email protected]','',''),('39212262427','Hannes','Hein','','Tartu Betooni 
24','heina@ tool .ee','müüja','AS Tool'),('38904292497','Leo',' Loots ','569873223','Tartu Pepleri 2-
1','[email protected]','autojuht','AS Maja'),('49111162497','Liia',' Lips ','69876222','Tartu Pärna 
6','[email protected]','müüja','Riidepood'),('38809262497','Kalev',' Komm ','5464645747','Tartu Võru 
33','[email protected]','müüja','Kommipood'),('49107232497',' Rita ','Rehv','52323424','Tartu 
Riia 
120','[email protected]','kassapidaja',' Rehvivahetus '),('38911222492',' Janek ','Jooksik','56787900'
,'Tartu Raatuse  22-4','[email protected]','õpilane','Meie 
kool'),('49009162497',' Jane ','Jänes','5424324325','Tartu Piima 12','[email protected]','õpilane','Teie 
kool'),('39112312497','Mart','Karu','587979797','Tartu Aleksandri 3-
2','[email protected]','õpetaja','Teie kool'),('48901312397','Kati','Karu','74645679','Tartu Jalaka 
23-3','[email protected]','õpilane','Teie kool'); 
 
Laps 
CREATE TABLE LAPS (        
Laps_ID INT AUTO_INCREMENT ,        
Eesnimi VARCHAR(40) NOT NULL,  
Pikkus CHAR(5) NOT NULL,              
Sünniaasta CHAR(4) NOT NULL,        
Sünnilinn VARCHAR(100),        
PRIMARY KEY (Laps_ID)); 
ÜLESANDED 
Ülesanne 3 
15.02. 2017  
__________________________________ 
 
Ülesanne 3. Lihtsamad  select  päringud 
 
Lihtsamad päringud SQL-is 
Käsud (alates 3. ülesandest), mida sisestad MySql -is ja saadud tulemused kopeeri ka siia töölehele.  
1. Loo  tabelid  RAAMAT ja LUGEJA MySql-is, kui Sa pole seda veel teinud. 
2. Sisesta andmed tabelisse RAAMAT vt faili "raamat.txt" 
___ 
3. Väljasta kogu tabeli RAAMAT andmed. 
 
SELECT * FROM `RAAMAT`;  
4. Väljasta raamatute  pealkirjad , autorid ja ilmumisaastad. 
 
SELECT Pealkiri, Autor, Aasta FROM RAAMAT 
5. Väljasta nende raamatute pealkirjad, autorid ja ilmumisaastad, mis on ilmunud enne 1950 aastat. 
Mitu neid on? 
 
SELECT Pealkiri, Autor, Aasta FROM RAAMAT WHERE Aasta1987; 
 
Eesti, jaapani, ja norra 
7. Leia kõik 1988 aastal ilmunud raamatud. Väljasta pealkiri ja aasta. Mitu neid on? 
 
SELECT Pealkiri, Aasta FROM RAAMAT WHERE Aasta=1988; 
 
Neid on 2 
8. Leia kõik Tartus välja antud raamatud. Väljasta koht ja pealkiri. 
 
SELECT Pealkiri, Koht FROM RAAMAT WHERE Koht = 'Tartu'; 
 
5 tk 
9. Leia raamatud, mille hind on suurem 15€. Väljasta raamatu pealkiri ja hind. 
 
SELECT Hind FROM RAAMAT WHERE Hind>15; 
10. Leia raamatud, mille hind on väiksem või võrdne 11€. Väljasta raamatu pealkiri ja hind. 
 
SELECT Pealkiri, Hind FROM RAAMAT WHERE Hind (SELECT AVG(YEAR(NOW())-Aasta) FROM RAAMAT); 
5. Leia raamatud, mille hind on suurem 150 eurost, kui eelnevalt suurendati raamatute hinda 1,5 
kord. 
 
SELECT Pealkiri, 1.5*Hind from RAAMAT where (1.5*Hind) > 150; 
6. Väljasta nende  autorite  raamatud, kellel on tabelis 2 raamatut. 
 
SELECT Autor,Pealkiri from RAAMAT as R where 2 = (select  count (*) from RAAMAT where 
R.Autor = RAAMAT.Autor) 
7. Arvuta  viivis nendele raamatute laenutajatele, kes ei tagastanud raamatut õigeks päevaks. Viivis on 
1 senti päevas. 
 
SELECT Eesnimi,Perenimi, datediff(tagastamine,tähtaeg)*0,01 as viivis from LAENUTAMINE 
LA inner  join  LUGEJA L on LA.Lugeja_ID=L.Lugeja_ID where datediff (tagastamine,tähtaeg)>0 
 
8. Leia eestikeelsete raamatute tagastamistähtajad 
 
SELECT Pealkiri, Keel, Tähtaeg From RAAMAT INNER JOIN LAENUTAMINE on 
RAAMAT.Raamatu_id=LAENUTAMINE.Raamatu_id WHEREKeel='eesti'; 
 
KOKKUVÕTTEV ÜLESANNE 
1. Sisestage faili  algusesse  oma nimi. 
2. Järgnevate ülesannete tegemisel kopeerida sisestatud MySql’i käsud ja saadud tulemused vastava 
käsu juurde. 
3.(3p) Kasutades MySql’i loo andmebaasi andmetabel LAPS, milles on järgmised andmeväljad: 
LapsID – primaarvõtme väli ja automaatselt suurenev ning ei tohi olla null, 
Nimi tekstiline, pikkus 40 ja väärtus ei tohi olla null, 
Pikkus täisarv, 
Synniaasta täisarv, 
SynniLinn täisarv. 
CREATE TABLE LAPS ( 
Laps_ID INT AUTO_INCREMENT , 
Eesnimi VARCHAR(40) NOT NULL, 
Pikkus INT, 
Synniaasta INT, 
Synnilinn INT, 
PRIMARY KEY (Laps_ID)); 
 
4.(3p) Lisa tabelisse järgnevalt toodud andmed 
Nimi, Pikkus, Synniaasta, SynniLinn 
 
Juku , 155, 1997, 1 
Kati, 158, 1997, 2 
Mati, 164, 1995, 2 
Siiri, 153, 1996, 1 
Madis, 174, 1995, 1 
Siim, 163, 1997, 2 
INSERT INTO LAPS (Eesnimi, Pikkus, Sünniaasta, Sünnilinn) 
VALUES('Juku','155','1997','1'),('Kati','158','1997','2'),('Mati','164','1995','2'),('Siiri','153','1996'
,'1'),('Madis','174','1995','1'),('Siim','163','1997','2'); 
 
5.(3p) Järjesta lapsed pikkuse järgi kahanevalt (väljasta nimi ja pikkus). 
SELECT Eesnimi, Pikkus FROM LAPS  ORDER  BY Pikkus  DESC ; 
6.(2p) Väljasta laste nimed ja pikkused (meetrites). Pikkuse lähteandmed on sentimeetrites. 
Select Eesnimi, concat( round (Pikkus/100, 2), 'm') as Pikkus From LAPS; 
7.(2p) Leia kõige pikema lapse pikkus. Väljasta lapse nimi ja pikkus 
SELECT Eesnimi, Pikkus FROM LAPS WHERE Pikkus =(select max(Pikkus)from LAPS);´ 
8.(3p) Leia laste vanused (väljasta nimi, pikkus ja vanus). Vanuse arvutamise käsk peab töötama ka 
järgmisel aastal. 
SELECT Eesnimi, Pikkus,Year(curdate()) - Sünniaasta as Vanus From LAPS; 
9.(5p) Leia laste keskmine vanus (ümarda täisarvuni). 
SELECT round(avg(Year(curdate())))- Sünniaasta as Keskmine_vanus From LAPS; 
10.(5p) Kes lastest on keskmisest vanemad? (Nimed ja vanused) 
SELECT Eesnimi, year(curdate())-Sünniaasta as Vanus From LAPS Where (year(curdate())-
Sünniaasta) > (Select avg(year(curdate())-Sünniaasta) From LAPS); 
11.(5p) Väljasta erinevad sünniaastad ning palju igal aastal lapsi on sündinud. 
SELECT Sünniaasta, count(Sünniaasta) as Sündinud_lapsed FROM LAPS Group by Sünniaasta; 
12.(3p) Leia laste keskmine pikkus. 
SELECT AVG(Pikkus) as Keskmine_pikkus  FROM LAPS; 
13.(2p) Ümarda eelnevalt saadud vastust nii, et oleks üks koht peale  koma . 
SELECT  round(avg(Pikkus),1) as Keskmine_pikkus From LAPS; 
14.(2p) Muuda Siimu sünniaasta, uus sünniaasta on 1996. 
UPDATE  LAPS SET Sünniaasta=1996 WHERE Eesnimi='Siim'; 
15.(2p) Lisa järgmine laps tabelisse LAPS: Ants pikkusega 165, sünniaasta 1996 ja sünnilinn 2. 
INSERT INTO LAPS (Eesnimi, Pikkus, Sünniaasta, SünniLinn) VALUES('Ants','165','1996','2'); 
16.(5p) Leia iga sünniaasta laste keskmine pikkus. 
SELECT Sünniaasta,avg(Pikkus)AS Keskmine_pikkus From LAPS Group by Sünniaasta; 
17.(1p) Käivita järgmised kaks käsku: 
CREATE TABLE LINN (LinnID  integer (4) NOT NULL AUTO_INCREMENT,  
Nimi varchar(40) NOT NULL, 
PRIMARY KEY(LinnID));  
INSERT INTO LINN (Nimi) VALUES ('Tallinn'), ('Tartu'); 
18.(5p) Väljasta kõik tabeli LAPS andmed koos sünnilinnade nimedega. 
SELECT * From LAPS inner join LINN on LINN.LinnID=LAPS.Sünnilinn; 
19.(5p) Väljasta iga linna kohta, mitu last on selles linnas sündinud (linna nimi, laste arv). 
SELECT count(LAPS.Eesnimi)as Sündinud_laste_arv, LINN.Nimi From LINN inner join LAPS on 
LINN.LinnID=LAPS.Sünnilinn Group by Sünnilinn; 
20.(2p) Muuda tabelit Linn lisades uus andmeväli Elanike_arve integer. 
ALTER  TABLE LINN ADD  COLUMN  Elanike_arve INTEGER; 
21.(3p) Lisa Linna tabelisse elanike arvud: Tallinn 400 200 elanikku, Tartu 100 100 elanikku. 
UPDATE LINN set Elanike_arve = 400200 WHERE Nimi = 'Tallinn'; 
UPDATE LINN set Elanike_arve = 100100 WHERE Nimi = 'Tartu'; 
22.(3p) Leia kõige lühem laps. Väljasta lapse nimi ja pikkus. 
SELECT Pikkus, Eesnimi from LAPS WHERE Pikkus = (Select min(Pikkus) From LAPS);