SQL parte 2

Ricapitolando

SQL (Structured Query Language)

 

è il linguaggio di interrogazione più diffuso tra quelli usati per l’interazione con i principali Database Management Systems (DBMS)

 

Ricapitolando

Chiave primaria

è un insieme di attributi che permette di individuare univocamente un record o tupla in una tabella o relazione.

Indice

è un insieme di attributi che permettono di ricercare più velocemente i dati all'interno di una tabella

Chiave esterna

è un insieme di attributi che permettono di relazionare una o più tabelle tra di loro

Relazioni tra tabelle

 

  

Tabella

sono rappresentazioni delle entità concrete

Sintassi SQL

Selezione
(le colonne della tabella)

Provenienza

Filtri
(le condizioni e la logica per estrrre ciò che ci serve)

Raggruppamento
(per raggruppare i dati e avere risultati aggregati)

Filtri su raggruppamenti

Sintassi SQL: la proiezione

Nella prima parte della query si specificano i dati che vogliamo vengano visualizzati nel nostro risultato.

 

La dicitura "as" identifica come si deve chiamare la colonna nel risultato

I dati possono anche essere combinazioni di:

 

  - dati semplici (attirbuti contenuti nel database)

  - aggregati (somme, medie, conteggi)

  - condizioni (if-then)

  - complessi (sotto query, funzioni)

Sintassi SQL: provenienza e join

Qui si identificano le tabelle da dove si dovranno prendere i dati o fare i filtri

LE JOIN

Servono per creare le relazioni tra le tabelle: sono quei collegamenti che le relazionano tra di loro.

Le join possono essere esplicite o implicite.

In un DB relazionale, se si usano join esplicite con indici definiti migliori le performance

TIPI di JOIN:

INNER, LEFT, RIGHT

[INNER|LEFT|RIGHT] JOIN <tabella> ON <condizione di aggregazione>

Sintassi SQL: filtri e raggruppamenti

Tutti i filtri e la logica per estrarre i dati avviene principalmente qui.

Si usano:

  - condizioni di AND, OR, NOT

  - operazioni matematiche di =, ≠, >, < , ≤, ≥

  - operatori complessi LIKE, IN, NOT IN

Operatori di raggruppamento ed ordinamento

 

Sono operatori che svolgono azioni sui dati filtrati.

  - Group by: raggruppa secondo alcuni attributi che si elencano

  - Having: filtra i dati secondo la logica di raggruppamento

  - Order by: ordina i dati secondo alcuni attributi che si elencano

Sintassi SQL: Join

Esercizi

Il titolo dei film in cui recita Mastroianni

SELECT F.Titolo
FROM Film as F
JOIN Recita as R ON F.CodFilm = R.CodFilm
JOIN Attore as A ON A.CodAttore = R.CodAttore
WHERE R.Nome = 'Mastroianni'

Union & Except

UNION viene utilizzato per combinare il risultato di due o più istruzioni SELECT.

EXCEPT (o INTERSECT) viene utilizzato per combinare il risultato di due o più istruzioni SELECT prendendo le tuple della query di sinistra non generate dalla query di destra.

Si noti che ogni istruzione SELECT deve avere lo stesso numero di colonne. Le colonne devono anche avere tipi di dati simili e devono essere nello stesso ordine.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2 ;
SELECT column_name(s) FROM table1
EXCEPT
SELECT column_name(s) FROM table2 ;

Union & Except


SELECT
  TO_DATE(D."U_data") "date",
  M."DocEntry" "id_appointment",
  M."U_codBP" "id_patient",
  OCPR."LastName" "cognome",
  OCPR."FirstName" "nome"
FROM "@SAINS_PRENOTAZIONI" M
INNER JOIN "@SAINS_PRESTAZIONI_P" D ON M."DocEntry" = D."DocEntry"
INNER JOIN "OCRD" P ON M."U_codBP" = P."CardCode"
LEFT JOIN OCPR ON OCPR."CardCode" = P."CardCode"
INNER JOIN "NNM1" T1 ON T1."Series" = M."Series"
INNER JOIN "OUBR" S ON S."Code" = T1."BPLId"
WHERE M."U_tipo" != 'M'	AND (M."U_codConv" = 'A-65'OR D."U_listino" = '60')

UNION 

SELECT
  TO_DATE(D."U_data") "date",
  M."DocEntry" "id_appointment", 
  M."U_codBP" "id_patient", 
  OCPR."LastName" "cognome",
  OCPR."FirstName" "nome"
FROM "@SAINS_PRENOTAZIONI" M
INNER JOIN "@SAINS_PRESTAZIONI_P" D ON M."DocEntry" = D."DocEntry"
INNER JOIN "OCRD" P ON M."U_codBP" = P."CardCode"
LEFT JOIN OCPR ON OCPR."CardCode" = P."CardCode"
INNER JOIN "NNM1" T1 ON T1."Series" = M."Series"
INNER JOIN "OUBR" S ON S."Code" = T1."BPLId"
WHERE M."U_tipo" != 'M'	AND D."U_codArt" IN ('controllo15', 'controllo30', 'controllo45', 'controllo60')

Altre tipologie di query

CREATE

INSERT

UPDATE

ALERT

Per creare tabelle all'interno del DB

ALTRE ...

Ci sono altre tipologie che servono per gestire ed amministrare un DB

Per modificare la struttura di una tabella e le sue relazioni

Per popolare i dati in un DB 

Per modificare i dati all'interno di una tabella

DELETE

Per cancellare i dati all'interno di una tabella

DROP

Per cancellare un'intera tabella

TRUNCATE

Per ripulire dai dati la tabella (svuotamento)

Insert

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

Customers : tabella in cui voglio inserire i dati

 

(CustomerName, City, Country) : sono le colonne che andrò a popolare

 

('Cardinal', 'Stavanger', 'Norway') : sono i valori che voglio inserire

Update

UPDATE Customers
SET City = 'Milan' 
WHERE CustomerName = 'Mario'

Customers : tabella in cui voglio modificare i dati

 

City : è il campo con il valore che voglio modificare

 

WHERE : regole di filtraggio per fare update mirati

Delete

DELETE FROM Customers
WHERE CustomerName = 'Mario'

Customers : tabella in cui vogliamo cancellare delle righe

 

WHERE : regole di filtraggio per fare update mirati

ESERCIZI

MUSEI (NomeM, Città)

ARTISTI (NomeA, Nazionalità)

OPERE (Codice, Titolo, NomeM*, NomeA*)

PERSONAGGI (Personaggio, Codice*)

  1. Il codice ed il titolo delle opere di Tiziano conservate alla “National Gallery”
  2. Per ciascun museo di Londra, il numero di opere di artisti italiani ivi conservate
  3. I musei che conservano almeno 20 opere di artisti italiani
  4.  Il nome dei musei di Londra che non conservano opere di artisti italiani, eccettuato Tiziano
  5. Per ogni museo, il numero di opere divise per la nazionalità dell’artista

ESERCIZI

Il codice ed il titolo delle opere di Tiziano conservate alla “National Gallery”

SELECT O.Codice, O.Titolo
FROM Opere O
WHERE O.NomeA = “Tiziano” 
AND O.NomeM = “National Gallery”

ESERCIZI

Per ciascun museo di Londra, il numero di opere di artisti italiani ivi conservate

SELECT M.NomeM. Count(*) AS NumOpereItaliane
FROM O.Opere, M.Musei, A.Artisti
WHERE M.Città = “Londra” 
AND A.Nazionalità = “Italia”
AND M.NomeM = O.NomeM 
AND O.NomeA = A.NomeA
GROUP BY M.NomeM 
SELECT M.NomeM. Count(*) AS NumOpereItaliane
FROM Opere O
JOIN Musei M ON M.Nome_M = O.Nome_M
JOIN Artisti A ON O.Nome_A = A.Nome_A
WHERE M.Città = “Londra” 
AND A.Nazionalità = “Italia”
GROUP BY M.NomeM

ESERCIZI

I musei che conservano almeno 20 opere di artisti italiani

SELECT O.NomeM, Count (*) as conteggio
FROM Opere O
JOIN Artisti A ON O.NomeA = A.NomeA
WHERE A.Nazionalita = "Italia" 
GROUP BY O.NomeM
HAVING conteggio >=20

ESERCIZI

Il nome dei musei di Londra che non conservano opere di artisti italiani, eccettuato Tiziano

SELECT M.NomeM
FROM Musei M
WHERE M.Citta = "Londra” 
AND M.Nome_A IN
( 
	SELECT O.Nome_A
 	FROM Opere O, Artisti A
    JOIN Artisti A ON M.Nome_A = O.Nome
 	WHERE A.NomeA <> “Tiziano ” 
    AND A.Nazionalità=”Italia”
    AND M.Nome_M = O.Nome_M
 )

ESERCIZI

Per ogni museo, il numero di opere divise per la nazionalità dell’artista

SELECT O.NomeM, A.Nazionalita, Count(*)
FROM Opere O
JOIN Artisti A ON M.Nome_A = O.Nome
WHERE O.NomeA = A.NomeA
GROUP BY O.NomeM, A.Nazionalita

ESERCIZI

AUTO (Targa, Marca, Cilindrata, Potenza, CodF*, CodAss*)

PROPRIETARI (CodF, Nome, Residenza)

ASSICURAZIONI (CodAss, Nome, Sede)

SINISTRO (CodS, Località, Data)

AUTOCOINVOLTE (CodS*, Targa*, ImportoDelDanno)

  1. Targa e Marca delle Auto di cilindrata superiore a 2000 cc o di potenza superiore a 120 CV
  2. Per ciascuna Assicurazione, il nome, la sede ed il numero di auto assicurate
  3. La targa delle auto che non sono state coinvolte in sinistri dopo il 20/01/01
  4. CodF e Nome di coloro che possiedono più di un’auto

ESERCIZI

Targa e Marca delle Auto di cilindrata superiore a 2000 cc o di potenza superiore a 120 CV

SELECT A.Targa, A.Marca
FROM Auto A
WHERE A.Cilindrata > 2000 
OR A.Potenza > 120

ESERCIZI

Per ciascuna Assicurazione, il nome, la sede ed il numero di auto assicurate

SELECT As.Nome, As.Sede, count(*) as NumAuto
FROM Assicurazioni As
JOIN Auto Au ON As.Cod_ass = Au.Cod_ass
GROUP BY As.CodAss, As.Nome, As.Sede

ESERCIZI

La targa delle auto che non sono state coinvolte in sinistri dopo il 20/01/01

SELECT A.Targa
FROM Auto A
WHERE A.Targa Not In (
	SELECT AC.Targa
 	FROM AutoCoinvolte AC, Sinistri S
 	WHERE AC.CodS=S.CodS and S.Data>20/01/01
)

ESERCIZI

CodF e Nome di coloro che possiedono più di un’auto

SELECT P.CodF, P.Nome
FROM Proprietari P
JOIN Auto A ON A.Cod_F = P.Cod_F
GROUP BY P.CodF, P.Nome
HAVING count(*) >1
SELECT P.CodF, P.Nome
FROM Proprietari P
WHERE 1 < (
	SELECT *
 	FROM Auto A
 	WHERE P.CodF = A.CodF
)

Sql conosciamolo part.2

By Saverio M.

Made with Slides.com

Sql conosciamolo part.2

  • 101

More from Saverio M.