Libri

Dato il seguente schema relazionale:

libro (isbn, genere, numero_pagine, prezzo, casa_editrice)
casa_editrice(nome, città, telefono)
autore(nome, cognome, nazionalità, data_nascita, data_morte)
ha_scritto(isbn, nome, cognome)

dopo aver disegnato il modello E-R rispondere alle seguenti interrogazioni:

1. Elencare gli autori vivi in ordine di cognome e nome

2. Visualizzare nome e cognome di chi ha scritto “ZANNA BIANCA”

3. Elencare i libri di autori russi che costano più di 20 euro

4. Contare e visualizzare quanti libri di autori francesi sono presenti in libreria

5. Stampare i libri di autori francesi

6. Trovare la somma dei costi dei libri gialli

7. Visualizzare il costo minimo tra i libri di narrativa

8. Visualizzare il numero di autori per ogni nazionalità

Diagramma ER

Modello fisico

CREATE TABLE casa_editrice(
	nome VARCHAR(20) PRIMARY KEY,
	citta VARCHAR(30) NOT NULL,
	telefono INT(10) NOT NULL
)

CREATE TABLE libro(
	isbn CHAR(13) PRIMARY KEY,
	genere CHAR(1) NOT NULL,
	numero_pagine INT(4) NOT NULL,
	prezzo DOUBLE NOT NULL,
	casa_editrice VARCHAR(20) REFERENCES casa_editrice(nome)
)

CREATE TABLE autore(
	nome VARCHAR(30),
	cognome VARCHAR(30),
	nazionalita VARCHAR(20) NOT NULL DEFAULT 'Italia',
	data_nascita DATE NOT NULL,
	data_morte DATE,
	PRIMARY KEY (nome, cognome)
)

CREATE TABLE ha_scritto(
	nome VARCHAR(30) REFERENCES autore(nome),
	cognome VARCHAR(30) REFERENCES autore(cognome),
	isbn CHAR(13) REFERENCES libro(isbn),
	PRIMARY KEY (nome, cognome, isbn)
)

ALTER TABLE libro
ADD title VARCHAR(30)

Interrogazioni

1. Elencare gli autori vivi in ordine di cognome e nome

   SELECT *
   FROM autore
   WHERE data_morte=null
   ORDER BY cognome, note ASC

2. Visualizzare nome e cognome di chi ha scritto “ZANNA BIANCA”

   SELECT nome
   FROM ha_scritto h, libro l
   WHERE l.titolo="ZANNA BIANCA" AND h.isbn=l.isbn

3. Elencare i libri di autori russi che costano più di 20 euro

   SELECT l.*
   FROM libro l, autore a, ha_scritto h
   WHERE l.isbn=h.isbn AND h.nome=a.nome AND h.cognome=a.cognome AND nazionalita='Russia' AND l.prezzo>20

4. Contare e visualizzare quanti libri di autori francesi sono presenti in libreria

   SELECT COUNT(h.*)
   FROM ha_scritto h, autore a
   WHERE h.nome=a.nome AND h.cognome=a.cognome AND nazionalita='Francia'

5. Stampare i libri di autori francesi

   SELECT l.*
   FROM ha_scritto h, autore a, libro l
   WHERE h.nome=a.nome AND h.cognome=a.cognome AND h.isbn=l.isbn AND nazionalita='Francia'

6. Trovare la somma dei costi dei libri gialli

   SELECT SUM(prezzo)
   FROM libro
   WHERE l.genere='giallo'

7. Visualizzare il costo minimo tra i libri di narrativa

   SELECT MIN(prezzo)
   FROM libro
   WHERE l.genere='narrativa'

8. Visualizzare il numero di autori per ogni nazionalità

   SELECT COUNT(*)
   FROM autore
   GROUP BY nazionalita

9. Trovare i libri pubblicati da case editrici veneziane e scritti da autori italiani vivi

   SELECT l.*
   FROM libro l, casa_editrice c, ha_scritto h, autore a
   WHERE h.nome=a.nome AND h.cognome=a.cognome AND h.isbn=l.isbn AND l.casa_editrice=c.nome AND c.citta='Venezia'  AND a.nazionalita='Italia' AND a.data_morte=null

10. Pubblicare gli autori il cui cognome, di 5 lettere, inizia per R e termina per I

    SELECT *
    FROM autore
    WHERE cognome LIKE 'R___I'