TIPI STANDARD DI SQL

BOOLEAN Valori: vero, falso

CHAR(n) Stringa di lunghezza n (fissa)

DATE Data nella forma MM/GG/AA

TIME Ora nella forma HH:MM

INTEGER Numero intero con precisione 10

SMALLINT Numero intero con precisione 5

FLOAT Numero reale con mantissa di precisione 15

Precisione indica il numero max di cifre che il n° può contenere, escluso segno e . (dec)Mantissa indica il numero di cifre che seguono il punto decimale.Le costanti stringa sono delimitate tramite l’apice ‘. Il linguaggio SQL può controllare la presenza e l’assenza di Null in una colonna. Tutti i risultati di confronto numerici o stringa con attributi di valore Null sono sconosciuti.

DBMS (DataBase Management System)

Un DBMS deve essere in grado di:•Permettere la creazione di una nuova base di dati, definendo la composizione degli archivi, la loro articolazione, le correlazioni logiche tra essi, gli accessi ed i vincoli al progetto, mediante il DDL (Data Definition Language);•Facilitare gli utenti nell’inserimento dati, cancellazione e variazione dei dati del DB, sfruttando il DML (Data Manipulation Language);•Rendere possibile l’estrazione di informazioni dal DB mediante il linguaggio di interrogazione QL (Query Language);•Fornire o revocare agli utenti i permessi necessari per poter utilizzare i comandi DML e DDL, usando il linguaggio DCL (Data Control Language).

2

DDL: CREATE TABLE, ALTER T., DROP T.

DML: INSERT INTO TABLE, DELETE, UPDATE … SET

DQL: SELECT, WHERE, SELECT + WHERE

CREATE TABLE

CREATE TABLE Impiegati(ID smallint primary key, Cognome char(15) not null, Nome char(15) not null, Residenza char(15) default ‘*** Manca Residenza’, Stipendio float, Dipartimento char(15) references Dipartimenti(Codice));

UNIQUE (Cognome, Nome, Dipartimento);

Altre costruzioni per le chiavi:

1)Se la chiave primaria è composta da più attributi si mettono alla fine

primary key (Matricola, CodCorso);

2)La chiave esterna è dichiarata come modifica: ALTER

TABLE Impiegati add constraint ffk foreign key (Dipartimento) references Dipartimenti(Codice);

ALTER TABLE

Serve per aggiungere una nuova colonna ed è seguito da ADD:ALTER TABLE ImpiegatiADD AnnoNascita;

Serve per cancellare una colonna ed è seguito da DROP:ALTER TABLE ImpiegatiDROP Residenza;

CREATE INDEXViene utilizzato per creare un nuovo indice su una tabella esistente, che esegue una selezione su uno o più campi specifici.

CREATE UNIQUE INDEX IndiceImpiegatiON (Cognome, Nome);

INSERT, DELETE e UPDATE

Per inserire i valori di una nuova riga in Impiegati:

INSERT INTO Impiegati (ID,Nome,Cognome,Residenza,Stipendio,

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

Per assegnare il dipendente con ID=20 al dipartimento Produzione:UPDATE ImpiegatiSET Dipartimento= ‘Prod’WHERE ID= 20;

Per aumentare del 5% lo stipendio di tutti i dipendenti del Dipart. Produzione:UPDATE ImpiegatiSET Stipendio= Stipendio*1,05WHERE Dipartimento= ‘Prod’;

Per cancellare da Impiegati i dati del dipendente con ID=20:DELETE FROM ImpiegatiWHERE ID= 20;

Query di inserimento: INSERT INTO

INSERT INTO dbase.tabella_dipendenti (IDUser,FullName,Interno) values (3,’Antonio Rossi’, 1234);

UPDATE … SET … WHERE

Questa istruzione serve per modificare i dati in una tabella.

UPDATE Persone SET Nome=‘Paperone’ WHERE Cognome= ‘De Paperoni’;

IDUser FullName Indirizzo Interno

1 Mario Bianchi Via Verdi, 5 7894

2 Carlo Verdi Via Roma, 45 1546

IDUser FullName Indirizzo Interno

1 Mario Bianchi Via Verdi, 5 7894

2 Carlo Verdi Via Roma, 45 1546

3 Antonio Rossi 1234

Query di cancellazione: DELETE

DELETE FROM Dbase.Persone WHERE Cognome= ‘De Paperoni’;

ALIAS

Sono usati per dare nomi ai campi (1) e alle tabelle (2).(1) SELECT campo AS alias_campo FROM tabella;(2) SELECT campo1, campo2 FROM Nometabella AS AliasTabella;

•Query di impiegati (nome, cognome impiegati, descrizione dipartimento) che hanno sede lavorativa a RomaSELECT I.Cognome, I.Nome, D.DescrizioneFROM Impiegati AS I, Dipartimenti AS DWHERE I.Dipartimento = D.Codice AND D.Sede = ‘Roma’;

ISTRUZIONI SQL

SELECT è usata per selezionare dei dati da una tabella.

Sintassi SELECT campo1, campo2, … FROM NomeDB.NomeTabella;

Esempio SELECT id, nome, telefono FROM Dbtelefoni.telefoni;

Per vedere tutti i campi del database SELECT * FROM Dbtelefoni.telefoni;

id nome telefono

1 Pippo 0444/543188

2 Pluto 0444/991761

3 Paperino 0444/746111

4 Topolino 0444/519922

id nome indirizzo telefono

1 Pippo Via Rossi, 4 0444/543188

2 Pluto Via Piave, 11 0444/991761

3 Paperino Via Roma, 1 0444/746111

4 Topolino Via Verdi, 6 0444/519922

ISTRUZIONI SQL

SELECT DISTINCT è utilizzata per restituire soltanto dei valori distinti.

SELECT Società FROM Ordini;

Mentre SELECT DISTINCT Società FROM Ordini;

Società

Microsoft

Oracle

Microsoft

HP

Società

Microsoft

Oracle

HP

ORDER BY

È possibile ordinare l’ elenco ottenuto con il comando ORDER BY NomeCampo.

SELECT id, nome, telefono FROM Dbtelefoni.telefoni ORDER BY nome;

SELECT id, nome, telefono FROM Dbtelefoni.telefoni ORDER BY nome DESC;

id nome telefono

3 Paperino 0444/746111

1 Pippo 0444/543188

2 Pluto 0444/991761

4 Topolino 0444/519922

id nome telefono

4 Topolino 0444/519922

2 Pluto 0444/991761

1 Pippo 0444/543188

3 Paperino 0444/746111

WHERE

SELECT id, nome, telefono FROM Dbtelefoni.telefoni WHERE nome= ‘Topolino’

ORDER BY nome DESC;

SELECT id, nome, telefono FROM Dbtelefoni.telefoni WHERE nome= ‘Topolino’ AND id >= 4;

id nome telefono

4 Topolino 0444/519922

id nome telefono

4 Topolino 0444/519922

9 Topolino 0444/214569

WHERE

SELECT id, nome, telefono FROM Dbtelefoni.telefoni WHERE nome= ‘Topolino’ OR id >= 4;

Da notare che se il valore che viene cercato è una stringa va inserito tra apici.

SELECT id, nome, telefono FROM Dbtelefoni.telefoni WHERE nome= Topolino OR id >= 4;restituirebbe un errore perché Topolino è una stringa.

BETWEEN … ANDSeleziona un intervallo di dati tra due valori, che possono essere numeri, testo, date. SELECT * FROM telefoni WHERE id BETWEEN 4 AND 22; oppureSELECT * FROM telefoni WHERE id NOT BETWEEN 4 AND 22;

id nome telefono

4 Topolino 0444/519922

45 Paperoga 0444/124589

9 Topolino 0444/214569

12 Clarabella 0444/927856

LIKE

SELECT id, nome FROM Dbtelefoni.telefoni WHERE nome LIKE ‘%ert%’;

•SELECT id, nome FROM Dbtelefoni.telefoni WHERE nome LIKE ‘Pa%’;Cerca quello che inizia con Pa e finisce con qualche altra.

•SELECT id, nome FROM Dbtelefoni.telefoni WHERE nome LIKE ‘_upa’ Cerca le stringhe di 4 caratteri che finiscono con ‘upa’.Invece:•SELECT id, nome FROM Dbtelefoni.telefoni WHERE nome LIKE ‘Anna_M%’ ESCAPE In questo caso l’ underscore ‘_’ non è sostituto di un carattere ma è proprio _, quindi metto ESCAPE.

id nome

405 Roberto

421 Alberto

437 Roberto

783 Umberto

ALTRE CONDIZIONI DEL WHERE

IN Controlla se un valore appartiene ad uno dei valori della lista precisata dopo In all’interno della condizione.

INTO È usato per indicare i valori dentro una selezione.

IS NULL Controlla la presenza del valore null in una colonna.

IS NOT NULL Per valutare la condizione opposta alla precedente.

GROUP BY Permette di raggruppare un insieme di righe aventi lo stesso valore nelle colonne indicate dalla clausola.*

HAVING (con il Group by): dopo l’having, è controllato il valore restituito dalle funzioni di aggregazione.

TOP Restituisce le prime n righe di una tabella.

AND È applicato prima di OR.

OR

NOT È applicato prima di AND.

*: tutti gli attributi che compaiono nella lista accanto al Select devono essere inclusi nel Group by oppure devono essere argomenti di una funzione di aggregazione.

FUNZIONI DI AGGREGAZIONE

SELECT funzione(campo) FROM NomeTabella;

Funzione Descrizione

AVG (coloumn) Restituisce il valore medio di una colonna

COUNT (coloumn) Restituisce il numero di righe (escludendo quelle con valore null) di una colonna

COUNT (*) Restituisce il numero di righe selezionate

FIRST (coloumn) Restituisce il valore del primo record di un campo specificato

LAST (coloumn) Restituisce il valore dell’ultimo record di un campo specificato

MAX [or MIN](coloumn) Restituisce il valore massimo [o minimo] in una colonna

SUM (coloumn) Restituisce la somma dei valori di una colonna

QUERY ANNIDATE

Si chiamano così perché il comando Select offre la possibilità di inserire un comando Select all’interno della struttura di un altro comando Select.

•Query dei clienti (nome, cognome, reddito) che hanno il reddito massimoSELECT nome, cognome, redditoFROM Clienti WHERE (reddito= SELECT MAX (reddito)

FROM Clienti));

•Query dei clienti (nome, cognome, reddito) che abitano in città che hanno almeno 2 clientiSELECT nome, cognome, reddito FROM ClientiWHERE città IN (SELECT città

FROM Clienti GROUP BY città HAVING COUNT (*) >= 2);

QUERY ANNIDATE

ANY è utilizzato in una clausola Where in espressioni : x > ANY Elenco.

•Query dei dipendenti (nome, cognome) del Dipartimento Amministrazione che hanno lo stipendio superiore a quello di almeno un impiegato del MKTSELECT nome, cognomeFROM Impiegati WHERE Dipartimento= ‘Amm’ AND Stipendio > ANY (SELECT stipendio

FROM ImpiegatiWHERE Dipartimento= ‘Mkt’ );

ALL è usato in una clausola Where in espressioni : x <= ALL Elenco.

•Query dei dipendenti (nome, cognome) dell’ Amministrazione che hanno lo stipendio superiore a quello di tutti i dipendenti del MKTSELECT nome, cognome FROM Impiegati WHERE Dipartimento= ‘Amm’ AND Stipendio > ALL (SELECT stipendio

FROM ImpiegatiWHERE Dipartimento= ‘Mkt’ );

QUERY ANNIDATE

Valgono le seguenti equivalenze:Attributo IN (Select…) equivale a: Attributo = ANY (Select…)Attributo NOT IN (Select…) equivale a: Attributo <> ALL (Select…)

EXISTS è utilizzato in una clausola Where in espressioni : EXISTS Elenco.

•Query dei dipendenti (nome, cognome, scostamento dalla media degli stipendi) del MKT, solo se esistono dipendenti che lavorano in quel dipart.SELECT nome, cognome, stipendio – (SELECT AVG(stipendio)

FROM Impiegati WHERE Dipartimento= ‘Mkt’)

FROM ImpiegatiWHERE EXISTS (SELECT *

FROM Impiegati WHERE Dipartimento= ‘Mkt’ );