(Microsoft PowerPoint - L03.Subquery.ppt [modalit\340 compatibilit
annuncio pubblicitario
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
