--1;1;30
SELECT * FROM Vysledek;
--1;2;20
SELECT * FROM Tanecnik;
--1;3;10
SELECT TOP 10 * FROM Vysledek;

--2;1;20
SELECT * FROM Tanecnik ORDER BY jmeno_tan;
--2;2;20
SELECT * FROM Tanecnik ORDER BY prijmeni_tan;
--2;3;20
SELECT * FROM Tanecnik ORDER BY jmeno_tan, prijmeni_tan;

--3;1;1
SELECT * FROM Tanecnik WHERE jmeno_tan = 'Martina';
--3;2;2
SELECT * FROM Tanecnik WHERE jmeno_tan IN ('Martina','Romana');
--3;3;28
SELECT * FROM Vysledek WHERE celkove_umisteni IN ('1','2','3','4');

--4;1;18
SELECT * FROM Tanecnik WHERE jmeno_tan LIKE '%a%';
--4;2;4
SELECT * FROM Predcvicujici WHERE jmeno LIKE '%a%' AND prijmeni LIKE '%l%';
--4;3;5
SELECT * FROM Soutez WHERE adresa_konani NOT LIKE '%Zlin%';

--5;1;20
SELECT REPLACE(jmeno_tan, 'Martina', 'Martinka') FROM Tanecnik;
--5;2;20
SELECT UPPER(jmeno_tan) FROM Tanecnik;
--5;3;20
SELECT jmeno_tan, LEN(jmeno_tan) AS delka_jmena, id_tanecnik FROM Tanecnik;

--6;1;1
SELECT AVG(pocet_bodu) AS prumerny_pocet_bodu FROM Vysledek;
--6;2;1
SELECT SUM(pocet_bodu) AS suma_poctu_bodu FROM Vysledek;
--6;3;1
SELECT COUNT(DISTINCT typ_sportu) AS pocetsportu FROM Kategorie;

--7;1;19
select jmeno_tan,SUM(id_tanecnik) from Tanecnik group by jmeno_tan;
--7;2;19
select jmeno_tan,SUM(id_tanecnik) from Tanecnik group by jmeno_tan HAVING COUNT(*) > 0;
--7;3;20
SELECT jmeno_tan,prijmeni_tan FROM Tanecnik GROUP BY jmeno_tan,prijmeni_tan HAVING COUNT(*) > 0;

--8;1;31
select p.jmeno_tan, v.pocet_bodu from Prihlaska as p LEFT join Vysledek as v on p.id_prihlasky=v.id_prihlasky;
--8;2;31
select p.jmeno_tan, v.pocet_bodu, k.typ_sportu from Prihlaska as p LEFT join Vysledek as v on p.id_prihlasky=v.id_prihlasky inner join Kategorie as k on k.id_kategorie=p.id_kategorie;
--8;3;31
select p.jmeno_tan, v.pocet_bodu, k.typ_sportu, s.adresa_konani from Prihlaska as p LEFT join Vysledek as v on p.id_prihlasky=v.id_prihlasky inner join Kategorie as k on k.id_kategorie=p.id_kategorie inner join Soutez as s on s.id_souteze=k.id_souteze

--9;1;0
select * from Tanecnik where soutezni_cislo in (select soutezni_cislo from Prihlaska);
--9;2;20
select * from Tanecnik where soutezni_cislo not in (select soutezni_cislo from Prihlaska);
--9;3;31
select jmeno_tan from Prihlaska where (select Count(*) from Vysledek where pocet_bodu > 80) > 7;

--10;1;3
select jmeno_tan,Len(jmeno_tan) from Tanecnik where LEN(jmeno_tan) in (select MIN(LEN(jmeno_tan)) from Tanecnik UNION ALL select MAX(LEN(jmeno_tan)) from Tanecnik);
--10;2;30
SELECT * FROM Vysledek WHERE id_prihlasky not IN (SELECT p.id_prihlasky FROM Prihlaska AS p INNER JOIN Kategorie AS k ON p.id_kategorie = k.id_kategorie
WHERE k.typ_sportu LIKE '%Zumba%' GROUP BY p.id_prihlasky HAVING COUNT(*) > 2);
--10;3;30
SELECT * FROM Vysledek WHERE id_prihlasky in (SELECT id_prihlasky FROM Prihlaska GROUP BY id_prihlasky  HAVING COUNT(*) >= 1);