SQL - DBS - cv3

USE MI_Antalik_Dominik
GO

--3.5--

SELECT nazov, COUNT(znamka.id_predmet) AS pocet, avg(znamka.body) AS priemer, SUM(znamka.body) AS sucet, MIN(znamka.body) AS minimum, MAX(znamka.body) AS maximum FROM znamka
INNER JOIN predmet ON znamka.id_predmet=predmet.id_predmet
GROUP BY predmet.nazov

--3.6--
SELECT s.priezvisko, COUNT(z.id_predmet) AS pocet, AVG(z.body) AS priemer, SUM(z.body) AS sucet, MIN(z.body) AS minimum, MAX(z.body) AS maximum FROM student s
INNER JOIN znamka z ON z.id_student = s.id_student
GROUP b s.priezvisko

--3.7--
SELECT u.meno+' '+u.priezvisko AS 'Jmeno ucitele', z.body AS 'body', p.nazov AS 'predmet', s.meno+' '+s.priezvisko AS 'Jmeno studenta' FROM ucitel u
INNER JOIN znamka z ON z.id_ucitel=u.id_ucitel
INNER JOIN predmet p ON p.id_predmet=z.id_predmet
INNER JOIN student s ON s.id_student=z.id_student

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

--4.1 -- pohľady
CREATE VIEW prehled
AS
SELECT u.meno+' '+u.priezvisko AS 'Jmeno_ucitele', z.body AS 'body', p.nazov AS 'nazov', s.meno+' '+s.priezvisko AS 'Jmeno_studenta' FROM ucitel u
INNER JOIN znamka z ON z.id_ucitel=u.id_ucitel
INNER JOIN predmet p ON p.id_predmet=z.id_predmet
INNER JOIN student s ON s.id_student=z.id_student

DROP VIEW prehled
SELECT * FROM prehled

--4.2--
SELECT Jmeno_ucitele, body FROM prehled
WHERE Jmeno_studenta LIKE '%Novák%'

--4.3--
SELECT Jmeno_ucitele, nazov, body FROM prehled
WHERE Jmeno_studenta LIKE '%Novák%' OR jmeno_studenta LIKE '%Pořizek%'

--4.4--
SELECT jmeno_studenta, nazov, body FROM prehled
WHERE (nazov = 'matematika' AND body >= 60) OR (nazov = 'chémia' AND body <= 20)