SQL upiti nad bazom biblioteke, knjige i pozajmice: rešen zadatak

Zadatak

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

• Zanr({IDZ, NAZIVZ}, {IDZ})
• Knjiga({IDK, IDZ, NAZIVK, AUTORK, GODK, BRSTRK}, {IDK})
• Clan({IDC, IMEC, PRZC, KATC, OMILJENAKC}, {IDC})
• Pozajmica({IDP, IDC, IDK, VRACENAP}, {IDP})

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

• Knjiga[IDZ] ⊆ Zanr[IDZ]
• Clan[OMILJENAKC] ⊆ Knjiga[IDK]
• Pozajmica[IDC] ⊆ Clan[IDC]
• Pozajmica[IDK] ⊆ Knjiga[IDK]
• dom(KATC) = {D, U, R, P}
• dom(VRACENAP) = {D, N}

Značenje obeležja je sledeće:

• IDZ – identifikaciona oznaka žanra
• NAZIVZ – naziv žanra
• IDK – identifikaciona oznaka knjige
• NAZIVK – naziv knjige
• AUTORK – autor knjige
• GODK – godina izdavanja knjige
• BRSTRK – broj strana knjige
• IDC – identifikaciona oznaka člana
• IMEC – ime člana
• PRZC – prezime člana
• KATC – kategorija člana
• OMILJENAKC – omiljena knjiga člana
• IDP – identifikaciona oznaka pozajmice
• VRACENAP – da li je pozajmica vraćena

Prikazana je pojava nad datom šemom baze podataka:

1. Prikazati knjige (IDK, NAZIVK), sortirane u rastućem redosledu naziva knjige.
2. Prikazati sve različite autore (AUTORK) knjiga čija dužina je veća od 100 strana.
3. Za pozajmice prikazati ime i prezime člana koji je knjigu iznajmio, kao i naziv knjige.
4. Prikazati članove (IMEC, PRZC) koji su barem jednom pozajmili knjigu sa identifikacionim brojem 1, ali nisu nikada pozajmili knjigu sa identifikacionim brojem 4.
5. Prikazati sve knjige (IDK, NAZIVK) koje je pozajmio barem jedan član, kao i broj pozajmljivanja te knjige.
6. Svim knjigama kojima naziv počinje na slovo P povećati broj strana za 10.
7. U tabelu Knjiga dodati kolonu OCENAK (prosečna ocena te knjige) čije vrednosti moraju biti u opsegu od 0 do 10.
8. Prikazati članove (IDC, IMEC, PRZC) koji su pozajmili više od dve različite knjige.
9. Za svaki žanr (IDZ, NAZIVZ) prikazati prosečan broj strana koje imaju knjige tog žanra. Ako za neki žanr ne postoji ni jedna knjiga, kao prosečan broj strana prikazati 0.
10. Za svaki žanr (IDZ, NAZIVZ) prikazati ukupan broj iznajmljivanja knjiga tog žanra. Ako žanr nema ni jednu knjigu ili knjige tog žanra nisu ni jednom pozajmljene prikazati 0.

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 žanrove ili knjige bez ijedne pozajmice, unutrašnji spoj nije dovoljan, pa ide LEFT JOIN. Svaki upit je pokrenut nad skriptama ovog zadatka, a ispod njega je dobijeni rezultat. Tamo gde nema ORDER BY, redosled redova nije zadat.

Upit 1

Redosled zadaje ORDER BY, a rastući je podrazumevan.

SELECT IDK, NAZIVK
FROM Knjiga
ORDER BY NAZIVK;

Upit 2

Isti autor ima više knjiga, pa DISTINCT ostavlja svakog jednom. Knjige sa 100 strana ili manje, ovde kuvari, ne prolaze, pa ni njihovi autori.

SELECT DISTINCT AUTORK
FROM Knjiga
WHERE BRSTRK > 100;

Upit 3

Svaka pozajmica ima po jedan član i jednu knjigu, pa se od tabele Pozajmica spaja na obe strane. Marko Kanazir je istu knjigu pozajmio dva puta, pa je to dva reda.

SELECT C.IMEC, C.PRZC, K.NAZIVK
FROM Pozajmica P
JOIN Clan C ON C.IDC = P.IDC
JOIN Knjiga K ON K.IDK = P.IDK;

Upit 4

Dva uslova nad istim članom, pa svaki ide u svoj podupit. Knjigu 1 pozajmili su Marko Kanazir i Aleksandar Tomic, a Marko je pozajmio i knjigu 4, pa ostaje Aleksandar.

SELECT IMEC, PRZC
FROM Clan
WHERE IDC IN (SELECT IDC FROM Pozajmica WHERE IDK = 1)
  AND IDC NOT IN
    (SELECT IDC FROM Pozajmica WHERE IDK = 4);

Upit 5

Unutrašnji spoj ostavlja samo pozajmljene knjige, a broj pozajmica je broj redova grupe.

SELECT K.IDK, K.NAZIVK, COUNT(*) AS BROJ_POZAJMICA
FROM Knjiga K
JOIN Pozajmica P ON P.IDK = K.IDK
GROUP BY K.IDK, K.NAZIVK;

Upit 6

Naziv koji počinje slovom P traži LIKE sa znakom % na kraju. Posle naredbe te knjige imaju ovoliko strana:

UPDATE Knjiga
SET BRSTRK = BRSTRK + 10
WHERE NAZIVK LIKE 'P%';

Upit 7

Kolona se dodaje naredbom ALTER TABLE, a opseg je ograničenje CHECK. Postojeći redovi dobijaju NULL, a unos ocene 11 baza odbija.

ALTER TABLE Knjiga
ADD OCENAK decimal(4, 2)
  CONSTRAINT Knjiga_OCENAK_CK CHECK (OCENAK BETWEEN 0 AND 10);

Upit 8

Traže se različite knjige, pa se broji COUNT sa DISTINCT. Marko Kanazir ima tri pozajmice, ali samo dve različite knjige, pa ostaje Jovana Lalic.

SELECT C.IDC, C.IMEC, C.PRZC
FROM Clan C
JOIN Pozajmica P ON P.IDC = C.IDC
GROUP BY C.IDC, C.IMEC, C.PRZC
HAVING COUNT(DISTINCT P.IDK) > 2;

Upit 9

LEFT JOIN čuva žanrove bez knjiga. AVG im daje NULL, a COALESCE to pretvara u 0. BRSTRK je celobrojan, a SQL Server bi prosek celih brojeva zaokružio na ceo broj, pa se množi decimalnim brojem.

SELECT Z.IDZ, Z.NAZIVZ,
  COALESCE(AVG(1.0 * K.BRSTRK), 0) AS PROSEK_STRANA
FROM Zanr Z
LEFT JOIN Knjiga K ON K.IDZ = Z.IDZ
GROUP BY Z.IDZ, Z.NAZIVZ;

Upit 10

Dva LEFT JOIN-a čuvaju žanrove bez knjiga i knjige bez pozajmica, a COUNT sa kolonom IDP preskače NULL, pa oni dobijaju 0.

SELECT Z.IDZ, Z.NAZIVZ, COUNT(P.IDP) AS BROJ_POZAJMICA
FROM Zanr Z
LEFT JOIN Knjiga K ON K.IDZ = Z.IDZ
LEFT JOIN Pozajmica P ON P.IDK = K.IDK
GROUP BY Z.IDZ, Z.NAZIVZ;

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