Linguaggio SQL - IISS Giulio Cesare

Linguaggio SQL
Prof. Giuseppe Tandoi
Linguaggio SQL
Caratteristiche generali
SQL è uno strumento per
organizzare
gestire
recuperare
dati memorizzati in DataBase organizzati su base Relazionale.
Prof. Giuseppe Tandoi
Linguaggio SQL
Caratteristiche generali
SQL è l'acronimo di
Structured
Query
Language.
SQL è un linguaggio (non procedurale) per interagire con database.
Prof. Giuseppe Tandoi
Linguaggio SQL
Caratteristiche generali
SQL è un linguaggio standard per chi deve gestire database.
Può essere considerato un
sottolinguaggio di interazione le cui dichiarazioni possono anche essere incluse
in programmi scritti in altro linguaggio (Cobol, Php, ecc.).
Prof. Giuseppe Tandoi
Linguaggio SQL
Caratteristiche generali
SQL non è
un software
un sistema di gestione di database
un linguaggio di programmazione procedurale
Prof. Giuseppe Tandoi
Linguaggio SQL
Caratteristiche generali - DBMS
DBMS = Data Base Management System
Software che controlla il database
consente le operazioni di definizione, manipolazione e estrazione
Prof. Giuseppe Tandoi
Linguaggio SQL
Caratteristiche generali – estrazione informazioni
Processo di estrazione delle informazioni
1. DBMS riceve la richiesta SQL
2. DBMS elabora la richiesta
3. DBMS recupera i dati richiesti
4. DBMS restituisce le informazioni
Il processo di estrazione delle informazioni viene denominato QUERY.
Prof. Giuseppe Tandoi
Linguaggio SQL
Caratteristiche generali - DBMS
E' il DBMS che consente interazioni attraverso l'uso del linguaggio SQL.
Prof. Giuseppe Tandoi
Linguaggio SQL
Regole generali - formato istruzione
Le istruzioni sono formate dal verbo imperativo e dai parametri e terminano con il
punto e virgola ( ; )
create table ALUNNI
( ID
Smallint,
COGNOME char(20),
NOME
char(20) ) ;
Prof. Giuseppe Tandoi
Linguaggio SQL
Regole generali – definizione identificatori
Gli identificatori, cioè i nomi di Tabelle o di Attributi
- sono costituiti da caratteri, cifre , carattere '_' (UnderScore)
- possono essere lunghi massimo 18 caratteri
- devono iniziare con una lettera
Prof. Giuseppe Tandoi
Linguaggio SQL
Regole generali – definizione identificatori
L'identificatore assoluto di un attributo è costituita dalla abituale notazione :
NomeTabella . NomeAttributo
Ordini.Data
Alunni.Data_di_Nascita
NomeTabella si può omettere se non vi sono di ambiguità di identificazione.
Prof. Giuseppe Tandoi
Linguaggio SQL
Regole generali – definizione costanti
La costante e' definita attraverso il suo valore
*
considerata di tipo numerico intero se il valore non contiene parte frazionaria
*
considerata di tipo numerico reale se il valore presenta il punto decimale
separatore tra parte intera e parte frazionaria
*
la costante di tipo stringa è racchiusa tra singoli apici ('cost')
se la costante contiene un'apice allora deve essere racchiusa tra
doppi apici (“cost”)
La costante NULL rappresenta il concetto di valore non definito o valore non
disponibile incontrato in una colonna.
Prof. Giuseppe Tandoi
Linguaggio SQL
Definizione dei dati - DDL
DDL : Data Description Language
Sottoinsieme di istruzioni di SQL che consentono di
* definire nuove tabelle
* modificare la struttura delle tabelle
* rimuovere tabelle
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – tipi standard di dati
BOOLEAN
Valore logico
CHARACTER(n) Stringa di lunghezza (n)
True, false
N da 1 a 15000
DATE
Data nella forma mm/gg/aa
TIME
Ora nella forma hh:mm
INTEGER(p)
Numero intero di precisione P
P da 1 a 45
SMALLINT
Numero intero (16 bit)
Da -32768 a +32767
INTEGER
Numero intero (32 bit)
Da -2147483648 a +2147483647
DECIMAL(p,s)
Numero decimale di p cifre di cui s decimali
REAL
Numero reale con mantissa di precisione 7
FLOAT
Numero reale con mantissa di precisione 15
FLOAT(p)
Numero reale con mantissa di precisione p
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione nuove tabelle
CREATE TABLE NomeTabella
( nome attr1 tipo,
nome attr2 tipo,
...
nome attrn tipo) ;
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione nuove tabelle
CREATE TABLE Alunni
( Id
SMALLINT ,
Cognome
CHAR(20),
Nome
CHAR(20),
Indirizzo CHAR(40),
Classe
CHAR(10) );
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – eliminazione tabella
DROP TABLE NomeTabella ;
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – eliminazione tabella
DROP TABLE Alunni;
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – modifica struttura tabella
Aggiunta attributo
ALTER TABLE NomeTabella
ADD nome attr1 tipo;
Rimozione attributo
ALTER TABLE NomeTabella
DROP nome attr ;
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – modifica struttura tabella
Aggiunta attributo
ALTER TABLE Alunni
ADD ContattoMSN Char(50);
Rimozione attributo
ALTER TABLE Alunni
DROP Classe;
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione nuove tabelle - clausole
CREATE TABLE NomeTabella
( nome attr1 tipo PRIMARY KEY,
nome attr2 tipo UNIQUE,
nome attr3 tipo NOT NULL,
nome attr4 tipo AUTO_INCREMENT,
...
nome attrn tipo DEFAULT valore) ;
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione nuove tabelle - clausole
PRIMARY KEY
dichiara quell'attributo come chiave primaria della tabella
UNIQUE
rende inammissibili valori uguali
NOT NULL
obbliga ad indicare un valore
DEFAULT
determina il valore (costante) da attribuire al momento della
creazione del record in assenza di indicazione
AUTO_INCREMENT per attributi di tipo numerico; assegna automaticamente il
valore incrementando un contatore ad ogni inserimento di
record
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione nuove tabelle - clausole
CREATE TABLE Alunni
( Id
SMALLINT PRIMARY KEY,
Cognome
CHAR(20) NOT Null,
Nome
CHAR(20) NOT Null,
Indirizzo CHAR(40),
Classe
CHAR(10) NOT Null,
Citta
CHAR(30) DEFAULT 'Bari' );
Prof. Giuseppe Tandoi
Linguaggio SQL
Manipolazione dei dati - DML
DML : Data Manipulation Language
Sottoinsieme di istruzioni di SQL che consentono di
* inserire nuovi record
* modificare il contenuto dei campi dei record
* cancellare record
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Inserimento record
INSERT INTO NomeTabella
(nome attr1, nome attr2, ... , nome attrn )
VALUES (val1, val2, ... , valn);
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Inserimento record
INSERT INTO Alunni
(Id, Cognome, Nome, Indirizzo, Classe, Citta)
VALUES (23,'Rossi', 'Mario','Via Roma,12', '', 'Bari' );
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Modifica record
UPDATE NomeTabella
SET nome attr = valore
WHERE CondizioneDiSelezione;
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Modifica record
UPDATE Alunni
SET Classe = '5BM'
WHERE ID = 23;
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Elimina record
DELETE FROM NomeTabella
WHERE CondizioneDiSelezione;
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Elimina record
DELETE FROM Alunni
WHERE ID = 23;
DELETE FROM Alunni
WHERE Alunni.Classe = '5BM';
Prof. Giuseppe Tandoi
Linguaggio SQL
Query Language – Interrogazione database
L' interrogazione al database:
consente l'estrazione dal database delle informazioni desiderate
produce una nuova tabella attraverso l'attivazione delle operazioni relazionali
Prof. Giuseppe Tandoi
Linguaggio SQL
Query Language – Interrogazione database
Le interrogazioni al database si pongono attraverso il comando
SELECT
La struttura base del comando SELECT è
SELECT colonne
FROM tabella;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query Language – Interrogazione database
SELECT *
DELETE FROM Alunni
FROM Alunni;
WHERE ID = 23;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query Language – Interrogazione database
SELECT *
DELETE FROM Alunni
FROM Alunni;
WHERE ID = 23;
SELECT Alunni.Id, Alunni.Cognome, Alunni.Nome, Alunni.Indirizzo,
Alunni.Classe, Alunni.Citta
FROM Alunni;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query Language – Interrogazione database
SELECT *
DELETE FROM Alunni
FROM Alunni;
WHERE ID = 23;
SELECT Alunni.Id, Alunni.Cognome, Alunni.Nome, Alunni.Indirizzo,
Alunni.Classe, Alunni.Citta
FROM Alunni;
SELECT Id, Cognome, Nome, Indirizzo, Classe, Citta
FROM Alunni;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query Language – Interrogazione database
La tabella prodotta dalla interrogazione è costituita da colonne con l'intestazione
equivalente al nome dei campi
Si può far mostrare una intestazione differente utilizzando la clausola AS.
SELECT colonna AS intestazionecolonna
FROM tabella;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query Language – Interrogazione database
SELECT Id AS Matricola, Cognome, Nome AS NomeDiBattesimo,
DELETE FROM Alunni
FROM Alunni;
WHERE ID = 23;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query Language – Interrogazione database
La tabella prodotta dalla interrogazione può anche vedere la presenza di colonne con
valori calcolati sugli attributi presenti ed ottenuti dalla esecuzioni di espressioni
aritmetiche
SELECT espressionenumerica AS intestazionecolonna
FROM Tabella;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query Language – Interrogazione database
SELECT Numero, Data, Importo As Imponibile, Importo * 0,20 AS Iva,
DELETE FROM Alunni
Importo * 1,20 AS TotaleFattura
WHERE ID = 23;
FROM Fatture;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Selezione
Si ottiene specificando la clausola WHERE
SELECT *
FROM Tabella
WHERE CondizioneDiSelezione;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Condizioni di selezione
La Condizioni di Selezione determina la selezione delle righe
E' una espressione relazionale in cui sono confrontati una colonna ed un valore.
Il confronto è espresso da uno degli operatori relazionali:
= , > , < , <> , >= , <=
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Selezione
SELECT *
DELETE FROM Alunni
FROM Alunni
WHERE ID = 23;
WHERE Alunni.Citta = 'Bari';
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Condizioni di selezione
Una CondizioneDiSelezione può essere costituita da più condizioni legate tra loro
dagli operatori
AND , OR
Una CondizioneDiSelezione può essere preceduta dall'operatore
NOT
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Operatori di Selezione
SELECT *
DELETE FROM Alunni
FROM Alunni
WHERE ID = 23;
WHERE Alunni.Citta <> 'Bari'
AND Alunni.Classe = '5BM';
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Operatori di Selezione
SELECT *
DELETE FROM Alunni
FROM Alunni
WHERE ID = 23;
WHERE Alunni.Citta <> 'Bari'
AND Alunni.Classe = '5BM';
SELECT *
FROM Alunni
WHERE NOT (Alunni.Citta <> 'Bari'
AND Alunni.Classe = '5BM') ;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Altri operatori di relazione
Le Condizioni di Selezione si possono esprimere utilizzando altri operatori o
predicati per determinare ricerche più affinate
IN
LIKE
BETWEEN
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Altri operatori di relazione
IN
L'operatore IN confronta se il valore appartiene ad uno dei valori di una lista
definita, tra parentesi dopo la clausola In.
... WHERE Colonna IN ( valore1, Valore2, ... , Valoren);
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Operatori di Selezione
SELECT *
DELETE FROM Alunni
FROM Alunni
WHERE ID = 23;
WHERE Alunni.Classe IN ('3BM', '4BM', '5BM');
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Operatori di Selezione
SELECT *
DELETE FROM Alunni
FROM Alunni
WHERE ID = 23;
WHERE Alunni.Classe IN ('3BM', '4BM', '5BM');
corrisponde a
SELECT *
FROM Alunni
WHERE Alunni.Classe = '3BM'
OR Alunni.Classe = '4BM'
OR Alunni.Classe = '5BM';
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Altri operatori di relazione
BETWEEN
L'operatore BETWEEN confronta se il valore nella colonna è incluso in un
intervallo di valori, estremi compresi.
... WHERE Colonna BETWEEN Valore1 AND Valore2 ;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Operatori di Selezione
SELECT *
DELETE FROM Alunni
FROM Alunni
WHERE ID = 23;
WHERE Alunni.Voto BETWEEN 6 AND 8;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Altri operatori di relazione
LIKE
L'operatore LIKE confronta il valore di un attributo con un modello di stringa
contenenti caratteri caratteri jolly
*
una qualunque sequenza di caratteri
?
un carattere
... WHERE Colonna LIKE MascheraDiRicerca;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Operatori di Selezione
SELECT *
DELETE FROM Alunni
FROM Alunni
WHERE ID = 23;
WHERE Alunni.Cognome LIKE 'BA*';
SELECT *
FROM Alunni
WHERE Alunni.Cognome LIKE 'BA??';
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Predicati ALL e DISTINCT
SELECT ALL colonna1, colonna2, ... , colonnam
FROM Tabella
WHERE condizione;
consente di ottenere tutte le righe che soddisfano la condizione. Il predicato ALL
è considerato di default.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Predicati ALL e DISTINCT
SELECT ALL *
DELETE FROM Alunni
FROM Alunni
WHERE ID = 23;
WHERE Classe = '5BM';
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Predicati ALL e DISTINCT
SELECT DISTINCT colonna
FROM Tabella
WHERE condizione;
consente di ottenere nella colonna valori non ripetuti;
vale a dire che se nella colonna appare più volte uno stesso valore, sarà
riportata una sola riga in cui questo è contenuto. E' opportuno utilizzarlo per
conoscere quali sono i differenti valori presenti in una colonna
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Predicati ALL e DISTINCT
SELECT DISTINCT CittaNascita
DELETE FROM Alunni
FROM Alunni;
WHERE ID = 23;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Proiezione
Si ottiene specificando l'elenco degli attributi richiesti
SELECT colonna1, colonna2, ... , colonnam
FROM Tabella;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Proiezione
SELECT Alunni.Cognome, Alunni.Nome, Alunni.Classe
DELETE FROM Alunni
FROM Alunni;
WHERE ID = 23;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Proiezione
SELECT Alunni.Cognome, Alunni.Nome, Alunni.Classe
DELETE FROM Alunni
FROM Alunni;
WHERE ID = 23;
SELECT Cognome, Nome, Classe
FROM Alunni;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Selezione di proiezione
SELECT Colonna1, Colonna2, ..., Colonnam
FROM Tabella
WHERE CondizioneDiSelezione ;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Selezione di proiezione
SELECT Alunni.Cognome, Alunni.Nome, Alunni.Classe
DELETE FROM Alunni
FROM Alunni
WHERE ID = 23;
WHERE Alunni.Citta = 'Bari';
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Congiunzione
SELECT
*
FROM Tabella1, Tabella2
WHERE CondizioneDiCongiunzione ;
CondizioneDiCongiunzione : CampoChiaveEsterna = CampoChiavePrimaria
Sono combinate solo le righe per le quali si possono trovare valori uguali per gli
attributi che si corrispondono (equi-join).
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Congiunzione
SELECT *
DELETE FROM Alunni
FROM Alunni, Prove
WHERE ID = 23;
WHERE Prove.IDAlunno = Alunni.ID ;
La query dell'esempio produce una tabella costituita da tante colonne quanti sono gli
attributi delle tabelle congiunte e dalle sole righe aventi valori uguali per gli attributi
messi nella condizione di congiunzione.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Combinazione delle operazioni relazionali
SELECT
colonna1, colonna2, ... , colonnam
FROM Tabella1, Tabella2
WHERE CondizioneDiCongiunzione
AND
CondizioneDiSelezione ;
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Combinazione delle operazioni relazionali
Mostrare gli esiti di tutte le prove di Inglese sostenute dagli studenti della 5BM
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Combinazione delle operazioni relazionali
Mostrare gli esiti di tutte le prove di Inglese sostenute dagli studenti della 5BM
SELECT Alunni.Cognome, Alunni.Nome, Prove.Data, Prove.Voto
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'INGLESE' AND Alunni.Classe = '5BM' );
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
Funzioni predefinite che operano sui dati contenuti nelle righe selezionate
dall'interrogazione effettuando
conteggio, determinazione del massimo o del minimo, somma, media
fra i valori di una colonna.
COUNT
SUM
MIN e MAX
AVG
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT
COUNT(colonna)
FROM Tabella
WHERE CondizioneDiSelezione ;
Restituisce il numero delle righe della tabella (solo quelle selezionate) ove si riscontra
un valore non nullo nella colonna.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT COUNT(Cognome)
FROM Alunni
WHERE Classe = '5BM';
Mostrare il numero di alunni della 5BM
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT COUNT(Prove.Data)
FROM Alunni,Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'INGLESE' AND Alunni.Classe = '5BM' )
Mostrare il numero delle prove (compiti e interrogazioni) sostenute dagli alunni della
5BM in Inglese
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT
COUNT( DISTINCT ( colonna ) )
FROM Tabella
WHERE CondizioneDiSelezione ;
Conta i valori diversi non ripetutii riscontrati nella colonna.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT COUNT( DISTINCT (CittaDiResidenza) )
FROM Alunni
WHERE Classe = '5BM';
Mostrare il numero delle città di residenza degli alunni della 5BM
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT
COUNT(*)
FROM Tabella
WHERE CondizioneDiSelezione ;
Restituisce il numero di tutte le righe selezionate della tabella; non sono escluse
righe con valori nulli.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT COUNT(NumeroCartaDiCredito)
FROM Correntisti;
Mostrare il numero dei correntisti titolari di carta di credito
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT COUNT(NumeroCartaDiCredito)
FROM Correntisti;
Mostra il numero dei correntisti titolari di carta di credito
SELECT COUNT(*)
FROM Correntisti;
Mostra il numero di tutti i correntisti
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT
SUM(colonna)
FROM Tabella
WHERE CondizioneDiSelezione ;
Restituisce la somma di tutti i valori presenti in tutte le righe della tabella (solo quelle
selezionate) di una specifica colonna. La colonna deve contenere dati di tipo non
numerico.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT SUM(Prezzo)
FROM LibriDiTesto
WHERE Classe = '5BM';
Mostrare il costo totale dei libri di testo adottati per la 5BM
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT
MAX(colonna) oppure MIN(colonna)
FROM Tabella
WHERE CondizioneDiSelezione ;
Restituisce il massimo fra i valori presenti in tutte le righe della tabella (solo quelle
selezionate) di una specifica colonna. La colonna può contenere dati di tipo non
numerico.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT MIN(Voto)
FROM Prove;
Mostrare qual'è il voto minimo fra tutti quelli che sono stati registrati
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT MAX(Prove.Voto)
FROM Alunni,Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'INGLESE' AND Alunni.Classe = '5BM' );
Mostrare qual'è il massimo riportato da un alunno della 5BM in Inglese.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT
AVG(colonna)
FROM Tabella
WHERE CondizioneDiSelezione ;
Restituisce la media fra i valori presenti in tutte le righe della tabella (solo quelle
selezionate) di una specifica colonna. Ovviamente la colonna deve contenere dati di
tipo numerico.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT AVG(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM'
AND Alunni.Cognome = 'Martinelli' );
Mostrare qual'è la media dei voti assegnati a Martinelli della 5BM in Matematica
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT AVG(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM');
Mostrare qual'è la media dei voti delle prove conseguiti dagli alunni della 5bm in
matematica.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT AVG(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM');
Mostrare qual è la media dei voti delle prove conseguiti dagli alunni della 5bm in
matematica.
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Funzioni di aggregazione
SELECT Prove.Voto
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM');
Mostrare i voti riportati dagli alunni della 5 bm in matematica
Prof. Giuseppe Tandoi
Linguaggio SQL
Ordinamenti
L'interrogazione mostra le righe elencate come presenti nelle tabelle da cui sono
originate.
E' possibile mostrare le righe anche secondo un differente ordine basato sui valori
presenti in una colonna (o più) specificando la clausola
ORDER BY
Si impostare un ordinamento crescente (ASCending) o decrescente (DESCending)
Prof. Giuseppe Tandoi
Linguaggio SQL
Ordinamenti
SELECT
colonna1, colonna2, ..., colonnan
FROM ...
WHERE ...
ORDER BY colonna1 [ ASC / DESC ] ,
colonna2 [ ASC / DESC ] ... ;
Prof. Giuseppe Tandoi
Linguaggio SQL
Ordinamenti
SELECT Prove.Data, Alunni.Cognome, Alunni.Nome, Prove.Voto
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'INGLESE' AND Alunni.Classe = '5BM' )
ORDER BY Prove.Data DESC;
Lista delle prove (interrogazioni e compiti) di inglese svolte dagli alunni della 5BM
ordinati per data, dalla più recente alla più remota.
Prof. Giuseppe Tandoi
Linguaggio SQL
Ordinamenti
SELECT Alunni.Cognome, Alunni.Nome, Prove.Data, Prove.Voto
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'INGLESE' AND Alunni.Classe = '5BM' )
ORDER BY 4;
Lista delle prove (interrogazioni e compiti) di inglese svolte dagli alunni della 5BM
ordinati in base alla quarta colonna definita nella proiezione, cioè in base al voto.
Prof. Giuseppe Tandoi
Linguaggio SQL
Ordinamenti
SELECT Alunni.Cognome, Alunni.Nome, Prove.Data, Prove.Materia, Prove.Voto
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Alunni.Classe = '5BM' )
ORDER BY Prove.Materia, Prove.Voto DESC, Alunni.Cognome;
Lista delle prove (interrogazioni e compiti) svolte dagli alunni della 5BM ordinati
alfabeticamente per materia, in modo decrescente per voto nell'ambito delle prove di
una stessa materia ed infine per alunno.
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti
Le funzioni di aggregazione (MIN, MAX, AVG, COUNT, SUM )condensano le
informazioni di tutta una tabella (o solo delle righe selezionate) in un unico valore.
Le funzioni di aggregazione possono operare non solo su tutta la tabella ma per
gruppi di record.
Per esempio voglio che venga mostrata :
per ciascun alunno la sua media dei voti
per ciascuna materia il numero di prove che sono state sostenute
per ciascuna classe il voto più alto registrato al suo interno
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti
Cliente
NumFatt Importo
Altomare
8
1200
Altomare
15
2000
Bellomo
3
150
Bellomo
7
1250
Bellomo
14
800
Genchi
1
100
Genchi
11
900
Lorusso
2
100
Lorusso
6
1500
Lorusso
10
500
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti
Cliente
NumFatt Importo
Altomare
8
1200
Altomare
15
2000
Bellomo
3
150
Bellomo
7
1250
Altomare
3200
Bellomo
14
800
Bellomo
2200
Genchi
1
100
Genchi
1000
Genchi
11
900
Lorusso
2100
Lorusso
2
100
Lorusso
6
1500
Lorusso
10
500
Cliente
Sum(Importo)
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti
Il raggruppamento si definisce utilizzando la clausola
GROUP BY
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti
SELECT
Colonna, FunzioneDiAggregazione(colonna)
FROM ...
GROUP BY Colonna ;
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti
SELECT Cliente, Sum(Importo)
FROM Fatture
GROUP BY Cliente;
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti
Lista dei voti massimi riportati da ciascun alunno in qualunque prova.
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti
Lista dei voti massimi riportati da ciascun alunno in qualunque prova.
SELECT Alunni.Cognome, Max(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
GROUP BY Alunni.Cognome;
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti – condizioni sui raggruppamenti
Lista dei voti massimi riportati da ciascun alunno della 5BM nelle prove di
Matematica.
SELECT Alunni.Cognome, Max(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'MATEMATICA' AND Alunni.Classe = '5BM' )
GROUP BY Alunni.Cognome;
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti – condizioni sui raggruppamenti
Lista dei voti massimi riportati da ciascun alunno della 5BM nelle prove di
Matematica.
SELECT Alunni.Cognome, Max(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'MATEMATICA' AND Alunni.Classe = '5BM' )
GROUP BY Alunni.Cognome;
Il raggruppamento opera sulle righe selezionate dalla clausola WHERE
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti – condizioni sui raggruppamenti
La clausola
HAVING condizione
usata insieme a GROUP BY, consente di visualizzare le sole righe prodotte dai
raggruppamenti che soddisfano la condizione.
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti – condizioni sui raggruppamenti
La clausola
HAVING condizione
usata insieme a GROUP BY, consente di visualizzare le sole righe prodotte dai
raggruppamenti che soddisfano la condizione.
Per esempio
gli alunni con una media superiore a 6
i professionisti che hanno un compenso totale superiore a 50.000 Euro
le discipline in cui gli studenti non abbiano riportato un voto superiore a 7
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti – condizioni sui raggruppamenti
SELECT
Colonna, FunzioneDiAggregazione(colonna)
FROM ...
GROUP BY Colonna
HAVING condizione ;
La condizione si basa sul confronto tra la funzione di aggregazione e un altro
dato. Valuta per ciascun raggruppamento il risultato della funzione di aggregazione
sul raggruppamento e l'altro dato.
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti – condizioni sui raggruppamenti
Lista degli alunni che hanno riportato come loro voto più alto, un voto maggiore di 7.
In qualunque prova di qualunque disciplina
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti – condizioni sui raggruppamenti
Lista degli alunni che hanno riportato come loro voto più alto, un voto maggiore di 7.
In qualunque prova di qualunque disciplina
SELECT Alunni.Cognome, Max(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
GROUP BY Alunni.Cognome
HAVING Max(Prove.Voto) > 7;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
La sottoquery è una normale interrogazione che viene inclusa in un'altra query nella
espressione condizionale di quella che la contiene.
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
La sottoquery è una normale interrogazione che viene inclusa in un'altra query nella
espressione condizionale di quest'ultima.
ES.
SELECT ............
FROM ......
WHERE dato >, =, <, IN
;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
La sottoquery è una normale interrogazione che viene inclusa in un'altra query nella
espressione condizionale di quest'ultima.
ES.
SELECT ............
FROM ......
WHERE dato >, =, <, IN
( SELECT ........
FROM
WHERE ........) ;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
La sottoquery è una normale interrogazione che viene inclusa in un'altra query nella
espressione condizionale di quest'ultima.
ES.
SELECT ............
Query principale
FROM ......
WHERE dato >, =, <, IN
( SELECT ........
FROM
SottoQuery o Query interna
WHERE ........) ;
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
La sottoquery produce un'unica colonna in una o più righe.
Quindi la sottoquery deve essere la proiezione di un'unica colonna.
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
La sottoquery produce un'unica colonna in una o più righe.
Quindi la sottoquery deve essere la proiezione di un'unica colonna.
. . . SELECT AVG(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM') . . .
Media dei voti di tutte le prove di matematica degli alunni della 5BM.
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
La sottoquery produce un'unica colonna in una o più righe.
Quindi la sottoquery deve essere la proiezione di un'unica colonna.
. . . SELECT AVG(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM') . . .
Media dei voti di tutte le prove di matematica degli alunni della 5BM.
AVG(Prove.Voto)
6,34
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
Il valore prodotto dalla esecuzione della query interna
AVG(Prove.Voto)
6,34
è utilizzato nella espressione condizionale che definisce la selezione della query
principale
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
Alunni della 5BM che conseguito nelle prove di Matematica almeno un voto
superiore alla media di tutte le prove.
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
Alunni della 5BM che conseguito nelle prove di Matematica almeno un voto
superiore alla media dei voti di tutte le prove.
SELECT DISTINCT Alunni.Cognome, Alunni.Nome
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND Prove.Materia = 'Matematica'
AND Prove.Voto >
(
SELECT AVG(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM')
);
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
SELECT DISTINCT Prove.IdAlunno
FROM Prove
WHERE Prove.Materia = 'Matematica'
AND Prove.Voto >
(
SELECT AVG(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM')
);
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
SELECT DISTINCT Prove.IdAlunno
FROM Prove
WHERE Prove.Materia = 'Matematica'
AND Prove.Voto >
(
SELECT AVG(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM')
);
Codici degli alunni della 5BM che conseguito nelle prove di Matematica
almeno un voto superiore alla media dei voti di tutte le prove.
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
SELECT DISTINCT Prove.IdAlunno
FROM Prove
WHERE Prove.Materia = 'Matematica'
AND Prove.Voto >
(
SELECT AVG(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM')
);
Prove.IdAlunno
3
5
2
1
7
Prof. Giuseppe Tandoi
Linguaggio SQL
Query nidificate (o sottoquery)
SELECT DISTINCT Alunni.ComuneResidenza
FROM Alunni
WHERE Alunni.ID IN
(
SELECT DISTINCT Prove.IdAlunno
FROM Prove
WHERE Prove.Materia = 'Matematica'
AND Prove.Voto >
(
SELECT AVG(Prove.Voto)
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'Matematica'
AND Alunni.Classe = '5BM')
)
);
Prof. Giuseppe Tandoi