G. Mecca – [email protected] – Università della Basilicata Basi di Dati SQL-92 Concetti...

G. Mecca – [email protected] – Università della BasilicataG. Mecca – [email protected] – Università della Basilicata

Basi di Dati

SQL-92Concetti Fondamentali

versione 2.0

Questo lavoro è concesso in uso secondo i termini di una licenza Creative Commons (vedi ultima pagina)

2G. Mecca - [email protected] - Basi di DatiG. Mecca - [email protected] - Basi di Dati

Concetti Fondamentali

Introduzione Creazione ed

eliminazione di bd Creazione ed

eliminazione di tabelle

Inserimenti di ennuple

Interrogazioniclausola SELECTclausola FROMclausola WHEREclausola ORDER BYmetodo di scrittura

Cancellazioni Aggiornamenti

SQL-92 >> Sommario


Introduzione

SQL (“Structured Query Language”)linguaggio per l’interazione con il DBMStutte le operazioni vengono specificate in SQL

DDL (“Data Definition Language”)creazione degli oggetti dello schema

DCL (“Data Control Language”)controllo degli utenti e delle autorizzazioni

DML (“Data Manipulation Language”)manipolazione dell’istanza della base di dati

(interrogazioni e aggiornamenti)

SQL-92 >> Concetti Fondamentali >> Introduzione


Storia dello Standard

Prime implementazioniIBM System/R 1979 (SEQUEL)

Primi prodotti commercialiIBM SQL/DS, Oracle 1981

SQL-86 prima versione dello standard, basata sul

dialetto IBM



SQL-92, full

SQL-92, intermediate

SQL-92, entry


SQL-89 (SQL-1)vincoli di integritàlivello1 e livello2

SQL-92 (SQL-2)entryintermediatefull


SQL-89, livello 2

SQL-89, livello 1



Standard collegati SQL/CLI

“Call Level Interface” (ODBC), 1995 SQL/PSM

“Persistent Storage Modules”, 1997 SQL/OLB

“Object Language Bindings”, 1998




SQL:1999 (SQL-3)estensioni “object-

relational”core: tutto SQL-92

entry, (quasi) tutto SQL-92 intermediate, parte di SQL-92 full

packages Attualmente:

lavori su SQL:200x


SQL-92, full

SQL:1999, core


SQL-92, entry

SQL-89, livello 2

SQL-89, livello 1



Implementazioni dello standardgli standard hanno in alcuni casi inseguito la

tecnologia di mercato Corrispondenza imperfetta

non esiste nessuna implementazione completa in commercio di SQL-92 full

molti DBMS offrono funzionalità “proprietarie” e non standard (es: parte di SQL:1999)

problema di portabilità delle applicazioni




Tipico DBMS sul mercato oggiessenzialmente SQL-

92 intermediatelimitate funzionalità di

SQL-92 full parte di SQL:1999

In questo corsoci concentriamo su



SQL-92, full

SQL:1999, core


SQL-92, entry

SQL-89, livello 2

SQL-89, livello 1

tipico DBMS commerciale


Le Istruzioni Fondamentali

DDLcreazione della base

di datiCREATE DATABASE

creazione delle tabelleCREATE TABLE

DMLinserimento delle

ennupleINSERT INTO

interrogazioniSELECT

eliminazione delle ennuple DELETE

modifica della ennupleUPDATE



Creazione ed Eliminazione di BD

Istruzioni del DDL Sintassi

CREATE DATABASE <nome>;DROP DATABASE <nome>;

EsempioCREATE DATABASE universita;

DROP DATABASE universita;

SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di BD


Creazione ed Eliminazione di BD

SemanticaCREATE DATABASE

crea una nuova base di dati vuota l’utente deve essere autorizzatol’utente diventa il proprietario della bd

DROP DATABASEelimina una base di dati esistente (anche non

vuota)l’utente deve essere autorizzato

SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di BD


Creazione ed Eliminazione di Tabelle Istruzioni del DDL

CREATE TABLEDROP TABLE

SintassiCREATE TABLE <nome> (<schema>);DROP TABLE <nome>;

SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di Tabelle


Creazione ed Eliminazione di Tabelle Esempio: la tabella Professori

CREATE TABLE Professori ( cod char(4) PRIMARY KEY, cognome varchar(20) NOT NULL, nome varchar(20) NOT NULL, qualifica char(15), facolta char(10) );

DROP TABLE Professori;



Creazione ed Eliminazione di Tabelle Esempio: la tabella Esami

CREATE TABLE Esami ( studente integer

REFERENCES Studenti(matr) ON DELETE cascade ON UPDATE cascade, corso char(3) REFERENCES Corsi(cod), voto integer, lode bool, CHECK (voto>=18 and voto<=30), CHECK (not lode or voto=30), PRIMARY KEY (studente, corso));



Creazione ed Eliminazione di Tabelle <schema>

una o più definizioni di attributozero o più definizioni di vincoli di tabella

Definizione di attributo<nomeattributo> <tipo> [<vincoli di colonna>]

Definizioni di vincoli di tabellanormalmente vincoli relativi a più attributi



Creazione ed Eliminazione di Tabelle <nomeattributo>

identificatore <tipo>

INT, INTEGERREAL, FLOATDECIMAL(lung,dec)DOUBLE PRECISIONCHAR(n),

CHARACTER(n)VARCHAR(n)

LONG VARCHAR, TEXT

BOOLEAN, BOOLDATETIMETIMESTAMPBINARY(n), BIT(n)VARBINARY(n),

VARBIT(n)LONG VARBINARY,

BLOB



Creazione ed Eliminazione di Tabelle Vincoli di colonna

PRIMARY KEYUNIQUENOT NULLREFERENCES <chiave della tabella dest.>

[ON update CASCADE][ON delete CASCADE]

CHECK (<espressione>)



Creazione ed Eliminazione di Tabelle Vincoli di tabella (su più attributi)

PRIMARY KEY (<lista attributi>)UNIQUE (<lista attributi>)FOREIGN KEY (<lista attributi>)

REFERENCES <chiave esterna> [ON update CASCADE] [ON delete CASCADE]

CHECK (<espressione>)



Creazione ed Eliminazione di Tabelle Semantica

CREATE TABLEcrea una nuova tabella vuota secondo lo schema

specificato l’utente deve essere autorizzatol’utente diventa il proprietario della tabellaattenzione ai vincoli di riferimento

DROP TABLEelimina una tabella esistente l’utente deve essere autorizzato




CREATE TABLE Studenti ( matr integer PRIMARY KEY, cognome varchar(20) NOT NULL, nome varchar(20) NOT NULL, ciclo char(20), anno integer, relatore char(4) REFERENCES Professori(cod));


CREATE TABLE Tutorato ( studente integer REFERENCES Studenti(matr), tutor integer REFERENCES Studenti(matr), PRIMARY KEY (studente,tutor));

CREATE TABLE Esami ( studente integer REFERENCES Studenti(matr) ON DELETE cascade ON UPDATE cascade, corso char(3) REFERENCES Corsi(cod), voto integer, lode bool, CHECK (voto>=18 and voto<=30), CHECK (not lode or voto=30), PRIMARY KEY (studente, corso));

CREATE TABLE Corsi ( cod char(3) PRIMARY KEY, titolo varchar(20) NOT NULL, ciclo char(20), docente char(4) REFERENCES Professori(cod) );

CREATE TABLE Numeri ( professore char(4) REFERENCES Professori(cod), numero char(9), PRIMARY KEY (professore,numero));


Inserimenti

Istruzione del DMLINSERT

SintassiINSERT INTO <tabella> VALUES (<valori>);

Semanticainserimento della ennupla nella tabellacorrispondenza ordinata tra valori e attributi

(notazione posizionale)

SQL-92 >> Concetti Fondamentali >> Inserimenti


Inserimenti

Esempi:INSERT INTO Professori VALUES

(‘FT’, ‘Totti’, ‘Francesco’, ‘ordinario’, ‘Ingegneria’);

INSERT INTO Studenti VALUES(111, ‘Rossi’, ‘Mario’, ‘laurea tr.’, 3, null);

INSERT INTO Corsi VALUES (‘PR1’, ‘Programmazione 1’, ‘laurea tr.’, ‘FT’);

SQL-92 >> Concetti Fondamentali >> Inserimenti


Interrogazioni

Istruzione del DMLSELECTsintassi concreta per specificare operatori

dell’algebra Filosofia

parzialmente dichiarativasi specificano gli operatori da applicare, non

l’ordine in cui devono essere applicatil’ottimizzatore sceglie la strategia ottima

SQL-92 >> Concetti Fondamentali >> Interrogazioni


Interrogazioni

Tre “clausole” principali (nucleo)SELECT, FROM, WHERE

SELECTproiezioni, ridenominazioni, elimin. duplicati

FROMtabelle, join, prodotti cartesiani, alias

WHEREselezioni



Esempi

“Studenti della laurea triennale di anni successivi al primo”


ciclo=‘laurea tr.’ AND anno>1 (Studenti)Risultato =

SELECT *FROM StudentiWHERE ciclo=‘laurea tr.’ AND anno>1;


Esempi

“Matricola e cognome degli studenti che hanno sostenuto l’esame di informatica teorica”


SELECT matricola, cognome

Risultato = matricola, cognome ( titolo=‘Inform. t.’ ( Studenti matr=studente Esami cod=corso Corsi ))

FROM Studenti JOIN Esami ON matr=studente JOIN Corsi ON cod=corso

WHERE titolo=‘Inform. t.’;


Interrogazioni

Per sistematizzarepartiamo dalla forma standard dell’algebra

relazionale (nella versione più generale)mostriamo come le clausole della SELECT si

possono mettere in corrispondenza con gli operatori della forma standard

questo ci suggerirà anche un metodo per scrivere le interrogazioni SQL



Interrogazioni

Forma standard dell’algebrauna o più sottointerrogazionicorrelate da operatori insiemistici

Sottointerrogazionistrategia a: prodotti cartesiani tra le tabelle

(con eventuali alias)strategia b: join tra le tabelle (con eventuali

alias)


>>


Interrogazioni

Sottointerrogazioni (Continua)selezioniproiezioni (con funzioni aggregative)eliminazione di duplicati (DISTINCT)ridenominazioniordinamenti finali (ORDER BY)



Interrogazioni


U oppure ∩ oppure –

Sottointerrogazione

Sottointerrogazione

ORDER BYDISTINCT

TABELLE, con X oppure ed ev. ALIAS


Interrogazioni

Interrogazioni SQLuna o più sottointerrogazioni con eventuali operatori insiemistici

Per ogni sottointerrogazioneSELECT: proiezioni, ridenominazioni, distinctFROM: prodotti cartesiani o join, alias[WHERE]: selezioni (opzionale)[ORDER BY]: ordinamenti (opzionale)



Interrogazioni


UNION, INTERSECT, EXCEPT

Sottointerrogazione

Sottointerrogazione

ORDER BY

FROM

WHERE

SELECT

U oppure ∩ oppure –

Sottointerrogazione

Sottointerrogazione

ORDER BY

DISTINCT

tabelle con X o


Esempi

“Studenti della laurea triennale di anni successivi al primo”


ciclo=‘laurea tr.’ AND anno>1 (Studenti)Risultato =

SELECT *FROM StudentiWHERE ciclo=‘laurea tr.’ AND anno>1;

NOTA: le clausole vanno scritte nell’ordine in cui compaionoprima SELECT, poi FROM, poi WHERE, poi ORDER BY


Esempi

“Matricola e cognome degli studenti che hanno sostenuto l’esame di informatica teorica”


SELECT matricola, cognome

Risultato = matricola, cognome ( titolo=‘Inform. t.’ ( Studenti matr=studente Esami cod=corso Corsi ))

FROM Studenti JOIN Esami ON matr=studente JOIN Corsi ON cod=corso

WHERE titolo=‘Inform. t.’;


Clausola FROM

Serve a specificarele tabelle da cui prelevare i datigli eventuali prodotti cartesianigli eventuali joingli eventuali alias

Prodotti cartesiani: sintassiFROM R, S, T AS V …es: FROM Professori, NumeriFROM Professori, Numeri



Clausola FROM

Join: SintassiFROM S JOIN R ON S.A=R.B JOIN T AS V..es: FROM Professori JOIN Numeri ONFROM Professori JOIN Numeri ON cod=professore cod=professore

Semanticaapplicazione degli operatori corrispondenticostruisce la tabella unica a cui applicare gli

operatori successivi



Clausola WHERE

Serve a specificarele selezioni

SintassiWHERE <condizione>

Dove<condizione> è una condizione composta di

sottocondizioni di selezione, collegate con connettivi booleani (AND, OR, NOT)

sintassi identica alle condizioni dell’algebra



Clausola WHERE

Notapossono comparire solo attributi di tabelle

che compaiono nella clausola FROM Esempio FROM ProfessoriFROM ProfessoriWHERE qualifica=‘ordinario’ WHERE qualifica=‘ordinario’ AND facolta IS NOT NULL AND facolta IS NOT NULL

Semanticaapplica le selezioni alle tabelle della FROM



Clausola SELECT

Serve a specificareproiezionifunzioni aggregativeridenominazionieliminazione di duplicati

Notaanche in questo caso è possibile specificare

solo attributi di tabelle che compaiono nella clausola FROM



Clausola SELECT

SintassiSELECT [DISTINCT] <attributi>

Dove<attributi> è una lista di nomi di attributocon eventuali ridenominazioni nella forma

<vecchioNome> AS <nuovoNome> Semantica

applicare gli operatori corrispondenti alle tabelle della FROM



Clausola SELECT

EsempiSELECT cognome, nomeSELECT cognome, nomeFROM ProfessoriFROM Professori

SELECT DISTINCT cognome, nomeSELECT DISTINCT cognome, nomeFROM ProfessoriFROM Professori

SELECT cognome AS cognomeProf, SELECT cognome AS cognomeProf, nome AS nomeProf nome AS nomeProfFROM ProfessoriFROM Professori



Clausola SELECT

Notala clausola SELECT è obbligatoriache succede se non ci sono proiezioni e

ridenominazioni ? SELECT *

non è necessario effettuare nè proiezioni nè ridenominazioni



Clausola ORDER BY

Serve a specificareordinamenti finali

SintassiORDER BY <attributi>

Dove<attributi> è una lista di attributi di tabelle

che compaiono nella clausola FROM



Clausola ORDER BY

Esempio SELECT *

FROM Studenti

ORDER BY cognome, annoCorso

Normalemente:l’ordinamento è crescente

Ordinamento decrescente: DESCORDER BY cognome, annoCorso DESC



Esempi

“Cognomi e nomi degli studenti”


SELECT DISTINCT cognome, nome

cognome, nomeDISTINCT ( (Studenti))ElencoNomi =

FROM Studenti;


Esempi

“Cognomi e nomi degli studenti, in ordine alfabetico”


SELECT DISTINCT cognome, nome

cognome, nomeDISTINCT ( (Studenti)))

ElencoNomi = ORDER BY cognome, nome (

FROM StudentiORDER BY cognome, nome;


Esempi

“Voto medio riportato negli esami”


SELECT AVG(voto)

Risultato = AVG(voto) (Esami)

FROM Esami;


Esempi

“Cognomi, nomi e numeri di telefono dei professori” (strategia a)


SELECT cognome, nome, numero

cognome, nome, numero (ProfessoriENumeri =

cod=professore (

Professori X Numeri ))

FROM Professori, NumeriWHERE cod=professore;


Esempi

“Cognomi, nomi e numeri di telefono dei professori” (strategia b)


SELECT cognome, nome, numero

cognome, nome, numero (ProfessoriENumeri =

Professori cod=professore Numeri)

FROM Professori JOIN Numeri ON cod=professore;


Esempi

“Cognome e nome delle persone”


SELECT cognome AS cognomePersona, nome AS nomePersonaFROM Professori

Risultato = cognome AS cognomePersona, nome AS nomePersona (

cognome, nome (Professori))

U cognome, nome (Studenti)

SELECT cognome, nomeFROM Studenti;

UNION


Esempi

“Cognome e nome dei professori ordinari che non supervisionano tesi triennali”


Risultato = cognome AS cognomeProf, nome AS nomeProf(

cognome, nome (

qualifica = ‘Ordinario’ (Professori))

–cognome, nome (

ciclo = ‘laurea tr.’ (Studenti relatore = cod Professori ))


Esempi

“Cognome e nome dei professori ordinari che non supervisionano tesi triennali”


SELECT cognome AS cognomeProf, nome AS nomeProfFROM ProfessoriWHERE qualifica=‘ordinario’

SELECT cognome, nomeFROM Studenti JOIN Professori ON relatore=codWHERE ciclo=‘laurea tr.’;

EXCEPT


Esempi

“Cognomi e nomi degli studenti che all’esame di Programmazione hanno riportato un voto superiore a quello dei loro tutor”

Tabelle coinvolteStudenti, EsamiTutorato, Esami AS EsamiTutor



Esempi

“Studenti e tutor” (continua)


Risultato = cognome, nome (

Esami.corso=‘Pr1’ AND EsamiTutor.corso=‘Pr1’ AND Esami.voto > EsamiTutor.voto (

Tutorato.tutor=EsamiTutor.studente (Esami AS EsamiTutor)))

matr=Tutorato.studente Tutorato

Studenti matr=studente Esami


Esempi

“Studenti e tutor” (continua)


SELECT cognome, nomeFROM Studenti JOIN Esami ON matr=studente JOIN Tutorato ON matr=Tutorato.studente JOIN Esami AS EsamiTutor ON Tutorato.tutor = EsamiTutor.studenteWHERE Esami.corso=‘Pr1’ AND EsamiTutor.corso=‘Pr1’ AND Esami.voto > EsamiTutor.voto;


Interrogazioni

Notauna interrogazione particolareSELECT * FROM R;

es: SELECT * FROM Corsi;SELECT * FROM Corsi;preleva i dati dalla tabella R senza applicare

nessun operatoreè il modo con cui in un DBMS SQL si

visualizza interattivamente il contenuto di una tabella



Interrogazioni: Metodo di Scrittura

Scrivere l’interrogazione in algebra relazionale utilizzando la forma standard

Tradurre gli operatori nella sintassi di SQL Bisogna scegliere tra

strategia a: prodotti cartesianistrategia b: joinin generale è più efficiente la seconda



Cancellazioni

Istruzione del DMLDELETE

SintassiDELETE FROM <tabella>

[<clausola WHERE>];<clausola WHERE>: identica a quella vista

Semanticaelimina dalla tabella tutte le ennuple (che

soddisfano la condizione se è specificata)

SQL-92 >> Concetti Fondamentali >> Cancellazioni


Cancellazioni

Esempi:

DELETE FROM Numeri;

DELETE FROM Studenti WHERE matr=111;

DELETE FROM CorsiWHERE ciclo=‘laurea tr.’ AND docente=‘FT’;

SQL-92 >> Concetti Fondamentali >> Cancellazioni


Aggiornamenti

Istruzione del DMLUPDATE

SintassiUPDATE <tabella> SET <attributo>=<espressione>

[<clausola WHERE>]

Semanticaaggiorna il valore dell’attributo di tutte le

ennuple (che soddisfano la condizione se è specificata)

SQL-92 >> Concetti Fondamentali >> Aggiornamenti


Aggiornamenti

Esempi:

UPDATE Studenti SET anno=anno+1;

UPDATE Studenti SET matr=11111WHERE matr=111;

UPDATE Corsi SET docente=‘VC’ WHERE ciclo=‘laurea tr.’ AND docente=‘FT’;

SQL-92 >> Concetti Fondamentali >> Aggiornamenti


Concetti Fondamentali

Introduzione Creazione ed

eliminazione di bd Creazione ed

eliminazione di tabelle

Inserimenti di ennuple

Interrogazioniclausola SELECTclausola FROMclausola WHEREclausola ORDER BYmetodo di scrittura

Cancellazioni Aggiornamenti

SQL-92 >> Sommario


SQL-92 >> Concetti Fondamentali >> La Base di Dati di Esempio

CREATE TABLE Studenti ( matr integer PRIMARY KEY, cognome varchar(20) NOT NULL, nome varchar(20) NOT NULL, ciclo char(20), anno integer, relatore char(4) REFERENCES Professori(cod));


CREATE TABLE Tutorato ( studente integer REFERENCES Studenti(matr), tutor integer REFERENCES Studenti(matr), PRIMARY KEY (studente,tutor));

CREATE TABLE Esami ( studente integer REFERENCES Studenti(matr) ON DELETE cascade ON UPDATE cascade, corso char(3) REFERENCES Corsi(cod), voto integer, lode bool, CHECK (voto>=18 and voto<=30), CHECK (not lode or voto=30), PRIMARY KEY (studente, corso));

CREATE TABLE Corsi ( cod char(3) PRIMARY KEY, titolo varchar(20) NOT NULL, ciclo char(20), docente char(4) REFERENCES Professori(cod) );

CREATE TABLE Numeri ( professore char(4) REFERENCES Professori(cod), numero char(9), PRIMARY KEY (professore,numero));



Tutorato T

studente INTEGER PK, FK

tutor INTEGER PK, FK

Studenti T

matr INTEGER PK

cognome VARCHAR(20)

nome VARCHAR(20)

ciclo CHAR(20)

anno INTEGER

relatore CHAR(4) FK

Professori T

cod CHAR(4) PK

cognome VARCHAR(20)

nome VARCHAR(20)

qualifica CHAR(15)

facolta CHAR(10)

Esami T

corso CHAR(3) PK, FK

studente INTEGER PK, FK

voto INTEGER

lode BOOL

Corsi T

codice CHAR(3) PK

titolo VARCHAR(20)

ciclo CHAR(20)

docente CHAR(4) FK

Numeri T

numero CHAR(9) PK

docente CHAR(4) PK, FK



cod cognome nome qualifica facolta

FT Totti Francesco ordinario Ingegneria

CV Vieri Christian associato Scienze

ADP Del Piero Alessandro supplente null

Professori

matr cognome nome ciclo anno relatore

111 Rossi Mario laurea tr. 1 null

222 Neri Paolo laurea tr. 2 null

333 Rossi Maria laurea tr. 1 null

444 Pinco Palla laurea tr. 3 FT

77777 Bruno Pasquale laurea sp. 1 FT

88888 Pinco Pietro laurea sp. 1 CV

Studenti

cod titolo ciclo docente

PR1 Programmazione I laurea tr. FT

ASD Algoritmi e Str. Dati laurea tr. CV

INFT Informatica Teorica laurea sp. ADP

Corsi



studente tutor

111 77777

222 77777

333 88888

444 88888

Tutorato

studente corso voto lode

111 PR1 27 false

222 ASD 30 true

111 INFT 24 false

77777 PR1 21 false

77777 ASD 20 false

88888 ASD 28 false

88888 PR1 30 false

88888 INFT 30 true

Esami

professore numero

FT 0971205145

FT 347123456

VC 0971205227

ADP 0971205363

ADP 338123456

Numeri


Termini della Licenza

Termini della Licenza

This work is licensed under the Creative Commons Attribution-ShareAlike License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/1.0/ or send a letter to Creative Commons, 559 Nathan Abbott Way, Stanford, California 94305, USA.

Questo lavoro viene concesso in uso secondo i termini della licenza “Attribution-ShareAlike” di Creative Commons. Per ottenere una copia della licenza, è possibile visitare http://creativecommons.org/licenses/by-sa/1.0/ oppure inviare una lettera all’indirizzo Creative Commons, 559 Nathan Abbott Way, Stanford, California 94305, USA.

G. Mecca – [email protected] – Università della Basilicata Basi di Dati SQL-92 Concetti...

Documents

Transcript of G. Mecca – [email protected] – Università della Basilicata Basi di Dati SQL-92 Concetti...