Query SQL

[enigmagame]

Posto alcuni esercisi su query SQL per verificarne la correttezza e come esercizio per chi vuole .
Dato il seguente schema relazionale (chavi primarie sottolineate) contenente i dati relativi all'ccupazione delle stanze di un albergo:
STANZA(Piano, Numero, Tipo:{singola,doppia,tripla},Bagno:si/no);
OCCUPAZIONE(Piano, Stanza, Cliente, Data);
CLIENTE(CodiceCliente, N_documento, Cognome, Nome, DataNascita, CittaResidenza, Cittadinanza);
Vincoli di integrità: OCCUPAZIONE.Piano,Stanza -> Stanza; OCCUPAZIONE.Cliente -> Cliente
Formulare in SQL le seguenti interrogazioni (definire viste solo dove è necessario):

a) Trovare la cittadinanza dei clienti che oggi occupano stanze doppie del quinto piano.

SELECT c.Cittadinanza
FROM Stanza s JOIN Occupazione o ON (s.Piano = o.Piano AND s.Numero = o.Stanza) JOIN Cliente c ON o.Cliente = c.CodiceCliente
WHERE s.Tipo = 'doppia' AND o.Data = '03/07/2007' AND s.Piano = 5

b) Trovare il cognome, il nome e la città di residenza dei clienti che hanno SEMPRE soggiornato in stanze singole.

SELECT Cognome, Nome, CittaResidenza
FROM Cliente
WHERE CodiceCliente <> any (SELECT o.Cliente
                            FROM Occupazione O JOIN Stanza s ON (o.Piano = s.Piano AND o.Stanza = s.Numero)
                            WHERE s.Tipo = 'doppia')
                            AND
                            SELECT o.Cliente(
                            FROM Occupazione O JOIN Stanza s ON (o.Piano = s.Piano AND o.Stanza = s.Numero)
                            WHERE s.Tipo = 'tripla')

c) Trovare per ogni piano e con riferimento al giorno 1/1/2002: il numero di stanze singole occupate, il numero di stanze doppie occupate e il numero di stanze triple occupate.

CREATE VIEW Stanze_1_1_2002(Piano, Numero, Tipo, Bagno) AS
SELECT s.Piano, s.Numero, s.Tipo, s.Bagno
FROM Stanza s JOIN Occupazione o ON (s.Piano = o.Piano AND s.Numero = o.Stanza)
WHERE o.Data = '1/1/2002'

SELECT Piano, Tipo, COUNT(TIPO)
FROM Stanze_1_1_2002
GROUP BY Piano, Tipo

PS: Nel caso i vincoli di integrità referenziale tra due entità (Stanza, Occupazione) siano ad esempio 2, è corretto per creare il JOIN, la sintassi che ho usato nella ON?

[codino75]

allora:

- non usavo (ai miei tempi) la sintassi della clausola 'JOIN' , in quanto il jioin lo facevo esplicitamente nella clausola WHERE, quindi non so dirti come si usa.

- non mi e' chiara la funzione della parola 'AND' nella seconda query

-(errore veniale) nella terza query non serviva portarsi dietro anche l'attributo 'bagno'.

- non rilevo (a mio modestissimo giudizio) gravi errori concettuali, ma non posso certificare la sintassi , della quale sono a digiuno da anni.

ciao alex

[enigmagame]

Oggi mi informo sull'utilizzo della clausola JOIN, nel caso i vincoli siano più di uno, poi scrivo qua se è corretto o meno.

Per la seconda query, seleziono tutte i clienti che hanno soggiornato in una stanza singola e tutti quelli che hanno soggiornato in una stanza tripla, forse però potevo mettere tutto nella prima query nidificata, senza utilizzarne due...

Vero, mi porto sempre dietro, nelle viste, alcuni attributi che non contano assolutamente nulla .

Grazie!
Ciao!

[TomSawyer1]

L'uso dei join è corretto (e anche molto consigliato, per vari motivi). La seconda query va benissimo così, perché è più chiara con una sottoselect. Potresti anche farne solo una, giocando un po' con i quantificatori, ma la lascerei com'è.

[codino75]

a me sembra che al posto di 'AND' andava usato 'UNION'

[enigmagame]

"codino75":a me sembra che al posto di 'AND' andava usato 'UNION'

Mmm, se uso l'AND quindi il risultato è sbagliato?

[TomSawyer1]

Hai provato anche con l'AND e ti dava i risultati giusti? Penso ti dia addirittura errore. Per l'unione di due query c'è l'UNION, appunto.

[enigmagame]

"TomSawyer":Hai provato anche con l'AND e ti dava i risultati giusti? Penso ti dia addirittura errore. Per l'unione di due query c'è l'UNION, appunto.

Sisi è giusta la UNION, scusatemi...
Più tardi posterò altre query SQL svolte, per verificarne la correttezza ed in futuro per chi vuole esercitarsi
Ciao!

[enigmagame]

Dato il seguente schema relazionale (chiavi primarie sottolineate) contenente le partite di un torneo di clacio:
SQUADRA(Nome,Sponsor,AnnoFondazione)
PARTITA(SquadraA,SquadraB,Data,Ora,GoalA,GoalB,Conclusa:{si,no})
GIOCATORE(Cognome,Nome,Ruolo,Squadra,NumeroMaglia)
Vincoli di integrità:
PARTITA.SquadraA -> SQUADRA
PARTITA.SquadraB -> SQUADRA
GIOCATORE.Squadra -> SQUADRA
Formulare in SQL le seguenti interrogazioni (definire viste solo dove è necessario):
1) Trovare le squadre che non hanno giocato fino ad oggi nessuna partita con la squadra 'X', riportando il nome della squadra e il suo sponsor.

SELECT Nome, Sponsor
FROM SQUADRA
WHERE Nome <> 'X' AND Nome <> ANY (SELECT SquadraA
                                   FROM PARTITA
                                   WHERE SquadraB = 'X'
                                   UNION
                                   SELECT SquadraB
                                   FROM PARTITA
                                   WHERE SquadraA = 'X'
                                   )

2) Trovare le partite svoltesi nel giugno 2006 dove hanno partecipato squadre con i giocatori di nome 'Giovanni' o 'Andrea', riportando il nome delle due squadre coinvolte nella partita e il risultato della stessa,

CREATE VIEW SQUADRE_GA(Nome) AS
SELECT Squadra
FROM GIOCATORE
WHERE Nome = 'Giovanni' OR Nome = 'Andrea'

SELECT SquadraA, SquadraB, GoalA, GoalB
FROM PARTITA
WHERE SquadraA = ANY (SELECT * FROM SQUADRE_GA) AND SquadraB = ANY (SELECT * FROM SQUADRE_GA) AND Data > '20060601' 
                 AND Data < '20060630' AND Conclusa = 'si'

Nell'ultima query nella WHERE ho anche richiesto "Conclusa = si", secondo voi nel testo intende questo o tutte le partite comprese quelle non terminate?
Seconda cosa, come notate, nel testo degli esercizi è sempre espresso: definire viste solo dove è necessario, secondo voi in questo caso risultava utile/necessario definire la vista o no? Essendo testo di esame, probabilmente se la definisco e non serve, sbaglio l'esercizio...
Grazie!

[codino75]

non so aiutarti sulle 'viste'.
cmq le query mi sembrano concettualmente corrette, rimane solu 1 dubbio sull'interpretazione del fatto di dover considerare o meno , per la seconda query, le partite in cui una sola squadra aveva in campo giocatori di nome giovanni o andrea.
per quanto riguarda il fatto di escludere quelle non terminate, non so che dirti, forse potresti anche includerle, in quanto sono partite a tutti gli effetti (opinione personale).

[_Tipper]

Una sola cosa: anziché <> ANY non si dovrebbe usare <> ALL?

[TomSawyer1]

Io non credo; cioè <> ANY funziona di sicuro, e ho sempre visto <> ANY in questi casi, quindi per questo dico così. Ma mi sa che anche con <> ALL funziona. Può anche usare il NOT IN.

[_Tipper]

No perché... io ho sempre usato =ANY o <>ALL, pensavo addirittura che <>ANY non esistesse... evidentemente mi sbagliavo.

[enigmagame]

"codino75":non so aiutarti sulle 'viste'.
cmq le query mi sembrano concettualmente corrette, rimane solu 1 dubbio sull'interpretazione del fatto di dover considerare o meno , per la seconda query, le partite in cui una sola squadra aveva in campo giocatori di nome giovanni o andrea.
per quanto riguarda il fatto di escludere quelle non terminate, non so che dirti, forse potresti anche includerle, in quanto sono partite a tutti gli effetti (opinione personale).

Sulle partite non terminate probabilmente hai ragione, alla fine sono state giocate e sono 'terminate' anche queste con un risultato...
Sul primo dubbio invece, secondo me è richieso proprio che entrambe le squadre abbiano giocatori di nome Giovanni o Andrea. Se cosi non fosse, bè, sarebbe dura capire ciò che vuole...

"TomSawyer":
Io non credo; cioè <> ANY funziona di sicuro, e ho sempre visto <> ANY in questi casi, quindi per questo dico così. Ma mi sa che anche con <> ALL funziona. Può anche usare il NOT IN.

Si, anche NOT IN va bene...

"Tipper":
No perché... io ho sempre usato =ANY o <>ALL, pensavo addirittura che <>ANY non esistesse... evidentemente mi sbagliavo.

Funziona anche con <>ALL. Ma anche <> ANY è corretto...

Grazie a tutti, fra poco posto qualche altro esercizio.
Ciao!