Esercitazione 3
SQL: subquery
Sistemi Informativi T
Versione elettronica: L03.Subquery.pdf
Il database SAMPLE (parte di)
DEPARTMENT
DEPTNO
DEPTNAME
MGRNO
ADMRDEPT
LOCATION
EMPACT
SALES
EMPNO
SALES_DATE
PROJNO
SALES_PERSON
ACTNO
EMPTIME
EMSTDATE
REGION
SALES
EMENDATE
EMPLOYEE
EMPNO
FIRSTNME
MIDINIT
LASTNAME
WORKDEPT
PHONENO
HIREDATE
EDLEVEL
SEX
BIRTHDATE
SALARY
BONUS
COMM
Es. 3: subquery
STAFF
PROJECT
ID
PROJNO
NAME
PROJNAME
DEPT
DEPTNO
JOB
RESPEMP
YEARS
SALARY
COMM
PRSTAFF
PRSDATE
PRENDATE
MAJPROJ
Sistemi Informativi T
2
Esercizio 1: le tabelle del DB SAMPLE
Asterisco = valori nulli presenti nel DB
Le relazioni foreign key -> (primary) key sono indicate da frecce
Per vedere gli attributi di una tabella, ad es. DEPARTMENT:
DESCRIBE TABLE DEPARTMENT
Spiegazione di alcuni attributi:
MGRNO: manager del dipartimento
EDLEVEL: anni di formazione (SMALLINT)
ACTNO: codice di un’attività
EMPSTDATE, EMENDATE: periodo (inizio, fine) dedicato all’attività
SALES_PERSON: cognome (LASTNAME) di un dipendente, non ambiguo
La relazione STAFF non è interessata da nessuna delle interrogazioni
proposte
Es. 3: subquery
Sistemi Informativi T
3
Esercizio 1: preliminari
Connettersi al database SAMPLE
CONNECT TO SAMPLE USER xxxxxx
Cambiare lo schema corrente
(per evitare di scrivere, ad es.: DB2INST1.EMPLOYEE):
SET CURRENT SCHEMA DB2INST1
Per vedere le tabelle dello schema DB2INST1:
LIST TABLES FOR SCHEMA DB2INST1
(ci sono anche altre cose che non interessano)
Due funzioni SQL utili per i confronti tra stringhe:
UPPER(<stringa>) converte <stringa> in maiuscole
LOWER(<stringa>) converte <stringa> in minuscole
Per sapere quanti giorni sono passati da DATA1 a DATA2:
DAYS(DATA2) - DAYS(DATA1)
Es. 3: subquery
Sistemi Informativi T
4
Esercizio 1: interrogazioni (1)
Risolvere le seguenti query con CLP
NB Non tutte richiedono l’uso di subquery. Dove possibile provare a risolvere sia
con subquery che senza (le subquery si possono usare anche nella clausola
HAVING)
Q1)
Q2)
Q3)
Q4)
Il numero dei dipartimenti con almeno 7 dipendenti
I dati dei dipendenti che lavorano in un dipartimento con almeno 7 dipendenti
I dati del dipartimento con il maggior numero di dipendenti
Il nome delle regioni e il totale delle vendite per ogni regione con un totale di vendite
maggiore di 30, ordinando per totale vendite decrescente
Q5) Lo stipendio medio degli impiegati che non sono manager di nessun dipartimento
-- JOB = ‘MANAGER’ non è significativo!!
Q6) I dipartimenti che non hanno impiegati il cui cognome inizia per ‘L’
Es. 3: subquery
Sistemi Informativi T
5
Esercizio 1: interrogazioni (2)
Q7) I dipartimenti e il rispettivo massimo stipendio per tutti i dipartimenti aventi un salario
medio minore del salario medio calcolato considerando i dipendenti di tutti gli altri
dipartimenti
Q8) Per ogni dipartimento determinare lo stipendio medio per ogni lavoro per il quale il
livello di educazione medio è maggiore di quello degli impiegati dello stesso
dipartimento che fanno un lavoro differente
Q9) Lo stipendio medio degli impiegati che non sono addetti alle vendite
Q10) Per ogni regione, i dati dell’impiegato che ha il maggior numero di vendite
(SUM(SALES)) in quella regione
Q11) I codici dei dipendenti che svolgono un’attività per cui ogni tupla di EMPPROJACT
riguarda un periodo minore di 200 giorni
Es. 3: subquery
Sistemi Informativi T
6
Esercizio 1: alcuni risultati
Q5) 49199,41
Q8) WORKDEPT JOB
Q7)
WORKDEPT MAX_SALARY
-------- ----------D21
96170,00
E11
89750,00
E21
86150,00
STIP_MEDIO
-------- ----------- ---------A00
PRES
152750,00
A00
SALESREP
56500,00
C01
MANAGER
98250,00
D11
DESIGNER
57437,00
D21
MANAGER
96170,00
E11
MANAGER
89750,00
E21
FIELDREP
39274,00
Es. 3: subquery
Q11) EMPNO
-----000130
000140
000200
Sistemi Informativi T
7
