1 / 50

Structured Query Language - SQL un linguaggio di definizione e manipolazione dei dati

Structured Query Language - SQL un linguaggio di definizione e manipolazione dei dati. Esercitazioni del Corso di Sistemi Informativi Marina Mongiello. SQL come standard. Varie versioni con successivi miglioramenti: SQL-1 o SQL-89 SQL-2 o SQL-92 (entry, intermediate e full) SQL-3?.

lynde
Download Presentation

Structured Query Language - SQL un linguaggio di definizione e manipolazione dei dati

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Structured Query Language - SQL un linguaggio di definizione e manipolazione dei dati Esercitazioni del Corso di Sistemi Informativi Marina Mongiello

  2. SQL come standard • Varie versioni con successivi miglioramenti: • SQL-1 o SQL-89 • SQL-2 o SQL-92 (entry, intermediate e full) • SQL-3?

  3. La struttura base della query L’interrogazione è specificata in maniera dichiarativa: si specifica non il modo in cui l’interrogazione deve essere eseguita, ma le caratteristiche del risultato che deve fornire. Query semplici: Select AttrExpr [[as] Alias]{, AttrExpr [[as] Alias]} FromTableName [[as] Alias]{, TableName [[as] Alias]} [Wherecondition] Le tre parti componenti vengono chiamate target list, from clause e where clause

  4. Query SQL in ACCESS: • In applicazioni sviluppate con ACCESS, le query possono essere create • mediante la griglia di struttura grafica QBE (query by example) • mediante la specifica in SQL

  5. Esempi di query in SQL • Di seguito riportiamo alcuni esempi di query in linguaggio SQL facendo riferimento al database di esempio di cui riportiamo il diagramma delle relazioni

  6. Esempio 1 Query di Selezione: Si vuole determinare la data di nascita e l’indirizzo degli impiegati il cui nome è John Smith SELECT Data_di_nascita, Via, Numero_civico,CAP,Città FROM IMPIEGATO WHERE Nome = ‘John’ AND Cognome = ‘Smith’ Cfr. La query Esempio 1 su azienda2002.mdb

  7. Esempio 2 Si vuole un elenco completo dei nomi e degli indirizzi degli impiegati che lavorano nel dipartimento “Research” SELECT Nome, Cognome, Via, Numero_civico, CAP,Città FROM IMPIEGATO, DIPARTIMENTO WHERE Nome_dipartimento = ‘Research’ AND Identificativo_dipartimento = Id_Dipartimento Cfr. La query Esempio 2 su azienda2002.mdb

  8. Esempio 3 Per tutti i progetti con sede a ‘Stafford’, si richiede l’identificativo del progetto e del dipartimento che lo conduce, nonché il nome e la data di nascita del direttore di tale dipartimento. SELECT Identificativo_progetto, Identificativo_dipartimento, Cognome, Data_di_nascita FROM PROGETTO, DIPARTIMENTO, IMPIEGATO WHERE Id_dipartimento_coord =Identificativo_dipartimento AND CF_Direttore = Codice_Fiscale AND Sede =‘Stafford’ Cfr. La query Esempio 3 su azienda2002.mdb

  9. Se in più relazioni sono presenti attributi con lo stesso nome, bisogna specificare il nome della tabella prima dell’attributo che vogliamo estrarre. ESEMPIO: Supponiamo che nel DB azienda2002.mdb sia la relazione IMPIEGATO che la relazione DIPARTIMENTO abbiano gli attributi Nome ed Id_Dipartimento. La query Esempio 2 diventa: SELECT IMPIEGATO.Nome, Cognome, Via, Numero_civico, CAP,Città FROM IMPIEGATO, DIPARTIMENTO WHERE DIPARTIMENTO.Nome = ‘Research’ AND DIPARTIMENTO.ID_Dipartimento = IMPIEGATO.Id_Dipartimento Ambiguità dei nomi degli attributi

  10. ALIASING Per facilitare la manipolazione si possono dichiarare dei nomi alternativi per gli attributi e per le relazioni mediante il comando AS (che può anche essere omesso dove indicato dalle parentesi graffe negli esempi). ESEMPI: La query della slide precedente può essere anche scritta in uno dei seguenti modi: 1) SELECT I.Nome, Cognome, Via, Numero_civico, CAP,Città FROM IMPIEGATO {AS} I, DIPARTIMENTO {AS} D WHERE D.Nome = ‘Research’ AND D.Id_Dipartimento = I.Id_Dipartimento • SELECT IMPIEGATO.Nome, Cognome, Via, Numero_civico, CAP,Città, DIPARTIMENTO.ID_Dipartimento AS ID FROM IMPIEGATO, DIPARTIMENTO WHERE DIPARTIMENTO.Nome = ‘Research’ AND ID = IMPIEGATO.Id_Dipartimento

  11. Esempio 4 Per ogni impiegato, ricercare il nome ed il cognome del supervisore. SELECT I.Nome, I.Cognome, S.Nome, S.Cognome FROM IMPIEGATO AS I, IMPIEGATO AS S WHERE I.CF_Superiore=S.Codice_Fiscale Cfr. La query Esempio 4 su azienda2002.mdb

  12. Query senza where clause Non specificano alcuna condizione di selezione ESEMPI: • Selezionare i Codici Fiscali di tutti gli impiegati SELECT Codice_Fiscale FROM IMPIEGATO • Cercare tutte le combinazioni dei codici fiscali degli impiegati con gli identificativi dei dipartimenti SELECT Codice_Fiscale, Identificativo_Dipartimento FROM IMPIEGATO, DIPARTIMENTO

  13. Operatore * Seleziona tutti gli attributi delle relazioni specificate nella FROM CLAUSE senza elencarli singolarmente. Esempio 5 Si vuole un elenco completo di tutti gli attributi degli impiegati che lavorano nel dipartimento “Research” SELECT * FROM IMPIEGATO, DIPARTIMENTO WHERE Nome_dipartimento = ‘Research’ AND Identificativo_dipartimento = Id_Dipartimento Cfr. La query Esempio 5 su azienda2002.mdb

  14. La parola chiave DISTINCT In genere SQL non tratta le tabelle come insiemi , quindi può capitare che in una relazione o nel risultato di una query compaiano più di una volta delle tuple duplicate, sia perché è costoso eliminarle, sia perché talvolta l’utente può essere interessato alla visualizzazione delle tuple duplicate. Affinché il risultato di una query sia a sua volta una relazione(insieme di tuple distinguibili) bisogna usare la parola chiave DISTINCT nella operazione SELECT. ESEMPIO: SELECT DISTINCT Stipendio_annuo FROM IMPIEGATO

  15. Operazioni su insiemi predefinite in SQL • Unione (UNION) • Differenza(EXCEPT) • Intersezione(INTERSECT) In SQL2 Queste operazioni restituiscono relazioni, cioè insiemi di tuple distinguibili. Per ottenere anche le tuple duplicate si aggiunge all’operatore la parola chiave ALL (es. UNION ALL)

  16. Esempio 6 Elencare tutti gli identificativi dei progetti in cui lavora un impiegato il cui cognome è “Smith”, in qualità di lavoratore semplice o di direttore del dipartimento che controlla il progetto. (SELECT Identificativo_progetto FROM PROGETTO, DIPARTIMENTO, IMPIEGATO WHERE Id_dipartimento_coord=Identificativo_dipartimento AND CF_Direttore=Codice_Fiscale AND Cognome=‘Smith’) UNION (SELECT Identificativo_progetto FROM PROGETTO, LAVORA, IMPIEGATO WHERE Identificativo_progetto= Id_Progetto AND CF_Impiegato = Codice_Fiscale AND Cognome =‘Smith’) Cfr. La query Esempio 6 su azienda2002.mdb

  17. Query nidificate • L’argomento della clausola where puo’ essere a sua volta il risultato dell’esecuzione di una interrogazione(outer query). • E’ possibile anche usare gli operatori di confronto =,<,>,>=,<= per confrontare il risultato della outer query con valori di attributi.

  18. Esempio 6-A La query dell’esempio 6 può essere formulata anche come segue: SELECT DISTINCT Identificativo_progetto FROM PROGETTO WHERE Identificativo_progetto IN (SELECT Identificativo_progetto FROM PROGETTO, DIPARTIMENTO, IMPIEGATO WHERE Id_dipartimento_coord=Identificativo_dipartimento AND CF_Direttore=Codice_Fiscale AND Cognome=‘Smith’) OR Identificativo_progetto IN (SELECT Identificativo_progetto FROM LAVORA, IMPIEGATO WHERE CF_Impiegato = Codice_Fiscale AND Cognome =‘Smith’) Cfr. La query Esempio 6A su azienda2002.mdb

  19. Esempio 7 Cercare gli impiegati che guadagnano più di tutti gli impiegati del Dipartimento “5” SELECT Nome, Cognome, Stipendio_annuo FROM IMPIEGATO WHERE Stipendio_annuo > ALL (SELECT Stipendio_annuo FROM IMPIEGATO WHERE Id_Dipartimento = 5) Cfr. La query Esempio 7 su azienda2002.mdb

  20. OPERATORE EXIST :Esempi 1)Cercare gli impiegati che non hanno familiari SELECT Nome, Cognome FROM IMPIEGATO WHERE NOT EXISTS (SELECT * FROM FAMILIARE WHERE Codice_Fiscale=CF_Impiegato) 2)Cercare i direttori che hanno almeno un familiare SELECT Nome, Cognome FROM IMPIEGATO WHERE EXISTS (SELECT * FROM FAMILIARE WHERE Codice_Fiscale=CF_Impiegato) AND EXISTS (SELECT * FROM DIPARTIMENTO WHERE Codice_Fiscale=CF_Direttore)

  21. INSIEMI ESPLICITI e VALORI NULLI :Esempi 1)Cercare i codici fiscali degli impiegati che lavorano sui progetti 1 , 2 o 3. SELECT DISTINCT CF_Impiegato FROM LAVORA WHERE Id_Progetto IN (1,2,3) 2)Cercare tutti gli impiegati che non hanno un superiore SELECT Nome, Cognome FROM IMPIEGATO WHERE CF_Superiore IS NULL

  22. Operatori aggregati • In SQL è possibile utilizzare operatori per valutare delle proprietà su insiemi di tuple • count ( < * | [ distinct | all ] ListaAttributi>) • < sum | max | min | avg > ( [ distinct | all ] ListaAttributi ) Effettua un’operazione di conteggio Distinct elimina i duplicati All trascura solo i valori nulli Corrispondono alla somma, massimo, minimo, media

  23. Query con operatori aggregatiEsempio 8 Determinare il totale degli stipendi di tutti gli impiegati e lo stipendio minimo, massimo e medio. SELECT SUM(Stipendio_annuo), MAX(Stipendio_annuo), MIN(Stipendio_annuo), AVG(Stipendio_annuo) FROM IMPIEGATO Cfr. La query Esempio 8 su azienda2002.mdb

  24. Esempio 9 Determinare il totale degli stipendi degli impiegati del Dipartimento “Research” e lo stipendio minimo, massimo e medio. SELECT SUM(Stipendio_annuo), MAX(Stipendio_annuo), MIN(Stipendio_annuo), AVG(Stipendio_annuo) FROM IMPIEGATO, DIPARTIMENTO WHERE Identificativo_dipartimento=Id_Dipartimento AND Nome_dipartimento=‘Research’ Cfr. La query Esempio 9 su azienda2002.mdb

  25. Esempio 10 Determinare il numero degli impiegati SELECT COUNT(*) FROM IMPIEGATO Cfr. La query Esempio 10 su azienda2002.mdb

  26. Esempio 11 Determinare il numero degli impiegati del Dipartimento “Research”. SELECT COUNT(*) FROM IMPIEGATO, DIPARTIMENTO WHERE Identificativo_dipartimento=Id_Dipartimento AND Nome_dipartimento=‘Research’ Cfr. La query Esempio 11 su azienda2002.mdb ESEMPIO 11 B: Conta gli stipendi diversi nell’azienda SELECT COUNT (DISTINCT Stipendio_annuo) FROM IMPIEGATO

  27. Esempio 12 Determinare gli impiegati che hanno almeno due familiari. SELECT Nome, Cognome FROM IMPIEGATO WHERE (SELECT Count(*) FROM FAMILIARE WHERE CF_Impiegato = Codice_Fiscale) >= 2 Cfr. La query Esempio 12 su azienda2002.mdb

  28. Query con raggruppamento • E’ possibile dividere la tabella risultante da una query con operatori aggregati in sottoinsiemi, raggruppando le righe che contengono gli stessi valori per un insieme di attributi e restituendole in ordine crescente(default) o descrescente(DESC). SELECT count(*) AS nomeAlias FROM nomeTabella group by nomeAttributo Operatore di raggruppamento

  29. Query con raggruppamentoEsempio 13 Per ogni dipartimento si vuole visualizzare l’identificativo, il numero degli impiegati ed il loro stipendio medio. SELECT Id_Dipartimento, COUNT(*), AVG(Stipendio_annuo) FROM IMPIEGATO GROUP BY Id_Dipartimento Cfr. La query Esempio 13 su azienda2002.mdb

  30. Esempio 14 Per ogni progetto in cui lavorano più di 2 impiegati, ricercare l’identificativo di progetto, il nome e il numero di impiegati coinvolti. SELECT Identificativo_progetto, Nome_progetto, Count(*) FROM PROGETTO, LAVORA WHERE Identificativo_progetto=Id_Progetto GROUP BY Identificativo_progetto, Nome_progetto HAVING Count(*)>2 Cfr. La query Esempio 14 su azienda2002.mdb

  31. Esempio 15 Per ogni dipartimento in cui lavorano più di 2 impiegati, ricercare l’identificativo di dipartimento e il numero di impiegati che guadagnano più di 30,000 $. SELECT Nome_dipartimento, Count(*) FROM DIPARTIMENTO, IMPIEGATO WHERE Identificativo_dipartimento=Id_Dipartimento AND Stipendio_annuo>30000 AND Id_Dipartimento In (SELECT Id_Dipartimento FROM IMPIEGATO GROUP BY Id_Dipartimento HAVING COUNT(*)>2) GROUP BY Nome_dipartimento ORDER BY Nome_dipartimento DESC Cfr. La query Esempio 15 su azienda2002.mdb

  32. Tipi di Join • Join interno(default): sono incluse nel risultato le tuple di una tabella che hanno una tupla corrispondente secondo la condizione di join nell’altra tabella. La condizione di JOIN in questo caso è sempre di uguaglianza (EQUI JOIN). • Join naturale: la condizione di join è implicita e rappresentata dall’uguaglianza di ogni coppia di attributi che hanno lo stesso nome in entrambe le tabelle(l’uguaglianza dei nomi si può imporre anche con l’aliasing) • Join esterno sinistro (rispettivamente destro): include tutti i record della tabella di sinistra (rispettivamente di destra) anche se non esistono valori corrispondenti ai record nella tabella di destra (rispettivamente di sinistra)

  33. E’ possibile determinare il tipo di join dalle proprietà del join

  34. Esempio 16Query di Selezione: JOIN INTERNO Si vuole un elenco completo dei nomi e degli indirizzi degli impiegati che lavorano nel dipartimento “Research” (analogo esempio 2) SELECT Nome, Cognome, Data_di_nascita FROM (DIPARTIMENTO JOIN IMPIEGATO ON Identificativo_dipartimento = Id_Dipartimento) WHERE Nome_dipartimento=‘Research’ La parola chiave INNER prima di JOIN è sottintesa. Cfr. La query Esempio 16 su azienda2002.mdb

  35. JOIN NATURALE :ESEMPIO Supponiamo che nel DB azienda2002.mdb sia la relazione IMPIEGATO che la relazione DIPARTIMENTO abbiano l’attributo Id_Dipartimento. La query precedente può essere scritta come segue: • SELECT Nome, Cognome, Data_di_nascita • FROM (DIPARTIMENTO NATURAL JOIN IMPIEGATO) • WHERE Nome_dipartimento=‘Research’ La condizione IMPIEGATO.Id_Dipartimento=DIPARTIMENTO.Id_Dipartimento è sottintesa dalla parola NATURAL

  36. Esempio:JOIN NATURALE con aliasing Gli attributi vengono rinominati con l’aliasing per poter effettuare il JOIN NATURALE delle tabelle. La query 16 è ancora equivalente alla seguente. SELECT Nome, Cognome, Data_di_nascita FROM (IMPIEGATO NATURAL JOIN (DIPARTIMENTO AS DIP(Id_Dipartimento, Nome_dipartimento, CF_Direttore, Inizio_direzione))) WHERE Nome_dipartimento=‘Research’ Ora Id_Dipartimento ha lo stesso nome in entrambe le tabelle e la condizione di join verrà implicitamente applicata ad esso.

  37. Esempio 17Query di Selezione: JOIN ESTERNO Per ogni impiegato, ricercare il cognome del supervisore. SELECT I.Cognome AS Cognome_Impiegato, S.Cognome AS Cognome_Superiore FROM (IMPIEGATO I LEFT OUTER JOIN IMPIEGATO S ON I.CF_Superiore=S.Codice_Fiscale) L’INNER Join non avrebbe restituito gli impiegati per i quali non è specificato un superiore. La parola chiave OUTER può essere omessa con l’uso di LEFT e RIGHT. Cfr. La query Esempio 17 su azienda2002.mdb

  38. Esempio 18Query di Selezione: JOIN ANNIDATI Per tutti i progetti con sede a ‘Stafford’, si richiede l’identificativo del progetto e del dipartimento che lo conduce, nonché il nome e la data di nascita del direttore di tale dipartimento. SELECT Identificativo_progetto, Identificativo_dipartimento, Cognome, Data_di_nascita FROM (( PROGETTO JOIN DIPARTIMENTO ON Id_dipartimento_coord =Identificativo_dipartimento) JOIN IMPIEGATO ON CF_Direttore = Codice_Fiscale) WHERE Sede =‘Stafford’ Cfr. La query Esempio 18 su azienda2002.mdb

  39. JOIN NEL DATABASE Molte queryprecedenti possono essere riscritte utilizzando l’operazione di JOIN. Nel DB azienda2002.mdb ci sono le versioni con l’uso del JOIN di alcune delle query 1-15, sotto il nome “Esempio X join”.

  40. Query di inserimento • Possono essere inserite singole righe all’interno delle tabelle: insert into Nometabella [Lista Attributi] <values (ListadiValori SelctSQL)> • Oppure insiemi di righe come risultato di una precedente selezione

  41. Esempio 19:Inserimento di righe INSERT INTO IMPIEGATO( Nome, Cognome, Codice_Fiscale ) VALUES (‘Richard’, ‘Johnson’, ‘1012345643569876’) Gli attributi non elencati assumono valore NULL nelle tupla inserita. Cfr. La query Esempio 19 su azienda2002.mdb

  42. Esempio 20 : Query di creazione tabella Viene creata una tabella temporanea, che può essere manipolata (interrogata, cancellata,…) come una normale tabella. Nella tabella creata vengono inseriti dati con una query successiva(i.e. Esempio 21). La tabella risultante è però una vista sul database, ovvero una query materializzata, che non viene aggiornata automaticamente quando le tabelle da cui è ricavata vengono aggiornate. CREATE TABLE INFO_DIP (Nome_Dip VARCHAR(15), Numero_Impiegati INTEGER, Totale_Stipendi INTEGER) Cfr. La query Esempio 20 su azienda2002.mdb

  43. Esempio 21: Query di inserimento risultati Nella tabella creata con la query precedente si inseriscono i dati risultanti da un’altra query. INSERT INTO INFO_DIP (Nome_Dip, Numero_Impiegati, Totale_Stipendi) SELECT Nome_dipartimento, Count(*), Sum(Stipendio_annuo) FROM DIPARTIMENTO, IMPIEGATO WHERE Identificativo_dipartimento = Id_Dipartimento GROUP BY Nome_dipartimento Cfr. La query Esempio 21 su azienda2002.mdb ACCESS mette a disposizione uno specifico tipo di query( query di creazione tabella) per ottenere il risultato dell’esecuzione successiva delle 2 query precedenti: Cfr. La query Esempio 20-21 su azienda2002.mdb

  44. Aggiornamento di righe • E’ possibile aggiornare una o più righe mediante il comando update che presenta la seguente sintassi: update NomeTabella set Attributo = <Espressione | SelectSQL |null|default>{,Attributo =<Espressione |SelectSQL |null|default)} [where Condizione]

  45. Esempio 22: Aggiornamento di righe Il progetto numero 10 è stato spostato a ‘Bellaire’ ed è ora coordinato dal dipartimento numero 5. Aggiornare il database. UPDATE PROGETTO SET Sede = ‘Bellaire’, Id_ dipartimento_coord = ‘5’ WHERE Identificativo_progetto)= ’10’ Cfr. La query Esempio 22 su azienda2002.mdb

  46. Esempio 23: Aggiornamento di righe Il Dipartimento Research ha aumentato gli stipendi del 10%. Aggiornare il database. UPDATE IMPIEGATO SET Stipendio_annuo = 1.1* Stipendio_annuo WHERE Id_Dipartimento IN (SELECT Identificativo_dipartimento FROM DIPARTIMENTO WHERE Nome_dipartimento =‘Research’) Cfr. La query Esempio 23 su azienda2002.mdb

  47. Cancellazione di righe • La cancellazione di righe dalle tabella è effettuata dal comando delete, la cui sintassi è la seguente: delete from NomeTabella [where condizione]

  48. Esempio 24Cancellazione di righe DELETE FROM IMPIEGATO WHERE Codice_Fiscale = ‘1234567891011121’ Cancellare l’impiegato dal codice fiscale ‘1234567891011121’. Cfr. La query Esempio 24 su azienda2002.mdb

More Related