QL -...
Transcript of QL -...
Alice Pavarani
QL (Query Language)
QL – Query Language
V B - Sistemi Informativi Aziendali
Linguaggio di interrogazione dei dati, permette di:
Interrogare la base di dati per estrarre informazioni
Elaborare i dati
Il risultato di un’interrogazione è sempre una tabella
Il comando di interrogazione dei dati riflette la natura dichiarativa del linguaggio SQL: le interrogazioni sul database sono eseguite specificando solo cosa si vuole ottenere, senza preoccuparsi di problemi di rappresentazione e memorizzazione dei dati.
Struttura di base: SELECT-FROM-WHERE
V B - Sistemi Informativi Aziendali
La struttura più semplice di interrogazione di una base di dati è composta da 3 clausole:
SELECT
FROM
WHERE
Permette di estrapolare le informazioni, prese da una o più tabelle, che rispettano determinate condizioni
SELECT Attributi FROM Tabelle WHERE Condizioni
Esempi
V B - Sistemi Informativi Aziendali
Si ottiene l’elenco con Cognome, Nome, Residenza di tutti gli Impiegati
Si ottiene l’elenco con Cognome, Nome, Residenza di tutti gli Impiegati del Dipartimento con codice‘Prod’
Si ottiene l’elenco con ID, Cognome, Nome di tutti gli Impiegati del Dipartimento con codice‘Prod’ e che risiedono a ‘Torino’
Si ottiene l’elenco completo di tutte le informazioni degli Impiegati residenti a ‘Roma’
SELECT Cognome, Nome, Residenza FROM Impiegato
SELECT Cognome, Nome, Residenza FROM Impiegato WHERE Dipartimento = ‘Prod’
SELECT IDImpiegato, Cognome, Nome FROM Impiegato WHERE Dipartimento = ‘Prod’ AND Residenza = ‘Torino’
SELECT * FROM Impiegato WHERE Residenza = ‘Roma’
Si usa una , (virgola) per separare più attributi da visualizzare nella clausola SELECT
PROIEZIONE (taglio verticale)
SELEZIONE (taglio orizzontale)
SELECT
V B - Sistemi Informativi Aziendali
La clausola SELECT richiede che venga specificata la lista degli attributi (colonne) da elencare
Se sono necessari tutti gli attributi delle tabelle indicate, è sufficiente scrivere SELECT *
Predicati
ALL (di default) impone che nel risultato vengano incluse tutte le righe che soddisfano alle condizioni contenute nel comando
DISTINCT riduce ad un’unica riga tutte le righe duplicate ottenute come risultato di un’interrogazione
Calcolo di espressioni
V B - Sistemi Informativi Aziendali
Con il comando SELECT si può anche richiedere il calcolo di espressioni sugli attributi dell’interrogazione
La tabella risultante contiene una colonna aggiuntiva con i risultati del calcolo ottenuto per ogni riga
Il calcolo di espressioni non modifica il valore dell’attributo in tabella
Si ottiene una tabella composta da 4 colonne: Cognome, Nome, Attuale, Nuovo
dove, per ogni riga, viene visualizzato sia lo Stipendio memorizzato nella tabella Impiegato (Attuale), sia il nuovo stipendio se il precedente fosse aumentato del 5% per tutti gli impiegati del dipartimento ‘Mag’ (Nuovo)
SELECT Cognome, Nome, Stipendio AS Attuale, Stipendio*1.05 AS Nuovo FROM Impiegato WHERE Dipartimento = ‘Mag’
Si utilizza la clausola AS per rinominare una colonna
Condizioni di ricerca: WHERE
V B - Sistemi Informativi Aziendali
I criteri di selezione delle righe vengono determinati in base ad una o più condizioni (Vero/Falso)
Nella scrittura di una condizione sono leciti i simboli: Operatori di confronto = , < , > , <= , >= , <> Connettivi logici AND , OR , NOT Altri predicati BETWEEN / NOT BETWEEN controlla se un valore è compreso in un
intervallo, estremi inclusi
IN / NOT IN controlla se un valore appartiene o meno (NOT IN) ad un insieme di valori precisato dopo il predicato
LIKE / NOT LIKE confronta il valore di un attributo di tipo carattere con un modello di stringa eventualmente composto da caratteri jolly
IS NULL / IS NOT NULL confronta il valore di una colonna con NULL
Ordine di precedenza: NOT AND OR
L’uso di questo predicato il solo modo per controllare la presenza del valore NULL in una colonna
Il predicato LIKE
V B - Sistemi Informativi Aziendali
Caratteri jolly (metacaratteri):
_ (underscore) indica uno e un solo carattere qualsiasi in quella posizione della stringa
% (percento) indica una sequenza di zero o più caratteri in quella posizione della stringa
Esempi
Attr LIKE ‘xyz%’ tutti gli attributi che iniziano con ‘xyz’
Attr LIKE ‘%xyz’ tutti gli attributi che finiscono con ‘xyz’
Attr LIKE ‘%xyz%’ tutti gli attributi che comprendono ‘xyz’
Attr LIKE ‘_xyz’ tutti gli attributi che iniziano con un carattere e poi ‘xyz’
Il predicato LIKE utilizzato con un modello di stringa che non contiene caratteri jolly è equivalente all’operatore =
In Access: • _ ? • % *
Esempi
V B - Sistemi Informativi Aziendali
Si ottiene l’elenco con Cognome, Nome, Residenza di tutti gli Impiegati aventi Stipendio compreso tra 30000€ e 50000€ (equivale a >= AND <=)
Si ottiene l’elenco con IDImpiegato, Cognome, Nome di tutti gli Impiegati con Residenza a Roma, Milano o Torino
(equivale al connettivo OR tra le condizioni di uguaglianza)
Si ottiene l’elenco con IDImpiegato, Cognome, Nome di tutti gli Impiegati tranne quelli con Residenza a Roma o a Milano (equivale al connettivo AND tra le condizioni di disuguaglianze)
Si ottiene l’elenco con ID, Cognome,
Nome di tutti gli Impiegati di cui non è nota la Residenza
SELECT Cognome, Nome, Residenza FROM Impiegato WHERE Stipendio BETWEEN 30000 AND 50000
SELECT IDImpiegato, Cognome, Nome FROM Impiegato WHERE Residenza IN (‘Roma’, ‘Milano’, ‘Torino’)
SELECT IDImpiegato, Cognome, Nome FROM Impiegato WHERE Residenza IS NULL
SELECT IDImpiegato, Cognome, Nome FROM Impiegato WHERE Residenza NOT IN (‘Roma’, ‘Milano’)
JOIN
V B - Sistemi Informativi Aziendali
Grazie al comando SELECT è possibile operare su più tabelle: operazione di JOIN
Per realizzare un collegamento tra due tabelle occorre specificare nella clausola WHERE una condizione che coinvolga un attributo comune:
chiave primaria ↔ chiave esterna
Si ottiene l’elenco di tutti gli Impiegati con i dati del Dipartimento in cui lavorano
Se la condizione è di uguaglianza si chiama equi-join
SELECT * FROM Impiegato, Dipartimento WHERE Impiegato.Dipartimento = Dipartimento.Codice
INNER JOIN
V B - Sistemi Informativi Aziendali
La versione SQL adottata da Access prevede un’altra istruzione per realizzare il JOIN tra due tabelle, del tutto equivalente alla precedente ma con una sintassi diversa
Se si utilizza l’INNER JOIN la condizione di collegamento è scritta nella clausola FROM, dopo la parola chiave ON
SELECT * FROM Impiegato INNER JOIN Dipartimento ON Impiegato.Dipartimento = Dipartimento.Codice
SELF JOIN
V B - Sistemi Informativi Aziendali
Viene chiamata SELF JOIN quella particolare operazione che collega una tabella con sé stessa
Occorre utilizzare un alias per il nome della tabella, per evitare ambiguità nei ruoli dell’associazione
Esempio:
PERSONA (CF, Cognome, Nome, Coniuge) in cui il campo Coniuge è chiave esterna riferita al campo CF di Persona
Si ottiene l’elenco con Cognome e Nome di ogni coppia di Coniugi
SELECT Marito.Cognome, Marito.Nome, Moglie.Cognome, Moglie.Nome FROM Persona AS Marito, Persona AS Moglie WHERE Marito.CF = Moglie.Coniuge
B
JOIN esterni
V B - Sistemi Informativi Aziendali
Generalmente, l’equi-join NON include nella selezione risultante quelle righe di una delle due tabelle che non trovano corrispondenza nell’altra (valori nulli)
Esistono tre diverse forme di JOIN, detti join esterni, per rispondere a questa esigenza
Left-join … FROM TabellaA LEFT JOIN TabellaB ON ChiaveA = ChiaveB …
Right-join … FROM TabellaA RIGHT JOIN TabellaB ON ChiaveA = ChiaveB …
Full-join … FROM TabellaA FULL JOIN TabellaB ON ChiaveA = ChiaveB …
A B
A
B A B
Esempio:
V B - Sistemi Informativi Aziendali
Left-join
Seleziona la Descrizione di tutti i Dipartimenti e, se noti, il Nome ed il Cognome degli Impiegati che vi lavorano
Seleziona la Descrizione di tutti i Dipartimenti senza Impiegati
SELECT Descrizione, Nome, Cognome FROM Dipartimento LEFT JOIN Impiegato ON Dipartimento.Codice = Impiegato.Dipartimento
SELECT Descrizione, Nome, Cognome FROM Dipartimento LEFT JOIN Impiegato ON Dipartimento.Codice = Impiegato.Dipartimento WHERE Cognome IS NULL
V B - Sistemi Informativi Aziendali
Ordinamenti
V B - Sistemi Informativi Aziendali
La clausola ORDER BY consente l’ordinamento dei risultati di un’interrogazione, secondo una o più colonne in senso:
ASC (default) = crescente per i numeri, alfabetico per le stringhe (dalla A alla Z)
DESC = decrescente per i numeri, alfabetico inverso per le stringhe (dalla Z alla A)
Il valore NULL compare, di norma, all’inizio delle sequenze crescente e alla fine delle sequenze decrescenti
SELECT Cognome, Nome FROM Impiegato WHERE Dipartimento = ‘Mag’ ORDER BY Cognome
Seleziona Cognome e Nome degli Impiegati del Dipartimento “Mag” in ordine alfabetico per Cognome
SELECT Cognome, Stipendio FROM Impiegato WHERE Dipartimento = ‘Mag’ ORDER BY DESC Stipendio
Seleziona Cognome e Stipendio degli Impiegati del Dipartimento “Mag” in ordine decrescente di Stipendio
Funzioni di aggregazione
V B - Sistemi Informativi Aziendali
Le funzioni di aggregazione agiscono sui valori di una singola colonna e restituiscono un unico valore (il massimo, il minimo, la somma, …)
Le righe considerate per l’aggregazione sono quelle prodotte dall’interrogazione, cioè rispettano le eventuali condizioni (clausola WHERE)
Possono essere utilizzate solo nella clausola SELECT oppure nella clausola HAVING
Funzioni di aggregazione
V B - Sistemi Informativi Aziendali
COUNT conta il numero di righe risultanti dall’interrogazione
SUM calcola la somma dei valori contenuti nella colonna
AVG calcola la media dei valori contenuti nella colonna
MIN / MAX restituisce il minimo/massimo tra i valori della colonna
Ovviamente su attributi di tipo numerico
Anche su attributi di tipo carattere (seguendo l’ordine alfabetico)
SELECT COUNT (*) FROM Impiegato WHERE Residenza = ‘Milano’
Restituisce il numero di Impiegati residenti a Milano
Si può specificare il nome dell’attributo o il carattere *: nel primo caso non vengono conteggiate le righe con valore NULL nell’attributo specificato
SELECT SUM (Stipendio) FROM Impiegato WHERE Dipartimento = ‘Amm’
Restituisce la somma degli Stipendi degli Impiegati del Dipartimento ‘Amm’
SELECT AVG (Stipendio) FROM Impiegato WHERE Dipartimento = ‘Amm’
Restituisce la media degli Stipendi degli Impiegati del Dipartimento ‘Amm’
SELECT MAX (Stipendio) FROM Impiegato
Restituisce lo Stipendio massimo tra tutti gli Impiegati
NB: L’unica funzione di aggregazione che considera anche i valori NULL è COUNT(*)
Raggruppamenti
V B - Sistemi Informativi Aziendali
Le funzioni di aggregazione condensano le informazioni di una tabella in un solo valore (il numero di righe, la somma, il massimo, …)
Spesso è necessario sintetizzare i valori per classi omogenee secondo opportuni criteri di raggruppamento
Per tale scopo si utilizza la clausola GROUP BY
GROUP BY
V B - Sistemi Informativi Aziendali
La clausola GROUP BY permette di raggruppare un insieme di righe aventi lo stesso valore nelle colonne indicate dalla clausola
Produce una riga di risultati per ogni raggruppamento
NOTA BENE:
Quando si utilizza una clausola GROUP BY, tutti gli attributi che compaiono nella lista della clausola SELECT devono essere inclusi nella clausola GROUP BY oppure devono essere argomenti di una funzione di aggregazione
Esempio: GROUP BY
V B - Sistemi Informativi Aziendali
Elenca il codice dei dipartimenti (Dipartimento) in cui lavorano gli Impiegati, la somma degli stipendi (Stipendi) ed il numero di impiegati (NumDipendenti) per ogni Dipartimento
L’istruzione viene eseguita per passi:
Le righe sono raggruppate per Dipartimento (clausola GROUP BY)
Le funzioni di aggregazione sono applicate singolarmente su ogni
raggruppamento
SELECT Dipartimento, COUNT (ID) AS NumDipendenti, SUM (Stipendio) AS Stipendi FROM Impiegato GROUP BY Dipartimento
HAVING
V B - Sistemi Informativi Aziendali
La clausola HAVING permette di imporre condizioni sui gruppi, creati precedentemente tramite la clausola GROUP BY
Permette di visualizzare i soli raggruppamenti che soddisfano alle condizioni della clausola HAVING
Specifica una condizione che, generalmente, controlla il valore restituito dalle funzioni di aggregazione
Elenca il codice dei dipartimenti in cui lavorano almeno 2 Impiegati, la somma degli stipendi (Stipendi)
ed il numero di impiegati (NumDipendenti) per ogni Dipartimento
SELECT Dipartimento, COUNT (ID) AS NumDipendenti, SUM (Stipendio) AS Stipendi FROM Impiegato GROUP BY Dipartimento HAVING COUNT (ID) >= 2
Comando SELECT
V B - Sistemi Informativi Aziendali
SELECT Elenco colonne da mostrare
FROM Tabelle da cui estrarre i record
WHERE Condizioni che i record devono verificare
GROUP BY Campi da considerare per i raggruppamenti
HAVING Condizioni sui raggruppamenti
ORDER BY Ordinamenti sulle colonne elencate