SQL - DBS - cv2

USE MI_Antalik_Dominik
GO

SELECT * FROM student

SELECT priezvisko FROM student

-- pomocou WHERE a skratiek
SELECT st.meno, st.priezvisko, z.body FROM student st, znamka z
WHERE st.id_student=z.id_student

SELECT student.meno, student.priezvisko, znamka.body FROM student, znamka
WHERE student.id_student=znamka.id_student

-- pomocou JOIN, viac používať, aj v teste
SELECT st.meno, st.priezvisko, z.body FROM student st
INNER JOIN znamka z ON st.id_student=z.id_student

-- 3.1
SELECT u.meno, u.priezvisko, p.nazov FROM ucitel u
INNER JOIN vyucujuci v ON u.id_ucitel=v.id_predmet
INNER JOIN predmet p ON p.id_predmet=v.id_predmet

SELECT ucitel.meno, ucitel.priezvisko, predmet.nazov FROM ucitel, predmet, vyucujuci
WHERE ucitel.id_ucitel=vyucujuci.id_ucitel AND predmet.id_predmet=vyucujuci.id_predmet

--3.2
--vnorený select, pomocou WHERE
SELECT ucitel.meno, ucitel.priezvisko FROM ucitel
WHERE NOT EXISTS (SELECT * FROM vyucujuci WHERE vyucujuci.id_ucitel=ucitel.id_ucitel)

--(left) JOIN a IS NULL
SELECT ucitel.meno, ucitel.priezvisko FROM ucitel
LEFT JOIN vyucujuci ON ucitel.id_ucitel=vyucujuci.id_ucitel
WHERE vyucujuci.id_ucitel IS NULL


--3.3
SELECT student.meno, student.priezvisko, znamka.body FROM student, znamka, predmet
WHERE student.id_student=znamka.id_student AND znamka.id_predmet=predmet.id_predmet
AND predmet.nazov='matematika'

SELECT student.meno, student.priezvisko, znamka.body FROM student
INNER JOIN znamka ON student.id_student=znamka.id_student
INNER JOIN predmet ON znamka.id_predmet=predmet.id_predmet
WHERE predmet.nazov='matematika'


--3.4
SELECT student.meno, student.priezvisko, znamka.body, predmet.nazov FROM student
LEFT JOIN znamka ON student.id_student=znamka.id_student
LEFT JOIN predmet ON znamka.id_predmet=predmet.id_predmet


--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