--ZAD.1
--A
SELECT  P.NAME,
        SUM(B.ISBN) AS ILOSC,
        ROUND(AVG(B.RETAIL),2) AS SREDNIA
FROM PUBLISHER P
INNER JOIN BOOKS B ON B.PUBID = P.PUBID
GROUP BY P.NAME, P.PUBID
UNION ALL
SELECT  'TOTAL',
        SUM(B.ISBN) AS ILOSC,
        ROUND(AVG(B.RETAIL),2) AS SREDNIA
FROM BOOKS B;
--B
SELECT  DECODE(P.NAME,NULL,'TOTAL',P.NAME),
        SUM(B.ISBN) AS ILOSC,
        ROUND(AVG(B.RETAIL),2) AS SREDNIA
FROM PUBLISHER P
INNER JOIN BOOKS B ON B.PUBID = P.PUBID
GROUP BY GROUPING SETS ((P.NAME, P.PUBID),());
--C
SELECT  DECODE(P.NAME,NULL,'TOTAL',P.NAME),
        SUM(B.ISBN) AS ILOSC,
        ROUND(AVG(B.RETAIL),2) AS SREDNIA
FROM PUBLISHER P
INNER JOIN BOOKS B ON B.PUBID = P.PUBID
GROUP BY ROLLUP ((P.NAME, P.PUBID));
--ZAD.2
SELECT  C.FIRSTNAME||' '||C.LASTNAME AS CUSTOMER,
        O.ORDER#,
        SUM(OI.QUANTITY*B.RETAIL),
        SUM(OI.QUANTITY)
FROM CUSTOMERS C
INNER JOIN ORDERS O ON O.CUSTOMER# = C.CUSTOMER#
INNER JOIN ORDERITEMS OI ON OI.ORDER# = O.ORDER#
INNER JOIN BOOKS B ON B.ISBN = OI.ISBN
GROUP BY GROUPING SETS ((C.FIRSTNAME, C.LASTNAME, C.CUSTOMER#, O.ORDER#), (C.FIRSTNAME, C.LASTNAME, C.CUSTOMER#), ());
--ZAD.3
SELECT  B.CATEGORY,
        EXTRACT(DAY FROM O.ORDERDATE) AS DZIEN,
        SUM(OI.QUANTITY*(B.RETAIL-B.COST)) AS ZYSK
FROM ORDERS O
INNER JOIN ORDERITEMS OI ON OI.ORDER# = O.ORDER#
INNER JOIN BOOKS B ON B.ISBN = OI.ISBN
WHERE EXTRACT(YEAR FROM O.ORDERDATE) = 2005 AND EXTRACT(MONTH FROM O.ORDERDATE) = 4
GROUP BY GROUPING SETS ((B.CATEGORY, EXTRACT(DAY FROM O.ORDERDATE)), B.CATEGORY)
ORDER BY 2,1;
--ZAD.4
SELECT  EXTRACT(YEAR FROM O.ORDERDATE) AS ROK,
        EXTRACT(MONTH FROM O.ORDERDATE) AS MIESIAC,
        C.ZIP AS KOD_POCZTOWY,
        SUM(OI.QUANTITY) AS ILOSC,
        ROUND(AVG(OI.QUANTITY*B.RETAIL),2) AS SREDNIA
FROM CUSTOMERS C
INNER JOIN ORDERS O ON O.CUSTOMER# = C.CUSTOMER#
INNER JOIN ORDERITEMS OI ON OI.ORDER# = O.ORDER#
INNER JOIN BOOKS B ON B.ISBN = OI.ISBN
GROUP BY ROLLUP (EXTRACT(YEAR FROM O.ORDERDATE), EXTRACT(MONTH FROM O.ORDERDATE), C.ZIP);
--ZAD.5
SELECT  TO_CHAR(O.ORDERDATE,'MM-YYYY') AS DATA,
        A.FNAME||' '||A.LNAME AS AUTOR,
        SUM(OI.QUANTITY) AS ILOSC
FROM AUTHOR A
INNER JOIN BOOKAUTHOR BA ON BA.AUTHORID = A.AUTHORID
INNER JOIN BOOKS B ON B.ISBN = BA.ISBN
INNER JOIN ORDERITEMS OI ON OI.ISBN = B.ISBN
INNER JOIN ORDERS O ON O.ORDER# = OI.ORDER#
GROUP BY CUBE (TO_CHAR(O.ORDERDATE,'MM-YYYY'), (A.FNAME, A.LNAME, A.AUTHORID));