Baze podataka 1/Lab 2 semestralni 2020

Postavka

Posmatra se deo sistema prodaje na benzinskim stanicama. Ovaj deo sistema prati benzinske stanice kao i njihove koordinate, ocenu i informacije u regionima gde se nalaze. Na benzinskim stanicama se prodaju proizvodi koje prati tip i naziv. Ako se neki prozivod prodaje na benziskoj stanici onda postoji aktivni cenovnik za taj proizvod. Tom prilikom se prati i datum cenovnika. Proizvod se prodaje na benzinskoj stanici po ceni na osnovu aktivnog cenovnika za taj proizvod na toj benzinskoj stanici. U jednom trenutku maksimalno jedan cenovnik je aktivan za proizvod na benzinskoj stanici. Prilikom prodaje prate se cenovnik po kojem je prodat proizvod, kao i količina i datum prodaje.

U sistemu svi datumi se pamte kao celobrojne veličine u formatu yyyymmdd (yyyy - godina, mm - mesec, dd - dan).

U nastavku je data relaciona šema posmatranog dela baze podataka.

Nije dozvoljeno korišćenje pogleda.

Prvih pet zadataka nosi jedan bod, ostalih pet nose dva boda (maks 15). Dostupni su Cactus fajlovi bez rešenja i sa rešenjem.

Zadaci

Zadatak 1

Napraviti SQL upit koji ispisuje svu prodaju za 2021. godinu pri čemu je količina veća od 10.

Sortirati po Datum rastuće, pa po Kolicina opadajuće.

Rezultat dati u formi: Datum, Kolicina, IdPro

Rešenje

SELECT Datum, Kolicina, IdPro 
FROM Prodaja 
WHERE datum > 20210000 AND datum < 20220000 AND Kolicina > 10 
ORDER BY Datum, Kolicina DESC

Zadatak 2

Napraviti SQL skriptu koja briše svu prodaju koja je starija od 01.01.2021. (datumi pre 2021. godine), a zatim ispisuje svu preostalu prodaju.

Rezutate sortirati po IdPro rastuće.

Rezultat dati u formi: IdPro, Kolicina, Datum, IdCen

Rešenje

DELETE FROM Prodaja WHERE Datum < 20210101;
SELECT IdPro, Kolicina, Datum, idCen FROM Prodaja ORDER BY idPro

Zadatak 3

Napraviti SQL skriptu koja uvodi nov proizvod u bazu podataka, a zatim ispisuje sve proizvode. Proizvod za dodavanje je tipa Gorivo sa nazivom EuroDizel. Uzeti da identifikator proizvoda ima vrednost 1000.

Rezultat sortirati po IdPro opadajuće.

Rezultat dati u formi: IdPro, Tip, Naziv

Rešenje

INSERT INTO proizvod(Tip, Naziv, Idpro) VALUES('Gorivo', 'EuroDizel', 1000);
SELECT Idpro, tip, naziv FROM Proizvod ORDER BY IdPro DESC

Zadatak 4

Napraviti SQL skriptu koja ažurira vrednost svih cenovnika tako što uveća vrednost za 10% ukoliko je cenovnik aktivan ili postavlja na vrednost 0 ukoliko je cenovnik neaktivan, a zatim je potrebno ispisati sve cenovnike.

Rezultat sortirati po IdCen rastuće.

Rezultat dati u formi: IdCen, Datum, IdBS, IdPro, Vrednost, Aktivan

Rešenje

UPDATE Cenovnik SET Vrednost = CASE(Aktivan)
    WHEN 0 THEN 0
    ELSE Vrednost * 1.10
END;

SELECT IdCen, Datum, idBS, IdPro, vrednost, Aktivan FROM Cenovnik ORDER BY idcen

Zadatak 5

Napraviti SQL skriptu koja ako postoji tabela BenStanica izbacuje tabelu BenStanica iz šeme, a zatim formira novu tabelu BenStanica koja treba da ima odgovarajuću strukturu i ograničenja.

Rešenje

DROP TABLE IF EXISTS BenStanica;

CREATE TABLE BenStanica
(
    IdBs INTEGER PRIMARY KEY AUTOINCREMENT,
    Ocena INTEGER CHECK (ocena >= 1 AND ocena <= 10),
    X DOUBLE,
    Y DOUBLE,
    IdReg INTEGER REFERENCES Region(IdReg)
);

Zadatak 6

Napraviti SQL upit koji pronalazi sve aktuelne cene (vrednost) svih proizvoda koji se prodaju na benzinskoj stanici sa identifikatorom 1 za dan 11.01.2021.

Rezultat sortirati po IdPro rastuće.

Rezultat dati u formi: IdPro, Naziv, Cena

Rešenje

SELECT idPro, Proizvod.Naziv, Vrednost AS Cena 
FROM Cenovnik JOIN(Proizvod) USING(IdPro) 
WHERE idBs = 1 AND datum <= 20210111 AND Aktivan = 1
ORDER BY idPro

Zadatak 7

Napraviti SQL upit koji pronalazi benzisku stanicu (ili stanice ako ih ima više) koja je najprofitabillnija u 2021. godini. Profit benzinske stanice se računa kao ukupna vrednost prodaje na toj benzinskoj stanici, pri čemu se vrednost prodaje računa kao proizvod količine i vrednosti sa cenovnika.

Rezultat sortirati po IdBS rastuće.

Rezultat dati u formi: IdBS, Ocena, X, Y

Rešenje

WITH Profit AS
(
    SELECT IdBs, SUM(Vrednost * Prodaja.Kolicina) AS Suma
    FROM Cenovnik JOIN(prodaja) USING(Idpro) JOIN(BenStanica) USING(idBs)
    WHERE prodaja.Datum > 20210000 and prodaja.Datum < 20220000
    GROUP BY idBs
)
SELECT idBs, Ocena, X, Y
FROM BenStanica
WHERE (SELECT Suma FROM Profit WHERE idBs = BenStanica.idBs) = (SELECT MAX(Suma) FROM Profit) ORDER BY idBs

Zadatak 8

Napraviti SQL upit koji pronalazi najmanju razdaljinu dve benzinske stanice. Za računanje razdaljine koristiti Dekartov koordinatni sistem. Rezultat dati u jednom redu sa konkretnom vrednosti. Smatrati da u sistemu uvek postoji barem dve benzinske stanice.

Rezultat dati u formi: Razdaljina

Rešenje

WITH Razdaljina AS (
    SELECT sqrt(power(b2.X - b1.X, 2) + power(b2.Y - b1.Y, 2)) AS 'Razdaljina'
    FROM BenStanica b1, BenStanica b2
    WHERE b1.idBs != b2.idBs AND b1.idBs < b2.idBs
)
SELECT MIN(Razdaljina) AS 'Razdaljina' FROM Razdaljina

Zadatak 9

Napraviti SQL upit koji ispisuje za svaki region koliko ima benzinskih stanica.

Rezultat sortirati po IdReg rastuće.

Rezultat dati u formi: IdReg, Naziv, Broj benzinskih stanica

Rešenje

SELECT idReg, Naziv, COUNT(idBs) AS 'Broj benzinskih stanica' 
FROM Region LEFT JOIN BenStanica USING(idReg) 
GROUP BY IdReg 
ORDER BY idReg

Zadatak 10

Napraviti SQL upit koji prikazuje benzinske stanice koje trenutno prodaju više od 3 različite vrste goriva (Tip proizvoda je Gorivo).

Rezultat sortirati po IdBS rastuće.

Rezultat dati u formi: IdBS, Ocena, X, Y

Rešenje

WITH BenSta AS
(
    SELECT *
    FROM BenStanica JOIN Cenovnik USING(idbs) JOIN Proizvod USING (idpro)
    WHERE Tip = 'Gorivo' AND aktivan = 1
)
SELECT *
FROM BenStanica B
WHERE (SELECT COUNT(*) FROM bensta WHERE idbs = B.idbs) > 3
ORDER BY B.IdBs