SQL - DBS - cv4

    -- 3.6


    SELECT s.prijmeni , COUNT(z.id_predmet) AS pocet, AVG(z.body) AS prumer , SUM(z.body) AS soucet, MIN(z.body) AS minimum, MAX(z.body) maximum FROM student s
    INNER JOIN znamka z ON z.id_student = s.id_student
    GROUP BY s.prijmeni



    -- 3.7

    SELECT u.jmeno+' '+u.prijmeni AS 'Jmeno ucitele', z.body, p.nazev, s.jmeno+' '+s.prijmeni AS 'Jmeno studenta'   FROM ucitel u
    INNER JOIN znamka z ON z.id_ucitel = u.id_ucitel
    INNER JOIN student s ON s.id_student = z.id_student
    INNER JOIN predmet p ON p.id_predmet = z.id_predmet





    -- 3.8

    SELECT u.jmeno+' '+u.prijmeni AS 'Jmeno ucitele' FROM ucitel u
    INNER JOIN znamka z ON z.id_ucitel = u.id_ucitel
    INNER JOIN student s ON s.id_student = z.id_student
    INNER JOIN predmet p ON p.id_predmet = z.id_predmet
    WHERE s.jmeno = 'Jarda'
    GROUP BY u.jmeno+' '+u.prijmeni


    -- 4.1

    CREATE VIEW prehled
    AS
    SELECT u.jmeno+' '+u.prijmeni AS 'Jmeno_ucitele', z.body, p.nazev, s.jmeno+' '+s.prijmeni AS 'Jmeno_studenta'   FROM ucitel u
    INNER JOIN znamka z ON z.id_ucitel = u.id_ucitel
    INNER JOIN student s ON s.id_student = z.id_student
    INNER JOIN predmet p ON p.id_predmet = z.id_predmet

    DROP VIEW prehled

    SELECT * FROM prehled

    -- 4.2

    SELECT jmeno_ucitele, body FROM prehled
    WHERE jmeno_studenta LIKE '%Novak%'

    -- 4.3

    SELECT jmeno_ucitele, nazev, body FROM prehled
    WHERE jmeno_studenta LIKE '%Novak%' OR jmeno_studenta LIKE '%Pořízek%'


    -- 4.4

    SELECT jmeno_studenta, body, nazev FROM prehled
    WHERE (nazev = 'matematika' AND body >= 60) OR (nazev = 'chemie' AND body <= 20)