homeworkBD2.sql

/* Ho provato a fare gli esercizi anche in SQL usando SQLite come db. */

/************* SCHEMA **********************************************/

CREATE TABLE "libri" (
    "isbn" INTEGER NOT NULL PRIMARY KEY,
    "titolo" VARCHAR(100) NOT NULL,
    "editore" VARCHAR(100) NOT NULL,
    "anno" INTEGER,
    "costo" INTEGER
);
CREATE TABLE "scrittori" (
    "cf" INTEGER NOT NULL PRIMARY KEY,
    "nome" VARCHAR(100) NOT NULL,
    "cognome" VARCHAR(100) NOT NULL,
    "nazione" VARCHAR(100) NOT NULL
);
CREATE TABLE "autori" (
    "libro" INTEGER NOT NULL REFERENCES "libri" ("isbn"),
    "scrittore" INTEGER NOT NULL REFERENCES "scrittori" ("cf"),
    PRIMARY KEY ("libro", "scrittore")
);


/************* VALORI INIZIALI *************************************/

INSERT INTO "libri" VALUES
    (1234, "Titolo1", "Editore1", 1999, 234),
    (4245, "Titolo2", "Editore2", 1870, 15),
    (2123, "Novelle Sudamericane", "Feltrinelli", 2000, 10),
    (4355, "Guerra e Pace", "Feltrinelli", 2010, 13);

INSERT INTO "scrittori" VALUES
    (1, "Giovanni", "Moravia", "Frascati"),
    (2, "Francesco", "Totti", "Italia"),
    (3, "Alex", "Del Piero", "Cile");

INSERT INTO "autori" VALUES
    (1234, 1),
    (4245, 1),
    (4245, 2),
    (4355, 2),
    (2123, 2),
    (2123, 3);


/************* QUERY ***********************************************/
/* alcune sono fatte sia con JOIN esplicito che implicito.         */

/* 1) */
SELECT titolo, editore FROM autori, scrittori, libri
WHERE scrittore = cf
  AND libro = isbn
  AND cognome = 'Moravia';

SELECT titolo, editore FROM autori
  JOIN scrittori ON (scrittore = cf)
  JOIN libri ON (libro = isbn)
WHERE cognome = 'Moravia';

/* 2) */
SELECT nome, cognome FROM autori
  JOIN scrittori ON (scrittore = cf)
  JOIN libri ON (libro = isbn)
WHERE
  titolo = 'Novelle Sudamericane' AND
  nazione = 'Cile';

/* 3) */
SELECT titolo FROM libri
WHERE costo > (SELECT costo FROM libri WHERE titolo = 'Guerra e Pace');

/* 4) */
SELECT nome, cognome FROM scrittori
EXCEPT
SELECT nome, cognome FROM autori
  JOIN scrittori ON (scrittore = cf)
  JOIN libri ON (libro = isbn)
WHERE anno > 2000;

/* 5) */
SELECT DISTINCT titolo FROM autori a1, autori a2, libri
WHERE a1.libro = isbn
  AND a1.libro = a2.libro
  AND a1.scrittore != a2.scrittore;

SELECT DISTINCT titolo FROM autori a1
  JOIN libri ON (a1.libro = isbn)
  JOIN autori a2 ON (a1.libro = a2.libro)
WHERE a1.scrittore != a2.scrittore;

/* 6 */
SELECT titolo FROM libri EXCEPT
SELECT DISTINCT titolo FROM autori a1, autori a2, libri
WHERE a1.libro = isbn
  AND a1.libro = a2.libro
  AND a1.scrittore != a2.scrittore;

/* 7 */
SELECT nome, cognome FROM scrittori EXCEPT
SELECT DISTINCT nome, cognome FROM autori
  JOIN libri ON (libro = isbn)
  JOIN scrittori ON (scrittore = cf)
WHERE editore != 'Feltrinelli'

/* 8 */
SELECT nome, cognome FROM scrittori EXCEPT
SELECT DISTINCT nome, cognome FROM autori a1
  JOIN autori a2 ON (a1.scrittore = a2.scrittore)
  JOIN scrittori ON (a1.scrittore = cf)
WHERE a1.libro != a2.libro;

/* 9 */
SELECT scrittore, anno, COUNT(*), avg(cost)
FROM autori, libri WHERE libro = isbn
GROUP BY scrittore, anno;

/* 10 */
SELECT editore, anno FROM (
    SELECT editore, anno, COUNT(*) AS num
    FROM libri GROUP BY editore, anno
) GROUP BY anno HAVING num = MAX(num);