Esercitazione per test SQL

Com'è di solito il test vero

Non esiste un unico test SQL. Ogni datore di lavoro sceglie una piattaforma di test oppure crea il proprio test, decidendo quante domande ci sono, quanto dura e quale database viene usato. L'annuncio di lavoro o l'invito al test sono la guida migliore per capire cosa aspettarsi. Gli esempi pubblici mostrano la varietà:

• I test di competenze SQL di HackerRank. HackerRank indica che il suo test di competenze SQL (Basic) prevede 2 domande in 30 minuti e tratta argomenti come le query semplici (HackerRank); il test SQL (Intermediate) prevede invece 2 domande in 35 minuti e tratta argomenti come le join complesse (HackerRank). I datori di lavoro che creano i propri test su HackerRank possono aggiungere domande sui database e scegliere quali database i candidati possono usare (assistenza HackerRank).
• Un esercizio di scrittura di query. TestGorilla descrive il suo test di programmazione SQLite come un esercizio di 30 minuti in cui si scrive una query per uno scenario realistico su un database di media complessità, che tratta join e filtri, calcoli semplici, ordinamento, arrotondamento ed esclusione delle voci irrilevanti (TestGorilla).
• Un breve test di conoscenze. TestGorilla indica che il suo test MySQL prevede 15 domande in 10 minuti, selezionate da un archivio di 101 (TestGorilla).

Argomenti comunemente presenti nei test SQL:

• Filtri e ordinamento: SELECT, WHERE, ORDER BY e condizioni con AND, OR, IN e BETWEEN.
• Join: join inner e left e cosa accade alle righe senza corrispondenze.
• Raggruppamento: GROUP BY con COUNT, SUM, AVG, MIN e MAX, e HAVING per filtrare i gruppi.
• Valori NULL: come si comportano i valori mancanti nei confronti, nei conteggi e nelle medie.
• Sottoquery: una query all'interno di un'altra, con IN, EXISTS o un singolo valore.
• Scrivere una query a partire da una richiesta in linguaggio semplice, spesso valutando se restituisce esattamente le righe attese.

Cosa comprendono le nostre simulazioni. Ogni simulazione completa prevede 18 domande in 45 minuti, suddivise in quattro sezioni: concetti SQL (5), risultati restituiti dalla query (6), correzione di una query (4) e scelta della query corretta (3). È possibile tornare indietro e contrassegnare le domande. Usiamo SQL standard e portabile, quindi le query hanno lo stesso significato in PostgreSQL, MySQL, SQL Server, SQLite e nella maggior parte degli altri database. Le nostre simulazioni permettono di esercitarsi a leggere, seguire e scegliere le query; non eseguono le query scritte dall'utente. Molti test SQL reali chiedono di scrivere una query che viene verificata rispetto ai risultati attesi. Le nostre simulazioni aiutano a sviluppare le capacità di lettura e di analisi necessarie, ma è utile anche scrivere query autonomamente in un database gratuito come SQLite.

Tipi di domande con esempi svolti

Un esempio svolto per ogni sezione, con l'errore in cui cadono più spesso le persone.

1. Concetti di SQL. Qual è la differenza tra COUNT() e COUNT(email)? COUNT() conta tutte le righe; COUNT(email) conta solo le righe in cui email non è NULL. Attenzione: pensare che ogni conteggio includa tutte le righe. La maggior parte delle funzioni di aggregazione ignora i valori NULL.

2. Cosa restituisce la query. La tabella scores contiene i valori 8, 5, NULL e 8. Cosa restituisce SELECT COUNT(DISTINCT score)? I valori distinti diversi da NULL sono 8 e 5, quindi la risposta è 2. Attenzione: contare NULL come un valore oppure contare due volte l'8 ripetuto. Scrivere la tabella e cancellare le righe man mano che la query le elaborerebbe.

3. Correggere una query. Una query contiene WHERE city = 'Austin' OR city = 'Dallas' AND active = 1 e restituisce clienti inattivi di Austin. AND viene valutato prima di OR, quindi la condizione significa in realtà Austin oppure Dallas attiva. La correzione consiste nell'aggiungere parentesi: WHERE (city = 'Austin' OR city = 'Dallas') AND active = 1. Attenzione: leggere la condizione da sinistra a destra.

4. Scegliere la query giusta. Occorre trovare i cinque ordini più recenti. Qual è la query corretta: ORDER BY order_date DESC con un limite di 5 oppure ORDER BY order_date con un limite di 5? La prima. Senza DESC, l'ordinamento parte dagli ordini più vecchi e si ottengono quindi i cinque più vecchi. Attenzione: scegliere la query che sembra corretta senza controllare la direzione dell'ordinamento. (La sintassi per indicare il limite varia da un database all'altro, ad esempio LIMIT 5 o FETCH FIRST 5 ROWS ONLY, quindi verificare quale viene usata nel test.)

5. Join con condizioni. Una left join seguita da una condizione WHERE sulla colonna della tabella a destra si trasforma di fatto in una inner join, perché le righe senza corrispondenza hanno NULL in quella colonna e vengono escluse dalla condizione. Attenzione: non accorgersi di quali righe scompaiono. Chiedersi: "Cosa succede a una riga senza corrispondenza?"

Come prepararsi

1. Leggi prima l'invito. Scopri quale piattaforma userai, quanto tempo avrai, se dovrai scrivere query o rispondere a domande a scelta multipla e quale database verrà utilizzato. I formati variano troppo per fare supposizioni.
2. Impara l'ordine di esecuzione di una query. FROM e JOIN, poi WHERE, GROUP BY, HAVING, SELECT e ORDER BY. La maggior parte delle domande del tipo "perché non funziona?" diventa chiara quando sai quale passaggio vede quali dati.
3. Segui a mano l'elaborazione di tabelle piccole. Scrivi tre o quattro righe, poi applica ogni clausola nell'ordine. È il modo più rapido per verificare la risposta e individuare i tranelli legati a NULL e ai duplicati.
4. Considera NULL un argomento a sé. Fai pratica con IS NULL, COUNT(column) rispetto a COUNT(*), AVG che ignora i valori NULL, NOT IN con un valore NULL nell'elenco e il modo in cui una left join riempie le colonne senza corrispondenze.
5. Scrivi anche query vere. Le nostre simulazioni sviluppano la capacità di leggere e seguire le query; scrivile anche in un database gratuito come SQLite, così digitare entro un limite di tempo ti sembrerà normale. Alcuni test verificano la query su molti casi, come gli oltre 10 casi di test che TestGorilla indica per il suo test SQLite (TestGorilla).
6. Controlla il risultato, non solo la sintassi. Una query può essere eseguita correttamente e restituire comunque righe sbagliate. Prima di inviarla, chiediti cosa succede quando non ci sono corrispondenze, ci sono duplicati o sono presenti valori NULL.
7. Allenati al ritmo effettivo. Un breve test di conoscenze come il test MySQL di TestGorilla, 15 domande in 10 minuti, lascia ben meno di un minuto per domanda (TestGorilla). Svolgi simulazioni complete a tempo, così il giorno del test il ritmo ti sembrerà normale.

Come funziona di solito la valutazione

Il punteggio dipende dal datore di lavoro e dalla piattaforma. Non esiste un punteggio SQL standard né una soglia di superamento universale. Ogni datore di lavoro decide quale risultato è considerato positivo. Ecco due esempi pubblici di come vengono valutate le domande sulle query:

• Domande sui database di HackerRank: HackerRank afferma che le valuta automaticamente confrontando il risultato della query con l'output previsto. Una corrispondenza ottiene il punteggio pieno, mentre qualsiasi altro risultato ottiene zero, a meno che il datore di lavoro non esamini la risposta manualmente e assegni un punteggio parziale. Il datore di lavoro può anche scegliere di ignorare l'ordine delle righe (assistenza HackerRank).
• Test SQLite di TestGorilla: TestGorilla afferma che i candidati vengono classificati usando più di 10 casi di test che verificano i casi limite della soluzione (TestGorilla).

Poiché spesso i risultati vengono controllati riga per riga, una query quasi corretta, per esempio una che esclude un cliente senza ordini, può ottenere lo stesso punteggio di una risposta mancante.

Il nostro punteggio di esercitazione è il numero di domande originali a cui ha risposto correttamente, con una suddivisione per sezione, così può capire su cosa concentrarsi. È un punteggio di esercitazione: non corrisponde al punteggio ottenuto in un test di un datore di lavoro o di una piattaforma e non permette di prevedere se supererà il test.

Errori comuni

• Confrontare con NULL usando =. Usa IS NULL e IS NOT NULL. Qualsiasi confronto = NULL ha un risultato sconosciuto, quindi la riga viene esclusa.
• Filtrare un left join in WHERE. Un filtro sulle colonne della tabella di destra elimina le righe senza corrispondenze. Inserisci quel filtro nella clausola ON se vuoi mantenerle.
• Usare WHERE per un totale. Le funzioni aggregate vanno in HAVING, dopo GROUP BY.
• Selezionare una colonna che non hai raggruppato. In SQL standard, ogni colonna selezionata deve essere raggruppata o aggregata. Alcuni database eseguono comunque la query e scelgono un valore al posto tuo, nascondendo l'errore.
• Dimenticare i duplicati. Un join può ripetere le righe, aumentando SUM e COUNT. Chiediti se ogni riga di un lato può corrispondere a più di una riga dell'altro.
• Affidarsi alle scorciatoie di un solo database. Alcune funzioni e la sintassi per limitare i risultati variano da un database all'altro. Impara la forma standard e verifica quale database usa il test.