Gestione dati e archivi - progettazione ER e SQL

SELECT aerei.id
FROM aerei
WHERE aerei.capienza > [valore]

1. di 100che partono il 17 maggio '04" 16
2. SQLsoluzione:select aerei.idfrom aerei, partenzewhere aerei.id=partenze.aereo andaerei.capienza>100 andpartenze.data=17/5/04 17
3. SQLsempre in riferimento agli stessi schemirelazionali, esprimere in SQL la seguenteinterrogazione:"Trovare tutti i nomi dei piloti che partonodopo il 17 maggio '04 con un aereo aventecapienza maggiore di 100 posti" 18
4. SQLsoluzione:select piloti.nomefrom piloti, aerei, partenzewhere piloti.id=partenze.pilota andaerei.id=partenze.aereo andpartenze.data>17/5/04 andaerei.capienza>100 19
5. SQLTrovare l'età media dei piloti che pilotanoaerei che partono entro il 25 aprile '04select avg(piloti.età) as etàmediafrom piloti, partenzewhere piloti.id=partenze.pilota andpartenze.data<25/4/04 20
6. Progettazione ER
7. Si considerino le seguenti informazioni riguardanti unDB universitario:i professori sono univocamente identificati dalcodice fiscale, inoltre posseggono un nome,

Età, grado ed attività di ricerca i progetti sono identificati da un numero, inoltre possiedono uno sponsor, data d'inizio, data di fine e budget. Gli studenti laureati sono univocamente identificati dal codice fiscale, nome, età e corso di studi. Inoltre...

21Progettazione ER

Ogni progetto è gestito da un professore. Ad ogni progetto lavorano uno o più professori. I professori possono gestire e/o lavorare a più progetti. Ad ogni progetto lavorano uno o più studenti. Quando uno studente lavora ad un progetto, un professore supervisiona lo studente. E poi...

22Progettazione ER

I dipartimenti sono identificati da un numero, inoltre hanno un nome ed un ufficio principale. Ogni dipartimento è gestito da un professore. I professori lavorano in uno o più dipartimenti e, per ogni dip., viene registrata la percentuale di tempo di lavoro. Gli studenti hanno un dipartimento di appartenenza. Ogni studente ha uno studente più anziano che lo aiuta.

• nella scelta dei corsi 23Progettazione ER iniziosponsor finepidetà grado budgetcf ricerca (1,N) proglavoroprof(1,N) (1,1)gestionegestlavoro LavoroProg.(1,1) superpct (1,1) aiutodip (1,1) studenteAppart. nomeUff.N° nome cf età corso 24SQL
• Si consideri il seguente schema di database:
• Studente(snum, nome, età, anno)
• Classe(nome, aula, ora, pid)
• Iscritto(snum, nome)
• Professore(pid, nome)
• 25SQLtrovare nome ed età degli studenti iscritti alcorso di paleontologia
• Select S.nome, S.etàFrom studente S, iscritto IWhere S.snum=I.snumand I.nome=‘paleontologia’
• 26SQLIn alternativa:Select studente.nome, studente.etàFrom studente SWhere S.snum in (select I.snumfrom Iwhere I.nome=‘paleontologia’)
• 27SQLTrovare i nomi di tutti gli studenti del primoanno iscritti ad un corso tenuto dal prof.RossiSelect distinct S.nomeFrom studente S, corso C, iscritto I, professore PWhere S.snum=I.snum and I.nome=C.nome andC.pid=P.pid and

P.nome=‘Rossi’ and S.anno=128

SQL

Oppure…

Select distinct S.nome

From studente S

Where S.anno=1 and S.snum in (select I.snum

from iscritto I

where I.nome in (select C.nome

from corso C

where C.pid in (select P.pid

from professore P

where P.nome = ‘Rossi’)))

29

SQL

Trovare l’età media degli studenti, suddivisi

per anno

Select anno, avg(età)

From studente S

Group by anno

30

SQL

Trovare gli studenti che non sono iscritti ad

alcun corso

Select S.nome

From studente S

Where S.snum not in (select I.snum

from iscritto I)

31

SQL

Trovare l’età degli studenti più anziani che

sono iscritti al secondo anno oppure che

seguono un corso tenuto dal prof. Rossi

Select max(S.age)

From studente S

Where S.anno=2 or S.snum in (select I.snum

from iscritto I, corso C, Professore P

where I.nome=C.nome and C.pid=P.pid and

P.nome=‘Rossi’)

32