Fondamenti di Informatica

(1)

Fondamenti di

Informatica

Prof. Fenza Giuseppe

DBMS – Linguaggio SQL

Interrogazioni Semplici

(2)

SQL Structured Query Language

• SQL è il linguaggio per DBMS più implementato

• Esso è un linguaggio di

– definizione dei dati (DDL)

• Agisce sulla struttura del DB

• per la definizione di

schemi

(logici, esterni, fisici) e altre

operazioni generali

– interrogazione e aggiornamento dei dati (DML)

• Agisce sul contenuto del DB.

• inserimento, cancellazione e modifica di dati

• interrogazione dei dati (per questo spesso il DML è detto

Query Language)

(3)

SQL

• Descritto teoricamente nel 1970 da Codd

• Progettato nel 1974 in IBM da Chamberlin e Boyce. • Sviluppato

– negli anni '70 da IBM a San José per il DBMS “System R”

– L’Università di Berkeley nel suo RDBMS Ingres prima, e la futura

Oracle poi, contribuirono alla definizione dello standard.

• Dichiarato standard

– 1986 da ANSI (American National Standards Institute), – 1987 da ISO (International Organization for Standards) • Esistono più Dialetti di SQL. Il principale è:

– SQL-92 (detto anche SQL-2), ultima versione universalmente accettata e corrisponde al nucleo del linguaggio; le sue regole sintattiche sono identiche in ogni versione.

(4)

SQL

• SQL è un

linguaggio dichiarativo

• specifichiamo l’obiettivo dell’interrogazione e non il

modo con cui ottenerlo

• È poi compito di una componente interna del DBMS (il

Query Optimization) trasformare l’interrogazione dal

formato dichiarativo al formato procedurale equivalente

• Noi siamo interessati alle sole funzionalità di

• interrogazione dei dati (DML) di SQL

• Le funzionalità di descrizione della struttura (DDL) le

realizziamo direttamente in ACCESS

(5)

SQL

•

La clausola WHERE è facoltativa e può contenere

•

gli operatori relazionali >, >=, etc.,

•

i connettivi logici AND, OR e NOT,

•

L’istruzione SELECT seleziona

•

tra le righe del

Prodotto Cartesiano R

₁

x …xR

_k •

quelle che

soddisfano la condizione

Y

•

Il risultato di una SELECT è una tabella che presenta

•

Le colonne A

₁

, …, A

_r

nell’ordine in cui compaiono qui

•

Una riga per ogni riga selezionata dalla clausola WHERE

(6)

Tabelle esempio: Impiegato/Dipartimento

Nome Cognome Dipartimento Ufficio Stipendio annuo Città

Impiegato _Anna _Bianco _{Amministrazione} ₇₅ _{€ 40.000,00 Venezia}

Carlo Bianchi Produzione 20 € 36.000,00 Torino Carlo Rossi Direzione € 80.000,00 Milano Franco Neri Distribuzione 16 € 45.000,00 Napoli

Giuseppe Verdi Amministrazione 20 € 40.000,00Roma Lorenzo Gialli Direzione € 73.000,00Genova Marco Franco Produzione 20 € 46.000,00Roma Mario Rossi Amministrazione 10 € 45.000,00 Milano

Impiegato (Nome, Cognome, Dipartimento, Ufficio, Stipendio annuo, Città)

Stipendio degli impiegati di cognome “Rossi”

SELECT FROM WHERE [Stipendio annuo] Impiegato Cognome="Rossi";

(7)

Query 01 - Alias su Attributi

Salario annuo degli impiegati di cognome “Rossi”

SELECT FROM WHERE [Stipendio annuo] Impiegato Cognome="Rossi"; Salario € 80.000,00 € 45.000,00 AS Salario

(8)

QUERY 02 - Wildcard

Tutti i dati degli impiegati di cognome “Rossi”

SELECT FROM WHERE * Impiegato Cognome="Rossi"; WILDCARD Equivale ad elencare

tutti i nomi dei campi

nell’ordine in cui

compaiono nella tabella input

(9)

QUERY 03 - Ordine delle colonne

Cognome, Nome, Stipendio, Ufficio, Dipartimento,

Città degli impiegati di cognome “Rossi”

SELECT FROM WHERE

Cognome, Nome, [Stipendio annuo], Ufficio, Dipartimento, Città

Impiegato

(10)

QUERY 04 - Funzioni di tuple

Nome, Cognome e stipendio mensile degli impiegati

di cognome “Rossi”

Nome, Cognome, [Stipendio annuo]/12 AS [Stipendio Mensile] Impiegato

Cognome="Rossi";

•

SELECT può presentare come argomenti anche

(11)

QUERY 05 - Assenza della WHERE

Cognome, Nome e Stipendio di tutti gli Impiegati

SELECT

FROM

Cognome, Nome, [Stipendio annuo] Impiegato

•

È possibile omettere la clausola WHERE (in questo

(12)

QUERY 06 - Condizioni multiple (1)

Nome e Cognome di tutti gli impiegati che lavorano

nell’ufficio 20 del dipartimento Amministrazione

Nome, Cognome Impiegato

Ufficio= 20 AND Dipartimento=“Amministrazione”;

(13)

QUERY 08 - Condizioni multiple (2)

Nome e Cognome di tutti gli impiegati che lavorano nel Dipartimento

Amministrazione o nel Dipartimento Produzione

SELECT FROM WHERE Nome, Cognome Impiegato Dipartimento=“Amministrazione” OR Dipartimento=“Produzione”;

(14)

QUERY 08 - Condizioni multiple (3)

Nome e Dipartimento degli impiegati che

a)lavorano nel Dipartimento Amministrazione o nel Dipartimento Produzione e che

b)hanno per cognome “Rossi” SELECT FROM WHERE Nome, Cognome Impiegato (Dipartimento=“Amministrazione” OR

(15)

QUERY 08bis - ERRATA

Nome e Dipartimento degli impiegati che

a)lavorano nel Dipartimento Amministrazione o nel Dipartimento Produzione e che

b)hanno per cognome “Rossi” SELECT FROM WHERE Nome, Cognome Impiegato Dipartimento=“Amministrazione” OR

Dipartimento=“Produzione” AND Cognome=“Rossi”

Formulazione errata. Mancano le parentesi che isolano l’OR

(16)

QUERY 09 - Ricerca in Intervalli

Tutti gli impiegati che hanno uno stipendio annuo compreso tra 40.000 e 75.000 Euro SELECT FROM WHERE * Impiegato

[Stipendio annuo]>= 40000 AND

(17)

QUERY 09bis - BETWEEN ... AND

Tutti gli impiegati che hanno uno stipendio annuo compreso tra

40.000 e 75.000 Euro SELECT FROM WHERE * Impiegato

(18)

QUERY 10 – Senza Ordinamento

Tutti gli impiegati che guadagnano almeno 45000 euro, ordinati rispetto alla chiave (modalità standard)

SELECT FROM WHERE * Impiegato [Stipendio annuo] >=45000

(19)

QUERY 11 - Ordinamento della risposta

Tutti gli impiegati che guadagnano almeno 45000 euro, ordinando la risposta in base allo Stipendio

(20)

QUERY 12 - Ordinamento doppio

Tutti gli impiegati che guadagnano almeno 45000 euro, ordinando la risposta in base allo Stipendio e, a parità di stipendio, rispetto al

cognome SELECT FROM WHERE * Impiegato [Stipendio annuo] >=45000

(21)

QUERY 13 - Ordinamento decrescente

Tutti gli impiegati che guadagnano almeno 45000 euro, ordinando in modo decrescente la tabella in base allo stipendio

(22)

QUERY 14 - Ordinamento doppio

Gli impiegati che guadagnano almeno 45000 euro, ordinando la

risposta in base a Stipendio decrescente e, a parità di stipendio, rispetto a cognome crescente

(23)

QUERY 15 - Ricerca parziale (LIKE)

Tutti gli impiegati con il cognome che comincia con la lettera “B”

SELECT FROM WHERE * Impiegato Cognome LIKE ‘B*’;

(24)

Condizioni di ricerca parziale (LIKE)

• Seleziona le tuple in cui il valore è recuperato con le wildcards (l’operatore LIKE non fa differenza tra maiuscolo e minuscolo).

(25)

QUERY 16 - Ricerca Parziale (LIKE)

Tutti gli impiegati con il cognome che presenta una “i” in seconda posizione

*

Impiegato

(26)

QUERY 17 - Ricerca Parziale (LIKE)

Tutti gli impiegati con il cognome che presenta una “i” in seconda posizione e una “i” in ultima posizione SELECT FROM WHERE * Impiegato

(27)

QUERY 18 - Ricerca Parziale (LIKE)

Tutti gli impiegati con il cognome che presenta una “i” in ultima posizione

*

Impiegato

(28)

QUERY 19 - Ricerca Parziale (LIKE)

Tutti gli impiegati con il cognome che presenta almeno una “r” in una qualsiasi posizione SELECT FROM WHERE * Impiegato Cognome LIKE ‘*R*’;

(29)

QUERY 20 - Ricerca parziale (LIKE)

Tutti gli impiegati con il cognome lungo esattamente 5 caratteri

SELECT FROM WHERE * Impiegato Cognome LIKE “?????”;

(30)

Fondamenti di

Informatica

Prof. Fenza Giuseppe

DBMS – Linguaggio SQL

Operatori di Aggregazione

(31)

Tabelle esempio: Impiegato/Dipartimento

(32)

Operatori di Aggregazione

• Una Query normalmente è applicata all’intera tabella, ma ne

analizza una riga per volta, estraendo tutte quelle che verificano le condizioni della WHERE

–

Es: Si vuole conoscere il numero di matricola di tutti gli studenti che abitano a Napoli.

•

Se ci sono 100 studenti di Napoli, la risposta sarà una tabella con 100 righe

• Una Query aggregata estrae un risultato che dipende da un

insieme di righe. Il risultato è un valore che non è posseduto da una particolare riga

–

Es: Si vuole il numero di studenti che abitano a Napoli

(33)

Operatori di Aggregazione

• Le Queries con operatori aggregati sono delle estensioni delle

normali interrogazioni

• Un’interrogazione con un operatore aggregato viene eseguita in 2

momenti successivi:

1. (Selezione di righe) Viene prima eseguita la query considerando

le parti FROM e WHERE, procedendo una riga alla volta

selezionando quelle che rispondono alle condizioni della clausola WHERE.

2. (Applicazione della funzione sulle righe selezionate) Sulla tabella risultante viene poi applicato l’operatore aggregato

(34)

QUERY 21 - COUNT

Numero di Dipendenti del Dipartimento

Produzione

SELECT FROM WHERE Count (*) Impiegato Dipartimento=“Produzione";

(35)

QUERY 21bis - COUNT

Numero di Dipendenti del Dipartimento Produzione

(metodo alternativo)

SELECT FROM WHERE Count ([Cognome]) Impiegato Dipartimento=“Produzione";

(36)

QUERY 22 - COUNT

Numero di tutti i Dipendenti

SELECT FROM

Count (*)

(37)

Altri Operatori di Aggregazione

• FUNZIONI di aggregazione su un insieme di tuple

–

COUNT

–

SUM

–

AVG

•

Ammettono come argomento solo espressioni numeriche o di tempo

–

MAX

–

MIN

•

Richiedono che sull’espressione sia definito un ordinamento (per cui si possono applicare anche a Caratteri, ad esempio)

(38)

QUERY 23 - SUM

Somma degli stipendi dei dipendenti del Dipartimento

Amministrazione

Sum ([Stipendio annuo]) AS [Stipendio Totale] Impiegato

(39)

QUERY 24 - MIN, MAX, AVG

Stipendi minimo, massimo e medio fra quelli di tutti i

Dipendenti

SELECT FROM

Min ([Stipendio annuo]), Max ([Stipendio annuo]), Avg ([Stipendio annuo])

(40)

QUERY 25 – MAX su testo

Cognome del Dipendente che, in ordine alfabetico, è in

ultima posizione

SELECT FROM

Max (Cognome)

(41)

Fondamenti di

Informatica

Prof. Fenza Giuseppe

DBMS – Linguaggio SQL

Raggruppamenti

(42)

Raggruppamenti

• Gli operatori aggregati visti fino ad ora forniscono

–

un risultato “complessivo” (SUM, COUNT, …) e

–

operano su tutte le righe prodotte da un’interrogazione

• Altre volte è, invece, necessario applicare un operatore aggregato

–

a sottoinsiemi di righe raggruppate su valori comuni

–

Ad esempio: se per ogni Dipartimento si volesse conoscere il

totale degli stipendi pagati bisognerebbe

•

raggruppare i dipendenti per dipartimento

•

e applicare l’operatore SUM ad ognuno di questi gruppi

•

È cioè necessario suddividere le righe in sottoinsiemi omogenei

(43)

Raggruppamenti

• Basta aggiungere in coda alla SELECT-FROM-WHERE la clausola GROUP BY

GROUP BY A₁,…A_k

HAVING Y

- A₁,…,A_kè un insieme di campi

Un sottoinsieme di A₁,…,A_kpuò comparire nella clausola SELECT (che non può contenere campi diversi da A₁,…,A_k)

- Y è una clausola opzionale che si applica ai gruppi (permette di selezionarne solo un sottoinsieme)

La query raggrupperà tutte le righe che presentano gli stessi valori sui campi A₁,…A_k

(44)

QUERY 26 - GROUP BY

Per ogni Dipartimento si vuole conoscere il totale degli

stipendi pagati

SELECT FROM

GROUP BY Dipartimento;

Dipartimento, Sum([Stipendio annuo]) AS Totale

(45)

Come “funziona” una query con GROUP BY

SELECT FROM

Dipartimento, [Stipendio annuo] Impiegato

(46)

Come “funziona” una query con GROUP BY

Nell’esempio il valore in base a cui raggruppare è il nome del Dipartimento

2) Le righe vengono poi raggruppate mettendo assieme tutte quelle con uguale valore degli attributi che compaiono nella clausola GROUP BY

(47)

Come “funziona” una query con GROUP BY

Nel nostro esempio, l’operatore è SUM e si applica ad ognuno dei 4 sottoinsiemi individuati

3) Viene applicato l’operatore aggregato ad ognuno dei sottoinsiemi individuati

(48)

GROUP BY - HAVING

• La lista dei campi della clausola SELECT deve essere un

sottoinsieme di quella della clausola GROUP BY.

• Se siamo interessati solamente ad alcuni dei sottoinsiemi

utilizziamo il predicato HAVING

–

Nota: HAVING può anche essere usato senza GROUP BY. In questo caso la tabella si considera come un unico grande

raggruppamento.

–

HAVING presenta espressioni che contengono campi solo se questi sono argomenti di operatori aggregati

(49)

QUERY 27 - HAVING

Nome e spesa complessiva dei Dipartimenti che spendono più di 100.000 euro in stipendi

SELECT FROM

GROUP BY HAVING

Dipartimento, Sum ([Stipendio annuo]) Impiegato

Dipartimento

(50)

QUERY 28 - HAVING

Tutti i Dipartimenti in cui la media degli stipendi dei dipendenti che lavorano nell’ufficio 20 è superiore a 25.000 euro

SELECT FROM WHERE GROUP BY HAVING Dipartimento Impiegato Ufficio = 20 Dipartimento Avg([Stipendio annuo])>25000;