SQL pärimine/laps, loom

-- Looma tabel
CREATE TABLE laps(
    lapsID int not null PRIMARY KEY identity(1,1),
    nimi varchar(40) not null unique,
    pikkus smallint,
    synniaasta int null,
    synnilinn varchar(15)
);

-- näita koike lastel
select * from laps;
--lisa teave tabelile
insert into laps (nimi,synnilinn,pikkus,synniaasta)
values 
 ('TEST0', 'Tallin', 172, 2000), 
 ('TEST1', 'Tartu', 172, 2001), 
 ('TEST2', 'Parnu', 172, 2002), 
 ('TEST3', 'Johvi', 172, 2003), 
 ('TEST4', 'Tallin', 172, 2004), 
 ('TEST5', 'Tartu', 172, 2005), 
 ('TEST6', 'Parnu', 172, 2006), 
 ('TEST7', 'Johvi', 172, 2007), 
 ('TEST8', 'Tallin', 172, 2008), 
 ('TEST9', 'Tartu', 172, 2009), 
 ('TEST10', 'Parnu', 172, 2010), 
 ('TEST11', 'Johvi', 172, 2011), 
 ('TEST12', 'Tallin', 172, 2012), 
 ('TEST13', 'Tartu', 172, 2013), 
 ('TEST14', 'Parnu', 172, 2014);

CREATE TABLE loom(
    loomID int not null PRIMARY KEY identity(1,1),
    nimi varchar(40) not null,
    kaal smallint,
    lapsID int,
	FOREIGN KEY (lapsID) REFERENCES laps(lapsID)
);

insert into loom (nimi,kaal,lapsid)
values 
   ('Kaas Mura',6,1),
   ('Kaas Vassily',6,2),
   ('äHiir Little',1,2),
   ('Hiir Maks', 1, 4),
   ('Koer Maks', 2, 5);

-- näita koike
select * from laps;
select * from loom;

-- näita ainult nimed
select nimi from laps;

-- näita ainult nimed mis alguvad T tähega
select nimi from laps
where nimi LIKE '%T%';

-- näita kaks veergu nimi ja pikkus
select nimi, pikkus from laps;

-- nimid algavad T tähega ja pikkus rohkem kui 130
select nimi, pikkus from laps
where nimi like 'T%' AND pikkus > 130;

-- pikkus on vahemikus (110;130)
select nimi, pikkus from laps
where pikkus >= 110 and pikkus <= 130;

select nimi, pikkus from laps
where pikkus between 110 and 130;

-- 2 sorteermine
SELECT nimi, pikkus FROM laps ORDER by nimi DESC;
SELECT nimi, pikkus FROM laps ORDER by nimi ASC;

SELECT nimi, pikkus FROM laps ORDER by nimi, pikkus;

-- 5 
--keskmine ainult lastele, kes on sündinud pärast 2005. aastat
SELECT AVG(pikkus) AS keskmine_pikkus FROM laps WHERE synniaasta > 2005;

SELECT SUM(pikkus) AS max_pikkusFROM from laps WHERE synniaasta = 2000;

SELECT MIN(pikkus)AS min_pikkus FROM laps WHERE synniaasta = 2000;

SELECT MAX(pikkus)AS max_pikkus FROM laps WHERE synniaasta = 2000;

SELECT COUNT(pikkus) AS count_pikkusFROM from laps WHERE synniaasta = 2000;

-- 6
select * from laps;
UPDATE laps
SET pikkus=pikkus*1.1; -- tõsta kõigi laste pikkust 10%

select * from laps;

-- 7
select * from laps;

update laps
SET pikkus = 158
where synniaasta = 2000;

select * from laps;

-- 8
select * from laps
where pikkus < 150 and synniaasta = 2000;

-- 9 SQL SERVER
select top 3 * from laps order by pikkus DESC; 

-- 9 PHPmyAdmin SERVER
select * from laps order by pikkus DESC limit 3; 

-- 10
-- 1 Otsib kolme kõige pikemat last süldilaualt, kes elavad Tartus
select top 3 * from laps
where synnilinn = 'Tartu'
order by pikkus DESC;

-- 2 näidist paarisaastatest aastatel 2007–2017.
select * from laps
where synniaasta % 2 = 0 and synnilinn = 'Johvi'
and  ( synniaasta > 2007 or synniaasta < 2017  );

-- 3
select lo.nimi,la.nimi,la.synnilinn from laps la inner join loom lo
on la.lapsID = lo.lapsID
where la.synnilinn = 'Tallin' ;

-- select laused 2 tabeli põhjal
select * from laps, loom

select * from laps, loom
where laps.lapsID = loom.lapsID

select loom.nimi, laps.nimi, laps.synniaasta from laps, loom
where laps.lapsID = loom.lapsID;

-- INNER JOIN -
select loom.nimi, laps.nimi, laps.synniaasta from laps inner join loom
on laps.lapsID = loom.lapsID;

-- Tabelite jaoks lühema määramine
select lo.nimi, la.nimi, la.synniaasta
from laps la inner join loom lo
on la.lapsID = lo.lapsID;