Orario lezioni

http://delorenzo.inginf.units.it/

Modalità esame

Progetto + Orale

Progetto va consegnato 3 giorni lavorativi prima dell’appello Se l’esame è mercoledì, venerdì è troppo tardi per inviare il progetto!

Gestione delle informazioni

L’essere umano genera e gestisce tante informazioni:

• idee informali
• linguaggio naturale (scritto o parlato, in lingue diverse)
• disegni, grafici, schemi
• numeri
• codici

Gestione delle informazioni

... salvate in tanti modi diversi

• memoria
• carta
• pietra
• scritta sul muro
• elettronica

Gestione delle informazioni

Anche le organizzazioni generano informazioni:

• utenze telefoniche
• conti correnti
• studenti iscritti ad un corso di laurea
• quotazioni di azioni

Codifica delle informazioni

Era informatica

Le informazioni vanno codificate

• si aggiungono elementi artificiali
• primo esempio: anagrafe
• nome e cognome
• indirizzo
• codice fiscale

Informazione vs Dato

Informazione: notizia, dato o elemento che consente di avere conoscenza più o meno esatta di fatti, situazioni, modi di essere

Dato: elemento di informazione costituito da simboli che debbono essere elaborati

Cartelli stradali in Finlandia

Lun - Ven

Sabato

Festivi

Numeri

Come codifico i numeri?

• Numeri naturali: facile, in binario
• Numeri interi: devo decidere come rappresentare il segno
• Numeri razionali?

10010011110011001111

Dati e Applicazioni

• I dati possono variare nel tempo (es: conto corrente)
• Le modalità con cui i dati sono rappresentati sono di solito stabili
• Le operazioni sui dati variano spesso (es: ricerche)

separare i dati dalle applicazioni che operano su essi

Data Base

Genericamente:

Collezione di dati, utilizzati per rappresentare le informazioni di interesse per una o più applicazioni di una organizzazione.

• schede perforate
• file CSV
• foglio di calcolo
• file XML
• Access

DataBase Management System

Software in grado di gestire collezioni di dati che siano:

• grandi: di dimensioni (molto) maggiori della memoria centrale
• persistenti: con un periodo di vita indipendente dalla singole esecuzioni dei programmi che le utilizzano
• condivise: utilizzate da applicazioni diverse

Base di Dati

Genericamente

Collezione di dati, utilizzati per rappresentare le informazioni di interesse per una o più applicazioni di una organizzazione

Per noi

Collezione di dati gestita da un DBMS

DataBase managment System

Un DBMS deve garantire:

• affidabilità: resistenza a malfunzionamenti hardware e software
• privatezza: con una disciplina e un controllo degli accessi
• efficienza: utilizzando al meglio le risorse di spazio e etempo del sistema
• efficacia: rendendo produttive le attività dei suoi utilizzatori

Condivisione

• più dipartimenti sono interessati agli stessi dati
• una base di dati è una risorsa integrata, condivisa

Condivisione

• L’integrazione e la condivisione permettono di
  • ridurre la ridondanza (evitando ripetizioni)
  • ridurre possibilità di incoerenza (o inconsistenza) fra i dati.
• Poiché la condivisione non è mai completa (o comunque non opportuna) i DBMS prevedono meccanismi per
  • privatezza dei dati
  • limitazione all’accesso (autorizzazioni).
• La condivisione richiede coordinamento degli accessi: controllo della concorrenza.

Efficienza

• Si misura in termini di tempo di esecuzione e spazio di memoria (principale e secondaria)
• I DBMS non sono necessariamente più efficienti dei file system
• L’efficienza è il risultato della qualità del DBMS e delle applicazioni che lo utilizzano

DBMS vs FS

File System

Descrizione dei dati contenuta nell'applicazione

File System

Descrizione dei dati contenuta nell'applicazione

File System

Descrizione dei dati contenuta nell'applicazione

DBMS e Descrizione dei Dati

• Il DBMS sa come persistere i dati, per l’applicazione è un atto di fede
• I dati sono INDIPENDENTI dalla forma fisica
• I programmi parlano con il DBMS per accedere ai dati

Modello Concettuale

Analisi del problema

Modello astratto

Non dipende dallo strumento utilizzato

Modello Logico

Come rappresentare i dati individuati con il modello concettuale

• Livello intermedio tra utente e implementazione
• sottintende una specifica rappresentazione dei dati (tabelle, alberi, grafi, oggetti, …)

DataBase System

• Software
  • DBMS: interposto tra il DB e l’utente
  • Utility di supporto (sviluppo, backup)
• Utenti
  • Progettista
  • Sviluppatore
  • Amministratore
  • Utente finale

DataBase System

• Schemi (struttura dei dati)
• Dati
  • come vengono salvati
  • condivisione
  • concorrenza
  • ridondanza
• Hardware

Base di Dati

• Genericamente: collezione di dati, utilizzati per rappresentare le informazioni di interesse per una o più applicazioni di una organizzazione.
• Per noi: collezione di dati gestita da un DBMS
• Per noi - 2: collezione di dati persistenti usata dal sistema di una azienda e gestita da un DBMS

Riassunto

• Data base
  • Dati, solo dati, niente altro che i dati
• Data Base Managmnet System
  • Software che gestisce i dati
  • Diversi vendor: IBM, Oracle, Microsoft
• Data Base System
  • DB + DBMS

Vantaggi e Svantaggi

• Vantaggi
  • Dati sono risorsa in comune
  • DB fornisce un modello unificato del business
  • Controllo centralizzato dei dati, quindi standardizzazione ed economie di scala
  • Riduzione ridondanza ed inconsistenza
  • Indipendenza dei dati
• Svantaggi
  • Costo e complessità
  • Servizi ridondanti/non necessari

Vero beneficio

Indipendenza dei dati!

Vero beneficio

Nei vecchi sistemi il modo in cui venivano organizzati i dati e le tecniche per accedervi facevano parte della logica e del codice del programma.

Schema ed Istanza

In ogni base di dati esistono:

• Schema:
  • invariante nel tempo
  • descrive la struttura
  • es: intestazione tabelle
• Istanza:
  • i valori attuali
  • possono cambiare
  • es: contenuto delle tabelle

Schemi

Schemi concettuali

Permettono di rappresentare i dati in modo indipendente da ogni sistema:

• cercando di descrivere i concetti del mondo reale
• sono utilizzati nelle fasi preliminare di progettazione

Modello più diffuso: Entity-Relationship

Schermi interni (o fisici)

Rappresentazione dello schema logico per mezzo di strutture di memorizzazione

• file CSV
• file XML
• file binari

Schemi logici

Come è organizzato il DB. Diverse soluzioni:

• gerarchico
• reticolare
• relazionale
• ad oggetti

Indipendenza

Lo schema logico è INDIPENDENTE da quello fisico

Es: una tabella è utilizzata sempre allo stesso modo qualunque sia la sua realizzazione fisica (che può variare nel tempo)

Indipendenza

Progettista DB ≠ Sviluppatore SW

Vista

L'amministratore del DB può modificare la struttura interna dei dati senza toccarne la visibilità esterna

IMMUNITÀ DELLE APPLICAZIONI A MODIFICHE DI STRUTTURA

Vista

Vista

Schema Esterno

== VISTA

• Descrive parte della base di dati di un modello logico
• NON è una copia dei dati

Architettura ANSi/SPARC

Schemi Logici

Schemi Logici Gerarchici

Schemi Logici Gerarchici

Problemi:

• accesso sequenziale: per arrivare al figlio devo attraversare tutti i nodi
• modifica parziale complicata
• cancellazione gerarchica
• stretto legame tra programma e struttura del database
• ridondanza

Schemi Logici Gerarchici (Ridondanza)

Schemi Logici Reticolari

• COBOL, 1970
• nodi collegati da PUNTATORI
• navigazione bi-direzionale

Schemi Logici Reticolari

Schemi Logici Relazionali

Codd, 1980

Liberarsi dei puntatori fisici

• i dati sono organizzati in tabelle di valori
• le operazioni vengono eseguite sulle tabelle
• i risultati delle operazioni sono tabelle
• i riferimenti tra dati in strutture (tabelle) diverse sono rappresentati con valori

Elementi di un DBR

Tabelle: organizzazione rettangolare di dati

• Record (righe) e campi (colonne) e domini dei dati
• I campi definiscono univocamente il tipo dei dati (dominio)
• I campi hanno un nome ed un ordine, le righe no
• Esistono tabelle vuote

Elementi di un DBR

Chiavi primarie

• Una (o più) colonne che identificano UNIVOCAMENTE il record
• Non possono essere duplicate
• Una tabella in cui ogni riga è diversa dalle altre è detta RELAZIONE

Elementi di un DBR

Relazioni

• Non esistono relazioni padre-figlio
• Le relazioni sono rappresentate da DATI COMUNI manipolabili

Chiavi Esterne (Secondarie, Foreign Key)

• Una colonna in una tabella il cui valore corrisponde ad una chiave primaria
• Sono fondamentali nella creazione delle relazioni

Dodici regole di Codd

Dodici regole di Codd

1 - Informazioni

Tutte le informazioni in un DBR sono rappresentate esplicitamente da valori in tabelle (DEFINIZIONE)

Dodici regole di Codd

2 - ACCESSO GARANTITO

Ciascun valore deve essere raggiunto univocamente da un nome di tabella, chiave primaria e nome di colonna (CHIAVI PRIMARIE)

Dodici regole di Codd

3 - VALORI NULL

Sono supportati per rappresentare informazioni mancanti indipendentemente dal tipo di dato

Dodici regole di Codd

4 - SYSTEM TABLE

Un data base relazionale deve essere strutturato logicamente come i dati e gestibile con lo stesso linguaggio

Dodici regole di Codd

5 - LINGUAGGIO DI INTERROGAZIONE STANDARD

Un DBR può supportare diversi linguaggi, ma deve supportare un linguaggio “English like” dove sia possibile (DEFINIZIONE DI SQL):

• Definire dati
• Definire viste
• Manipolare dati
• Gestire l’integrità

Dodici regole di Codd

6 - VISTE MODIFICABILI

Le viste che sono modificabili teoricamente dall’utente lo devono essere anche dal sistema (cruciale per campi calcolati);

Dodici regole di Codd

6 - VISTE MODIFICABILI

Affinché una vista sia modificabile, il DBMS deve essere in grado di tracciare ciascuna colonna e ciascuna riga UNIVOCAMENTE fino alle tabelle origine

Viste Modificabili

Viste Modificabili

Dodici regole di Codd

7 - INSERIMENTO E UPDATE DA LINGUAGGIO

Inserire e aggiornare devono avere la stessa logica “a righe” dell’estrazione (SET ORIENTED)

Dodici regole di Codd

8 - INDIPENDENZA FISICA DEI DATI

I programmi applicativi non devono sentire alcuna modifica fatta sul metodo e la locazione fisica dei dati

Dodici regole di Codd

9 - INDIPENDENZA LOGICA DEI DATI

Le modifiche al livello logico non devono richiedere cambiamenti non giustificati alle applicazioni che utilizzano il database (VISTE)

Dodici regole di Codd

10 - INTEGRITÀ

Vincoli di integrità devono essere implementabili sul motore (cruciale)

Dodici regole di Codd

11 - INDIPENDENZA DI LOCALIZZAZIONE

La distribuzione di porzioni del database su una o più allocazione fisiche o geografiche deve essere invisibile agli utenti del sistema

Dodici regole di Codd

12 - DEVE PREVENIRE ACCESSI NON DESIDERATI:

Garantisce l’impossibilità di bypassare le regole di integrità

Riassunto: DB Relazionale

Data Base dove tutti i dati visibili all’utente sono organizzati strettamente in tabelle di valori, e dove tutte le operazioni vengono eseguite su tabelle e danno come risultato tabelle.

Relazione

Relation

Relazione matematica (teoria degli insiemi)

Relationship

Associazione nel modello Entity-Relationship

Relazione Matematica

• $D_1, \dots, D_n$ ($n$ insiemi anche distinti) sono i domini
• prodotto cartesiano $D_1 \times \dots \times D_n$
  • insieme di tutte le $n$-uple ($d_1, \dots, d_n$) tali che $d_1 \in D_1, \dots, d_n \in D_n$
• relazione matematica su $D_1, \dots, D_n$:
  • un sottoinsieme di $D_1 \times \dots \times D_n$

Relazione Matematica (esempio)

Relazione Matematica (esempio)

Relazione Matematica (proprietà)

• una relazione matematica è un insieme di $n$-uple ordinate:
  • $ (d_1, \dots, d_n ) \mid d_1 \in D_1, \dots, d_n \in D_n $
• una relazione è un insieme:
  • non c’è ordinamento tra le $n$-uple
  • le $n$-uple sono distinte
  • ogni $n$-upla è ordinata: $i$-esimo valore proviene dall’$i$-esimo dominio

Relazione Matematica (esempio)

• ciascuno dei domini ha due ruoli diversi, distinguibili attraverso la posizione
• la struttura è posizionale

Struttura NON Posizionale

A ciascun dominio si associa un nome (attributo), che ne descrive il "ruolo"

Tabelle e Relazioni

• Una tabella è una relazione se:
  • i valori di ogni colonna sono omogenei
  • le righe sono diverse fra di loro
  • le intestazioni delle colonne sono diverse tra di loro
• In una tabella che rappresenta una relazione:
  • l’ordinamento tra le righe è irrilevante
  • l’ordinamento tra le colonne è irrilevante

Relazione

• Relation: relazione matematica (teoria degli insiemi)
• Relationship: rappresenta una associazione nel modello Entity-Relationship

Prima

Modello basato su VALORI

I riferimento fra dati in relazioni diverse sono rappresentati per mezzo di valori dei domini che compaiono nelle $n$-uple

DB Relazionale

Struttura basata su valori

Vantaggi

• indipendenza dalla struttura fisiche (si potrebbe avere anche con puntatori HL)
• si rappresenta solo ciò che rilevante dal punto di vista dell’applicazione
• utente finale vede stessi dati del programmatore
• portabilità dei dati tra sistemi
• puntatori direzionali

Relazione su singoli attributi

Strutture Nidificate

Strutture Nidificate

I valori devono essere semplici

Relazioni e strutture nidificate

Siamo stai bravi?

Abbiamo rappresentato tutti gli aspetti delle ricevute?

Dipende da cosa ci interessa:

• l’ordine delle righe è rilevante?
• possono esistere linee ripetute?
• Al bar, al servizio al tavolo, ad un gruppo:
  • Cliente: “Una birra”
  • Cameriere: “Se volete altre birre ditelo subito altrimenti non posso aggiungerle!”
• Sono possibili rappresentazioni diverse

Relazioni e strutture nidificate

Informazioni Incomplete

Ovvero: gestire i valori NULL

Ogni elemento in una tabella può essere o un valore del dominio oppure il valore nullo NULL

Informazione incompleta

Il modello relazionale impone una struttura rigida

• Le informazioni sono rappresentate per mezzo di $n$-uple
• Solo alcuni formati di $n$-upla sono ammessi: quelli che corrispondono agli schemi di relazione
• I dati disponibili possono non corrispondere al formato previsto

Esempio

NULL: come fare?

E se usassi il numero 0?

Non conviene, anche se spesso si fa, usare valori del dominio (0, stringa nulla, 99, …)

• potrebbero non esistere valori “non utilizzati”
• valori “non utilizzati” potrebbero diventare significativi
• in fase di utilizzo sarebbe necessario tener conto del significato di questi valori

Tipi di valore NULL

(Almeno) 3 casi differenti

• valore sconosciuto (quanti anni ha?)
• valore inesistente (non ha il secondo nome)
• valore non applicabile (anagrafica unica studenti/professori, i professori hanno ufficio)

I DBMS non distinguono i tipi di valore nullo

Troppi valori NULL

Vincoli di integrità

Esistono istanze di basi di dati che, pur sintatticamente corrette, non rappresentano informazioni possibili per l’applicazione di interesse.

DB Errato

Vincoli di Integrità

Proprietà che deve essere soddisfatta dalle istanza che rappresentano informazioni corrette per l’applicazione

Un vincolo è una funzione booleana (un predicato): associa ad ogni istanza il valore vero o falso

Vincoli di Integrità, perché?

• Descrizione più accurata della realtà
• contributo alla “qualità dei dati”
• utili nella progettazione
• usati dai DBMS nelle interrogazioni

Vincoli di integrità: nota

Alcuni vincoli (ma non tutti) sono supportati dai DBMS

• Possiamo specificare tali vincoli e il DBMS ne impedisce violazione
• Se non supportati, la responsabilità della verifica è dell’utente/programmatore

Tipi di Vincoli

• vincoli intrarelazionali
  • vincoli su valori (o di dominio)
  • vincoli di $n$-upla
• vincoli interrelazionali

Vincoli di valore

Voto ≥ 18 && Voto ≤ 30

Vincoli di $n$-upla

Lode solo se Voto == 30

Vincoli di $n$-upla

Lordo = (Ritenute + Netto)

Vincoli interrelazionali

Chiavi: identificare le $n$-uple

Identificare le $n$-uple

• non ci sono due ennuple con lo stesso valore sull’attributo Matricola
• non ci sono due ennuple uguali su tutti e tre gli attributi Cognome, Nome e Data di Nascita

CHIAVE

Insieme di attributi che identificano le $n$-uple di una relazione

CHIAVE

Formalmente

• Un insieme di $K$ attributi è superchiave per $r$ se non contiene due $n$-uple distinte $t_1$ e $t_2$ con $t_1^K = t_2^K$
• $K$ è chiave per $r$ se è una superchiave minimale per $r$
• superchiave minimale = non contiene un’altra superchiave

Una chiave

Matricola è una chiave:

• è superchiave
• contiene un solo attributo $r$ quindi è minimale

Un'altra chiave

Cognome, nome, nascita è un'altra chiave:

• è superchiave
• è minimale

Un'altra chiave

Non ci sono $n$-uple uguali su Cognome e Corso:

• Cognome e Corso formano una chiave
• È sempre vero o è un caso?

Chiavi

Esistenza

• Una relazione non può contenere $n$-uple distinte ma uguali
• Ogni relazione ha come superchiave l’insieme degli attributi su cui è definita
• quindi ha (almeno) una chiave

Chiavi

Importanza

• l’esistenza delle chiavi garantisce l’accessibilità a ciascun dato della base di dati
• le chiavi permettono di correlare i dati in relazioni diverse
  • il modello relazionale è basato su valori

Chiavi e valori NULL

• In presenza di valori nulli, i valori della chiave non permetteranno
  • di identificare le $n$-uple
  • di realizzare facilmente i riferimenti da altre relazioni
• la presenza di valori nulli nelle chiavi deve essere limitata

Chiavi e valori NULL

Chiave primaria

• Chiave su cui non sono ammessi valori NULL
• Notazione: sottolineatura

Integrità referenziale

• informazioni in relazioni diverse sono correlate attraverso valori comuni
• in particolare, valori delle chiavi (primarie)
• le correlazioni debbono essere "coerenti"

Integrità referenziale

Integrità referenziale

Integrità referenziale

Vincolo di Integrità Referenziale

Un vincolo di integrità referenziale (“foreing key”) fra attributi $X$ di una relazione $r_1$ e un’altra relazione $r_2$ impone ai valori su $X$ in $r_1$ di comparire come valori della chiave primaria di $r_2$

Vincolo di Integrità Referenziale

Vincoli di integrità referenziale fra

• attributo Vigile della relazione Infrazioni e la relazione Vigili
• attributi Prov e Numero di Infrazioni e la relazione Auto

Vincoli su più attributi

Integrità referenziale e valori NULL

Azioni compensative

Viene eliminata una $n$-upla causando una violazione

• Comportamento “standard”:
  • Rifiuto dell’operazione
• Azioni compensative
  • Eliminazione in cascata
  • Introduzione di valori nulli

Eliminazione in cascata

Introduzione di valori NULL

Vincoli multipli su più attributi

SQL

Benefici SQL

• Indipendenza dai venditori di HW e SW
• Portabilità attraverso varia piattaforme HW
• Coperto da standard internazionali SQL1, SQL2 e SQL3
• Strategico per IBM, Oracle, Microsoft, …
• Linguaggio per data base relazionali (unico)
• Strutturato ad alto livello (English-like)

Benefici SQL

• Linguaggio programmazione (Statico/Dinamico/API)
• In grado di fornire viste diverse del data base
• Linguaggio completo (IF, triggers, …) con T-SQL e PL-SQL
• Definizione dinamica dei dati
• Client/Server

SQL Standard?

In realtà ogni motore fa un po’ come vuole

http://troels.arvin.dk/db/rdbms/

Portabilità: davvero?

Non si può fare tutto

• codici di errore non standard
• tipi di dati non sempre supportati
• tabelle di sistema non sono uguali
• definisce solo linguaggio statico, non dinamico
• sorting

SQL basics

Data Definition Language (DDL)

CREATE/DROP/ALTER TABLE/VIEW/INDEX

Data Manipulation Language (DML)

SELECT - INSERT - DELETE - UPDATE

SQL basics

Data Control Language (DCL)

GRANT - REVOKE

Transaction Control Language (TCL o T-SQL)

COMMIT - ROLLBACK

Programming Language (PL)

DECLARE - OPEN - FETCH - CLOSE

Elencare i database

SHOW DATABASES;

Ritorna l’elenco dei Database presenti nel DBMS

I comandi possono occupare anche più righe e terminano con il ;

Creare un DataBase

CREATE DATABASE nomeDataBase;

Crea un nuovo DataBase con il nome specificato e lo rende accessibile all’utente ROOT.

CREATE DATABASE IF NOT EXISTS nomeDataBase;

Crea il DB solo se non esiste già

Eliminare un DataBase

DROP DATABASE [IF EXISTS] nomeDataBase;

usiamo [...] per indicare le parti opzionali dei comandi.

Selezionare un DataBase

USE nomeDataBase;

Tutti i comandi ora saranno riferiti a questo DB.

Definizione di dati

Istruzione CREATE TABLE;

• definisce uno schema di relazione e ne crea un’istanza vuota

• specifica attributi, domini e vincoli

  CREATE TABLE [IF NOT EXISTS] nomeTabella(
  	tipo nomeAttributo1,
  	tipo attributo2,
  	...
  	tipo attributoN
  )
  

Domini - Numeri Interi

INT(N): suggeriamo al motore di usare N caratteri per mostrare il dato; es: INT(11)

Domini - Numeri razionali

Virgola mobile

• float - 4 bytes
• double - 8 bytes

Virgola fissa

• numeric(i,n) salva esattamente n cifre decimali
• decimal(i,n) salva almeno n cifre decimali

Domini - Testo

Salvati in tabella

Domini - Generico

SalvatAGGIO DEDICATO

Domini - Testo

SalvatAGGIO DEDICATO

Domini - Tempo

• YEAR - anno nel formato YYYY
• DATE - data nel formato YYYY-MM-DD
• TIME - tempo nel formato hh:mm:ss
• DATETIME - tempo nel formato YYYY-MM-DD hh:mm:ss
• TIMESTAMP - come DATETIME, ma si aggiorna da solo

Domini - Spazio

… e molti altri

Domini - Stringhe

Sto salvando testo o sequenze di byte?

• testo: devo convertire la stringa in sequenza di byte (charset)
• sequenza e basta: posso salvarla così com’è

Domini - Stringhe

Dimensione fissa o variabile?

• fissa: devo indicare una dimensione (max 255)
• variabile: occupa lunghezza + 1; posso indicare una lunghezza massima

Domini - Stringhe

Domini - Stringhe

Dove salvo il dato?

• nella tabella: più rapido accedere al dato per interrogazioni
• storage dedicato: anche se ho molti dati la tabella resta piccola

Studenti ed esami

Creare una tabella

CREATE TABLE Studenti(
    matricola int(11),
    cognome varchar(45),
    nome varchar(45)
);

Cancellare una tabella

DROP TABLE [IF EXISTS] nomeTabella;

… intuitivo

DROP TABLE [IF EXISTS] nomeTabella1;
	nomeTabella2,
	nomeTabella3,
	...;

Vincoli

Posso definire dei vincoli:

• PRIMARY KEY - chiave primaria (una sola, implica NOT NULL)
• NOT NULL
• UNIQUE - definisce chiavi
• CHECK - vedremo più avanti

Vincoli - Chiave primaria

CREATE TABLE Studenti(
    matricola int(11) PRIMARY KEY,
    cognome varchar(45),
    nome varchar(45)
);

Vincoli - Chiave primaria

CREATE TABLE Studenti(
    matricola int(11),
    cognome varchar(45),
    nome varchar(45),
    PRIMARY KEY (matricola)
);

Vincoli - proibire i NULL

CREATE TABLE Studenti(
    matricola int(11) PRIMARY KEY,
    cognome varchar(45) NOT NULL,
    nome varchar(45) NOT NULL
);

Vincoli - proibire i NULL

CREATE TABLE Corsi(
    codice int(11) PRIMARY KEY,
    titolo varchar(45) NOT NULL,
    docente varchar(45)
);

Vincoli - chiavi composte

CREATE TABLE Esami(
    studente int(11) PRIMARY KEY,
    voto smallint NOT NULL,
    lode bool,
    corso int(11) PRIMARY KEY
);

Vincoli - chiavi composte

CREATE TABLE Esami(
    studente int(11),
    voto smallint NOT NULL,
    lode bool,
    corso int(11),
    PRIMARY KEY (studente, corso)
);

Not NULL + unique = Primary key

CREATE TABLE Corsi(
	codice int(11) NOT NULL UNIQUE,
	titolo varchar(45) NOT NULL,
	docente varchar(45)

);

CREATE TABLE Esami(
	studente int(11) NOT NULL UNIQUE,
	voto smallint NOT NULL,
	lode bool,
	corso int(11) NOT NULL UNIQUE
);

UNIQUE e NULL MULTIPLI

In general, a unique constraint is violated when there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.

PostgreSQL Manual

UNIQUE su più colonne

CREATE TABLE nomeTabella (
	id int(11) PRIMARY KEY,
	campo1 int(19),
	campo2 int(12),
	CONSTRAINT [nome] UNIQUE(campo1, campo2)
);

Auto Increment

• il motore si occupa di incrementare il contatore numerico
• identifico in modo chiaro una $n$-upla

• ottimo come chiave primaria!

  CREATE TABLE Studenti(
  	matricola int(11) PRIMARY KEY AUTO_INCREMENT,
  	cognome varchar(45),
  	nome varchar(45)
  );
  

CHECK

Serve per specificare vincoli complessi

Netto = Lordo - Trattenute

• non supportato in MySql
• MySql IGNORA il comando dato alla creazione della tabella

AUTO_INCREMENT: dettagli

cosa succede se cancello una riga?

• non riciclo!
• successivo = inserito automaticamente +1

AUTO_INCREMENT: dettagli

cosa succede se imposto un valore?

• se non è duplicato viene accettato
• successivo = inserito manualmente +1

AUTO_INCREMENT: dettagli

cosa succede se modifico un valore?

• se non è duplicato viene accettato
• successivo = inserito automaticamente +1

Valori predefiniti

CREATE TABLE nome(
	nomeAttributo tipo DEFAULT valore
);

Valori predefiniti

CREATE TABLE Corsi(
	codice int(11) PRIMARY KEY,
	titolo varchar(45) NOT NULL DEFAULT "nuovo",
	docente varchar(45)
);

Commenti

CREATE TABLE nome(
	nomeAttributo tipo COMMENT "commento"
);

Commenti

CREATE TABLE Corsi(
	codice int(11) PRIMARY KEY,
	titolo varchar(45) NOT NULL
	COMMENT "Titolo del corso",
	docente varchar(45)
);

Chiavi primarie e null

Modulo, Versione e Tipo sono una PK?

• identificano $n$-upla
• non contengono altre superchiavi
• non nulle

Chiavi primarie e null

• NULL = assenza di informazioni
• '' = informazione nota, pari a VUOTO
• NULL è uguale a NULL?

Chiavi primarie e null

Vincoli Interrelazionali

• FOREIGN KEY e REFERENCES e permettono di definire vincoli di integrità referenziale
• di nuovo due sintassi
  • per singoli attributi (non in MySql)
  • su più attributi
• è possibile definire azioni compensative

FOREIGN KEY

colonne che sono FK

REFERENCES

colonne nella relazione (tabella) esterna

Studenti ed esami

Vincoli Interrelazionali

CREATE TABLE Esami(
	studente int(11),
	voto smallint NOT NULL,
	lode bool,
	corso int(11),
	PRIMARY KEY (studente, corso),
	FOREIGN KEY (studente) REFERENCES Studenti(matricola)
);

Definizione compatta

Non funziona ovunque (MySql)

CREATE TABLE Esami(
	studente int(11) REFERENCES Studenti(matricola),
	voto smallint NOT NULL,
	lode bool,
	corso int(11),
	PRIMARY KEY (studente, corso)
);

Vincoli Interrelazionali

CREATE TABLE Esami(
	studente int(11),
	voto smallint NOT NULL,
	lode bool,
	corso int(11),
	PRIMARY KEY (studente, corso),
	FOREIGN KEY (studente) REFERENCES Studenti(matricola)
	FOREIGN KEY (corso) REFERENCES Corsi(codice)
);

Vincoli Interrelazionali con nome

CREATE TABLE Esami(
	studente int(11),
	voto smallint NOT NULL,
	lode bool,
	corso int(11),
	PRIMARY KEY (studente, corso),
	CONSTRAINT FK_Studente FOREIGN KEY (studente) REFERENCES Studenti(matricola)
	CONSTRAINT FK_Corso FOREIGN KEY (corso) REFERENCES Corsi(codice)
);

Disattivare i vincoli interrelazionali

• sto caricando i dati: ordine importante, altrimenti errore
• voglio disattivare temporaneamente i vincoli
• disattivazione
  • SET foreign_key_checks = 0
• riattivazione
  • SET foreign_key_checks = 1

Integrità referenziale

Integrità referenziale

CREATE TABLE Vigili(
    matricola int(11) PRIMARY KEY AUTO_INCREMENT,
    cognome varchar(45) NOT NULL,
    nome varchar(45) NOT NULL
);

Integrità referenziale

CREATE TABLE Automobile(
    prov char(2),
    targa char(6),
    cognone varchar(45) NOT NULL,
    nome varchar(45) NOT NULL,
    PRIMARY KEY (prov, targa)
);

Integrità referenziale

CREATE TABLE Infrazioni(
    codice int(11) PRIMARY KEY AUTO_INCREMENT,
    data datetime,
    vigile int(11),
    prov char(2),
    targa char(6),
    FOREIGN KEY (vigile) REFERENCES Vigili(matricola),
    FOREIGN KEY (prov, targa) 
    REFERENCES Automobile(prov, targa)
);

Cancellazione

CREATE TABLE Infrazioni(
	codice int(11) PRIMARY KEY AUTO_INCREMENT,
	data datetime NOT NULL,
	vigile int(11),
	prov char(2) NOT NULL,
	targa char(6) NOT NULL,
	FOREIGN KEY (vigile) REFERENCES Vigili(matricola)
	ON DELETE SET NULL ON UPDATE CASCADE,
	FOREIGN KEY (prov, targa) REFERENCES
	Automobile(prov, targa)
);

Cambiare lo schema

Cambiare lo schema

• DROP tabella
• ricreare tabella
• … perdo tutti i dati

Modificare tabelle

ALTER TABLE nomeTabella
	azione1
	[, azione2, ...]

• aggiungere/togliere colonne
• cambiare il tipo di dato
• rinominare la tabella
• definire chiavi primarie, esterne, ecc..

Aggiungere colonne

ALTER TABLE nomeTabella
ADD COLUMN definizioneColonna
[ FIRST | AFTER nomeColonna ]

Aggiungere colonne

ALTER TABLE Infrazioni
	ADD COLUMN dataModifica TIMESTAMP
	AFTER data

Rimuovere colonne

ALTER TABLE nomeTabella
	DROP COLUMN nomeColonna

Rimuovere colonne

ALTER TABLE infrazioni
	DROP COLUMN dataModifica

Modificare colonne

ALTER TABLE nomeTabella
	CHANGE COLUMN nomeOriginale
	nomeNuovo tipo

Modificare colonne

ALTER TABLE infrazioni
	CHANGE COLUMN dataModifica
	dataUltimaModifica TIMESTAMP

Modificare colonne

ALTER TABLE automobili
	CHANGE COLUMN cognome
	VARCHAR(100)

Rinominare tabelle

ALTER TABLE nomeTabella
	RENAME TO nuovoNome

Rinominare tabelle

ALTER TABLE infrazioni
	RENAME TO odiateInfrazioni

Aggiungere/Rimuovere Foreign Key

ALTER TABLE nomeTabella
	ADD CONSTRAINT nome
	FOREIGN KEY (...)
	REFERENCES tabella(...)

ALTER TABLE nomeTabella
	DROP FOREIGN KEY nome

DataBase di prova

classicmodels

vendita modellini

https://www.mysqltutorial.org/getting-started-with-mysql/mysql-sample-database-aspx/

DataBase di prova

classicmodels

• clienti
• dipendenti
• uffici di vendita
• prodotti
• ordini
• pagamenti

DataBase di prova

classicmodels

• customers: dati dei clienti
• products: modellini disponibili
• productlines: linee di prodotti
  • auto classiche, moto, navi, treni
• orders: ordini fatti dai clienti

DataBase di prova

classicmodels

• orderdetails: dettagli di ogni ordine dei clienti
  • linee dell’ordine
• payments: pagamenti fatti dai clienti
• employees: informazioni sui dipendenti
  • chi è il capo di chi, telefoni, ecc
• offices: uffici di vendita

Elencare elementi della tabella

Elencare tutti i modellini in vendita

SELECT * FROM products;

Istruzione select (base)

SELECT attributo1 [, attributo2, ...]
FROM tabella1 [, tabella2, ...]
[WHERE condizione]

• * = tutti gli attributi
• FROM = da dove (per ora)
• WHERE = quali ennuple

Tutto è una tabella

• selezione: WHERE
  • selezione una “sottotabella”
• proiezione: elenco attributi
  • mostro una tabella scegliendo quali colonne mostrare

Selezione

Mostra i modellini che costano meno di 75$

SELECT * FROM  products WHERE MSRP < 75;

Selezione e proiezione

Mostra nome, prezzo di acquisto e di vendita dei modellini che costano meno di 75$

SELECT productName, buyPrice, MSRP
FROM products
WHERE MSRP < 75;

Rinominare attributi

istruzione AS

SELECT productName AS nomeProdotto,
productVendor AS nomeVenditore
FROM products;

SELECT, abbreviazioni

SELECT productName, buyPrice, MSRP
FROM products
WHERE MSRP < 75;

in realtà stiamo scrivendo

SELECT p.productName, p.buyPrice, p.MSRP
FROM products p
WHERE p.MSRP < 75;

SELECT, abbreviazioni

SELECT * FROM products;

in realtà stiamo scrivendo

SELECT productCode, productName, productLine,
productScale, productVendor,
productDescription, quantityInStock,
buyPrice, MSRP
FROM products;

SELECT, abbreviazioni

SELECT * FROM products;

in realtà stiamo scrivendo

SELECT productCode, productName, productLine,
productScale, productVendor,
productDescription, quantityInStock,
buyPrice, MSRP
FROM products
WHERE true;

Condizioni: testo esatto

Mostra tutti i dipendenti di nome Leslie

SELECT * FROM employees
WHERE firstName = 'Leslie';

Virgolette singole o doppie?

• "Leslie" o 'Leslie'?
  • indifferente!
• standard ANSI: 'Leslie'

Virgolette singole o doppie?

per inserire ‘ in una stringa? Es: Ci'ao

• delimitata da " ": "Ci'ao"
• delimitata da ' ': raddoppio → 'Ci''ao'

per inserire ‘ in una stringa? Es: Ci"'"ao

• delimitata da " ": raddoppio → "Ci""ao"
• delimitata da ' ': 'Ci"ao'

Condizioni: testo incompleto

Mostra tutti i dipendenti il cui cognome finisce per “son”.

SELECT * FROM employees
WHERE lastName LIKE '%son';

Condizioni: testo incompleto

Mostra tutti i dipendenti il cui cognome NON finisce per “son”.

SELECT * FROM employees
WHERE lastName NOT LIKE '%son';

Condizioni: testo incompleto

• % = zero o più caratteri
• _ = esattamente un carattere
• per cercare il carattere % uso \%
• per cercare il carattere _ uso \_

Condizioni: testo incompleto

Mostra tutti i dipendenti il cui nome finisce per "arry" e davanti ha una sola lettera

SELECT * FROM employees
WHERE firstName LIKE '_arry';

Condizioni: testo incompleto

Mostra tutti i prodotti che hanno una scala divisibile per 10 e minore di 100 (es: 1:10, 1:20, 1:30, ...)

SELECT * FROM products
WHERE productScale LIKE '1:_0';

Condizioni: testo incompleto

Mostra tutti i dipendenti il cui nome inizia con M e la cui terza lettera è una r

SELECT * FROM employees
WHERE firstName LIKE 'M_r%';

Più condizioni

Mostra i modellini che costano meno di 75 e che abbiamo comprato a più di 30

SELECT productName, MSRP, buyPrice
FROM products
WHERE MSRP < 75 AND buyPrice > 30;

Più condizioni

Mostra i modellini che costano meno di 75 o più di 150

SELECT productName, MSRP
FROM products
WHERE MSRP < 75 OR MSRP > 150;

Più condizioni

Mostra i modellini che costano meno di 75 o più di 150 e che comunque abbiamo comprato a più di 30

SELECT productName, MSRP, buyPrice
FROM products
WHERE (MSRP<75 OR MSRP>150) AND buyPrice>30;

Intervalli

Seleziona i valori compresi tra x e y (inclusi)

SELECT ... FROM ...
WHERE colonna BETWEEN x AND y;

Intervalli

Mostra i pagamenti con importi compresi tra 5.000 ed 8.000

SELECT * FROM payments
WHERE amount BETWEEN 5000 AND 8000;

Intervalli

Mostra i pagamenti con importi compresi tra 5.000 ed 8.000

SELECT * FROM payments
WHERE amount BETWEEN 5000 AND 8000;

forma equivalente

SELECT * FROM payments
WHERE amount >= 5000 AND
amount <= 8000;

Intervalli

Prendi tutti i dipendenti il cui nome inizia con una lettera tra B ed F

SELECT * FROM employees
WHERE firstName BETWEEN 'B' AND 'F';

Liste

Controlla se il valore è presente in una lista di valori

SELECT ... FROM ...
WHERE colonna IN (val1, val2, ...)

Liste

Mostra codice ufficio, città e numero di telefono degli uffici in Francia o America

SELECT officeCode, city, phone
FROM offices
WHERE country IN ('USA', 'FRANCE');

Liste

SELECT officeCode, city, phone
FROM offices
WHERE country IN ('USA', 'FRANCE');

forma equivalente

SELECT officeCode, city, phone
FROM offices
WHERE country = 'USA' OR
country = 'FRANCE';

Liste

Mostrare i modellini del tipo "Planes", "Ships" o "Classic Cars"

SELECT * FROM products
WHERE productLine
IN ('Planes','Ships','Classic Cars');

Gestire i NULL

SELECT ... FROM ...
WHERE colonna = "";

NO

Gestire i NULL

SELECT ... FROM ...
WHERE colonna IS NULL;

Gestire i NULL

Mostra gli ordini non spediti

SELECT * FROM orders
WHERE shippedDate IS NULL;

Gestire i NULL

Mostra gli ordini creati dopo il 30/04/2005 e non spediti

SELECT * FROM orders WHERE
orderDate > '2005-04-30' AND
shippedDate IS NULL;

Espressioni

Mostra i prezzi di vendita senza l’IVA (prezzo / 1.22)

SELECT productName, MSRP/1.22 AS noIVA
FROM products;

Espressioni

Mostra i prodotti con un margine superiore a 50

SELECT productName, MSRP, buyPrice
FROM products
WHERE MSRP-buyPrice > 50;

Funzioni

Stringhe

• length()
• reverse()
• right()
• trim()
• …

Funzioni

Mostra i prodotti con nomi di almeno 15 caratteri.

SELECT productName, length(productName)
FROM products
WHERE length(productName) >= 15;

Funzioni

Data e ora

• day()
• year()
• now()
• month()
• monthname()
• …

Funzioni

Mostra i prodotti orinati nel mese di gennaio

SELECT * from orders
WHERE month(orderDate) = 1;

Ordinamento

Stabilire l’ordine di presentazione dei risultati

SELECT ... FROM ... WHERE ...
ORDER BY col1 [ASC|DESC], col2 [ASC|DESC], ...

• ASC: crescente → DEFAULT!
• DESC: decrescente

Ordinamento

Mostra i modellini ordinandoli per prezzo di vendita crescente

SELECT productName, MSRP
FROM products
ORDER BY MSRP;

Ordinamento

Mostra i clienti ordinandoli per paese crescente e credito massimo decrescente

SELECT customerName, country, creditLimit
FROM customers
ORDER BY country, creditLimit DESC;

Ordinamento

Mostra gli ordini ordinandoli in base allo status in cui si trovano (in corso, in attesa, cancellati, ecc).

SELECT * FROM orders
order by status;

NO

Ordinamento

Mostra gli ordini ordinandoli in base allo status in cui si trovano (in corso, in attesa, cancellati, ecc)

FIELD(text, str1, str2, str3, ...)

Ritorna la posizione della stringa text nella lista str1, str2, str3, …

Ordinamento

Mostra gli ordini ordinandoli in base allo status in cui si trovano (in corso, in attesa, cancellati, ecc).

SELECT * FROM orders
ORDER BY FIELD(status, 'In Process',
'On Hold', 'Cancelled', 'Resolved',
'Disputed','Shipped');

Uniamo il tutto...

Mostra i prodotti venduti a meno di 100€, mettendo in cima quelli con il margine più alto

SELECT productName,
MSRP-buyPrice as margine
FROM products
WHERE MSRP < 100
ORDER BY msrp-buyPrice DESC

Righe Duplicate

Ogni tanto le nostre query ritornano righe

duplicate: come facciamo ad eliminare i doppioni?

SELECT DISTINCT ... FROM ...

Righe Duplicate

Mostra tutte le città in cui si trovano i miei clienti, ordinandole alfabeticamente

SELECT DISTINCT city FROM customers
order by city;

Decodificare le relazioni

Prodotto cartesiano

Creo il prodotto cartesiano di più tabelle

SELECT ... FROM tabella1, tabella2, ...

Risultato: una riga per ogni combinazione di valori tra le righe di tabella1 e di tabella2

Prodotto cartesiano

Crea il prodotto cartesiano tra la tabella degli

impiegati e quella dei clienti

SELECT * FROM customers, employees;

Cross Join

Crea il prodotto cartesiano tra la tabella degli

impiegati e quella dei clienti

SELECT * FROM customers, employees;

Forma esplicita:

SELECT * FROM customers CROSS JOIN employees;

Prodotto Cartesiano Filtrato

Non voglio vedere tutte le combinazioni, solo filtrare tabella!

SELECT ... FROM tabella1, tabella2, ...
WHERE condizione sui valori comuni (PK e FK)

Creo una riga per ogni combinazione di valori tra le righe della tabella1 e della tabella2, ma poi salvo solo quelle sensate

Prodotto Cartesiano Filtrato

Mostra per ogni cliente il nome del venditore associato

SELECT customerName, salesRepEmployeeNumber,
lastName, employeeNumber
FROM customers, employees
WHERE salesRepEmployeeNumber = employeeNumber

Inner Join

Modo migliore per scrivere il tutto

SELECT ... FROM tabella1
INNER JOIN tabella2
ON PK = FK

Inner Join

Mostra per ogni cliente il nome del venditore associato

SELECT customerName, salesRepEmployeeNumber,
lastName, employeeNumber
FROM customers
INNER JOIN employees
ON salesRepEmployeeNumber = employeeNumber;

Inner Join ad ambiguità

Ogni tanto PK e FK hanno stesso nome

SELECT ... FROM tabella1
INNER JOIN tabella2
ON tabella2.PK = tabella1.FK

Inner Join ad ambiguità

Mostra per ogni prodotto la descrizione della linea di prodotti cui appartiene

SELECT productCode, productName, textDescription
FROM products INNER JOIN productlines
ON products.productline =
productlines.productline;

Inner Join ad ambiguità

SELECT productCode, productName, textDescription
FROM products INNER JOIN productlines
ON products.productline = productlines.productline;

Inner Join ad ambiguità

Forma equivalente

SELECT productCode, productName, textDescription
FROM products p1
INNER JOIN productlines p2
ON p1.productline = p2.productline;

Inner Join

Mostra per ogni prodotto la descrizione della linea di prodotti cui appartiene

SELECT productCode, productName, textDescription
FROM products INNER JOIN productlines
ON products.productline =
productlines.productline;

Criteri di Join

Se gli attributi hanno lo stesso nome tra le relazioni, si può usare una forma abbreviata

SELECT productCode, productName,
textDescription
FROM products INNER JOIN productlines
USING(productline);

Criteri di Join

Mostra tutti gli impiegati e la città in cui si trova l’ufficio cui afferiscono

SELECT firstName, lastName, city
FROM employees INNER JOIN offices
USING (officeCode);

Criteri di Join

Se gli unici nomi di attributi in comune tra due tabelle sono quelli di FK/PK, si può usare una forma ANCORA più abbreviata

SELECT ... FROM tabella1
NATURAL JOIN tabella2

pericolose!

Cosa succede se aggiungo/cambio colonne?

Criteri di Join

Mostra per ogni prodotto la descrizione della linea di prodotti cui appartiene

SELECT productCode, productName,
textDescription
FROM products NATURAL JOIN productlines;

Problema

• Voglio vedere i clienti ✓
• Voglio vedere il nome dei venditori assegnati ✓
• Voglio vedere anche i clienti senza venditore assegnato (?)

Left Outer Join

Mostra tutti i dati della prima tabella, e se possibile associa le informazioni della seconda

SELECT ... FROM tabella1
LEFT OUTER JOIN tabella2
ON PK = FK

Left Outer Join

Mostra tutti i clienti; se il cliente ha un venditore associato, mostrane i dati

SELECT customerName, concat(firstName,' ',lastName)
FROM customers LEFT OUTER JOIN employees
ON salesRepEmployeeNumber = employeeNumber

Left Outer Join

Mostra tutti i clienti; se il cliente ha un venditore associato, mostrane i dati

SELECT customerName, concat(firstName,' ',lastName)
FROM customers LEFT OUTER JOIN employees
ON salesRepEmployeeNumber = employeeNumber

forma equivalente

SELECT customerName, concat(firstName,' ',lastName)
FROM customers LEFT JOIN employees
ON salesRepEmployeeNumber = employeeNumber

Inner vs Left Outer Join

Inner

Left Outer

Left Outer Join

Mostra tutti i clienti ed i relativi ordini, inclusi i clienti che non hanno fatto ordini

SELECT c.customerNumber, c.customerName,
o.orderNumber, o.status
FROM customers c LEFT JOIN orders o
ON c.customerNumber = o.customerNumber;

Left Outer Join

Mostra tutti i clienti che non hanno ordini

SELECT c.customerNumber, c.customerName,
orderNumber, o.status
FROM customers c LEFT JOIN orders o
ON c.customerNumber = o.customerNumber
WHERE orderNumber is NULL

Right Outer Join

Mostra tutti i dati della SECONDA tabella, e se possibile associa le informazioni della prima

SELECT ... FROM tabella1
RIGHT OUTER JOIN tabella2
ON PK = FK

Inner vs Left vs Right Outer Join

Inner

Left Outer

Right Outer

Right Outer Join

Mostra tutti i clienti ed i relativi ordini, inclusi i clienti che non hanno fatto ordini

SELECT c.customerNumber, c.customerName,
orderNumber, o.status
FROM orders o RIGHT JOIN customers c
ON c.customerNumber = o.customerNumber

Join Multiple

Decodificare il contenuto di più tabelle in una sola query

SELECT ... FROM tabella1
[INNER|LEFT|RIGHT]JOIN tabella2
ON PK = FK
[INNER|LEFT|RIGHT]JOIN tabella3
ON PK = FK

Join Multiple

Mostra tutti i clienti, il nome dell’impiegato associato ed il numero di telefono dell’ufficio

SELECT c.customerName, e.firstName, o.phone
FROM customers c
LEFT JOIN employees e
ON c.salesRepEmployeeNumber = e.employeeNumber
LEFT JOIN offices o
USING (officeCode)

Join Multiple

Stampare ogni riga dell’ordine, indicando il nome del cliente, numero d’ordine ed il nome del prodotto ordinato

SELECT c.customerName, o.orderNumber, p.productName
FROM orderdetails d
INNER JOIN orders o
USING (orderNumber)
INNER JOIN customers c
USING (customerNumber)
INNER JOIN products p
USING (productCode)
ORDER BY o.orderNumber, d.orderLineNumber;

Join multiple

Inner + Inner

Inner + Left Outer

Cross Join

Inner Join

Inner Join e NULL

Left Outer Join

Join Multiple

Full Outer Join

Mostra tutti i dati della prima tabella, e se possibile associa le informazioni della seconda.

Mostra comunque tutti i dati della seconda tabella.

SELECT ... FROM tabella1
FULL OUTER JOIN tabella2
ON PK = FK

Join Multiple

Persone che possiedono veicoli colorati

SELECT v.veicolo, c.colore,  p.cognome
FROM veicolo v
INNER JOIN persona p ON  v.id = p.id
INNER JOIN colore c ON v.colore = c.id ;

Join Multiple

Persone che possiedono veicoli colorati o nessun veicolo

SELECT v.veicolo, c.colore,  p.cognome
FROM persona p
LEFT JOIN veicolo ON v.id = p.id
INNER JOIN colore c ON v.colore = c.id ;

Join Multiple

Persone che possiedono veicoli colorati o nessun veicolo

SELECT v.veicolo, c.colore,  p.cognome
FROM veicolo v
INNER JOIN colore c ON v.colore = c.id
RIGHT JOIN persona p ON v.id = p.id;

Self join

Join di una tabella con se stessa

SELECT ... FROM tabella1
[LEFT|RIGHT|INNER] JOIN tabella1
ON PK = FK

• avrò sicuramente nomi di attributi duplicati
• dovrò introdurre degli alias

Self Join

Self Join

Mostra tutti i dipendenti ed il nome del loro capo

SELECT m.employeeNumber, m.firstName,
m.lastName, m.reportsTo, c.firstName,
c.lastName FROM employees m
LEFT JOIN employees c
ON m.reportsTo = c.employeeNumber;

Self Join

Mostra tutte le coppie di clienti che abitano nella stessa città

SELECT c1.city, c1.customerName,
c2.customerName
FROM customers c1 INNER JOIN customers c2
ON c1.city = c2.city;

NO

Self Join

Mostra tutte le coppie di clienti che abitano nella stessa città

SELECT c1.city, c1.customerName,c2.customerName
FROM customers c1 INNER JOIN customers c2
ON c1.city = c2.city AND
c1.customername <> c2.customerName

Self Join

Mostra tutte le coppie di clienti che abitano nella stessa città

SELECT c1.city, c1.customerName,c2.customerName
FROM customers c1 INNER JOIN customers c2
ON c1.city = c2.city AND
c1.customername <> c2.customerName

forma equivalente

SELECT c1.city, c1.customerName,c2.customerName
FROM customers c1 INNER JOIN customers c2
ON c1.city = c2.city
WHERE c1.customername <> c2.customerName

Union join

Unisce i risultati di più query

SELECT ... FROM ...
UNION [DISTINCT | ALL]
SELECT ... FROM ...
[UNION [DISTINCT | ALL]
SELECT ... FROM ...]

ATTENZIONE!

• stesso numero di attributi
• attributi omogenei

UNION

SELECT ... FROM ...
UNION [DISTINCT | ALL]
SELECT ... FROM ...
[UNION [DISTINCT | ALL]
SELECT ... FROM ...]

• DISTINCT: default, elimina duplicati
• ALL: se specificato, NON elimina duplicati

UNION

Mostra l’identificativo ed il nome di tutti gli impiegati e di tutti i clienti

SELECT customerNumber AS id,contactLastname AS name
FROM customers
UNION
SELECT employeeNumber AS id, firstname AS name
FROM employees;

UNION

Mostra l’identificativo ed il nome di tutti gli impiegati e di tutti i clienti

SELECT customerNumber,contactLastname
FROM customers
UNION
SELECT employeeNumber, firstname
FROM employees;

• Se non specifico l’alias prende i nomi della prima query

UNION

Mostra l’id ed il nome di tutti gli impiegati e di tutti i clienti, scrivendo per ognuno cosa sia

SELECT customerNumber AS id,
contactLastname AS name, "Cliente" AS tipo
FROM customers
UNION
SELECT employeeNumber AS id,
concat(firstname," ",lastname) AS name,
"Impiegato" AS tipo
FROM employees;

UNION e ordinamento

E se volessi ordinare i risultati?

SELECT ... FROM ...
UNION [DISTINCT | ALL]
SELECT ... FROM ...
ORDER BY criteri

ATTENZIONE!

• l’ultima riga è riferita al RISULTATO della union, non all’ultima query
• se si inserisce la clausola all’interno della singola query verrà ignorata

UNION e ordinamento

Posso usare le parentesi per fare ordine

(SELECT ... FROM ...)
UNION [DISTINCT | ALL]
(SELECT ... FROM ...)
ORDER BY criteri

Union ed ordinamento

Mostrare id e nome di clienti ed impiegati ordinandoli per nome

(SELECT customerNumber AS id,
contactLastname AS name
FROM customers)
UNION
(SELECT employeeNumber AS id, firstname AS name
FROM employees)
ORDER BY name;

Union ed ordinamento

Mostrare i paesi in cui c’è un ufficio o un cliente, ordinati per nome

SELECT country FROM offices
UNION
SELECT country FROM customers
ORDER BY country;

Intersect

Restituisce l’intersezione di più query

SELECT ... FROM ...
INTERSECT
SELECT ... FROM ...
INTERSECT
SELECT ... FROM ...

Non c’è in MySql, servono query nidificate

Raggruppare i dati

Voglio raggruppare le ennuple in sottogruppi in base ad uno o più valori

SELECT a1 , a2 ,...,an
FROM tabella1 WHERE condizioni
GROUP BY a1, a2,...,an

Raggruppare i dati

Mostra tutti gli stati degli ordini esistenti

SELECT status
FROM orders
GROUP BY status

Raggruppare i dati

Mostra tutti gli stati degli ordini fatti prima del 31/12/2003

SELECT status
FROM orders
WHERE orderDate < "2003-12-31"
GROUP BY status;

Raggruppare i dati

Mostra tutti gli stati degli ordini fatti prima del 31/12/2003

SELECT status
FROM orders
WHERE orderDate < "2003-12-31"
GROUP BY status;

forma equivalente

SELECT DISTINCT status
FROM orders
WHERE orderDate < "2003-12-31"

Funzioni di aggregazione

Permettono di effettuare calcoli su tutti i valori che l’attributo assume nella query eseguita

SELECT a1, a2, ..., an, aggregatore(ax)
FROM tabella1 WHERE condizioni

Esempio:

• quanti ordini sono stati fatti?
• quanto costa in media un prodotto?

Funzioni di aggregazione

SELECT a1, a2, ..., an, aggregatore(ax)
FROM tabella1 WHERE condizioni

Aggregatori:

• COUNT: conta il numero di valori presenti
• SUM: somma dei valori
• AVG: media dei valori
• MAX/MIN: massimo e minimo

Funzioni di aggregazione

Quanti dipendenti ci sono in azienda?

SELECT count(*)
FROM employees;

Funzioni di aggregazione - NULL

Differenza fra

SELECT count(*)
FROM employees

e

SELECT count(reportsTo)
FROM employees

Funzioni di aggregazione - DINSTINCT

Quanti capi ci sono in azienda?

SELECT count( distinct reportsTo)
FROM employees;

Funzioni di aggregazione

Quanti pagamenti ho ricevuto?

SELECT count(*)
FROM payments;

Funzioni di aggregazione

Quanti soldi ho ricevuto con i pagamenti?

SELECT SUM(amount)
FROM payments;

Funzioni di aggregazione

Qual è il prezzo medio di vendita di un prodotto?

SELECT avg(MSRP)
FROM products;

Funzioni di aggregazione

Qual è il prezzo medio di vendita di un prodotto? Quale il massimo? Quale il minimo?

SELECT avg(MSRP), max(MSRP), min(MSRP)
FROM products;

Funzioni di aggregazione ERRATE

Attenzione a non chiedere cose assurde

SELECT avg(MSRP), productName
FROM products

• quale productName devo mostrare?
• standard ANSI: errore
• MySql risponde… con il primo valore!

Funzioni di aggregazione e raggruppamenti

Voglio raggruppare le ennuple in sottogruppi in base ad uno o più valori, mostrando anche valori calcolati su ogni gruppo

SELECT a1, a2 , ... , an, aggregatore(ax)
FROM tabella1 WHERE condizioni
GROUP BY a1, a2, ... ,an

Funzioni di aggregazione e raggruppamenti

Mostra gli stati degli ordini e quanti ordini si trovano in ciascuno stato

SELECT status, count(*)
FROM orders
GROUP BY status;

Funzioni di aggregazione e raggruppamenti

Mostra quanti prodotti ho per ogni categoria ed il prezzo medio di vendita

SELECT productLine, count(*), avg(MSRP)
FROM products
GROUP BY productLine;

Aggreghiamo...

Mostrare quanti ordini ho spedito ogni giorno

SELECT count(*), shippedDate
FROM orders
GROUP BY shippedDate

Aggreghiamo...

Mostrare quanti ordini ho spedito nei vari mesi (una riga per mese)

• PostgreSQL: usate date_part('month', attributo)

SELECT count(*), month(shippedDate)
FROM orders
WHERE shippedDate IS NOT NULL
GROUP BY month(shippedDate);

Aggreghiamo...

Mostrare quanti ordini ho spedito nei vari mesi (una riga per mese ed anno)

• PostgreSQL (mese): usate date_part('month', attributo)
• PostgreSQL (anno): usate date_part('year', attributo)

SELECT count(*), month(shippedDate),
year(shippedDate)
FROM orders
WHERE shippedDate IS NOT NULL
GROUP BY year(shippedDate), month(shippedDate);

Aggreghiamo…

Mostrare per ogni ordine: il nome del cliente, la data dell’ordine ed il totale dell’ordine

Un passo alla volta:

• calcoliamo il totale di ogni ordine
• estraiamo i dati dalle altre tabelle

Aggreghiamo...

Mostrare per ogni ordine: il totale dell’ordine

SELECT orderNumber,
sum(quantityOrdered*priceEach)
FROM orderdetails
GROUP BY orderNumber;

Aggreghiamo...

Mostrare per ogni ordine: il nome del cliente, la data dell’ordine ed il totale dell’ordine

SELECT customerName, orderDate,
sum(quantityOrdered*priceEach)
FROM orderdetails
INNER JOIN orders USING (orderNumber)
INNER JOIN customers USING (customerNumber)
GROUP BY orderNumber;

Aggreghiamo...

Mostrare quanti ordini ha fatto ogni cliente, mettendo in cima quelli più assidui

SELECT count(*) nOrdini, customerNumber
FROM orders
GROUP BY customerNumber
ORDER BY nOrdini DESC;

Aggreghiamo…

Mostrare l’estratto conto del cliente 124

• pagamenti cliente: importo negativo
• debiti cliente: importo positivo

Un passo alla volta:

1. estrarre i pagamenti fatti con la relativa data
2. prendere i totali degli ordini del cliente
3. unire i due risultati, ordinandoli per data

Aggreghiamo...

Mostrare l’estratto conto del cliente 124

estrarre i pagamenti fatti con la relativa data

SELECT amount*-1, paymentDate FROM payments
WHERE customerNumber = 124

Aggreghiamo...

Mostrare l’estratto conto del cliente 124

prendere i totali degli ordini del cliente

SELECT sum(quantityOrdered*priceEach),orderDate
FROM orderdetails INNER JOIN orders
USING (orderNumber)
WHERE customerNumber = 124
GROUP BY orderNumber;

Aggreghiamo...

Mostrare l’estratto conto del cliente 124

unire i due risultati, ordinandoli per data

SELECT amount*-1, paymentDate FROM payments
WHERE customerNumber = 124
UNION
SELECT sum(quantityOrdered*priceEach),orderDate
FROM orderdetails INNER JOIN orders
USING (orderNumber)
WHERE customerNumber = 124
GROUP BY orderNumber
ORDER BY paymentDate;

Filtrare dati aggregati

Come applicare un filtro al risultato di una funzione di aggregazione?

SELECT a1, a2, ... ,an, aggregatore(ax)
FROM tabella1 WHERE condizioni
GROUP BY a1, a2, ... ,an
HAVING condizioniAggregate

Filtrare dati aggregati

Mostrare tutti gli ordini il cui totale è < 10.000

SELECT orderNumber,
sum(quantityOrdered*priceEach) as tot
FROM orderdetails
GROUP BY orderNumber
HAVING tot < 10000;

Filtrare dati aggregati

Mostrare tutti gli ordini il cui totale è < 10.000 e per i quali verranno spediti più di 100 pezzi

SELECT orderNumber,
sum(quantityOrdered) as q,
sum(quantityOrdered*priceEach) as tot
FROM orderdetails
GROUP BY orderNumber
HAVING tot < 10000 AND q > 100;

Filtrare dati aggregati

Mostrare tutti gli ordini il cui totale è < 10.000 e che non sono stati spediti

Hint: ordini spediti hanno status "Shipped"

SELECT ordernumber, status,
SUM(quantityOrdered*priceeach) total
FROM orderdetails
INNER JOIN orders USING(ordernumber)
GROUP BY ordernumber
HAVING status <> 'Shipped' AND total < 10000;

Filtrare dati aggregati

SELECT ordernumber, status,
SUM(quantityOrdered*priceeach) total
FROM orderdetails
INNER JOIN orders USING(ordernumber)
GROUP BY ordernumber
HAVING status <> 'Shipped' AND total < 10000;

forma equivalente

SELECT ordernumber, status,
SUM(quantityOrdered*priceeach) total
FROM orderdetails
INNER JOIN orders USING(ordernumber)
WHERE status <> 'Shipped'
GROUP BY ordernumber
HAVING total < 10000;

Subquery

Posso annidiare le query una dentro l’altra

SELECT a1,a2,...,an,(QUERY singolo val.)
FROM (QUERY)
WHERE a1 > (QUERY singolo val.)
AND a2 IN (QUERY singolo attrib.)

Per adesso:

• le subquery vivono di vita propria
• possiamo scriverle separatamente, poi incorporarle

Subquery - singolo valore

Mostrare per ogni articolo il prezzo di vendita ed il prezzo del prodotto più caro

Hint: subquery nella clausola SELECT

SELECT productName, MSRP,
(SELECT max(MSRP)
FROM products) as massimo
FROM products;

Subquery - singolo valore

Mostrare i dati del pagamento più alto ricevuto

Hint: subquery nella clausola WHERE

SELECT customerNumber, checkNumber, amount
FROM payments
WHERE amount =
(SELECT MAX(amount)
FROM payments);

Subquery - singolo valore

Mostra i pagamenti superiori alla media

SELECT customerNumber, checkNumber, amount
FROM payments
WHERE amount >
(SELECT AVG(amount)
FROM payments);

Subquery - singolo valore

Mostrare i clienti che non hanno fatto ordini

Hint: subquery nella clausola WHERE ... NOT IN

SELECT customername
FROM customers
WHERE customerNumber NOT IN
(SELECT DISTINCT customernumber
FROM orders);

Subquery - singolo attributo

Mostrare i clienti che non hanno fatto ordini

SELECT customername
FROM customers
WHERE customerNumber NOT IN
(SELECT DISTINCT customernumber
FROM orders);

forma equivalente

SELECT customername
FROM customers
LEFT JOIN orders USING (customerNumber)
WHERE orderNumber IS NULL;

Subquery - FROM

Mostrare il numero massimo, minimo e medio di pezzi inseriti negli ordini

Hint: creare prima la query che somma le righe dell’ordine

SELECT max(items), min(items),
floor(avg(items)) as media
FROM (SELECT orderNumber,
SUM(quantityOrdered) AS items
FROM orderdetails
GROUP BY orderNumber) AS lineitems;

Subquery Correlate

Finora:

• posso eseguire la subquery da sola
• il motore la esegue una volta
• … non è sempre così

Subquery Correlate

SELECT a1,a2,...,an
FROM tab1 WHERE 
a1 > (SELECT c1
FROM tab2
WHERE tab2.c2 > tab1.a1)

• non eseguibile “da sola”
• eseguita per ogni riga della query principale
• visibilità varaibili: solo da query a subquery

Subquery Correlate

Mostrare i prodotti il cui prezzo di acquisto è superiore alla media della linea cui afferiscono

SELECT productname, buyprice
FROM products AS p
WHERE buyprice > (
SELECT AVG(buyprice)
FROM products
WHERE productline = p.productline);

EXISTS

Operatore booleano (per WHERE): ritorna vero se una sottoquery ha valori

SELECT a1,a2,...,an
FROM tab
WHERE EXISTS (QUERY singolo val.)

Limiti

Non voglio tutte le righe che soddisfano il filtro, solo le TOP N

SELECT a1,a2,...,an
FROM tab
WHERE ...
LIMIT numero

Subquery Correlate

Mostrare i primi 5 clienti (codice cliente, nome e limite di credito)

SELECT customernumber,
customername,
creditlimit
FROM customers
LIMIT 5;