Базе података 1/Лаб 2 семестрални 2020

Поставка

Посматра се део система продаје на бензинским станицама. Овај део система прати бензинске станице као и њихове координате, оцену и информације у регионима где се налазе. На бензинским станицама се продају производи које прати тип и назив. Ако се неки прозивод продаје на бензиској станици онда постоји активни ценовник за тај производ. Том приликом се прати и датум ценовника. Производ се продаје на бензинској станици по цени на основу активног ценовника за тај производ на тој бензинској станици. У једном тренутку максимално један ценовник је активан за производ на бензинској станици. Приликом продаје прате се ценовник по којем је продат производ, као и количина и датум продаје.

У систему сви датуми се памте као целобројне величине у формату yyyymmdd (yyyy - година, mm - месец, dd - дан).

У наставку је дата релациона шема посматраног дела базе података.

Није дозвољено коришћење погледа.

Првих пет задатака носи један бод, осталих пет носе два бода (макс 15). Доступни су Cactus фајлови без решења и са решењем.

Задаци

Задатак 1

Направити SQL упит који исписује сву продају за 2021. годину при чему је количина већа од 10.

Сортирати по Datum растуће, па по Kolicina опадајуће.

Резултат дати у форми: Datum, Kolicina, IdPro

Решење

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

Задатак 2

Направити SQL скрипту која брише сву продају која је старија од 01.01.2021. (датуми пре 2021. године), а затим исписује сву преосталу продају.

Резутате сортирати по IdPro растуће.

Резултат дати у форми: IdPro, Kolicina, Datum, IdCen

Решење

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

Задатак 3

Направити SQL скрипту која уводи нов производ у базу података, а затим исписује све производе. Производ за додавање је типа Gorivo са називом EuroDizel. Узети да идентификатор производа има вредност 1000.

Резултат сортирати по IdPro опадајуће.

Резултат дати у форми: IdPro, Tip, Naziv

Решење

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

Задатак 4

Направити SQL скрипту која ажурира вредност свих ценовника тако што увећа вредност за 10% уколико је ценовник активан или поставља на вредност 0 уколико је ценовник неактиван, а затим је потребно исписати све ценовнике.

Резултат сортирати по IdCen растуће.

Резултат дати у форми: IdCen, Datum, IdBS, IdPro, Vrednost, Aktivan

Решење

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

Задатак 5

Направити SQL скрипту која ако постоји табела BenStanica избацује табелу BenStanica из шеме, а затим формира нову табелу BenStanica која треба да има одговарајућу структуру и ограничења.

Решење

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)
);

Задатак 6

Направити SQL упит који проналази све актуелне цене (вредност) свих производа који се продају на бензинској станици са идентификатором 1 за дан 11.01.2021.

Резултат сортирати по IdPro растуће.

Резултат дати у форми: IdPro, Naziv, Cena

Решење

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

Задатак 7

Направити SQL упит који проналази бензиску станицу (или станице ако их има више) која је најпрофитабиллнија у 2021. години. Профит бензинске станице се рачуна као укупна вредност продаје на тој бензинској станици, при чему се вредност продаје рачуна као производ количине и вредности са ценовника.

Резултат сортирати по IdBS растуће.

Резултат дати у форми: IdBS, Ocena, X, Y

Решење

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

Задатак 8

Направити SQL упит који проналази најмању раздаљину две бензинске станице. За рачунање раздаљине користити Декартов координатни систем. Резултат дати у једном реду са конкретном вредности. Сматрати да у систему увек постоји барем две бензинске станице.

Резултат дати у форми: Razdaljina

Решење

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

Задатак 9

Направити SQL упит који исписује за сваки регион колико има бензинских станица.

Резултат сортирати по IdReg растуће.

Резултат дати у форми: IdReg, Naziv, Broj benzinskih stanica

Решење

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

Задатак 10

Направити SQL упит који приказује бензинске станице које тренутно продају више од 3 различите врсте горива (Tip производа је Gorivo).

Резултат сортирати по IdBS растуће.

Резултат дати у форми: IdBS, Ocena, X, Y

Решење

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