USE AdventureWorksOBP
GO

--ISHOD 4

--Z1
SELECT * FROM Proizvod 
WHERE Proizvod.MinimalnaKolicinaNaSkladistu<100 
ORDER BY Boja, CijenaBezPDV DESC

--Z2
UPDATE Stavka SET PopustUPostocima=PopustUPostocima*2, CijenaPoKomadu=15 WHERE Kolicina=34

--Z3
INSERT INTO Racun VALUES('2023-07-06', 'SO75124', 3, NULL, NULL, NULL)
INSERT INTO Stavka VALUES(75124, 2, 826, 200, 0.30, 280)

--Z4
--ovdje sam rjesio podupitom jer nije preciziran postupak
DELETE FROM Stavka WHERE RacunID=43659 AND IDStavka=(SELECT MAX(IDStavka) FROM Stavka WHERE RacunID=43659)

--Z5
UPDATE Racun SET Komentar=NULL WHERE KomercijalistID IS NULL AND KreditnaKarticaID IS NULL

--Z6
DELETE FROM Stavka WHERE RacunID IN (SELECT IDRacun FROM Racun WHERE KupacID=153)
DELETE FROM Racun WHERE KupacID=153
DELETE FROM Kupac WHERE Ime='Mari' AND Prezime='Caldwell'

--Z7
SELECT IDRacun, DatumIzdavanja, BrojRacuna, KupacID, KomercijalistID, KreditnaKarticaID, Komentar FROM Racun INNER JOIN KreditnaKartica ON Racun.KreditnaKarticaID=KreditnaKartica.IDKreditnaKartica WHERE KreditnaKartica.Tip='Diners' OR KreditnaKartica.Tip='MasterCard'

--Z8
INSERT INTO Grad VALUES('Manila', NULL)
INSERT INTO Drzava VALUES('Danska')
SELECT * FROM Grad FULL OUTER JOIN Drzava ON Drzava.IDDrzava=Grad.DrzavaID WHERE DrzavaID IS NULL OR IDGrad IS NULL

--Z9
SELECT Racun.BrojRacuna, Kupac.Ime, Kupac.Prezime FROM Racun RIGHT OUTER JOIN Kupac ON Racun.KupacID=Kupac.IDKupac ORDER BY BrojRacuna ASC

--Z10!
SELECT BrojRacuna, Proizvod.Naziv, Stavka.UkupnaCijena FROM Racun LEFT OUTER JOIN Stavka ON Racun.IDRacun=Stavka.RacunID LEFT OUTER JOIN Proizvod ON Stavka.ProizvodID=Proizvod.IDProizvod

--ISHOD 6.

--Z1
SELECT Proizvod.Naziv, 
(SELECT SUM(UkupnaCijena) FROM Stavka WHERE Stavka.ProizvodID=Proizvod.IDProizvod) AS 'Ukupna zarada'
FROM Proizvod

--Z2
SELECT DISTINCT Komercijalist.Ime, Komercijalist.Prezime
FROM Komercijalist
WHERE Komercijalist.IDKomercijalist IN
(SELECT Racun.KomercijalistID FROM Racun WHERE Racun.IDRacun IN
(SELECT Stavka.RacunID FROM Stavka WHERE Stavka.ProizvodID IN
(SELECT IDProizvod FROM Proizvod WHERE Proizvod.CijenaBezPDV>3000)))

--Z3
SELECT DISTINCT KreditnaKartica.Broj AS 'Broj kartice',
(SELECT COUNT(*) FROM Racun WHERE KreditnaKartica.IDKreditnaKartica=Racun.KreditnaKarticaID) AS 'Broj izdanih racuna'
FROM KreditnaKartica
ORDER BY [Broj izdanih racuna] DESC

--Z4
SELECT Q.Naziv FROM (SELECT * FROM Drzava D WHERE(SELECT COUNT(*) FROM Grad WHERE Grad.DrzavaID=D.IDDrzava)>(SELECT COUNT(*) FROM Grad)/(SELECT COUNT(*) FROM Drzava)) AS Q
