Riassunto esame Informatica, prof. Gnudi, libro consigliato Progettazione dei database, Lorenzi

ESEMPIO

CARATTERISTICHE GENERALI DI SQL

1) Linguaggio standard per la gestione di database relazionali

2) Linguaggio dichiarativo (si dichiara cosa si vuole ottenere e non come ottenerlo)

3) Estensione dell’algebra relazionale (calcoli, ordinamenti, raggruppamenti)

4) Visione tabellare dei dati (opera su gruppi di righe o sull’intera tabella, non su una riga per volta)

5) Identificatori (nomi di tabelle e di colonne di lunghezza massima di 18 caratteri)

6) Colonne specificate con la dot notation (NomeTabella.NomeColonna: obbligatoria solo in caso di

omonimia)

7) Operatori aritmetici e relazionali

+ - * / ^ & > < = <= >= <> Between IN LIKE …

8) Operatori logici:

AND OR NOT XOR…

9) Stringhe di caratteri delimitate con ‘ oppure “

10) Date delimitate con # (Access)

11) Assenza di informazioni, valore nullo: NULL

• Controllato con il predicato IS NULL

• Esempio: Dipartimento IS NULL

• Errore: Dipartimento = NULL

12) Tipi per i dati: INTEGER, DECIMAL, FLOAT, CHARACTER, DATE, TIME 28

SQL COME DDL

CREAZIONE TABELLA Impiegati

Le tabelle vengono definite con il comando CREATE TABLE seguito dal nome della tabella e dall’elenco degli

attributi (specificando nome e tipo di dato).

Gli attributi possono essere qualificati mediante diverse clausole con le quali è possibile determinare: chiave

primaria, obbligatorietà, valore di default di un campo, le chiavi esterne.

In tutte le righe della tabella quella colonna deve essere riempita

CREATE TABLE Impiegati ( con un valore non nullo e rende il campo obbligatorio

ID smallint primary key,

Nome char (20) not null, Definisce il valore da attribuire al campo se, al momento della

creazione del record che lo contiene, non gli viene assegnato alcun

Cognome char (30) not null, valore

Residenza char (20) default ‘*** Manca Residenza’,

Stipendio decimal (9,2),

Dipartimento char (5) references Dipartimenti (Codice));

Indica che l’attributo Dipartimento è chiave esterna e definisce un

vincolo di integrità referenziale con la tabella Dipartimenti

AGGIUNTA CAMPO Nascita a Impiegati

ALTER TABLE Impiegati

ADD Nascita date;

ELIMINAZIONE CAMPO Residenza da Impiegati

ALTER TABLE Impiegati

DROP Residenza;

Coppia di attributi: Cognome, Nome INDICIZZATA e NON DUPLICABILE

CREATE UNIQUE INDEX IndiceImpiegati

ON Impiegati (Cognome, Nome);

ELIMINAZIONE TABELLA Impiegati

DROP TABLE Impiegati;

ELIMINAZIONE INDICE IndiceImpiegati

DROP INDEX IndiceImpiegati ON Impiegati; 29

SQL COME DML

I valori degli attributi delle righe possono essere inseriti, aggiornati o cancellati con i comandi: INSERT,

UPDATE, DELETE

INSERIMENTO DI UN NUOVO RECORD nella tabella Impiegati

INSERT INTO Impiegati

(ID, Nome, Cognome, Residenza, Stipendio, Dipartimento)

VALUES (20, ‘Mario’, ‘Rossini’, ‘Caserta’, 31500, ‘Mag’);

ASSEGNARE il dipendente con ID=20 al dipartimento Produzione

UPDATE Impiegati

SET Dipartimento = ‘Prod’

WHERE ID = 20;

ELIMINAZIONE del dipendente con ID=20

DELETE FROM Impiegati

WHERE ID=20; SQL COME QL

Per estrarre informazioni dal database si usa il comando SELECT, la cui sintassi base è:

SELECT Colonne

FROM Tabelle

WHERE Condizioni ;

INTERROGAZIONI SU UNA SOLA TABELLA

1) Elenco dei dipendenti (con Cognome, Nome e Residenza) del dipartimento di codice Prod

SELECT Cognome, Nome, Residenza

FROM Impiegati

WHERE Dipartimento = ‘Prod’;

2) Elenco dei dipendenti (con ID, Cognome, Nome) che lavorano alla produzione e risiedono a Torino

SELECT ID, Cognome, Nome

FROM Impiegati

WHERE Dipartimento = ‘Prod’ AND Residenza = ‘Torino’;

3) Elenco di TUTTI i dati dei dipendenti che abitano a Roma

SELECT *

FROM Impiegati

WHERE Residenza = ‘Roma’;

4) Eliminare duplicati: Elenco di tutte le località di residenza dei dipendenti, SENZA DUPLICATI

SELECT DISTINCT Residenza

FROM Impiegati; 30

5) Elenco di tutte le località di residenza dei dipendenti

SELECT Residenza

FROM Impiegati;

6) Ridenominazione dei campi: Elenco degli impiegati (con ID, Nome, Cognome) RINOMINANDO

“Matricola” al posto di ID

SELECT ID AS Matricola, Nome, Cognome

FROM Impiegati;

7) Esecuzione di calcoli sui campi: Elenco degli impiegati: con Cognome, Nome, stipendi rinominati

come “Attuale” e nuovi stipendi degli impiegati del magazzino se aumentano del 5% (rinominati

come “Nuovo”)

SELECT Cognome, Nome, Stipendio AS Attuale, Stipendio*1,05 AS Nuovo

FROM Impiegati

WHERE Dipartimento = ‘Mag’;

8) Elenco dei dipendenti (con Cognome, Nome, Residenza, Stipendio) con retribuzione annua minima

di 55000 euro

SELECT Cognome, Nome, Residenza, Stipendio

FROM Impiegati

WHERE Stipendio >= 55000;

9) Interrogazioni parametriche: metto tra parentesi quadre un messaggio (al momento dell’esecuzione

dell’interrogazione all’utente viene richiesto di inserire il valore della retribuzione minima)

SELECT Cognome, Nome, Residenza, Stipendio

FROM Impiegati

WHERE Stipendio >= [Retribuzione minima?];

10) Ricerca di valori mancanti: elenco di tutti i dipendenti senza dipartimento di assegnazione

SELECT ID AS Matricola, Cognome, Nome

FROM Impiegati

WHERE Dipartimento IS NULL; 31

LE OPERAZIONI RELAZIONALI IN SQL

1) PROIEZIONI: SELECT ElencoColonne Proiezione di Impiegati su Cognome, Nome, ID

SELECT Cognome, Nome, ID

FROM Impiegati;

2) SELEZIONI: WHERE CondizioneDiSelezione Selezione di Impiegati per Stipendio < 31000

SELECT *

FROM Impiegati

WHERE Stipendio<31000;

3) CONGIUNZIONE→ EQUI-JOIN: FROM Tabella1, Tabella2

WHERE Condizione che fa corrispondere le righe di una tabella con

quelle dell’altra

Elenco di tutti i dipendenti operando una congiunzione delle tabelle Impiegati e Dipartimenti secondo gli

attributi comuni Dipartimento e Codice

SELECT *

FROM Impiegati, Dipartimenti

WHERE Dipartimento = Codice;

oppure

SELECT *

FROM Impiegati, Dipartimenti

WHERE Impiegati.Dipartimento = Dipartimento.Codice; (esplicito prima del punto le tabelle alle quali

appartengono) →

CONGIUNZIONE INNER JOIN (in Access): SELECT Tabella1.colonne , Tabella2.colonne

FROM Tabella2 INNER JOIN Tabella1 ON Condizione che

fa corrispondere le righe di una tabella con quelle

dell’altra (where)

Elenco di tutti i dipendenti operando una congiunzione delle tabelle Impiegati e Dipartimenti secondo gli

attributi comuni Dipartimento e Codice

SELECT Impiegati.*, Dipartimenti.*

FROM Dipartimenti INNER JOIN Impiegati ON Dipartimento.Codice =

Impiegati.Dipartimento

Elenco degli impiegati che lavorano in un dipartimento con sede a Roma (con Cognome, Nome, Descrizione

dipartimento)

SELECT I.Cognome, I.Nome, D.Descrizione

FROM Impiegati AS I INNER JOIN Dipartimenti AS D ON I.Dipartimento = D.Codice

WHERE D.Sede = ‘Roma’;

(si utilizza AS per abbreviare i comandi e la sua scrittura è opzionale: es. Impiegati AS I oppure Impiegati I) 32

9. LINGUAGGIO SQL – 2^ PARTE

IL COMANDO SELECT

Tutte le clausole del comando SELECT sono:

- SELECT (Elenco di espressioni da mostrare/colonne)

- FROM (Tabelle da cui estrarre le righe)

- WHERE (condizioni sulle congiunzioni e sulle righe da estrarre)

- GROUP BY (campi da considerare per i raggruppamenti)

- HAVING (condizioni sui raggruppamenti)

- ORDER BY (ordinamenti sulle espressioni elencate in SELECT)

SELECT e FROM sono obbligatorie. Si deve rispettare l’ordine mostrato.

FUNZIONI DI AGGREGAZIONE

- Vengono utilizzate solo accanto al comando SELECT e HAVING

- Sintetizzano le informazioni di una colonna in un solo valore

- Tali funzioni sono:

• →conta

COUNT il numero di righe o di valori non nulli in una colonna

→conta

COUNT (*) le righe di una tabella

• →

SUM somma i valori non nulli di una colonna

• →

AVG restituisce la media dei valori non nulli di una colonna

AVG (Stipendio) = SUM (Stipendio)/COUNT (Stipendio)

• →

MIN, MAX restituiscono il valore minimo e massimo di una colonna

ESEMPI

1)

SELECT COUNT (*) Restituisce 12 (12 righe nella tabella Impiegati)

FROM Impiegati;

2)

SELECT COUNT (Dipartimento) Restituisce 11 (11 righe nella colonna Dipartimento)

FROM Impiegati;

3)

SELECT COUNT (*),

COUNT (*) AS ResidentiRoma Restituisce 2

FROM Impiegati (per dare un’intestazione che scegliamo noi)

WHERE Residenza = ‘Roma’; altrimenti si rinomina automaticamente: EXPR1000, ….

4)

SELECT SUM (Stipendio) Restituisce 90000 (somma stipendi degli impiegati nel Dip. Amm)

FROM Impiegati

WHERE Dipartimento = ‘Amm’;

5) (Somma degli stipendi degli impiegati nel Dip. Amm. e somma

SELECT SUM (Stipendio) AS StipendiAmm, dei nuovi stipendi degli impiegati nel Dip. Amm. dopo un

incremento del 3%)

SUM (Stipendio*1.03) AS NuoviStipendiAmm

FROM Impiegati

WHERE Dipartimento = ‘Amm’; 33

(Stipendio medio dei dipendenti che lavorano in dipartimenti

con sede a Torino)

6)

SELECT AVG (Stipendio)

FROM Impiegati, Dipartimento

WHERE Dipartimento = Codice AND Sede = ‘Torino’;

7)

SELECT ROUND (AVG (Stipendio)), Per arrotondare i risultati

ROUND (AVG (Stipendio), 2)

FROM Impiegati, Dipartimenti

WHERE Dipartimento = Codice AND Sede = ‘Torino’;

8)

SELECT MIN (Stipendio), MAX (Stipendio)

FROM Impiegati;

ORDINAMENTI E RAGGRUPPAMENTI (ORDER BY e GROUP BY)

ORDER BY

Serve per ottenere i risultati di un’interrogazione oridnati secondo i valori contenuti in una o più colonne, tra

quelle elencate accanto alla parola SELECT. Ordinato per valori crescenti di Cognome e (a parità di Cognome) per

SELECT Cognome, Nome, Residenza Nome.

FROM Impiegati La parola chiave ASC (crescente) può essere omessa.

ORDER BY Cognome, Nome; La parola chiave DESC (decrescente) è obbligatoria.

SELECT Cognome, Stipendio Ordinato per valori decrescenti di Stipendio e (a parità di Stipendio)

FROM Impiegati per Cognome in ordine crescente.

ORDER BY Stipendio DESC, Cognome; Ordinato per valori decrescenti

SELECT NomeProdotto, PrezUnit, Qta, PrezUnit*qta AS Totale dell’espressione PrezUnit*Qta,

FROM Fatture rinominata come Totale

ORDER BY Totale DESC;

GROUP BY

Serve per raggruppare un insieme di righe aventi lo stesso valore nelle colonne indicate dalla clausola,

applicando le funzioni di aggregazione a singole parti di una colonna

Elenco dei dipartimenti dove lavorano i dipendenti, con la somma degli stipendi e il numero dei dipendenti

per ogni dipartimento

SELECT Dipartimento, COUNT (ID) AS Dipendenti, SUM (Stipendio) AS Stipendi

FROM Impie