SQL upiti nad odsecima i položenim predmetima: 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 šifre i nazive predmeta (SPR, NAZP), uređene u opadajućem redosledu naziva.
2. Prikazati studente (IMES, PREZS) na drugoj ili trećoj godini studija kao i šifre predmeta (SPR) koji su položili.
3. Prikazati studente (IMES, PREZS), nazive predmeta koje su položili (NAZP) i nazive odseka kojima pripadaju (NAZO).
4. Prikazati predmete (NAZP) koje je položio student sa šifrom S2, a nije položio student sa šifrom S5.
5. Sve studente koji pripadaju odseku O4 upisati u narednu godinu.
6. Prikazati odseke (NAZO) i broj studenata koji im pripadaju.
7. Prikazati prva tri slova naziva predmeta (NAZP) čiji studenti imaju prosečnu ocenu veću od 8,2.
8. Za svakog studenta (IDS, IMES, PREZS) prikazati ukupnu prosečnu ocenu na predmetima koje je položio. Ukoliko student nije položio nijedan predmet, umesto prosečne ocene prikazati 0.
9. U tabelu Odsek dodati kolonu SNO (skraćeni naziv odseka), maksimalne dužine 5 znakova, i ograničenje jedinstvene vrednosti na koloni NAZO.
10. Za svaki odsek (NAZO) prikazati ukupan broj različitih predmeta koje su studenti sa tog odseka položili. Ako studenti sa nekog odseka nisu položili nijedan predmet, ispisati 0 na mestu broja predmeta.

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

Opadajući redosled zadaje DESC u ORDER BY.

SELECT SPR, NAZP
FROM Predmet
ORDER BY NAZP DESC;

Upit 2

Godina je u tabeli Student, a šifra predmeta u tabeli Polozio. Uslov na godinu najkraće se piše sa IN.

SELECT S.IMES, S.PREZS, P.SPR
FROM Student S
JOIN Polozio P ON P.IDS = S.IDS
WHERE S.GOD IN (2, 3);

Upit 3

Naziv predmeta je u tabeli Predmet, a naziv odseka u tabeli Odsek, pa se od studenta spaja na obe strane.

SELECT S.IMES, S.PREZS, Pr.NAZP, O.NAZO
FROM Student S
JOIN Polozio Po ON Po.IDS = S.IDS
JOIN Predmet Pr ON Pr.SPR = Po.SPR
JOIN Odsek O ON O.IDO = S.IDO;

Upit 4

Razlika dva skupa predmeta: šifra mora biti među predmetima studenta S2, a ne sme biti među predmetima studenta S5. Mara Maric ima P3, P4 i P5, a Ivan Ivic P4 i P5, pa ostaje Logika.

SELECT NAZP
FROM Predmet
WHERE SPR IN (SELECT SPR FROM Polozio WHERE IDS = 'S2')
  AND SPR NOT IN
    (SELECT SPR FROM Polozio WHERE IDS = 'S5');

Upit 5

Posle naredbe studenti odseka O4 imaju ove godine:

UPDATE Student
SET GOD = GOD + 1
WHERE IDO = 'O4';

Upit 6

LEFT JOIN čuva i odsek bez studenata, Elektrotehniku. Broji se IDS, jer COUNT sa kolonom preskače NULL, pa taj odsek dobija 0.

SELECT O.NAZO, COUNT(S.IDS) AS BROJ_STUDENATA
FROM Odsek O
LEFT JOIN Student S ON S.IDO = O.IDO
GROUP BY O.IDO, O.NAZO;

Upit 7

Prosek po predmetu je uslov nad grupom, pa ide u HAVING, a početak naziva daje SUBSTRING. OCENA je ovde decimalna, pa je prosek tačan i bez pretvaranja. Prosek Analize 1 je tačno 8, pa ona ne prolazi.

SELECT SUBSTRING(Pr.NAZP, 1, 3) AS POCETAK
FROM Predmet Pr
JOIN Polozio Po ON Po.SPR = Pr.SPR
GROUP BY Pr.SPR, Pr.NAZP
HAVING AVG(Po.OCENA) > 8.2;

Upit 8

LEFT JOIN čuva Petra Perica, koji nema položenih ispita. AVG mu daje NULL, a COALESCE to pretvara u 0.

SELECT S.IDS, S.IMES, S.PREZS,
  COALESCE(AVG(P.OCENA), 0) AS PROSEK
FROM Student S
LEFT JOIN Polozio P ON P.IDS = S.IDS
GROUP BY S.IDS, S.IMES, S.PREZS;

Upit 9

Dve izmene iste tabele: nova kolona i ograničenje jedinstvenosti nad postojećom. Svi nazivi odseka su različiti, pa ograničenje može da se doda, a posle njega novi odsek sa nazivom Masinstvo baza odbija.

ALTER TABLE Odsek ADD SNO varchar(5);
ALTER TABLE Odsek
ADD CONSTRAINT Odsek_NAZO_UK UNIQUE (NAZO);

Upit 10

Različite predmete broji COUNT sa DISTINCT, a dva LEFT JOIN-a čuvaju odseke bez studenata i studente bez ispita. Studenti Gradjevine zajedno imaju četiri različita predmeta, a odseci bez položenih ispita dobijaju 0.

SELECT O.NAZO, COUNT(DISTINCT Po.SPR) AS BROJ_PREDMETA
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.