Sottoquery nidificate e collegate in SQL, predicato EXISTS. Utilizzo dell'operatore EXISTS Interroga utilizzando la funzione esiste

DOVE ESISTE

La sottoquery viene verificata per la presenza di una o più righe. Se almeno una riga corrisponde alla query, viene restituito il valore booleano TRUE. Quando viene specificata la parola chiave facoltativa NOT, viene restituito il valore booleano TRUE se la sottoquery non restituisce alcuna riga corrispondente.

sottoquery

In base alla sottoquery completamente formata, viene recuperato il set di dati risultante.

Regole generali

L'operatore EXISTS verifica l'esistenza di una o più righe in una sottoquery di una query padre.

SELECT * FROM lavori DOVE NON ESISTE (SELECT * FROM dipendente WHERE lavori.job_id=employye.job_id);

In questo esempio viene controllata la sottoquery dei record utilizzando la parola chiave aggiuntiva NOT. Nell'esempio seguente vengono cercati record specifici in una sottoquery per recuperare il set di risultati principale.

SELECT au_lname FROM autori WHERE EXISTS (SELECT * FROM editori WHERE autori.città=editori.città);

Questa query restituisce i cognomi degli autori (au_lname) che vivono nella stessa città degli editori. Tieni presente che puoi utilizzare un asterisco nella sottoquery perché la sottoquery deve restituire solo un record con il valore booleano TRUE. In questi casi, le colonne non contano. Il punto chiave è l'esistenza della stringa.

In molte query, l'operatore EXISTS esegue la stessa funzione di ANY. L'operatore EXISTS è in genere più efficiente se utilizzato con query correlate.

L'operatore EXISTS è semanticamente equivalente all'operatore ANY.

Una sottoquery in un'istruzione EXISTS esegue in genere uno dei due tipi di ricerca. La prima opzione consiste nell'utilizzare un carattere jolly, un asterisco (ad esempio, SELECT * FROM...), nel qual caso non recupererai alcuna colonna o valore specifico. L'asterisco qui significa "qualsiasi colonna". La seconda opzione consiste nel selezionare solo una colonna specifica nella sottoquery (ad esempio, SELECT aujd FROM). Alcune piattaforme individuali consentono sottoquery su più colonne (ad esempio SELECT aujd, aujname FROM...). Tuttavia, questa funzionalità è rara e dovrebbe essere evitata nel codice che deve essere portato su altre piattaforme.

Differenze tra le piattaforme

Tutte le piattaforme supportano l'operatore EXISTS nella forma descritta sopra.

"Prima era più facile" - ho pensato mentre mi sedevo per ottimizzare la query successiva in SQL studio di gestione. Quando scrivevo sotto MySQL, tutto era molto più semplice: o funziona o no. O rallenta oppure no. Spiegare ha risolto tutti i miei problemi, non serviva altro. Ora ho un ambiente potente per lo sviluppo, il debug e l'ottimizzazione di query e procedure/funzioni, e tutta questa confusione crea solo più problemi secondo me. E perché tutto? Perché l'ottimizzatore di query integrato è malvagio. Se in MySQL e PostgreSQL scrivo

Seleziona * da a, b, c dove a.id = b.id, b.id = c.id

e ciascuno dei tablet avrà almeno 5k linee: tutto si bloccherà. E grazie a Dio! Perché altrimenti lo sviluppatore, nella migliore delle ipotesi, sviluppa la pigrizia per scrivere correttamente e, nel peggiore dei casi, non capisce affatto cosa sta facendo! Dopotutto, la stessa query in MSSQL funzionerà in modo simile

Seleziona * da a unisci b su a.id = b.id unisci c su b.id = c.id

L'ottimizzatore integrato esaminerà la richiesta ridondante e tutto andrà bene.

Deciderà anche da solo cosa è meglio fare: esistere o unirsi e molto altro ancora. E tutto funzionerà nel modo più ottimale possibile.

Ce n'è solo uno MA. Ad un certo punto, l'ottimizzatore inciamperà interrogazione complessa e passa, e poi ottieni un grosso problema. E potresti non capirlo subito, ma quando il peso delle tabelle raggiunge la massa critica.

Quindi eccoci al punto dell'articolo. esiste e sono in corso operazioni molto pesanti. Questa è in realtà una sottoquery separata per ciascuno linee di risultato. E se c'è anche la nidificazione, generalmente si spengono le luci. Tutto andrà bene quando verranno restituite 1, 10, 50 righe. Non sentirai la differenza e forse l'adesione sarà ancora più lenta. Ma quando viene ritirata la 500, iniziano i problemi. 500 sottoquery all'interno di una richiesta sono gravi.

Sebbene dal punto di vista della comprensione umana, in ed esiste siano migliori, ma dal punto di vista dei costi in termini di tempo per le query che restituiscono più di 50 righe, non sono accettabili.

È necessario fare una prenotazione che, naturalmente, se diminuisce da qualche parte, deve arrivare da qualche parte. Sì, l'unione richiede più memoria, perché mantenere l'intera tabella di valori contemporaneamente e utilizzarla è più costoso che eseguire sottoquery per ciascuna riga, liberando rapidamente memoria. È necessario esaminare in modo specifico la richiesta e misurare se l'uso della memoria aggiuntiva per motivi di tempo sarà critico o meno.

Darò esempi di analogie complete. In generale, non ho ancora incontrato query di tale complessità da non poter essere espanse in una cascata di join. Potrebbe volerci un giorno, ma tutto può essere rivelato.

Seleziona * da a dove a.id in (seleziona id da b) seleziona * da a dove esiste (seleziona top 1 1 da b dove b.id = a.id) seleziona * da a join b su a.id = b. id seleziona * da a dove a.id non è in (seleziona id da b) seleziona * da a dove non esiste (seleziona top 1 1 da b dove b.id = a.id) seleziona * da a sinistra unisci b su a. id = b.id dove b.id è nullo

Ripeto: l'ottimizzatore MSSQL ottimizza questi esempi per massima performance e non ci saranno mai persone stupide con richieste così semplici.

Consideriamo ora un esempio di una query reale che ha dovuto essere riscritta perché semplicemente bloccata su alcuni campioni (la struttura è molto semplificata e i concetti sono stati sostituiti, non c'è bisogno di temere qualche non ottimalità della struttura del database ).

È necessario estrarre tutti i "prodotti" duplicati in diversi account, concentrandosi sui parametri del prodotto, del suo gruppo e del gruppo principale, se presente.

Seleziona d.PRODUCT_ID da PRODUCT s, PRODUCT_GROUP sg left join M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_ID = dd .M_PG_DE PENDENCY_CHILD_ID) dove s.PRODUCT_GROUP_ID=sg .PRODUCT_GROUP_ID e d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID e sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC e sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME e s.PRODUCT_NAME=d.PRODUCT_NAME e s.PRODUCT_TYPE=d. PRODUCT_TYPE e s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE e s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT e dg.PRODUCT_GROUP_IS_TMPL=0 e ((sd.M_PG_DEPENDENCY_CHILD_ID è null e dd.M_PG_DEPENDENCY_CHILD_ID è null) o esiste (seleziona 1 da PRODUCT_GROUP sg1, PRODUCT_GROUP dg1 dove sd.M_PG_DEP ENDENCY_PARENT_ID = sg1.PRODUCT_GROUP_ID e gg .M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID e sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC e sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME e))

Quindi questo è il caso in cui l'ottimizzatore si è arreso. E per ogni riga è stata eseguita un'esistenza pesante, che ha ucciso il database.

Seleziona d.PRODUCT_ID da PRODUCT s unisciti a PRODUCT d su s.PRODUCT_TYPE=d.PRODUCT_TYPE e s.PRODUCT_NAME=d.PRODUCT_NAME e s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE e s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT unisciti a PRODUCT_GROUP sg su s.PRODUCT_GRO UP_ID= sg.PRODUCT_GROUP_ID unisciti a PRODUCT_GROUP dg su d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID e sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME e sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC a sinistra unisciti a M_PG_DEPEND ENCY sd su sg .PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID left join M_PG_DEPENDENCY dd su dg. PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID a sinistra unisciti a PRODUCT_GROUP sgp su sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID a sinistra unisciti a PRODUCT_GROUP dgp su dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID e sgp. PRODUC T_GROUP_NAME = dgp.PRODUCT_GROUP_NAME e isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull( dgp.PRODUCT_GROUP_IS_TMPL, 0) dove (sd.M_PG_DEPENDENCY_CHILD_ID è null e dd.M_PG_DEPENDENCY_CHILD_ID è null) o (sgp.PRODUCT_GROUP_NAME non è null e dgp.PRODUCT_GROUP_NAME non è null) vai

Dopo queste trasformazioni, la performance della visualizzazione aumentava esponenzialmente al numero di prodotti trovati. O meglio, il tempo di ricerca è rimasto praticamente indipendente dal numero di corrispondenze ed è stato sempre molto ridotto. Come dovrebbe essere.

Questo è un chiaro esempio di come la fiducia nell'ottimizzatore MSSQL possa rivelarsi uno scherzo crudele. Non fidarti di lui, non essere pigro, unisciti manualmente, pensa ogni volta a cosa è meglio in una determinata situazione: esiste, è dentro o partecipa.

Il predicato del linguaggio SQL EXISTS esegue un'attività logica. IN query SQL questo predicato è usato nelle espressioni della forma

ESISTE (SELEZIONA * DA NOME_TABELLA ...).

Questa espressione restituisce true quando la query trova una o più righe che soddisfano la condizione e restituisce false quando non viene trovata alcuna riga.

Per NON ESISTE è il contrario. Espressione

NON ESISTE (SELEZIONA * DA NOME_TABELLA ...)

restituisce vero quando non viene trovata alcuna riga nella query e falso quando viene trovata almeno una riga.

Le query più semplici con il predicato SQL EXISTS

Negli esempi lavoriamo con il database della biblioteca e le sue tabelle “Libro in uso” (BOOKINUSE) e “Utente” (USER). Per ora abbiamo solo bisogno della tabella “Book in Use” (BOOKINUSE).

Autore	Titolo	Pubanno	Inv_No	ID utente
Tolstoj	Guerra e Pace	2005	28	65
Cechov	Il frutteto dei ciliegi	2000	17	31
Cechov	Storie selezionate	2011	19	120
Cechov	Il frutteto dei ciliegi	1991	5	65
Ilf e Petrov	Le dodici sedie	1985	3	31
Majakovskij	Poesie	1983	2	120
Pastinaca	Dottor Zivago	2006	69	120
Tolstoj	Domenica	2006	77	47
Tolstoj	Anna Karenina	1989	7	205
Puškin	La figlia del capitano	2004	25	47
Gogol	Gioca	2007	81	47
Cechov	Storie selezionate	1987	4	205
Pastinaca	Preferiti	2000	137	18

Esempio 1. Determina gli ID degli utenti a cui sono stati dati i libri di Tolstoj e a cui sono stati dati anche i libri di Cechov. La query esterna seleziona i dati sugli utenti a cui sono stati dati i libri di Tolstoj e il predicato EXISTS specifica una condizione aggiuntiva che viene verificata nella query interna: gli utenti a cui sono stati dati i libri di Cechov. Una condizione aggiuntiva nella richiesta interna è che gli ID utente delle richieste esterne e interne corrispondano: User_ID=tols_user.user_id. La richiesta sarà la seguente:

Questa query restituirà il seguente risultato:

Differenze tra i predicati EXISTS e IN

A prima vista, alle query con il predicato EXISTS si potrebbe avere l'impressione che sia identico predicato IN. Questo è sbagliato. Anche se sono molto simili. Il predicato IN cerca valori dall'intervallo specificato nel suo argomento e, se sono presenti tali valori, vengono selezionate tutte le righe corrispondenti a questo intervallo. Il risultato del predicato EXISTS è una risposta "sì" o "no" alla domanda se esistono valori corrispondenti a quelli specificati nell'argomento. Inoltre il predicato IN è preceduto dal nome della colonna con cui cercare le righe che corrispondono ai valori dell'intervallo. Diamo un'occhiata ad un esempio che mostra la differenza tra il predicato EXISTS e il predicato IN e il problema risolto utilizzando il predicato IN.

Esempio 4. Determinare gli ID degli utenti a cui sono stati rilasciati libri da autori i cui libri sono stati rilasciati all'utente con ID 31. La richiesta sarà la seguente:

ID utente

120

65

205

Una query interna (dopo IN) seleziona gli autori: Cechov; Ilf e Petrov. La query esterna seleziona tutti gli utenti a cui sono stati pubblicati libri da questi autori. Vediamo che, a differenza del predicato EXISTS, il predicato IN è preceduto dal nome della colonna, in questo caso Autore.

Query con predicato EXISTS e condizioni aggiuntive

Se, oltre al predicato EXISTS nella query, si applica almeno una condizione aggiuntiva, ad esempio specificata utilizzando funzioni aggregate, tali query possono servire per una semplice analisi dei dati. Dimostriamolo con il seguente esempio.

Esempio 5. Determinare gli ID degli utenti a cui è stato pubblicato almeno un libro da Pasternak e a cui sono stati pubblicati più di 2 libri. Scriviamo la seguente query, in cui la prima condizione è specificata dal predicato EXISTS con una query nidificata, e la seconda condizione con l'operatore HAVING deve sempre venire dopo la query nidificata:

Risultato della richiesta:

ID utente

120

Come si può vedere dalla tabella BOOKINUSE, anche il libro di Pasternak è stato rilasciato all'utente con ID 18, ma gli è stato assegnato un solo libro e non è incluso nel campione. Se applichi nuovamente la funzione COUNT a una query simile, ma questa volta per contare le righe selezionate (fai pratica tu stesso), puoi ottenere informazioni su quanti utenti che leggono i libri di Pasternak leggono anche libri di altri autori. Questo proviene già dal campo dell’analisi dei dati.

Query con predicato EXISTS su due tabelle

Le query con il predicato EXISTS possono recuperare dati da più di una tabella. Molti problemi possono essere risolti con lo stesso risultato utilizzando Operatore UNISCITI, ma in alcuni casi l'utilizzo di EXISTS consente di creare una query meno complicata. È preferibile utilizzare EXISTS nei casi in cui la tabella risultante conterrà colonne di una sola tabella.

Nell'esempio seguente, dallo stesso database, oltre alla tabella BOOKINUSE, sarà necessaria anche una tabella USER.

Il risultato della query sarà la seguente tabella:

Autore

Cechov

Majakovskij

Pastinaca

Come nel caso dell'operatore JOIN, nei casi in cui è presente più di una tabella, è necessario utilizzare gli alias di tabella per verificare che i valori delle chiavi che collegano le tabelle corrispondano. Nel nostro esempio, gli alias della tabella sono bk e us e la chiave che collega le tabelle è User_ID.

Predicato EXISTS nei join di più di due tabelle

Ora vedremo più in dettaglio perché è preferibile utilizzare EXISTS nei casi in cui la tabella risultante conterrà colonne di una sola tabella.

Lavoriamo con il database "Immobiliare". La tabella Deal contiene dati sulle offerte. Per i nostri compiti, in questa tabella sarà importante la colonna Tipo con i dati sul tipo di transazione - vendita o locazione. La tabella Object contiene dati sugli oggetti. In questa tabella avremo bisogno dei valori delle colonne Rooms (numero di stanze) e LogBalc, che contiene i dati sulla presenza di una loggia o balcone in formato booleano: 1 (sì) o 0 (no). Le tabelle Cliente, Gestore e Proprietario contengono rispettivamente i dati relativi ai clienti, ai dirigenti dell'azienda e ai proprietari degli immobili. In queste tabelle, FName e LName sono rispettivamente il nome e il cognome.

Esempio 7. Identificare i clienti che hanno acquistato o affittato immobili che non dispongono di loggia o balcone. Scriviamo la seguente query, in cui il predicato EXISTS specifica un accesso al risultato dell'unione di due tabelle:

Poiché le colonne vengono selezionate dalla tabella Client utilizzando l'operatore asterisco, verranno visualizzate tutte le colonne di questa tabella, che avranno tante righe quanti sono i client che soddisfano la condizione specificata dal predicato EXISTS. Non è necessario restituire alcuna colonna dalle tabelle il cui join è accessibile dalla sottoquery. Pertanto, per risparmiare tempo macchina, viene recuperata solo una colonna. Per fare ciò, viene scritta un'unità dopo la parola SELECT. La stessa tecnica viene utilizzata nelle query negli esempi seguenti.

Scrivi tu stesso una query SQL con il predicato EXISTS e poi guarda la soluzione

Continuiamo a scrivere query SQL insieme al predicato EXISTS

Esempio 9. Determinare i proprietari degli oggetti affittati. Scriviamo la seguente query, in cui il predicato EXISTS specifica anche un accesso al risultato dell'unione di due tabelle:

Come nell'esempio precedente, verranno restituiti tutti i campi della tabella a cui ha avuto accesso la query esterna.

Esempio 10. Determinare il numero di proprietari le cui proprietà sono state gestite dal manager Savelyev. Scriviamo una query in cui la query esterna accede a un join di tre tabelle e il predicato EXISTS specifica l'accesso a una sola tabella:

Tutte le query vengono confrontate con un database esistente. Utilizzo riuscito!

Database relazionali e linguaggio SQL

Accademia statale di economia e management di Novosibirsk

PRATICA DI LABORATORIO SULLA DISCIPLINA

"BANCA DATI"

Lavoro di laboratorio n. 7

"Linguaggio delle basi Dati SQL: comandi di manipolazione dei dati»

NOVOSIBIRSK 2000

SQL è l'abbreviazione di Structured Query Language. Dal nome del linguaggio è chiaro che il suo scopo principale è generare query per ottenere informazioni da un database. I comandi per il recupero dei dati costituiscono la base del linguaggio di manipolazione dei dati DML, parte integrante del linguaggio SQL. Tuttavia, DML è costituito da qualcosa di più che semplici comandi per il recupero dei dati da un database. Sono inoltre disponibili comandi per la modifica dei dati, la gestione dei dati e altri.

Il lavoro di laboratorio prende in esame gli strumenti base del linguaggio DML. In corso lavoro di laboratorio ci atterremo allo standard SQL2.

Dato che SQL è un linguaggio di grandi dimensioni, prenderemo in considerazione solo i comandi di base. Vari strumenti SQL specifici verranno trattati nei laboratori successivi.

Per eseguire il lavoro di laboratorio è richiesta la conoscenza dei fondamenti del modello dati relazionale, dei fondamenti dell'algebra relazionale e del calcolo relazionale e dei principi di lavoro con il DBMS MS SQL Server.

Come risultato del completamento del lavoro di laboratorio, padroneggerai i metodi di manipolazione dei dati utilizzando i comandi del linguaggio SQL, considererai il dialetto del linguaggio implementato nel DBMS MS SQL Server.

INTRODUZIONE

SQL contiene un'ampia gamma di funzionalità di manipolazione dei dati, sia per creare query che per aggiornare il database. Queste capacità si basano solo sulla struttura logica del database e non sulla sua struttura fisica, che è coerente con i requisiti del modello relazionale.

La struttura originale della sintassi SQL era (o almeno sembrava essere) basata sul calcolo relazionale di Codd. L'unica operazione supportata nell'algebra relazionale era l'unione.

Oltre alla sintassi simile al calcolo relazionale sviluppata nello standard precedente, SQL2 implementa direttamente le operazioni unione, intersezione, differenza e unione. Le operazioni di selezione, progetto e prodotto erano (e continuano ad essere) supportate quasi direttamente, mentre le operazioni di divisione e assegnazione sono supportate in una forma più complessa.

Descriveremo prima il linguaggio di interrogazione SQL e poi le sue operazioni di immissione e modifica dei dati. Le operazioni di modifica dei dati verranno descritte per ultime, poiché la loro struttura si basa in una certa misura sulla struttura del linguaggio di interrogazione.

Domande semplici

Per noi semplice richiesta ci sarà una query che accede a una sola tabella nel database. Semplici query ci aiuteranno a illustrare la struttura di base di SQL.

Richiesta semplice. Una query che accede solo a una tabella del database.

Richiesta: Chi lavora come stuccatore?

WHERE SKILL_TYPE = "Stonacatore"

Risultato:

G.Rickover

Questa query illustra le tre più comuni frasi SQL: SELEZIONA, DA e DOVE. Anche se nel nostro esempio li abbiamo posizionati su righe diverse, possono apparire tutti sulla stessa riga. Possono anche avere un rientro diverso e le parole all'interno delle frasi possono essere separate da un numero arbitrario di spazi. Vediamo le caratteristiche di ogni frase.

Selezionare. La clausola SELECT elenca le colonne che dovrebbero apparire nella tabella risultante. Queste sono sempre colonne di una tabella relazionale. Nel nostro esempio, la tabella risultante è composta da una colonna (NOME), ma in generale può contenere più colonne; può contenere anche valori o costanti calcolati. Forniremo esempi di ciascuna di queste opzioni. Se la tabella risultante deve contenere più di una colonna, tutte le colonne richieste vengono elencate di seguito Comandi SELEZIONA separati da virgole. Ad esempio, la frase SELECT WORKER_ID, NAME risulterà in una tabella composta dalle colonne WORKER_ID e NAME.

Clausola SELECT. Specifica le colonne della tabella risultante.

Da. La clausola FROM specifica una o più tabelle a cui accede la query. Tutte le colonne elencate nelle clausole SELECT e WHERE devono esistere in una delle tabelle elencate nel comando FROM. In SQL2, queste tabelle possono essere definite direttamente nello schema come tabelle di base o visualizzazioni di dati oppure possono essere esse stesse tabelle senza nome risultanti da query SQL. In quest'ultimo caso la richiesta viene data esplicitamente nel comando FROM.

La frase FROM. Specifica le tabelle esistenti a cui accede la query.

Dove. La clausola WHERE contiene una condizione. in base al quale vengono selezionate le righe della/e tabella/e. Nel nostro esempio la condizione è che la colonna SKILL_TYPE contenga la costante "Plasterer" racchiusa tra apostrofi, come avviene sempre con le costanti di testo in SQL. La clausola WHERE è il comando SQL più volatile; può contenere molte condizioni diverse. Gran parte della nostra discussione sarà dedicata all'illustrazione dei vari costrutti consentiti nel comando WHERE.

Dove la clausola. Specifica la condizione in base alla quale vengono selezionate le righe dalle tabelle specificate.

La query SQL precedente viene elaborata dal sistema nel seguente ordine: FROM, WHERE, SELECT. Cioè, le righe della tabella specificata nel comando FROM vengono inserite nell'area di lavoro per l'elaborazione. La clausola WHERE viene quindi applicata a ciascuna riga in sequenza. Tutte le righe che non soddisfano la condizione WHERE sono escluse dalla considerazione. Quindi le righe che soddisfano la condizione WHERE vengono elaborate dall'istruzione SELECT. Nel nostro esempio, da ciascuna di queste righe viene selezionato NAME e tutti i valori selezionati vengono visualizzati come risultati della query.

Richiesta: Fornire tutte le informazioni sugli edifici adibiti ad uffici.

WHERE TYPE = "Ufficio"

Risultato:

BLDG IDADDRESSTYPEQLTY LEVELSTATUS

312 Elm St., 123 Ufficio 2 2

Via Berezovaja 210. 1011 Ufficio Z1

Via Osinovaja 111. 1213 Ufficio 4 1

Un asterisco (*) in un comando SELECT significa "intera riga". Questa è una comoda abbreviazione che utilizzeremo spesso.

Richiesta: Qual è lo stipendio settimanale di ciascun elettricista?

SELEZIONA NOME, "Stipendio settimanale = ", 40 * TASSO_HRLY

WHERE SKILL_TYPE = "Elettricista"

Risultato:

M. Faraday Stipendio settimanale = 500,00

H.Columbus Stipendio settimanale = 620,00

Questa query illustra l'uso sia delle costanti di carattere (nel nostro esempio "Stipendio settimanale = ") che dei calcoli nel comando SELECT. All'interno dell'istruzione SELECT, è possibile eseguire calcoli che utilizzano colonne numeriche e costanti numeriche, nonché operatori aritmetici standard ( +, -, *, /), raggruppati secondo necessità utilizzando parentesi. Abbiamo incluso anche un nuovo comando ORDINA BY, che ordina il risultato della query in ordine alfanumerico crescente in base alla colonna specificata. Se vuoi ordinare i risultati in ordine decrescente, devi aggiungere DESC al comando. La clausola ORDER BY può ordinare i risultati in base a più colonne, alcune in ordine crescente e altre in ordine decrescente. La colonna della chiave primaria dell'ordinamento è elencata per prima.

Costante del carattere. Una costante composta da lettere, numeri e caratteri “speciali”.

Richiesta: Chi ha una tariffa oraria compresa tra $ 10 e $ 12?

DOVE FREQUENZA_HR > = 10 E FREQUENZA_HR< - 12

Risultato:

ID LAVORATORE NOME HRLY_RATE SKILL_TYPE SUPV_ID

Questa query illustra alcune delle funzionalità aggiuntive dell'istruzione WHERE: operatori di confronto e operatore booleano AND. Sei operatori di confronto (=,<>(non uguale),<, >, <=, >=). Gli operatori booleani AND, OR e NOT possono essere utilizzati per creare condizioni composte o per negare una condizione. Le parentesi possono essere utilizzate per raggruppare le condizioni, come è comune nei linguaggi di programmazione.

Operatori di confronto =,<>, <, >, <=, >=.

Operazioni booleane AND (AND), OR (OR) e NOT (HE) .

Potresti anche utilizzare l'operatore BETWEEN (tra) per formulare questa query:

DOVE TARIFFA_HR TRA 10 E 12

BETWEEN può essere utilizzato per confrontare una quantità con altre due quantità, la prima delle quali è inferiore alla seconda, se la quantità confrontata può essere uguale a ciascuna di queste quantità o a qualsiasi valore intermedio.

Richiesta: elenco stuccatori, conciatetti ed elettricisti.

WHERE SKILL_TYPE IN ("Stonacatore", "Conciatetti", "Elettricista")

Risultato:

LAVORATORE_ID NOME HRLY_RATE SKILL_TYPE SUPV_ID

1412 K.Nemo 13,75 Stuccatore 1520

2920 R. Garrett 10.00 Copritetti 2920

1520 G. Rickover 11.75 Stuccatore 1520

Questa query spiega l'uso dell'operatore di confronto IN (B). La condizione WHERE è considerata vera se il tipo di specialità della riga si trova all'interno dell'insieme specificato tra parentesi, ovvero se il tipo di specialità è stuccatore, conciatetti o elettricista. Vedremo nuovamente l'operatore IN nelle sottoquery.

Supponiamo di non ricordare esattamente l'ortografia della nostra specialità: "elettricista" o "ingegnere elettronico" o qualcos'altro. I caratteri jolly, che sostituiscono le stringhe di caratteri non definite, semplificano la ricerca di ortografie imprecise in una query.

Simboli del modello. Caratteri che sostituiscono stringhe di caratteri non definite.

Richiesta: Elenca i dipendenti la cui specialità inizia con "Elek".

WHERE SKILL_TYPE LIKE ("Eletti%")

Risultato:

NOME ID LAVORATORE HRLY_RATE SKILL_TYPE SUPV_ID

1235 M. Faraday 12.50 Elettricista 1311

1311 H. Columbus 15.50 Elettrico 1311

SQL ha due caratteri jolly: % (percentuale) e _ (trattino basso). Il carattere di sottolineatura sostituisce esattamente un carattere non definito. La percentuale sostituisce un numero arbitrario di caratteri, iniziando da zero. Quando vengono utilizzati caratteri jolly, è necessario un operatore LIKE per confrontare le variabili di carattere con le costanti. Altri esempi:

NOME COME "__Columbus"

NOME COME "__K%"

La condizione nel primo esempio è vera se NAME è composto da due caratteri seguiti da "Columbus". Nella tabella LAVORATORE, tutti i nomi iniziano con la prima iniziale e un punto. Quindi, utilizzando questa condizione noi. Troviamo tutti i dipendenti con il cognome "Columbus". La condizione del secondo esempio ci consente di trovare tutti i dipendenti i cui cognomi iniziano con la lettera "K".

Richiesta: Trova tutti i lavori che iniziano entro le prossime due settimane.

DOVE LA DATA INIZIO TRA LA DATA CORRENTE E

Risultato:(Supponiamo che la data corrente sia CURRENT DATE = 10.10)

WORKER_ID BLDG_ID START_DATE NUM_DAYS

1235 312 10.10 5

1235 515 17.10 22

3231 111 10.10 8

1412 435 15.10 15

3231 312 24.10 20

1311 460 23.10 24

Questa query illustra l'utilizzo dell'operatore BETWEEN con valori di data e intervallo. CURRENT_DATE è una funzione che restituisce sempre la data odierna. Espressione

DATA_CORRENTE + INTERVALLO "14" GIORNO

aggiunge un periodo di due settimane alla data corrente. Pertanto, ASSIGNMENT viene selezionato (presupponendo che oggi sia il 10/10) se il valore della colonna START_DATE è compreso tra 10/10 e 24/10. Da ciò possiamo vedere che possiamo aggiungere valori di intervallo ai campi data. Inoltre, possiamo moltiplicare i valori degli intervalli per valori interi. Ad esempio, supponiamo di voler scoprire quale numero sarà tra un certo numero di settimane (indicato dalla variabile NUM_WEEKS). Possiamo farlo in questo modo:

DATA_CORRENTE + INTERVALLO "7" GIORNI * NUM_SETTIMANE

2. Query su più tabelle

La capacità di mettere in relazione gli elementi di dati oltre i confini di una singola tabella è importante per qualsiasi linguaggio di database. Nell'algebra relazionale, questa funzione viene eseguita dall'operazione di unione. Sebbene gran parte di SQL sia basata direttamente sul calcolo relazionale, SQL collega i dati di tabelle diverse in modo simile all'operazione di unione dell'algebra relazionale. Ora mostreremo come è possibile farlo. Considera la richiesta:

Richiesta:

I dati necessari per la risposta sono presenti in due tabelle: LAVORATORE e INCARICO. La soluzione SQL richiede l'elenco di entrambe le tabelle nel comando FROM e la specifica di un tipo speciale di clausola WHERE:

SELEZIONA TIPO_ABILITÀ

DA LAVORATORE, INCARICO

DOVE LAVORATORE.ID_LAVORATORE = ASSEGNAZIONE.ID_LAVORATORE

E ID_BLDG = 435

Cosa sta succedendo qui? Dobbiamo considerare due fasi nel modo in cui il sistema elabora questa richiesta.

1. Come al solito, la clausola FROM viene elaborata per prima. Tuttavia, in questo caso, poiché il comando specifica due tabelle, il sistema crea un prodotto cartesiano delle righe di queste tabelle. Ciò significa che viene creata (logicamente) una tabella di grandi dimensioni composta da colonne di entrambe le tabelle, con ciascuna riga di una tabella accoppiata con ciascuna riga dell'altra tabella. Nel nostro esempio, poiché la tabella LAVORATORE ha cinque colonne e la tabella ASSEGNAZIONE ha quattro colonne, il prodotto cartesiano prodotto dal comando FROM avrà nove colonne. Il numero totale di righe del prodotto cartesiano è pari a m * n, dove m è il numero di righe della tabella LAVORATORE; e n è il numero di righe nella tabella ASSEGNAZIONE. Poiché la tabella LAVORATORE ha 7 righe e la tabella ASSEGNAZIONE ha 19 righe, il prodotto cartesiano conterrà 7x19 o 133 righe. Se il comando FROM elenca più di due tabelle, viene creato un prodotto cartesiano di tutte le tabelle specificate nel comando.

prodotto cartesiano. Il risultato dell'unione di ogni riga di una tabella con ogni una riga da un'altra tabella.

2. Dopo aver creato la tabella relazionale gigante, il sistema utilizza il comando WHERE come prima. Ogni riga della tabella creata dal comando FROM. viene controllato per vedere se la condizione WHERE è soddisfatta. Le righe che non soddisfano la condizione vengono escluse dalla considerazione. La clausola SELECT viene quindi applicata alle righe rimanenti.

La clausola WHERE nella nostra query contiene due condizioni:

1. LAVORATORE. ID_LAVORATORE = ASSEGNAZIONE.ID_LAVORATORE

2.ID_BLDG = 435

La prima di queste condizioni è la condizione di unione. Tieni presente che poiché entrambe le tabelle LAVORATORE e ASSEGNAZIONE contengono una colonna denominata LAVORATORE_ID, il loro prodotto cartesiano conterrà due colonne con quel nome. Per differenziarli, facciamo precedere il nome della colonna con il nome della tabella di origine, separato da un punto.

La prima condizione significa che in qualsiasi riga selezionata, il valore della colonna WORKER_ID della tabella WORKER deve corrispondere al valore della colonna WORKER_ID della tabella ASSIGNMENT. In realtà, stiamo unendo due tabelle tramite WORKER_ID. Tutte le righe in cui i valori di queste due colonne non sono uguali vengono escluse dalla tabella dei prodotti. Esattamente la stessa cosa accade quando si esegue l'operazione di unione naturale dell'algebra relazionale. (Tuttavia, c'è ancora qualche differenza rispetto a un join naturale: SQL non rimuove automaticamente la colonna WORKER_ID aggiuntiva). L'unione completa di queste due tabelle con la condizione aggiuntiva BLDG_ID = 435 è mostrata in Fig. 1. L'utilizzo del comando SELECT alla fine darà il seguente risultato della query:

TIPO DI ABILITA'

Intonacatore

Conciatetti

Elettricista

Riso. 1. Unione delle tabelle LAVORATORE e ASSEGNAZIONE

Ora mostreremo come unire una tabella a se stessa in SQL.

Richiesta: Elencare i dipendenti, indicando i nomi dei loro dirigenti.

SELEZIONA A.NOME_LAVORATORE, B.NOME_LAVORATORE

DAL LAVORATORE A, LAVORATORE B

DOVE B.ID_LAVORATORE = A.ID_SUPV

La clausola FROM in questo esempio crea due "copie" della tabella WORKER, assegnando loro gli alias A e B. Un alias è un nome alternativo assegnato alla tabella. Quindi le copie A e B della tabella LAVORATORE vengono unite dal comando WHERE in base alla condizione di uguaglianza di LAVORATORE_ID in B e SUPV_ID in A. Pertanto, ogni riga di A viene unita alla riga B, che contiene informazioni sul gestore della riga A (Fig. 2).

Riso. 2. Unione di due copie della tabella LAVORATORE

Selezionando due nomi di dipendenti da ciascuna riga, otteniamo l'elenco richiesto:

A.NOMEB.NOME

M. Faraday H. Colombo

K.Nemo G.Rickover R.Garrett R.Garrett

P. Mason P. Mason G. Rickover G. Rickover H. Columbus H. Columbus J. Barrister P. Mason

Soprannome. Un nome alternativo dato alla tabella.

A.WORKER_NAME rappresenta il lavoratore e B.WORKER_NAME rappresenta il manager. Tieni presente che alcuni lavoratori sono manager di se stessi, il che deriva dall'uguaglianza WORKER_ID - SUPV_ID nelle loro righe.

In SQL, puoi collegare più di due tabelle alla volta:

Richiesta

SELEZIONA LAVORATORE_NOME

DA LAVORATORE, INCARICO, EDILIZIA

DOVE LAVORATORE.ID_LAVORATORE = ASSEGNAZIONE.ID_LAVORATORE E ASSEGNAZIONE.ID_BLDG = EDILIZIA.ID_BLDG E

TIPO = "Ufficio"

Risultato:

M. Faraday

G.Rickover

J.Avvocato

Tieni presente che se il nome di una colonna (come WORKER_ID o BLDG_ID) appare in più di una tabella, per evitare ambiguità dobbiamo anteporre al nome della colonna il nome della tabella originale. Ma se il nome della colonna ricorre solo in una tabella, come TYPE nel nostro esempio, non c'è ambiguità, quindi non è necessario specificare il nome della tabella.

I comandi SQL in questa query creano una tabella da tre tabelle di database relazionali. Le prime due tabelle vengono unite da WORKER_ID, dopodiché la terza tabella viene unita da BLDG_ID alla tabella risultante. Condizione

TIPO = "Ufficio"

La clausola WHERE fa sì che tutte le righe vengano escluse tranne quelle relative agli edifici adibiti ad uffici. Questo soddisfa i requisiti della richiesta.

3. Sottoquery

Sottoquery. Interrogazione all'interno di un'interrogazione

È possibile inserire una sottoquery all'interno della clausola WHERE di una query, espandendo così le capacità della clausola WHERE. Diamo un'occhiata a un esempio.

Richiesta: Quali sono le specialità degli operai addetti all'edificio 435?

SELEZIONA SKTLL_TYPE

DAL LAVORATORE DOVE LAVORATORE_ID IN

(SELEZIONA LAVORATORE_ID

DOVE BLDG_ID = 435)

Sottoquery in questo esempio

(SELEZIONA LAVORATORE_ID

DOVE BLDG_ID = 435)

Viene richiamata una query che contiene una sottoquery richiesta esterna O richiesta principale. La sottoquery comporta la creazione del seguente set di ID dipendente:

ID LAVORATORE

Richiesta esterna. La query principale, che contiene tutte le sottoquery.

Questo insieme di ID prende quindi il posto di una sottoquery nella query esterna. Da questo punto in poi, la query esterna viene eseguita utilizzando il set creato dalla sottoquery. La query esterna elabora ogni riga della tabella WORKER in base alla clausola WHERE. Se WORKER_ID di una riga si trova nel set (IN) creato dalla sottoquery, allora SKILL_TYPE della riga viene selezionato e visualizzato nella tabella risultante:

TIPO DI ABILITA'

Intonacatore

Conciatetti

Elettricista

È molto importante che la clausola SELECT della sottoquery contenga WORKER_ID e solo WORKER_ID. Altrimenti, la clausola WHERE della query esterna, che indica che WORKER_ID è nell'insieme di ID lavoratore, non avrebbe alcun significato.

Si noti che una sottoquery può essere eseguita logicamente prima che almeno una riga venga considerata dalla query principale. In un certo senso, una sottoquery è indipendente dalla query principale. Può essere eseguito come una query completa. Diciamo che tale sottoquery non è correlata alla query principale. Come vedremo tra breve, le sottoquery possono essere correlate.

Sottoquery non correlata. Una sottoquery il cui valore è indipendente da qualsiasi query esterna.

Ecco un esempio di una sottoquery all'interno di una sottoquery.

Richiesta: Elenca i dipendenti assegnati agli edifici adibiti ad uffici.

Ancora una volta guardiamo la query con cui abbiamo esaminato la connessione.

SELEZIONA LAVORATORE_MAME

DOVE LAVORATORE_ID IN

(SELEZIONA LAVORATORE_ID

DOVE BLDG_ID IN

WHERE TYPE = "Ufficio"))

Risultato:

M. Faraday

G.Rickover

J.Avvocato

Tieni presente che non è necessario anteporre ai nomi delle colonne i nomi delle tabelle ovunque, poiché ogni sottoquery elabora una e solo una tabella, quindi non possono sorgere ambiguità.

L'esecuzione della query avviene in un ordine interno-esterno. Cioè, la query più interna (o "più in basso") viene eseguita per prima, poi viene eseguita la sottoquery che la contiene e infine la query esterna.

Sottoquery correlate. Tutte le sottoquery discusse sopra erano indipendenti dalle query principali in cui venivano utilizzate. Per indipendenti intendiamo che le sottoquery possono essere eseguite da sole come query complete. Passiamo ora a considerare una classe di sottoquery i cui risultati di esecuzione possono dipendere dalla riga considerata dalla query principale. Tali sottoquery sono chiamate sottoquery correlate.

Sottoquery correlata. Una sottoquery il cui risultato dipende dalla riga considerata dalla query principale.

Richiesta: Elenca i dipendenti le cui tariffe orarie sono superiori a quelle dei loro dirigenti.

SELEZIONA LAVORATORE_NOME

DOVE A.TARIFFA_HR >

(SELEZIONARE B.TARIFFA_HRLY

DOVE B.ID_LAVORATORE = A.ID_SUPV)

Risultato:

I passaggi logici per eseguire questa richiesta sono:

1. Il sistema crea due copie della tabella LAVORATORE: copia A e copia B. Secondo il modo in cui le abbiamo definite, A si riferisce al dipendente, B si riferisce al manager.

2. Il sistema considera quindi ciascuna riga A. Una determinata riga viene selezionata se soddisfa la condizione WHERE. Questa condizione significa che una riga verrà selezionata se il suo valore HRLY_RATE è maggiore del HRLY_RATE generato dalla sottoquery.

3. La sottoquery seleziona il valore HRLY_RATE dalla riga B, il cui WORKER_ID è uguale a SUPV_ID della riga A, in questo momento considerata dalla richiesta principale. Questo è il HRLY_RATE del manager.

Tieni presente che poiché A.HRLY_RATE può essere confrontato solo con un valore, la sottoquery deve restituire solo un valore. Questo valore cambia a seconda di quale riga A viene considerata. Pertanto, la sottoquery è correlata alla query principale. Vedremo più esempi di sottoquery correlate più avanti quando studieremo le funzioni integrate.

Operatori EXISTS e NOT EXISTS

Supponiamo di voler identificare i lavoratori che non sono assegnati a lavorare in un determinato edificio. A prima vista, sembra che tale richiesta possa essere facilmente soddisfatta semplicemente negando la versione affermativa della richiesta. Supponiamo ad esempio di essere interessati ad un edificio con BLDG_ID 435. Consideriamo la richiesta:

SELEZIONA LAVORATORE_ID

DOVE BLDG_ID NON 435

Sfortunatamente, questa è una formulazione errata della soluzione. La richiesta ci fornirà semplicemente gli ID dei lavoratori che lavorano in altri edifici. Ovviamente alcuni di essi possono essere assegnati anche all'edificio 435.

Una soluzione formulata correttamente utilizza l'operatore NOT EXISTS:

SELEZIONA LAVORATORE_ID

DOVE NON ESISTE

DOVE ASSEGNAZIONE.ID_LAVORATORE = LAVORATORE.ID_LAVORATORE AND

Risultato:

LAVORATORE_ID

Gli operatori EXISTS e NOT EXISTS vengono sempre inseriti prima della sottoquery. EXISTS restituisce true se il set generato dalla sottoquery non è vuoto. Se l'insieme generato dalla sottoquery è vuoto, EXISTS assume il valore “false”. L'operatore NOT EXISTS, ovviamente, funziona esattamente al contrario. È vero se il risultato della sottoquery è vuoto e falso altrimenti.

Operatore ESISTE. Restituisce vero se il set di risultati non è vuoto.

NON ESISTE operatore. Restituisce vero se il set di risultati è vuoto.

In questo esempio abbiamo utilizzato l'operatore NOT EXISTS. La sottoquery seleziona tutte le righe della tabella ASSIGNMENT in cui WORKER_ID ha lo stesso valore della riga considerata dalla query principale e BLDG_ID è uguale a 435. Se questo set è vuoto, allora la riga lavoratore considerata dalla query principale è selezionato, poiché ciò significa che questo dipendente non lavora nell'edificio 435.

Nella soluzione che abbiamo fornito, abbiamo utilizzato una sottoquery correlata. Se usiamo l'operatore IN invece di NOT EXISTS, possiamo cavarcela con una sottoquery non correlata:

SELEZIONA LAVORATORE_ID

DOVE LAVORATORE_ID NON È PRESENTE

(SELEZIONA LAVORATORE_ID

DOVE BLDG_ID = 435)

Questa soluzione è più semplice della soluzione con l'operatore NOT EXISTS. Sorge una domanda naturale: perché abbiamo bisogno di ESISTE e NON ESISTE affatto? La risposta è che NOT EXISTS è l'unico modo per risolvere le query che contengono la parola "every" nella condizione. Tali domande vengono risolte nell'algebra relazionale utilizzando l'operazione di divisione e nel calcolo relazionale utilizzando il quantificatore universale. Ecco un esempio di una query con la parola "ogni" nella sua condizione:

Richiesta: Elencare i dipendenti assegnati a ciascun edificio.

Questa domanda può essere implementata in SQL utilizzando la doppia negazione. Riformuleremo la query per includere una doppia negazione:

Richiesta: Elenca tali dipendenti per chi Non c'è un edificio a cui non sono assegnati.

Abbiamo evidenziato la doppia negazione. È chiaro che questa richiesta è logicamente equivalente alla precedente.

Ora vogliamo formulare la soluzione in SQL. Per rendere la soluzione finale più comprensibile, diamo innanzitutto la soluzione ad un problema preliminare: il problema di individuare tutti gli edifici per i quali un ipotetico lavoratore, "1234" Non nominato.

(I) SELEZIONA BLDG_ID

DOVE NON ESISTE

ASSEGNAZIONE.LAVORATORE_ID = 1234)

Abbiamo contrassegnato questa query (I) perché vi faremo riferimento in seguito. Se non esiste alcun edificio che soddisfi questa richiesta, a ciascun edificio viene assegnato il lavoratore 1234 e quindi soddisfa le condizioni della richiesta originaria. Per ottenere una soluzione alla query originale, dobbiamo generalizzare la query (I) da uno specifico lavoratore 1234 alla variabile WORKER_ID e trasformare questa query modificata in una sottoquery della query più grande. Ecco la soluzione:

(II) SELEZIONA ID_LAVORATORE

DOVE NON ESISTE

DOVE NON ESISTE

DOVE ASSEGNAZIONE.BLDG_ID = BUILDING.BLDG_ID AND

ASSEGNAZIONE.LAVORATORE_ID = LAVORATORE.LAVORATORE_ID)

Risultato:

ID LAVORATORE

Tieni presente che la sottoquery che inizia sulla quarta riga della query (II) è identica alla query (I), con "1234" sostituito da WORKER.WORKER_ID. La domanda (II) può essere letta come segue:

Seleziona WORKER_ID da WORKER se non esiste alcun edificio a cui non è assegnato WORKER_ID.

Ciò corrisponde alle condizioni della richiesta originale.

Vediamo che l'operatore NOT EXISTS può essere utilizzato per formulare quelle query che richiedevano un'operazione di divisione nell'algebra relazionale e un quantificatore universale nel calcolo relazionale. Dal punto di vista della facilità d'uso, l'operatore NOT EXISTS non offre alcun vantaggio particolare, nel senso che le query SQL che utilizzano NOT EXISTS due volte non sono più facili da comprendere rispetto alle soluzioni di algebra relazionale con divisione o alle soluzioni di calcolo relazionale con quantificatori universali. Saranno necessarie ulteriori ricerche per creare costrutti linguistici che consentano di risolvere tali domande in modo più naturale.

Funzioni integrate

Consideriamo domande di questo tipo:

Quali sono le tariffe orarie massime e minime? Qual è il numero medio di giorni di lavoro dei dipendenti nell'edificio 435? Qual è il numero totale di giorni assegnati per i lavori di intonacatura dell'edificio 312? Quante specialità diverse esistono?

Per rispondere a queste domande sono necessarie funzioni statistiche che esaminino molte righe in una tabella e restituiscano un singolo valore. Esistono cinque funzioni di questo tipo in SQL, chiamate funzioni integrate o funzioni impostate. Queste funzioni sono SUM (somma), AVG (media), COUNT (quantità), MAX (massimo) e MIN (minimo).

Funzione integrata (imposta funzione). Una funzione statistica che opera su più righe: SUM (somma), AVG (media), COUNT (quantità), MAX (massimo), MIN (minimo).

Richiesta: Quali sono le tariffe orarie massime e minime?

SELEZIONA MAX(TARIFFA_HR), MIN(TARIFFA_HR)

Risultato: 17.40, 8.20

Funzioni MASSIME e MIN operano su una colonna della tabella. Selezionano rispettivamente il valore massimo o minimo da questa colonna. La nostra formulazione della query non contiene una clausola WHERE. Per la maggior parte delle query questo potrebbe non essere il caso, come mostra il nostro prossimo esempio.

Richiesta: Qual è il numero medio di giorni di lavoro dei dipendenti nell'edificio 435?

SELEZIONA AVG(NUM_GIORNI)

DOVE BLDG_ID =435

Risultato: 12.33

Richiesta: Qual è il numero totale di giorni assegnati per i lavori di intonacatura dell'edificio 312?

SELEZIONA SOMMA(NUM_GIORNI)

DA INCARICO, LAVORATORE

DOVE LAVORATORE.ID_LAVORATORE = ASSEGNAZIONE.ID_LAVORATORE E

SKILL_TYPE = "Stonacatore" AND

Risultato: 27

La soluzione utilizza un join tra le tabelle ASSIGNMENT e WORKER. Ciò è necessario perché SKILL_TYPE si trova nella tabella WORKER e BLDG_ID nella tabella ASSIGNMENT.

Richiesta: Quante specialità diverse esistono?

SELEZIONA CONTEGGIO (TIPO_ABILITA' DISTINTA)

Risultato: 4

Poiché la stessa specialità può essere visualizzata in più righe diverse, è necessario utilizzare la parola chiave DISTINCT in questa query per evitare che il sistema conteggi lo stesso tipo di specialità più di una volta. L'operatore DISTINCT può essere utilizzato con qualsiasi funzione incorporata, sebbene ovviamente sia ridondante con le funzioni MAX e MIN.

DISTINTO. Un operatore che elimina le righe duplicate.

Le funzioni SUM e AVG devono essere utilizzate solo con colonne numeriche. Altre funzioni possono essere utilizzate sia con dati numerici che con caratteri. Tutte le funzioni tranne COUNT possono essere utilizzate con le espressioni calcolate. Per esempio:

Richiesta: Qual è lo stipendio medio settimanale?

SELEZIONA MEDIA (40 * TARIFFA_HR)

Risultato: 509.14

COUNT può fare riferimento a un'intera riga anziché a una singola colonna :

Richiesta: Quanti edifici hanno il livello di qualità 3?

SELEZIONA CONTEGGIO (*)

DALL'EDIFICIO DOVE

Risultato: 3

Come mostrano tutti questi esempi, se un comando SELECT contiene una funzione incorporata, non può apparire nient'altro in quel comando SELECT. L'unica eccezione a questa regola è la clausola GROUP BY, che esamineremo ora.

Clausole GROUP BY e HAVING

Nella gestione, sono spesso richieste informazioni statistiche su ciascun gruppo in molti gruppi. Ad esempio, considera la seguente query:

Richiesta: Per ciascun manager, scopri la tariffa oraria massima tra i suoi subordinati.

Per risolvere questo problema dobbiamo dividere i lavoratori in gruppi a seconda dei loro dirigenti. Successivamente determineremo l'offerta massima all'interno di ciascun gruppo. In SQL questo viene fatto in questo modo:

GRUPPO PER SUPV_ID

Risultato:

SUPV_IDMAX(TARIFFA ORARIA)

Durante l'elaborazione di questa query, il sistema suddivide innanzitutto le righe della tabella WORKER in gruppi utilizzando la seguente regola. Le righe vengono inserite nello stesso gruppo se e solo se hanno lo stesso SUPV_ID. La clausola SELECT viene quindi applicata a ciascun gruppo. Poiché in questo gruppo è presente un solo valore SUPV_ID, non vi è alcuna incertezza SUPV_ID nel gruppo. Per ciascun gruppo, la clausola SELECT restituisce SUPV_ID e calcola e restituisce anche il valore MAX(HRLY_RATE). Il risultato è presentato sopra.

In un comando SELECT con funzioni integrate, possono essere visualizzate solo le colonne incluse nella clausola GROUP BY. Tieni presente che SUPV_ID può essere utilizzato in un comando SELECT perché è incluso nella clausola GROUP BY.

Clausola GRUPPO BY. Indica che le righe devono essere divise in gruppi con valori comuni delle colonne specificate.

La clausola GROUP BY consente di eseguire determinati calcoli complessi. Ad esempio, potremmo voler scoprire la media di queste offerte massime. Tuttavia, il calcolo con funzioni integrate è limitato nel senso che non consente l'utilizzo di funzioni integrate all'interno di altre funzioni integrate. Quindi un'espressione come

AVG(MAX(TARIFFA_HR))

vietato. L'attuazione di tale richiesta consisterà in due fasi. Dobbiamo prima inserire le offerte massime in una nuova tabella e, in un secondo passaggio, calcolare la loro media.

Puoi utilizzare la clausola WHERE con il comando GROUP BY:

Richiesta: Per ogni tipologia di edificio scoprilo livello medio qualità tra gli edifici di stato 1.

SELEZIONA TIPO, AVG(QLTY_LEVEL)

DOVE STATO = 1

Risultato:

TIPOAVG(QLTY_LEVEL)

Negozio 1

Edificio residenziale 3

La clausola WHERE viene eseguita prima dell'istruzione GROUP BY. Pertanto, nessun gruppo può contenere una riga con uno stato diverso da 1. Le righe con stato 1 vengono raggruppate in base al valore TYPE, quindi a ciascun gruppo viene applicata una clausola SELECT.

frase AVERE. Pone condizioni sui gruppi.

Possiamo anche applicare condizioni ai gruppi creati dalla clausola GROUP BY. Questo viene fatto usando la frase HAVING. Supponiamo, ad esempio, di decidere di rendere più specifica una delle query precedenti:

Richiesta: Per ogni manager che ha più di un subordinato, scoprire la tariffa oraria massima tra i suoi subordinati.

Possiamo riflettere questa condizione con l'apposito comando HAVING:

SELEZIONA SUPV_ID, MAX(HRLY_RATE)

DAL GRUPPO DI LAVORATORI PER SUPV_ID

AVERE CONTEGGIO(*) > 1

Risultato:

SUPV_ID MAX(TARIFFA_HRLY)

La differenza tra le clausole WHERE e HAVING è che WHERE si applica alle righe, mentre HAVING si applica ai gruppi.

Una query può contenere sia una clausola WHERE che una clausola HAVING. In questo caso, la clausola WHERE viene eseguita per prima perché viene eseguita prima del raggruppamento. Ad esempio, considera la seguente modifica della query precedente:

Richiesta: Per ciascuna tipologia di edificio, individuare il livello medio di qualità tra gli edifici di stato 1. Considerare solo quelle tipologie di edifici il cui livello massimo di qualità non supera 3.

SELEZIONA TIPO, AVG (QLTY_JLEVEL)

DOVE STATO = 1

AVERE MAX(QLTY_LEVEL)<= 3

Risultato:

TIPO AVG(QLTY_LEVEL)

Negozio 1

Edificio residenziale 3

Si noti che a partire dalla clausola FROM, le clausole vengono eseguite in ordine, quindi viene applicata la clausola SELECT. Pertanto, la clausola WHERE viene applicata alla tabella BUILDING e tutte le righe in cui STATUS è diverso da 1 vengono eliminate. Le restanti righe sono raggruppate per TIPO; tutte le righe con lo stesso valore TYPE finiscono nello stesso gruppo. Pertanto vengono creati diversi gruppi, uno per ciascun valore TYPE. La clausola HAVING viene quindi applicata a ciascun gruppo e i gruppi il cui valore del livello di qualità massimo supera 3 vengono rimossi. Infine, la clausola SELECT viene applicata ai gruppi rimanenti.

7. Funzioni integrate e sottoquery

Le funzioni integrate possono essere utilizzate solo in una clausola SELECT o in un comando HAVING. Tuttavia, una clausola SELECT contenente una funzione inline può far parte di una sottoquery. Diamo un'occhiata ad un esempio di tale sottoquery:

Richiesta: Quali lavoratori hanno una tariffa oraria superiore alla media?

SELEZIONA LAVORATORE_NOME

DOVE TARIFFA_HR >

(SELEZIONA AVG(TARIFFA_HRLY)

Risultato:

H. Colombo

Tieni presente che la sottoquery non è correlata alla query principale. La sottoquery restituisce esattamente un valore: la tariffa oraria media. La query principale seleziona un lavoratore solo se la sua tariffa è maggiore della media calcolata.

Le query correlate possono anche utilizzare funzioni integrate:

Domanda: Quale dipendente ha una tariffa oraria superiore alla tariffa oraria media tra i subordinati dello stesso manager?

In questo caso, invece di calcolare una tariffa oraria media per tutti i lavoratori, dobbiamo calcolare la tariffa media per ciascun gruppo di lavoratori che fa capo allo stesso dirigente. Inoltre, il nostro calcolo deve essere rifatto per ogni lavoratore considerato dalla query principale:

SELEZIONA A. LAVORATORE_NOME

SQL consente di annidare le query l'una nell'altra. In genere una sottoquery restituisce un singolo valore, che viene controllato per verificare se il predicato è vero.

Tipi di termini di ricerca:
. Confronto con il risultato di una sottoquery (=, >=)
. Verifica di appartenenza ai risultati di una sottoquery (IN)
. Verifica dell'esistenza (EXISTS)
. Confronto multiplo (quantitativo) (QUALSIASI, TUTTI)

Note sulle query nidificate:
. Una sottoquery deve selezionare solo una colonna (ad eccezione di una sottoquery con un predicato EXISTS) e il tipo di dati del risultato deve corrispondere al tipo di dati del valore specificato nel predicato.
. In alcuni casi, è possibile utilizzare la parola chiave DISTINCT per garantire che venga restituito un singolo valore.
. Non è possibile includere una clausola ORDER BY o UNION in una sottoquery.
. La sottoquery può essere posizionata a sinistra o a destra della condizione di ricerca.
. Le sottoquery possono utilizzare funzioni di aggregazione senza una clausola GROUP BY, che restituiscono automaticamente un valore speciale per qualsiasi numero di righe, uno speciale predicato IN ed espressioni basate su colonne.
. Quando possibile, dovresti utilizzare i join di tabella JOIN invece delle sottoquery.

Esempi di query nidificate:

SELECT * FROM Ordini WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName='Motika')
SELECT * FROM Ordini WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City='Londra')
SELECT * FROM Ordini WHERE SNum=(SELECT DISTINCT SNum FROM Ordini WHERE CNum=2001)
SELECT * FROM Ordini WHERE Amt>(SELECT AVG(Amt) FROM Ordini WHERE Odata=10/04/1990)
SELECT * FROM Cliente WHERE CNum=(SELECT SNum+1000 FROM Venditori WHERE SNome=’Serres’)

2) Sottoquery correlate

In SQL è possibile creare sottoquery che fanno riferimento a una tabella da una query esterna. In questo caso, la sottoquery viene eseguita più volte, una per ogni riga della tabella della query esterna. Pertanto, è importante che la sottoquery utilizzi l'indice. Una sottoquery può accedere alla stessa tabella di una esterna. Se la query esterna restituisce un numero relativamente piccolo di righe, la sottoquery collegata sarà più veloce di quella non collegata. Se una sottoquery restituisce un numero limitato di righe, la query correlata sarà più lenta di quella non correlata.

Esempi di sottoquery correlate:

SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //restituisce tutti gli ordini il cui valore supera il valore medio dell'ordine per un determinato cliente

3) Il predicato ESISTE

Forma sintattica: ESISTE ()

Il predicato accetta una sottoquery come argomento e restituisce true se la sottoquery ha un output e false altrimenti. La sottoquery viene eseguita una volta e può contenere più colonne, poiché i loro valori non vengono controllati, ma viene semplicemente registrato il risultato della presenza di righe.

Note sul predicato EXISTS:
. EXISTS è un predicato che restituisce TRUE o FALSE e può essere utilizzato da solo o con altre espressioni booleane.
. EXISTS non può utilizzare funzioni di aggregazione nella sua sottoquery.
. Nelle sottoquery correlate, il predicato EXISTS viene eseguito per ogni riga della tabella esterna.
. Puoi combinare il predicato EXISTS con i join di tabella.

Esempi per il predicato EXISTS:

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City='San Jose') – restituisce tutti i clienti se qualcuno di loro vive a San Jose.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNumFirst.CNum) – restituisce il numero di venditori che hanno servito un solo cliente.
SELECT DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – restituisce numeri, nomi e città di residenza di tutti i venditori che hanno servito più clienti.
SELECT * FROM SalesPeople First WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) Predicati di comparazione quantitativa

Forma sintattica: (=|>|=|) QUALSIASI|TUTTI ()

Questi predicati utilizzano una sottoquery come argomento, tuttavia, rispetto al predicato EXISTS, vengono utilizzati insieme ai predicati relazionali (=,>=). In questo senso sono simili al predicato IN, ma vengono utilizzati solo con le sottoquery. Lo standard consente di utilizzare la parola chiave SOME anziché ANY, ma non tutti i DBMS la supportano.

Note sui predicati di confronto:
. Il predicato ALL restituisce TRUE se ciascun valore selezionato durante l'esecuzione della sottoquery soddisfa la condizione specificata nel predicato della query esterna. Viene spesso utilizzato con le disuguaglianze.
. Il predicato ANY restituisce TRUE se almeno un valore selezionato durante l'esecuzione della sottoquery soddisfa la condizione specificata nel predicato della query esterna. Viene spesso utilizzato con le disuguaglianze.
. Se la sottoquery non restituisce alcuna riga, ALL assume automaticamente il valore TRUE (si considera soddisfatta la condizione di confronto) e per ANY assume il valore FALSE.
. Se il confronto è VERO per nessuna riga e sono presenti una o più righe con un valore NULL, ANY restituisce UNKNOWN.
. Se il confronto è FALSE per nessuna riga e sono presenti una o più righe con un valore NULL, ALL restituisce UNKNOWN.

Esempi per il predicato del confronto quantitativo:

SELECT * FROM Venditori WHERE Città=ANY(SELECT Città FROM Cliente)
SELECT * FROM Ordini WHERE Amt ALL(SELECT Valutazione FROM Cliente WHERE Città=’Roma’)

5) Predicato di unicità

UNICO|DISTINTO ()

Il predicato viene utilizzato per verificare l'unicità (assenza di duplicati) nei dati di output della sottoquery. Inoltre, nel predicato UNIQUT, le stringhe con valori NULL sono considerate uniche e nel predicato DISTINCT due valori non definiti sono considerati uguali tra loro.

6) Corrispondenze del predicato

INCONTRO ()

Il predicato MATCH verifica se il valore di una stringa di query corrisponderà al valore di qualsiasi stringa risultante dalla sottoquery. Questa sottoquery differisce dai predicati IN AND ANY in quanto consente l'elaborazione di corrispondenze “parziali” (PARTIAL) che possono verificarsi tra righe che hanno alcuni valori NULL.

7) Query nella sezione DA

In effetti, è legale utilizzare una sottoquery ovunque sia consentito un riferimento a una tabella.

SELECT CName, Totale_Amt FROM Cliente, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE Città='Londra' AND Cliente.CNum=Ordini.CNum
//subquery restituisce l'importo totale degli ordini effettuati da ciascun cliente da Londra.

8) Query ricorsive

CON RICORSIVA
Q1 COME SELEZIONA... DA... DOVE...
Q2 COME SELEZIONA... DA... DOVE...