Laboratoare la Baze de Date 1

/*1. Pentru fiecare sectie, numele studentului cu media cea mai mare, si media (aceeasi conditie asupra mediei ca la punctul 5).*/

create table query10_1

SELECT studenti.cods, rezz.medie, studenti.nume

FROM studenti RIGHT JOIN (SELECT avg(nota) as medie, nrmatricol

FROM rezultate

WHERE nota>=5

GROUP BY nrmatricol) AS rezz ON studenti.nrmatricol=rezz.nrmatricol;

create table query5

SELECT studenti.nume, rezz.medie

FROM studenti RIGHT JOIN (SELECT avg(nota) as medie, nrmatricol

FROM rezultate

WHERE nota>=5

GROUP BY nrmatricol) AS rezz ON studenti.nrmatricol=rezz.nrmatricol;

create table query10_2

SELECT query10_1.cods, query10_1.medie AS medie, query10_1.nume AS nume

FROM query10_1 INNER JOIN (

SELECT studenti.cods, max(query5.medie) AS Media

FROM studenti INNER JOIN query5 ON studenti.nume=query5.nume

GROUP BY cods) AS reyy ON (reyy.media=query10_1.medie) AND (reyy.cods=query10_1.cods)

ORDER BY query10_1.cods;

SELECT denumires, nume, medie

FROM sectii INNER JOIN query10_2 ON sectii.cods=query10_2.cods

ORDER BY medie DESC;

laboratoare la baze de date1