Corso SQL Server

SQL Server lezione #06 Come codificare le sottoquery

 

Come codificare le subquery

Una subquery è un'istruzione SELECT codificata all'interno di un'altra istruzione SQL. Di conseguenza, è possibile utilizzare le subquery per costruire query che sarebbero difficili o impossibili da eseguire altrimenti. In questa lezione si apprende come utilizzare le subquery all'interno delle istruzioni SELECT. Poi, nella lezione successiva, si apprenderà come utilizzarle quando si codificano le istruzioni INSERT, UPDATE e DELETE.

Introduzione alle subquery

Dal momento che si sa come codificare le istruzioni SELECT, si sa già come creare una subquery. Si tratta semplicemente di un'istruzione SELECT codificata all'interno di un'altra istruzione SQL. Il trucco per usare le subquery è sapere dove e quando usarle. Le specifiche dell'uso delle subquery sono descritte in questa lezione. I due argomenti che seguono, tuttavia, forniranno una panoramica su dove e quando utilizzarle.

Come utilizzare le subquery

Nell'esempio qui sotto, si può vedere che una sottoquery può essere codificata, o introdotta, nella clausola WHERE, HAVING, FROM o SELECT di un'istruzione SELECT. L'istruzione SELECT di questo esempio illustra come sia possibile utilizzare una subquery nella condizione di ricerca di una clausola WHERE. Quando viene usata in una condizione di ricerca, una sottoquery può essere chiamata condizione di ricerca di una sottoquery o predicato di una sottoquery. L'istruzione riportata in questa figura recupera tutte le fatture dalla tabella Fatture che hanno un totale di fatture superiore alla media di tutte le fatture. A tale scopo, l'istruzione calcola la media di tutte le fatture. Quindi, la condizione di ricerca verifica se il totale delle fatture è superiore alla media. Quando una sottoquery restituisce un singolo valore, come in questo esempio, è possibile utilizzarla ovunque si utilizzi normalmente un'espressione. Tuttavia, una sottoquery può restituire un insieme di risultati a colonna singola con due o più righe. In questo caso, può essere utilizzata al posto di un elenco di valori, come l'elenco di un operatore IN. Inoltre, se una subquery è codificata all'interno di una clausola FROM, può restituire un insieme di risultati con due o più colonne. Tutti questi tipi di subquery sono illustrati in questa lezione. È anche possibile codificare una subquery all'interno di un'altra subquery. In questo caso, le subquery si dice che sono annidate. Poiché le subquery annidate possono essere difficili da leggere e possono causare scarse prestazioni, è consigliabile utilizzarle solo quando necessario.

Quattro modi per introdurre una sottoquery in un'istruzione SELECT

Un'istruzione SELECT che utilizza una sottoquery nella clausola WHERE

    USE AP
    GO                    
    SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
    FROM Invoices
    WHERE InvoiceTotal > 
        (SELECT AVG(InvoiceTotal)
            FROM Invoices)
    ORDER BY InvoiceTotal

Il valore restituito dalla sottoquery

    USE AP
    GO                    
    SELECT AVG(InvoiceTotal)
                FROM Invoices

Descrizione

Come le subquery si confrontano con i Join

Nell'ultima figura, si è visto un esempio di una subquery che restituisce un valore aggregato che viene utilizzato nella condizione di ricerca di una clausola WHERE. Questo tipo di subquery consente un'elaborazione che non può essere eseguita in altro modo. Tuttavia, la maggior parte delle subquery può essere riformulata come join e la maggior parte dei join può essere riformulata come subquery. Questo è illustrato dalle istruzioni SELECT dell'esempio che segue. Entrambe le istruzioni SELECT di questa figura restituiscono un insieme di risultati che consiste in righe e colonne selezionate dalla tabella Fatture. In questo caso, solo le fatture dei fornitori della California. La prima istruzione utilizza un join per combinare le tabelle Venditori e Fatture in modo da poter verificare la colonna VendorState per ogni fattura. La seconda istruzione, invece, utilizza una sottoquery per restituire un insieme di risultati costituito dalla colonna VendorID per ogni fornitore della California. Quindi, questo insieme di risultati viene utilizzato con l'operatore IN nella condizione di ricerca in modo che solo le fatture con un VendorID in quell'insieme di risultati sono incluse nell'insieme di risultati finale. Quindi, potendo scegliere, quale tecnica utilizzare? In generale, consiglio di utilizzare la tecnica che produce il codice più leggibile. Ad esempio ritengo che una join tenda a essere più intuitiva di una subquery quando si utilizza una relazione esistente tra due tabelle. Questo è il caso delle tabelle Venditori e Fatture utilizzate negli esempi di questa figura. D'altra parte una sottoquery tende a essere più intuitiva quando utilizza una relazione ad hoc. Man mano che le query diventano più complesse, si può scoprire che è più facile codificarle utilizzando le subquery, indipendentemente dalle relazioni coinvolte. D'altra parte una query con un join interno di solito è più veloce della stessa query con una subquery. Quindi, se le prestazioni del sistema sono un problema, si consiglia di utilizzare i join interni invece delle query. Si deve anche tenere presente che quando si usa una sottoquery in una condizione di ricerca, i suoi risultati non possono essere inclusi nell'insieme dei risultati finali. Ad esempio, il secondo esempio in questa figura non può essere modificato per includere la colonna VendorName della tabella Vendors. Questo perché la tabella Venditori non è nominata nella clausola FROM della query esterna. Quindi, se si vogliono includere le informazioni di entrambe le tabelle nell'insieme dei risultati, è necessario utilizzare un join.

Una query che utilizza un join interno

    USE AP
    GO
    SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
    FROM Invoices i
        JOIN Vendors v
        ON i.VendorID = v.VendorID
    WHERE VendorState = 'CA'
    ORDER BY InvoiceDate

La stessa query riproposta con una sottoquery

    USE AP
    GO
    SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
    FROM Invoices
    WHERE VendorID IN
        (SELECT VendorID
        FROM Vendors
        WHERE VendorState = 'CA')
    ORDER BY InvoiceDate

Vantaggi dei join

Vantaggi delle subquery

Descrizione

Come codificare le subquery nelle condizioni di ricerca

È possibile utilizzare diverse tecniche per lavorare con una sottoquery in una condizione di ricerca. Queste tecniche verranno illustrate negli argomenti che seguono. Si tenga presente che, sebbene tutti gli esempi illustrino l'uso delle di subquery in una clausola WHERE, tutte le informazioni si applicano anche alla clausola HAVING.

Come utilizzare le subquery con l'operatore IN

Nella lezione 3, si è appreso come utilizzare l'operatore IN per verificare se un'espressione è contenuta in un elenco di valori. Un modo per fornire tale elenco di valori è quello di utilizzare una subquery. Questo è illustrato nell' esempio che segue. L'esempio in questa figura recupera i fornitori dalla tabella Vendors che non hanno fatture nella tabella Fatture. A tale scopo, si utilizza una sottoquery per recuperare il VendorID di ciascun fornitore nella tabella Fatture. Il risultato è un insieme di risultati come quello mostrato in questa figura, che contiene solo la colonna VendorID. Quindi, questo viene utilizzato per filtrare i fornitori da includere nel set di risultati finale. Si notano due cose su questa sottoquery. Innanzitutto, restituisce una singola colonna. Questo è un requisito quando si usa una sottoquery con l'operatore IN. In secondo luogo, la sottoquery include la parola chiave DISTINCT. In questo modo, se esiste più di una fattura, esiste più di una fattura per un fornitore, il VendorID per quel fornitore sarà incluso una sola volta. Si noti, tuttavia, che quando la query viene analizzata da SQL Server, questa parola chiave viene aggiunta automaticamente. È quindi possibile ometterla se lo si desidera. Nella figura precedente, si è visto che una query che utilizza una sottoquery con l'operatore IN può essere riformulata utilizzando un join interno. Allo stesso modo, una query che utilizza una sottoquery con l'operatore NOT IN può essere riproposta utilizzando un join esterno. La prima query mostrata in questa figura, ad esempio, può essere riformulata come la seconda query. In questo caso, però, credo che la query con la subquery sia più leggibile. Inoltre, una query con una sottoquery a volte viene eseguita più velocemente di una query con un join esterno. Naturalmente, questo dipende da una serie di fattori. In particolare, dipende dalle dimensioni delle tabelle e dal numero relativo di righe non corrispondenti. Quindi, se le prestazioni sono un problema, si consiglia di testare la query in entrambi i modi per vedere quale viene eseguita più velocemente.

La sintassi di una clausola WHERE che utilizza una frase IN con una sottoquery

    WHERE test_expression [NOT] IN (subquery)

Una query che restituisce i fornitori senza fatture

    USE AP
    GO
    SELECT VendorID, VendorName, VendorState
    FROM Vendors
    WHERE VendorID NOT IN
        (SELECT DISTINCT VendorID FROM Invoices)

Il risultato della sottoquery

    USE AP
    GO
    SELECT DISTINCT VendorID FROM Invoices

La query riformulata senza una sottoquery

    USE AP
    GO
    SELECT Vendors.VendorID, VendorName, VendorState
    FROM Vendors LEFT JOIN Invoices
        ON Vendors.VendorID = Invoices.VendorID
    WHERE Invoices.VendorID IS NULL

Descrizione

Come confrontare il risultato di una sottoquery con un'espressione

L'esempio qui sotto illustra come si possono usare gli operatori di confronto per confrontare un'espressione con il risultato di una sottoquery. Nell'esempio di questa figura, la subquery restituisce la media del saldo dovuto delle fatture della tabella Fatture che hanno un saldo dovuto maggiore di zero. Quindi, utilizza questo valore per recuperare tutte le fatture che hanno un saldo dovuto inferiore alla media. Quando si utilizza un operatore di confronto come mostrato in questa figura, la subquery deve restituire un singolo valore. Nella maggior parte dei casi, ciò significa che si utilizza una funzione aggregata. Tuttavia, è possibile utilizzare gli operatori di confronto anche con subquery che restituiscono due o più valori. A tale scopo, si utilizza la parola chiave SOME, ANY o ALL per modificare l'operatore di confronto. Si apprenderà di più su queste parole chiave nei prossimi due argomenti.

La sintassi di una clausola WHERE che confronta un'espressione con il valore restituito da una sottoquery

    WHERE expression comparison_operator [SOME | ANY ! ALL] (subquery)

Una query che restituisce le fatture con un saldo dovuto inferiore alla media

    USE AP
    GO
    SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
        (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
    FROM Invoices
    WHERE (InvoiceTotal - PaymentTotal - CreditTotal)  > 0 
        AND (InvoiceTotal - PaymentTotal - CreditTotal) <
            (SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal)
            FROM Invoices
            WHERE (InvoiceTotal - PaymentTotal - CreditTotal) > 0)
    ORDER BY InvoiceTotal DESC

Il valore restituito dalla sottoquery

    USE AP
    GO
    SELECT AVG(InvoiceTotal - PaymentTotal - CreditTotal)
    FROM Invoices
    WHERE (InvoiceTotal - PaymentTotal - CreditTotal) > 0

Descrizione

Come utilizzare la parola chiave ALL

La figura qui sotto mostra come utilizzare la parola chiave ALL. Questa parola chiave modifica l'operatore di confronto in modo che la condizione debba essere vera per tutti i valori restituiti da una sottoquery. Questo è equivalente alla codifica di una serie di condizioni collegate da operatori AND. La tabella in cima a questa figura descrive come funziona questo funzionamento per alcuni degli operatori di confronto. Se si utilizza l'operatore maggiore di (>), l'espressione deve essere maggiore del valore massimo restituito dalla sottoquery. Al contrario, se si utilizza l'operatore minore di (<), l'espressione deve essere inferiore al valore minimo restituito dalla sottoquery. Se si usa l'operatore di uguaglianza (=), l'espressione deve essere uguale a tutti i valori restituiti dalla subquery. Se si utilizza l'operatore non uguale ( <>), l'espressione non deve essere uguale a nessuno dei valori restituiti dalla subquery. Si noti che una condizione non uguale può essere riformulata utilizzando una condizione NOT IN La query in questa figura illustra l'uso dell'operatore greater than con la parola chiave ALL. In questo caso, la sottoquery seleziona la colonna lnvoiceTotal per tutte le fatture con un VendorID pari a 34. Il risultato è una tabella con due righe, come mostrato in questa figura. Quindi, la query esterna recupera le righe della tabella Fatture che hanno un totale di fatture superiore a tutti i valori restituiti dalla sottoquery. In altre parole, questa query restituisce tutte le fatture che hanno totali maggiore della fattura più grande per il fornitore numero 34. Quando si utilizza l'operatore ALL, occorre tenere presente che se la sottoquery non restituisce alcuna riga, l'operazione di confronto sarà sempre vera. Al contrario se invece la sottoquery restituisce solo valori nulli, l'operazione di confronto sarà sempre falsa. In molti casi, una condizione con la parola chiave ALL può essere riscritta in modo da essere più facile da leggere e da mantenere. Ad esempio, la condizione nella query di questa figura potrebbe essere riscritta per utilizzare la funzione MAX in questo modo: 

    WHERE InvoiceTotal >
        (SELECT MAX(InvoiceTotal)
        FROM Invoices
        WHERE vendorID = 34)

Ogni volta che è possibile, quindi, si consiglia di sostituire la parola chiave ALL con una condizione equivalente.

Come funziona la parola chiave ALL

CondizioneEspressione equivalenteDescrizione
x > ALL (1,2)x > 2x deve essere maggiore di tutti i valori restituiti dalla sottoquery, il che significa che deve essere maggiore del valore massimo.
x < ALL (1,2)x < 1x deve essere inferiore a tutti i valori restituiti dalla sottoquery, il che significa che deve essere inferiore al valore minimo.
x = ALL (1,2)(x = 1) AND (x = 2)Questa condizione può essere valutata come vera solo se la sottoquery restituisce un singolo valore o se tutti i valori valori restituiti dalla sottoquery sono gli stessi. In caso contrario, la valutazione è Falsa.
x <> ALL (1,2 )(x <> 1) AND (x <> 2)Questa condizione è equivalente a: x NON IN (1 , 2 )

Una query che restituisce le fatture più grandi della fattura più grande del fornitore 34

    USE AP
    GO                    
    SELECT VendorName , InvoiceNumber, InvoiceTotal
    FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID
    WHERE InvoiceTotal > ALL
        (SELECT InvoiceTotal
        FROM Invoices
        WHERE VendorID = 34)
    ORDER BY VendorName

Il risultato della sottoquery

    USE AP
    GO
    SELECT InvoiceTotal
    FROM Invoices
    WHERE VendorID = 34

Descrizione

Come usare le parole chiave ANY e SOME

L'esempio qui sotto mostra come utilizzare le parole chiave ANY e SOME. Queste parole chiave sono utilizzate per verificare se un confronto è vero per uno o più valori restituiti da una subquery. Ciò equivale a codificare una serie di condizioni collegate con gli operatori OR. Poiché queste parole chiave sono equivalenti, si può usare quella che si preferisce. La tabella in cima alla figura descrive il funzionamento di queste parole chiave con alcuni operatori di confronto. L'esempio in questa figura mostra come si può utilizzare la parola chiave ANY con l'operatore minore di. Questa istruzione è simile a quella vista nella figura precedente, tranne per il fatto che recupera le fatture con totali di fatture inferiori ad almeno uno dei totali di fatture di un determinato fornitore. Come l'istruzione della figura precedente, questa condizione può essere riscritta utilizzando la funzione MAX in questo modo: 

    WHERE InvoiceTotal <
        (SELECT MAX(InvoiceTotal)
        FROM Invoices
        WHERE VendorID = 115)

Poiché di solito è possibile sostituire una condizione ANY con una condizione equivalente più leggibile, probabilmente non si userà spesso ANY.

Come funzionano le parole chiave ANY e SOME

CondizioneEspressione equivalenteDescrizione
x > ANY (1,2)x > 1x deve essere maggiore di almeno uno dei valori restituiti dall'elenco di sottoquery, il che significa che deve essere maggiore del valore minimo restituito dalla sottoquery.
x < ANY (1,2)x < 2x deve essere minore di almeno uno dei valori restituiti dall'elenco di sottoquery, il che significa che deve essere minore del valore valore massimo restituito dalla sottoquery.
x = ANY (1,2)(x = 1) OR (x = 2)Questa condizione è equivalente a: x IN (1, 2)
x <> ANY (1,2 )(x <> 1) OR (x <> 2)Questa condizione sarà valutata come vera per qualsiasi insieme di risultati non vuoto che contenga almeno un valore valore non nullo che non sia uguale a x.

Una query che restituisce le fatture più piccole della fattura più grande per il fornitore 115

    USE AP
    GO
    SELECT VendorName, InvoiceNumber, InvoiceTotal
    FROM Vendors v
        JOIN Invoices i
        ON v.VendorID = i.VendorID
    WHERE InvoiceTotal < ANY
        (SELECT InvoiceTotal
        FROM Invoices
        WHERE VendorID = 115)

Il risultato della sottoquery

    USE AP
    GO                    
    SELECT InvoiceTotal
    FROM Invoices
    WHERE VendorID = 115

Descrizione

Come codificare le subquery correlate

Le subquery viste finora in questa lezione sono subquery che vengono eseguite una sola volta per l'intera query. Tuttavia, è possibile codificare anche subquery che vengono eseguite una sola volta per ogni riga elaborata dalla query esterna. Questo tipo di query si chiama subquery correlata ed è simile all'uso di un ciclo per eseguire elaborazioni ripetitive in un linguaggio di programmazione procedurale. L'esempio qui sotto illustra il funzionamento delle sottoquery correlate. L'esempio recupera le righe dalla tabella Fatture per le fatture che hanno un totale di fattura superiore alla media di tutte le fatture dello stesso fornitore. A tale scopo, la condizione di ricerca nella clausola WHERE della sottoquery si riferisce al valore VendorID della fattura corrente. In questo modo, solo le fatture per il fornitore corrente saranno incluse nella media. Ogni volta che viene elaborata una riga della query esterna, il valore della colonna VendorID di quella riga viene sostituito dal riferimento alla colonna nella sottoquery. Quindi, la sottoquery viene eseguita in base al valore corrente della colonna VendorID. Se il valore VendorID è 95, ad esempio, verrà eseguita questa sottoquery: 

    SELECT AVG(InvoiceTotal)
    FROM Invoices AS i_sub
    WHERE i_sub.VendorID = 95

Dopo l'esecuzione di questa sottoquery, il valore che restituisce viene utilizzato per determinare se la fattura corrente è inclusa nell'insieme dei risultati. Ad esempio, il valore restituito dalla sottoquery per il fornitore 95 è 28.5016. Quindi, questo valore viene confrontato con il totale della fattura corrente. Se il totale della fattura è maggiore di questo valore, la fattura viene inclusa nell'insieme dei risultati, altrimenti, non viene inclusa. Questo test viene ripetuto fino a quando tutte le fatture della tabella Fatture non sono state processate. Studiando questo esempio, si nota come i nomi delle colonne nella clausola WHERE della query interna sono qualificati per indicare se si riferiscono a una colonna della query interna o di quella esterna. In questo caso, la stessa tabella è utilizzata sia nella query interna che in quella esterna, quindi sono stati assegnati degli alias, o nomi di correlazione, alle tabelle. Questi nomi di correlazione vengono usati per qualificare i nomi delle colonne. Sebbene sia necessario qualificare un riferimento a una colonna nella query esterna, non è necessario qualificare un riferimento a una colonna nella query interna. Tuttavia, è prassi comune qualificare entrambi i nomi, soprattutto se si riferiscono alla stessa tabella. Poiché le subquery correlate possono essere difficili da codificare, è possibile che si voglia testare una subquery separatamente prima di utilizzarla in un'altra istruzione SELECT. Per farlo, tuttavia, è necessario sostituire un valore costante alla variabile che si riferisce a una colonna della query esterna. Questo è ciò che ho fatto per ottenere il totale medio della fattura per il fornitore 95. Una volta che si è sicuri che la sottoquery funziona da sola, si può sostituire il valore costante con un riferimento alla query esterna, in modo da poterla utilizzare all'interno di un'istruzione SELECT.

Una query che utilizza una sottoquery correlata per restituire ciascuna fattura che è più alta della fattura media del fornitore

    USE AP
    GO
    SELECT VendorID, InvoiceNumber, InvoiceTotal
    FROM Invoices i
    WHERE InvoiceTotal >
        (SELECT AVG(InvoiceTotal)
        FROM Invoices AS i_sub
        WHERE i_sub.VendorID = i.VendorID)
    ORDER BY VendorID, InvoiceTotal

Il valore restituito dalla sottoquery per il venditore 95

    USE AP
    GO                    
    SELECT AVG(InvoiceTotal)
    FROM Invoices AS i_sub
    WHERE i_sub.VendorID = 95

Descrizione

Note

Come utilizzare l'operatore EXISTS

L'esempio qui sotto mostra come utilizzare l'operatore EXISTS con una sottoquery. Questo operatore verifica se la sottoquery restituisce o meno un insieme di risultati. In altre parole, verifica se l'insieme di risultati esiste. Quando si utilizza questo operatore, la sottoquery non restituisce effettivamente un insieme di risultati alla query esterna. Invece, restituisce semplicemente un'indicazione della presenza di righe che soddisfano la condizione di ricerca della sottoquery. Per questo motivo, le query che utilizzano questo operatore vengono eseguite rapidamente. In genere si usa l'operatore EXISTS con una sottoquery correlata, come illustrato in questa figura. Questa query recupera tutti i venditori della tabella Vendors che non hanno fatture nella tabella Fatture. Si noti che questa query restituisce i venditori come le due query viste nelle figure che utilizzano l'operatore IN con una sottoquery e un join esterno. Tuttavia, la query di questa figura viene eseguita più rapidamente di entrambe le query precedenti. In questo esempio, la sottoquery correlata seleziona tutte le fatture che hanno lo stesso valore di VendorID del fornitore corrente nella query esterna. Poiché la sottoquery non restituisce un insieme di risultati, non importa quali colonne siano incluse nella clausola SELECT. È quindi consuetudine codificare un asterisco. In questo modo, SQL Server determinerà quali colonne selezionare per ottimizzare le prestazioni. Dopo che la sottoquery è stata eseguita, la condizione di ricerca nella clausola WHERE della query esterna utilizza NOT EXISTS per verificare se sono state trovate fatture per il fornitore corrente. In caso negativo, la riga del fornitore viene inclusa nel set di risultati. Altrimenti, non viene inclusa.

La sintassi di una sottoquery che utilizza l'operatore EXISTS

    WHERE [NOT] EXISTS (subquery)

Una query che restituisce i fornitori senza fatture

    USE AP
    GO
    SELECT VendorID, VendorName, VendorState
    FROM Vendors v
    WHERE NOT EXISTS
        (SELECT *
        FROM Invoices i
        WHERE i.VendorID = v.VendorID)

Descrizione

Altri modi per utilizzare le subquery

Anche se di solito si usano le subquery nelle clausole WHERE o HAVING di un'istruzione SELECT, è possibile utilizzarle anche nelle clausole FROM e SELECT. Scoprirete come farlo negli argomenti che seguono.

Come codificare le subquery nella clausola FROM

L'esempio qui sotto mostra come codificare una sottoquery in una clausola FROM. Come si può vedere, è possibile codificare una subquery al posto di una specifica di tabella. In questo esempio, i risultati della subquery, chiamati tabella derivata, sono uniti a un'altra tabella. Quando si utilizza una sottoquery in questo modo, essa può restituire un numero qualsiasi di righe e colonne. Le sottoquery sono tipicamente utilizzate nella clausola FROM per creare tabelle derivate che forniscono dati riassunti a una query di riepilogo. La subquery in questa figura, crea una tabella derivata che contiene i valori VendorID e i totali medi delle fatture dei cinque fornitori con le fatture medie più alte. Per fare ciò raggruppa le fatture per VendorID, le ordina in sequenza decrescente per totale medio della fattura e quindi restituisce le prime cinque righe. La tabella derivata viene poi unita alla tabella Fatture e le righe risultanti vengono raggruppate per VendorID. Infine, per le righe raggruppate vengono calcolati la data massima della fattura e il totale medio della fattura. I risultati vengono ordinati in base alla data massima della fattura in ordine decrescente. Si possono notare quattro cose in questa query. Innanzitutto, alla tabella derivata viene assegnato un alias di tabella, in modo da potervi fare riferimento dalla query esterna. Secondo, al risultato della funzione AVG nella sottoquery viene assegnato un alias di colonna. Questo perché una tabella derivata non può avere colonne senza nome. In terzo luogo, poiché la sottoquery utilizza una frase TOP, include anche una clausola ORDER BY. Quarto, anche se si potrebbe pensare di poter utilizzare i totali medi delle fatture calcolati nella lista di selezione della query esterna, non è possibile. Questo perché la query esterna include una clausola GROUP BY, quindi solo le funzioni aggregate, le colonne denominate nella clausola GROUP BY e i valori costanti possono essere inclusi in questo elenco. Per questo motivo, la funzione AVG viene ripetuta nell'elenco di selezione. Quando viene utilizzata nella clausola FROM, una sottoquery è simile a una vista. Come una vista è un'istruzione SELECT predefinita salvata nel database. Poiché viene salvata con il database, una vista ha in genere prestazioni più più efficiente di una tabella derivata. Tuttavia, non è sempre pratico usare una vista. In questi casi, le tabelle derivate possono essere molto utili. Inoltre, le tabelle derivate possono essere utili per testare possibili soluzioni prima di creare una vista. Quindi, una volta che la tabella derivata funziona nel modo desiderato, si può definire la vista basata sulla sottoquery utilizzata per creare la tabella derivata.

A query that uses a derived table to retrieve the top 5 vendors by average invoice total

    USE AP
    GO
    SELECT i.VendorID, MAX(InvoiceDate) AS LatestInv,
        AVG(InvoiceTotal) AS AvgInvoice
    FROM Invoices i
        JOIN
            (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice
            FROM Invoices
            GROUP BY VendorID
            ORDER BY AvgInvoice DESC) tv -- tv for TopVendor
                ON i.VendorID = tv.VendorID
    GROUP BY i.VendorID
    ORDER BY LatestInv DESC

La tabella derivata generata dalla sottoquery

    USE AP
    GO
    SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice
    FROM Invoices
    GROUP BY VendorID
    ORDER BY AvgInvoice DESC

Descrizione

Come codificare le subquery nella clausola SELECT

L'esempio che segue mostra come utilizzare le subquery nella clausola SELECT. Come si può vedere, si può usare una sottoquery al posto di una specifica di colonna. Per questo motivo la subquery deve restituire un singolo valore. Nella maggior parte dei casi, le sottoquery utilizzate nella clausola SELECT saranno subquery correlate. La subquery di questa figura, ad esempio, calcola la data massima della fattura per ogni fornitore della tabella Fornitori. Per farlo, fa riferimento alla colonna VendorID della tabella Fatture nella query esterna. Poiché le subquery codificate nella clausola SELECT sono difficili da leggere e perché le subquery correlate sono tipicamente inefficienti, non si dovrebbero usare a meno che non si possa trovare un'altra soluzione. Nella maggior parte dei casi, tuttavia, è possibile sostituire la subquery correlata con un join. La prima query mostrata in questa figura, per esempio, potrebbe essere come mostrato nella seconda query. Questa query unisce le tabelle Venditori e Fatture, raggruppa le righe per VendorName e utilizza la funzione MAX per calcolare la data massima della fattura per ogni fornitore. Come si può notare, questa query è molto più semplice da leggere rispetto a quella con la sottoquery. Inoltre, viene eseguita più rapidamente.

Una query che utilizza una sottoquery correlata nella sua clausola SELECT per recuperare la fattura più recente per ogni fornitore

    USE AP
    GO
    SELECT DISTINCT VendorName,
        (SELECT MAX(InvoiceDate) FROM Invoices i
        WHERE i.VendorID = v.VendorID) AS LatestInv
    FROM Vendors v
    ORDER BY LatestInv DESC

La stessa query è stata rielaborata utilizzando un join

    USE AP
    GO
    SELECT VendorName, MAX(InvoiceDate) AS LatestInv
    FROM Vendors v
        LEFT JOIN Invoices i
            ON v.VendorID = i.VendorID
    GROUP BY VendorName
    ORDER BY LatestInv DESC

Descrizione

Linee guida per lavorare con query complesse

Finora gli esempi di query che utilizzano le subquery sono stati relativamente semplici. Tuttavia, questo tipo di query può complicarsi in fretta, soprattutto se le sottoquery sono annidate. Per questo motivo, è necessario assicurarsi di pianificare e testare queste query con attenzione. Tra poco si apprenderà una procedura, ma prima vedremo una query complessa che illustra il tipo di query di cui sto parlando.

Una query complessa che utilizza sottoquery

L'esempio che segue presenta una query che utilizza tre sottoquery. La prima sottoquery viene utilizzata nella clausola FROM della query esterna per creare una tabella derivata che contiene lo stato, il nome e l'importo totale della fattura per ogni fornitore della tabella Vendors. La seconda sottoquery è anch'essa utilizzata nella clausola FROM della query esterna per creare una tabella derivata unita alla prima tabella. Questa tabella derivata contiene lo stato e l'importo totale delle fatture per il fornitore di ogni stato che ha il totale di fattura più alto. Per creare questa tabella, una terza sottoquery è annidata all'interno della clausola FROM della sottoquery. Questa sottoquery è identica alla prima. Dopo che le due tabelle derivate sono state create, vengono unite in base alle colonne di ciascuna tabella che contengono lo stato e il totale della fattura. Il risultato finale include lo stato, il nome e l'importo totale della fattura per il fornitore in ogni stato con il totale della fattura più alto. Questo insieme di risultati è ordinato per stato. Come si può vedere, questa query è piuttosto complicata e difficile da capire. In effetti, ci si potrebbe chiedere se non esista una soluzione più semplice per questo problema. Ad esempio, si potrebbe pensare di risolvere il problema semplicemente unendo le tabelle Venditori e Fatture e creando un aggregato raggruppato. Tuttavia, se si raggruppa per stato del venditore, non sarebbe possibile includere il nome del venditore nell'insieme dei risultati. E se si raggruppasse per stato e nome del venditore, l'insieme dei risultati includerebbe tutti i fornitori, non solo il fornitore di ogni stato con il totale di fatture più alto. Se si pensa a come si potrebbe risolvere questa query in altro modo, credo che si converrà che la soluzione presentata qui è abbastanza semplice. Tuttavia, nell'esempio impariamo come utilizzare una funzione introdotta con SQL Server 2005 per semplificare questa query. In particolare, questa funzione consente di codificare un'unica Summary invece di codificare le subquery Summaryl e Summary2. mostrate qui.

Una query che utilizza tre sottoquery

    USE AP
    GO
    SELECT Summary1.VendorState, Summary1.VendorName, TopInState.SumOfInvoices
    FROM
            (SELECT v.VendorState, v.VendorName, 
                    SUM(i.InvoiceTotal) AS SumOfInvoices
                FROM Invoices AS i JOIN Vendors AS v ON i.VendorID = v.VendorID
                GROUP BY v.VendorState, v.VendorName) AS Summary1
        JOIN 
            (SELECT Summary2.VendorState, 
                    MAX(Summary2.SumOfInvoices) AS SumOfInvoices
                FROM
                (SELECT v.VendorState, v.VendorName, 
                        SUM(i.InvoiceTotal) AS SumOfInvoices
                    FROM Invoices AS i JOIN Vendors AS v ON i.VendorID = v.VendorID
                    GROUP BY v.VendorState, v.VendorName) AS Summary2
            GROUP BY Summary2.VendorState) AS TopInState
        ON Summary1.VendorState = TopInState.VendorState AND
            Summary1.SumOfInvoices = TopInState.SumOfInvoices
    ORDER BY Summary1.VendorState

Come funziona la query

Una procedura per costruire query complesse

Per costruire una query complessa come quella della figura precedente, si può usare una procedura come quella della figura qui sotto. Per iniziare, è necessario indicare il problema da risolvere, in modo che sia chiaro cosa si vuole ottenere con la query. In questo caso, la domanda è: "Quale fornitore in ogni stato ha il totale delle fatture più alto?"". Una volta chiarito il problema, si dovrebbe delineare la query utilizzando pseudocodice. Lo pseudocodice è semplicemente un codice che rappresenta l'intento della query, ma non necessariamente della query, ma non utilizza necessariamente il codice SQL. Lo pseudocodice mostrato in questa figura, ad esempio, utilizza in parte codice SQL e in parte codice inglese. Si noti che questo pseudocodice identifica le due sottoquery principali. Poiché queste sottoquery definiscono tabelle derivate, lo pseudocodice indica anche l'alias che verrà utilizzato per ciascuna di esse: Summary 1 e Top In State. In questo modo, è possibile utilizzare questi alias nel pseudocodice della query esterna per chiarire la provenienza dei dati utilizzati. Se dallo pseudocodice non è chiaro come verrà codificata ogni sottoquery o, come in questo caso, se una sottoquery viene utilizzata per la query esterna, se una sottoquery è annidata all'interno di un'altra sottoquery, si può anche scrivere pseudocodice per le sottoquery. Ad esempio, lo pseudocodice della query TopInState è presentato in questa figura. Dato che questa sottoquery ha una sottoquery annidata nella sua clausola FROM, tale sottoquery è identificata in questo pseudocodice come Summary2. Il passo successivo della procedura consiste nel codificare e testare le subquery effettive per assicurarsi che funzionino nel modo desiderato. Ad esempio, il codice per le query Summaryl e Summary2 è mostrato in questa figura, insieme ai risultati di queste query e a quelli della query di TopInState. Una volta che si è sicuri che le sottoquery funzionano nel modo desiderato, è possibile codificare e testare la query finale. Se si segue la procedura descritta in questa figura, è più facile costruire query complesse che utilizzano le sottoquery. Prima di poter utilizzare questa procedura, ovviamente, è necessario avere una conoscenza approfondita del funzionamento delle subquery e cosa possono fare. Per questo motivo, è consigliabile sperimentare con le tecniche apprese in questa lezione prima di tentare di costruire una query complessa come quella mostrata qui.

Una procedura per costruire query complesse

Il problema da risolvere con la query nella figura

Pseudocodice per la query

    SELECT Summaryl.VendorState, Summaryl.VendorName, TopInState.SumOfInvoices
    FROM (Derived table returning VendorState, VendorName, SumOfInvoices)
            AS Summary1
        JOIN (Derived table returning VendorState, MAX (SumOfInvoices))
            AS TopInState
        ON Summaryl.VendorState = TopInState.VendorState AND
           Summary1.SumOfInvoices = TopInState.SumOfInvoices
    ORDER BY Summaryl.VendorState

Pseudocodice per la sottoquery TopInState

    SELECT Summary2.VendorState, MAX (Summary2.SumOfInvoices)
    FROM (Derived table returning VendorState, VendorName, SumOfInvoices)
        AS Summary2
    GROUP BY Summary2.VendorState

Il codice per le subquery Summary

    USE AP
    GO
    -- The Summary subquery
    SELECT v.VendorState, v.VendorName, 
        SUM(i.InvoiceTotal) AS SumOfInvoices
    FROM Invoices AS i JOIN Vendors AS v ON i.VendorID = v.VendorID
    GROUP BY v.VendorState, v.VendorName

Il codice per la subquery TopInState

    USE AP
    GO
    -- The TopInState subquery
    SELECT Summary2.VendorState, MAX(Summary2.SumOfInvoices) AS SumOfInvoices
    FROM
        (SELECT v.VendorState, v.VendorName, 
                SUM(i.InvoiceTotal) AS SumOfInvoices
         FROM Invoices AS i JOIN Vendors AS v ON i.VendorID = v.VendorID
         GROUP BY v.VendorState, v.VendorName) AS Summary2
    GROUP BY Summary2.VendorState

Come lavorare con le comuni espressioni di tabella

Un'espressione di tabella comune (CTE) è una funzione introdotta con SQL Server 2005 che consente di codificare un'espressione che definisce una tabella derivata. Le CTE possono essere utilizzate per semplificare query complesse che utilizzano sottoquery. Questo può semplificare la lettura e la manutenzione del codice. Inoltre, è possibile utilizzare una CTE per strutture annidate.

Come codificare un CTE

L'esempio che segue mostra come utilizzare una CTE per semplificare la complessa query presentata nell'esempio precedente. Per iniziare, l'istruzione per la query inizia con la parola chiave WITH per indicare che si sta per definire una CTE. Quindi, si specifica Summary come nome della prima tabella, seguita dalla parola chiave AS, seguita da una parentesi di apertura, seguita da un'istruzione SELECT che definisce la tabella, seguita da una parentesi di chiusura. In questa figura, ad esempio, l'istruzione restituisce lo stesso set di risultati delle sottoquery denominate Summaryl e Summary2 presentate nella figura precedente. Dopo aver definito la prima CTE, l'esempio prosegue definendo una seconda CTE denominata TopInState. Per iniziare, si codifica una virgola per separare le due CTE. Quindi, la query specifica TopInState come nome della seconda tabella, seguito dalla parola chiave AS, seguita da una parentesi iniziale, seguita da un'istruzione SELECT che definisce la tabella, seguita da un'istruzione che definisce la tabella, seguita da una parentesi di chiusura. In questo caso, l'istruzione SELECT si riferisce alla tabella Summary definita dalla prima CTE. Quando si codificano più CTE in questo modo, una CTE può fare riferimento a qualsiasi CTE nella stessa clausola WITH che sono state codificate prima di essa, ma non può fare riferimento a CTE codificate dopo di essa. Di conseguenza, l'istruzione non funzionerebbe se le due CTE fossero codificate in ordine inverso. Infine, l'istruzione SELECT codificata immediatamente dopo le due CTE utilizza entrambe le CTE come se fossero tabelle. A tale scopo, l'istruzione SELECT unisce le due tabelle, specifica le colonne da recuperare e l'ordine di ordinamento. Per evitare riferimenti ambigui, ogni colonna è qualificata dal nome della CTE. Se si confronta la nuova figura con la query dell'esempio precedente, si concorda sul fatto che il codice della nuova figura sia più facile da leggere. Questo è dovuto in parte al fatto che le tabelle definite dalle sottoquery non sono annidate all'interno dell'istruzione SELECT. Inoltre credo che sarete d'accordo che il codice della nuova figura è più facile da mantenere. Questo perché la query riduce la duplicazione del codice, codificando la query di riepilogo in un solo punto non in due. Quando si usa la sintassi mostrata qui per definire le CTE, è necessario fornire nomi distinti per tutte le colonne definite dalla SELECT, compresi i valori calcolati. In questo modo, è possibile che altre istruzioni facciano riferimento alle colonne dell'insieme dei risultati. Nella maggior parte dei casi, questo è tutto ciò che occorre sapere per poter lavorare con le CTE. Per ulteriori informazioni su come lavorare con le CTE, si può consultare il sito documentazione di SQL Server con la chiave di ricerca "WITH common_table_expression".

La sintassi di una CTE

    WITH cte_namel AS (query_definitionl)
    [, cte_name2 AS (query_definition2)]
    [ ... ]
    sql_statement

Due CTE e una query che li utilizza

    USE AP
    GO
    WITH Summary AS
    (
        SELECT VendorState, VendorName, SUM(InvoiceTotal) AS SumOfInvoices
        FROM Invoices i
            JOIN Vendors v
            ON i.VendorID = v.VendorID
        GROUP BY VendorState, VendorName
    ),
    
    TopInState AS
    (
        SELECT VendorState, MAX(SumOfInvoices) AS SumOfInvoices
        FROM Summary
        GROUP BY VendorState
    )
    
    SELECT s.VendorState, s.VendorName, t.SumOfInvoices
    FROM Summary s 
        JOIN TopInState t
            ON s.VendorState = t.VendorState AND
                s.SumOfInvoices = t.SumOfInvoices
    ORDER BY s.VendorState

Descrizione

Come codificare un CTE ricorsivo

Una query ricorsiva è una query in grado di scorrere un insieme di risultati e di eseguire un'elaborazione per restituire un insieme di risultati finale. Le query ricorsive sono comunemente utilizzate per restituire dati gerarchici, come ad esempio un organigramma in cui un elemento genitore può avere uno o più elementi figli e ogni elemento figlio può avere uno o più elementi figlio. Prima di SQL Server 2005, una query ricorsiva richiedeva l'utilizzo di tabelle derivate, cursori e logica per controllare il flusso dei passaggi ricorsivi. Con SQL Server 2005 e versioni successive, è possibile utilizzare una CTE ricorsiva per codificare in modo più semplice le query ricorsive. La Figura seguente mostra come fare. La parte superiore di questa figura mostra una tabella Dipendenti in cui la colonna ManagerID viene utilizzata per identificare il manager di ciascun dipendente. In questo caso, Cindy Smith è il manager di primo livello, poiché non ha un manager, Elmer Jones e Paulo Locario fanno capo a Cindy. Locario fanno capo a Cindy e così via. La CTE ricorsiva mostrata in questa figura restituisce ogni dipendente in base al suo livello nell'organigramma dell'azienda. A tale scopo, l'istruzione inizia definendo una CTE denominata EmployeesCTE. All'interno di questa CTE, due istruzioni SELECT sono unite dall'operatore UNION ALL. In questo caso, la prima istruzione SELECT utilizza l'operatore IS NULL per restituire la prima riga dell'insieme di risultati. Questa è nota come membro di ancoraggio della CTE ricorsiva. Quindi, la seconda istruzione SELECT crea un ciclo facendo riferimento a se stessa. In particolare, questa query unisce la tabella Employees alla tabella EmployeesCTE definita dalla CTE. Questa istruzione è nota come membro ricorsivo e percorre ogni riga della tabella Dipendenti. A ogni ciclo, aggiunge 1 alla colonna rank e aggiunge il risultato corrente al risultato finale. Ad esempio, nel primo ciclo, aggiunge Elmer Jones e Paulo Locario all'insieme dei risultati finali. Nel secondo ciclo, vengono aggiunti Ralph Simonian, Thomas Hardy, Olivia Hernandez e Rhea O'Leary al risultato finale. E così via. Quando si codifica un CTE ricorsivo, è necessario seguire alcune regole. Innanzitutto, è necessario fornire un nome per ogni colonna definita dalla CTE. Per farlo, è sufficiente assicurarsi di specificare un nome per ogni colonna nel membro di ancoraggio. In secondo luogo, le regole per la codifica di un'unione, apprese nella lezione 4. In particolare, il membro di ancoraggio e il membro ricorsivo devono avere lo stesso numero di colonne e le colonne devono avere dati compatibili. Nella maggior parte dei casi, questo è tutto ciò che è necessario sapere per poter lavorare con le CTE ricorsive. Tuttavia, l'obiettivo di questa figura è mostrare una semplice CTE ricorsiva per dare un'idea generale del suo funzionamento. Se necessario, è possibile codificare CTE ricorsivi molto più complessi. Ad esempio, è possibile codificare più membri ricorsivi. Per ulteriori informazioni sul lavoro con le CTE ricorsive, si può iniziare cercando "espressioni ricorsive di tabelle comuni" nella documentazione di SQL Server. Se si utilizza spesso una CTE ricorsiva per restituire dati gerarchici, è opportuno approfondire il tipo di dati hierarchyid, introdotto con SQL Server 2008. con SQL Server 2008. Questo tipo di dati facilita il lavoro con i dati gerarchici, come gli organigrammi. Per saperne di più su questo tipo di dati, è possibile cercare "hierarchyid (Transact-SQL)" nella documentazione di SQL Server.

Una CTE ricorsiva che restituisce dati gerarchici

    USE AP
    GO
    SELECT EmployeeID, LastName, FirstName, ManagerID
    FROM Employees

Una CTE ricorsiva che restituisce dati gerarchici

    USE AP
    GO
    WITH EmployeesCTE AS
    (
            -- Anchor member
            SELECT EmployeeID, 
                FirstName + ' ' + LastName As EmployeeName, 
                1 As Rank
            FROM Employees
            WHERE ManagerID IS NULL
        UNION ALL
            -- Recursive member
            SELECT e.EmployeeID, 
                FirstName + ' ' + LastName, 
                Rank + 1
            FROM Employees e
                JOIN EmployeesCTE e2
                    ON e.ManagerID = e2.EmployeeID
    )
    SELECT *
    FROM EmployeesCTE
    ORDER BY Rank, EmployeeID

Descrizione

Prospettiva

Come hai visto in questa lezione, le sottoquery forniscono uno strumento potente per risolvere problemi difficili. Prima di usare una sottoquery, tuttavia, ricorda che una sottoquery può spesso essere riformulata più chiaramente usando un join. Inoltre, una query con un join spesso viene eseguita più rapidamente di una query con una sottoquery. Per questo motivo, in genere userai una sottoquery solo quando non può essere riformulata come un join o quando rende la query più facile da capire senza rallentarla in modo significativo. Se ti ritrovi a codificare le stesse sottoquery più e più volte, dovresti considerare la creazione di una vista per quella sottoquery come descritto nella lezione 13. Questo ti aiuterà a sviluppare le query più rapidamente poiché puoi usare la vista anziché codificare di nuovo la sottoquery. Inoltre, poiché le viste vengono eseguite più rapidamente delle sottoquery, questo potrebbe migliorare le prestazioni delle tue query.

Esercizi

Se non diversamente indicato, utilizzare la sintassi di unione esplicita

    USE AP
    GO
    SELECT VendorName
    FROM Vendors
    WHERE VendorID IN
        (SELECT VendorID FROM Invoices)
    ORDER BY VendorName
                          

    USE AP
    GO
    SELECT InvoiceNumber, InvoiceTotal
    FROM Invoices
    WHERE PaymentTotal >
        (SELECT AVG(PaymentTotal)
        FROM Invoices
        WHERE PaymentTotal <> 0)
                          

    USE AP
    GO
    SELECT InvoiceNumber, InvoiceTotal
    FROM Invoices
    WHERE PaymentTotal > ALL
        (SELECT TOP 50 PERCENT PaymentTotal
        FROM Invoices
        WHERE PaymentTotal <> 0
        ORDER BY PaymentTotal)
                          

    USE AP
    GO
    SELECT AccountNo, AccountDescription
    FROM GLAccounts gl
    WHERE NOT EXISTS
        (SELECT *
        FROM InvoiceLineItems li
        WHERE li.AccountNo = gl.AccountNo)
    ORDER BY AccountNo
                          

    USE AP
    GO
    SELECT VendorName, i.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
    FROM Vendors v
    JOIN Invoices i
        ON v.VendorID = i.VendorID
    JOIN InvoiceLineItems li
        ON i.InvoiceID = li.InvoiceID
    WHERE i.InvoiceID IN
        (SELECT InvoiceID
            FROM InvoiceLineItems
            WHERE InvoiceSequence > 1)
    ORDER BY VendorName, i.InvoiceID, InvoiceSequence
                          

    USE AP
    GO
    SELECT SUM(InvoiceMax) AS SumOfMaximums
    FROM (SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax
        FROM Invoices
        WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0
        GROUP BY VendorID) AS MaxInvoice
                          

    USE AP
    GO
    SELECT VendorName, VendorCity, VendorState
    FROM Vendors
    WHERE VendorState + VendorCity NOT IN 
        (SELECT VendorState + VendorCity
        FROM Vendors
        GROUP BY VendorState + VendorCity
        HAVING COUNT(*) > 1)
    ORDER BY VendorState, VendorCity
                          
    -- codice alternativo
    USE AP
    GO
    SELECT VendorName, VendorCity, VendorState
    FROM Vendors AS Vendors_Main
    WHERE VendorCity + VendorState NOT IN
        (SELECT VendorCity + VendorState
        FROM Vendors AS Vendors_Sub
        WHERE Vendors_Sub.VendorID <> Vendors_Main.VendorID)
    ORDER BY VendorState, VendorCity
                        

    USE AP
    GO
    SELECT VendorName, InvoiceNumber AS FirstInv,
        InvoiceDate, InvoiceTotal
    FROM Invoices i
        JOIN Vendors v
            ON i.VendorID = v.VendorID
    WHERE InvoiceDate =
        (SELECT MIN(InvoiceDate)
        FROM Invoices I_Sub
        WHERE I_Sub.VendorID = i.VendorID)
    ORDER BY VendorName
                          
    -- codice alternativo
    USE AP
    GO
    SELECT VendorName, InvoiceNumber AS FirstInv,
        FirstInvoice.InvoiceDate, InvoiceTotal
    FROM Invoices JOIN
        (SELECT VendorID, MIN(InvoiceDate) AS InvoiceDate
        FROM Invoices
        GROUP BY VendorID) AS FirstInvoice
    ON (Invoices.VendorID = FirstInvoice.VendorID AND
        Invoices.InvoiceDate = FirstInvoice.InvoiceDate)
    JOIN Vendors
    ON Invoices.VendorID = Vendors.VendorID
    ORDER BY VendorName
                        

    USE AP
    GO
    WITH MaxInvoice AS
    (
        SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax
        FROM Invoices
        WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0
        GROUP BY VendorID
    )
    SELECT SUM(InvoiceMax) AS SumOfMaximums
    FROM MaxInvoice
Per visualizzare le soluzioni degli esercizi devi inserire la password fornita dal docente