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 colonnaaverso 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 VARCHAR(20),
NOME
VARCHAR(20) ) ;
Prof. Giuseppe Tandoi
Linguaggio SQL
Regole generali – definizione identificatori
Gli identificatori, cioè i nomi di Tabelle o di colonnaibuti
- 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 colonnaibuto è costituita dalla abituale notazione :
NomeTabella . Nomecolonnaibuto
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 colonnaaverso 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
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione nuove tabelle
CREATE TABLE NomeTabella
( nome colonna1
tipo,
nome colonna2
tipo,
...
nome colonnan
tipo) ;
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione nuove tabelle
CREATE TABLE Alunni
( Id
SMALLINT
,
Cognome
VARCHAR(20),
Nome
VARCHAR(20),
Indirizzo
VARCHAR(40),
Classe
VARCHAR(10) );
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione nuove tabelle - clausole
CREATE TABLE NomeTabella
( nome colonna1
tipo PRIMARY KEY,
nome colonna2
tipo UNIQUE,
nome colonna3
tipo NOT NULL,
nome colonna4
tipo AUTO_INCREMENT,
...
nome colonnan
tipo DEFAULT valore) ;
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione nuove tabelle - clausole
PRIMARY KEY
dichiara la colonna come chiave primaria della tabella
UNIQUE
rende inammissibili valori uguali
NOT NULL
obbliga, in fase di manipolazione, ad indicare un valore
DEFAULT
determina il valore (costante) da proporre al momento
della creazione della
AUTO_INCREMENT
per colonna di tipo numerico; ad ogni
inserimento di una nuova riga, assegna
automaticamente un valore ottenuto incrementando un
contatore
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione nuove tabelle - clausole
CREATE TABLE Alunni
( Id
SMALLINT PRIMARY KEY,
Cognome
VARCHAR(20) NOT Null,
Nome
VARCHAR(20) NOT Null,
Indirizzo
VARCHAR(40),
Classe
VARCHAR(10) NOT Null,
Citta
VARCHAR(30) DEFAULT 'Bari' );
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione chiavi primarie
CREATE TABLE Alunni
( Id
SMALLINT PRIMARY KEY,
. . .
. . .,
. . .
. . .,
. . .
. . .
);
.. o in alternativa
CREATE TABLE Alunni
( Id
SMALLINT,
. . .
. . .,
. . .
. . .,
PRIMARY KEY (Id)
);
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – definizione chiavi esterne
CREATE TABLE Alunni
( Id
SMALLINT PRIMARY KEY,
. . .
. . .,
IdClasse
SMALLINT REFERENCES CLASSI(Id),
. . .
. . .
);
.. o in alternativa
CREATE TABLE Alunni
( Id
. . .
IdClasse
SMALLINT PRIMARY KEY,
. . .,
SMALLINT ,
FOREIGN KEY (IdClasse)
REFERENCES CLASSI(Id) );
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 colonna
ALTER TABLE NomeTabella
ADD nome colonna tipo;
Rimozione colonna
ALTER TABLE NomeTabella
DROP nome colonna ;
Prof. Giuseppe Tandoi
Linguaggio SQL
DDL – modifica struttura tabella
Aggiunta colonna
ALTER TABLE Alunni
ADD ContattoMSN VARCHAR(50);
Rimozione colonna
ALTER TABLE Alunni
DROP Classe;
Prof. Giuseppe Tandoi
Linguaggio SQL
Manipolazione dei dati - DML
DML : Data Manipulation Language
Sottoinsieme di istruzioni di SQL che consentono di
* inserire nuove righe
* modificare il contenuto di una o più colonne
* cancellare righe
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Inserimento record
INSERT INTO NomeTabella
(colonna1, colonna2, ... , colonnan ) VALUES (val1, val2, ... , valn);
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Inserimento record
INSERT INTO NomeTabella
(colonna1, colonna2, ... , colonnan ) VALUES (val1, val2, ... , valn);
INSERT costruisce una riga di dati con la struttura e i valori dati e la aggiunge in
coda alla tabella
L’elenco dei valori della proposizione VALUES deve corrispondere, per numero e per
ordine, all’elenco delle colonne
Il tipo di dato di ciascun valore deve essere compatibile con il tipo di dato della
colonna corrispondente
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Inserimento record
INSERT INTO Alunni
(Id, Cognome, Nome, Indirizzo, Classe, Citta)
VALUES (23,'Rossi', 'Mario', 'Via Roma,12', ‘5C Sia', 'Bari' );
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Inserimento record
INSERT INTO Alunni
(Id, Cognome, Nome, Indirizzo, Classe, Citta)
VALUES (23,'Rossi', 'Mario', 'Via Roma,12', ‘5C Sia', 'Bari' );
Insert Into
Fatture
( Data, Aliquota, Iva, TotaleDocumento, Imponibile)
VALUES
( ‘2014-08-21’, 22, 110.00, 610.00, 500.00);
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Inserimento record
Nella lista delle colonne e dei valori assegnati, si può omettere di riportarne una o
più.
In tale caso, ad ogni colonna mancante, viene attribuito il valore NULL.
INSERT INTO Alunni
(Id, Cognome, Nome, Indirizzo, Classe, Citta)
VALUES (23,'Rossi', 'Mario', 'Via Roma,12', ‘5CSia', 'Bari' );
INSERT INTO Alunni
(Id, Cognome, Nome, Classe, Citta)
VALUES (23,'Rossi', 'Mario’, '5CSia', 'Bari' );
Non definita la colonna Indirizzo
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Inserimento record
Se la chiave primaria è di tipo INTEGER Auto_Increment, il valore da dare alla
colonna corrispondente non deve apparire nella lista.
INSERT INTO Alunni
(Matricola, Cognome, Nome, Indirizzo, Classe, Citta)
VALUES ( ,'Rossi', 'Mario', 'Via Roma,12', ‘5CSia', 'Bari' );
Oppure si può omettere anche la colonna.
INSERT INTO Alunni
(Cognome, Nome, Indirizzo, Classe, Citta)
VALUES ('Rossi', 'Mario’, 'Via Roma,12', '5CSia', 'Bari' );
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Modifica record
UPDATE NomeTabella
SET nome colonna = valore
WHERE CondizioneDiSelezione;
Prof. Giuseppe Tandoi
Linguaggio SQL
DML – Modifica record
UPDATE Alunni
SET Classe = '5BSia'
WHERE ID = 23;
UPDATE Alunni
SET Classe = '5BSia'
WHERE Classe = ‘4BM’;
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;
Oppure
DELETE FROM Alunni
WHERE Alunni.Classe = '5BSia';
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 che vengono 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 = '5BSia';
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 = '5BSia';
SELECT *
FROM Alunni
WHERE NOT (Alunni.Citta <> 'Bari'
AND Alunni.Classe = '5BSia') ;
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', '5BSia');
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', '5BSia');
corrisponde a
SELECT *
FROM Alunni
WHERE Alunni.Classe = '3BM'
OR Alunni.Classe = '4BM'
OR Alunni.Classe = '5BSia';
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 colonnaibuto 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 = '5BSia';
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; è opportuno utilizzarlo per conoscere quali
siano 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 delle colonne richieste
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 le
colonne 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 ;
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 quante sono le colonne di ciascuna delle tabelle congiunte
e
dalle sole righe aventi valori uguali per le colonne poste 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 5BSia
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Combinazione delle operazioni relazionali
Mostrare gli esiti di tutte le prove di Inglese sostenute dagli studenti della 5BSia
SELECT Alunni.Cognome, Alunni.Nome, Prove.Data, Prove.Voto
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'INGLESE' AND Alunni.Classe = '5BSia' );
Prof. Giuseppe Tandoi
Linguaggio SQL
Operazioni relazionali – Combinazione delle operazioni relazionali
Mostrare gli esiti di tutte le prove di Inglese sostenute dagli studenti della 5BSia
SELECT Alunni.Cognome, Alunni.Nome, Prove.Data, Prove.Voto
FROM Alunni, Prove
WHERE Prove.IDAlunno = Alunni.ID
AND
( Prove.Materia = 'INGLESE' AND Alunni.Classe = '5BSia' );
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 = '5BSia';
Mostrare il numero di alunni della 5BSia
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 = '5BSia' )
Mostrare il numero delle prove (compiti e interrogazioni) sostenute dagli alunni della
5BSia 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 = '5BSia';
Mostrare il numero delle città di residenza degli alunni della 5BSia
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 = '5BSia';
Mostrare il costo totale dei libri di testo adottati per la 5BSia
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 = '5BSia' );
Mostrare qual è il massimo riportato da un alunno della 5BSia 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 = '5BSia'
AND Alunni.Cognome = 'Martinelli' );
Mostrare qual è la media dei voti assegnati a Martinelli della 5BSia 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 = '5BSia');
Mostrare qual è la media dei voti delle prove conseguiti dagli alunni della 5BSia 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 = '5BSia');
Mostrare qual è la media dei voti delle prove conseguiti dagli alunni della 5BSia 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 = '5BSia');
Mostrare i voti riportati dagli alunni della 5BSia 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 = '5BSia' )
ORDER BY Prove.Data DESC;
Lista delle prove (interrogazioni e compiti) di inglese svolte dagli alunni della 5BSia
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 = '5BSia' )
ORDER BY 4;
Lista delle prove (interrogazioni e compiti) di inglese svolte dagli alunni della 5BSia
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 = '5BSia' )
ORDER BY Prove.Materia, Prove.Voto DESC, Alunni.Cognome;
Lista delle prove (interrogazioni e compiti) svolte dagli alunni della 5BSia 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 5BSia 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 = '5BSia' )
GROUP BY Alunni.Cognome;
Prof. Giuseppe Tandoi
Linguaggio SQL
Raggruppamenti – condizioni sui raggruppamenti
Lista dei voti massimi riportati da ciascun alunno della 5BSia 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 = '5BSia' )
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 ............
FROM ......
Query principale
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 = '5BSia') . . .
Media dei voti di tutte le prove di matematica degli alunni della 5BSia.
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 = '5BSia') . . .
Media dei voti di tutte le prove di matematica degli alunni della 5BSia.
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 5BSia 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 5BSia 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 = '5BSia')
);
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 = '5BSia')
);
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 = '5BSia')
);
Codici degli alunni che conseguito nelle prove di Matematica almeno un voto
superiore alla media dei voti di tutte le prove di Matematica degli alunni della
5BSia.
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 = '5BSia')
);
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 = '5BSia')
)
);
Prof. Giuseppe Tandoi
Linguaggio SQL
Unione – operazione insiemistica
L’operazione insiemistica dell’unione viene realizzata dall’operatore UNION.
UNION opera su due tabelle compatibili (due tabelle aventi lo stesso numero di
colonne e con le colonne corrispondenti che hanno gli stessi tipi di dato)
UNION prende come input due tabelle e genera una singola tabella unita dai risultati
di query
Prof. Giuseppe Tandoi
Linguaggio SQL
Unione – operazione insiemistica
Elenco dei comuni di residenza degli alunni
SELECT DISTINCT Alunni.ComuneResidenza
FROM Alunni;
Prof. Giuseppe Tandoi
Linguaggio SQL
Unione – operazione insiemistica
Elenco dei comuni di residenza degli alunni
SELECT DISTINCT Alunni.ComuneResidenza
FROM Alunni;
Elenco dei comuni di nascita degli alunni
SELECT DISTINCT Alunni.ComuneNascita
FROM Alunni;
Prof. Giuseppe Tandoi
Linguaggio SQL
Unione – operazione insiemistica
Elenco dei comuni di residenza o di nascita degli alunni
SELECT DISTINCT Alunni.ComuneResidenza
FROM Alunni
UNION
SELECT DISTINCT Alunni.ComuneNascita
FROM Alunni;
;
Prof. Giuseppe Tandoi