{count, plural, one {Corso} other {Corsi}}
Reportistica in SQL
{hours} ore
39.9K
In questo tutorial ti concentrerai sull’SQL ANSI (American National Standards Institute) che funziona su ogni database, come Oracle, MySQL, Microsoft SQL Server, ecc. Iniziamo con una introduzione a SQL (Structured Query Language) e perché può servirti come data scientist.
SQL e Data Science
In questa parte del tutorial vedrai perché un data scientist dovrebbe imparare SQL. Ecco come SQL ti aiuterà nella tua carriera da data scientist:
- SQL è diventato un requisito per la maggior parte dei lavori in data science, tra cui: data analyst, sviluppatore BI (Business Intelligence), programmatore, programmatore di database. SQL ti permette di comunicare con il database e lavorare con i tuoi dati.
- Se hai usato software come Tableau o altri strumenti di reportistica o visualizzazione, avrai visto come colleghi il tuo progetto a un database e trascini grafici e diagrammi nel report specificando solo i campi, e il software fa il resto. Quando esegui il drag and drop degli attributi o usi l’interfaccia grafica per svolgere tutte le operazioni con un clic, dietro le quinte il software esegue SQL per interagire con il database. Imparando SQL, puoi interagire direttamente con il database.
- SQL può essere usato con tutti i linguaggi di programmazione applicativa come PHP, Java. Puoi creare visualizzazioni dati integrando SQL nell’applicazione oppure recuperare dati dal database e convertirli in formati XML o JSON per usarli in web service o API.
- I database si sono evoluti negli anni: con il Big Data al centro dell’attenzione e i dati usati nella vita quotidiana, i database NoSQL stanno diventando popolari. Imparare SQL ti aiuta a costruire solide basi sui database e a capire quando usare database strutturati e quando NoSQL, apprezzandone le differenze.
Qui farò una breve introduzione e passerò in rassegna le terminologie usate in SQL e nei database che ti aiuteranno a imparare a programmare più velocemente! Se sai già cos’è un database relazionale e cos’è SQL, puoi saltare direttamente al codice.
Introduzione a SQL e ai database
Un database è una raccolta organizzata di informazioni. Per gestire i database servono i Data Base Management Systems (DBMS). Un DBMS è un programma che, su richiesta, memorizza, recupera e modifica i dati nei database.
Nel tempo ci sono stati diversi tipi di database: gerarchici, a rete, relazionali e oggi NoSQL. Un database relazionale è una raccolta di relazioni o tabelle bidimensionali.
Di seguito le terminologie usate negli RDBMS:
| Termine | Descrizione |
|---|---|
| Tabella | Una tabella è la struttura di archiviazione di base di un RDBMS. Conserva tutti i dati necessari su qualcosa del mondo reale. Esempio: Dipendenti. |
| Riga o Tupla | Rappresenta tutti i dati richiesti per un determinato dipendente. Ogni riga in una tabella può essere identificata da una chiave primaria, che non consente righe duplicate. |
| Colonna o Attributo | Di solito si riferisce a una caratteristica di un’entità |
| Chiave primaria | Un campo che identifica in modo univoco una riga |
| Chiave esterna | Una chiave esterna è una colonna che identifica come le tabelle sono collegate tra loro. Una chiave esterna fa riferimento a una chiave primaria in un’altra tabella. |
Puoi collegare più tabelle usando chiavi primarie e chiavi esterne. Ogni riga in un database relazionale è identificata in modo univoco da una Primary Key (PK). Puoi fare riferimento a un’altra tabella usando una Foreign Key (FK). Per esempio:
I database relazionali si accedono usando il linguaggio SQL (Structured Query Language). Ogni database supporta l’SQL standard ANSI ma ha anche una propria sintassi per facilitare alcune operazioni. In questo tutorial imparerai l’SQL ANSI così da poter lavorare con tutti i database. L’SQL ANSI può essere suddiviso in cinque sezioni. Le elenco tutte, ma qui tratteremo in dettaglio solo due sezioni rilevanti: Data Retrieval e DML:
- Data Retrieval:
- Select.
- Data Manipulation Language (DML):
- Insert, Update, Delete, Merge
- Data Definition Language (DDL):
- Create, Alter, Drop, Rename, Truncate.
- Data Control Language (DCL):
- Grant, Revoke.
- Transaction Control:
- Commit, Rollback, Savepoint.
Esistono diversi fornitori di RDBMS. I più comuni e utilizzati sono:
- Oracle (Oracle Corporation)
- Microsoft SQL Server (Microsoft)
- MySQL (Oracle Corporation)
- PostgreSQL (PostgreSQL Global Development Group)
- SQLite (sviluppato da D. Richard Hipp)
Dove esegui le query SQL? Può essere:
- Un software di reportistica che ti consente di leggere dal database e visualizzare i dati (ad esempio: Tableau, Microsoft BI)
- Una GUI per l’amministrazione del database (ad esempio: TOAD, SQL Developer per Oracle, phpMyAdmin per MySQL)
- Un’interfaccia a console diretta al database (ad esempio: SQL*Plus per Oracle)
Se hai le credenziali per accedere a un database, puoi visualizzare gli oggetti del database usando query o una GUI, a seconda del fornitore del database.
Ecco fatto! Passiamo a imparare SQL…
Tipi di dato in SQL
Ogni colonna in un database ha un nome, un tipo di dato e talvolta una dimensione associata. Spetta allo sviluppatore del database progettare lo schema e decidere quale tipo di dato usare in base ai requisiti e al volume di dati.
Come data scientist devi conoscere i tipi di dato, perché ti aiuteranno a usare correttamente le funzioni del database e a scrivere query accurate. C’è un tipo di dato per ogni tipo di colonna in un database: nome di una persona, testo, numeri, un’immagine memorizzata nel database e così via.
Di seguito ti mostriamo i tipi di dato di base per Oracle Server, SQL Server e MySQL Server:
Per approfondire:
SQL e Data Reporting
Per tutto l’SQL usato in questo tutorial, si considera il seguente schema di database di esempio:
Considera un database con due tabelle: emp, che contiene i dati dei dipendenti, e dept, che contiene le informazioni sui reparti.
La tabella emp ha numero dipendente (empno), nome dipendente (ename), stipendio (sal), commissione (comm), mansione (job), id manager (mgr), data di assunzione (hiredate) e numero di reparto (deptno). Poiché il manager è anch’egli un dipendente e ha un numero dipendente, mgr corrisponde a uno degli empno la cui mansione è "MANAGER".
La tabella dept ha numero di reparto (deptno), nome reparto (dname) e sede del reparto (loc).
Nota: i diversi database hanno formati di data diversi. Quello qui mostrato, DD-MON-YY, è il formato predefinito per un database Oracle. Il formato predefinito per Microsoft SQL Server e MySQL è YYYY-MM-DD.
Le tue tabelle potrebbero essere diverse, quindi dovrai adattare solo il nome della tabella e degli attributi. In questo tutorial leggerai soltanto dal database, senza scrivere, aggiornare o creare nuove tabelle e oggetti. Quindi nessun rischio di perdita o modifica dei dati!
Informazione: uno schema è una raccolta di oggetti come tabelle, funzioni, procedure, viste che appartengono a un utente
Nello schema sopra, puoi vedere che la tabella emp ha sei attributi per l’entità Employee. La tabella dept ha tre attributi per l’entità Department.
Recuperare dati con l’istruzione SELECT
Un’istruzione SELECT recupera informazioni dal database. Con SELECT puoi fare quanto segue:
1. Proiezione: puoi selezionare le colonne di una tabella che vuoi restituire con la tua query. Puoi scegliere poche o molte colonne a seconda delle esigenze.
2. Selezione: puoi scegliere le righe di una tabella che vuoi restituire con una query.
Puoi usare vari criteri per restringere le righe visualizzate.
3. Join: puoi mettere insieme dati memorizzati in tabelle diverse creando un collegamento tra di esse.
La SELECT di base ti consente di specificare quali colonne vuoi da quale tabella. La clausola SELECT specifica colonne ed espressioni, la clausola FROM da quale tabella prelevare i dati.
Per esempio: quali sono i nomi di tutti i dipendenti e le loro mansioni: SELECT ename, job FROM employee; La query SQL qui sopra restituirà il seguente output in base allo schema discusso:
| ename | job |
|---|---|
| A | Salesman |
| B | Manager |
| C | Manager |
Per selezionare tutti gli attributi e tutte le righe da una tabella si usa l’operatore *:
SELECT *
FROM employee;
The output will be:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Alcuni suggerimenti per scrivere istruzioni SQL:
- Le istruzioni SQL non sono case sensitive
- Scrivi le clausole su righe separate per aumentare la leggibilità
- Puoi scrivere un’istruzione SQL su una o più righe
Puoi anche creare espressioni usando gli operatori +, -, /, * su date e numeri per generare i dati richiesti. Per esempio, ti viene chiesto di calcolare il 20% dello stipendio di tutti i dipendenti. La query sarà:
SELECT ename, sal*(20/100)
FROM employee;
Output:
ENAME SAL*(20/100)
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
Informazioni:
- Le parentesi servono a chiarire l’espressione
- Divisione e moltiplicazione hanno precedenza su addizione e sottrazione
- Se c’è la stessa precedenza, l’espressione è valutata da sinistra a destra
I valori NULL sono trattati in modo particolare nei database. Un valore NULL indica che il valore è sconosciuto. Qualsiasi operazione con NULL restituisce NULL. Database diversi hanno funzioni diverse per gestire i null. Esiste una funzione comune usata in MySQL, Microsoft SQL Server e Oracle per trattare i NULL.
SELECT ename, sal+COALESCE(comm,0)
FROM employee;
Output:
ENAME SAL+COALESCE(COMM,0)
---------- --------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
BLAKE 2850
Alias di colonna e concatenazione
Negli output sopra, il nome della colonna è lo stesso del campo del database o dell’espressione selezionata. A volte, quando generi report, vuoi dare nomi personalizzati alle intestazioni. Puoi farlo con gli alias.
SELECT ename AS "Emp Name", sal*(20/100) as "20% of Salary"
FROM employee;
Output:
Emp Name 20% of Salary
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
Quando assegni un alias che contiene spazi devi usare le virgolette doppie. Altrimenti non è necessario metterle dopo AS. A volte puoi anche omettere la parola chiave AS.
Puoi formattare l’output tramite concatenazione. Puoi aggiungere testo personalizzato usando la funzione CONCAT o operatori come || o + a seconda del database:
- Oracle supporta CONCAT() e || ma CONCAT() accetta solo due argomenti. Devi usare CONCAT() annidati.
- MySQL usa CONCAT()
- Microsoft SQL Server usa l’operatore "+" e CONCAT().
Oracle: SELECT '20% of salary of '||ename||' is '||sal*(20/100) as "20% of salary" FROM employee;
SELECT CONCAT(CONCAT('20% of salary of',ename),CONCAT(' is ',sal(20/100))) as "20% of salary" FROM employee; MySQL e Microsoft SQL Server: SELECT CONCAT('20% of salary of ',ename,' is ',sal(20/100)) as "20% of salary" FROM employee; Tutte produrranno lo stesso output:
20% of salary
-----------------------------------------------------------------------
20% of salary of SMITH is 160
20% of salary of ALLEN is 320
20% of salary of WARD is 250
20% of salary of JONES is 595
20% of salary of BLAKE is 570
Eliminare righe ripetute con DISTINCT
SELECT deptno
FROM employee;
Above query will result in:
DEPTNO
----------
20
30
30
20
30
Qui i deptno 20 e 30 si ripetono. Puoi eliminare tali righe usando la parola chiave DISTINCT nella clausola SELECT.
SELECT distinct ename, deptno, job
FROM employee;
DEPTNO
----------
30
20
Limitare e ordinare i dati
La clausola WHERE si usa per filtrare i dati in base a una condizione.
Trova tutti i dipendenti con mansione CLERK:
SELECT ename, job
FROM employee
WHERE job='CLERK';
ENAME JOB
---------- ---------
SMITH CLERK
Puoi voler filtrare i risultati con condizioni diverse. Per questo si usano operatori, simboli condizionali e parole chiave specifiche:
L’uso di =, <>, !=, >=, <=, >, < è semplice come nella query sopra che usa =.
Sintassi AND & OR: SELECT column1, column2,.. FROM table_name WHERE condition1 AND condition2 AND condition 3...;
SELECT column1, column2,.. FROM table_name WHERE condition1 OR condition2 OR condition 3...; Trova i nomi dei dipendenti con mansione MANAGER e appartenenti al reparto 30:
SELECT ename
FROM employee
WHERE job='MANAGER' AND deptno=30;
ENAME
----------
BLAKE
SELECT ename
FROM employee
WHERE job='MANAGER' OR deptno=30;
ENAME
----------
ALLEN
WARD
JONES
BLAKE
Sintassi NOT: SELECT column1, column2, ... FROM table_name WHERE NOT condition; Trova tutti i dipendenti la cui mansione non è SALESMAN:
SELECT ename, job
from employee
WHERE NOT job='SALESMAN';
ENAME JOB
---------- ---------
SMITH CLERK
JONES MANAGER
BLAKE MANAGER
Puoi creare condizioni complesse usando insieme AND, OR e NOT. La precedenza è:
- NOT
- AND
- OR
Trova tutti i dipendenti la cui mansione non è CLERK e con reparto 20:
SELECT ename, job
from employee
WHERE NOT job='SALESMAN' AND sal>800;
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
Qui prima è stato eseguito NOT e dopo NOT è stato valutato AND.
Altri operatori condizionali utili sono i seguenti:
BETWEEN...AND:
SELECT *
FROM employee
WHERE sal BETWEEN 1000 AND 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
LIKE:
LIKE usa due wildcard: percentuale % e underscore _ per rappresentare il numero di caratteri nel pattern.
- % indica zero, uno o più caratteri
- %M%: corrisponde a qualsiasi stringa che contiene M in qualunque posizione
- M%: corrisponde a valori che iniziano con M
- %M: corrisponde a valori che finiscono con M
- M%A: inizia con M e finisce con A
I pattern sono case sensitive.
- _ specifica il numero di caratteri sconosciuti prima o dopo il carattere noto. Un underscore vale un carattere.
- _r%: corrisponde a un valore con r in seconda posizione.
Ottieni i nomi di tutti i dipendenti che iniziano con "B":
SELECT *
FROM employee
WHERE ename LIKE 'B%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Ottieni i nomi di tutti i dipendenti che iniziano con "A" e hanno "E" in una qualsiasi posizione dopo "A":
SELECT *
FROM employee
WHERE ename LIKE 'A%E%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
IN(value1,value2, value3,..):
La funzione IN() può prendere uno, due o più valori e ti permette di far corrispondere una colonna ai valori tra parentesi nella clausola WHERE:
SELECT ename, job, hiredate
FROM employee
WHERE job IN ('CLERK','SALESMAN');
ENAME JOB HIREDATE
---------- --------- ---------
SMITH CLERK 17-DEC-80
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
Puoi anche usare una SELECT in IN() che restituisce alcuni valori. Per esempio:
SELECT ename, job, hiredate
FROM employee
WHERE deptno IN (select deptno FROM department WHERE loc='CHICAGO');
ENAME JOB HIREDATE
---------- --------- ---------
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
BLAKE MANAGER 01-MAY-81
La SELECT dentro IN() si chiama anche sottoquery. Più avanti nel tutorial ne parleremo ancora!
IS NULL:
IS NULL si usa per verificare i valori NULL in un dato attributo. Per esempio, trova tutti i dipendenti che non hanno commissione:
SELECT ename, job, sal
FROM employee
WHERE comm IS NULL;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
BLAKE MANAGER 2850
Se vuoi ottenere i nomi dei dipendenti che hanno percepito una commissione userai IS NOT NULL:
SELECT ename, job, sal, comm
FROM employee
WHERE comm IS NOT NULL;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
ALLEN SALESMAN 1600 300
WARD SALESMAN 1250 500
Per filtrare i risultati usando colonne data devi usare il formato data predefinito. Se serve un altro formato, vanno applicate funzioni di data che vedrai più avanti nel tutorial. Trova tutti i dipendenti assunti dopo il 21 febbraio 1981:
(Qui si usa il formato data predefinito di Oracle)
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81';
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
JONES MANAGER 2975
BLAKE MANAGER 2850
Puoi creare espressioni condizionali complesse combinando tutti gli operatori sopra, ma devi fare attenzione alla precedenza, cioè l’ordine in cui gli operatori sono valutati. Ecco le regole di precedenza per vari database:
- Precedenza degli operatori in Microsoft Transact-SQL
- Precedenza delle condizioni in Oracle 10g
- Precedenza degli operatori in Oracle MySQL 9
- Precedenza degli operatori in PostgreSQL
- Precedenza degli operatori in SQLite
Ci sono due funzioni utili: ANY() e ALL() che possono essere usate nelle condizioni. Per esempio:
SELECT ename, job, sal, comm FROM employee WHERE deptno=ANY(SELECT deptno from dept WHERE loc='NEW YORK');
SELECT ename, job, sal, comm FROM employee WHERE deptno=ALL(SELECT deptno from dept WHERE dname='SALES');
Nota l’uso delle sottoquery negli esempi sopra. Imparerai di più sulle sottoquery più avanti.
Ordinare i risultati con ORDER BY
Puoi ordinare i risultati in modo crescente (ASC) o decrescente (DESC) per qualsiasi attributo o più attributi della tabella. Puoi anche ordinare per alias specificati nella clausola SELECT:
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal desc;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
WARD SALESMAN 1250 500
Nota: l’ordinamento predefinito è crescente (ASC) e non serve specificarlo.
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
BLAKE MANAGER 2850
JONES MANAGER 2975
Puoi specificare più colonne in ORDER BY, che saranno valutate nell’ordine indicato. Per esempio, ordina i dipendenti prima per deptno in modo crescente e poi per nome in modo decrescente:
SELECT ename, job, sal, comm, deptno
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY deptno ASC, ename DESC;
ENAME JOB SAL COMM DEPTNO
---------- --------- ---------- ---------- ----------
JONES MANAGER 2975 20
WARD SALESMAN 1250 500 30
BLAKE MANAGER 2850 30
Usare funzioni su singola riga per personalizzare l’output
Esistono numerose funzioni in tutti gli RDBMS che ti aiutano a svolgere attività comuni come ottenere la lunghezza di una stringa, unire stringhe, formattare, funzioni matematiche, ecc. In SQL ci sono due tipi di funzioni di riga:
- Funzioni su singola riga
- Funzioni su più righe
Funzioni su singola riga:
Queste funzioni si applicano a ciascuna riga e restituiscono un risultato per riga. CONCAT() è una funzione di manipolazione dei caratteri su singola riga. Possono essere usate nelle clausole SELECT, WHERE e ORDER BY. In tutti gli RDBMS esistono funzioni su singola riga per:
- Manipolazione di stringhe
- Data e ora
- Numeri
- Conversione
Database diversi hanno nomi di funzione diversi per la stessa funzionalità. Qui ti mostrerò funzioni comuni utili tra Microsoft SQL Server, Oracle e MySQL. Alcune le hai già viste sopra. Alla fine di questa sezione troverai i link all’elenco completo delle funzioni dei vari vendor per esercitarti.
Vediamo ciascuna categoria:
Funzioni di manipolazione dei caratteri
LOWER(): converte una stringa in minuscolo.
SELECT lower(ename) as ename
FROM employee;
ENAME
----------
smith
allen
ward
jones
blake
UPPER(): converte una stringa in maiuscolo.
SELECT upper(ename) as ename
FROM employee;
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
SUBSTR()[Oracle, MySQL]: restituisce la sottostringa come specificato SUBSTR(string, start-position, length)
SUBSTRING()[SQL Server]: restituisce la sottostringa come specificato SUBSTRING(string, start-position, length).
SELECT SUBSTR(ename,2,3) as substr_ename FROM employee;
Per SQL Server sostituisci semplicemente il nome funzione con SUBSTRING.
SUBSTR_ENAME ------------ MIT LLE ARD ONE LAK
LENGTH()[Oracle, MySQL]: restituisce la lunghezza della stringa tra parentesi
LEN()[SQL Server]: restituisce la lunghezza della stringa tra parentesi
SELECT LENGTH(ename) as len_ename FROM employee;
Per SQL Server sostituisci il nome funzione con LEN.
LEN_ENAME
----------
5
5
4
5
5
Funzioni come padding a sinistra o destra, replace, ecc., differiscono nella sintassi tra i vari database. Elenco delle funzioni carattere e stringa per i tre vendor:
Funzioni numeriche
| Nome funzione | Funzione |
|---|---|
| ROUND(m,n): | Arrotonda il valore m a n decimali specificati. |
| ABS(m,n): | Restituisce il valore assoluto di un numero |
| FLOOR(n): | FLOOR restituisce il più grande intero minore o uguale a n. |
| MOD(m,n): | Restituisce il resto della divisione di m per n (non disponibile in SQL Server, che usa l’operatore %: 35 % 6) |
Oracle:
SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24) from dual;
OUTPUT:
ROUND(45.926,2) MOD(11,5) FLOOR(34.4) ABS(-24)
--------------- ---------- ----------- ----------
45.93 1 34 24
MySQL:
SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24);
SQL Server:
SELECT ROUND(45.926,2) as round,11%5 as mod, FLOOR(34.4) as floor,ABS(-24) as abs;
Elenco delle funzioni numeriche per i tre vendor:
Funzioni di conversione
Le funzioni di conversione servono a convertire un tipo di dato in un altro. Le funzioni di conversione differiscono da server a server. Qui ti do un esempio per Oracle che usa la funzione to_char. Questo ti aiuterà a capire come usare le funzioni di conversione per personalizzare l’output. Il formato data predefinito di Oracle è DD-MON-YY
SELECT ename, to_char(hiredate,'DD, MONTH YYYY') as Hiredate, to_char(hiredate,'DY') as Day
from employee;
ENAME Hiredate DAY
---------- -------------------------------------------- ------------
SMITH 17,DECEMBER 1980 WED
ALLEN 20,FEBRUARY 1981 FRI
WARD 22,FEBRUARY 1981 SUN
JONES 02,APRIL 1981 THU
BLAKE 01,MAY 1981 FRI
Ogni server ha molte funzioni di conversione. Per approfondire ed esplorare, ecco i link alle funzioni di conversione dei diversi server:
- Funzioni di conversione in Microsoft SQL Server
- Funzioni di conversione Oracle
- Funzioni di conversione MySQL
Funzioni di data e ora
Le funzioni di data e ora ti permettono di manipolare le date aggiungendo giorni, calcolando i mesi tra due date, ecc. Queste funzioni sono utili nella generazione di report. Le funzioni di data variano anch’esse tra i database. Una funzione può svolgere la stessa operazione ma avere un nome diverso in un altro database. Qui vedrai qualche esempio e poi ti fornirò i link alle funzioni di data dei database più comuni: Oracle, SQL Server e MySQL. Prova pure questi esempi sul tuo server!
- Funzioni di data e ora Oracle
- Funzioni di data e ora MySQL
- Funzioni di data e ora in Microsoft SQL Server
Raggruppare i risultati SQL
Le funzioni di gruppo o su più righe si applicano a gruppi e restituiscono un risultato per gruppo. Ti serviranno quando vuoi trovare fatti come le vendite totali per trimestre, il prezzo medio di un prodotto in un certo periodo, il più alto investimento ricevuto dalla tua azienda questo mese e così via.
Report con dati aggregati usando le funzioni di gruppo
La clausola GROUP BY si usa per raggruppare i risultati, spesso insieme a funzioni come COUNT, MAX, MIN, AVG e SUM. La sintassi di GROUP BY è:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
Tieni a mente alcuni punti per GROUP BY:
- Gli alias non possono essere usati nella clausola GROUP BY
- La clausola WHERE viene sempre prima di GROUP BY
- La clausola HAVING viene dopo GROUP BY e applica condizioni sulle funzioni di gruppo.
- La clausola ORDER BY viene sempre per ultima
COUNT è una funzione di gruppo che conta il numero di righe in base a una condizione o senza condizioni. COUNT ignora i valori null.
Conta tutti i dipendenti il cui nome inizia con "A":
SELECT count(ename)
FROM employee
WHERE ename LIKE 'A%';
Output:
COUNT(ENAME)
------------
1
Trova stipendio totale, medio, minimo e massimo nella tabella employee:
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL"
FROM employee;
TOTAL SAL AVG SAL MIN SAL MAX SAL
---------- ---------- ---------- ----------
9475 1895 800 2975
Puoi raggruppare il risultato sopra per ogni reparto:
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL", deptno
FROM employee
GROUP BY deptno;
TOTAL SAL AVG SAL MIN SAL MAX SAL DEPTNO
---------- ---------- ---------- ---------- ----------
5700 1900 1250 2850 30
3775 1887.5 800 2975 20
Puoi anche calcolare varianza e deviazione standard dei valori con le seguenti funzioni:
| Per Oracle e MySQL | MS SQL SERVER |
|---|---|
| SELECT STDDEV(column_name) FROM table_name; | SELECT STDEV(column_name) FROM table_name; |
| SELECT VARIANCE(column_name) FROM table_name; | SELECT VAR(column_name) FROM table_name; |
Puoi raggruppare i risultati per più di una colonna.
Trova la somma degli stipendi per ciascuna mansione all’interno di ogni reparto:
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job;
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
800 20 CLERK
2850 30 SALESMAN
2975 20 MANAGER
2850 30 MANAGER
Puoi limitare i risultati di gruppo con la clausola HAVING. Si usa solo per condizioni sui gruppi.
Nota: non usare WHERE per applicare condizioni su funzioni di gruppo.
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job
HAVING sum(sal)>1000
ORDER BY sum(sal);
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
2850 30 SALESMAN
2850 30 MANAGER
2975 20 MANAGER
Visualizzare dati da più tabelle
In questa sezione vedremo i seguenti join:
- Prodotto cartesiano/Cross join
- Inner join/EquiJoin
- Natural join
- Outer join (Left, Right, Full)
- Self join
Dovrai creare report che prelevano dati da più tabelle. Vedi l’esempio qui sotto:
Per generare un report come sopra, devi collegare le tabelle employee e department e prelevare dati da entrambe. Per questo in SQL si usano i join.
Prodotto cartesiano/Cross join:
Il prodotto cartesiano si forma quando ogni tupla di una relazione R crea una combinazione con ogni tupla di una relazione S.
L’operazione di prodotto cartesiano, detta anche Cross Join, moltiplica due tabelle per formare una relazione che consiste di tutte le coppie possibili di tuple tra le due tabelle. Se una relazione R ha I tuple e M attributi e un’altra relazione S ha J tuple e N attributi, il prodotto cartesiano avrà I×J tuple con M+N attributi. Un prodotto cartesiano può essere formato anche con l’istruzione standard SQL CROSS JOIN.
SELECT empno, ename, dname
FROM employee, department;
OR
SELECT empno, ename, dname
FROM employee CROSS JOIN department;
EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH ACCOUNTING
7499 ALLEN ACCOUNTING
7521 WARD ACCOUNTING
7566 JONES ACCOUNTING
7698 BLAKE ACCOUNTING
7369 SMITH RESEARCH
7499 ALLEN RESEARCH
7521 WARD RESEARCH
7566 JONES RESEARCH
7698 BLAKE RESEARCH
7369 SMITH SALES
EMPNO ENAME DNAME
---------- ---------- --------------
7499 ALLEN SALES
7521 WARD SALES
7566 JONES SALES
7698 BLAKE SALES
15 righe selezionate.
Poiché la tabella employee ha 5 tuple e department ne ha 3, il prodotto cartesiano ha 5×3=15 righe. Un prodotto cartesiano si genera quando:
- Non si usa una condizione di join
- La condizione di join non è valida o non è formulata correttamente
Quando servono dati da due o più tabelle, si crea una condizione di join usando gli attributi comuni che collegano le tabelle coinvolte. Di solito si tratta di chiave primaria e chiave esterna.
I prodotti cartesiani sono usati per simulare grandi volumi di dati a scopo di test.
*Inner Join/EquiJoin:
L’Inner Join o EquiJoin (termine usato dagli sviluppatori Oracle) usa le relazioni tra chiave primaria ed esterna per unire due o più tabelle:
SELECT ename, dname
FROM employee e,department d
WHERE e.deptno=d.deptno;
OR
SELECT ename, dname
FROM employee e
JOIN department d
ON e.deptno=d.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
BLAKE SALES
Nella query sopra, deptno è chiave primaria nella tabella department e chiave esterna in employee. Puoi aggiungere condizioni aggiuntive usando gli operatori logici visti nelle sezioni precedenti.
Cose da ricordare sui JOIN:
- Se lo stesso nome di colonna appare in più tabelle, il nome deve essere preceduto dal nome della tabella. È buona pratica farlo comunque per maggiore chiarezza.
- Per unire n tabelle servono almeno n-1 condizioni di join. Ad esempio, per unire quattro tabelle servono almeno tre join.
Alias di tabella: gli alias di tabella, come in FROM employee e, department d dove e e d sono alias, aiutano il motore del database a capire da quale tabella proviene la colonna, specialmente quando le stesse colonne compaiono in più tabelle coinvolte, altrimenti si avrebbe un errore di colonna ambigua. Inoltre, risparmia tempo nel riscrivere nomi di tabelle lunghi.
Puoi creare anche nonequi-join, che uniscono tabelle basandosi su condizioni diverse dall’uguaglianza. Considera un’altra tabella, salgrade, che contiene le informazioni sul grado di un dipendente in base allo stipendio:
Vuoi trovare il grado di ciascun dipendente in base allo stipendio: i gradi sono memorizzati in salgrade mentre lo stipendio è in employee:
SELECT e.ename, e.sal, s.grade
FROM employee e, salgrade s
WHERE e.sal between s.losal AND s.hisal;
ENAME SAL GRADE
---------- ---------- ----------
JONES 2975 4
BLAKE 2850 4
ALLEN 1600 3
WARD 1250 2
SMITH 800 1
Esempio: ti viene chiesto di fornire nomi dipendenti, stipendio, grado e nome reparto. Poiché lo stipendio è nella tabella employee, i gradi in salgrade e il nome reparto in department, dovrai unire tre tabelle:
SELECT e.ename, e.sal, d.dname, s.grade FROM employee e, department d, salgrade s WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal; Output: ENAME SAL DNAME GRADE ---------- ---------- -------------- ---------- JONES 2975 RESEARCH 4 BLAKE 2850 SALES 4 ALLEN 1600 SALES 3 WARD 1250 SALES 2 SMITH 800 RESEARCH 1
Nell’esempio sopra sono coinvolte tre tabelle e due condizioni di join, una delle quali è un nonequi-join.
Natural Join:
I natural join consentono ai database di unire automaticamente le tabelle facendo combaciare le colonne con lo stesso nome. Se le colonne corrispondenti hanno lo stesso nome ma tipi di dato diversi, viene generato un errore.
Sintassi: SELECT FROM table1 NATURAL JOIN table2; SELECT FROM employee NATURAL JOIN department; Poiché il natural join trova da sé le colonne corrispondenti, potrebbe individuarne più di una con lo stesso nome ma con tipi di dato diversi, causando un errore. Perciò si usa la clausola USING per specificare le colonne su cui effettuare un equi join.
Nota: NATURAL JOIN e USING sono due clausole diverse e si usano separatamente. Non possono essere usate insieme, sono mutuamente esclusive.
SELECT e.ename, d.dname, e.sal FROM employee e JOIN department d USING (deptno) WHERE deptno=20; output: ENAME DNAME SAL ---------- -------------- ---------- SMITH RESEARCH 800 JONES RESEARCH 2975
Le colonne nella clausola USING non possono usare il nome o il prefisso della tabella in nessun punto della query. Per esempio, il seguente è errato:
SELECT e.ename, d.dname, e.sal FROM employee e JOIN department d USING (d.deptno) WHERE d.deptno=20;
d.deptno è errato. Va usato solo deptno. Outer Join:
Ci sono tre outer join:
- Left Outer Join: un join tra due tabelle che restituisce i risultati dell’inner join, più le righe non corrispondenti della tabella di sinistra, è detto LEFT OUTER JOIN
- Right Outer Join: un join tra due tabelle che restituisce i risultati dell’inner join, più le righe non corrispondenti della tabella di destra, è detto RIGHT OUTER JOIN.
- Full Outer Join: un join tra due tabelle che restituisce i risultati dell’inner join, più le righe non corrispondenti della tabella di destra, è detto RIGHT OUTER JOIN.
Vediamoli uno per uno:
SELECT e.ename, s.grade FROM salgrade s LEFT OUTER JOIN employee e ON e.sal BETWEEN s.losal AND s.hisal; Output: ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH ALLEN 30 SALES WARD 30 SALES BLAKE 30 SALES.sal BETWEEN s.losal AND s.hisal; Output: ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH ALLEN 30 SALES WARD 30 SALES BLAKE 30 SALESSELECT e.ename, d.deptno, d.dname
FROM employee e RIGHT OUTER JOIN department d
ON e.deptno=d.deptno;
output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTINGSELECT e.ename, d.deptno, d.dname
FROM employee e FULL OUTER JOIN department d
ON e.deptno=d.deptno;
Output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTING
La tabella department aveva una riga non corrispondente per deptno 10 e non sono state trovate righe non corrispondenti per employee. Analogamente, salgrade ha una riga non corrispondente per grade 5.
Self join:
Ci sono situazioni in cui vuoi unire una tabella con se stessa. Per esempio, la tabella employee contiene informazioni su tutti i dipendenti, inclusi i manager (vedi la tabella employee sopra). Se ti chiedono di trovare i dipendenti e i loro manager, dovrai unire la tabella a se stessa. I self join si costruiscono così:
SELECT e.ename as Employee, m.ename as Manager
FROM employee e, employee m
WHERE e.mgr=m.empno;
EMPLOYEE MANAGER
---------- ----------
ALLEN BLAKE
WARD BLAKE
Usare le sottoquery per risolvere query
Una sottoquery è una query dentro una query. È utile per scomporre query grandi in segmenti. Le sottoquery possono essere annidate e possono essere usate nelle seguenti clausole SQL:
- WHERE
- FROM
- HAVING
Considera un esempio: ti chiedono di trovare i dipendenti con stipendio superiore a quello di un altro dipendente, in questo caso James. Come procederesti? Puoi scomporre la query così:
- Trova lo stipendio di James
- Confronta lo stipendio di James con tutti i dipendenti
Questo si risolve con una sottoquery. La sottoquery (interna) viene eseguita una volta prima della query principale (esterna).
SELECT empno, ename FROM employee WHERE sal>(SELECT sal from employee where ename='JAMES');
Tipi di sottoquery:
-
Sottoquery a riga singola: query che restituisce una sola riga dalla SELECT interna.
-
Sottoquery a più righe: query che restituisce più di una riga dalla SELECT interna.
Punti da ricordare:
- Racchiudi le sottoquery tra parentesi.
- Metti le sottoquery a destra dell’operatore di confronto.
- Usa operatori a riga singola (>, <, >=, <=, <>) con sottoquery a riga singola.
- Usa operatori multi-riga (IN, ANY, ALL) con sottoquery a più righe.
Sottoquery a riga singola:
Ti viene chiesto di trovare i nomi dei dipendenti la cui mansione è uguale a quella dell’empno 7521
SELECT ename, job
FROM employee
WHERE job=(SELECT job FROM employee WHERE empno=7521);
ENAME JOB
---------- ---------
ALLEN SALESMAN
WARD SALESMAN
Seleziona lo stipendio massimo dei reparti in cui lo stipendio massimo è maggiore o uguale allo stipendio massimo del reparto 20
SELECT deptno, max(sal)
FROM employee
GROUP BY deptno
HAVING max(sal)>=(SELECT max(sal) FROM employee WHERE deptno=20);
DEPTNO MAX(SAL)
---------- ----------
20 2975
Sottoquery a più righe:
Trova i dipendenti il cui stipendio è uguale a uno qualsiasi degli stipendi dei dipendenti con mansione Manager. Questa sarà una sottoquery a più righe perché la sottoquery può restituire più righe.
SELECT ename, job, sal
FROM employee
WHERE sal IN (SELECT sal FROM employee WHERE job='MANAGER');
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
Quando le sottoquery sono usate nella clausola FROM, agiscono come una tabella temporanea che non esiste fisicamente in archiviazione ma è una vista sui dati. Per esempio:
SELECT e.ename, e.job, e.sal
FROM employee e, (SELECT deptno FROM department WHERE loc='DALLAS') d
WHERE e.deptno=d.deptno;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
Usare gli operatori insiemistici
In SQL gli operatori insiemistici si usano per combinare i risultati di più query in un unico risultato. Si basano sulla teoria degli insiemi e sulle sue operazioni come UNION, MINUS, INTERSECT. Qui vedrai come usare gli operatori per ottimizzare le query. In questo tutorial tratteremo i seguenti operatori:
Informazione: le query che usano operatori insiemistici si chiamano istruzioni composte.
- UNION e UNION ALL
- INTERSECT
- EXCEPT (standard SQL) e MINUS (specifico Oracle)
UNION
Considera due relazioni (tabelle) R e S: UNION seleziona tutte le righe da R e tutte le righe da S eliminando i duplicati. Il numero massimo di righe restituite può essere r+s, dove r è il numero di righe in R e s in S.
Seleziona tutti i nomi di reparto inclusi quelli dei reparti a cui appartengono dipendenti assunti prima del 23-OCT-1999
SELECT dname
FROM department
UNION
SELECT dname
FROM department, employee
WHERE department.deptno=employee.deptno AND employee.hiredate<to_date('23-OCT-1999');
DNAME
--------------
ACCOUNTING
OPERATIONS
RESEARCH
SALES
Cose da ricordare per UNION:
- Il numero e i tipi di dato delle colonne selezionate devono essere identici in tutte le SELECT della query.
- I nomi delle colonne non devono essere necessariamente identici.
- L’output è ordinato in ordine crescente in base alla prima colonna della clausola SELECT.
- I valori NULL non sono ignorati durante il controllo dei duplicati.
UNION ALL combina i risultati di una o più query e NON rimuove i duplicati; di conseguenza, non si può usare DISTINCT. Considera un’altra tabella emp che contiene dipendenti dall’anno 2000. Ottieni tutti numero, nome e mansione dei dipendenti dalle tabelle emp ed employee:
SELECT empno, ename, job
FROM employee
UNION ALL
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
EMPNO ENAME JOB
---------- ---------- ---------
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
INTERSECT
L’intersezione segue la stessa regola della teoria degli insiemi: restituisce i valori comuni a due insiemi. Considera due relazioni R e S: INTERSECT restituirà l’insieme di tutte le tuple comuni a entrambe.
SELECT empno, ename, job
FROM employee
INTERSECT
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
Cose da ricordare per INTERSECT:
- Il numero e i tipi di dato delle colonne selezionate devono essere identici in tutte le SELECT della query.
- I nomi delle colonne non devono essere necessariamente identici.
- INTERSECT non ignora i valori NULL
EXCEPT e MINUS:
EXCEPT (usato in SQL Server) e MINUS (specifico Oracle) hanno la stessa funzione: selezionare tutte le righe distinte restituite dalla prima query ma non dalla seconda. Per capire questo operatore, considera la seguente tabella:
Supponi di dover trovare i prodotti con quantità tra 1 e 100 ma non tra 50 e 75. Qui userai EXCEPT se stai usando SQL Server e MINUS se stai usando Oracle.
SELECT prod_name, qty FROM products WHERE qty BETWEEN 1 AND 100 EXCEPT SELECT prod_name, qty FROM products WHERE qty BETWEEN 50 AND 75;
Per Oracle:
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
MINUS
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;
PROD_NAME QTY
------------- ----------
COLGATE 1
SENSODYNE 100
SENSODYNE TOOTHBRUSH 30
Cose da ricordare per EXCEPT/MINUS:
- Il numero e i tipi di dato delle colonne selezionate devono essere identici in tutte le SELECT della query.
- I nomi delle colonne non devono essere necessariamente identici.
- Tutte le colonne nella clausola WHERE devono essere nella clausola SELECT perché l’operatore MINUS funzioni.
Congratulazioni!
Siamo alla fine del tutorial. In questo percorso hai imparato una quantità significativa di SQL che ti aiuterà a padroneggiarlo nel tuo cammino nella data science. SQL si usa per generare report dai database. In questo tutorial hai appreso le basi dei database e di SQL, i tipi di dato comuni, le funzioni che aiutano a formattare i report, ad aggregare i risultati creando riepiloghi e anche i modi per recuperare dati da tabelle diverse in base alle tue esigenze! Questo tutorial è pensato specificamente per chi studia data science e non solo li aiuterà con i database relazionali, ma li preparerà anche ad affrontare senza problemi i database NoSQL e a usare le competenze SQL negli studi di Big Data.
Se vuoi imparare di più su SQL, segui il corso gratuito di DataCamp Intro to SQL for Data Science.
Riferimenti
Argomenti
