SQL

1. Abbiamo inserito i dati nel DB, sappiamo che le informazioni sono nel sistema informatico, impariamo a cercarle senza l’aiuto degli informatici. SQL INFN Laboratori Nazionali di Frascati SQL Structured Query Language Sandro Antidormi

2. 1.1 INFN Laboratori Nazionali di Frascati Breve storia di SQL In lavorazione ….segue introduzione a SQL alcune spiegazioni sulle tabelle (Unione – intersezione) Implementazioni (odbc – jdbc) Domande e risposte

3. Scrivere una query in linguaggio SQL Selezionare ed elencare tutte le righe e le colonne di una tabella Selezionare ed elencare determinate colonne di una tabella Selezionare ed elencare le colonne di più tabelle 2.1 L’istruzione SELECT INFN Laboratori Nazionali di Frascati L'istruzione SELECT Obiettivi:

4. 2.2 INFN Laboratori Nazionali di Frascati Anche se la parola querypuò essere tradotta in interrogazione o domanda, una query SQL non è necessariamente una domanda, può essere un comando per svolgere una delle seguenti operazioni: SELECT: regole sintattiche generali Creare o cancellare una tabella Inserire, modificare o cancellare campi Ricercare informazioni specifiche in più tabelle e restituire i risultati in un particolare ordine Modificare i parametri di protezione di un database

5. 2.3 INFN Laboratori Nazionali di Frascati SELECT: regole sintattiche generali SELECT NOME, INDIRIZZO, CITTA FROM PRESIDENTI WHERE NOME = ‘LINCOLN’; Select nome, indirizzo, citta From presidenti Where nome = ‘LINCOLN’; E’ un linguaggio abbastanza flessibile, i comandi possono essere scritti sia in maiuscolo che in minuscolo I riferimenti ai contenuti di un database devono essere scritti esattamente come sono stati registrati.

6. 2.3 INFN Laboratori Nazionali di Frascati SELECT: regole sintattiche generali Anche questa query è corretta, tuttavia usando con cura la spaziatura e le lettere minuscole/maiuscole, è possibile rendere più leggibili le istruzioni: Select nome, indirizzo, citta, from presidenti where nome = ‘LINCOLN’; NOME, INDIRIZZO, CITTA FROM PRESIDENTI WHERE NOME = ‘LINCOLN’; Il ; posto al termine della query serve a segnalare la fine delle istruzioni

7. 2.3 INFN Laboratori Nazionali di Frascati SELECT: regole sintattiche generali Gli elementi importanti di una query sono le parole chiave ovvero le parole del linguaggio SQL che sono riservate come parte della sintassi. Negli esempi precedenti le parole chiavi sono: SELECT FROM WHERE Consultare il manuale della versione in uso per conoscere tutte le parole chiave riservate

8. 2.4 INFN Laboratori Nazionali di Frascati SQL> SELECT; SELECT * ERROR at line 1; ORA-00936: missing expression SELECT <NOMI DI COLONNE> FROM <TABELLA> Parola chiave - istruzione – clausola La parola chiave si riferisce ai singoli elementi del linguaggio (SELECT e FROM), una clausola è parte di un’istruzione SQL; per esempio SELECT colonna1, colonna2, … è una clausola. Le clausole si combinano per formare un’istruzione completa. Per esempio è possibile combinare una clausola SELECT e una clausola FROM per scrivere un’istruzione SQL SELECT e FROM sono le parole chiave più usate, indispensabili per stabilire unaconnessione al computer mirata a leggere i dati

9. 2.6 INFN Laboratori Nazionali di Frascati tabella ASSEGNI la prima query • SQL> select * from assegni; • Num BENEFICIARIO IMPORTO NOTE 1 Computer shop 1.500,00 Dischetti e CD ROM 2 Librerie Riunite 2.453,40 Libri, cancelleria 3 Computer Shop 2.003,20 Telefono cellualare 4 BIOGA srl 980,00 GAS 5 Supermarket GC 1.500,00 Alimentari 6 Assicurazioni Italy 250,00 Assicurazione casa 7 GAS S.p.A. 251,00 Gas L’asterisco (*) di select * indica al database di fornire TUTTE le colonne associate alla tabella AS/400

10. 2.6 INFN Laboratori Nazionali di Frascati E’ possibile indicare le singole colonne che verranno elencate nello stesso ordine indicato: SQL> SELECT beneficiario, note, num, importo from assegni Cambiare l’ordine delle colonne • BENEFICIARIO NOTE Num IMPORTO Computer shop Dischetti e CD ROM 1 1.500,00 Librerie Riunite Libri, cancelleria 2 2.453,40 Computer Shop Telefono cellualare 3 2.003,20 BIOGA srl GAS 4 980,00 Supermarket GC Alimentari 5 1.500,00 Assicurazioni Italy Assicurazione casa 6 250,00 GAS S.p.A. Gas 7 251,00 La virgola separa le singole colonne ed è sempre possibile scrivere le istruzioni su più righe AS/400

11. 2.6 INFN Laboratori Nazionali di Frascati Per selezionare solo alcune colonne basta indicarle, solo quelle dichiarate verrano elencate nell’ordine indicato: SQL> SELECT num, importo from assegni Selezionare le colonne • Num IMPORTO 1 1.500,00 2 2.453,40 3 2.003,20 5 1.500,00 6 250,00 4 980,00 7 251,00 Sono state indicate istruzioni sia in maiuscolo che in minuscolo, come si può notare il risultato è lo stesso. AS/400

12. 2.6 INFN Laboratori Nazionali di Frascati Per selezionare dati da un’altra tabella è sufficiente modificare la clausola FROM SQL> SELECT * from depositi Selezionare tabelle differenti • DEPOSITO CHIHAPAGATO IMPORTO NOTE • 1 Zio ricco 2000,00 regalo di Natale 2 Mia azienda 2.453,40 Stipendio 3 Trasporti S.r.L. 250,00 Affitto garage Vengono elencate tutte le colonne della tabella DEPOSITI con le righe ordinate direttamente dal database AS/400

13. 2.7 INFN Laboratori Nazionali di Frascati Esaminando il contenuto della tabella assegni alucni importi sono duplicati: SQL> SELECT importo from assegni • IMPORTO 1.500,00 2.453,40 2.003,20 980,00 1.500,00 250,00 251,00 Query senza duplicati Per elencare gli importi senza duplicazioni occorre la clausola DISTINCT SQL> SELECT DISTINCT importo from assegni • IMPORTO 1.500,00 2.453,40 2.003,20 980,00 250,00 251,00 Con la clausola DISTINCT l’output riporta una sola istanza dei dati duplicati AS/400

14. 2.8 INFN Laboratori Nazionali di Frascati Riepilogo Le parole SELECT e FROM consentono a una query di caricare dei dati. E’ possibile creare una query generica e includere tutte le colonne con l’istruzione SELECT *. E’ anche possibile selezionare solo alcune colonne e anche modificare l’ordine in cui devono essere presentate. La parola chiave DISTINCT limita l’output delle query poiché consente di escludere i valori duplicati di una colonna.

15. 2.9 INFN Laboratori Nazionali di Frascati Le seguenti query non funzionano. Perché? a. Select * b. Select * from assegni c. Select importo numAssegno beneficiario FROM assegni; Quesiti Quale delle seguenti istruzioni SQL è corretta? a. select * from assegni; b. select * from assegni; c. select * from assegni / Le seguenti istruzioni forniscono lo stesso risultato? SELECT * FROM ASSEGNI select * from assegni; AS/400

16. 2.10 INFN Laboratori Nazionali di Frascati Esercizi 1. Utilizzando la tabella ASSEGNI scrivere una query per selezionare soltanto i numeri e le note degli assegni. 2. Riscrivere la query del precedente esercizio in modo che le note figurino come prima colonna del risultato della query. 3. Utilizzando la tabella ASSEGNI scrivere una query per ottenere tutte le note uniche (escludendo quelle duplicate) AS/400

17. In particolare impareremo a: Capire cos’è una espressione e come si utilizza Capire cos’è una condizione e come si utilizza Familiarizzare con la clausola WHERE Imparare ad usare gli operatori aritmetici, di confronto, di caratteri, logici e di insiemi Conoscere altri utili operatori 3.1 Espressioni, condizioni e operatori INFN Laboratori Nazionali di Frascati Espressioni, condizioni e operatori Obiettivi Approfondire le clausole SELECT e FROM, ampliare la query con qualche nuovo termine e introdurre gli operatori.

18. 3.2 INFN Laboratori Nazionali di Frascati La definizione di espressione è semplice: Espressioni un’espressione restituisce un valore Esistono vari tipi di espressioni in modo da poterle adattare ai differenti tipi di dati, come le stringhe, i valori numerici e booleani. Nell’esempio che segueimportoè un’espressione che fornisce il valore della colonna importo: SELECT importo from assegni; AS/400

19. 3.2 INFN Laboratori Nazionali di Frascati Espressioni La seguente espressione: WHERE NOME = ‘ROSSI’ contiene una condizione di una espressione booleana. Questa condizione potrà essere TRUE (vera) o FALSE (falsa) rispettivamente se la colonna NOME contiene ROSSI oppure no. Nella seguente istruzione, NOME, INDIRIZZO, TELEFONO E RUBRICA sono espressioni: SELECT NOME, INDIRIZZO, TELEFONO, RUBRICA FROM RUBRICA;

20. 3.3 INFN Laboratori Nazionali di Frascati Condizioni Le condizioni sono introdotte dalla clausola WHERE. Nell’esempio precedente la condizione è NOME = ‘ROSSI’ Tutte le volte che si vuole trovare un particolare elemento o gruppo di elementi in un database, occorre specificare una o più condizioni. Per trovare tutti gli impiegati che hanno lavorato più di 100 ore la condizione potrebbe essere: NUMERODIORE > 100

21. 3.3 INFN Laboratori Nazionali di Frascati Condizioni Le condizioni consentono di effettuare query selettive. Nella forma più comune includono una variabile, una costante e un operatore di confronto. Variabile………………………….. NOME Costante…………………………..’ROSSI’ Operatore di confronto………. > Per scrivere una query condizionale bisogna conoscere la clasuola WHERE e gli operatori

22. 3.3 INFN Laboratori Nazionali di Frascati La clausola WHERE La sintassi della clausola WHERE è la seguente: WHERE <condizione di ricerca> La clausola WHERE rende selettive le query, senza questa clausola la query visualizzerebbe tutti i record della tabella: SQL> SELECT * FROM BICICLETTE;

23. 3.3 INFN Laboratori Nazionali di Frascati La clausola WHERE Per selezionare una particolare bicicletta si può usare la seguente query: SELECT * FROM BICICLETTE WHERE NOME = ‘GIANT’; che fornisce un solo record: Questo semplice esempio mostra come applicare una condizione sui dati da caricare

24. 3.4 INFN Laboratori Nazionali di Frascati Operatori • aritmetici • di confronto • di caratteri • logici • di insieme • vari Gli operatori sono gli elementi utilizzati all’interno delle espressioni per specificare le condizioni necessarie a caricare i dati. Possono essere divisi in sei gruppi: E’ un potente gruppo di strumenti a base della conoscenza del linguaggio SQL

25. 3.4 INFN Laboratori Nazionali di Frascati * (moltiplicazione) % (modulo o resto) Operatori aritmetici + (somma) - (sottrazione) / (divisione) I primi quattro operatori si spiegano da soli. L’operatore modulo restituisce il resto di una divisione. Ad esempio: 5 % 2 = 1 6 % 2 = 0 Non funziona con i tipi di dati che hanno cifre decimali

26. 3.4 INFN Laboratori Nazionali di Frascati Operatori aritmetici Se vengono inseriti più operatori aritmetici in una espressione senza parentesi, essi vengono valutati nell’ordine: moltiplicazione, divisione, modulo, somma e sottrazione. Ad esempio: 2 * 6 + 9 / 3 vale 12 + 3 = 15 mentre l’espressione 2 * (6 + 9) / 3 vale 2 * 15 / 3 = 10 Attenzione alla posizione delle parentesi!

27. 3.4 INFN Laboratori Nazionali di Frascati L’operatore somma (+) Può essere usato in vari modi. La seguente espressione visualizza il contenuto della tabella PREZZO: La terza colonna (PREZZOINGROSSO + 150) non si trova nella tabella originale (in entrambi i casi sono state selezionate con il carattere * tutte le colonne). SQL consente di creare colonne virtuali o derivate combinando o modificando le colonne esistenti.

28. 3.4 INFN Laboratori Nazionali di Frascati L’operatore somma (+) SQL> SELECT ELEMENTO, PREZZOINGROSSO, (PREZZOINGROSSO + 1.50) PREZZODETTAGLIO FROM PREZZO E’ possibile assegnare una intestazione più comprensibile alla nuova colonna:

29. 3.4 INFN Laboratori Nazionali di Frascati L’operatore somma (+) SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO, (PREZZOINGROSSO + 1.50) PREZZODETTAGLIO FROM PREZZO E’ possibile assegnare una intestazione più comprensibile anche a colonne esistenti: La colonna ELEMENTO è stata ridenominata PRODOTTO. E’ comunque consigliabile consultare la documentazione della propria implementazione SQL per conoscere la sintassi esatta.

30. 3.4 INFN Laboratori Nazionali di Frascati L’operatore sottrazione (-) L’operatore meno svolge due funzioni, la prima è quella di cambiare segno ad un numero: SQL> SELECT * FROM MINMAX SQL> SELECT REGIONE, -TEMPMAX, -TEMPMIN FROM MINMAX

31. 3.4 INFN Laboratori Nazionali di Frascati L’operatore sottrazione (-) SQL>SELECT REGIONE, TEMPMAX MINIME, TEMPMIN MASSIME, (TEMPMIN - TEMPMAX) DIFFERENZA FROM MINMAX; La seconda (e ovvia) funzione dell’operatore meno è quella di sottrarre i valore di una colonna da quelli di un’altra colonna. Esempio: Oltre che aver creato la nuova colonna questa query ha corretto (solo sullo schermo) i nomi di quelle errate.

32. 3.4 INFN Laboratori Nazionali di Frascati L’operatore sottrazione (-) Se venisse utilizzato accidentalmente l’operatore meno su un campo caratteri si otterrà un messaggio simile a questo: SQL> SELECT –REGIONE FROM MINMAX ERROR: ORA-01722: invalid number no row selected Il testo del messaggio varia da un’implementazione all’altra, ma il risultato è lo stesso

33. 3.4 INFN Laboratori Nazionali di Frascati L’operatore divisione (/) L’operatore divisione ha un solo significato, per vedere gli effetti di una vendita a metà prezzo basta digitare la seguente istruzione: SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO, (PREZZOINGROSSO/2) PREZZOVENDITA FROM PREZZO L’uso dell’operatore divisione in questa istruzione SELECT è semplice, anche se vendere a metà prezzo non è proprio convienente.

34. 3.4 INFN Laboratori Nazionali di Frascati L’operatore moltiplicazione (*) Anche l’’operatore moltiplicazione è semplice da usare, ad esempio questa query visualizza l’effetto di uno sconto del 10% sui prezzi di tutti i prodotti: SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO, (PREZZOINGROSSO*0.9) NUOVOPREZZO FROM PREZZO; Questi operatori permettono di svolgere con grande efficienza calcoli aritmetici nell’istruzione SELECT

35. 3.4 INFN Laboratori Nazionali di Frascati L’operatore modulo (%) L’operatore modulo restituisce il resto intero di una operazione di divisione. Esempio: SQL> SELECT * FROM RESTI E’ possibile creare una nuova colonna, RESTO, dove registrare il resto della divisione tra NUMERATORE e DENOMINATORE SQL> SELECT NUMERATORE, DENOMINATORE, NUMERATORE%DENOMINATORE RESTO FROM RESTI

36. 3.4 INFN Laboratori Nazionali di Frascati Operatori di confronto Molte implementazioni SQL cambiano Unknown in FALSE e forniscono un operatore speciale, IS NULL, per verificare la condizione NULL (assenza di dati). SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO = NULL; No row selected SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO IS NULL; Questi operatori confrontano le espressioni e restituiscono uno di questi tre valori: TRUE, FALSE, Unkown. I primi due sono semplici da spiegare, TRUE significa vero e FALSE significa falso, il terzo, Unknow, identifica l’assenza di dati in una colonna, cioè NULL. Nel database la colonna prezzoingrosso della riga Limoni non contiene dati (non è zero)

37. 3.4 INFN Laboratori Nazionali di Frascati SQL> SELECT * FROM AMICI; L’operatore (=) Nella clausola WHERE il segno uguale è l’operatore di confronto più utilizzato, molto comodo per selezionare un valore tra tanti. SQL> SELECT * FROM AMICI WHERE NOME = ‘ALE’; SQL> SELECT * FROM AMICI WHERE NOME = ‘Ale’; no row selected.

38. 3.4 INFN Laboratori Nazionali di Frascati L’ operatore maggiore (>) e maggiore uguale (>=) 1 2 questi operatori operano nel seguente modo modo: 3 Non si usano apici per racchiudere il numero 4.50

39. 3.4 INFN Laboratori Nazionali di Frascati L’operatore maggiore (<) e maggiore uguale (<=) SQL> SELECT * FROM AMICI; questi operatori operano in senso inverso al precedente: SQL> SELECT * FROM AMICI WHERE CITTA < ‘MILANO’ ; Ovviamente è possibile applicarli anche a valori alfabetici, per la sequenza corretta consultare la propria implementazione SQL

40. 3.4 INFN Laboratori Nazionali di Frascati L’operatore di diseguaglianza (<> o !=) SQL> SELECT * FROM AMICI WHERE NOME <> ‘ALE’; Consente di trovare dati escludendone altri, cioè il simbolo (<>) oppure (!=) si legge “diverso da”. Per trovare gli amici tranne ALE (cioè con il nome diverso da ALE): In molte implementazione SQL è indifferente usare la forma (<>) anzichè (!=)

41. 3.4 INFN Laboratori Nazionali di Frascati Gli operatori di carattere L’operatore LIKE consente di estrarre dati che somigliano ad un certo schema Consentono di manipolare il modo in cui debbono essere rappresentate le stringhe durante la preparazione delle condizioni che selezionano i dati. Come fare a trovare tutte le parti che si trovano nella zona dorsale del corpo? Osservando la tabella è possibile individuarne due, ma hanno nomi differenti.

42. 3.4 INFN Laboratori Nazionali di Frascati L’operatore LIKE SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘%DORSO%’; SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘DORSO%’; SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘%DORSO’;

43. 3.4 INFN Laboratori Nazionali di Frascati L’operatore LIKE SQL> SELECT * FROM PARTI WHERE NOME LIKE ‘F%’; SQL> SELECT * FROM PARTI WHERE POSIZIONE LIKE ‘f%’; no rows selected. L’operatore like è sempre sensibile ai caratteri minuscoli/maiuscoli

44. 3.4 INFN Laboratori Nazionali di Frascati Il carattere di sottolineatura è un carattere jolly singolo. Il carattere di sottolineatura (_) SQL> SELECT * FROM AMICI WHERE PROVINCIA LIKE ‘_O’; E’ possibile utilizzare più di un carattere di sottolineatura e può essere combinato con l’operatore LIKE.

45. 3.4 INFN Laboratori Nazionali di Frascati Il simbolo || serve a concatenare due stringhe: L’operatore di concatenazione (||) SQL> SELECT NOME || COGNOME NOMECOMPLETO FROM AMICI; Se si utilizzasse il segno + per concatenare due stringhe, l’interprete SQL fornirebbe un messaggio d’errore: SQL> SELECT NOME + COGNOME NOMECOMPLETO FROM AMICI; ERROR: ORA-01722: invalid member

46. 3.4 INFN Laboratori Nazionali di Frascati Negli esempi precedenti è stato effettuato sempre un controllo alla volta. Questo metodo va bene per i casi semplici, ma come fareste a trovare tutti quei dipendenti I cui nomi iniziano con la lettera P e che hanno meno di tre giorni di ferie? Gli operatori logici • Gli operatori logici separano due o più condizioni nella clausola WHERE di un’istruzione SQL. • Essi sono: • AND • OR • NOT

47. 3.4 INFN Laboratori Nazionali di Frascati Si supponga che un’azienda conceda 12 giorni di ferie per ogni anno lavorativo. In base a quanto detto sarà scritta una query per trovare tutti gli impiegati i cui nomi iniziano con la lettera B ed hanno più di 50 giorni di ferie da godere: Esempio operatore AND SQL> SELECT COGNOME, ANNI * 12 – FERIEGODUTE [AS] FERIERESTANTI FROM TABELLAFERIE WHERE COGNOME LIKE ‘’B%’ AND ANNI * 12 – FERIEGODUTE > 50;

48. 3.4 INFN Laboratori Nazionali di Frascati L’operatore logico AND indica che entrambe le espressioni che si trovano ai suoi lati devono essere soddisfatte per restituire il valore TRUE (vero). Se una solo delle espressioni non è soddisfatta l’operatore AND restituisce FALSE. Ad esempio, per sapere quali impiegati hanno lavorato per l’azienda per 5 anni o meno ed hanno utilizzato più di 20 giorni di ferie, si può scrivere: L’operatore AND SQL> SELECT * FROM FERIE WHERE ANNI <= 5 AND FERIEGODUTE > 20;

49. 3.4 INFN Laboratori Nazionali di Frascati L’operatore logico OR puo’ essere utilizzato per combinare una serie di condizioni. Se una di queste è soddisfatta viene restituito TRUE L’operatore OR SQL> SELECT * FROM FERIE WHERE COGNOME LIKE ‘BO%’ OR COGNOME LIKE ‘CO%’; L’operatore OR richiede che una soltanto delle condizioni sia vera affinchè I dati possano essere restitutiti

50. 3.4 INFN Laboratori Nazionali di Frascati L’operatore logico NOT ha il compito di invertire il significato di una condizione L’operatore NOT SQL> SELECT * FROM FERIE WHERE COGNOME NOT LIKE ‘B%’; L’operatore OR richiede che una soltanto delle condizioni sia vera affinchè I dati possano essere restitutiti