lab4_dbd(NOT DONE)

--a. Show all the products possessing a certain property (along with the
--corresponding categories). The property selection criteria remains
--at your own choice.

SELECT prod.ProductName, categ.categoryname, prop.propertyname, prop.propertyvalue
FROM Products prod, Categories categ, Properties prop, bindproductsproperties bind
WHERE categ.categoryid = prod.categoryid
  AND bind.productid = prod.productid
  AND bind.propertyid = prop.propertyid
  AND prop.propertyvalue = 'Black';


--b. Display a top containing all clients - based on the number of products ordered.

SELECT c.clientid, c.clientname, sum(crt.quantity) as nbOfProducts
FROM clients c, ordereditems crt, orders ord
WHERE c.clientid = ord.clientid
  AND ord.orderId = crt.orderID
GROUP BY c.clientid,c.clientname
ORDER BY nbOfProducts DESC;

--c. Display a top containing all clients - based on the number of categories
--that the ordered products belong to.

SELECT c.clientid, c.clientname, count(distinct ct.categoryid) as nbOfCategories
FROM clients c, ordereditems crt, orders ord, categories ct, products prod
WHERE c.clientid = ord.clientid
  AND ord.orderId = crt.orderID
  AND crt.productid=prod.productid
  AND prod.categoryid = ct.categoryid
GROUP BY c.clientid,c.clientname
ORDER BY nbOfCategories DESC;

--d. Display a top containing all clients - based on the overall value
--of the ordered products.
SELECT c.CLIENTID, c.CLIENTNAME, SUM(pr.PRICE*crt.QUANTITY) as overallValue
FROM clients c, ordereditems crt, orders ord, prices pr, products prod
WHERE c.clientid = ord.clientid
  AND ord.orderId=crt.orderID
  AND crt.productid=prod.productid
  AND prod.PRODUCTID=pr.PRODUCTID
GROUP BY c.clientid, c.clientname
ORDER BY overallValue DESC;

-- e. Show a list of categories containing at least 3 other categories / products
SELECT ct.CATEGORYID, ct.CATEGORYNAME, count(prod.productId) as productnb
FROM categories ct, products prod
WHERE prod.CATEGORYID = ct.CATEGORYID
GROUP BY ct.CATEGORYID, ct.CATEGORYNAME
having count(prod.productId)>=3
UNION
SELECT ct.CATEGORYID, ct.CATEGORYNAME, count(oct.otherCatId) as categNb
FROM categories ct, categories oct
WHERE ct.CATEGORYID = oct.OTHERCATID
GROUP BY ct.CATEGORYID, ct.CATEGORYNAME
having count(oct.OTHERCATID)>=3;

-- f. Show a category (or subcat) containing similary named products:
-- (Cat Name 1, Cat Name 2, Product Name)
SELECT DISTINCT ct1.categoryname, ct2.categoryname, prod1.productname
FROM categories ct1, categories ct2, products prod1, products prod2
WHERE ct1.categoryid = prod1.categoryid  AND ct2.categoryid = prod2.categoryid
AND ct1.categoryname!=ct2.categoryname
AND prod1.productname = prod2.productname ;

--g. Show the total product price (sum) corresponding to the 3 "most recent" orders.

CREATE VIEW MOSTRECENTORDERS AS
SELECT ord.requestdate, ord.orderid
FROM orders ord
WHERE ROWNUM <= 3
ORDER BY ord.req;

SELECT SUM(price.price) AS TOTAL_PRICE
FROM prices price, mostrecentorders mostrec, orders ord
WHERE ord.orderid=mostrec.orderid


CREATE VIEW ULTIMELE_COMENZI(data_com, comanda_id) as
SELECT data_com, comanda_id
FROM comenzi
WHERE ROWNUM <= 3
ORDER BY data_com DESC;

SELECT SUM(prod.pret) AS pret_total
FROM produs prod, ULTIMELE_COMENZI comenzi, produsecomanda prodcom
WHERE prodcom.com_id = comenzi.comanda_id
AND prodcom.prod_id = prod.prod_id;