Sottoquery nidificate e collegate in SQL, predicato EXISTS. Utilizzo dell'operatore EXISTS Interrogazioni tramite la funzione exists

DOVE ESISTE

La sottoquery viene verificata per una o più righe. Se almeno una riga soddisfa la query, viene restituito il valore booleano TRUE. Quando viene specificata la parola chiave NOT facoltativa, viene restituito il valore booleano TRUE se la sottoquery non restituisce righe corrispondenti.

sottointerrogazione

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 della query padre.

SELECT * FROM job WHERE NOT EXISTS (SELECT * FROM dipendente WHERE jobs.job_id=employye. job_id);

Questo esempio viene convalidato in una sottoquery di record utilizzando la parola chiave NOT facoltativa. L'esempio seguente cerca 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. Si noti che è possibile utilizzare un asterisco nella sottoquery perché la sottoquery deve restituire solo un record con un valore booleano TRUE. In tali casi, le colonne non svolgono un ruolo. Il punto chiave è l'esistenza della stringa.

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

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

Una sottoquery in una clausola EXISTS in genere esegue uno dei due tipi di ricerche. La prima opzione consiste nell'utilizzare un carattere jolly asterisco (ad es. SELECT * FROM...), nel qual caso non si recupera alcuna colonna o valore particolare. L'asterisco qui significa "qualsiasi colonna". La seconda opzione consiste nel selezionare solo una colonna specifica nella sottoquery (ad esempio, SELECT aujd FROM). Alcune singole piattaforme consentono sottoquery su più colonne (ad es. 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.

"Era più facile" - ho pensato, sedendomi per ottimizzare la query successiva in SQL atelier di gestione. Quando ho scritto sotto MySQL, tutto era davvero più semplice: o funziona o no. O rallenta o no. Spiega risolto tutti i miei problemi, non era necessario nient'altro. Ora ho un potente ambiente di sviluppo, debug e ottimizzazione di richieste e procedure/funzioni, e tutto questo mucchio crea, secondo me, solo più problemi. E tutto perché? Perché l'ottimizzatore di query integrato è malvagio. Se in MySQL e PostgreSQL scrivo

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

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

Selezionare * da a join b su a.id = b.id join c su b.id = c.id

L'ottimizzatore integrato pettinerà i bydlozapros e tutto andrà bene.

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

C'è solo un MA. Ad un certo punto, l'ottimizzatore inciamperà domanda complessa e piega, e poi hai un grosso problema. E lo riceverai, forse non subito, ma quando il peso dei tavoli raggiungerà una massa critica.

Quindi questo è il punto dell'articolo. esiste e sono operazioni molto pesanti. In effetti, questa è una sottoquery separata per ciascuno linee di risultato. E se è presente anche la nidificazione, questa è generalmente una carcassa leggera. Andrà tutto bene quando verranno restituite 1, 10, 50 righe. Non sentirai la differenza e forse l'unione sarà ancora più lenta. Ma quando 500 vengono estratti, iniziano i problemi. 500 sottorichieste all'interno di una singola richiesta sono gravi.

Lascia entrare ed esiste essere migliore dal punto di vista della comprensione umana, ma dal punto di vista dei costi 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ù risorse in termini di memoria, perché tenere l'intera tabella di valori contemporaneamente e operare su di essa è più costoso che estrarre sottoquery per ogni riga, liberando rapidamente memoria. È necessario esaminare in modo specifico la richiesta e misurare se sarà fondamentale utilizzare memoria aggiuntiva per motivi di tempo o meno.

Darò esempi di analogie complete. In generale, non ho ancora visto richieste di un tale grado di complessità che non potessero essere districate in una cascata di join. Lascia che ci voglia 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 è presente (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 su richieste così semplici non ci sarà mai stupidità.

Consideriamo ora un esempio di query reale che doveva essere riscritta a causa del fatto che su alcune selezioni era semplicemente bloccata (la struttura è molto semplificata e i concetti sono stati sostituiti, non c'è bisogno di aver paura di alcuni non- struttura ottimale del database).

Devi estrarre tutti i "prodotti" duplicati in account diversi, concentrandoti sui parametri del prodotto, del suo gruppo e dell'eventuale gruppo principale.

Seleziona d.PRODUCT_ID da PRODUCT s, PRODUCT_GROUP sg a sinistra unisciti a M_PG_DEPENDENCY sd su (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg a sinistra unisciti a M_PG_DEPENDENCY gg su (dg.PRODUCT_GROUP_ID = gg.M_PG_DEPENDENCY_CHILD_ID) dove s.ID=sGPRODUCT_GRO .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_GROUP_NAME e s.PRODUCT_NAME=d.PRODUCT_NAME e s.PRODUCT_TYPE=d.PRODUC_TYPE e s.PRODUCT_S. e s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT e dg.PRODUCT_GROUP_IS_TMPL=0 e ((sd.M_PG_DEPENDENCY_CHILD_ID è nullo e dd.M_PG_DEPENDENCY_CHILD_ID è nullo) o esiste (seleziona 1 da PRODUCT_GROUP sg1, PRODUCT_GROUP dg1 dove sd.M_PG_DEPENDENCY_PARENT_IDCT e dg1 .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_N AM e))

E così è quel caso in cui l'ottimizzatore ha chiuso. E per ogni riga è stato eseguito il pesante esistente, che ha ucciso la base.

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 sg .PRODUCT_GROUP_ID join PRODUCT_GROUP dg on d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC left join M_PG_DEPENDENCY sd on sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID left join M_PG_DEPENDENCY dd on dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID left join PRODUCT_GROUP sgp on sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID left join PRODUCT_GROUP dgp on dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID and sgp.PRODUCT_GROUP_NAME = dgp.PRODUCT_GROUP_NAME and isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull(dgp. PRODUCT_GROUP_IS_TMPL , 0) dove (sd.M_PG_DEPENDENCY_CHILD_ID è nullo e dd.M_PG_DEPENDENCY_CHILD_ID è nullo) o (sgp.PRODUCT_GROUP_NAME non è nullo e dgp.PRODUCT_GROUP_NAME non è nullo) vai

Dopo queste trasformazioni, le prestazioni della vista sono aumentate in modo esponenziale nel numero di prodotti trovati. Più precisamente, 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 fare uno scherzo crudele. Non fidarti di lui, non essere pigro, unisciti alle maniglie, ogni volta pensa a cosa è meglio in questa situazione: esiste, dentro o unisciti.

Il predicato SQL EXISTS esegue un'attività logica. IN query SQL questo predicato è usato in espressioni come

ESISTE (SELEZIONA * DA NOME_TABELLA ...).

Questa espressione restituisce true quando una o più righe corrispondenti alla condizione vengono trovate dalla query e false quando non viene trovata alcuna riga.

Per NON ESISTE, è vero il contrario. Espressione

NON ESISTE (SELEZIONA * DA NOME_TABELLA ...)

restituisce true quando non vengono trovate righe per la query e false 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 "Book in use" (BOOKINUSE) e "User" (USER). Per ora ci serve solo la tabella "Libri in uso" (BOOKINUSE).

Autore	Titolo	Anno della birreria	N. inv	ID utente
Tolstoj	Guerra e Pace	2005	28	65
Cechov	Il Giardino dei Ciliegi	2000	17	31
Cechov	Storie selezionate	2011	19	120
Cechov	Il Giardino 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
Pushkin	La figlia del capitano	2004	25	47
Gogol	Riproduce	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 emessi libri da Tolstoj, a cui sono stati anche emessi libri da Cechov. La query esterna seleziona i dati sugli utenti a cui sono stati rilasciati i libri di Tolstoj e il predicato EXISTS specifica una condizione aggiuntiva che viene verificata nella query interna: gli utenti a cui sono stati rilasciati i libri di Cechov. Una condizione aggiuntiva nella query interna è la corrispondenza degli ID utente dalle query esterne e interne: 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 le query con il predicato EXISTS potrebbero avere l'impressione che sia identico predicato IN. Questo è sbagliato. Anche se sono molto simili. Il predicato IN cerca i valori dall'intervallo specificato nel suo argomento e, se tali valori sono presenti, vengono selezionate tutte le righe corrispondenti a questo intervallo. Il risultato dell'azione del predicato EXISTS è una risposta sì o no alla domanda se esistano valori che corrispondono a quelli specificati nell'argomento. Inoltre, il predicato IN è preceduto dal nome della colonna con cui cercare le righe che corrispondono ai valori nell'intervallo. Diamo un'occhiata a un esempio che mostra la differenza tra il predicato EXISTS e il predicato IN e il problema risolto utilizzando il predicato IN.

Esempio 4 Ottieni gli ID degli utenti che hanno i libri rilasciati agli autori i cui libri sono stati ritirati per l'utente con ID 31. La query sarà la seguente:

ID utente

120

65

205

La 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 il predicato EXISTS e condizioni aggiuntive

Se, oltre al predicato EXISTS nella query, viene applicata 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 Determina gli ID degli utenti a cui è stato rilasciato almeno un libro di Pasternak e a cui sono stati rilasciati più di 2 libri. Scriviamo la seguente query, in cui la prima condizione è specificata dal predicato EXISTS con una sottoquery, e la seconda condizione con l'operatore HAVING deve sempre seguire la sottoquery:

Risultato dell'esecuzione della query:

ID utente

120

Come si può vedere dalla tabella BOOKINUSE, il libro di Pasternak è stato rilasciato anche all'utente con ID 18, ma gli è stato rilasciato un solo libro e non è incluso nella selezione. Se applichi di nuovo la funzione COUNT a tale query, ma 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 è già nel campo dell'analisi dei dati.

Query con il 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 Istruzione UNISCITI, ma in alcuni casi l'utilizzo di EXISTS consente di effettuare una query meno macchinosa. È 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, è necessaria anche una tabella USER.

Il risultato della query sarà la seguente tabella:

Autore

Cechov

Majakovskij

Pastinaca

Come per l'istruzione 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 le colonne di una sola tabella entreranno nella tabella risultante.

Lavoriamo con il database "Real Estate". La tabella Deal contiene dati sulle offerte. Per le nostre attività in questa tabella, la colonna Tipo sarà importante con i dati sul tipo di transazione: vendita o locazione. La tabella Object contiene dati sugli oggetti. In questa tabella ci servono i valori delle colonne Rooms (numero di stanze) e LogBalc, contenenti i dati sulla presenza di una loggia o di un balcone in formato booleano: 1 (sì) o 0 (no). Le tabelle Cliente, Gestore e Proprietario contengono dati rispettivamente su clienti, gestori di società e proprietari di immobili. In queste tabelle, FName e LName sono rispettivamente il nome e il cognome.

Esempio 7 Identifica i clienti che hanno acquistato o affittato immobili privi 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, in cui ci saranno tante righe quanti sono i client che soddisfano la condizione specificata dal predicato EXISTS. Non è necessario visualizzare alcuna colonna dalle tabelle unite 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 la query SQL con il predicato EXISTS e poi vedi la soluzione

Continuiamo a scrivere query SQL insieme al predicato EXISTS

Esempio 9 Determina i proprietari degli oggetti che sono stati 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 visualizzati tutti i campi della tabella a cui è indirizzata la query esterna.

Esempio 10 Determina il numero di proprietari, con gli oggetti di cui ha speso il manager Savelyev. Scriviamo una query in cui la query esterna accede al join di tre tabelle e il predicato EXISTS specifica l'accesso a una sola tabella:

Tutte le query vengono convalidate rispetto a un database esistente. Uso riuscito!

Database relazionali e linguaggio SQL

Accademia statale di economia e management di Novosibirsk

LABORATORIO SULLA DISCIPLINA

"BANCA DATI"

Lavoro di laboratorio N 7

"Lingua del database SQL: comandi di manipolazione dei dati»

NOVOSIBIRSK 2000

SQL è l'abbreviazione di Structured Query Language. Dal nome della lingua è chiaro che il suo scopo principale è quello di generare richieste di informazioni dal database. I comandi per la selezione dei dati costituiscono la base del linguaggio di manipolazione dei dati DML, parte integrante del linguaggio SQL. Tuttavia, DML consiste in più di semplici comandi per recuperare dati da un database. Ci sono anche comandi per la modifica dei dati, la gestione dei dati e altri.

Questo laboratorio copre le basi del linguaggio DML. In corso lavoro di laboratorio aderiremo allo standard SQL2.

A causa del fatto che SQL è un linguaggio voluminoso, considereremo solo i comandi principali. Varie caratteristiche specifiche di SQL sono trattate nei laboratori successivi.

Per eseguire il lavoro di laboratorio, è necessario conoscere le basi del modello di dati relazionale, le basi dell'algebra relazionale e del calcolo relazionale e i principi per lavorare con il DBMS MS SQL Server.

Come risultato del lavoro di laboratorio, imparerai come manipolare i dati usando i comandi SQL, considera il dialetto del linguaggio implementato nel DBMS MS SQL Server.

INTRODUZIONE

SQL contiene un'ampia gamma di funzionalità di manipolazione dei dati, sia per la creazione di query che per l'aggiornamento del 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.

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

In SQL2, oltre alla sintassi del calcolo relazionale simile sviluppata nello standard precedente, vengono implementate direttamente le operazioni unione, intersezione, differenza e join. 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ù ingombrante.

Descriveremo prima il linguaggio di interrogazione SQL e poi le sue operazioni di inserimento 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 query.

Domande semplici

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

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

Richiesta: Chi lavora come stuccatore?

WHERE SKILL_TYPE = "Intonacatore"

Risultato:

G. Rickover

Questa query illustra i tre più comuni frasi SQL: SELEZIONA, DA e DOVE. Sebbene nel nostro esempio li inseriamo su righe diverse, possono trovarsi tutti sulla stessa riga. Possono anche essere rientrati in modo diverso e le parole all'interno delle frasi possono essere separate da un numero arbitrario di spazi. Considera le caratteristiche di ogni frase.

Selezionare. La clausola SELECT elenca le colonne che devono essere incluse nella tabella risultante. Queste sono sempre colonne di una tabella relazionale. Nel nostro esempio, la tabella risultante è composta da una colonna (NAME), ma in generale può contenere più colonne; può anche contenere valori calcolati o costanti. Daremo esempi di ciascuna di queste opzioni. Se la tabella risultante deve contenere più di una colonna, tutte le colonne richieste vengono elencate dopo SELEZIONA i comandi attraverso una virgola. Ad esempio, la frase SELECT WORKER_ID, NAME risulterà in una tabella costituita dalle colonne WORKER_ID e NAME.

SELEZIONA frase. Specifica le colonne della tabella risultante.

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

Frase DA. Specifica le tabelle esistenti a cui fa riferimento la query.

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

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

La suddetta query SQL viene elaborata dal sistema nel seguente ordine: FROM, WHERE, SELECT. Ovvero, le righe della tabella specificata nel comando FROM vengono posizionate 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 clausola WHERE sono escluse dalla considerazione. Quindi quelle righe che soddisfano la clausola WHERE vengono elaborate dal comando SELECT. Nel nostro esempio, NAME viene selezionato da ciascuna di queste righe e tutti i valori selezionati vengono visualizzati come risultati della query.

Richiesta: Fornire tutti i dati sugli edifici per uffici.

WHERE TIPO = "Ufficio"

Risultato:

BLDG IDADDRESSTYPEQLTY LEVELSTATUS

312 Via Vyazov, 123 Ufficio 2 2

Via Berezovaja 210 1011 Ufficio Z 1

111 Osinovaja st. 1213 Ufficio 4 1

Un asterisco (*) in un comando SELECT significa "l'intera riga". Questa è una comoda scorciatoia che useremo spesso.

Richiesta: Qual è lo stipendio settimanale di ciascun elettricista?

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

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 carattere (nel nostro esempio "Weekly salary = ") sia dei calcoli nel comando SELECT. All'interno del comando SELECT, è possibile eseguire calcoli che utilizzano colonne numeriche e costanti numeriche, nonché operatori aritmetici standard ( +, -, *, /), raggruppati secondo necessità con parentesi. Abbiamo anche incluso un nuovo comando ORDINA BY, che ordina il risultato della query in ordine alfanumerico crescente in base alla colonna specificata. Se desideri 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, altre in ordine decrescente. La colonna della chiave primaria di ordinamento viene specificata per prima.

costante di carattere. Una costante composta da lettere, numeri e caratteri "speciali".

Richiesta: Chi ha una tariffa oraria da $ 10 a $ 12?

DOVE HRLY_RATE >= 10 AND HRLY_RATE< - 12

Risultato:

ID LAVORATORE NOME HRLY_RATE SKILL_TYPE SUPV_ID

Questa query illustra alcune delle funzionalità avanzate del comando WHERE: operatori di confronto e l'operazione booleana AND. I sei operatori di confronto (=,<>(non uguale),<, >, <=, >=). Gli operatori booleani AND (AND), OR (OR) e NOT (HE) 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) .

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

DOVE HRLY_RATE TRA 10 E 12

BETWEEN può essere utilizzato per confrontare un valore con altri due valori, il primo dei quali è minore del secondo, se il valore confrontato può essere uguale a ciascuno dei valori dati o qualsiasi valore intermedio.

Domanda: Elenca stuccatori, conciatetti ed elettricisti.

WHERE SKILL_TYPE IN ("Intonacatore", "Cottaio", "Elettricista")

Risultato:

WORKER_ID NOME HRLY_RATE SKILL_TYPE SUPV_ID

1412 C. 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 clausola WHERE è vera se il tipo di professione della riga è all'interno dell'insieme tra parentesi, ovvero se il tipo di professione è stuccatore, conciatetti o elettricista. Vedremo di nuovo l'operatore IN nelle sottoquery.

Supponiamo di non ricordare esattamente l'ortografia della specialità: "elettricista" o "ingegnere elettronico" o qualcos'altro. I caratteri jolly che sostituiscono le stringhe di caratteri non definite rendono più facile trovare ortografie imprecise in una query.

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

Richiesta: Elenca i lavoratori il cui tipo di lavoro inizia con Elek.

WHERE SKILL_TYPE LIKE ("Elek%")

Risultato:

ID LAVORATORE NOME HRLY_RATE SKILL_TYPE SUPV_ID

1235 M.Faraday 12.50 Elettricista 1311

1311 H. Columbus 15.50 Elettricista 1311

Ci sono due caratteri jolly in SQL: % (percentuale) e _ (trattino basso). Il carattere di sottolineatura sostituisce esattamente un carattere non definito. La percentuale sostituisce un numero arbitrario di caratteri, partendo da zero. Quando vengono utilizzati i caratteri jolly, l'operatore LIKE (like) è necessario per confrontare le variabili 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 un'iniziale e un punto. Quindi, con questa condizione, noi trova tutti i lavoratori con il cognome "Columbus". La condizione del secondo esempio 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 INIZIA _DATA TRA LA DATA_CORRENTE E

Risultato:(Supponiamo che la data corrente sia DATA CORRENTE = 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'uso dell'operatore BETWEEN (tra) con valori di tipo data (data) e intervallo (intervallo). CURRENT_DATE è una funzione che restituisce sempre la data odierna. Espressione

CORRENTE_DATA + INTERVALLO "14" GIORNO

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

CURRENT_DATE + INTERVAL "7" GIORNO * NUM_SETTIMANE

2. Query multi-tabella

La capacità di collegare elementi di dati oltre i confini della stessa tabella è importante in qualsiasi linguaggio di database. In algebra relazionale, questa funzione viene eseguita dall'operazione di join. Sebbene gran parte di SQL si basi direttamente sul calcolo relazionale, SQL collega i dati di diverse tabelle in modo simile a come fa un'operazione di join di algebra relazionale. Mostreremo ora come questo è fatto. Considera la domanda:

Richiesta:

I dati richiesti per la risposta sono in due tabelle: LAVORATORE e ASSEGNAZIONE. La soluzione SQL richiede l'elenco di entrambe le tabelle nella clausola FROM e la specifica di un tipo speciale di clausola WHERE:

SELEZIONA SKILL_TYPE

DA LAVORATORE, ASSEGNAZIONE

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID

AND BLDG_ID = 435

Cosa sta succedendo qui? Dobbiamo considerare due fasi nell'elaborazione di una data richiesta da parte del sistema.

1. Come di consueto, la clausola FROM viene elaborata per prima. Tuttavia, in questo caso, poiché nel comando sono specificate due tabelle, il sistema crea un prodotto cartesiano delle righe di queste tabelle. Ciò significa che viene creata una (logicamente) una grande tabella, costituita dalle colonne di entrambe le tabelle, in cui ogni riga di una tabella è abbinata a ciascuna riga dell'altra tabella. Nel nostro esempio, poiché la tabella WORKER ha cinque colonne e la tabella ASSIGNMENT ha quattro colonne, il prodotto cartesiano generato dal comando FROM avrà nove colonne. Il numero totale di righe del prodotto cartesiano è m * n, dove m è il numero di righe nella tabella LAVORATORE; e n è il numero di righe nella tabella ASSIGNMENT. Poiché ci sono 7 righe nella tabella WORKER e 19 righe nella tabella ASSIGNMENT, il prodotto cartesiano conterrà 7x19 o 133 righe. Se nel comando FROM sono elencate più di due tabelle, viene creato il prodotto cartesiano di tutte le tabelle specificate nel comando.

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

2. Dopo aver creato la tabella relazionale gigante, il sistema applica ancora la clausola WHERE come prima. Ogni riga della tabella creata dal comando FROM. viene confrontato con la condizione WHERE. Le righe che non soddisfano la condizione sono escluse dalla considerazione. La clausola SELECT viene quindi applicata alle righe rimanenti.

La clausola WHERE nella nostra query contiene due condizioni:

1. LAVORATORE. WORKER_ID = ASSIGNMENT.WORKER_ID

2.BLDG_ID = 435

La prima di queste condizioni è la condizione di join. Si noti che poiché entrambe le tabelle WORKER e ASSIGNMENT contengono una colonna denominata WORKER_ID, il loro prodotto cartesiano conterrà due colonne con quel nome. Per distinguerli, facciamo precedere il nome della colonna dal nome della tabella sorgente, separandola con un punto.

La prima condizione indica 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 sono escluse dalla tabella dei prodotti. Esattamente la stessa cosa accade quando si esegue l'operazione di unione naturale di un'algebra relazionale. (Tuttavia, c'è ancora qualche differenza rispetto a un join naturale: SQL non rimuove automaticamente la colonna WORKER_ID aggiuntiva). Il full join di queste due tabelle con la condizione aggiuntiva BLDG_ID = 435 è mostrato in fig. 1. L'applicazione del comando SELECT alla fine darà il seguente risultato della query:

TIPO DI COMPETENZA

Intonacatore

Conciatetti

Elettricista

Riso. 1. Unire le tabelle LAVORATORE e ASSEGNAZIONE

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

Richiesta: Elenca i dipendenti con i nomi dei loro dirigenti.

SELEZIONA A.WORKER_NAME, B.WORKER_NAME

DAL LAVORATORE A, LAVORATORE B

WHERE B.WORKER_ID = A.SUPV_ID

La clausola FROM in questo esempio crea due "copie" della tabella WORKER, assegnando loro gli alias A e B. L'alias è un nome alternativo assegnato alla tabella. Quindi le copie A e B della tabella WORKER sono collegate con il comando WHERE in base alla condizione che WORKER_ID in B e SUPV_ID in A sono uguali. Pertanto, ogni riga da A è unita alla riga B contenente informazioni sul gestore di riga A (Fig. 2).

Riso. 2. Unire due copie della tabella LAVORATORE

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

A.NOMEB.NOME

M.Faraday H.Columbus

C.Nemo G.Rickover R.Garrett R.Garrett

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

Alias. Nome alternativo dato alla tabella.

A.WORKER_NAME rappresenta un lavoratore e B.WORKER_NAME rappresenta un manager. Tieni presente che alcuni lavoratori sono i propri manager, il che deriva dall'uguaglianza WORKER_ID - SUPV_ID eseguita nelle loro righe.

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

Richiesta

SELEZIONA NOME_LAVORATORE

DA OPERAIO, INCARICO, COSTRUTTORE

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND

TIPO = "Ufficio"

Risultato:

Signor Faraday

G. Rickover

J. Barrister

Si noti che se un nome di colonna (ad esempio, WORKER_ID o BLDG_ID) ricorre in più di una tabella, per evitare ambiguità, è necessario far precedere il nome della colonna dal nome della tabella di origine. Ma se il nome della colonna ricorre in una sola tabella, come TYPE nel nostro esempio, allora 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"

il comando WHERE comporta l'esclusione di tutte le righe ad eccezione delle righe relative agli edifici per uffici. Ciò corrisponde ai requisiti della richiesta.

3. Sottoquery

Sottointerrogazione. Richiesta nella richiesta

Una sottoquery può essere inserita nella clausola WHERE di una query, espandendo così le capacità della clausola WHERE. Considera un esempio.

Richiesta: Quali sono le specialità dei lavoratori assegnati all'edificio 435?

SELEZIONA SKTLL_TYPE

FROM WORKER WHERE WORKER_ID IN

(SELEZIONA ID_LAVORATORE

DOVE BLDG_ID = 435)

Sottoquery in questo esempio

(SELEZIONA ID_LAVORATORE

DOVE BLDG_ID = 435)

Viene chiamata una query che contiene una sottoquery richiesta esterna O richiesta principale. La sottoquery genera il seguente set di ID lavoratore:

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 l'insieme creato dalla sottoquery. La query esterna elabora ogni riga della tabella WORKER in base alla clausola WHERE. Se WORKER_ID della riga si trova nel set (IN) creato dalla sottoquery, SKILL_TYPE della riga viene selezionato e visualizzato nella tabella risultante:

TIPO DI COMPETENZA

Intonacatore

Conciatetti

Elettricista

È molto importante che la clausola SELECT della sottoquery contenga WORKER_ID e solo WORKER_ID. In caso contrario, la clausola WHERE della query esterna, che indica che WORKER_ID si trova nell'insieme degli ID lavoratore, non avrebbe senso.

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

Sottoquery non correlata. Una sottoquery il cui valore non dipende da alcuna query esterna.

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

Richiesta: Elenca i lavoratori assegnati agli edifici per uffici.

Ancora una volta consideriamo la query con cui abbiamo appreso la connessione.

SELEZIONA LAVORATORE_MAME

WHERE WORKER_ID IN

(SELEZIONA ID_LAVORATORE

DOVE BLDG_ID IN

WHERE TIPO = "Ufficio"))

Risultato:

Signor Faraday

G. Rickover

J. Barrister

Si noti che non è necessario far precedere i nomi delle colonne da nessuna parte con i nomi delle tabelle, poiché ogni subquery elabora una e una sola tabella, quindi non possono esserci ambiguità.

La query viene eseguita in ordine dall'interno verso l'esterno. Cioè, la query più interna (o "più in basso") viene eseguita per prima, quindi viene eseguita la sottoquery che la contiene e quindi la query esterna.

Sottoquery correlate. Tutte le sottoquery discusse in precedenza erano indipendenti dalle query principali in cui erano utilizzate. Per indipendenza intendiamo che le subquery possono essere eseguite da sole come query complete. Passiamo ora a una classe di subquery i cui risultati 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 NOME_LAVORATORE

WHERE A.HRLY_RATE >

(SELEZIONA B.HRLY_RATE

WHERE B.WORKER_ID = A.SUPV_ID)

Risultato:

I passaggi logici per l'esecuzione di questa query sono:

1. Il sistema crea due copie della tabella LAVORATORE: copia A e copia B. Secondo come le abbiamo definite, A si riferisce all'operaio, B al manager.

2. Il sistema esamina quindi ogni riga A. Questa riga viene selezionata se soddisfa la clausola WHERE. Questa condizione significa che una riga verrà selezionata se il suo valore HRLY_RATE è maggiore di HRLY_RATE generato dalla sottoquery.

3. La subquery seleziona il valore HRLY_RATE dalla riga B, il cui WORKER_ID è uguale al SUPV_ID della riga A, in questo momento considerata dalla query principale. Questo è l'HRLY_RATE del manager.

Si noti che poiché A.HRLY_RATE può essere confrontato solo con un valore, la sottoquery deve restituire un solo valore. Questo valore varia a seconda della riga A considerata. Pertanto, la sottoquery è correlata alla query principale. Vedremo altri esempi di sottoquery correlate più avanti quando esploreremo le funzioni incorporate.

Operatori EXISTS e NOT EXISTS

Supponiamo di voler identificare i lavoratori che non sono assegnati a lavorare su un edificio. In apparenza, sembra che tale richiesta possa essere facilmente avanzata semplicemente negando la versione affermativa della richiesta. Supponiamo, ad esempio, di essere interessati a un edificio con BLDG_ID 435. Considera la query:

SELEZIONA LAVORATORE_ID

DOVE BLDG_ID NON 435

Sfortunatamente, questa è la formulazione sbagliata della soluzione. La query ci fornirà semplicemente gli ID dei lavoratori che lavorano su altri edifici. Ovviamente, alcuni di essi potrebbero essere assegnati anche all'edificio 435.

La soluzione ben formulata utilizza l'operatore NOT EXISTS (non esiste):

SELEZIONA LAVORATORE_ID

DOVE NON ESISTE

WHERE ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID AND

Risultato:

ID_LAVORATORE

Gli operatori EXISTS e NOT EXISTS vengono sempre posizionati prima della sottoquery. EXISTS restituisce true se l'insieme generato dalla sottoquery non è vuoto. Se l'insieme generato dalla sottoquery è vuoto, allora EXISTS è falso. L'operatore NOT EXISTS, ovviamente, funziona esattamente al contrario. È vero se il risultato della sottoquery è vuoto, falso in caso contrario.

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

Operatore NON ESISTE. Restituisce true se il set di risultati è vuoto.

In questo esempio, abbiamo utilizzato l'operatore NOT EXISTS. La subquery seleziona tutte quelle righe della tabella ASSIGNMENT dove WORKER_ID è uguale alla riga considerata dalla query principale e BLDG_ID è 435. Se questo set è vuoto, allora viene selezionata la riga del worker considerata dalla query principale perché questo significa che questo operaio non lavora all'edificio 435.

Nella nostra soluzione, utilizzando una sottoquery correlata. Se usiamo l'operatore IN invece di NOT EXISTS, possiamo cavarcela con una subquery non correlata:

SELEZIONA LAVORATORE_ID

WHERE WORKER_ID NON IN

(SELEZIONA ID_LAVORATORE

DOVE BLDG_ID = 435)

Questa soluzione è più semplice della soluzione con l'operatore NOT EXISTS. Sorge spontanea una domanda sul 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 "each" nella condizione. Tali domande vengono risolte in algebra relazionale utilizzando l'operazione di divisione e nel calcolo relazionale utilizzando il quantificatore universale. Ecco un esempio di query che contiene la parola "each" nella condizione:

Richiesta: Elenca i lavoratori assegnati a ciascun edificio.

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

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

Abbiamo evidenziato il doppio negativo. È chiaro che questa query è logicamente equivalente alla precedente.

Ora vogliamo formulare una soluzione in SQL. Per facilitare la comprensione della soluzione finale, daremo anzitutto soluzione ad un problema preliminare: il problema di individuare tutti gli edifici per i quali l'ipotetico lavoratore "1234" Non assegnato.

(I) SELEZIONA BLDG_ID

DOVE NON ESISTE

ASSIGNMENT.WORKER_ID = 1234)

Abbiamo contrassegnato questa query con una (I) come ci riferiremo in seguito. Se non c'è nessun edificio che soddisfi questa richiesta, allora il lavoratore 1234 viene assegnato a ciascun edificio e quindi soddisfa le condizioni della richiesta originaria. Per risolvere la query originale, dobbiamo generalizzare la query (I) da un lavoratore specifico 1234 alla variabile WORKER_ID e trasformare questa query modificata in una sottoquery della query più grande. Ecco una soluzione:

(II) SELEZIONA LAVORATORE_ID

DOVE NON ESISTE

DOVE NON ESISTE

WHERE ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND

ASSIGNMENT.WORKER_ID = LAVORATORE.WORKER_ID)

Risultato:

ID LAVORATORE

Si noti che la sottoquery che inizia alla quarta riga della query (II) è identica alla query (I) in cui "1234" è sostituito da WORKER.WORKER_ID. La domanda (II) può essere letta come segue:

Seleziona WORKER_ID da WORKER se non ci sono edifici 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 richiedono un'operazione di divisione nell'algebra relazionale e un quantificatore universale nel calcolo relazionale. In termini di facilità d'uso, l'operatore NOT EXISTS non offre vantaggi speciali, ovvero le query SQL che utilizzano NOT EXISTS due volte non sono più facili da comprendere rispetto alle soluzioni di algebra relazionale con un'operazione di divisione o alle soluzioni di calcolo relazionale con un quantificatore universale. Sono necessarie ulteriori ricerche per creare costrutti linguistici che consentano un modo più naturale per risolvere tali domande.

Funzioni integrate

Considera questi tipi di domande:

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 per i lavori di intonacatura dell'Edificio 312? Quante specialità diverse ci sono?

Per rispondere a queste domande sono necessarie funzioni di aggregazione che esaminano più righe della tabella e producono un singolo valore. SQL ha cinque di queste funzioni, chiamate funzioni integrate o funzioni impostate. Queste sono le funzioni SUM (somma), AVG (media), COUNT (conteggio), MAX (massimo) e MIN (minimo).

Funzione inline (funzione set). Funzione statistica che opera su più righe: SUM (somma), AVG (media), COUNT (conteggio), MAX (massimo), MIN (minimo).

Richiesta: Quali sono le tariffe orarie massime e minime?

SELEZIONA MAX(HRLY_RATE), MIN(HRLY_RATE)

Risultato: 17.40, 8.20

Funzioni MAX e MIN operano su una colonna della tabella. Selezionano il valore massimo o minimo, rispettivamente, da quella colonna. La nostra formulazione della query non contiene una clausola WHERE. Per la maggior parte delle richieste, 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 MEDIA(NUM_GIORNI)

DOVE BLDG_ID=435

Risultato: 12.33

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

SELEZIONA SOMMA(NUM_GIORNI)

DA ASSEGNAZIONE, LAVORATORE

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND

SKILL_TYPE = "Intonacatore" AND

Risultato: 27

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

Richiesta: Quante specialità diverse ci sono?

SELEZIONA CONTEGGIO (DISTINCT SKILL_TYPE)

Risultato: 4

Poiché la stessa specialità può essere ripetuta in più righe diverse, in questa query deve essere utilizzata la parola chiave DISTINCT (differente) 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 sia, ovviamente, ridondante con le funzioni MAX e MIN.

DISTINTO. Un operatore che esclude 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 espressioni calcolate. Per esempio:

Richiesta: Qual è lo stipendio settimanale medio?

SELEZIONA MEDIA(40*HRLY_RATE)

Risultato: 509.14

COUNT può fare riferimento all'intera riga, non solo 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 c'è una funzione incorporata nel comando SELECT, allora nient'altro può essere in questo comando SELECT. L'unica eccezione a questa regola è con la clausola GROUP BY, che esamineremo tra un momento.

Frasi GROUP BY e HAVING

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

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

Per risolvere questo problema, dobbiamo dividere i lavoratori in gruppi in base ai loro dirigenti. Successivamente determineremo la puntata massima all'interno di ciascun gruppo. In SQL è fatto così:

RAGGRUPPARE PER SUPV_ID

Risultato:

SUPV_IDMAX(TARIFFA ORARIA)

Durante l'elaborazione di questa query, il sistema prima divide le righe della tabella WORKER in gruppi secondo 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é esiste un solo valore SUPV_ID in questo gruppo, non vi è alcuna ambiguità SUPV_ID nel gruppo. Per ogni gruppo, l'istruzione SELECT restituisce SUPV_ID e calcola e restituisce anche il valore MAX(HRLY_RATE). Il risultato è presentato sopra.

In un'istruzione SELECT con funzioni incorporate, possono verificarsi solo le colonne incluse nella clausola GROUP BY. Si noti che SUPV_ID può essere utilizzato nell'istruzione SELECT poiché è incluso nella clausola GROUP BY.

Frase GRUPPO PER. Indica che le righe devono essere suddivise 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, i calcoli con funzioni integrate sono limitati nel senso che non è consentito l'uso di funzioni integrate all'interno di altre funzioni integrate. Quindi un'espressione come

MEDIA(MAX(HRLY_RATE))

vietato. L'attuazione di tale richiesta consisterà in due fasi. Per prima cosa dobbiamo inserire le offerte massime in una nuova tabella, e nella seconda fase calcolare la loro media.

Con il comando GROUP BY, puoi usare il comando WHERE:

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

SELEZIONA TIPO, MEDIA(QLTY_LEVEL)

DOVE STATO = 1

Risultato:

TIPO MEDIA(QLTY_LEVEL)

Negozio 1

Edificio residenziale 3

La clausola WHERE viene eseguita prima del comando GROUP BY. Pertanto, nessun gruppo può contenere una riga con uno stato diverso da 1. Le righe dello stato 1 vengono raggruppate per TIPO e quindi viene applicata una clausola SELECT a ciascun gruppo.

Frase AVERE. Impone condizioni ai gruppi.

Possiamo anche applicare condizioni ai gruppi creati dalla clausola GROUP BY. Questo viene fatto con la clausola HAVING. Supponiamo, ad esempio, di decidere di arricchire una delle query precedenti:

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

Possiamo riflettere questa condizione con il comando HAVING appropriato:

SELEZIONA SUPV_ID, MAX(HRLY_RATE)

DAL GRUPPO LAVORATORI PER SUPV_ID

CON CONTEGGIO(*) > 1

Risultato:

SUPV_ID MAX(HRLY_RATE)

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 valutata per prima, poiché viene valutata prima del raggruppamento. Ad esempio, considera la seguente modifica della query precedente:

Richiesta: Per ogni tipo di edificio, trova il livello di qualità medio tra gli edifici di stato 1. Considera solo quei tipi di edifici il cui livello di qualità massimo non supera 3.

SELEZIONA TIPO, MEDIA(QLTY_JLEVEL)

DOVE STATO = 1

HAVING MAX(QLTY_LEVEL)<= 3

Risultato:

TIPO MEDIA(QLTY_LEVEL)

Negozio 1

Edificio residenziale 3

Si noti che a partire dalla clausola FROM, le frasi vengono eseguite in ordine, quindi viene applicata la clausola SELECT. Ad esempio, la clausola WHERE viene applicata alla tabella BUILDING e tutte le righe in cui STATUS non è 1 vengono eliminate. Le righe rimanenti sono raggruppate per TIPO; tutte le righe con lo stesso valore TYPE finiscono nello stesso gruppo. Questo crea più gruppi, uno per ogni valore TYPE. La clausola HAVING viene quindi applicata a ciascun gruppo e vengono rimossi quei gruppi il cui valore del livello di qualità massimo è maggiore di 3. Infine, la clausola SELECT viene applicata ai restanti gruppi.

7. Funzioni integrate e sottoquery

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

Richiesta: Quale dipendente ha una tariffa oraria superiore alla media?

SELEZIONA NOME_LAVORATORE

DOVE HRLY_RATE >

(SELEZIONA MEDIA(HRLY_RATE)

Risultato:

H. Colombo

Si noti 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 offerta è maggiore della media calcolata.

Le query correlate possono anche utilizzare funzioni integrate:

Domanda: Quale dei dipendenti ha una tariffa oraria superiore alla media oraria tra i dipendenti dello stesso dirigente?

In questo caso, invece di calcolare una tariffa oraria media per tutti i dipendenti, dobbiamo calcolare la tariffa oraria media per ogni gruppo di dipendenti che riportano allo stesso responsabile. Inoltre, il nostro calcolo deve essere ripetuto per ogni lavoratore considerato dalla query principale:

SELEZIONA A. NOME_LAVORATORE

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

Tipi di condizioni di ricerca:
. Confronto con il risultato della sottoquery (=, >=)
. Verifica dell'appartenenza dei risultati di una sottoquery (IN)
. Test di esistenza (ESISTE)
. Confronto multiplo (quantitativo) (ANY, ALL)

Note sulle query nidificate:
. Una sottoquery deve selezionare solo una colonna (ad eccezione di una sottoquery con un predicato EXISTS) e il relativo tipo di dati del risultato deve corrispondere al tipo di dati del valore specificato nel predicato.
. In alcuni casi, puoi utilizzare la parola chiave DISTINCT per assicurarti di ottenere un singolo valore.
. Non è possibile includere una clausola ORDER BY e UNION in una sottoquery.
. Una sottoquery può apparire a sinistra oa destra del termine 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, un predicato IN speciale ed espressioni basate su colonne.
. Quando possibile, i join di tabella JOIN dovrebbero essere utilizzati al posto delle sottoquery.

Esempi di query nidificate:

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

2) Sottoquery correlate

In SQL è possibile creare sottoquery con un riferimento a una tabella da una query esterna. In questo caso, la sottoquery viene eseguita più volte, una per ogni riga della tabella dalla query esterna. Quindi è importante che la sottoquery utilizzi un indice. La sottoquery può fare riferimento alla stessa tabella di quella esterna. Se la query esterna restituisce un numero relativamente piccolo di righe, la sottoquery associata funzionerà più velocemente di quella non correlata. Se la sottoquery restituisce un numero ridotto di righe, la query associata verrà eseguita più lentamente 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 superiori al valore medio dell'ordine per questo cliente

3) Predicato ESISTE

Modulo di sintassi: ESISTE ()

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

Note sul predicato EXISTS:
. EXISTS è un predicato che restituisce VERO o FALSO e può essere utilizzato da solo o con altre espressioni booleane.
. EXISTS non può utilizzare funzioni di aggregazione nella sua sottoquery.
. Nelle sottoquery correlate (correlate, dipendenti - Correlate), il predicato EXISTS viene eseguito per ogni riga della tabella esterna.
. È possibile 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ù acquirenti.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) Predicati di confronto quantitativo

Modulo di sintassi: (=|>|=|) 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 si applicano solo alle sottoquery. Lo standard consente di utilizzare la parola chiave SOME invece di ANY, ma non tutti i DBMS la supportano.

Note sui predicati di confronto:
. Il predicato ALL restituisce TRUE se ogni valore selezionato durante l'esecuzione della sottoquery soddisfa la condizione specificata nel predicato della query esterna. Molto spesso è usato 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. Molto spesso è usato con le disuguaglianze.
. Se la sottoquery non restituisce righe, ALL assume automaticamente il valore TRUE (si considera che la condizione di confronto sia soddisfatta) e per ANY - FALSE.
. Se il confronto non è VERO per nessuna riga e sono presenti una o più righe nulle, ANY restituisce UNKNOWN.
. Se il confronto non è FALSO per nessuna riga e sono presenti una o più righe nulle, ALL restituisce UNKNOWN.

Esempi per il predicato di confronto quantitativo:

SELEZIONA * FROM Venditori WHERE Città=QUALSIASI(SELEZIONA Città FROM Cliente)
SELEZIONA * FROM Ordini WHERE Amt TUTTI(SELEZIONA Valutazione FROM Cliente WHERE Città='Roma')

5) Predicato di unicità

UNICO|DISTINTO ()

Il predicato viene utilizzato per verificare l'univocità (assenza di duplicati) nell'output della sottoquery. Inoltre, nel predicato UNIQUT, le righe con valori NULL sono considerate univoche e nel predicato DISTINCT due valori null sono considerati uguali tra loro.

6) Abbina il predicato

INCONTRO ()

Il predicato MATCH verifica se il valore di una stringa di query corrisponderà al valore di qualsiasi stringa restituita dalla sottoquery. Questa subquery differisce dai predicati IN e ANY in quanto consente di elaborare corrispondenze “parziali” (PARTIAL) che possono verificarsi tra righe che hanno parte di valori NULL.

7) Richieste nella sezione FROM

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

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

8) Query ricorsive

CON RICORSIVO
D1 COME SELEZIONA... DA... DOVE...
D2 COME SELEZIONA... DA... DOVE...