-- Create table (Task 1/a)
CREATE TABLE X (
  id NUMBER,
  nazwa VARCHAR2(30),
  data DATE
);

-- Create sequenece (Task 1/b)
CREATE SEQUENCE sek_X;

-- pl/sql block (Task 2)
-- || stands for concat
-- We can access sequence only inside block (between BEGIN and END)
BEGIN
  FOR i IN 1..100 LOOP
    INSERT INTO X VALUES (sek_X.NEXTVAL, 'NAZWA' || sek_X.CURRVAL, SYSDATE);
  END LOOP;
END;

-- Sum of sold for all titles - not using pq/sql but not perfect
SELECT * FROM
  (
    SELECT B.TITLE, SUM(OI.QUANTITY)
    FROM BOOKS B, ORDERITEMS OI
    WHERE B.ISBN = OI.ISBN
    GROUP BY B.TITLE
    ORDER BY SUM(OI.QUANTITY) desc
  )
WHERE ROWNUM <= 3;

-- ???
SELECT B.TITLE, SUM(OI.QUANTITY)
    FROM BOOKS B, ORDERITEMS OI
    WHERE B.ISBN = OI.ISBN
    GROUP BY B.TITLE
    ORDER BY SUM(OI.QUANTITY) desc;

-- Solution provided by teacher is do 3 times select with max while eleminating old results
DECLARE
  CURSOR c_bestsellers IS
    SELECT B.TITLE as title, SUM(OI.QUANTITY) as overall, B.PUBDATE as pub
      FROM BOOKS B, ORDERITEMS OI
      WHERE B.ISBN = OI.ISBN
      GROUP BY B.TITLE, B.PUBDATE
      ORDER BY overall desc;

  cnt NUMBER := 0;
  lastRecord NUMBER;

-- Wrong solution kinda
BEGIN
  FOR i IN c_bestsellers LOOP
    IF lastRecord != null AND lastRecord != i.overall THEN
      cnt := cnt + 1;
    END IF;

    EXIT WHEN cnt = 3;

    INSERT INTO X VALUES (sek_X.NEXTVAL, i.title, i.pub);

    lastRecord := i.overall;
  END LOOP;
END;