CREATE TABLE Ksiazki2004(
ISBN NUMBER,
Cena_jedn NUMBER,
rok_wyd NUMBER,
ilosc NUMBER,
PRIMARY KEY(ISBN)
);

CREATE TABLE Zam_Ksiazki2004(
num_klienta NUMBER,
ISBN NUMBER REFERENCES Ksiazki2004(ISBN),
ilosc_zam NUMBER,
PRIMARY KEY(num_klienta, ISBN)
);

INSERT INTO Ksiazki2004 VALUES(100,23,1999,20);
INSERT INTO Ksiazki2004 VALUES(1001,15,2001,3);
INSERT INTO Ksiazki2004 VALUES(102,99,1990,5);
INSERT INTO Ksiazki2004 VALUES(233,49,2010,13);
INSERT INTO Ksiazki2004 VALUES(400,140,2014,31);
INSERT INTO Ksiazki2004 VALUES(500, 69,2003,10);
INSERT INTO Ksiazki2004 VALUES(600,79,2003,15);

INSERT INTO Zam_Ksiazki2004 VALUES(1,1001,1);
INSERT INTO Zam_Ksiazki2004 VALUES(1,233,1);
INSERT INTO Zam_Ksiazki2004 VALUES(2,100,2);
INSERT INTO Zam_Ksiazki2004 VALUES(3,100,1);
INSERT INTO Zam_Ksiazki2004 VALUES(4,400,4);
INSERT INTO Zam_Ksiazki2004 VALUES(4,233,1);
INSERT INTO Zam_Ksiazki2004 VALUES(5,500,1);


--b)
SELECT num_klienta, SUM(ilosc_zam)
FROM Zam_Ksiazki2004, Ksiazki2004
WHERE Zam_Ksiazki2004.ISBN=Ksiazki2004.ISBN AND Ksiazki2004.rok_wyd = 2003
GROUP BY num_klienta;

-c)
CREATE VIEW Zamowienia2004 AS
SELECT Ksiazki2004.ISBN, rok_wyd, cena_jedn
FROM Ksiazki2004
WHERE Ksiazki2004.ISBN NOT IN (SELECT Zam_Ksiazki2004.ISBN
                          FROM Zam_Ksiazki2004)
GROUP BY Ksiazki2004.ISBN, rok_wyd, cena_jedn;


DROP VIEW Zamowienia2004;
SELECT * FROM Zamowienia2004;