SQL upiti nad bazom studenata i predmeta: rešen zadatak

Zadatak

Data je šema baze podataka (S, I), gde skup S čine sledeće šeme relacija:

• Odsek({IDO, NAZO}, {IDO})
• Student({IDS, IMES, PREZS, GOD, IDO}, {IDS})
• Predmet({SPR, NAZP, SEM}, {SPR})
• Polozio({IDS, SPR, OCENA}, {IDS+SPR})

a skup I sledeća ograničenja referencijalnog integriteta:

• Student[IDO] ⊆ Odsek[IDO]
• Polozio[IDS] ⊆ Student[IDS]
• Polozio[SPR] ⊆ Predmet[SPR]

Značenje obeležja je sledeće:

• IDO – šifra odseka
• NAZO – naziv odseka
• IDS – šifra studenta
• IMES – ime studenta
• PREZS – prezime studenta
• GOD – godina u koju je upisan student
• SPR – šifra predmeta
• NAZP – naziv predmeta
• SEM – semestar u kom se predaje predmet
• OCENA – ocena sa kojom je student položio predmet

Prikazana je pojava nad datom šemom baze podataka:

1. Prikazati šifru, ime i prezime studenata (IDS, IMES, PREZS), sortirane u rastućem redosledu imena i prezimena.
2. Prikazati različita imena studenata (IMES), koji imaju 10 iz nekog predmeta.
3. Za položene ispite prikazati nazive predmeta (NAZP), imena i prezimena studenata (IMES, PREZS) koji su ih položili i ocenu koju su dobili (OCENA).
4. Prikazati studente (IMES, PREZS) koji su položili neki predmet sa ocenom 10, a nisu položili ni jedan sa ocenom 8.
5. U tabelu Polozio dodati kolonu BODOVI (broj osvojenih bodova na ispitu) koja ne sme da ima vrednost manju od 51.
6. Prikazati predmete (NAZP), koje je položio bar jedan student, i broj studenata koji su ih položili.
7. Svim predmetima čiji naziv počinje sa Analiza uvećati semestar za jedan.
8. Prikazati studente (IMES, PREZS) koji imaju više od dva položena ispita.
9. Za svaki predmet (SPR, NAZP) prikazati prosečnu ocenu na predmetu. Ukoliko predmet nije položio nijedan student, umesto prosečne ocene prikazati 0.
10. Za svaki Odsek (NAZO) prikazati prosečnu ocenu studenata tog odseka. Ako odsek nema studenata ili ako nijedan student odseka nije položio nijedan predmet, ispisati 0 na mestu prosečne ocene.

Rešenje

Šta se traži i kojim postupkom

Svaki upit se sastavlja istim redom: iz kojih tabela dolaze obeležja, kako se te tabele spajaju, koji redovi ostaju i kako se grupišu. Kada zadatak traži i predmete ili odseke bez ijednog položenog ispita, unutrašnji spoj nije dovoljan, pa ide LEFT JOIN. Svaki upit je pokrenut nad tabelama i podacima ovog testa, a ispod njega je dobijeni rezultat. Tamo gde nema ORDER BY, redosled redova nije zadat.

Upit 1

Redosled zadaje ORDER BY: prvo po imenu, pa među istim imenima po prezimenu. Rastući redosled je podrazumevan.

SELECT IDS, IMES, PREZS
FROM Student
ORDER BY IMES, PREZS;

Upit 2

Ime je u tabeli Student, a ocena u tabeli Polozio, pa se spajaju po IDS. Isto ime može da se pojavi više puta, pa DISTINCT ostavlja svako jednom.

SELECT DISTINCT S.IMES
FROM Student S
JOIN Polozio P ON P.IDS = S.IDS
WHERE P.OCENA = 10;

Upit 3

Ocena je u tabeli Polozio, ime i prezime u tabeli Student, a naziv u tabeli Predmet, pa se spajaju sve tri, po spoljnim ključevima iz tabele Polozio.

SELECT Pr.NAZP, S.IMES, S.PREZS, Po.OCENA
FROM Polozio Po
JOIN Student S ON S.IDS = Po.IDS
JOIN Predmet Pr ON Pr.SPR = Po.SPR;

Upit 4

To su dva uslova nad istim studentom, a ne nad istim redom tabele Polozio, pa svaki ide u svoj podupit. Mira Ilic ima i desetke i osmicu, pa ostaje samo Mara Maric.

SELECT IMES, PREZS
FROM Student
WHERE IDS IN (SELECT IDS FROM Polozio WHERE OCENA = 10)
  AND IDS NOT IN
    (SELECT IDS FROM Polozio WHERE OCENA = 8);

Upit 5

Kolona se dodaje naredbom ALTER TABLE, a donja granica je ograničenje CHECK. Postojeći redovi dobijaju NULL, koji CHECK propušta, a unos vrednosti 50 baza odbija.

ALTER TABLE Polozio
ADD BODOVI integer
  CONSTRAINT Polozio_BODOVI_CK CHECK (BODOVI >= 51);

Upit 6

Unutrašnji spoj ostavlja samo predmete koje je neko položio, pa Analiza 2 ne ulazi u rezultat. Grupiše se i po šifri, jer dva predmeta mogu imati isti naziv.

SELECT Pr.NAZP, COUNT(*) AS BROJ_STUDENATA
FROM Predmet Pr
JOIN Polozio Po ON Po.SPR = Pr.SPR
GROUP BY Pr.SPR, Pr.NAZP;

Upit 7

Naziv koji počinje sa Analiza traži LIKE sa znakom % na kraju. Posle naredbe predmeti imaju ove semestre:

UPDATE Predmet
SET SEM = SEM + 1
WHERE NAZP LIKE 'Analiza%';

Upit 8

Broj položenih ispita je broj redova grupe, a uslov nad grupom ide u HAVING, ne u WHERE.

SELECT S.IMES, S.PREZS
FROM Student S
JOIN Polozio P ON P.IDS = S.IDS
GROUP BY S.IDS, S.IMES, S.PREZS
HAVING COUNT(*) > 2;

Upit 9

LEFT JOIN čuva i predmet bez položenih ispita, sa NULL umesto ocene. Funkcija AVG ga preskače i vraća NULL, a COALESCE ga pretvara u 0. OCENA je celobrojna, a SQL Server bi prosek celih brojeva zaokružio na ceo broj, pa se ocena prvo množi decimalnim brojem.

SELECT Pr.SPR, Pr.NAZP,
  COALESCE(AVG(1.0 * Po.OCENA), 0) AS PROSEK
FROM Predmet Pr
LEFT JOIN Polozio Po ON Po.SPR = Pr.SPR
GROUP BY Pr.SPR, Pr.NAZP;

Upit 10

Od odseka se ide preko studenata do ispita, sa dva LEFT JOIN-a, da bi ostali i odseci bez studenata i studenti bez ispita. Elektrotehnika nema studenata, a jedini student Masinstva nema položen ispit, pa oba odseka imaju 0.

SELECT O.NAZO,
  COALESCE(AVG(1.0 * Po.OCENA), 0) AS PROSEK
FROM Odsek O
LEFT JOIN Student S ON S.IDO = O.IDO
LEFT JOIN Polozio Po ON Po.IDS = S.IDS
GROUP BY O.IDO, O.NAZO;

Ako si autor nečega što je ovde i želiš da se ukloni, piši mi na milossaric@outlook.com i uklanjam ga.