owned this note changed 3 years ago

Podatkovne baze 1 - vaje 25.10.2021


SELECT stolpci, SUM(stolpec), COUNT(*), COUNT(DISTINCT stolpec), AVG(stolpec), ...
FROM tabela
WHERE pogoj
GROUP BY stolpci
HAVING pogoj
ORDER BY stolpci
LIMIT število;

Agregatne funkcije z bazo filmov

  1. Vrnite povprečno oceno filmov iz leta 2019.

    ​​​SELECT AVG(ocena) FROM film
    ​​​WHERE leto = 2019;
    
  2. Za vsak ID osebe vrnite število različnih filmov, pri katerih je sodelovala ta oseba (bodisi kot igralec ali režiser). Ne izpisujte imen oseb.

    ​​​SELECT oseba, COUNT(DISTINCT film) AS število
    ​​​FROM vloga
    ​​​GROUP BY oseba;
    
  3. Vrnite oznake, ki se pojavijo pri vsaj 100 filmih. Prazno oznako (NULL) izpustite.

    ​​​SELECT oznaka FROM film
    ​​​GROUP BY oznaka
    ​​​HAVING COUNT(oznaka) >= 100;
    ​​​
    ​​​SELECT oznaka FROM film
    ​​​WHERE oznaka IS NOT NULL
    ​​​GROUP BY oznaka
    ​​​HAVING COUNT(*) >= 100;
    

SUM in COUNT

Naloga TA ŽA AB PK KK AL JL HL MM AO DR BR AS MS LT TT
1 x x x x x x x x x x x x x x x x
2 x x x x x x x x x x x x x x x x
3 x x x x x x x x x x x x x x x x
4 x x x x x x x x x x x x x x x x
5 x x x x x x x x x x x x x x x x
6 x x x x x x x x x x x x x x x x
7 x x x x x x x x x x x x x x x
8 x x x x x x x x x x x x x x x x

Agregatne funkcije s tabelo nobel

Naloga TA ŽA AB PK KK AL JL HL MM AO DR BR AS MS LT TT
1 x x x x x x x x x x x x x x x x
2 x x x x x x x x x x x x x x x x
3 x x x x x x x x x x x x x x x x
4 x x x x x x x x x x x x x x x x
5 x x x x x x x x x x x x x x x x
6 x x x x x x x x x x x x x x x x
7 x x x x x x x x x x x x x x x x
8 x x x x x x x x x x x x x x x x
9 x x x x x x x x x x x x x x x
11 x x x x x x x x x x x x x x x
12 x x x x x x x x x x x x x x x
Select a repo