SQL requests/laps, loom

--Creating tables
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)
);

-- Showing data from table laps
select * from laps;
-- Adding data to a table
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);

-- Show all
select * from laps;
select * from loom;

-- Show names only
select nimi from laps;

-- Show only names starting with the letter T
select nimi from laps
where nimi LIKE '%T%';

-- Show two columns name and length
select nimi, pikkus from laps;

-- Names starting with T and length more than 130
select nimi, pikkus from laps
where nimi like 'T%' AND pikkus > 130;

-- Length is between (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 Sorting
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 
-- average only for kid born after 2005
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; --increasing the height of all children by 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 Searches for the three tallest children on the laps table who live in Tartu
select top 3 * from laps
where synnilinn = 'Tartu'
order by pikkus DESC;

-- 2 samples from even years between 2007 and 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 sentences based on 2 tables
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;

-- Setting a shorter for tables
select lo.nimi, la.nimi, la.synniaasta
from laps la inner join loom lo
on la.lapsID = lo.lapsID;