Basi di Dati

1. Basi di Dati SQL-92 Concetti Fondamentali versione 2.0 Questo lavoro è concesso in uso secondo i termini di una licenza Creative Commons (vedi ultima pagina) G. Mecca – mecca@unibas.it – Università della Basilicata

2. Introduzione Creazione ed eliminazione di bd Creazione ed eliminazione di tabelle Inserimenti di ennuple Interrogazioni clausola SELECT clausola FROM clausola WHERE clausola ORDER BY metodo di scrittura Cancellazioni Aggiornamenti SQL-92 >> Sommario Concetti Fondamentali G. Mecca - mecca@unibas.it - Basi di Dati

3. SQL-92 >> Concetti Fondamentali >> Introduzione Introduzione • SQL (“Structured Query Language”) • linguaggio per l’interazione con il DBMS • tutte 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) G. Mecca - mecca@unibas.it - Basi di Dati

4. SQL-92 >> Concetti Fondamentali >> Introduzione Storia dello Standard • Prime implementazioni • IBM System/R 1979 (SEQUEL) • Primi prodotti commerciali • IBM SQL/DS, Oracle 1981 • SQL-86 • prima versione dello standard, basata sul dialetto IBM G. Mecca - mecca@unibas.it - Basi di Dati

5. SQL-92, full SQL-92, intermediate SQL-92, entry SQL-89, livello 2 SQL-92 >> Concetti Fondamentali >> Introduzione Storia dello Standard • SQL-89 (SQL-1) • vincoli di integrità • livello1 e livello2 • SQL-92 (SQL-2) • entry • intermediate • full SQL-89, livello 1 G. Mecca - mecca@unibas.it - Basi di Dati

6. SQL-92 >> Concetti Fondamentali >> Introduzione Storia dello Standard • Standard collegati • SQL/CLI • “Call Level Interface” (ODBC), 1995 • SQL/PSM • “Persistent Storage Modules”, 1997 • SQL/OLB • “Object Language Bindings”, 1998 G. Mecca - mecca@unibas.it - Basi di Dati

7. 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-92, intermediate SQL-92, entry SQL-89, livello 2 SQL-89, livello 1 SQL:1999, core SQL-92 >> Concetti Fondamentali >> Introduzione Storia dello Standard G. Mecca - mecca@unibas.it - Basi di Dati

8. SQL-92 >> Concetti Fondamentali >> Introduzione Storia dello Standard • Implementazioni dello standard • gli 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 G. Mecca - mecca@unibas.it - Basi di Dati

9. Tipico DBMS sul mercato oggi essenzialmente SQL-92 intermediate limitate funzionalità di SQL-92 full parte di SQL:1999 In questo corso ci concentriamo su SQL-92, intermediate tipico DBMS commerciale SQL-92, full SQL-92, intermediate SQL-92, entry SQL-89, livello 2 SQL-89, livello 1 SQL:1999, core SQL-92 >> Concetti Fondamentali >> Introduzione Storia dello Standard G. Mecca - mecca@unibas.it - Basi di Dati

10. DDL creazione della base di datiCREATE DATABASE creazione delle tabelleCREATE TABLE DML inserimento delle ennupleINSERT INTO interrogazioniSELECT eliminazione delle ennuple DELETE modifica della ennupleUPDATE SQL-92 >> Concetti Fondamentali >> Introduzione Le Istruzioni Fondamentali G. Mecca - mecca@unibas.it - Basi di Dati

11. SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di BD Creazione ed Eliminazione di BD • Istruzioni del DDL • Sintassi • CREATE DATABASE <nome>; • DROP DATABASE <nome>; Esempio CREATE DATABASE universita; DROP DATABASE universita; G. Mecca - mecca@unibas.it - Basi di Dati

12. SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di BD Creazione ed Eliminazione di BD • Semantica • CREATE DATABASE • crea una nuova base di dati vuota • l’utente deve essere autorizzato • l’utente diventa il proprietario della bd • DROP DATABASE • elimina una base di dati esistente (anche non vuota) • l’utente deve essere autorizzato G. Mecca - mecca@unibas.it - Basi di Dati

13. SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di Tabelle Creazione ed Eliminazione di Tabelle • Istruzioni del DDL • CREATE TABLE • DROP TABLE • Sintassi • CREATE TABLE <nome> (<schema>); • DROP TABLE <nome>; G. Mecca - mecca@unibas.it - Basi di Dati

14. 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; G. Mecca - mecca@unibas.it - Basi di Dati

15. SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di Tabelle 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)); G. Mecca - mecca@unibas.it - Basi di Dati

16. SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di Tabelle Creazione ed Eliminazione di Tabelle • <schema> • una o più definizioni di attributo • zero o più definizioni di vincoli di tabella • Definizione di attributo • <nomeattributo> <tipo> [<vincoli di colonna>] • Definizioni di vincoli di tabella • normalmente vincoli relativi a più attributi G. Mecca - mecca@unibas.it - Basi di Dati

17. <nomeattributo> identificatore <tipo> INT, INTEGER REAL, FLOAT DECIMAL(lung,dec) DOUBLE PRECISION CHAR(n), CHARACTER(n) VARCHAR(n) LONG VARCHAR, TEXT BOOLEAN, BOOL DATE TIME TIMESTAMP BINARY(n), BIT(n) VARBINARY(n), VARBIT(n) LONG VARBINARY, BLOB SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di Tabelle Creazione ed Eliminazione di Tabelle G. Mecca - mecca@unibas.it - Basi di Dati

18. SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di Tabelle Creazione ed Eliminazione di Tabelle • Vincoli di colonna • PRIMARY KEY • UNIQUE • NOT NULL • REFERENCES <chiave della tabella dest.>[ON update CASCADE][ON delete CASCADE] • CHECK (<espressione>) G. Mecca - mecca@unibas.it - Basi di Dati

19. SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di Tabelle 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>) G. Mecca - mecca@unibas.it - Basi di Dati

20. SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di Tabelle Creazione ed Eliminazione di Tabelle • Semantica • CREATE TABLE • crea una nuova tabella vuota secondo lo schema specificato • l’utente deve essere autorizzato • l’utente diventa il proprietario della tabella • attenzione ai vincoli di riferimento • DROP TABLE • elimina una tabella esistente • l’utente deve essere autorizzato G. Mecca - mecca@unibas.it - Basi di Dati

21. SQL-92 >> Concetti Fondamentali >> Creazione ed Eliminazione di Tabelle CREATE TABLE Professori ( cod char(4) PRIMARY KEY, cognome varchar(20) NOT NULL, nome varchar(20) NOT NULL, qualifica char(15), facolta char(10) ); 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 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 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)); G. Mecca - mecca@unibas.it - Basi di Dati

22. SQL-92 >> Concetti Fondamentali >> Inserimenti Inserimenti • Istruzione del DML • INSERT • Sintassi • INSERT INTO <tabella> VALUES (<valori>); • Semantica • inserimento della ennupla nella tabella • corrispondenza ordinata tra valori e attributi (notazione posizionale) G. Mecca - mecca@unibas.it - Basi di Dati

23. 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’); G. Mecca - mecca@unibas.it - Basi di Dati

24. SQL-92 >> Concetti Fondamentali >> Interrogazioni Interrogazioni • Istruzione del DML • SELECT • sintassi concreta per specificare operatori dell’algebra • Filosofia • parzialmente dichiarativa • si specificano gli operatori da applicare, non l’ordine in cui devono essere applicati • l’ottimizzatore sceglie la strategia ottima G. Mecca - mecca@unibas.it - Basi di Dati

25. SQL-92 >> Concetti Fondamentali >> Interrogazioni Interrogazioni • Tre “clausole” principali (nucleo) • SELECT, FROM, WHERE • SELECT • proiezioni, ridenominazioni, elimin. duplicati • FROM • tabelle, join, prodotti cartesiani, alias • WHERE • selezioni G. Mecca - mecca@unibas.it - Basi di Dati

26. s (Studenti) Risultato = ciclo=‘laurea tr.’ AND anno>1 SQL-92 >> Concetti Fondamentali >> Interrogazioni Esempi • “Studenti della laurea triennale di anni successivi al primo” SELECT * FROM Studenti WHERE ciclo=‘laurea tr.’ AND anno>1; G. Mecca - mecca@unibas.it - Basi di Dati

27. Risultato = pmatricola, cognome ( stitolo=‘Inform. t.’ ( Studenti matr=studente Esami cod=corso Corsi )) SQL-92 >> Concetti Fondamentali >> Interrogazioni Esempi • “Matricola e cognome degli studenti che hanno sostenuto l’esame di informatica teorica” SELECT matricola, cognome FROM Studenti JOIN Esami ON matr=studente JOIN Corsi ON cod=corso WHERE titolo=‘Inform. t.’; G. Mecca - mecca@unibas.it - Basi di Dati

28. SQL-92 >> Concetti Fondamentali >> Interrogazioni Interrogazioni • Per sistematizzare • partiamo 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 G. Mecca - mecca@unibas.it - Basi di Dati

29. SQL-92 >> Concetti Fondamentali >> Interrogazioni Interrogazioni • Forma standard dell’algebra • una o più sottointerrogazioni • correlate da operatori insiemistici • Sottointerrogazioni • strategia a: prodotti cartesiani tra le tabelle (con eventuali alias) • strategia b: join tra le tabelle (con eventuali alias) >> G. Mecca - mecca@unibas.it - Basi di Dati

30. SQL-92 >> Concetti Fondamentali >> Interrogazioni Interrogazioni • Sottointerrogazioni (Continua) • selezioni • proiezioni (con funzioni aggregative) • eliminazione di duplicati (DISTINCT) • ridenominazioni • ordinamenti finali (ORDER BY) G. Mecca - mecca@unibas.it - Basi di Dati

31. Sottointerrogazione ORDER BY r DISTINCT p s TABELLE, con X oppure ed ev. ALIAS Sottointerrogazione SQL-92 >> Concetti Fondamentali >> Interrogazioni Interrogazioni U oppure ∩ oppure – G. Mecca - mecca@unibas.it - Basi di Dati

32. SQL-92 >> Concetti Fondamentali >> Interrogazioni Interrogazioni • Interrogazioni SQL • una o più sottointerrogazioni • con eventuali operatori insiemistici • Per ogni sottointerrogazione • SELECT: proiezioni, ridenominazioni, distinct • FROM: prodotti cartesiani o join, alias • [WHERE]: selezioni (opzionale) • [ORDER BY]: ordinamenti (opzionale) G. Mecca - mecca@unibas.it - Basi di Dati

33. Sottointerrogazione Sottointerrogazione SQL-92 >> Concetti Fondamentali >> Interrogazioni Interrogazioni Sottointerrogazione ORDER BY r SELECT DISTINCT FROM p s WHERE ORDER BY tabelle con X o U oppure ∩ oppure – UNION, INTERSECT, EXCEPT Sottointerrogazione G. Mecca - mecca@unibas.it - Basi di Dati

34. s (Studenti) Risultato = ciclo=‘laurea tr.’ AND anno>1 SQL-92 >> Concetti Fondamentali >> Interrogazioni Esempi • “Studenti della laurea triennale di anni successivi al primo” SELECT * FROM Studenti WHERE ciclo=‘laurea tr.’ AND anno>1; NOTA: le clausole vanno scritte nell’ordine in cui compaionoprima SELECT, poi FROM, poi WHERE, poi ORDER BY G. Mecca - mecca@unibas.it - Basi di Dati

35. Risultato = pmatricola, cognome ( stitolo=‘Inform. t.’ ( Studenti matr=studente Esami cod=corso Corsi )) SQL-92 >> Concetti Fondamentali >> Interrogazioni Esempi • “Matricola e cognome degli studenti che hanno sostenuto l’esame di informatica teorica” SELECT matricola, cognome FROM Studenti JOIN Esami ON matr=studente JOIN Corsi ON cod=corso WHERE titolo=‘Inform. t.’; G. Mecca - mecca@unibas.it - Basi di Dati

36. SQL-92 >> Concetti Fondamentali >> Interrogazioni Clausola FROM • Serve a specificare • le tabelle da cui prelevare i dati • gli eventuali prodotti cartesiani • gli eventuali join • gli eventuali alias • Prodotti cartesiani: sintassi • FROM R, S, T AS V … • es: FROM Professori, Numeri G. Mecca - mecca@unibas.it - Basi di Dati

37. SQL-92 >> Concetti Fondamentali >> Interrogazioni Clausola FROM • Join: Sintassi • FROM S JOIN R ON S.A=R.B JOIN T AS V.. • es: FROM Professori JOIN Numeri ON cod=professore • Semantica • applicazione degli operatori corrispondenti • costruisce la tabella unica a cui applicare gli operatori successivi G. Mecca - mecca@unibas.it - Basi di Dati

38. SQL-92 >> Concetti Fondamentali >> Interrogazioni Clausola WHERE • Serve a specificare • le selezioni • Sintassi • WHERE <condizione> • Dove • <condizione> è una condizione composta di sottocondizioni di selezione, collegate con connettivi booleani (AND, OR, NOT) • sintassi identica alle condizioni dell’algebra G. Mecca - mecca@unibas.it - Basi di Dati

39. SQL-92 >> Concetti Fondamentali >> Interrogazioni Clausola WHERE • Nota • possono comparire solo attributi di tabelle che compaiono nella clausola FROM • Esempio FROM ProfessoriWHERE qualifica=‘ordinario’ AND facolta IS NOT NULL • Semantica • applica le selezioni alle tabelle della FROM G. Mecca - mecca@unibas.it - Basi di Dati

40. SQL-92 >> Concetti Fondamentali >> Interrogazioni Clausola SELECT • Serve a specificare • proiezioni • funzioni aggregative • ridenominazioni • eliminazione di duplicati • Nota • anche in questo caso è possibile specificare solo attributi di tabelle che compaiono nella clausola FROM G. Mecca - mecca@unibas.it - Basi di Dati

41. SQL-92 >> Concetti Fondamentali >> Interrogazioni Clausola SELECT • Sintassi • SELECT [DISTINCT] <attributi> • Dove • <attributi> è una lista di nomi di attributo • con eventuali ridenominazioni nella forma<vecchioNome> AS <nuovoNome> • Semantica • applicare gli operatori corrispondenti alle tabelle della FROM G. Mecca - mecca@unibas.it - Basi di Dati

42. SQL-92 >> Concetti Fondamentali >> Interrogazioni Clausola SELECT • Esempi SELECT cognome, nomeFROM Professori SELECT DISTINCT cognome, nomeFROM Professori SELECT cognome AS cognomeProf, nome AS nomeProfFROM Professori G. Mecca - mecca@unibas.it - Basi di Dati

43. SQL-92 >> Concetti Fondamentali >> Interrogazioni Clausola SELECT • Nota • la clausola SELECT è obbligatoria • che succede se non ci sono proiezioni e ridenominazioni ? • SELECT * • non è necessario effettuare nè proiezioni nè ridenominazioni G. Mecca - mecca@unibas.it - Basi di Dati

44. SQL-92 >> Concetti Fondamentali >> Interrogazioni Clausola ORDER BY • Serve a specificare • ordinamenti finali • Sintassi • ORDER BY <attributi> • Dove • <attributi> è una lista di attributi di tabelle che compaiono nella clausola FROM G. Mecca - mecca@unibas.it - Basi di Dati

45. SQL-92 >> Concetti Fondamentali >> Interrogazioni Clausola ORDER BY • Esempio SELECT * FROM Studenti ORDER BY cognome, annoCorso • Normalemente: • l’ordinamento è crescente • Ordinamento decrescente: DESC ORDER BY cognome, annoCorso DESC G. Mecca - mecca@unibas.it - Basi di Dati

46. DISTINCT (p (Studenti)) ElencoNomi = cognome, nome SQL-92 >> Concetti Fondamentali >> Interrogazioni Esempi • “Cognomi e nomi degli studenti” SELECT DISTINCT cognome, nome FROM Studenti; G. Mecca - mecca@unibas.it - Basi di Dati

47. ORDER BY ( ElencoNomi = cognome, nome DISTINCT (p (Studenti))) cognome, nome SQL-92 >> Concetti Fondamentali >> Interrogazioni Esempi • “Cognomi e nomi degli studenti, in ordine alfabetico” SELECT DISTINCT cognome, nome FROM Studenti ORDER BY cognome, nome; G. Mecca - mecca@unibas.it - Basi di Dati

48. SQL-92 >> Concetti Fondamentali >> Interrogazioni Esempi • “Voto medio riportato negli esami” Risultato = pAVG(voto) (Esami) SELECT AVG(voto) FROM Esami; G. Mecca - mecca@unibas.it - Basi di Dati

49. p ( ProfessoriENumeri = cognome, nome, numero s ( cod=professore Professori X Numeri )) SQL-92 >> Concetti Fondamentali >> Interrogazioni Esempi • “Cognomi, nomi e numeri di telefono dei professori” (strategia a) SELECT cognome, nome, numero FROM Professori, Numeri WHERE cod=professore; G. Mecca - mecca@unibas.it - Basi di Dati

50. p ( ProfessoriENumeri = cognome, nome, numero Professori cod=professore Numeri) SQL-92 >> Concetti Fondamentali >> Interrogazioni Esempi • “Cognomi, nomi e numeri di telefono dei professori” (strategia b) SELECT cognome, nome, numero FROM Professori JOIN Numeri ON cod=professore; G. Mecca - mecca@unibas.it - Basi di Dati