Structured Query Language: SQL

Structured Query Language: SQL Maurizio Fermeglia Università di Trieste email: mauf@dicamp.univ.trieste.it

SQL • Structured Query Language • è un linguaggio con varie funzionalità: • contiene sia il DDL sia il DML; • esistono varie versioni dell’SQL; • vediamo gli aspetti essenziali non i dettagli

La storia di SQL • Dr. Codd + IBM = SYSTEM/R=SEQUEL (1970-74) • ORACLE (1979) • Berkeley + SQL = INGRES (1980) • IBM: SQL/DS e DB2 (1983) • dal 1983 ca., “standard di fatto” • ANSI SQL1 Standard (1986) • Sybase esce con RDBMS (1986) • SQL Access group pubblica le specifiche ODBC (1991) • ANSI SQL2 Standard (1992) • ODBC standard in 1993 • 1996 specifiche interrogazione OLAP • 1999 standard SQL3 SQL 99(… scarso interesse industriale) • 200n SQL 200n nuovo che introduce tante novità compreso SQL XML • SQLJ standard SQL all‘interno di programmi Java

Metadata Open Forum Standard SQL The following is a short, incomplete history of the SQL Standards – ISO/IEC 9075 • 1987 – Initial ISO/IEC Standard • 1989 – Referential Integrity • 1992 – SQL2 • 1995 SQL/CLI (ODBC) • 1996 SQL/PSM – Procedural Language extensions • 1999 – User Defined Types • 2003 – SQL/XML • 2008 – Expansions and corrections • 2011 (or 2012) System Versioned and Application Time Period Tables

SQL standard 1 2

SQL 99 standard: carattersitiche

SQL standard ‘in fieri’ • SQL 2003: introduce l’uso di XML (poche funzionalità), funzioni di finestre, valori autogenerati (colonne identity) • SQL 2006: introduce Xquery (ISO/IEC 9075-14:2006 ) e completa l’uso di XML: import e memorizzazioen di XML in DB, manipolazione di XML. • SQL:2008 Legalizza uso di ORDER BY al di fuori della definizione dei corsori. Aggiunge INSTEAD OF nei triggers. Aggiunge TRUNCATE statement • Interessante link: http://troels.arvin.dk/db/rdbms/

SQL CONTROLLA TUTTE LE FUNZIONI DI DBMS • Definizione dei dati (struttura e relazioni) • Estrazione dei dati • Manipolazione di dati • Controllo degli accessi (privilegi di accesso) • Condivisione dei dati • Integrità dei dati DBMS SQL Query Data Base Dati

Il ruolo di SQL (che cosa è) • Linguaggio di Query interattivo • Linguaggio di programmazione per data base • Linguaggio di amministrazione di data base • Linguaggio per il client/server • Linguaggio per la gestione di data base distribuiti • Linguaggio per la gestione di gateway

CARATTERISTICHE E BENEFICI.... • Indipendenzadaivenditori di HW e SW • Portabilitàattraversovariepiattoforme HW • Coperto da standard internazioneli SQL1 ed SQL2 e SQL3 • Strategico per IBM – Oracle – Micrsoft - … • Linguaggio per data base relazionali (unico) • Strutturo ad alto livello (English-like) • Linguaggiointerattivo • Linguaggioprogrammatico (Statico - Dinamico - API) • In grado di fornireviste diverse del data base • Linguaggiocompleto (IF, triggers, ...) con T SQL e PL SQL • Definizionedinamicadeidati (anchequando in uso) • Client / server

SQL è strategico per molti Hw e Sw

IL MITO DELLA PORTABILITA’ • Ovvero ... non e’ verochesipuo fare tutto • Codici di errore non sono standard • I tipi di dati non sonesempresupportati • Le tabelle di sistema non sonouguali • Gli standard definiscono solo il SQL via programma, non l’interattivo • Definisce solo lo Static Embedded Standard • Alcunedifferenzesemantiche (funzioni, NULL) • Sorting secondo ASCII o EBCDIC (collating sequence e codici) • Strutturadelletabelle

SQL LE BASI • Statements di manipolazione dei dati (DML) • SELECT - INSERT - DELETE - UPDATE • Statements di definizione dei dati (DDL) • CREATE/DROP/ALTER TABLE, VIEW, INDEX • Statements di accesso • GRANT e REVOKE • Statements di per processi transazionali • COMMIT - ROLLBACK • Statements programmatici • DECLARE - OPEN - FETCH - CLOSE

SQL: Le convenzioni • Sui NOMI • Sui TIPI DI DATI (interi - testo ,...) • Su COSTANTI • Su ESPRESSIONI • Su FUNZIONI

Definizione dei dati in SQL • Istruzione CREATE TABLE: • definisce uno schema di relazione e ne crea un’istanza vuota • specifica attributi, domini e vincoli

CREATE TABLE, esempio CREATE TABLE Impiegato( Matricola CHAR(6) PRIMARY KEY, Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, Dipart CHAR(15), Stipendio NUMERIC(9) DEFAULT 0, FOREIGN KEY(Dipart) REFERENCES Dipartimento(NomeDip), UNIQUE (Cognome,Nome) )

Domini • Domini elementari (predefiniti) • Domini definitidall'utente (semplici, mariutilizzabili) • Domini elementari • Carattere: singoli caratteri o stringhe, anche di lunghezza variabile • Bit: singoli booleani o stringhe • Numerici, esatti e approssimati • Data, ora, intervalli di tempo • Introdotti in SQL:1999: • Boolean • BLOB, CLOB (binary/character large object): per grandi immagini e testi

Definizione di domini • Istruzione CREATE DOMAIN: • definisce un dominio (semplice), utilizzabile in definizioni di relazioni, anche con vincoli e valori di default CREATE DOMAIN, esempio CREATE DOMAIN Voto AS SMALLINT DEFAULT NULL CHECK ( value >=18 AND value <= 30 )

Vincoli intrarelazionali • NOT NULL • UNIQUE definiscechiavi • PRIMARY KEY: chiaveprimaria (unasola, implica NOT NULL) • CHECK, vedremo più avanti UNIQUE e PRIMARY KEY • due forme: • nella definzione di un attributo, se forma da solo la chiave • come elemento separato

PRIMARY KEY, alternative Matricola CHAR(6) PRIMARY KEY Matricola CHAR(6),…,PRIMARY KEY (Matricola)

Chiavi su più attributi, attenzione Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, UNIQUE (Cognome,Nome), Nome CHAR(20) NOT NULL UNIQUE, Cognome CHAR(20) NOT NULL UNIQUE, • Non è la stessa cosa!

Vincoli interrelazionali • CHECK, vedremo più avanti • REFERENCESeFOREIGNKEY permettono di definire vincoli di integrità referenziale • di nuovo due sintassi • per singoli attributi • su più attributi • E' possibile definire politiche di reazione alla violazione

Infrazioni Codice Data Vigile Prov Numero 34321 1/2/95 3987 MI 39548K 53524 4/3/95 3295 TO E39548 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 PR 839548 Vigili Matricola Cognome Nome 3987 Rossi Luca 3295 Neri Piero 9345 Neri Mario 7543 Mori Gino

Infrazioni Codice Data Vigile Prov Numero 34321 1/2/95 3987 MI 39548K 53524 4/3/95 3295 TO E39548 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 PR 839548 Auto Prov Numero Cognome Nome MI 39548K Rossi Mario TO E39548 Rossi Mario PR 839548 Neri Luca

CREATE TABLE, esempio CREATE TABLE Infrazioni( Codice CHAR(6) NOT NULL PRIMARY KEY, Data DATE NOT NULL, Vigile INTEGER NOT NULL REFERENCES Vigili(Matricola), Provincia CHAR(2), Numero CHAR(6) , FOREIGN KEY(Provincia, Numero) REFERENCES Auto(Provincia, Numero) )

Modifiche degli schemi ALTER DOMAIN ALTER TABLE DROP DOMAIN DROP TABLE ...

Definzione degli indici • è rilevante dal punto di vista delle prestazioni • ma è a livello fisico e non logico • in passato era importante perché in alcuni sistemi era l'unico mezzo per definire chiavi • CREATE INDEX • Vedremo dettagli nella progettazione fisica

DDL, in pratica • In molti sistemi si utilizzano strumenti diversi dal codice SQL per definire lo schema della base di dati

SQL, operazioni sui dati • interrogazione: • SELECT • modifica: • INSERT, DELETE, UPDATE

Istruzione SELECT (versione base) SELECT ListaAttributi FROM ListaTabelle [ WHERE Condizione ] • "target list" • clausola FROM • clausola WHERE

selezione proiezione

Persone Maternità Madre Figlio Nome Età Reddito Luisa Maria Andrea 27 21 Luisa Luigi Aldo 25 15 Anna Olga Maria 55 42 Anna Filippo Anna 50 35 Maria Andrea Filippo 26 30 Maria Aldo Luigi 50 40 Paternità Padre Figlio Franco 60 20 Sergio Franco Olga 30 41 Luigi Olga Sergio 85 35 Luigi Filippo Luisa 75 87 Franco Andrea Franco Aldo

Selezione e proiezione • Nome e reddito delle persone con meno di trenta anni PROJNome, Reddito(SELEta<30(Persone)) select nome, reddito from persone where eta < 30

Persone Nome Età Reddito Andrea Andrea Andrea 27 27 27 21 21 21 Reddito Aldo Aldo Aldo 25 25 25 15 15 15 21 Maria 55 42 15 Anna 50 35 30 Filippo Filippo Filippo 26 26 26 30 30 30 Luigi 50 40 Franco 60 20 Olga 30 41 Sergio 85 35 Luisa 75 87

SELECT, abbreviazioni select nome, reddito from persone where eta < 30 select p.nome as nome, p.reddito as reddito from persone p where p.eta < 30

Selezione, senza proiezione • Nome, età e reddito delle persone con meno di trenta anni SELEta<30(Persone) select * from persone where eta < 30

SELECT, abbreviazioni select * from persone where eta < 30 select nome, età, redditofrom persone where eta < 30

Proiezione, senza selezione • Nome e reddito di tutte le persone PROJNome, Reddito(Persone) select nome, reddito from persone

SELECT, abbreviazioni • R(A,B) select * from R equivale (intuitivamente) a select X.A as A, X.B as B from R X where true

Espressioni nella target list select Reddito/2 as redditoSemestralefrom Personewhere Nome = 'Luigi'

Condizione complessa select *from personewhere reddito > 25 and (eta < 30 or eta > 60)

Condizione “LIKE” • Le persone che hanno un nome che inizia per 'A' e ha una 'd' come terza lettera select * from persone where nome like 'A_d%'

Matricola Cognome Filiale Età 7309 Rossi Roma 32 5998 5998 9553 9553 9553 5998 Neri Bruni Neri Bruni Neri Bruni Milano Milano Milano Milano Milano Milano NULL 45 NULL 45 45 NULL Gestione dei valori nulli • Gli impiegati la cui età è o potrebbe essere maggiore di 40 Impiegati SEL Età > 40 OR Età IS NULL (Impiegati)

Gli impiegati la cui età è o potrebbe essere maggiore di 40 SEL Età > 40 OR Età IS NULL (Impiegati) select * from impiegati where eta > 40 or eta is null

Selezione, proiezione e join • Istruzioni SELECT con una sola relazione nella clausola FROM permettono di realizzare: • selezioni, proiezioni, ridenominazioni • con più relazioni nella FROM si realizzano join (e prodotti cartesiani)

SQL: esecuzione delle interrogazioni • Le espressioni SQL sono dichiarative e noi ne stiamo vedendo la semantica • In pratica, i DBMS eseguono le operazioni in modo efficiente, ad esempio: • eseguono le selezioni al più presto • se possibile, eseguono join e non prodotti cartesiani

SQL: speciifca delle interrogazioni • La capacità dei DBMS di "ottimizzare" le interrogazioni, rende (di solito) non necessario preoccuparsi dell'efficienza quando si specifica un'interrogazione • È perciò più importante preoccuparsi della chiarezza (anche perché così è più difficile sbagliare …)

Matricola Cognome Filiale Stipendio 7309 Neri Napoli 55 5998 9553 5698 Rossi Neri Rossi Roma Milano Roma 64 64 44 Proiezione, attenzione • cognome e filiale di tutti gli impiegati PROJ Cognome, Filiale (Impiegati)

Structured Query Language: SQL