SQL-exercise-01

SQL-exercise-01.md

Zadani

Mas tabulku s tymy

INSERT  INTO  `tymy` (`id`, `name`) VALUES
(1, 'Brno'),
(2, 'Praha'),
(3, 'Ostrava'),
(4, 'Plzen');

a tabulku s probehlymi zapasy:

INSERT  INTO  `zapasy` (`id`, `id_domaci`, `id_host`, `goly_domaci`, `goly_host`) VALUES
(1, 1, 2, 2, 1),
(2, 3, 4, 2, 1),
(3, 4, 1, 1, 5),
(4, 2, 3, 3, 2),
(5, 1, 3, 4, 1),
(6, 2, 4, 3, 3);

Vypis, kolik kazdy tym dostal bodu, kdyz bodove hodnoceni zni:

• vitez = 3
• porazeny = 1
• remiza = 2

Reseni

1. Prehledny vypis (pro kontrolu)

SELECT z.id, t1.name domaci, t2.name host, z.goly_domaci, z.goly_host
FROM zapasy z
LEFT JOIN tymy t1 ON t1.id = z.id_domaci
LEFT JOIN tymy t2 ON t2.id = z.id_host;

id	domaci	host	goly_domaci	goly_host
1	Brno	Praha	2	1
2	Ostrava	Plzen	2	1
3	Plzen	Brno	1	5
4	Praha	Ostrava	3	2
5	Brno	Ostrava	4	1
6	Praha	Plzen	3	3

2. Vypis skore pro kazdy tym z kazdeho zapasu

CREATE TEMPORARY TABLE skore
SELECT z.id id_zapasu, t.name tym,
CASE 
    WHEN t.id = z.id_domaci THEN
        CASE
            WHEN z.goly_domaci > z.goly_host THEN 3
            WHEN z.goly_domaci < z.goly_host THEN 1
            ELSE 2
        END
    WHEN t.id = z.id_host THEN 
        CASE
            WHEN z.goly_host > z.goly_domaci THEN 3
            WHEN z.goly_host < z.goly_domaci THEN 1
            ELSE 2
        END
END as skore
FROM tymy t
LEFT JOIN zapasy z ON z.id_domaci = t.id OR z.id_host = t.id;

SELECT * FROM skore;

id_zapasu	tym	skore
1	Brno	3
1	Praha	1
2	Ostrava	3
2	Plzen	1
3	Brno	3
3	Plzen	1
4	Praha	3
4	Ostrava	1
5	Brno	3
5	Ostrava	1
6	Praha	2
6	Plzen	2

Secteni skore

SELECT s.tym, SUM(s.skore) celkove_skore
FROM skore s
GROUP BY s.tym;

tym	celkove_skore
Brno	9
Praha	6
Ostrava	5
Plzen	4

Written with StackEdit.