SELECT 
    MAX(nume) AS nume_max,
    MIN(nume) AS nume_min,
    COUNT(*) AS nr_stud
FROM studenti;
--sau echivalent: 
SELECT
    (SELECT MAX(nume) FROM studenti) AS nume_max,
    (SELECT MIN(nume) FROM studenti) AS media_min,
    (SELECT COUNT(*) FROM studenti) AS nr_stud
FROM dual;
 
--CASE in lista de coloane
SELECT nume,prenume,
      CASE sectia
            WHEN 1
            THEN 'Matematica'
            WHEN 2
            THEN 'Informatica'
            WHEN 3
            THEN 'Matematica informatica'
            ELSE 'Alta'
         END AS sectia
FROM studenti,sectii where studenti.sectia=sectii.cod
ORDER BY 3,1,2;

--CASE in lista de coloane
SELECT nume,prenume,media,
      CASE 
            WHEN media BETWEEN 9.5 and 10 THEN 'Merit'
            WHEN media BETWEEN 8 and 9.49 THEN 'Studiu'
            ELSE '-'
      END AS bursa
FROM infstud
ORDER BY 1,2;

--doua nivele de agregare
select sectia,avg(media) as mg from infstud group by sectia
union
select 0,max(avg(media)) from infstud group by sectia;