[4 ESERCITAZIONE] - Interrogazioni nidificate in SQL

Discutiamo qui di seguito dell'esercitazione riguardante le interrogazioni nidificate in SQL.

nell'esercizio proposto pag.28 numero 3 ( trovare tutti gli fnome dei fornitori che forniscono solo pezzi rossi)andrebbe bene la seguente risoluzione ? :
SELECT fnome
FROM fornitore F
WHERE exist ( SELECT*
FROM pezzi P, catalogo C
WHERE P.pid=C.pid AND P. colore = 'rosso' AND C.fid=F.fid)

Nell'esempio a pag.19,relativo ai cantautori,avremmo potuto fare in questo modo?:

SELECT Nome
FROM Cantante C
WHERE Nome =all (SELECT Nome
FROM Cantante C,Autore A
WHERE C.Nome=A.Nome AND C.Canzone=A.Canzone)

Phoebe ha scritto:
Nell'esempio a pag.19,relativo ai cantautori,avremmo potuto fare in questo modo?:

SELECT Nome
FROM Cantante C
WHERE Nome =all (SELECT Nome
FROM Cantante C,Autore A
WHERE C.Nome=A.Nome AND C.Canzone=A.Canzone)

Ci sono alcuni punti ambigui nell'interrogazione :

• Eviterei di assegnare la variabile di range C sia all'interrogazione più esterna che a quella più interna. Diciamo che nell'interrogazione più interna potrei chiamare Cantanti con C1. Oppure, dato che se ho capito bene non le interessa avere variabile di range per l'interrogazione più esterna, basterebbe cancellare la variabile di range 'C' per essa.
  
• Mentre nell'interrogazione più esterna è chiaro che l'attributo Nome si riferisce alla tabella Cantante, in quella più interna Nome si riferisce ad Autore o a Cantante?
  

Riscrivo l'interrogazione come la propone lei, ma con le correzioni viste sopra :
SELECT Nome
FROM Cantante
WHERE Nome =all (SELECT C.Nome
FROM Cantante C,Autore A
WHERE C.Nome=A.Nome AND C.Canzone=A.Canzone)


L'interrogazione più esterna non è correlata con quella più interna. Perciò possiamo calcolare quest'ultima una volta per tutte! L'interrogazione più interna trova tutti quei cantanti che sono anche autori....la condizione C.Canzoni=A.Canzone mi taglia quelle tuple esistenti se il Cantante non è un autore di alcune delle sue canzoni. Perciò l'interrogazione più interna restituisce una relazione contenente cantanti che sono anche autori di ALCUNE delle loro canzoni, ma non necessariamente di tutte. Provi a ragionare....si disegni un'istanza....ad esempio

Cantante

Nome	Canzone
Vasco	Un senso
Vasco	Una Vita Spericolata
Vasco	Alba Chiara
Renga	Segreti
Renga	Meravigliosa

Autore

Nome	Canzone
Vasco	Un senso
Vasco	Una Vita Spericolata
Renga	Segreti
Renga	Meravigliosa

L'unico cantautore puro in questo caso sarebbe Renga.....provi a riformulare l'interrogazione in altro modo.....

miss butterfly ha scritto:nell'esercizio proposto pag.28 numero 3 ( trovare tutti gli fnome dei fornitori che forniscono solo pezzi rossi)andrebbe bene la seguente risoluzione ? :
SELECT fnome
FROM fornitore F
WHERE exist ( SELECT *
FROM pezzi P, catalogo C
WHERE P.pid=C.pid AND P. colore = 'rosso' AND C.fid=F.fid)

Nell'interrogazione ci sono due erroretti veniali...

• La tabella è Fornitori (con la 'i' alla fine)
  
• Exist va scritto Exists (con la 's' finale)
  

Detto questo, l'interrogazione chiede di trovare tutti quei fornitori che forniscono SOLO pezzi rossi (quindi non devono fornire pezzi di nessun altro colore). L'interrogazione più interna, per l'i-esimo fornitore, verifica che esso venda pezzi rossi, ma non esclude che il venditore stesso ne venda di altri colori. Perciò con questa interrogazione lei mi sta prendendo tutti quei fornitori che vendono certamente dei pezzi rossi, ma non necessariamente solo quelli.
Una possibile soluzione corretta la trova anche sulle slides Inoltre può testare le soluzioni su MySQL....in linea è presente da un pezzo il file SQL con le tabelle popolate

esercizio che richiede i fid dei fornitori che forniscono un pezzo rosso e uno verde,non capisco a cosa si riferisce IN,nella soluzione proposta:
SELECT distinct C.fid
FROM Catalogo C, Pezzi P
WHERE C.pid = P.pid and P.colore = ‘rosso’ in
(SELECT distinct C1.fid
FROM Catalogo C1, Pezzi P1
WHERE C1.pid = P1.pid and P1.colore = ‘verde’);

e perchè con questa soluzione,che tra l'altro è quella del libro,relativamente all'esempio sui velisti,il risultato non è lo stesso:

select f.fid
from fornitori f,catalogo c,pezzi p
where f.fid=c.fid and c.pid = p.pid and p.colore='rosso'and f.fid in (select f2.fid
from fornitori f2,catalogo c2,pezzi p2
where f2.fid=c2.fid and c2.pid=p2.pid and p2.colore='verde');

Effettivamente manca un pezzo nel file SQL...
SELECT distinct C.fid
FROM Catalogo C, Pezzi P
WHERE C.pid = P.pid and P.colore = ‘rosso’ AND C.fid in
(SELECT distinct C1.fid
FROM Catalogo C1, Pezzi P1
WHERE C1.pid = P1.pid and P1.colore = ‘verde’);

Ora il risultato dovrebbe essere lo stesso....grazie per avermelo notificato. Ho già aggiornato il nuovo file SQL sulla mia pag.web