query builder - Ravagnati Sistemi

Release
Manuale
4.90
Tecnico - Operativo
QUERY BUILDER:
Gestione delle Viste e il Linguaggio SQL
Query Builder è uno strumento che consente di creare interrogazioni personalizzate (query
o viste), in aggiunta alle funzioni previste di standard dal programma, per ottenere
rapidamente qualsiasi tipo di informazione.
La caratteristica principale delle viste è la loro semplicità di esecuzione, la velocità di
risposta ad un altissimo numero di quesiti, e la possibilità di ottenere una stampa sia su
carta sia su file (excel).
La creazione di viste personalizzate può interessare qualsiasi dato memorizzato nel
Database: ogni query può essere inoltre visualizzata come normale voce di menù, ed
eventualmente può essere parametrizzata con i dati presenti in una maschera già attiva.
Si possono così creare delle viste sui dati del dataBase della Società che potranno essere
legate a una specifica maschera del programma, oppure indipendenti da esse, e che
potranno essere rese disponibili come voci di menù alla stregua di qualsiasi altra funzione.
Considerando che le potenzialità e l’utilizzo del modulo Query Builder è legato alla
conoscenza del Linguaggio SQL, e che il carattere molto tecnico che assume il manuale
presuppone determinate conoscenze informatiche, è presente un capitolo dedicato alle
istruzioni fondamentali e alle caratteristiche proprie del Linguaggio puro, in modo da
rispondere (almeno in parte) ai quesiti e alle curiosità del lettore.
2 – Manuale Tecnico - Operativo
Indice degli argomenti
1
QUERY BUILDER................................................................................................................. 3
1.1
Creazione e gestione della vista .................................................................................... 4
1.1.1
1.1.2
1.1.2.1
1.1.2.2
1.1.3
1.1.4
1.1.4.1
1.1.4.2
1.1.4.3
1.1.4.4
1.2
1.3
2
Modifica di una vista già creata..................................................................................... 5
Creazione di una nuova query ...................................................................................... 5
Esecuzione immediata della query ............................................................................... 8
Salvataggio della query ............................................................................................... 10
Proprietà della query ................................................................................................... 11
Altri Parametri per strutturare la query ........................................................................ 12
Unione di due o più tabelle (Join)................................................................................ 12
Applicare una Condizione (operatore Where)............................................................. 15
Raggruppamento e Ordinamento................................................................................ 18
Selezione delle prime N righe ..................................................................................... 21
Personalizzazioni dei Menù e delle Toolbar................................................................. 22
Creazione di una vista interna ad una Maschera ......................................................... 23
IL LINGUAGGIO SQL (CENNI) .......................................................................................... 28
2.1
Definizioni formali ......................................................................................................... 29
2.1.1
2.2
Il modello di base dati relazionale e il concetto di chiave ........................................... 31
COMANDI PER L’ESTRAZIONE DEI DATI ................................................................. 32
2.2.1
2.2.1.1
2.2.1.2
2.2.1.3
2.2.1.4
2.2.2
2.2.3
2.2.4
2.2.5
2.2.5.1
2.2.6
2.2.6.1
L’espressione SELECT ............................................................................................... 32
Rinomina di campi e tabelle con un ALIAS ................................................................. 34
Espressioni e l’uso di Operatori ARITMETICI ............................................................. 35
Operatori di AGGREGAZIONE ................................................................................... 36
Operatore Insiemistico UNION.................................................................................... 39
La Condizione WHERE e l’uso di Operatori RELAZIONALI ....................................... 40
Prelievo da più tabelle: Operatori di JOIN................................................................... 47
Criteri di ordinamento ORDER BY .............................................................................. 50
QUERY DI RAGGRUPPAMENTO: criterio GROUP BY............................................. 51
La clausola HAVING ................................................................................................... 53
QUERY ANNIDATE (Subquery) ................................................................................. 55
Subquery nella clausola WHERE................................................................................ 55
2.2.6.1.1
2.2.6.2
2.3
Query CORRELATE ............................................................................................... 58
Subquery nella clausola FROM .................................................................................. 58
COMANDI PER LA MODIFICA DEI DATI.................................................................... 59
2.3.1
2.3.2
Il comando DELETE.................................................................................................... 59
Il comando UPDATE ................................................................................................... 60
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 3
1 Query Builder
Il modulo Query Builder è stato creato per rendere ancora più flessibile e personalizzabile la
procedura: l’obiettivo è quello di creare un interfaccia semplice per la consultazione di tutti i dati
inseriti nel sistema, con la possibilità di generare una reportistica sia su carta sia su file (excel).
Quotidianamente viene inserita nel gestionale un’ampia gamma di dati, vincolati alla gestione
dei processi interni: codificare un’anagrafica clienti, inserire un ordine, evaderlo, contabilizzarne
la fattura… frutto di operazioni fondamentali per la normale operatività, nel tempo costituiscono
un patrimonio informativo molto ampio e complesso.
Le stesse informazioni vengono spesso richieste per risolvere esigenze quotidiane che si
differenziano dai flussi standardizzati del sistema ERP, come verificare tutti i clienti che hanno
acquistato un prodotto per gestire un malino mirato, estrarre tutti i numeri telefonici facenti capo
ad un agente per effettuare un’azione di telemarketing….
Query Builder è studiato per fornire all’utente uno strumento di navigazione e interrogazione
dei dati completo e potente, così da risolvere qualsiasi esigenza con la massima efficienza
operativa: utilizzato in maniera adeguata, permette infatti agli operatori di velocizzare i flussi di
lavoro, ed al management di determinare le scelte strategiche.
Il cuore di Query Builder è il linguaggi odi SQL1, che garantisce l’estrema accessibilità ai dati e
la possibilità di estrapolarli in modo rapido e preciso; una delle particolarità del modulo è di
permettere anche ad utenti che non conoscono il linguaggio SQL di essere guidati nella
creazione di query2, grazie ad un’interfaccia di semplice utilizzo.
Un altro plus della funzione è trasformare le query elaborate in veri e propri pulsanti da inserire
all’interno dei menù (scegliendone l’esatta posizione e l’eventuale icona) in modo da essere
richiamabili in qualsiasi momento: in questo modo, con un semplice click, anche un utente poco
esperto può ottenere report aggiornati, stampabili oppure esportabili in formati excel.
I vantaggi sostanziali del Query Builder sono:
; Potenza del linguaggio SQL, per la realizzazione di query personalizzate
; Massimo sfruttamento delle qualità di flessibilità della base dati SQL, per possibilità di
accedere ai dati e alle informazioni codificate nel sistema gestionale in modo completo
; Semplicità di utilizzo, anche da parte di utenti poco esperti grazie alla possibilità di
associare le interrogazioni a semplici funzioni utente.
; Possibilità di stampa immediata del risultato della query su cartaceo
; Possibilità di esportazione in formato Excel del risultato della query per poter creare
situazioni personalizzate e manipolare i dati con le procedure di Office.
1
Il linguaggio SQL di riferimento è nello standard Microsoft, come l’intero apllicativo
Si definisce Query un insieme di istruzioni in linguaggio Sql che permetti di ottenere le informazioni in base ai criteri
scelti. Una volta elaborata, i risultati sono gli elementi che compongono i report.
2
4 – Manuale Tecnico - Operativo
1.1 Creazione e gestione della vista
Il corpo principale della funzione Query Builder è costituito da una Maschera Iniziale che
elenca le viste al momento esistenti, ovvero le query già create. Dopo aver creato la vista, è
possibile trasformarla in una funzione da aggiungere a menù (insieme alle voci standard).
La creazione di una query può anche essere legata ad una particolare funzione della
procedura: in questo modo, è possibile creare delle viste parametrizzate legate ai dati
visualizzati a video (grazie alle potenzialità della Personalizzazione on Fly; i dettagli nel
paragrafo Personalizzazioni dei Menù e delle Toolbar
Si accede alla funzione dal Menù Utilità Æ Query Builder:
Nella finestra centrale, vengono elencate le viste già create: al primo accesso, non essendo
state salvate delle viste, la finestra apparirà vuota. Premendo il tasto destro del mouse (in un
qualsiasi punto della maschera attiva) appare un menù di scelta rapida:
•
•
•
•
Aggiungi nuova vista per creare una nuova query
Modifica vista
per modificare le query già create
Elimina vista
per eliminare le query già create
Proprietà vista
per visualizzare particolari informazioni relative alla query, quali il
nome, il testo della query, ecc…
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 5
1.1.1 Modifica di una vista già creata
Per la modifica delle query è sufficiente selezionare il Codice della query da modificare e
scegliere dal Menù di scelta rapida la voce Modifica Vista
La funzione è del tutto simile alla funzione di creazione (ved. Paragrafo Creazione di una nuova
query)
1.1.2 Creazione di una nuova query
Per la creazione delle query è stata studiata un’interfaccia che guida l’utente nelle scelte delle
informazioni che desidera elaborare. Dal Menù di scelta rapida, selezionando la voce
Aggiungi Nuova Vista appare la seguente maschera:
La maschera è organizzata in diverse finestre, collegate tra loro, che corrispondono alle
seguenti sezioni:
•
•
•
Tabelle: visualizza tutte le tabelle contenute nel Database della Società; è possibile
selezionare una o più tabelle da cui si desidera visualizzare i dati.
Campi disponibili: selezionate le tabelle nella sezione precedente, in automatico vengono
proposti tutti i campi contenuti nella tabella appena scelta
Campi da mostrare: in funzione dei dati che si vogliono mostrare, è possibile ‘spostare’ i
campi desiderati dalla sezione Campi Disponibili a questa sezione. Lo spostamento
avviene tramite i campi freccia:
6 – Manuale Tecnico - Operativo
(freccia destra) porta il campo nella sezione Campi da Mostrare
(freccia sinistra) toglie il campo dalla sezione Campi da Mostrare
L’ordine dei campi da mostrare è modificabile utilizzando i pulsanti
spostano i campi all’interno della finestra.
e
che
Esempio: si vuole avere un report che indichi il codice articolo, la descrizione e il prezzo in anagrafica
Per fare ciò, scegliere (con un click nella finestra) la Tabella degli Articoli: nella lista dei Campi
disponibili vengono visualizzati tutti i campi presenti. Da qui, si procede selezionando le voci CodArt,
Descrizione e PvendPub (che è il nome del campo che contiene l’informazione del prezzo) e cliccare sul
pulsante [Freccia destra] così da determinare quali Campi Mostrare nel report. I 3 campi scelti vengono
così visualizzati nella finestra Campi da mostrare.
Se non si seleziona alcun campo da mostrare, nella query saranno presi tutti i campi della
tabella selezionata, mentre se occorre visualizzare dati da più tabelle si procederà
selezionando una tabella alla volta e spostando in Campi da mostrare i rispettivi campi di
riferimento.
Una volta specificate le istruzioni che definiscono la vista che si vuole creare, contenente tutti i
parametri di base e i campi da visualizzare; cliccare sul pulsante:
: viene creata automaticamente la frase SQL, utilizzando i parametri
precedentemente impostati; il risultato viene visualizzato nel pannello posto
nella parte bassa della maschera Statement SQL.
Se i parametri vengono modificati (ad esempio, per aggiungere un nuovo
campo) è necessario cliccare nuovamente su questo pulsante per rigenerare
ed aggiornare la query modificata.
La frase SQL è modificabile manualmente: questo consente, ad esempio, di
costruire query più articolate permettendo ad utenti più esperti di
personalizzare sempre di più l’estrazione dei dati.
La modifica manuale della stringa creata avviene direttamente nel campo
Statement SQL: le modifiche dovranno essere sempre confermate (cliccando
sul pulsante Salva), in quanto un eventuale pressione del pulsante Crea SQL
ripristinerebbe le condizioni iniziali (in base ai parametri indicati nei campi
precedenti.
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 7
A questo punto, l’utente ha a disposizione una serie di strumenti per controllare il risultato della
query; in qualsiasi momento può inoltre decidere di confermare l’opzerazione.
8 – Manuale Tecnico - Operativo
1.1.2.1 Esecuzione immediata della query
Creata la frase SQL, tramite i seguenti pulsanti è possibile:
visualizzare il risultato della vista, mostrandone il risultato in una griglia:
Con Stampa viene visualizzata l’anteprima di stampa:
La modalità di stampa è attivabile anche mediante la combinazione di tasti Ctrl+S così
come avviene per le liste di consultazione del programma.
Da qui è possibile procedere alla stampa di una o più pagine.
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 9
Con Excel è possibile esportare i dati in formato MsExcel; viene mostrata la
maschera di navigazione di windows per scegliere dove salvare il file:
Il risultato sul file excel è quindi il seguente:
10 – Manuale Tecnico - Operativo
1.1.2.2 Salvataggio della query
Per salvare la vista e le eventuali modifiche apportate, cliccare su:
al primo salvataggio sarà necessario attribuire un nome alla vista, mentre ai
salvataggi successivi sarà possibile eventualmente modificare il nome già
attribuito.
I dettagli dei campi richiesti in questa funzione sono descritti nel paragrafo
Proprietà della query
Cliccando sul tasto Conferma viene salvata la vista e presentata nella maschera
iniziale:
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 11
1.1.3 Proprietà della query
A ogni vista sono assegnate una serie di proprietà, che vengono visualizzate sia all’atto del
salvataggio della vista, sia su specifica richiesta dell’utente, utilizzando la funzione Proprietà
vista o Modifica Vista (presenti nel menù di scelta rapida, accessibile dalla maschera iniziale
selezionando una query già creata e cliccando il tasto destro del mouse).
La finestra di Proprietà vista visualizza le seguenti informazioni, alcune delle quali sono
assegnate automaticamente dal sistema, altre definibili dall’utente.
•
•
•
•
•
Codice Identificativo: numero identificativo assegnato dalla procedura (non
modificabile). E’ un progressivo che identifica univocamente la vista.
Descrizione: nome della vista, che deve essere assegnato dall’utente e può essere
modificato in qualsiasi momento. Corrisponde al nome con il quale la vista sarà
visualizzata a Menù.
Statement SQL: sintassi della query SQL appena creata; anche questo campo è
direttamente modificabile.
Maschera di Rif: riporta il nome della form di riferimento della query, nel caso di
creazione di viste parametrizzate (v. paragrafo Creazione di una vista interna ad una
Maschera)
ID Funzione: numero di funzione fornito in automatico dalla procedura (non
modificabile). E’ il numero della funzione con la quale la vista sarà identificata nella
tabella Funzioni del database Comune: tale codice viene creato aggiungendo al numero
“3” (che rappresenta il numero riservato alle viste personalizzate nell’elenco delle
funzioni Utente) il Codice Identificativo.
Da questa funzione è quindi sempre possibile modificare la Descrizione e anche la sintassi
sql (Statement SQL).
12 – Manuale Tecnico - Operativo
1.1.4 Altri Parametri per strutturare la query
Il linguaggio Sql permette di costruire una serie di query in grado di estrapolare le informazioni
in modo mirato in base alle esigenze informative dell’utente, utilizzando delle particolari
istruzioni (che nel seguito andiamo a dettagliare) per collegare, ordinare e filtrare i dati.
1.1.4.1 Unione di due o più tabelle (Join)
Molti dati sono suddivisi su più tabelle, e spesso è necessario unirli per ottenere
un’informazione precisa. Nel linguaggio Sql questa istruzione si chiama Join (collegamento)
L’operatore Join permette di legare dati appartenenti a tabelle diverse: con Query Builder è
possibile costruire Inner Join, grazie ad una maschera che elenca i campi delle due tabelle che
devono essere uniti nella query finale.
La condizione di Join può essere dunque impostata solamente se sono state selezionate
almeno due tabelle. Premendo il pulsante Imposta Joins appare la seguente maschera:
Esempio: si vuole elaborare un report che indichi il codice articolo, la descrizione, il prezzo in anagrafica
e anche l’unità di misura alternativa correlata all’articolo:
Per richiedere queste informazioni, è necessario impostare il join (correlazione) tra la tabella ArtUm dove
risiede l’informazione sulle unità di misura alternative degli articoli e la tabella Articoli. La relazione che
collega le due tabelle avviene tramite il CodArt, ovvero il codice articolo, in quanto può esistere un solo
codice articolo al quale possono essere associate diverse unità di misura alternativa – legame uno a
molti -
Premendo il pulsante Imposta Joins appare una maschera, che visualizza (nella prima
finestra a sinistra) l’elenco delle tabelle che sono state selezionate per la query.
Per costruire la Join (legame di correlazione) procedere così:
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 13
1. selezionare le due tabelle sulla quale effettuare il legame
2. nelle finestre successive vengono visualizzati i rispettivi campi (dell’una e dell’altra
tabella)
3. selezionare il campo che rappresenta il legame tra le due tabelle
Tabella
Articoli
Tabella
ArtUm
4. cliccare sul pulsante Aggiungi Join alla Query
5. cliccare sul pulsante Chiudi
6. alla chiusura, sarà visualizzata nella maschera principale la condizione di Join appena
creata:
7. cliccare sul pulsante Crea SQL per aggiornare il testo della vista: la procedura
costruirà la query con la sintassi SQL adeguata.
A sinistra sono elencate le tabelle che sono state selezionate per la query; selezionando tali
tabelle nella maschera vengono mostrati i campi corrispondenti, che è possibile utilizzare per
costruire la Join (legame di correlazione). È sufficiente selezionare i campi che definiscono il
criterio di legame tra le tabelle e premere il pulsante Aggiungi Join alla Query per creare la
correlazione.
Chiudendo la maschera sarà visualizzata, nella maschera principale, la condizione di join
appena creata:
Per gli utenti più esperti è sempre attiva la possibilità di intervenire manualmente sulla sintassi della
query appena generata, apportando anche altre modifiche direttamente nella stringa creata.
Esempio – proseguendo l’esempio precedente, una volta creata la correlazione dei campi e selezionato
quali informazioni avere nel report, premendo sul pulsante Esegui viene presentata la seguente
maschera:
14 – Manuale Tecnico - Operativo
Eseguendo la query, sono stati trovati solo due articoli che hanno l’unità alternativa di misura.
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 15
1.1.4.2 Applicare una Condizione (operatore Where)
La condizione di Where permette di creare delle query restringendo il campo di ricerca,
ottenendo per esempio informazioni che riguardano un articolo, un cliente, ecc.
La selettività della ricerca rende la stessa più efficace.
Le condizioni di WHERE possono essere impostate tramite i campi della maschera posti al di
sotto delle liste di tabelle e campi:
•
•
Nome campo (ComboBox) visualizza i campi disponibili, in funzione della tabella scelta in
precedenza;
Operatore (ComboBox) si può definire quale operazione di confronto deve
essere seguito per la ricerca dei dati. Vengono elencati gli usuali operatori
aritmetici (=, <>, >, >=, <, <=), oltre all’operatore LIKE, da utilizzare per il
confronto di stringhe (insieme di parole). Tale operatore si comporta come
un operatore di uguaglianza arricchito dal supporto per la coppia di caratteri
speciali “?” e “*”. Il carattere speciale “?” rappresenta un carattere qualsiasi.
Il carattere speciale “*” rappresenta una stringa di un numero arbitrario
(eventualmente anche zero) di caratteri qualsiasi. I due simboli ? e *
possono anche essere utilizzati insieme.
Esempi:
• M* indica una qualsiasi stringa che inizia con la lettera M (es: Milano, Motore…).
• ????A indica una stringa di 5 caratteri di cui l’ultimo è una A (es: ELICA, MARIA…).
• *R? indica una qualsiasi stringa il cui penultimo carattere è una R (es; FERRARA,
TORO, FIORE…).
• BANC? indica una stringa formata da BANC e seguita da un qualsiasi carattere (es:
BANCA, BANCO).
• BANC* indica una stringa formata da BANC e seguita da una qualsiasi sequenza di
caratteri (es: BANCA POPOLARE DI MILANO, BANCO DI DESIO…).
• *ST* indica una qualsiasi stringa che contiene la sequenza ST (es: ASTA, CESTA,
GESTIONE…).
•
Valore (ComboBox) per inserire l’espressione con cui si vuole confrontare il valore del
campo selezionato: sono accettati tutti i caratteri numerici, alfanumerici e date a seconda
del tipo del campo a cui il valore viene associato.
Esempio, se la ricerca deve avvenire per prezzo per un importo maggiore di 1.000 Euro allora il
campo sarà di tipo numerico.
il pulsante Lista possibili valori permette automaticamente
di visualizzare in anteprima i valori del campo presenti nel
DataBase.
16 – Manuale Tecnico - Operativo
Esempio: si vuole elaborare un report che indichi il codice articolo, la descrizione, il prezzo in anagrafica
solo per gli articoli che hanno un prezzo superiore ad Euro 1.000.
In questo caso la condizione where deve indicare il campo PvendPub > 1.000
Una volta definiti i campi che si vogliono portare nel report occorre definire la condizione nel seguente
modo:
Scegliere il Nome Campo: Articoli.PvendPub;
l’operatore: >; il valore: 1.000.
cliccare sul pulsante Aggiungi come And
Un utente può creare più condizioni di WHERE in una sola query, aggiungendole e
concatenandole tramite i pulsanti
e
La differenza tra i due pulsanti risiede nel modo con cui i dati saranno filtrati:
• Per la condizione AND tutti i criteri di WHERE devono essere soddisfatti affinché il dato
venga estratto nella vista
• Per la condizione OR basta che solo un parametro all’interno del WHERE sia
soddisfatto affinché il dato venga estratto nella vista
Esempio: Al report elaborato nell’esempio precedente, si vuole aggiungere un ulteriore filtro per estrarre
i soli articoli che hanno la descrizione che inizia per H.
In questo caso, nella condizione di WHERE già indicata ( il campo PvendPub > 1.000) va aggiunta la
nuova condizione nel seguente modo:
scegliere il Nome Campo Articoli.Descrizione;
l’operatore: like; il valore: H*.
cliccare sul pulsante Aggiungi come And
Il criteri appena scelti risulteranno restrittivi nel senso che un articolo deve avere entrambi le
caratteristiche per essere scelto.
Infatti se viene eseguita la query il risultato sarà mil seguente, in quanto non esistono dati per i parametri
selezionati nella ricerca:
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 17
Utilizzando invece il criterio OR la selezione non è restrittiva e quindi:
scegliendo il Nome Campo Articoli.Descrizione;
l’operatore: like; il valore: H*.
cliccare sul pulsante Aggiungi come Or
Eseguendo la ricerca il risultato sarà il seguente:
Come si può notare nessun articolo ha una descrizione che inizia per H, ma comunque ne esistono
quattro che hanno un prezzo superiore a 1.000 euro.
18 – Manuale Tecnico - Operativo
1.1.4.3 Raggruppamento e Ordinamento
Una volta determinati quali dati ricercare è possibile definire se raggruppare le informazioni e/o
ordinarle.
Raggruppato per
Consente di definire eventuali condizioni di raggruppamento.
Per funzionare correttamente, deve essere eseguito su tutti i campi che sono stati scelti come
elementi della ricerca (così come previsto dallo standard del linguaggio SQL).
La procedura prevede la possibilità di indicare in Raggruppato per un solo campo: gli altri
campi devono essere aggiunti manualmente direttamente nel Statement SQL, dopo che è stata
creata la frase SQL, editando la query nella parte inferiore della maschera3.
Esempio - Si vuole conoscere quali categorie merceologiche sono presenti negli articoli. Se la ricerca
fosse generica la vista elaborata dal Query Builder è:
Select Articoli.CodArt, Articoli.Descrizione, Articoli.PVendPub, Articoli.CatMerc From Articoli,ArtUM
3
Lo stesso vale per le funzioni di aggregazione (Count, Min, Max, Sum, Avg), che non possono essere definite in
automatico.
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 19
Essendo la ricerca generica non è facile sapere quali sono le categorie merceologiche, utilizzando
l’operatore di raggruppamento invece si possono individuare in maniere dettaglia.
La regola per raggruppare è quella che solo i campi da ricercare possono essere raggruppati. In questo
modo selezionando solo la categoria merceologica come elemento di ricerca, la query elaborata dal
Query Builder è diventata:
Select Articoli.CatMerc From Articoli Group By Articoli.CatMerc
La ricerca è stata così limitata alle sole categorie merceologiche, e il risultato è il seguente:
Una volta generata la query di estrazione dei dati, è possibile intervenire sempre manualmente
sulla stringa, ad esempio, si potrebbe contare quanti articoli fanno parte di una categoria
merceologica, aggiungendo la funzione Count (conteggio):
Select count(Articoli.CatMerc) as num, articoli.CatMerc
From Articoli
Group By Articoli.CatMerc
20 – Manuale Tecnico - Operativo
Ordinato per
Per esporre i dati in un report in maniera chiara e di facile lettura, è spesso necessario definire
un criterio di ordinamento: nel campo ComboBox Ordinato per è possibile, a tal proposito,
scegliere quali campi devono essere ordinati e in che modo:
Asc ascendente
Desc discendente
Anche in questo caso è possibile selezionare un solo campo per l’ordinamento; se fosse
necessario creare altri ordinamenti, si può intervenire manualmente sulla vista utilizzando
sempre l’operatore ORDER BY.
Esempio - Si vuole elaborare un report che mostra la lista degli articoli ordinata per prezzo, per sapere
quale è l’articolo con il prezzo di vendita più alto. Selezionata la tabella Articoli e i campi che si desidera
visualizzare nel report come Codice articolo, descrizione, prezzo di vendite, si procede a definire
l’ordinamento per il campo PVendPub.
Scelti tutti i parametri con il pulsante Crea Sql, il query Builder genera la seguente query :
Il risultato è il seguente:
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 21
1.1.4.4 Selezione delle prime N righe
Se nella vista si vogliono visualizzare solo un certo numero di record (a partire dal primo), è
possibile specificare quanti records estrarre indicandone il numero nel campo Mostra primi N
records;
Esempio: riprendendo l’esempio precedente, dove venivano estratti tutti gli articoli ordinati per
prezzo, se si volesse conoscere solo i primi 5 (ovvero quelli che hanno il prezzo più alto),
generando la query sql i dati presentati sarebbero i seguenti:
Il Query Builder genera la seguente query:
22 – Manuale Tecnico - Operativo
1.2 Personalizzazioni dei Menù e delle Toolbar
Le query create con il Query Builder possono essere aggiunte come vere e proprie funzioni
personalizzate nei Menù e nella Toolbar, così da poter essere trovate ed utilizzate in modo
rapido e immediato da parte di qualsiasi utente.
Una volta creata la vista, è possibile scegliere se assegnargli un’icona e in quale Menù
posizionarla. Le modalità di personalizzazioni sono le consuete previste dal sistema gestionale:
con il tasto destro sulla Toolbar principale viene mostrato il seguente menù di scelta rapida, dal
quale, scegliendo Personalizza, si accede alla maschera di personalizzazione del menù
principale:
Nella categoria Vista saranno presenti le query create: per renderle visibili (nel menù oppure
nella toolbar), sarà sufficiente trascinare la voce nell’area ritenuta idonea.
Nel caso di aggiunta della query nella Toolbar, è consigliabile associare alla stessa un’icona
personalizzata (utilizzando l’apposita funzione Gestione Toolbar) in modo da rendere
maggiormente visibile la query, in una logica di economia di spazi visivi.
Con i normali strumenti della procedura si può anche abbinare prima una icona alla query in
modo da rendere maggiormente visibile la stessa anche in una logica di economia di spazi
visivi.
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 23
1.3 Creazione di una vista interna ad una Maschera
Molte funzioni presentano una specifica toolbar interna, nella quale è possibile inserire altre
sotto-funzioni correlate al task attivo. In queste maschere, è possibile associare query
personalizzate sui dati presenti nella maschera stessa.
In altre parole, il sistema permette la creazione di viste parametrizzate con i dati presenti nelle
maschere attive: è possibile creare una (o più) query che filtra i risultati in base ai valori
presenti, al momento del lancio, in un preciso campo della maschera.
Per ottenere questo risultato, l’esecuzione della funzione principale e la successiva creazione
della query deve avvenire dopo aver aperto la funzione cui si vuole collegare l’interrogazione.
E’ necessario effettuare i seguenti passaggi:
1. Aprire la funzione nella quale si vuole aggiungere l’interrogazione
2. Avviare il Query Builder, dal menù Utilità Æ Query Builder
3. Creare la query, utilizzando le modalità descritte nel paragrafo Creazione di una nuova
query
4. salvare la nuova query
Durante la creazione della query, sarà possibile specificare come Operatore di riferimento
delle condizioni di WHERE un campo della form. Dopo aver creato la query, sarà possibile
aggiungerla nella toolbar della maschera come funzione personalizzata:
1. dalla stessa funzione nella quale si è creata la query
2. inserire nella toolbar della maschera la vista appena creata
La query così creata potrà essere eseguita in fase di inserimento/variazione dei dati: il lancio
della funzione produrrà, come risultato, un’interrogazione che terrà conto dei dati inseriti
dall’utente in un preciso momento.
Ad esempio, ipotizziamo di voler conoscere, durante l’input delle bolle, gli ultimi 5 prezzi applicati al
cliente nelle vendite precedenti; dalla form delle bolle, creeremo la seguente istruzione (query di esempio
per archivio Access senza form new):
Select Top 10 BolleTest.NumDoc, MovMagDett.CodArt, MovMagDett.ImpUni,
MovMagDett.Qta, BolFatDett.Importo
FROM (BolFatDett INNER JOIN MovMagDett ON MovMagDett.IDdettDoc =
BolFatDett.ID)
INNER
JOIN
BolleTest
ON
BolFatDett.IDTest
=
BolleTest.ID
Where
(BolleTest.Cliente = '|txt(2)|' ) And (MovMagDett.CodArt =
'|grd(1)|' ) and (MovMagDett.Tipo = 'B')
order by BolleTest.NumDoc
desc
Nelle prossime proponiamo un esempio passo passo per verificare ogni passaggio.
24 – Manuale Tecnico - Operativo
Esempio: un esempio può aiutare a comprendere il meccanismo di relazione query/maschera.
Ipotizziamo che in anagrafica clienti si desideri visualizzare le fatture emesse per il cliente che
si sta visualizzando in quel momento in maschera: innanzitutto, prima di procedere alla
creazione della query, è necessario attivare la funzione di query builder per la maschera in
questione (clienti) - Fase 1.
A tal fine occorre accedere alla funzione dal menù Archivi Æ Anagrafica Clienti
Con il tasto destro sulla tollbar posta in alto a destra si inserisce la funzione Query Builder
Una volta inserita questa si presenterà nel seguente modo:
Una volta attivata la funzionalità si procede alla creazione della query (Fase 2)
Quindi dopo aver richiamato un cliente è possibile cliccare sull’icona del Query Builder, e quindi
sarà visualizzata la seguente maschera:
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 25
Come si nota nella parte alta della maschera viene mostrata tra parentesi quadre il nome della
maschera di riferimento. In questo caso non esistono altre query specifiche per l’anagrafica
cliente, l’unica funzione attiva è quella di Aggiungere una nuova vista.
Le modalità di inserimento sono uguali a quelle viste in precedenza, l’unica eccezione è nella
condizione WHERE dove bisognerà indicare il nome del campo di riferimento, ovvero il
parametro da passare alla query. Nell’esempio sarà il nome del cliente.
Cliccando sul pulsante Lista Valore Possibili la procedura mostra una serie di valori speciali,
ovvero la lista di tutti i campi disponibili nella maschera preceduti da un codice che definisce la
natura del campo. Ad esempio Pnl indica un campo pannello, Cbo un valore espresso nella
combo, Txt un campo di testo.
Quindi se si vuole associare la query al codice cliente basta selezionare la voce TXT(0) Codice
Cliente.
26 – Manuale Tecnico - Operativo
Una volta scelti i campi da visualizzare come anno della fattura, numero della fattura, data
fattura, cliente e importo totale si può creare la query SQL con il pulsante Crea SQL .
Come illustrato in precedenza, se è necessario inserire un criterio di ordinamento è sufficiente
scegliere per quale campo e in che modo (Asc o Desc) nella sezione Ordinato per.
Se la query è corretta si può procedere al suo salvataggio cliccando sul tasto Salva: in questo
caso sarà presentata la maschera delle proprietà dove dovrà essere indicato il nome con cui
salvare la vista. Nel nostro esempio si è scelto il nome “Lista Fatture”
Una volta creata la nuova lista è necessario personalizzare la toolbar dell’anagrafica clienti al
fine di avere sempre disponibile la nuova query (Fase 4)
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 27
Solo così si potrà visualizzare immediatamente la lista delle fatture per cliente.
Note operative: se nella lista delle viste non dovesse essere presente quella appena creata,
conviene uscire dalla procedura e rientrare al fine di permettere un corretto aggiornamento di
tutte le modiche apportate.
Richiamando il cliente è possibile sapere in tempo reale quali fattura sono state emesse:
28 – Manuale Tecnico - Operativo
2 Il linguaggio SQL (cenni)
Questa breve presentazione del linguaggio SQL è nata dall’idea di creare un’appendice utile sia
per tutti gli utenti che utilizzano Query Builder sia per gli utenti\installatori che vogliono usare la
frasi SQL nei campi personalizzati creati con la personalizzazione On-Fly.
Lo scopo è quello di introdurre l’utente alla comprensione del significato delle varie parti in cui si
compone un’istruzione SQL e all’acquisizione delle corrette regole di sintassi per la costruzione
di interrogazioni di un database. La presentazione teorica è corredata da esempi creati sullo
stesso database su cui lavora il programma.
Il linguaggio SQL (acronimo di Structured Query Language) è un linguaggio per la
definizione e la manipolazione dei dati; è un linguaggio oggi usato in numerosi DBMS4
disponibili come prodotti commerciali.
In origine SQL era chiamato SEQUEL (Structured English QUery Language) ed era utilizzato dall’IBM
Research come interfaccia per i primi database relazioni chiamati SYSTEM R. Attualmente, sono
numerosissimi i database che lo utilizzano, a partire dagli stessi DB2/DB£… di IBM, per terminare con
Oracle, SQL Server (Microsoft), Informix, Sybase, CA-Ingres (Computer Associates), MySQL, Postgree,
etc… Tuttavia, ogni DBMS ha apportato modiche proprietarie al linguaggio, tanto che uno sforzo non
indifferente è stato prodotto negli scorsi anni dagli enti ANSI (American National Standard Institute) e ISO
(International Standard Organization) per ottenere una versione unica: SQL 2 (noto anche come SQL-92)
e SQL3 (definito nel 1999) sono i risultati. La maggior parte dei sistemi supporta le funzionalità di base
dello standard SQL-3 ed offrono estensioni proprietarie.
SQL è un linguaggio di interrogazione e manipolazione di database che esprime le
interrogazioni e gli aggiornamenti in modo dichiarativo, ovvero specificando l’obiettivo
dell’operazione e non il modo in cui ottenerlo. Il linguaggio SQL permette la programmazione a
due livelli (normalmente messi disposizione da qualsiasi DBMS):
; DDL – Data Definition Language: comprende un numero di istruzioni necessarie per
modificare lo schema della base dati (Data Dictionary), definendo le relazione e i vincoli di
interità
; DML – Data Manipulation Language: comprende un numero di istruzioni necessarie per
modificare l’istanza della base dati, utilizzando operatori dell’algebra relazionale
Query Builder utilizza alcune potenzialità del linguaggio DML (oggetto della trattazione delle prossime
pagine): per ovvi motivi, sono state inibite le possibilità di intervenire sui dati in modifica (per evitare un
inavvertito danneggiamento dei dati).
Notazioni utilizzate nel seguito del manuale - Per descrivere la sintassi dei comandi del linguaggio verrà usata
una notazione che fa uso di alcuni simboli che hanno questo significato:
‚
[]
‚
{}
‚
‚
‚
||
<>
()
4
Le parentesi quadre indicano che il termine all’interno è opzionale, ovvero può comparire o non comparire
una sola volta.
Le parentesi graffe indicano invece che il termine racchiuso può non comparire o essere ripetuto un
numero arbitrario di volte.
Le barre verticali indicano che deve essere scelto uno tra i termini separati dalle barre.
Le parentesi angolate racchiudono un elenco di termini in alternativa.
Le parentesi tonde vanno sempre intese come termini del linguaggio SQL.
DBMS - DataBase Management Sistem – sistema software specifico per la gestione di base dati, che opera al di
sopra del sistema informativo ed è finalizzato a organizzare e gestire le informazioni contenute nel database di
riferimento. E’ compito del DBMS controllare e monitorare le operazioni effettuare dai diversi programmi e dagli utenti
informatici sui dati. Contiene diversi strumenti che ne facilitano la programmazione e la gestione delle informazioni.
(Access non è un DBMS ma un file).
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 29
2.1 Definizioni formali
L’utilizzo di istruzioni SQL prevede la conoscenza di questi concetti di base:
Archivio
File contenete informazioni.
Le informazioni in esso contenute sono distribuite in record logici: ogni record logico contiene una
sequenza di byte ed è suddiviso in campi, ogni dei quali contiene un’informazioni.
Si definisce coefficiente di riempimento di un file il rapporto tra:
numero byte allocati record logici / numero byte blocchi fisici allocati al file
Struttura rigida che contiene diversi insiemi di informazioni, memorizzate in modo
compatto e organizzate per essere ritrovate efficientemente.
Database
Il database è una collezione di dati memorizzati in modo permanente su memoria di massa,
gestiti da un sistema di gestione di database (il DBMS – ved. definizione) che fornisce
meccanismi di accesso ai dati assicurando al contempo la loro affidabilità e privatezza.
Le informazioni in esso contenute sono organizzate in tabelle.
Tabella
(o relazione)
Collezione di informazioni strutturate appartenenti ad uno stesso contesto (argomento
specifico) organizzate in un numero fisso di colonne e un numero variabile di righe.
Ciascuna ‘relazione’ ha un nome univoco.
Campo
Cella contenete una singola informazione; elemento di una riga
Colonne
(Attributi)
Elenco dei campi che costituiscono una tabella o un istanza, detti anche “attributi”; a
ciascuna colonna è associato una tipologia di valori corrispondente (testo, numerico,
data…).. Gli attributi di una stessa relazione devono avere nomi diversi tra loro.
Il numero di colonne che costituisce una tabella\istanza è detto grado della relazione
Riga
Unione di informazioni presenti in più colonne (detta anche Tupla o Record)
(Tupla, Record) riconducibili ad un informazione comune (legame).
Il numero di righe che costituisce una tabella\istanza è detto cardinalità della relazione
Esempio: Consideriamo la tabella Clienti
Campo
Ciascun campo della tabella Clienti contiene lo stesso tipo di informazione per ciascun cliente, ad
esempio il codice del cliente. Ogni campo ha un “nome_campo” diverso dagli altri
CodCli
Denom
C0000001
ROSSI
Ind
Cap
Prov Loc
Tel
1232131
C01050002
EXPERT CASA NEW
Via Cesare Battisti, 76
27100
PV
PAVIA
C01050003
NEW ELECTRONIC CENTER
Viale Manzoni, 73
20052
MI
MONZA
039/233322
C01050004
LAMBERTI GIULIANA
Viale Piemonte, 35
28100
NO
NOVARA
0322/28335
C01050007
GAMMA S.N.C.
Via Puccini, 34
20122
MI
MILANO
02/44554298
C01050008
RACHENA CORPORATION
3/2 hafeez centre, main bld.
54660
C01050009
POWER INSTRUMENTS LTD
37 brook road rayleigh essex
Riga
GULBERG
LAHORE
SS67XJ
ENGLAND
0382/2298779
- 0092425645604
0044/26832455
Ciascuna riga della tabella Clienti contiene tutte le informazioni relative ad un cliente:
codice, società, denominazione, indirizzo…Ogni riga ha un codice diverso (ID)
30 – Manuale Tecnico - Operativo
Schema
Struttura delle tabelle che compongono il database (statico).
Lo schema di una relazione è composto dal nome della relazione, seguito dal nome dei suoi
attributi cui è associato il tipo; lo schema di una base dati relazionale è data dall’elenco delle
relazioni in essa presenti.
La definizione degli schemi delle tabelle di un database avviene in una fase molto delicata,
detta Database design durate la quale il DBA (Database ADministrator) definisce dapprima il
tipo di dati e le regole di gestione, quindi la struttura nella quale andranno memorizzati.
Nel nostro sistema gestionale, grazie alla funzionalità avanzata di Personalizzazione On Fly!
è possibile operare modifiche strutturali al database, creando nuovi schemi (tabelle) e
inserendole nel contesto applicativo già attualizzato.
Istanza
(o Occorrenza)
Insieme di tuple presenti in una base dati in un preciso momento\istante (dinamica).
Mentre lo schema di una relazione viene fissato in fase di creazione, l’istanza di relazione si
modifica in continuazione in seguito alle azioni che vengono effettuate sulla base dai, ovvero
alle operazioni di inserimento, modifica e cancellazione delle tuple. L’istanza di una base dati
relazione è data dall’insieme delle tuple presenti in un preciso istante.
Transazione
Esecuzioni di programma su una base dati: le transazioni andate a buon fine
modificano le istanze di una base dati.
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 31
2.1.1 Il modello di base dati relazionale e il concetto di chiave
Il modello di database sul quale utilizzale il linguaggio SQL è il modello relazionale5, basato
sul concetto di relazione: un database relazionale è una collezione di relazioni6 (o tabelle).
Alla base del modello relazionale vi è il concetto di chiave, la quale rappresenta un
identificativo univoco di ogni singolo record: una chiave è formalmente un sottoinsieme di una
relazione, composto da tutti i valori inseriti in un attributo che rispondono ai requisiti di unicità
(non può esistere nella stessa colonna lo stesso valore ripetuto in più tuple) e minimalità.
In uno relazione è possibile creare più chiavi: in questo caso, sarà possibile indicare quale di
queste è la chiave primaria, ovvero l’attributo che più di frequente sarà utilizzato per accedere
al dato.
Nella procedura gestionale, i dati vengono distribuiti nelle diverse tabelle, ognuna delle quali è adibita alla
gestione di un particolare gruppo (o sottoinsieme) di informazioni: per ricollegare i dati così distribuiti,
facenti capo ad uno stesso insieme, è possibile utilizzare proprio la chiave, che permette di aggregare le
informazioni in modo logico.
ID
CodDoc
Tabella OrdCliTest
OrdNum
OrdDat
CodCli
Flag
Flag
Flag
CodPag Sconto1 Sconto2 TotQta TotImp
Evaso Stampa Conferma
1 ORDCLI O0000001 15/01/2003 C01050005 BB30
5
0
5130
2
No
Sì
2 ORDCLI O0000002 20/01/2003 C01050003 RB36
10
0
10 3014,21
2
No
Sì
3 ORDCLI O0000003 25/01/2003 C01050007 RB369
20
0
16 5321,78
2
No
Sì
4 OFFCLI T0000001 28/01/2003 C01050004 RD36
0
ID
ID C01050004
NRiga
CodArt0
5 ORDCLI O0000004 31/01/2003
RD36
Testata
6 ORDCLI O0000005 31/01/2003
TR60
20
1 C01050006
1
1 P_BICICLETTA
3
Tabella OrdCliDett
0
5 10845,6
2
No
No
UM0QtaOrd5 QtaEva
10845,6 Prezzo
0 Sconto
No Sconto2SìImporto
N. 0
2 C01050008
2
1 H_PCPENTIUM
N. 0
7 ORDCLI O0000006 31/01/2003
RB36
10
3
2
2
H_ST_EPSONEPL
N. 0
8 OFFCLI T0000002 31/01/2003 C01050002 RB36
10
4
2
3
R_NASTRO
PZ 0
9 ORDCLI O0000007 31/01/2003 C01050002 RB36
10
5
2
4 W_MSOFFICE
N.
10 ORDCLI O0000008 31/01/2003 C01050006 TR60
20
0
6
3
1 H_PCPENTIUM
N.
2
1800
Sì0
2 3184,13
2 1084,56
15
2
23 1597,1
2
345
2
40
40
17,5
2
34 2519,09
3
No0
No0
0Sì1952,21
0
621
No
No0
0
No
0
0Sì
63
0
378
Sì
0 3458,18
2
2
3
2
210
422,4
2
3 1440,91
0Sì
5130
7
3
2 H_ST_EPSONEPL N.
4
4
345
0
0
1104
8
3
3 R_NASTRO
PZ
6
6
25
7
0
111,6
9
3
4 W_MSOFFICE
N.
3
3
300
10
0
648
10
4
1 P_BICICLETTA
N.
5
5 2169,12
0
0 10845,6
11
5
1 P_BICICLETTA
N.
5
0 2169,12
0
0 10845,6
12
6
1 H_PCPENTIUM
N.
2
2 1456,41
5
8 2036,64
13
6
2 H_ST_EPSONEPL N.
2
2
345
5
8
15
7
1 H_PCPENTIUM
N.
1
1 1425,42
0
0 1282,88
16
7
2 H_ST_EPSONEPL N.
1
1
0
0
310,5
Esempio:
17
7
3 R_NASTRO
PZ
5
5
17,5
0
La tabella OrdCliTest contiene
i dati
degli ordini N.clienti: la
è 0il
18
7 di 4testa
W_MSOFFICE
8 chiave
8 primaria
210
campo ID
19
8
1 H_PCPENTIUM
N.
1
1 1084,56
0
La tabella OrdCliDett contiene
di dettaglio degli
20 invece
8 i dati
2 H_ST_EPSONEPL
N. ordini2 clienti:2 anche
345 questa
0
tabella ha la sua chiave primaria,
che9 identifica
univocamente
21
1 H_PCPENTIUM
N. i record
1 della 1tabella,
1084,56 che è 0il
campo ID.
22
9
2 H_ST_EPSONEPL N.
2
2
345
0
Dato che ad una testata dell’ordine
possono
corrispondere più
dettagli
(= righe
di300
articoli12e
23
10
1 W_MSOFFICE
N.
2
2
di note) nella tabella OrdCliDett sarà riportato, per ogni riga , il valore della chiave primaria
che identifica nella tabella OrdClitest l’ordine di origine nel campo ID Testata.
0
78,75
5
345
482,45
0
1512
0
976,1
0
621
0
976,1
0
621
0
422,4
I principali modelli di base dati dei DBMS sono 4: gerarchico, reticolare, relazionale e a oggetti.
Ved. definizioni date in precedenza, anche se da un punto di vista rigoroso e formale, una relazione è definita come
un insieme di tuple, dove ciascuna tupla dovrebbe essere differente dalle altre; i DBMS commerciali tuttavia non
verificano la condizione di univocità della singola tupla in modo automatico.
6
32 – Manuale Tecnico - Operativo
2.2 COMANDI PER L’ESTRAZIONE DEI DATI
2.2.1 L’espressione SELECT
L’espressione SELECT consente di specificare un’operazione di interrogazione in SQL: l’effetto
di questa istruzione è quello di estrarre i dati da una o più tabelle in base ai vincoli impostati
nell’espressione.
Viene formalmente definita come il prodotto cartesiano di relazioni elencate nella clausola From (o
proiezione del cartesiano potendo selezionare solo alcuni attributi, non tutti *).
STRUTTURA DI UNA SELECT
(zona) ISTRUZIONE
(1) SELECT
ALL oppure DISTINCT oppure TOP N (percent)
(2) * oppure {campo [ [ AS] Alias] [, campo [ [AS] Alias] ]}
(3) FROM
< elenco tabelle >
(4) WHERE
< condizione di estrazione delle righe >
(5) GRUOP BY
< criteri di raggruppamento >
(6) HAVING
< condizioni di estrazione dei gruppi >
(7) ORDER BY
< criteri di ordinamento >
Parola chiave
Descrizione
1 Select
Comando che specifica l’operazione di interrogazione.
2 Campo
Nome di una colonna di una tabella (la tabella è indicata nel From)
3 Tabella
Nome di una tabella del database (almeno uno, non ripetibile)
4 Condizione di estrazione
criterio in base al quale vengono confrontate ed estratte le righe
5 Criterio di raggruppamento
nome di un campo in base al quale effettuare il raggruppamento.
6 Criterio di estrazione dei gruppi
legato al criterio di raggruppamento.
7 Criterio di ordinamento
nome di un campo in base al quale ordinare le righe
L’SQL seleziona, tra le righe che appartengono alla concatenazione delle righe delle tabelle
elencate nella clausola From, quelle che soddisfano le condizioni espresse nell’argomento della
clausola Where (se la clausola Where è assente si selezionano tutte le righe).
Il risultato dell’esecuzione di una interrogazione SQL è una tabella con una riga per ogni riga
selezionata e con un insieme di colonne dato dai campi elencati nella Select, dove:
‚
‚
il/i campo/i da estrarre deve essere scritto come tabella.campo oppure come campo se nel From è
presente 1 tabella o i nomi degli attributi non si ripetono in tabelle diverse (valido anche nel Where)
Il carattere * rappresenta la selezione di tutti i campi delle tabelle elencate nel From.
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 33
Per evitare duplicati nelle tabelle risultato di una interrogazione, cioè righe con gli stessi valori
per tutti gli attributi, è sufficiente specificare la parola chiave DISTINCT immediatamente dopo
la parola chiave Select: se non si specifica nulla, viene presa di default la parola chiave ALL e
in questo caso il risultato della query SQL può contenere anche righe duplicate.
Con la parola chiave TOP N è possibile fermare la ricerca una volta raggiunto un certo numero
di record, e con TOP N percent è possibile fermare la ricerca una volta raggiunta una certa
percentuale di record estratti.
Esempio:
Supponiamo che la tabella Articoli contenga questi valori:
CodArt
H_PCPENTIUM
M_BORDOTAPP
M_FILOCUCITO
M_MOQUETTE
M_STOFFATAPP
P_AUTOMOBILE
S_COMAD_VETRO
S_INTERNI
S_MANOVEL_VETRO
S_PORTA_PANNELLO
S_PORTIERE
S_RUOTE
So Tipo PVendPub
3000000
250
50
45000
3500
18000000
12500
1500000
3000
95000
450000
110000
Descrizione
Pentium Pro 200 Mhz
Bordo in Stoffa pregiata per Tappetini
Filo per cuciture Tappetini Auto
Moquette per il Basamento dell'Auto
Stoffa per Tappetini Auto
Automobile [Distinta Principale]
Comandi Elettrici Vetri Anteriori
Interni per Autovettura
Manovella Vetri Portiera Posteriore
Pannello per Portiera
Protiere Autovettura Anteriori Post.
Ruote Mod. Pirelli P600 165/60 R13
e consideriamo la seguente interrogazione:
Select Descrizione
From Articoli
La sua esecuzione produce la seguente tabella come risultato:
Descrizione
Pentium Pro 200 Mhz
Bordo in Stoffa pregiata per Tappetini
Filo per cuciture Tappetini Auto
Moquette per il Basamento dell'Auto
Stoffa per Tappetini Auto
Automobile [Distinta Principale]
Comandi Elettrici Vetri Anteriori
Interni per Autovettura
……
cioè un elenco delle descrizioni di tutti gli articoli della tabella.
De Misu
N.
MT
MT
MQ
MQ
N.
N.
N.
N.
N.
N.
N.
34 – Manuale Tecnico - Operativo
2.2.1.1 Rinomina di campi e tabelle con un ALIAS
E’ possibile rinominare i campi o le tabelle nella relazione finale con un Alias cioè un altro
nome: con questo meccanismo è possibile sia assegnare ai campi dei nomi più significativi, sia
far apparire una stessa tabella più volte nel FROM con alias diversi (superando il limite di poter
indicare una tabella una unica volta).
La query che rinomina le tabelle della clausola FROM con un alias, costruisce il prodotto cartesiano delle
tabelle nella clausola FROM e la relazione così ottenuta proietta solo le colonne specificate nella lista in
corrispondenza di SELECT rinominando quelle colonne che possiedono un alias.
SELECT
NomeTabella.Attributo AS [Alias]
[,NomeTabella.Attributo AS [Alias]]...
FROM
NomeTabella [Alias]
[,NomeTabella [Alias]]...
…
WHERE
La sintassi è leggermente diversa se si rinomina il campo o la tabella:
Campo:
l’assegnazione avviene utilizzando la parola chiave AS seguita dal nome da
associare come alias. Tale nome va indicato necessariamente tra parentesi
quadrate [ ] nel caso in cui contenga spazi o caratteri particolari (*, ?…)
Esempio:
SELECT Clienti.CodCli as [Codcli 1*], Clienti.Denom, CliFatt1.CodCli as
Codcli2
FROM Clienti, Clifatt1
WHERE Clienti.CodCli = CliFatt1.CodCli
Codcli 1*
Denom
Codcli2
C01050001 EXPERT CASA NEW SRL
C01050001
C01050002 NEW ELECTRONIC CENTER SPA C01050002
…
Tabella:
l’assegnazione avviene indicando direttamente il nome tra parentesi quadre [ ].
Assegnando un alias ad una tabella tale nome deve necessariamente sostituire il
nome della relazione in tutte le parti della query (sia nel SELECT sia nel WHERE
che in eventuali criteri di ordinamento/raggruppamento): nel caso in cui l’alias
contenga spazi, le parantesi quadre vanno necessariamente aggiunte anche
nelle altre parti della query
Esempio:
La stessa query dell’esempio precedente può essere scritta così:
SELECT Tabella.CodCli as [Codcli 1*], Tabella.Denom, Tabella1.CodCli
as Codcli2
FROM Clienti [Tabella], Clifatt1 [Tabella1]
WHERE Tabella.CodCli = Tabella1.CodCli
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 35
2.2.1.2 Espressioni e l’uso di Operatori ARITMETICI
Mediante il costrutto SELECT è possibile calcolare valori di espressioni aritmetiche a partire dai
valori assunti, su uno o più attributi, dalle tuple della relazione costruita nella clausola FROM.
Le espressioni sono formulate applicando gli operatori aritmetici +, −, _, / ai valori assunti dalle
tuple sugli attributi della relazione: il risultato è ottenere relazioni in cui uno o più attributi sono
calcolati a partire dagli attributi presenti nella relazione di partenza. Questi attributi sono nuovi,
non presenti in nessuna delle relazioni della clausola FROM.
Il risultato è quello di ottenere per proiezione nuove relazioni, risultato di quelle originali.
SELECT
Espressione [AS Alias]
[,Espressione [AS Alias]].....
FROM
NomeTabella [AS Alias]
[,NomeTabella [AS Alias]]...
WHERE
…
All’interno del costrutto, il termine Espressione [AS Alias] [,Espressione [AS Alias]]... è un
elenco separato da virgole in cui Espressione può essere di 2 tipi:
‚
‚
NomeTabella.Attributo
Espressione Aritmetica
NomeTabella.Attributo.
in
cui
gli
operandi
sono
numeri
o
elementi
del
tipo
Ad ogni espressione va associato un Alias, che individuerà il risultato nella query finale.
Una espressione aritmetica usata nella clausola di proiezione di un’interrogazione dà luogo ad
una colonna virtuale, non presente nella relazione su cui si effettua l’interrogazione, che non
viene fisicamente memorizzate ma solo materializzate come risultato delle interrogazioni. Nel
calcolo delle espressioni aritmetiche la presenza del valore nullo rende l’espressione indefinita.
Esempio:
Supponiamo di voler calcolare l’importo Iva delle righe contenute in BolFattDett:
Select BolFatDett.prezzo, BolFatDett.Importo, BolFatDett.codIva, Tabiva.aliquota,
(BolFatDett.Importo/100)*Tabiva.Aliquota as [Imponibile Iva]
from BolFatDett, Tabiva
WHERE BolFatDett.CodIva = Tabiva.Codiva
Prezzo
1456,41
345
300
1710,5
414
1494,98
332,89
975,64
217,25
Importo
2036,64
482,45
422,4
1539,45
372,6
1494,98
332,89
975,64
217,25
CodIva Aliquota
20
20
20
20
20
20
20
20
20
20
VIN
0
VIN
0
VIN
0
VIN
0
Imponibile Iva
407,328
96,49
84,48
307,89
74,52
0
0
0
0
In questo esempio abbiamo introdotto una
condizione di WHERE (il cui significato sarà
trattato più avanti) necessaria per ottenere il
campo numerico TabIva.CodIva richiesto dal
calcolo.
36 – Manuale Tecnico - Operativo
2.2.1.3 Operatori di AGGREGAZIONE
Le funzioni di aggregazione consentono di valutare delle condizioni su insiemi di record;
utilizzano i seguenti operatori aggregati:
COUNT
MIN
MAX
SUM
AVG
conta il numero di righe nella tabella risultato dell’interrogazione
minimo valore di una espressione
massimo valore di una espressione
somma
media aritmetica
Gli operatori aggregati possono essere uniti alle parole chiave
DISTINCT elimina i duplicati
ALL
trascura solo i valori nulli
; L’operatore COUNT conta il numero dei record estratti in una relazione, considerando tutte
le condizioni impostate; usa la sintassi:
SELECT COUNT ( < * | Camp01 & Campo 2 …> )
‚
‚
‚
‚
costruisce il prodotto cartesiano delle relazioni nella clausola FROM
se la clausola WHERE è presente seleziona solo le tuple che ne soddisfano il predicato
proietta la colonna specificata in corrispondenza di SELECT
restituisce il numero di tuple non NULL che la compongono.
E’ equivalente alla seguente query:
SELECT
COUNT ( ALL <NomeTabella.Attributo> )
Tra parentesi ( ) è possibile indicare
‚
‚
(*) restituisce il numero totale di righe (incluse quelle che contengono campi nei valori
nulli)
un insieme di campi (<Camp01 & Campo 2 …> ) dove i nomi dei campi devono essere
separati dal carattere &.: in questo caso, l’operatore count conteggia una riga solo se
almeno uno dei campi non è nullo, e se tutti i campi specificati nella ListaDeiCampi sono
nulli la riga non viene contata.
Utilizzando il count con il comando distinct si ottiene:
SELECT COUNT ( DISTINCT <NomeTabella.Attributo> )
‚
‚
‚
‚
costruisce il prodotto cartesiano delle relazioni nella clausola FROM
se la clausola WHERE è presente seleziona solo le tuple che ne soddisfano il predicato
proietta la colonna specificata in corrispondenza di SELECT
restituisce il numero di tuple DISTINTE che la compongono
Le tuple NULL non vengono contate.
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 37
Esempio:
Sulla tabella Clienti, vogliamo determinare il numero di clienti della provincia di Milano.
CodCli
Societa Denom
ind
Cap Prov Loc
C0000001
ROSSI
C01050002
EXPERT CASA NEW Via Cesare Battisti, 76 2710 PV PAVIA
C01050003
NEW
ELECTRONIC Viale Manzoni, 73
2005 MI
MONZA
C01050004
LAMBERTI GIULIANA Viale Piemonte, 35
2810 NO NOVARA
C01050005
MICHELI GIOVANNI
Via Bissolati, 2
2610 CR CREMON
C01050006
TOSELLI MARCO
Via A. Volta, 77
4610 MN MANTOVA
C01050007
GAMMA S.N.C.
Via Puccini, 34
2012 MI
MILANO
C01050008
RACHENA
3/2
HAFEEZ 5466
GULBERG
Consideriamo la seguente interrogazione:
Select count (*) as NumeroClienti
From Clienti
Where Prov = “MI”;
Prima viene normalmente eseguita l’interrogazione considerando solo le parti From e Where.
L’operatore aggregato viene poi applicato alla tabella contenente i risultati dell’interrogazione. Il
risultato finale corrisponde al numero di righe nella tabella Clienti che possiedono “Mi” come valore
del campo Prov. Questo numero non è una proprietà posseduta da una riga in particolare ma deve
essere determinato lavorando su tutte le righe della tabella Clienti.
NumeroClienti
2
; Gli altri operatori hanno tutti la medesima sintassi: MIN e MAX restituiscono rispettivamente
il minimo e il massimo valore tra quelli di ciascuna riga, SUM restituisce la somma dei valori
posseduti dal campo su tutte le righe della tabella, mentre AVG restituisce la media dei
valori dell’argomento.
SELECT <operatore> ( [ DISTINCT | ALL ] Campo o Espressione ) As [Alias]
<operatore> ( [ DISTINCT | ALL ] Campo o Espressione ) As [Alias]
…
La clausola SELECT può essere seguita da un numero arbitrario di operatori aggregati.
Per MIN e MAX è obbligatorio indicare un Alias (viene comunque aggiunto dal sistema)
Le parentesi ( ) ammettono come argomento un campo o un’espressione.
La query costruisce il prodotto cartesiano delle relazioni nella clausola FROM, se la clausola WHERE
`e presente seleziona solo le tuple che ne soddisfano il predicato, proietta le colonne della relazione
che compaiono in “FormulaConAttributi”, per ogni tupla calcola il valore della formula
“FormulaConAttributi” e quindi ne esegue il totale considerando solo i valori distinti (se presente il
DISTINCT) o tutti (ALL) .
38 – Manuale Tecnico - Operativo
Esempio:
Operando sulla tabella Articoli, La seguente interrogazione:
Select max(PVendPub) as MaxPrezzo
From Articoli;
produce il risultato:
MaxPrezzo
7746,85
Esempio:
Operando sulla tabella Ordini, la seguente interrogazione:
Select avg (prezzo) as MediaOrd
From ordclidett
Where ordclidett.flagevaso = 2
evidenzia il valore medio di ogni di ogni ordine ancora da evadere
MediaOrd
699,78761905
Esempio:
Operando sulla tabella dettaglio Ordini, la seguente interrogazione:
Select sum (prezzo * QtaOrd) As [Totale prezzo], avg (prezzo * QtaOrd) as
Media, max (prezzo * QtaOrd) as Max, min (prezzo * QtaOrd) as Min
From ordclidett
Where ordclidett.flagevaso = 2
Applica tutti gli operatori analizzati, sul prezzo moltiplicato per la quantità della singola riga
Totale prezzo
Media
Max Min
37637,31 1792,25285714286 10845,6 70
Per quanto illustrato fino ad ora, la clausola SELECT ha esclusivamente due forme possibili:
SELECT Espressione [AS Alias] [,Espressione [AS Alias]]...
SELECT OperatoreAggregato [AS Alias] [,OperatoreAggregato [AS Alias]]...
È scorretto mischiare le due sintassi: la lista che segue la clausola SELECT deve essere
omogenea:
•
•
una lista espressioni
una lista di operatori aggregati (in questo caso, l’eventuale espressione può trovarsi solo
all’interno dell’argomento dell’operatore aggregato).
Per questo, una query tipo “Select sum (prezzo), Importo from BolFatDett” è scorretta:
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 39
2.2.1.4 Operatore Insiemistico UNION
Le operazioni insiemistiche di unione, intersezione, differenza possono unire in un'unica
relazione il risultato di due (o più) relazioni differenti (realizzate con il comando SELECT) le
quali hanno una struttura compatibile, ovvero:
‚
‚
Stesso numero degli attributi visualizzati
Domini compatibili tra i diversi attributi, nello stesso ordine
Nel caso in cui nelle due query vi siano campi con nomi differenti ( a patto che sia mantenuto lo
stesso dominio) l’attributo risultante avrà il nome dell’attributo della prima query indicata.
; L’unione di due relazioni è realizzata mediante il costrutto UNION.
SelectSQL1
UNION [ALL]
SelectSQL2…
La query SQL realizza l’unione delle tuple contenute nelle relazioni generate dalle due
query che sono argomento di UNION. In automatico, le tuple duplicate sono rimosse a
meno che non sia presente ALL.
SelectSQL = query SQL realizzata col comando SELECT.
Esempio:
Supponiamo di voler unire un un'unica relazione il risultato di 2 relazioni differenti sulla tabella clienti
SELECT Codcli, Denom, Ind, Prov, Loc From Clienti Where prov = 'MI'
UNION
SELECT Codcli, Societa, Ind, Prov, Loc From Clienti Where prov = 'TO' …
40 – Manuale Tecnico - Operativo
2.2.2 La Condizione WHERE e l’uso di Operatori RELAZIONALI
Grazie alla clausola Where è possibile costruire interrogazioni che restituiscono un insieme di
righe che soddisfano la condizione espressa nella clausola stessa o che selezionano una
specifica riga (nel caso in cui la condizione Where sia costruita su un campo che non ammette
duplicati).
Viene formalmente definito l’operatore di selezione: le tuple risultato della query saranno estratte in
base a questo parametro.
La clausola WHERE ha come argomento un predicato semplice o una espressione booleana
costruita combinando predicati semplici con gli operatori AND, OR, NOT.
Ciascun predicato semplice confronta tramite gli operatori
‚
‚
Gli operatori relazionali =, <, >, <>, <=, >=
Gli operatori speciali LIKE, BETWEEN, IN
il valore di una campo con un valore costante, o con il valore di una parametro o con il risultato
della valutazione di un’altra espressione; ogni operatore può combinarsi con AND, OR e NOT.
In sintesi, gli operatori <, >, =,… permettono di costruire predicati che vengono valutati su
ciascuna riga delle tabelle coinvolte, indipendentemente da tutte le altre righe.
; L’operatore LIKE è da utilizzare per il confronto di stringhe.
Tabella.nomcampo
LIKE <stringa>
La stringa può contenere (combinandoli):
% = diversi caratteri
_ = un carattere
? = un carattere
L’operatore BETWEEN permette di considerare le tuple che contengono (in un attributo) un
valore numerico compreso in un intervallo specificato. Si utilizza in unione con l’operatore
AND ed eventualmente con l’operatore NOT (per escludere un range di valori).
NOT
BETWENN valore1
BETWEEN valore1
Equivale a
AND
AND
valore2
valore2
>= valore1 AND <= valore2
; L’operatore IN permette di determinare le tuple che contengono (in un attributo) un valore
presente in un insieme specificato
IN
(valore1, valore2, …)
Equivale a IN (a=valore1) OR IN (a=valore2)….
NOT
IN
(insieme di valori)
Equivale a NOT IN (a=valore1) AND NOT IN (a=valore2)….
; Nella clausola WHERE è possibile anche specificare il predicato di JOIN tramite
uguaglianza diretta tra due attributi: in particolare, la condizione di JOIN prevede diverse
sintassi, pertanto Vi dedichiamo un paragrafo apposito (Prelievo da più tabelle: Operatori di
JOIN).
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 41
Esempio 1:
Lavorando sulla tabella Clienti:
CodCli
So Denom
C0000001
ROSSI
C01050002
EXPERT CASA NEW
C01050003
NEW
ELECTRONIC
C01050004
LAMBERTI GIULIANA
C01050005
MICHELI GIOVANNI
C01050006
TOSELLI MARCO
C01050007
GAMMA S.N.C.
C01050008
RACHENA
C01050009
POWER INSTRUMENTS
C01999999
POWER INSTRUMENTS
Ind
Cap
Prov Loc
Via Cesare Battisti, 76
Viale Manzoni, 73
Viale Piemonte, 35
Via Bissolati, 2
Via A. Volta, 77
Via Puccini, 34
3/2
HAFEEZ
37 BROOK ROAD
37 BROOK ROAD
27100
20052
28100
26100
46100
20122
54660
PV
MI
NO
CR
MN
MI
PAVIA
MONZA
NOVAR
CREMO
MANTO
MILANO
GULBE
SS67XJ
SS67XJ
Tel
1232131
0382/229877
039/233322
0322/28335
0372/334557
0376/234276
02/44554298
0092425645
0044/268324
0044/268324
L’interrogazione seguente:
Select CodCli, Denom, Ind, Cap, Prov, Loc
From Clienti
Where CodCli = 'C01050002';
restituisce la seguente riga:
CodCli
Denom
C01050002
EXPERT CASA NEW
Ind
Via Cesare Battisti, 76
Cap
27100
Prov Loc
PV
PAVIA
Esempio 2:
Lavorando sulla tabella Articoli:
CodArt
So Tipo PVendPub
H_PCPENTIUM
3000000
M_BORDOTAPP
250
M_FILOCUCITO
50
M_MOQUETTE
45000
M_STOFFATAPP
3500
P_AUTOMOBILE
18000000
S_COMAD_VETRO
12500
S_INTERNI
1500000
S_MANOVEL_VETRO
3000
S_PORTA_PANNELLO
95000
S_PORTIERE
450000
S_RUOTE
110000
Descrizione
Pentium Pro 200 Mhz
Bordo in Stoffa pregiata per Tappetini
Filo per cuciture Tappetini Auto
Moquette per il Basamento dell'Auto
Stoffa per Tappetini Auto
Automobile [Distinta Principale]
Comandi Elettrici Vetri Anteriori
Interni per Autovettura
Manovella Vetri Portiera Posteriore
Pannello per Portiera
Protiere Autovettura Anteriori Post.
Ruote Mod. Pirelli P600 165/60 R13
De Misu
N.
MT
MT
MQ
MQ
N.
N.
N.
N.
N.
N.
N.
l’interrogazione
Select PvendPub, Descrizione
From Articoli
Where CodArt = ‘H_PCPENTIUM’
Fornisce questa tabella risultato:
PvendPub
Descrizione
3000000
Pentium Pro 200 Mhz
cioè restituisce i campi PvendPub e Descrizione della riga della tabella Articoli in cui il campo CodArt
risulta essere uguale a H_PCPENTIUM.
42 – Manuale Tecnico - Operativo
Esempio 3:
Consideriamo la seguente interrogazione sulla stessa tabella Articoli:
Select CodArt, PvendPub, Descrizione
From Articoli
Where PvendPub > 2500000
La sua esecuzione produce il seguente risultato:
CodArt
PvendPub
Descrizione
H_PCPENTIUM
3000000
Pentium Pro 200 Mhz
P_AUTOMOBILE
18000000
Automobile [Distinta Principale]
Per selezionare i termini con valori nulli cioè campi in cui c’è assenza di informazione si utilizza il
predicato IS NULL.
Il predicato risulta vero solo se il campo ha valore nullo.
Il predicato IS NOT NULL è la sua negazione.
I predicati possono essere separati dal connettivo logico AND, e in questo caso sono selezionate solo le
righe per cui tutti i predicati sono veri, o mediante il connettivo logico OR, e in questo caso sono
selezionate solo le righe per cui almeno uno dei predicati risulta vero. L’operatore logico NOT è unario,
cioè si applica ad un solo predicato e ha l’effetto di invertire il valore di verità del predicato stesso.
Esempio 4:
La seguente interrogazione eseguita sulla tabella Articoli:
Select CodArt, PvendPub, Descrizione, Misura
From Articoli
Where Misura = "MQ" AND PvendPub > 5000;
fornisce il seguente risultato:
CodArt
PvendPub
M_MOQUETTE
45000
Descrizione
Moquette per
il
Misura
Basamento MQ
Esempio 5:
La seguente interrogazione eseguita sulla tabella Articoli:
Select CodArt, PvendPub, Descrizione, Misura
From Articoli
Where Misura = "MQ" OR PvendPub > 1500000;
produce il risultato di questa tabella:
CodArt
PvendPub
H_PCPENTIUM
3000000
M_MOQUETTE
45000
M_STOFFATAPP
3500
P_AUTOMOBILE
18000000
S_TELAIO
2500000
Descrizione
Pentium Pro 200 Mhz
Moquette
per
il
Stoffa per Tappetini
Automobile
[Distinta
Telaio
Atuovettura
Misura
N.
MQ
MQ
N.
N.
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 43
Esempio 6:
La seguente interrogazione eseguita sulla tabella Articoli:
Select CodArt, PvendPub, Descrizione, Misura
From Articoli
Where NOT Misura = "N.";
produce il risultato di questa tabella:
CodArt
PvendPub
M_BORDOTAPP
250
M_FILOCUCITO
50
M_MOQUETTE
45000
M_STOFFATAPP
3500
Descrizione
Bordo
in
Stoffa
Filo
per
cuciture
Moquette
per
il
Stoffa per Tappetini
Misura
MT
MT
MQ
MQ
La sintassi assegna nella valutazione della condizione Where la precedenza all’operatore NOT, ma non
definisce una relazione di precedenza tra gli operatori AND e OR. In una interrogazione
che richiede l’uso di entrambi gli operatori conviene esplicitare l’ordine di valutazione mediante parentesi.
Esempio 7:
La seguente interrogazione sulla tabella Articoli:
Select CodArt, PvendPub, Descrizione, Misura
From Articoli
Where (Misura = "MQ" AND PvendPub > 5000) OR (Misura = "MT");
produce questo risultato:
CodArt
M_BORDOTAPP
M_FILOCUCITO
M_MOQUETTE
PvendPub
250
50
45000
Descrizione
Bordo
in
Stoffa
Filo
per
cuciture
Moquette
per
il
Misura
MT
MT
MQ
cioè un elenco di articoli che soddisfano la condizione racchiusa dalle prime due parentesi oppure quella
racchiusa dal secondo gruppo di parentesi.
Una diversa disposizione delle parentesi produce un risultato completamente diverso:
Select CodArt, PvendPub, Descrizione, Misura
From Articoli
Where Misura = "MQ" AND (PvendPub > 5000 OR Misura = "MT");
CodArt
M_MOQUETTE
PvendPub Descrizione
45000
Moquette
per
Misura
il MQ
L’operatore LIKE consente il confronto di stringhe. Si comporta come un operatore di uguaglianza
arricchito con il supporto per una coppia di caratteri speciali: ? e * che rappresentano rispettivamente un
carattere arbitrario e una sequenza di un numero qualsiasi (anche zero) di caratteri arbitrari.
È da notare che le stringhe vanno sempre racchiuse tra apici ‘ ’ oppure tra virgolette ” ”.
44 – Manuale Tecnico - Operativo
Esempio 8:
La seguente interrogazione:
Select Nome
From BancheCli
Where Nome LIKE '*Commerciale*';
produce il risultato espresso da questa tabella:
Nome
Credito Commerciale
Banca Commerciale Italiana
cioè seleziona dalla tabella BancheCli tutte le banche il cui nome contiene la parola “Commerciale”.
Esempio 9:
Se la tabella BancheCli contiene questi valori:
IDBa CodCli
Nome
11
C01050001
Credito Italiano
12
C01050002
Banca Popolare di Milano
13
C01050003
Cassa di Risparmio di Torino
14
C01050004
Credito Commerciale
15
C01050005
Banca Agricolo Mantovana
16
C01050006
Banca Commerciale Italiana
17
C01050007
CARIPLO
La seguente interrogazione:
Indirizzo
via Cavour, 11
p.za Garibaldi, 5
via Ricostruzione, 3
p.za XXV Aprile, 2
VIA TORINO
Localita
TORINO
MILANO
TORINO
MILANO
MANTOVA
MILANO
MILANO
Tipo
Filiale
Sede
Filiale
Sede
Sede
Agenzi
A
Select Nome, Localita, NAge
From BancheCli
Where Nome LIKE 'Banca*' or NAge > 5;
produce il risultato espresso da questa tabella:
Nome
Localita
Banca Popolare di Milano
MILANO
Credito Commerciale
MILANO
Banca Agricolo Mantovana
MANTOVA
Banca Commerciale Italiana
MILANO
CARIPLO
MILANO
NAge
2
7
3
14
6
cioè tutte le righe della tabella BancheCli in cui il campo Nome inizia con la stringa Banca seguita da un
insieme qualsiasi di caratteri e le righe che contengono nel campo Nage un valore maggiore di 5.
NAg
1
2
4
7
3
14
6
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 45
Esempio 10:
La seguente interrogazione:
Select Nome, Localita
From BancheCli
Where Localita LIKE '*N?';
produce questo risultato:
Nome
Credito Italiano
Banca Popolare di Milano
Cassa di Risparmio di
Credito Commerciale
Banca Commerciale Italiana
CARIPLO
Localita
TORINO
MILANO
TORINO
MILANO
MILANO
MILANO
cioè un elenco di banche la cui località contiene la lettera N come penultimo carattere.
Esempio 9:
La seguente interrogazione:
Select Nome, Localita
From BancheCli
Where Localita LIKE 'M??????';
produce questo risultato:
Nome
Banca Agricolo Mantovana
Localita
MANTOVA
cioè la selezione delle banche la cui località è una stringa di 7 caratteri di cui il primo corrisponde alla
lettera M.
46 – Manuale Tecnico - Operativo
Esempio 10 – lavorare con le date:
La seguente interrogazione in ACCESS:
SELECT Ordclitest.OrdDat, Ordclitest.CodCli, Ordclitest.OrdNum
FROM Ordclitest
WHERE (((Ordclitest.OrdDat)=#31/12/2007#));
produce questo risultato:
orddat
codcli
ordnum
31/12/2007 C01050011 O0000019
31/12/2007 C01050006 Z0000001
31/12/2007 C01050011 Z0000002
31/12/2007 C01050001 O0000020
cioè la selezione degli ordini clienti con data 31/12/2007
La stessa interrogazione in SQL per funzionare va modificata in:
SELECT Ordclitest.OrdDat, Ordclitest.CodCli, Ordclitest.OrdNum
FROM Ordclitest
Where Orddat = '31/12/2007'
produce lo stesso risultato di cui sopra.
La stessa interrogazione (sia in ACCESS che in SQL) potrebbe essere cambiata anche così:
Select orddat, codcli, ordnum
From Ordclitest
Where year (Orddat) = 2007
Producendo questo risultato:
orddat
codcli
ordnum
31/12/2007 C01050011 O0000019
31/12/2007 C01050006 Z0000001
31/12/2007 C01050011 Z0000002
31/12/2007 C01050001 O0000020
cioè la selezione degli ordini clienti dell’anno 2007
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 47
2.2.3 Prelievo da più tabelle: Operatori di JOIN
Gli operatori di JOIN consentono di correlale dati provenienti da tabelle diverse, quando tra
questi esiste una corrispondenza di valori contenuti nei rispettivi attributi (che devono
appartenere allo stesso dominio).
Viene formalmente definito prodotto cartesiano di più relazioni.
Il JOIN può avere diverse sintassi: la più semplice è l’indicazione del predicato di JOIN nella
clausola WHERE, indicando il legame diretto esistente tra due tabelle tramite un uguaglianza:
WHERE Tabella1.nomecapo1 = Tabella2.nomecapo2 …
Questa sintassi a livello logico è equivalente all’utilizzo dell’operatore INNER JOIN.
Gli operatori disponibili sono 3:
INNER JOIN
LEFT JOIN
RIGHT JOIN
join interno
join esterno
join esterno
uno a uno
molti a uno
uno a molti
(=)
(*=)
(=*)
Il loro utilizzo è all’interno della condizione FROM ed legato ad una in sintassi particolare:
SELECT
FROM
WHERE
Campi…
NomeTabella1.1 Inner|Left|Right JOIN
NomeTabella2.1 ON NomeTabella1.1 = NomeTabella2.1,
NomeTabella1.2 Inner|Left|Right JOIN
NomeTabella2.2 ON NomeTabella1.2 = NomeTabella2.2,
…
…
Le righe che vengono coinvolte nel join sono in generale un sottoinsieme delle righe di ciascuna
tabella: può infatti capitare che alcune righe non vengano considerate in quanto non esiste una
corrispondente riga nell’altra tabella per cui la condizione sia soddisfatta.
Il join esterno (rappresentato dagli operatori Left join e Right join) esegue un join tra tabelle
mantenendo però tutte le righe che fanno parte di una o dell’altra delle tabelle coinvolte
(rispettivamente alla sinistra o alla destra del join): in questo caso, vengono posti degli
opportuni valori nulli per rappresentare l’assenza di informazioni provenienti dall’altra tabella.
48 – Manuale Tecnico - Operativo
; L’INNER JOIN è un operatore che correla dati in tabelle diverse sulla base di valori uguali in
campi con lo stesso tipo: l’inner join tra due tabelle fa si che vengano selezionate, dalla
concatenazione delle tabelle coinvolte, le righe per cui la condizione di join è vera.
Esempio:
Un join può essere costruito indicando un ugualianza nella clausola WHERE oppure all’interno del
FROM
la seguente interrogazione:
Select BancheCli.CodCli, Denom, Tel, Nome, Localita
From BancheCli INNER JOIN Clienti ON BancheCli.CodCli = Clienti.CodCli;
Equivale a:
Select BancheCli.CodCli, Denom, Tel, Nome, Localita
From BancheCli, Clienti
Where BancheCli.CodCli = Clienti.CodCli;
Supponendo che la tabella BancheCli contenga questi valori:
IDBa
11
12
13
14
15
16
17
CodCli
C01050001
C01050002
C01050003
C01050004
C01050005
C01050006
C01050007
Nome
Credito Italiano
Banca Popolare di Milano
Cassa di Risparmio di
Credito Commerciale
Banca Agricolo Mantovana
Banca Commerciale Italiana
CARIPLO
Indirizzo
via Cavour, 11
p.za Garibaldi, 5
via Ricostruzione, 3
p.za XXV Aprile, 2
VIA TORINO
Localita
TORINO
MILANO
TORINO
MILANO
MANTOVA
MILANO
MILANO
e la tabella Clienti:
CodCli
Soc Denom
Ind
C0000001
ROSSI
C01050002
EXPERT CASA NEW Via Cesare Battisti, 76
C01050003
NEW
ELECTRONIC Viale Manzoni, 73
C01050004
LAMBERTI GIULIANA Viale Piemonte, 35
C01050005
MICHELI GIOVANNI
Via Bissolati, 2
C01050006
TOSELLI MARCO
Via A. Volta, 77
C01050007
GAMMA S.N.C.
Via Puccini, 34
C01050008
RACHENA
3/2
HAFEEZ
C01050009
POWER
37 BROOK ROAD
C01999999
POWER
37 BROOK ROAD
Cap
Prov Loc
Tel
1232131
PAVIA
0382/229877
MONZA
039/233322
NOVARA 0322/28335
CREMON 0372/334557
MANTOVA 0376/234276
MILANO
02/44554298
GULBERG 00924256456
SS67XJ
0044/268324
SS67XJ
0044/268324
2710
2005
2810
2610
4610
2012
5466
PV
MI
NO
CR
MN
MI
il risultato dell’interrogazione è la seguente tabella:
CodCli
C01050002
C01050003
C01050004
C01050005
C01050006
C01050007
Denom
EXPERT CASA NEW
NEW
ELECTRONIC
LAMBERTI GIULIANA
MICHELI GIOVANNI
TOSELLI MARCO
GAMMA S.N.C.
Tel
0382/2298779
039/233322
0322/28335
0372/334557
0376/2342768
02/44554298
Nome
Banca Popolare di Milano
Cassa di Risparmio di Torino
Credito Commerciale
Banca Agricolo Mantovana
Banca Commerciale Italiana
CARIPLO
Localita
MILANO
TORINO
MILANO
MANTOVA
MILANO
MILANO
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 49
; Il LEFT JOIN fornisce come risultato l’inner join esteso con le righe della tabella che
compare a sinistra dell’operatore Left join anche se non esiste una corrispondente riga nella
tabella di destra (risultato dell’inner l’inner join + tutti valori della tabella di sinistra).
Esempio:
Considerando le tabelle BancheCli e Clienti dell’esempio precedente e la seguente interrogazione:
Select BancheCli.CodCli, Denom, Tel, Nome, Localita
From BancheCli LEFT JOIN Clienti ON BancheCli.CodCli = Clienti.CodCli;
si ottiene questo risultato:
CodCli
C01050001
C01050002
C01050003
C01050004
C01050005
C01050006
C01050007
Denom
Tel
EXPERT
CASA
NEW
LAMBERTI
MICHELI
TOSELLI MARCO
GAMMA S.N.C.
0382/229877
039/233322
0322/28335
0372/334557
0376/234276
02/44554298
Nome
Credito Italiano
Banca Popolare di Milano
Cassa di Risparmio di
Credito Commerciale
Banca Agricolo Mantovana
Banca Commerciale Italiana
CARIPLO
Localita
TORINO
MILANO
TORINO
MILANO
MANTOVA
MILANO
MILANO
; Il RIGHT JOIN restituisce invece, oltre al risultato dell’inner join, le righe della tabella che
compare a destra dell’operatore Right join per le quali l’operazione non trova un
corrispondente nella tabella di sinistra (risultato dell’inner l’inner join + tutti valori della
tabella di destra).
Esempio:
Considerando le tabelle BancheCli e Clienti dell’esempio precedente e la seguente interrogazione:
Select BancheCli.CodCli, Denom, Tel, Nome, Localita
From BancheCli RIGHT JOIN Clienti ON BancheCli.CodCli = Clienti.CodCli;
si ottiene questo risultato:
CodCli
C01050002
C01050003
C01050004
C01050005
C01050006
C01050007
Denom
ROSSI
EXPERT CASA NEW
NEW
ELECTRONIC
LAMBERTI GIULIANA
MICHELI GIOVANNI
TOSELLI MARCO
GAMMA S.N.C.
RACHENA
POWER
POWER
Tel
1232131
0382/229877
039/233322
0322/28335
0372/334557
0376/234276
02/44554298
00924256456
0044/268324
0044/268324
Nome
Localita
Banca Popolare di Milano
Cassa di Risparmio di
Credito Commerciale
Banca Agricolo Mantovana
Banca Commerciale Italiana
CARIPLO
MILANO
TORINO
MILANO
MANTOVA
MILANO
MILANO
50 – Manuale Tecnico - Operativo
2.2.4 Criteri di ordinamento ORDER BY
La clausola ORDER BY consente di definire il criterio di ordinamento delle tuple estratte
dall’interrogazione; chiude l’interrogazione SQL. L’ordine su ciascun campo può essere:
ASC
DESC
: ascendente (default)
: discendente
Se il qualificatore è omesso si assume un ordinamento ascendente.
Omettendo la clausola di order by, l’ordinamento viene definito dal sistema (secondo la strategia usata
per creare l’interrogazione)
E’ possibile specificare anche più attributi che devono essere usati per l’ordinamento; la sintassi
della clausola di ordinamento è la seguente:
ORDER BY Tabella.nomcampo [ asc | desc] { , Tabella.nomcampo [ asc | desc ]}…
Viene prima valutato il primo campo nell’elenco e si ordinano le righe in base a questo attributo
(l’attributo più a sinistra ha più priorità dell’attributo a destra). Per righe che hanno lo stesso
valore in questo campo si considerano i valori dei campi di ordinamento successivi, in
sequenza.
Esempio:
L’interrogazione seguente sulla tabella Clienti
Select Denom, Tel
From Clienti
Order by Denom
Seleziona l’elenco di clienti ordinato in ordine ascendente in base al valore del campo Denom,
visualizzando le colonne Denom e Tel:
Denom
EXPERT CASA NEW
GAMMA S.N.C.
LAMBERTI GIULIANA
MICHELI GIOVANNI
NEW
ELECTRONIC
POWER
POWER
RACHENA
ROSSI
TOSELLI MARCO
Tel
0382/229877
02/44554298
0322/28335
0372/334557
039/233322
0044/268324
0044/268324
00924256456
1232131
0376/234276
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 51
2.2.5 QUERY DI RAGGRUPPAMENTO: criterio GROUP BY
Il criterio di raggruppamento GROUP BY consente di suddividere (= raggruppare) le tuple di
una relazione in tanti gruppi, e su ciascuno di questi applicare un operatore aggregato (ved.
Operatori di AGGREGAZIONE).
Questo criterio consente quindi di suddividere le tabelle in sottoinsiemi, raggruppando le righe
che possiedono gli stessi valori per un insieme di campi specificato, applicando quindi gli
operatori aggregati come se ciascun gruppo di tuple fossero una relazione distinta vera e
propria.
SELECT
NomeTabella.Attributo [,NomeTabella.Attributo]...
OperatoreAggregato [AS Alias] [,OperatoreAggregato [AS Alias]]...
FROM
WHERE
GROUP BY
NomeTabella [,NomeTabella]...
…
NomeTabella.Attributo [,NomeTabella.Attributo]...
ORDER BY
NomeTabella.Attributo [,NomeTabella.Attributo]...
La sintassi SQL impone che in una interrogazione che fa uso della clausola GROUP BY
possano comparire come argomento della SELECT solamente
‚
‚
un sottoinsieme dei campi utilizzato per il raggruppamento delle righe
le funzioni aggregate valutate solo sugli altri attributi.
Quindi, nella clausola SELECT devono comparire tutti i campi presenti anche nella clausola
GROUP BY , eccetto per gli operatori aggregati; viceversa, nella clausola GROUP BY possono
apparire anche campi che non sono presenti nella select.
‚
‚
‚
‚
La query SQL costruisce il cartesiano delle relazioni nella clausola FROM
Se la clausola WHERE è presente, seleziona solo le tuple che ne soddisfano il predicato
Le tuple della relazione così ottenuta vengono suddivise in gruppi, dove ogni gruppo contiene quelle
tuple che assumono il medesimo valore in corrispondenza degli attributi elencati nella clausola
GROUP BY
Per ogni gruppo la query SQL proietta sia le colonne NomeTabella.Attributo
[,NomeTabella.Attributo]... sia il valore degli operatori aggregati che compaiono nella clausola
SELECT, dove gli operatori aggregati vengono calcolati sulle tuple del gruppo
Dopo l’esecuzione del raggruppamento, ogni sottoinsieme di righe deve corrispondere a una
sola riga nella tabella risultato dell’interrogazione: dopo che le righe sono state raggruppate in
sottoinsiemi, l’operatore aggregato viene applicato separatamente su ogni sottoinsieme. Il
risultato dell’interrrogazione è costituito quindi da una tabella con righe che contengono il
risultato della valutazione dell’operatore aggregato, affiancato al valore del campo che è stato
usato per l’aggregazione.
La sintassi SQL permette che in una interrogazione che fa uso della clausola GROUP BY
possa essere usata la clausola ORDER BY: in questo caso, è necessario che ogni attributo
presente nel ORDER BY appaia anche nel GROUP BY
52 – Manuale Tecnico - Operativo
Esempio:
Supponiamo che la tabella BancheCli contenga queste informazioni (alcuni campi sono stati eliminati):
IDBan CodCli
Nome
Indirizzo
Localita
Tipo
11
C01050001 Credito Italiano
via Cavour, 11
TORINO
Filiale
12
C01050002 Banca Popolare di Milano
p.za Garibaldi, 5
MILANO
Sede
13
C01050003 Cassa di Risparmio di Torino
via Ricostruzione, 3 TORINO
Filiale
14
C01050004 Credito Commerciale
p.za XXV Aprile, 2
MILANO
Sede
15
C01050005 Banca Agricolo Mantovana
MANTOVA Sede
16
C01050006 Banca Commerciale Italiana
MILANO
Agenzia
17
C01050007 CARIPLO
VIA TORINO
MILANO
A
NAg
1
2
4
7
3
14
6
Consideriamo la seguente interrogazione:
Select count(Nome) as NClientiConBancaA, Localita
From BancheCli
Group by Localita
Questa interrogazione ha l’effetto di contare il numero di clienti la cui banca ha sede nella medesima
località. Per ciascuna località visualizza il numero di clienti.
Nella query in esame è come se venisse eseguita l’interrogazione:
Select Nome, Localita
From BancheCli
La tabella ottenuta viene poi analizzata, dividendo le righe in insiemi caratterizzati dallo stesso valore dei
campi che compaiono come argomenti della clausola group by.
Nell’esempio le righe vengono raggruppate in base allo stesso valore del campo Località, ottenendo in
memoria questo risultato:
Banca Agricolo Mantovana
MANTOVA
Banca Popolare di Milano
MILANO
Credito Commerciale
MILANO
Banca Commerciale Italiana
MILANO
CARIPLO
MILANO
Credito Italiano
TORINO
Cassa di Risparmio di Torino
TORINO
Che viene elaborato nel seguente risultato dell’interrogazione.
NClientiConBancaA
Localita
1
MANTOVA
4
MILANO
2
TORINO
Esempio:
La seguente query estrae, legando due tabelle diverse, il totale dei diversi destinatari di ogni cliente:
SELECT Clienti.Denom, count(DivDest.Denom) As [N Div Dest*]
FROM Clienti,DivDest
WHERE Clienti.CodCli=DivDest.Codcli
GROUP BY Clienti.Denom
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 53
2.2.5.1 La clausola HAVING
La clausola HAVING descrive le condizioni che si devono applicare al termine dell’esecuzione
di una query che fa uso della clausola GROUP BY e di criteri di aggregazione: il ruolo della
clausola HAVING è di fatto analogo a quello della clausola WHERE, solo che la clausola
WHERE agisce su singole tuple mentre HAVING agisce su gruppi di tuple.
Quindi, in sintesi, in una interrogazione che fa uso del GRUOP BY è possibile specificare due
condizioni di selezione che le tuple dovranno soddisfare per essere estratte:
‚
‚
la clausola HAVING può avere come argomento gli operatori aggregati
la clausola WHERE potrà avere come argomento gli altri attributi della query
NOTA IMPORTANTE: gli operatori aggregati NON possono comparire in Condizione Where; solo la
condizione HAVING ammette nel predicato SQL gli operatori aggregati.
Applicando la clausola HAVING, il risultato finale della query evidenzia un sottoinsieme di righe
costruito dalle tuple della GROUP BY che soddisfano il predicato argomento della HAVING.
SELECT
NomeTabella.Attributo [,NomeTabella.Attributo]...
OperatoreAggregato [AS Alias] [,OperatoreAggregato [AS Alias]]...
FROM
WHERE
GROUP BY
HAVING
NomeTabella [,NomeTabella]...
…
NomeTabella.Attributo [,NomeTabella.Attributo]...
OperatoreAggregato [AS Alias]
ORDER BY
NomeTabella.Attributo [,NomeTabella.Attributo]...
‚
‚
‚
‚
‚
La query SQL costruisce il cartesiano delle relazioni nella clausola FROM
se la clausola WHERE è presente, la query seleziona solo le tuple che ne soddisfano il predicato,
le tuple della relazione così ottenuta vengono suddivise in gruppi, dove ogni gruppo contiene quelle
tuple che assumono il medesimo valore in corrispondenza degli attributi elencati nella clausola
GROUP BY
la query elimina quindi i gruppi di tuple che non soddisfano il predicato nella clausola HAVING
infine PER OGNI GRUPPO la query SQL proietta sia le colonne NomeTabella.Attributo
[,NomeTabella.Attributo]... sia il valore delle espressioni che compaiono nella clausola SELECT,
dove le espressioni vengono calcolate sulle tuple del gruppo.
54 – Manuale Tecnico - Operativo
Esempio:
Ecco
Select count(Nome) as NClientiConBancaA, Localita
From BancheCli
Group by Localita
Having count(Nome) > 1
Questa interrogazione produce la seguente tabella come risultato:
NClientiConBancaA
4
2
Localita
MILANO
TORINO
Esempio:
Ipotizziamo di volere visualizzare gli articoli che sono presenti nell’archivio ordini con prezzi variabili.
Per fare ciò, scrivendo la seguente query senza HAVING possiamo ottenere una visualizzazione,
raggruppata per ogni articolo, del prezzo minimo e del prezzo massimo di vendita:
SELECT CodArt, max(Prezzo) As [Prezzo Max], min(Prezzo) As [Prezzo Min]
FROM OrdCliDett
GROUP BY CodArt
La query da il seguente risultato:
CodArt
Prezzo Max Prezzo Min
H_PCPENTIUM
1456,41
1084,56
H_ST_EPSONEPL
345
345
P_BICICLETTA
2169,12
1800
R_NASTRO
25
17,5
W_MSOFFICE
300
210
Vediamo dal risultato della query che l’unico articolo venduto a prezzo costante è l’articolo
H_ST_EPSONEPL: infatti, aggiungendo una apposita clausola HAVING è possibile per estrarre solo le
tuple relative ad articoli che presentano una certa variabilità di prezzo, le quali presenta un max diverso
dal valore min (in caso di invarianza, i due valori sarebbero uguali).
SELECT CodArt, max(Prezzo) As [Prezzo Max], min(Prezzo) As [Prezzo Min]
FROM OrdCliDett
GROUP BY CodArt
HAVING max(Prezzo) <> min (Prezzo)
CodArt
Prezzo Max Prezzo Min
H_PCPENTIUM
1456,41
1084,56
P_BICICLETTA
2169,12
1800
R_NASTRO
25
17,5
W_MSOFFICE
300
210
Con la stessa logica, possono creare una clausola HAVING per estrarre solo quelli che presentano una
variabilità tra il valore massimo e minimo maggiore di € 500,00:
…
HAVING max(Prezzo) - min (Prezzo) > 500,0
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 55
2.2.6 QUERY ANNIDATE (Subquery)
Esistono problemi, rilevanti dal punto di vista pratico, che per essere risolti richiedono di leggere
i dati di una relazione più volte: in questo senso, le SUBQUERY (o NESTED QUERY)
consentono di trattare queste casistiche.
Una SUBQUERY7 è una query SQL che compare all’interno di un’altra query SQL (la quale
potrebbe essere subquery di un’altra query e così via); sono delle espressioni SELECT
nidificate, che possono essere posizionate:
; nella clausola WHERE
; nella clausola FROM
Per comprendere le interrogazioni nidificate le si può interpretare in questo modo:
l’interrogazione nidificata viene eseguita per prima e il risultato viene salvato in una variabile
temporanea. Il confronto viene poi operato accedendo direttamente a questi risultati
temporanei.
2.2.6.1 Subquery nella clausola WHERE
Il caso più frequente è trovare le subquery all’interno della clausola WHERE: permette di
costruire predicati con strutture complesse, in cui si confronta un valore (ottenuto come risultato
di una espressione valutata sulla singola riga) con l’insieme di valori risultato dell’esecuzione di
un’altra interrogazione SQL nidificata; la nested query viene eseguita per prima e il risultato
viene salvato in una variabile temporanea. Il confronto viene quindi operato accedendo
direttamente a questi risultati temporanei, come condizioni di WHERE.
WHERE
Espressione [Operatore] (Subquery)
La Sub Query deve essere compresa tra parentesi tonde ( ).
‚
‚
‚
SQL esegue prima la subquery
dopo che ne ha calcolato il risultato esegue la query più esterna
nella clausola WHERE una subquery ha il ruolo di un operando
Una subquery rende un risultato confrontabile, e può restituire più valori (relazioni) o un unico
valore: in base a ciò, sarà possibile utilizzare diversi operatori.
•
Se la subquery restituisce un singolo valore, è lecito usarla come operando di:
OPERATORI RELAZIONALI (=,<,>, …)
OPERATORI MATEMATICI (+, _, −, . . )
•
Se la subquery restituisce più valori (Nb: separati da virgole) può essere un operando di:
OPERATORI INSIEMISTICI (ved. pagina seguente).
7
SQL è un linguaggio potente proprio perché consente di avere query all’interno di altre query: in questo modo una
query complessa può essere scomposta in una query più semplice..
56 – Manuale Tecnico - Operativo
Poiché il risultato di una interrogazione SQL è generalmente costituito da un insieme di valori e
in una Select nidificata questo viene confrontato con il valore di un campo per una singola riga
alla volta, i normali operatori di confronto <, >, =,…. sono estesi con le parole chiave ALL e
ANY. La sintassi diventa quindi la seguente.
; L’operatore ALL specifica che la riga soddisfa la condizione solo se tutti gli elementi restituiti
dall’interrogazione nidificata Subquery rendono vero il confronto; l’operatore ANY specifica
che la riga soddisfa la condizione solo se almeno uno degli elementi restituiti
dall’interrogazione nidificata Subquery rende vero il confronto.
Espressione
[Operatori Relazionali] [ALL | ANY] (Subquery)
Esempio:
Espressione >= ALL (SubQuery)
SQL mette a disposizione anche due appositi operatori IN e NOT IN che rappresentano il
controllo di appartenenza e di esclusione rispetto ad un insieme. La sintassi è la seguente.
; IN: ha lo stesso significato dell’operatore = ANY.
Verifica se l’Espressione (capo o valore) è presente nell’insieme di tutti gli elementi restituiti
dall’interrogazione nidificata (Subquery)
; NOT IN ha lo stesso significato dell’operatore <> ALL.
Verifica se espressione non è presente nell’insieme di tutti gli elementi restituiti
dall’interrogazione nidificata (Subquery).
Espressione
[IN | NOT IN] (Subquery)
Espressione Sx può essere :
‚ un singolo valore costante
‚ un nome di attributo
‚ un’espressione contenente operatori matematici
‚ una subquery che restituisce un singolo valore
Un altro operatore è l’operatore EXISTS (e la sua negazione). La sintassi è la seguente.
; L’operatore EXIST e
NOT EXIST Verifica se esiste o no almeno una riga nell’insieme
restituito dall’interrogazione nidificata Subquery
Espressione
[EXISTS | NOT EXISTS] (Subquery)
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 57
Esempio:
Operando sulla tabelle Articoli contenente questi valori:
CodArt
H_PCPENTIUM
M_BORDOTAPP
M_FILOCUCITO
M_MOQUETTE
M_STOFFATAPP
P_AUTOMOBILE
S_COMAD_VETRO
S_INTERNI
S_MANOVEL_VETRO
S_PORTA_PANNELLO
S_PORTIERE
S_RUOTE
S_SEDILI_A
S_SEDILI_P
S_TAPPETINI
S_TELAIO
W_MSOFFICE
Societa TipoMag PVendPub
3000000
45000
50
45000
3500
18000000
12500
1500000
3500
95000
450000
110000
550000
400000
45000
2500000
800000
Descrizione
Desc Misura
Pentium Pro 200 Mhz
N.
Bordo
in
Stoffa
MT
Filo
per
cuciture
MT
Moquette
per
il
MQ
Stoffa per Tappetini
MQ
Automobile
[Distinta
N.
Comandi Elettrici Vetri
N.
Interni per Autovettura
N.
Manovella
Vetri
N.
Pannello per Portiera
N.
Protiere
Autovettura
N.
Ruote
Mod.
Pirelli
N.
Sedili
Anteriori
N.
Sedili
Posteriori
N.
Tappetini Anteriori e
N.
Telaio
Atuovettura
N.
Microsoft Office 97
N.
La seguente interrogazione:
Select Descrizione, PvendPub, Misura
From Articoli
Where PvendPub = ANY (Select PvendPub From Articoli Where Misura = ‘MQ’);
ha l’effetto di selezionare dalla tabella articoli quegli articoli che hanno lo stesso prezzo PvendPub di tutti
gli articoli con Misura ‘MQ’;
Descrizione
Bordo
in
Stoffa
Moquette
per
il
Stoffa per Tappetini
Manovella
Vetri
Tappetini Anteriori e
PvendPub
45000
45000
3500
3500
45000
Misura
MT
MQ
MQ
N.
N.
58 – Manuale Tecnico - Operativo
2.2.6.1.1
Query CORRELATE
Ci sono casistiche in cui non è sufficiente che la subquery venga eseguita una sola volta, ma è
necessario che l’esecuzione avvenga per ogni tupla nella relazione della query esterna.
Quando una subquery deve essere eseguita tante volte quante sono le tuple della relazione
esterna ed il suo risultato è di volta in volta funzione della tupla esterna, si parla di QUERY
CORRELATE. La sintassi è la seguente:
WHERE
Espressione (Subquery) [Operatore]
Affinché SQL esegua una subquery come query correlata, una o più relazioni nella clausola
FROM della query esterna devono avere un Alias: tale alias più essere usato nelle espressioni
all’interno della subquery.
Esempio:
Operando sulla tabella Ordclidett, è possibile elaborare una query per estrarre le righe di ordini che
presentano sia un prezzo di vendita maggiore di 1000, sia in assoluto più alto per l’articolo in esame:
Select id, codart, prezzo
From ordclidett x
where (select max(prezzo) from ordclidett where ordclidett.prezzo = x.prezzo) >
1000
2.2.6.2 Subquery nella clausola FROM
Dato che una sottoquery restituisce una relazione, può comparire anche nell’elenco di relazioni
nella clausola FROM: in questo caso, si richiede che alla sottoquery sia associato un Alias, che
all’interno della una query SQL può essere utilizzato come un normale nome di relazione.
Esempi:
Operando sulla tabella Ordclidett, è possibile elaborare una query (annidata) che effettua una media dei
prezzi di vendita associati degli articoli di magazzino, da utilizzare come confronto per verificare quali
articoli sono stati venduti ad un prezzo superiore al prezzo medio di vendita:
Select ordclidett.codart, ordclidett.prezzo
from (Select avg(PvendPub) As PMedio from articoli) X, ordclidett
where ordclidett.prezzo > X.Pmedio
La query può essere velocemente modificata per verificare quante righe di ordine riportano prezzi
superiori al prezzo di vendita dell’articolo:
Select ordclidett.codart, ordclidett.prezzo
from ordclidett, (Select codart, PvendPub from articoli) X
where ordclidett.prezzo > X.PVendPub and ordclidett.codart = x.codart
QUERY BUILDER: Gestione delle Viste e il Linguaggio SQL - 59
2.3 COMANDI PER LA MODIFICA DEI DATI
[Non utilizzabili da Query Builder]
Questi comandi non sono utilizzabili in Query Builder: ne riportiamo la descrizione per
completezza espositiva (possono essere di largo utilizzo da parte di personale tecnico).
2.3.1
Il comando DELETE
Il comando DELETE consente di cancellare righe da una tabella del database.
DELETE
FROM
WHERE
NomeTabella
Condizione
L’effetto del comando è l’eliminazione dalla tabella NomeTabella di tutte le righe che soddisfano
la condizione di Where; se non è specificata la clausola Where l’esecuzione del comando
produce la rimozione di tutte le righe della tabella specificata.
Esempio:
Il comando:
Delete From BancheCli Where Nome = ‘CARIPLO’;
ha l’effetto di eliminare dalla tabella BancheCli la riga il cui campo Nome contiene il valore ‘CARIPLO’.
Dopo la sua esecuzione la tabella si presenta con una riga in meno.
IDBan
11
12
13
14
15
16
CodCli
C01050001
C01050002
C01050003
C01050004
C01050005
C01050006
Nome
Credito Italiano
Banca Popolare di Milano
Cassa di Risparmio di
Credito Commerciale
Banca Agricolo Mantovana
Banca Commerciale Italiana
Indirizzo
via Cavour, 11
p.za Garibaldi, 5
via Ricostruzione, 3
p.za XXV Aprile, 2
Localita
TORINO
MILANO
TORINO
MILANO
MANTOVA
MILANO
Nella condizione WHERE è possibile indicare anche una NESTED QUERY: in questo caso,verranno
applicate le stesse regole per la query di SELECT, e le tuple eliminate saranno quelle che soddisfano la
condizione di WHERE.
60 – Manuale Tecnico - Operativo
2.3.2 Il comando UPDATE
l comando UPDATE consente di apportare modifiche al contenuto di una tabella.
UPDATE
SET
WHERE
NomeTabella
Campo = < Espressione | SelectSQL | null >
{, Campo = < Espressione | SelectSQL | null | default > }
Condizione
Con questo comando vengono aggiornati uno o più campi delle righe della tabella NomeTabella
che soddisfano l’eventuale condizione argomento della clausola Where; se il comando non
contiene la clausola Where la modifica viene effettuata sugli attributi di tutte le righe.
Nei campi oggetto di modifica viene posto un valore che può essere il risultato della valutazione
di una espressione sui campi della tabella, il risultato di una interrogazione SQL o il valore nullo;
questo valore aggiornare deve essere dello stesso dominio (tipo) del valore da aggiornare.
Nella clausola SET è possibile indicare anche una NESTED QUERY: in questo caso, la query annidata
deve restituire un valore di dominio compatibile con quello del campo che deve aggiornare.
Esempio: A partire da questi valori nella tabella BancheCli:
IDBan
11
12
13
14
CodCli
C01050001
C01050002
C01050003
C01050004
Nome
Credito Italiano
Banca Popolare di Milano
Cassa di Risparmio di Torino
Credito Commerciale
Indirizzo
via Cavour, 11
p.za Garibaldi, 5
via Ricostruzione, 3
p.za XXV Aprile, 2
Localita
TORINO
MILANO
TORINO
MILANO
Il seguente comando di update:
Update BancheCli
Set Indirizzo = ‘via Mazzini, 23’
Where Nome = ‘CARIPLO’;
ha l’effetto di modificare il campo Indirizzo nella riga contenente il Nome CARIPLO:
IDBan
11
12
13
14
CodCli
C01050001
C01050002
C01050003
C01050004
Nome
Indirizzo
Credito Italiano
via Cavour, 11
Banca Popolare di Milano
p.za Garibaldi, 5
Cassa di Risparmio di via Ricostruzione, 3
Credito Commerciale
p.za XXV Aprile, 2
Localita
TORINO
MILANO
TORINO
MILANO
Un comando di update può anche essere effettuato riprendendo i dati presenti su tabelle differenti: in
questo caso, per gestire il join delle tabelle, indicare direttamente nel ‘from’ le tabelle di riferimento e
specificare la condizione di join nel ‘where’:
Update clienti
set clienti.campo1 = tmp.campo2
FROM clienti, tmp
where tmp.id = clienti.codcli
ha l’effetto di aggiornare il campo1 della tabella clienti con il valore del campo2 della tabella tmp
(temporanea) a condizione che sia rispettato il vincolo “tmp.id = clienti.codcli”.