Esercitazioni SQL per principianti: Esercizi AdventureWorks

Affinate le vostre abilità SQL con esercizi pratici utilizzando il database di esempio di AdventureWorks. Esercitatevi con le funzioni SQL essenziali utilizzando scenari reali.

Esercitarsi con l'SQL è importante se si vuole migliorare il suo utilizzo. Questo articolo contiene 20 esercizi per principianti che utilizzano il database di esempio AdventureWorks di Microsoft. Questo database è stato progettato per mostrare il funzionamento di SQL Server. Rappresenta una società fittizia di produzione di biciclette chiamata AdventureWorks Cycles e comprende cinque schemi: Risorse umane, Personale, Produzione, Acquisti e Vendite. Questo lo rende ideale per l'apprendimento e la pratica di SQL.

Il database AdventureWorks copre vari scenari aziendali, come produzione, vendite, acquisti, gestione dei prodotti, gestione dei contatti e risorse umane. Questo lo rende una risorsa eccellente per l'apprendimento e la pratica dell'SQL, perché offre un'ampia gamma di dati e processi aziendali con cui lavorare. Facendo pratica con questo database, si può acquisire esperienza pratica con dati e scenari reali; questo vi aiuterà a capire come scrivere query efficienti e risolvere efficacemente i problemi di dati.

Se volete provare altri database di esempio, consultate il nostro elenco delle fonti di dati più interessanti per esercitarsi con SQL. Tuttavia, la creazione di un ambiente di pratica SQL può richiedere tempo. Per una pratica rapida, provate la nostra tracciaPratica su SQL o i nostri database di pratica SQL. Inoltre, ogni mese pubblichiamo un corso SQL gratuito per aiutarvi a continuare a imparare.

Ora iniziamo a fare pratica di SQL con gli esercizi di AdventureWorks!

Esame del database AdventureWorks

Il database AdventureWorks comprende cinque schemi di database (cioè strutture di database utilizzate per raggruppare le tabelle di dati), tra cui HumanResources, Person, Production, Purchasing e Sales. Nei prossimi esercizi utilizzeremo lo schema HumanResources.

Iniziamo ad analizzare lo schema:

Innanzitutto, rivediamo i concetti di chiave primaria e di chiave esterna:

• La chiave primaria (PK) è una colonna (o un insieme di colonne) che identifica in modo univoco ogni riga di una tabella. Ad esempio, la colonna BusinessEntityID è la chiave primaria della tabella Employee perché a ogni dipendente viene assegnato un numero ID univoco.
• La chiave esterna (FK) è una colonna (o un insieme di colonne) che collega due tabelle. Si noti che la chiave esterna di una tabella è la chiave primaria di un'altra tabella: in base a ciò, le due tabelle sono collegate. Ad esempio, la colonna BusinessEntityID è la chiave primaria della tabella Employee e anche una chiave esterna della tabella JobCandidate della tabella. Questo collega le due tabelle e consente alla tabella JobCandidate di fare riferimento alle righe della tabella Employee

Analizziamo ora le tabelle dello schema.

La tabella Employee memorizza le informazioni sui dipendenti ed è la tabella principale di questo schema. È collegata alle seguenti tabelle:

• La tabella JobCandidate memorizza i curriculum dei candidati al lavoro. La colonna BusinessEntityID è una PK nella tabella Employee e una FK nella tabella JobCandidate La PK della tabella JobCandidate della tabella è la colonna JobCandidateID.
• La tabella EmployeePayHistory memorizza lo storico delle retribuzioni dei dipendenti. La colonna BusinessEntityID è una PK nella tabella Employee e una FK nella tabella EmployeePayHistory Si noti che la colonna BusinessEntityID nella tabella EmployeePayHistory tabella è contemporaneamente una FK e una parte della PK. La PK della tabella EmployeePayHistory della tabella comprende sia la colonna BusinessEntityID che la colonna RateChangeDate. Si tratta di una chiave primaria composita.
• La tabella EmployeeDepartmentHistory memorizza la storia dei reparti dei dipendenti. La colonna BusinessEntityID è una PK nella tabella Employee e una FK nella tabella EmployeeDepartmentHistory Si noti che la colonna BusinessEntityID nella tabella EmployeeDepartmentHistory è contemporaneamente una FK e una parte della PK. La PK della tabella EmployeeDepartmentHistory della tabella comprende le colonne BusinessEntityID, DepartmentID, ShiftID e StartDate. Si tratta di un'altra chiave primaria composita.

La tabella EmployeeDepartmentHistory memorizza informazioni sui dipendenti e sui loro reparti nel tempo e può essere considerata un'altra tabella principale di questo schema. È collegata alle seguenti tabelle:

• La tabella Shift memorizza le informazioni sui turni disponibili. La colonna ShiftID è una PK nella tabella Shift e una FK nella tabella EmployeeDepartmentHistory Si noti che la colonna ShiftID è sia un FK che una parte del PK della tabella. EmployeeDepartmentHistory tabella.
• La tabella Department memorizza informazioni sui dipartimenti. La colonna DepartmentID è una PK nella tabella Department e una FK nella tabella EmployeeDepartmentHistory Si noti che la colonna DepartmentID è sia una FK che una parte della PK della tabella. EmployeeDepartmentHistory della tabella.

Ora siamo pronti per iniziare gli esercizi di AdventureWorks.

Esercizi AdventureWorks per principianti

Nel corso di questo articolo affronteremo tutte le nozioni di base di SQL (e ci diletteremo con alcune funzioni avanzate):

• Query a tabella singola per recuperare e filtrare i dati.
• Query su più tabelle che utilizzano le JOIN per combinare i dati di due o più tabelle.
• Raggruppamento e aggregazione dei dati per eseguire operazioni matematiche sui dati selezionati.
• Altre funzioni SQL importanti come le subquery, UNION e INTERSECT e le espressioni di tabella comuni (CTE).

Parte 1: Query a tabella singola

In questa parte verranno recuperati e filtrati i dati.

Nota: per scrivere query corrette, è necessario prima familiarizzare con i dati memorizzati nelle tabelle.

Esercizio 1: Selezionare i titoli di lavoro di tutti i dipendenti maschi single.

Esercizio: Selezionare il titolo di lavoro di tutti i dipendenti maschi non sposati.

Soluzione:

SELECT JobTitle
FROM HumanResources.Employee
WHERE Gender = 'M'
AND MaritalStatus != 'M';

Spiegazione: Selezioniamo la colonna JobTitle dalla Employee dalla tabella. In questo caso la sintassi SQL può essere letta esattamente come un semplice inglese.

Le condizioni di filtraggio sono inserite nella clausola WHERE:

• Vogliamo selezionare solo i dipendenti maschi, quindi imponiamo una condizione sulla colonna Gender: Gender = 'M'.
• Vogliamo selezionare solo i dipendenti non sposati, quindi imponiamo un'altra condizione sulla colonna MaritalStatus: MaritalStatus != 'M'.

Poiché entrambe le condizioni devono essere applicate contemporaneamente, utilizziamo l'operatore AND per combinarle.

Esercizio 2: Selezionare i dipendenti il cui stipendio è pari o superiore a 50

Esercizio: Selezionare le colonne BusinessEntityID, Rate e RateChangeDate per tutti i dipendenti la cui retribuzione è sempre stata di 50 o più.

Soluzione:

SELECT BusinessEntityID, Rate, RateChangeDate
FROM HumanResources.EmployeePayHistory
WHERE Rate >= 50;

Spiegazione: Selezioniamo tre colonne - BusinessEntityID, Rate e RateChangeDate - dalla tabella. EmployeePayHistory tabella.

Quindi si inserisce una condizione di filtro nella clausola WHERE per recuperare solo i dipendenti la cui retribuzione è sempre stata maggiore o uguale a 50: Rate >= 50.

Esercizio 3: Selezionare tutti i dipendenti che si sono uniti a nuovi reparti nel 2008

Esercizio: Selezionare i campi BusinessEntityID, DepartmentID e StartDate per ogni dipendente che ha iniziato a lavorare in un qualsiasi reparto nel 2008.

Soluzione:

SELECT BusinessEntityID, DepartmentID, StartDate
FROM HumanResources.EmployeeDepartmentHistory
WHERE StartDate BETWEEN '2008-01-01' AND '2008-12-31';

Spiegazione: Si selezionano gli ID dei dipendenti (BusinessEntityID), gli ID dei reparti (DepartmentID) e la data in cui il dipendente è entrato a far parte di quel reparto (StartDate) dalla tabella EmployeeDepartmentHistory tabella.

Poiché vogliamo elencare solo i dipendenti che si sono uniti a nuovi reparti nel 2008, imponiamo una condizione sulla colonna StartDate: StartDate BETWEEN '2008-01-01' AND '2008-12-31'.

Questa condizione è autoesplicativa, poiché è comprensibile in parole povere. Vogliamo che il valore StartDate sia compreso tra il 1° gennaio 2008 e il 31 dicembre 2008, in modo da coprire tutti i giorni del 2008.

Esercizio 4: Selezionare i reparti il cui nome corrisponde allo schema

Esercizio: Selezionare gli ID dei reparti, i nomi e i nomi dei gruppi per tutti i reparti il cui nome inizia con "Prod" OPPURE per tutti i reparti il cui nome del gruppo termina con "ring".

Soluzione:

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
WHERE Name LIKE 'Prod%'
OR GroupName LIKE '%ring';

Spiegazione: Selezioniamo le colonne DepartmentID, Name e GroupName dalla tabella. Department dalla tabella.

Poi, forniamo le condizioni della clausola WHERE:

• Vogliamo selezionare i reparti il cui nome inizia con 'Prod', quindi imponiamo una condizione sulla colonna Name: Name LIKE 'Prod%'.
• Vogliamo anche selezionare i reparti il cui nome del gruppo termina con 'ring', quindi imponiamo una condizione sulla colonna GroupName: GroupName LIKE '%ring'.

La parola chiave LIKE consente di definire lo schema a cui deve corrispondere il valore della colonna. Ad esempio, vogliamo che la colonna Name inizi con 'Prod', quindi lo schema 'Prod%'; % rappresenta una qualsiasi sequenza di caratteri.

Poiché vogliamo trovare tutti i record in cui almeno una delle condizioni è vera, usiamo la parola chiave OR.

Esercizio 5: Selezionare i reparti che appartengono a determinati gruppi

Esercizio: Selezionare i nomi dei reparti che appartengono al gruppo "Ricerca e sviluppo" o al gruppo "Produzione".

Soluzione:

SELECT Name
FROM HumanResources.Department
WHERE GroupName IN ('Research and Development', 'Manufacturing');

Spiegazione: Selezioniamo la colonna Nome dalla Department tabella.

Poiché vogliamo elencare i reparti che appartengono a gruppi definiti, utilizziamo la parola chiave IN nella condizione della clausola WHERE: GroupName IN ('Research and Development', 'Manufacturing').

In questo modo si ottiene l'output di tutti i reparti che appartengono ai gruppi elencati nella parola chiave IN.

Parte 2: Query a tabelle multiple

In questa parte, utilizzeremo JOINs per combinare i dati di più tabelle.

Nota: Per scrivere query corrette, è necessario prima familiarizzare con i dati memorizzati nelle tabelle pertinenti.

Esercizio 6: Selezionare i dipendenti e i loro reparti

Esercizio: Selezionare gli ID dei dipendenti con tutti i nomi dei reparti in cui hanno lavorato.

Soluzione:

SELECT edh.BusinessEntityID, d.Name
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID;

Spiegazione: Selezioniamo la colonna BusinessEntityID dalla tabella EmployeeDepartmentHistory e la colonna Name dalla tabella Department tabella.

Uniamo queste due tabelle usando la clausola JOIN sulla loro colonna comune, DepartmentID.

Esercizio 7: Selezionare i titoli di lavoro dei dipendenti di sesso femminile e le date di cambio di reparto

Esercizio: Selezionare l'ID del dipendente e il titolo del lavoro insieme alle date in cui il dipendente ha cambiato reparto (StartDate) per tutti i dipendenti di sesso femminile.

Soluzione:

SELECT e.BusinessEntityID, e.JobTitle, edh.StartDate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE e.Gender = 'F';

Spiegazione: Si selezionano le colonne BusinessEntityID e JobTitle dalla tabella e la colonna dalla tabella. Employee e la colonna StartDate dalla tabella EmployeeDepartmentHistory tabella.

Utilizziamo la clausola JOIN per unire le tabelle sulla loro colonna comune, BusinessEntityID.

Poiché vogliamo elencare queste informazioni solo per i dipendenti di sesso femminile, imponiamo una condizione sulla colonna Gender: e.Gender = 'F'.

Esercizio 8: Selezionare i titoli di lavoro per reparto

Esercizio: Selezionare i titoli delle mansioni e i corrispondenti nomi dei reparti per trovare tutti i titoli delle mansioni che sono stati utilizzati in ciascun reparto. Non includere multipli dello stesso titolo di lavoro.

Soluzione:

SELECT DISTINCT e.JobTitle, d.Name
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID;

Spiegazione: Selezioniamo la colonna JobTitle dalla tabella Employee e la colonna Name dalla tabella Department dalla tabella.

Per selezionare titoli di lavoro distinti (cioè senza duplicati), si utilizza la parola chiave DISTINCT prima dei nomi delle colonne.

È necessario unire la tabella Employee con la tabella EmployeeDepartmentHistory sulla loro colonna comune, BusinessEntityID. Quindi uniamo la tabella EmployeeDepartmentHistorycon la tabella Department tabella sulla loro colonna comune, DepartmentID.

Esercizio 9: Selezionare i dipendenti con i loro reparti e turni di lavoro

Esercizio: Selezionare i nomi distinti dei reparti e dei turni (senza coppie duplicate) in cui lavorano i dipendenti di ciascun reparto. Rinominare il nome del reparto in DepartmentName e il nome del turno in ShiftName.

Soluzione:

SELECT DISTINCT d.Name AS DepartmentName, s.Name AS ShiftName
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
JOIN HumanResources.Shift AS s
ON edh.ShiftID = s.ShiftID;

Spiegazione: Selezioniamo la colonna Nome dalla tabella Department e la colonna Name dalla tabella Shift dalla tabella, fornendo loro dei nomi alias con la parola chiave AS. Utilizziamo DISTINCT per selezionare coppie distinte.

Per selezionare i nomi dei reparti e dei turni, dobbiamo unire le tre tabelle:

• Uniamo la tabella EmployeeDepartmentHistory con la tabella Department sulla tabella DepartmentID
• Uniamo la tabella EmployeeDepartmentHistory con la tabella Shift sulla tabella ShiftID

Ciascuna delle clausole JOIN ha una clausola ON che definisce le colonne comuni su cui viene eseguita la join.

Esercizio 10: Selezionare i dipendenti assunti dopo il 2010 con i relativi reparti e turni di lavoro

Esercizio: Selezionare gli ID dei dipendenti, i nomi dei reparti e dei turni. Includere solo i dipendenti assunti dopo il 2010-01-01 e che lavorano nei reparti dei gruppi Produzione e Assicurazione qualità.

Soluzione:

SELECT e.BusinessEntityID, d.Name, s.Name
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
JOIN HumanResources.Shift AS s
ON edh.ShiftID = s.ShiftID
WHERE e.HireDate > '2010-01-01'
AND d.GroupName IN ('Manufacturing', 'Quality Assurance');

Spiegazione: Si seleziona la colonna BusinessEntityID dalla tabella Employees dalla tabella, la colonna Name dalla tabella Department e la colonna Name dalla tabella Shift.

Per selezionare gli ID dei dipendenti insieme ai nomi dei reparti e dei turni, è necessario utilizzare tre join:

• Uniamo la tabella Employee con la tabella EmployeeDepartmentHistory sulla colonna BusinessEntityID.
• Uniamo la tabella EmployeeDepartmentHistory con la tabella Department tabella sulla colonna DepartmentID
• Uniamo la tabella EmployeeDepartmentHistory con la tabella Shift sulla colonna ShiftID.

Quindi forniamo le condizioni della clausola WHERE come segue:

• Vogliamo elencare tutti i dipendenti assunti dopo il 1° gennaio 2010, quindi imponiamo una condizione sulla colonna HireDate: HireDate > '2010-01-01'.
• Vogliamo elencare solo i dipendenti che appartengono a determinati gruppi di reparti, quindi utilizziamo la parola chiave IN per creare questa condizione: GroupName IN ('Manufacturing', 'Quality Assurance').

Parte 3: Raggruppamento e aggregazione dei dati

In questa parte raggrupperemo e aggregheremo i dati per poter eseguire operazioni matematiche sui dati selezionati.

Nota: Per scrivere query corrette, è necessario prima familiarizzare con i dati memorizzati nelle tabelle pertinenti.

Esercizio 11: selezionare le ore di assenza per malattia più alte e più basse

Esercizio: Selezionare il numero minimo e massimo di ore di malattia prese dai dipendenti.

Soluzione:

SELECT MIN(SickLeaveHours) AS MinSickLeaveHours,
       MAX(SickLeaveHours) AS MaxSickLeaveHours
FROM HumanResources.Employee;

Spiegazione: Utilizziamo le funzioni aggregate MIN() e MAX() - fornendo come argomento la colonna SickLeaveHours della tabella - per selezionare il numero minimo e massimo di ore di assenza per malattia dei dipendenti. Employee per selezionare il numero minimo e massimo di ore di assenza per malattia dei dipendenti.

Esercizio 12: Selezionare il numero medio di ore di ferie per qualifica

Esercizio: Selezionare i titoli di lavoro e il numero medio di ore di ferie per titolo di lavoro.

Soluzione:

SELECT JobTitle, AVG(VacationHours) AS AvgVacationHours
FROM HumanResources.Employee
GROUP BY JobTitle;

Spiegazione: Selezioniamo la colonna JobTitle dalla Employee dalla tabella.

Utilizziamo la funzione aggregata AVG() per ottenere il numero medio di ore di ferie per ciascun titolo di lavoro. Vogliamo avere gruppi di dati basati sui valori distinti della colonna JobTitle; questo richiede l'uso della clausola GROUP BY con la colonna JobTitle come argomento.

Esercizio 13: Selezionare il conteggio dei dipendenti in base al loro sesso

Esercizio: Selezionare il sesso dei dipendenti e il conteggio dei dipendenti di ciascun sesso.

Soluzione:

SELECT Gender, COUNT(*) AS Count
FROM HumanResources.Employee
GROUP BY Gender;

Spiegazione: Si seleziona la colonna Gender dalla Employee dalla tabella.

Utilizziamo la funzione aggregata COUNT() per ottenere il conteggio dei dipendenti per ogni genere. Ciò richiede l'uso della clausola GROUP BY con la colonna Gender come argomento; vogliamo avere gruppi di dati basati sui valori distinti della colonna Gender.

Esercizio 14: Selezionare il numero di reparti in ogni gruppo.

Esercizio: Trovare il numero di reparti in ogni gruppo di reparti. Elencare solo i nomi dei gruppi di reparto che hanno più di due reparti.

Soluzione:

SELECT GroupName, COUNT(*) AS DepartmentsCount
FROM HumanResources.Department
GROUP BY GroupName
HAVING COUNT(*) > 2;

Spiegazione: Si seleziona la colonna GroupName dalla tabella Reparti.

Si utilizza la funzione aggregata COUNT() per ottenere il conteggio dei reparti in ciascun gruppo di reparti. Ciò richiede l'uso della clausola GROUP BY con la colonna GroupName come argomento.

Per imporre una condizione alla funzione aggregata, utilizziamo la clausola HAVING dopo la clausola GROUP BY: HAVING COUNT(*) > 2.

Esercizio 15: selezionare la somma delle ore di assenza per malattia per ogni reparto

Esercizio: Selezionare i nomi dei reparti e la somma delle ore di assenza per malattia dei dipendenti che lavorano attualmente in ciascun reparto. Rinominare questa colonna in SumSickLeaveHours.

Soluzione:

SELECT d.Name, SUM(e.SickLeaveHours) AS SumSickLeaveHours
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
WHERE edh.EndDate IS NULL
GROUP BY d.Name;

Spiegazione: Selezioniamo la colonna Nome dal file Department.

Utilizziamo la funzione aggregata SUM() per ottenere la somma delle ore di assenza per malattia utilizzate dai dipendenti per ogni reparto. Ciò richiede l'uso della clausola GROUP BY con la colonna Nome dalla tabella Department come argomento.

Dobbiamo unire la tabella Employee con la tabella EmployeeDepartmentHistory sulla colonna BusinessEntityID. Poi uniamo la tabella EmployeeDepartmentHistorycon la tabella Department tabella sulla colonna DepartmentID.

Poiché consideriamo solo i dipendenti che attualmente lavorano in un qualsiasi reparto, imponiamo la condizione che la colonna EndDate della tabella sia . EmployeeDepartmentHistory deve essere NULL.

Parte 4: Altre funzioni SQL

In questa parte tratteremo le sottoquery (cioè le query annidate all'interno delle query), gli operatori come UNION e INTERSECT, e le espressioni di tabella comuni (CTE).

Nota: per scrivere query corrette, è necessario prima familiarizzare con i dati memorizzati nelle tabelle pertinenti.

Esercizio 16: Selezionare i dipendenti con la loro retribuzione attuale

Esercizio: Selezionare gli ID dei dipendenti e le loro retribuzioni attuali.

Soluzione:

SELECT e.BusinessEntityID, e.Rate AS CurrentPayRate
FROM HumanResources.EmployeePayHistory AS e
WHERE e.RateChangeDate = (
			SELECT MAX(e2.RateChangeDate)
			FROM HumanResources.EmployeePayHistory AS e2
			WHERE e2.BusinessEntityID = e.BusinessEntityID
		);

Spiegazione: Si selezionano le colonne BusinessEntityID e Tasso dalla tabella. EmployeePayHistory tabella.

Poiché la tabella EmployeePayHistory memorizza la cronologia delle tariffe per ciascun dipendente, è necessario imporre una condizione sul valore della colonna RateChangeDate che deve essere uguale alla data più recente di modifica della tariffa del dipendente. Per farlo, si definisce una sottoquery nella clausola WHERE che seleziona la data più recente di modifica della tariffa salariale. Questa sottoquery utilizza la funzione aggregata MAX() per selezionare la data più recente.

La selezione dell'ultimo RateChangeDate per ogni dipendente è separata, quindi si aggiunge una clausola WHERE nella subquery per far corrispondere gli ID dei dipendenti tra la query esterna e quella interna.

Esercizio 17: Selezionare le retribuzioni minime, medie e massime

Esercizio: Selezionare le retribuzioni minime, medie e massime dalle retribuzioni attuali dei dipendenti.

Soluzione:

SELECT MIN(CurrentPayRate) AS MinPayRate,
       AVG(CurrentPayRate) AS AvgPayRate,
       MAX(CurrentPayRate) AS MaxPayRate
FROM (
		SELECT e.BusinessEntityID, e.Rate AS CurrentPayRate
		FROM HumanResources.EmployeePayHistory AS e
		WHERE e.RateChangeDate = (
				SELECT MAX(e2.RateChangeDate)
				FROM HumanResources.EmployeePayHistory AS e2
				WHERE e2.BusinessEntityID = e.BusinessEntityID
			)
) AS cpr;

Spiegazione: Si utilizzano le funzioni aggregate MIN(), AVG() e MAX() per selezionare le retribuzioni minime, medie e massime, fornendo come argomento la colonna CurrentPayRate della sottoquery.

La subquery viene utilizzata nella clausola FROM. Questa subquery seleziona le retribuzioni correnti per ciascun dipendente, come spiegato nell'Esercizio 16.

Si noti che questa soluzione annida due sottoquery. Innanzitutto, si utilizza una sottoquery nella clausola FROM. Poi questa subquery utilizza una subquery nella propria clausola WHERE.

Esercizio 18: Selezionare gli ID dei dipendenti con le loro ore di lavoro fuori sede

Esercizio: Selezionare tutti gli ID dei dipendenti che hanno preso più di 60 ore di ferie o che hanno preso più di 60 ore di malattia.

Soluzione:

SELECT BusinessEntityID
FROM HumanResources.Employee
GROUP BY BusinessEntityID
HAVING SUM(VacationHours) > 60

UNION

SELECT BusinessEntityID
FROM HumanResources.Employee
GROUP BY BusinessEntityID
HAVING SUM(SickLeaveHours) > 60;

Spiegazione: La prima query seleziona la colonna BusinessEntityID dalla tabella e utilizza l'aggregazione . Employee e utilizza la funzione aggregata SUM() per sommare il numero di ore di ferie prese da ciascun dipendente. Si raggruppa in base all'ID del dipendente e si impone la condizione che la somma sia superiore a 60.

Analogamente, la seconda query seleziona la colonna BusinessEntityID dalla tabella e utilizza la funzione aggregata per sommare il numero di ore di ferie godute da ciascun dipendente. Employee e utilizza la funzione aggregata SUM() per sommare il numero di ore di assenza per malattia di ciascun dipendente. Si raggruppa in base all'ID del dipendente e si impone la condizione che la somma sia superiore a 60.

L'operatore UNION combina i risultati di entrambe le query.

Esercizio 19: Selezionare gli ID dei dipendenti con determinati titoli di lavoro e dipartimenti

Esercizio: Selezionare gli ID dei dipendenti che hanno il titolo di "Addetto alle vendite" o "Progettista di strumenti" e che hanno lavorato (o lavorano) nei reparti vendite o marketing.

Soluzione:

SELECT e.BusinessEntityID
FROM HumanResources.Employee AS e
WHERE e.JobTitle IN ('Sales Representative', 'Tool Designer')

INTERSECT

SELECT edh.BusinessEntityID
FROM HumanResources.EmployeeDepartmentHistory AS edh
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
WHERE d.Name IN ('Sales', 'Marketing');

Spiegazione: La prima query seleziona la colonna BusinessEntityID dalla tabella. Employee dalla tabella. Filtra la colonna JobTitle per includere solo i rappresentanti e i progettisti di strumenti.

La seconda query seleziona la colonna BusinessEntityID dalla tabella. EmployeeDepartmentHistory dalla tabella. Filtra il nome del reparto per includere solo i reparti Vendite e Marketing.

L'operatore INTERSECT individua l'output comune delle due query, ossia visualizza solo gli ID dei dipendenti che soddisfano le condizioni della clausola WHERE di entrambe le query.

Esercizio 20: Elencare i rappresentanti di vendita e i responsabili marketing con i rispettivi reparti

Esercizio: Selezionare gli ID, i titoli di lavoro e i nomi dei reparti dei dipendenti associati ai ruoli di Sales Representative o Marketing Manager.

Soluzione:

SELECT e.BusinessEntityID, e.JobTitle, d.Name AS DepartmentName
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeeDepartmentHistory AS edh 
ON e.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON edh.DepartmentID = d.DepartmentID
WHERE e.JobTitle IN ('Sales Representative', 'Marketing Manager');

Spiegazione: La query utilizza un JOIN per collegare la tabella con la tabella Employee con la tabella EmployeeDepartmentHistory basata sulla colonna BusinessEntityID. Questo join assicura che il record di ogni dipendente sia collegato alla storia del suo reparto.

Un altro JOIN collega la tabella EmployeeDepartmentHistory tabella alla tabella Department utilizzando la colonna DepartmentID. Questo permette alla query di recuperare le informazioni sul reparto relative alla storia lavorativa del dipendente.

La clausola WHERE filtra i risultati in modo da includere solo i dipendenti il cui titolo è "Sales Representative" o "Marketing Manager". Questo filtraggio viene effettuato utilizzando l'operatore IN, che specifica i titoli di lavoro desiderati. La struttura della query combina e filtra in modo efficiente i dati provenienti da più tabelle per restituire i titoli di lavoro dei dipendenti e i reparti associati.

Esercitatevi su SQL con gli esercizi di AdventureWorks!

In questo articolo avete esplorato i fondamenti dell'SQL attraverso esercizi con il database AdventureWorks. Si è imparato a eseguire query su tabelle singole con SELECT, a combinare i dati delle tabelle con JOIN, a raggruppare i dati con GROUP BY e a utilizzare funzioni aggregate come COUNT(), AVG(), SUM(), MIN() e MAX(). Inoltre, avete approfondito le subquery, UNION e INTERSECT.

Per ulteriori esercizi con soluzioni e spiegazioni dettagliate, consultate i nostri articoli 10 esercizi per principianti su Pratica su SQL con soluzioni e SQL Joins: 12 domande pratiche con risposte dettagliate. Approfondite le opportunità di esercitazione in SQL con la nostra Guida completa a Pratica su SQL su LearnSQL.com. E come ho già detto, potete anche continuare a esercitarvi con la nostra traccia Pratica su SQL , con i database di esercitazione SQL o con il corso SQL mensile gratuito.

Buona fortuna nel vostro viaggio in SQL!