Preparando l'esame di Basi di Dati... aiuti e consigli..

[luxorl]

Ciao a tutti, se ho la seguente Basi di Dati:

IMP(Nimp, Nome, Ndipart, Lavoro, Diret, Stip)
DIPART(Ndipart, NomeDipart, Luogo)
USO(Ndipart, Parte)
Fornitura(Fornitore, Parte)

E la seguente richiesta:

Elencare i nomi e i codici dei dipartimenti dove lo stipendio medio degli impiegati è < 1000

Che ne pensate della seguente soluzione? E' corretta?

Select distinct Ndipart, NomeDipart
from dipart
where Ndipart in 
               (Select distinct Ndipart
                 from imp
                 group by Ndipart
                 having avg(Stip) > 1000 )

In particolare vorrei sapere:

1)Se è possibile mettere nella Select il solo attributo del group by tralasciando quello usato nella having.
2)Quando uso nella where la strategia "attributo in ( nuova query )" il risultato della query interna deve dare solo il singolo attributo di confronto della query esterna? Cioè nella Select interna deve esserci solo e soltanto l'attributo di confronto?

Come sempre grazie

[gugoXX]

Originariamente inviato da luxorl

Ciao a tutti, se ho la seguente Basi di Dati:


E la seguente richiesta:



Che ne pensate della seguente soluzione? E' corretta?

Select distinct Ndipart, NomeDipart
from dipart
where Ndipart in 
               (Select distinct Ndipart
                 from imp
                 group by Ndipart
                 having avg(Stip) > 1000 )

E' corretta, ma entrambe le DISTINCT sono superflue.
La prima perche' per forza non potrai che avere dipartimenti distinti, essendo la chiave della tabella dipart contenuta nella clausola SELECT
La seconda perche' la DISTINCT e' implicita nella clausola IN (subselect)

In particolare vorrei sapere:

1)Se è possibile mettere nella Select il solo attributo del group by tralasciando quello usato nella having.

Se ho capito bene la domanda, in generale si puo' mettere anche solo la clausola GROUP BY (e' il caso piu' frequente), ma in questa situazione non risolveresti il tuo problema

2)Quando uso nella where la strategia "attributo in ( nuova query )" il risultato della query interna deve dare solo il singolo attributo di confronto della query esterna? Cioè nella Select interna deve esserci solo e soltanto l'attributo di confronto?

Come sempre grazie

Si', anche perche' non avrebbe senso e non servirebbe SELECTionare altri campi, perche' la INNER query serve solo ed esclusivamente per cercare i valori da soddisfare per la clausola di appartenenza IN.

Da SQL-Standard e' possibile avere un solo parametro di confronto di gruppo. Alcune estensioni dell'SQL (Oracle) prevedono l'uso anche di T-uple per questa condizione, es

... WHERE (IDDipart, IDNazione) IN (SELECT IDDipart,IDNazione FROM ...)

in questo caso utile, p.es. se i codici di dipartimento sono univoci solo all'interno di una nazione.

In SQL-Standard si sarebbe dovuto scrivere storture simili a

... WHERE (IDNazione*100000+IDDipart) IN (SELECT IDNazione*100000+IDDipart FROM ...)

in cui si puo' intuire chiaramente che l'ottimizzatore non puo' usare gli eventuali indici presenti sulle tabelle.

[luxorl]

Grazie delle risposte, gentilissimo. Tutto chiaro.

[gugoXX]

Bene.

Originariamente inviato da luxorl

1)Se è possibile mettere nella Select il solo attributo del group by tralasciando quello usato nella having.

Ho capito ora la domanda.
Puoi mettere zero o piu' elmenti della GROUP BY
zero o piu' costanti
zero o piu' funzioni di gruppo ciascuna delle quali dipendenti da zero o piu' elementi della GROUP BY
zero o piu' funzioni normali che accettino zero o piu' dei qualsiasi dei valori precedenti
(fatto salvo che almeno un elemento occorre selezionarlo)

Quindi, puoi anche evitare di mettere la funzione di gruppo scelta per l'eventuale HAVING, oppure ne puoi anche mettere un'altra

ES:

SELECT ID1, COUNT(ID3), ID2, 4, 5, 7, SUM(ID1)+SUM(ID2), AVG(ID1+ID2), Funzione(ID3, AVG(ID18), ID1*ID1+AVG(ID2+ID1) )
FROM...
GROUP BY ID1, ID3, ID2, ID18
HAVING COV(ID3+ID2) > 10

[luxorl]

Ho dubbi sul Group By.
Come funziona quando si raggruppa su più attributi?

Per esempio se ho una tabella formata da attributi x, y, z, k e faccio

group by x, y, z

in uscita cosa ottengo? k viene eliminato?

[gugoXX]

Originariamente inviato da luxorl

Ho dubbi sul Group By.
Come funziona quando si raggruppa su più attributi?

Per esempio se ho una tabella formata da attributi x, y, z, k e faccio

group by x, y, z

in uscita cosa ottengo? k viene eliminato?

Certo, verra' eliminato.
Al termine avrai tanti record quante sono tutte le differenti T-Uple x,y,z

PS: Non usare MySql per fare le prove o per studiare, in quanto non si comporta in modo standard a fronte della Group By.

[luxorl]

Originariamente inviato da gugoXX

Certo, verra' eliminato.
Al termine avrai tanti record quante sono tutte le differenti T-Uple x,y,z

PS: Non usare MySql per fare le prove o per studiare, in quanto non si comporta in modo standard a fronte della Group By.

Noooooooooo... non mi dire così che mi sono appena fatto il bip per montare la BD dell'esame su MySql

[luxorl]

Ma esiste qualche programma che riempie in modo random ma coerente un DB?

[gugoXX]

Originariamente inviato da luxorl

Noooooooooo... non mi dire così che mi sono appena fatto il bip per montare la BD dell'esame su MySql

Ecco, peccato.
Davvero, non so se c'e' un parametro di compatibilita' per forzare MySql a comportarsi in modo standard a fronte delle GroupBy

PostgresSql andrebbe meglio.
E pure SqlServerExpress, che si installa con 2 click...
Ma ancora meglio Oracle Express (che e' gratuito e si installa con 3 click)
http://www.oracle.com/technology/pro.../xe/index.html

Per un dB pronto per le prove, SqlServer se non sbaglio ti chiede se vuoi anche installare l'istanza chiamata Northwind o qualcosa di simile, con fatture, clienti, fornitori, etc.
Per Oracle non mi risulta ci sia, e effettuare il restore di un database occorre imparare qualcosa in piu'.

[luxorl]

Passiamo alle query toste d'esame

Dato il seguente schema

Università(u/nome, u/strada, u/città, a/a, num/fac)
Comprende(u/nome, u/città, a/a, nome/laurea)
CorsodiLaurea(nome/laurea, a/a, facoltà, durata)
Organizza(nome/laurea, i/nome, u/nome, u/città, a/a, n/ore/lez, n/ore/es, nome/prof, annocorso, f/c)
Appello(i/nome, nome/laurea, u/nome, u/città, n/ordine, a/a, data, ore, aula)
Esame(i/nome, nome/laurea, u/nome, u/città, n/ordine, a/a, s/nome, risultato)
Studente(s/nome, s/città, s/prov, matr, anno/nascita, nome/laurea, u/nome, u/città, a/a, annocorso)
Piano(s/nome, a/a, i/nome, nome/laurea, annocorso)
Orario(nome/laurea, i/nome, u/nome, u/città, a/a, giorno, ora, aula, capienza)


Scrivere una richiesta SQL per conoscere quest'anno, università per università, quanti professori svolgono il corso di Analisi I in Facoltà di Ingegneria che abbiano attivato almeno 5 corsi di laurea ed abbiamo almeno 500 iscritti al primo anno.

Mia soluzione

Select distinct o.u/nome, count(distinct o.nome/prof)
From Organizza as o
Where o.i/nome='Analisi I' and o.a/a='2008/09' and
         4 < (Select count(distinct c.nome/laurea)
                From Corsodilaurea as c
                Where c.nome/laurea=o.nome/laurea and
                          c.facoltà='Ingegneria' and
                          499 < (Select count(distinct s.s/nome)
                                    From Studente as s
                                    Where s.nome/laurea=c.nome/laurea and
                                             s.annocorso=1))
Group by o.u/nome

Secondo me sbaglio qualcosa voi che dite?

[gugoXX]

Si', c'e' qualcosa che non va.
La Distinct insieme alla GROUP BY non ha molto senso, essendo che per definizione il risultato della GROUP BY saranno tanti record quante le possibili combinazioni esistenti degli elementi messi in GROUP BY.
Nel tuo caso e' gia' quello che vuoi ottenere (un record per ciascun nome di universita'), pertanto la DISTINCT e' superflua.

Poi c'e' qualche casino. ti propongo di iniziare a risolvere la seguente:

Scrivere una richiesta SQL per conoscere quest'anno, università per università, quanti professori svolgono il corso di Analisi I in Facoltà di Ingegneria

e poi di condire aggiungendo qualche clausola per filtrare via i restanti intrusi...

[luxorl]

Originariamente inviato da gugoXX

Poi c'e' qualche casino. ti propongo di iniziare a risolvere la seguente:

Scrivere una richiesta SQL per conoscere quest'anno, università per università, quanti professori svolgono il corso di Analisi I in Facoltà di Ingegneria

e poi di condire aggiungendo qualche clausola per filtrare via i restanti intrusi...

Select o.u/nome, count(distinct o.nome/prof)
          From Organizza as o, CorsodiLaurea as c
          Where o.i/nome='Analisi I' and o.a/a='2008/09' and
                    c.facoltà='Ingegneria' and
                    c.nome/laurea=o.nome/laurea and
                    c.a/a=o.a/a
Group By o.u/nome

Che riscrivendo il join in modo più visibile potrei fare come:

Select o.u/nome, count(distinct o.nome/prof)
          From Organizza as o
          Where o.i/nome='Analisi I' and o.a/a='2008/09' and
                    o.nome/laurea in (Select c.nome/laurea
                                      From CorsodiLaurea as c
                                      Where c.a/a='2008/09' 
                                            and c.facoltà='Ingegneria')
Group By o.u/nome

Se è corretta provo ad arricchirla con le altre condizioni

[gugoXX]

Direi che e' giusta, pero', giusto perche' stai preparando l'esame, ti proporrei di usare la sintassi corretta che prevede la divisione netta tra le clausole di JOIN e quelle di WHERE

A me personalmente piace di piu' la prima delle 2 query, quindi scriverei qualcosa tipo

Select o.u/nome, count(distinct o.nome/prof)
          From Organizza as o
          JOIN CorsodiLaurea as c ON (o.nome/laurea = c.nome/laurea AND c.a/a=o.a/a)
          Where o.i/nome='Analisi I' and o.a/a='2008/09' and
                    c.facoltà='Ingegneria'
          Group By o.u/nome