Enhance your SQL Server proficiency with our SQL Server practice exercises. Each of these 15 practical T-SQL tasks includes a detailed solution to help you improve your querying skills.
You know how everyone says, "Practice makes perfect"? Well, it couldn't be truer for SQL. The real learning happens when you start working with queries, tables, and data. If you're looking to sharpen your skills in MS SQL Server, you're in the right spot. These 15 beginner-friendly T-SQL exercises are perfect for getting the hang of the basics. (If you’re wondering, T-SQL is the SQL dialect used in SQL Server databases.)
The exercises are taken from our interactive course SQL Practice Set in MS SQL Server. This is a T-SQL practice course for beginners working with SQL Server. It offers over 80 hands-on practical exercises, grouped into sections on single table queries, aggregate functions, grouping and ordering results, JOINS, and subqueries, and others. If you like the exercises in this article, I recommend you try the course!
If you’re a beginner who wants to learn everything T-SQL has to offer, check out our complete learning track SQL from A to Z in MS SQL Server. The track contains 7 interactive T-SQL courses that will teach you complete modern T-SQL for data analysis.
Section 1: Cats – Purr-fect SQL Exercises to Sharpen Your Claws
In the first section, we’ll work on the table Cat. This table has the following columns:
Id– The ID of a given cat.Name– The cat’s name.Breed– The cat’s breed (e.g. Siamese, Cornish Rex).Coloration– The cat’s coloration (e.g. tortoiseshell, black).Age– The cat’s age.Sex– The cat’s sex.FavToy– The cat’s favorite toy.
Exercise 1: Get to Know the Cat Table
Exercise: Select all data from the Cat table.
Solution:
SELECT * FROM Cat;
Solution: We use the SELECT statement to select data from the database. The asterisk (*) following SELECT means that you want all columns from the table. The FROM Cat command indicates the table from which you pull the rows.
Exercise 2: Kittens
Exercise: Select the Name, Breed, and Coloration for every cat that is younger than five years old.
Solution:
SELECT Name, Breed, Coloration FROM Cat WHERE Age < 5;
Explanation: As before, we use the SELECT statement. This time, we list the columns we want to select: Name, Breed, and Coloration. In the FROM clause, we give the name of the table (Cats).
In the WHERE clause, we specify the condition to only select cats younger than 5 years old: Age < 5. We use the < operator to compare the value of the Age column with the number 5. The database will only select the cats who are younger than 5 (i.e. have Age lower than 5).
Exercise 3: Young and Old Ragdoll cats
Exercise: Select the ID and name for every cat that is either younger than five years old or older than ten years old and that is of the Ragdoll breed.
Solution:
SELECT Id, Name FROM CatWHERE (Age < 5 OR Age > 10)AND Breed = 'Ragdoll';
Explanation: This query is similar to the previous query. The only difference is in the WHERE clause.
We are looking for cats who are younger than 5 or older than 10. To this end, we use the condition (Age < 5 OR Age > 10). We use the comparison operators < and >, and we combine them with the logical operator OR. This selects cats who are either younger than 5 or older than 10.
We then use the condition Breed = 'Ragdoll' to only select Ragdoll cats. It’s important to note that the text value Ragdoll is put in single quotes: 'Ragdoll'.
Finally, we use the AND operator to combine the age and breed conditions.
Exercise 4: Which Cats Like to Play with Balls?
Exercise: Select all data for cats whose:
- Breed starts with an 'R'.
- Coloration ends with an 'm'.
- Favorite toy starts with the word 'ball'.
Solution:
SELECT *FROM CatWHERE Breed LIKE 'R%' AND Coloration LIKE '%m' AND FavToy LIKE 'ball%'
Explanation: We use a simple SELECT query to solve the exercise. In SELECT, we put the asterisk * to select all columns from the table.
We then use AND to combine three WHERE conditions. The first condition selects cats with breed names starting with R. We use the LIKE operator and the wildcard %: Breed LIKE 'R%'. This condition means that we are looking for breeds that start with R, followed by any text (empty or not).
In the second condition, we use Coloration LIKE '%m' to find cats whose coloration ends in “m”. In the third condition, we use FavToy LIKE 'ball%' to look for favorite toys that start with “ball”.
If you are looking for additional basic SQL exercises, check out our article 10 Beginner SQL Practice Exercises with Solutions.
Section 2: Games – Level Up Your SQL Skills with Video Game Data
Now we move on from cats and focus on something completely different: video games. We’ll work with the Games table, which consists of 9 columns:
Id– The ID of a given game.Title– The game ‘s title (e.g. Mario Kart).Company– The company that produced the game.Type– The genre (e.g. racing).ProductionYear– The year when the game was created.System– The system for which the game was released (e.g. Nintendo).ProductionCost– The cost of producing the game.Revenue– The revenue generated by this game.Rating– The game’s rating (by users).
In this section we’ll focus on using GROUP BY and aggregate functions.
Exercise 5: Average Production Costs for Good Games
Exercise: Show the average production cost of games that were produced between 2010 and 2015 and were rated higher than 7.
Solution:
SELECT AVG(ProductionCost)FROM GamesWHERE ProductionYear BETWEEN 2010 AND 2015 AND Rating > 7;
Explanation: In the SELECT statement, we use the aggregate function AVG() to compute the average production cost. We give ProductionCost as an argument; the full expression is AVG(ProductionCost).
In the WHERE clause, we filter for high-rated games (Rating > 7) and produced between 2010 and 2015 (ProductionYear BETWEEN 2010 AND 2015).
Exercise 6: Game Production Statistics by Year
Exercise: For all games, display how many games were released each year (as the count column), the average cost of production (as the AvgCost column), and their average revenue (as the AvgRevenue column).
Solution:
SELECT ProductionYear, COUNT(*) AS count, AVG(ProductionCost) AS AvgCost, AVG(Revenue) AS AvgRevenueFROM GamesGROUP BY ProductionYear;
Explanation: You’re used to the basic SELECT syntax, so we won’t go over that. In this query, we are using GROUP BY to organize rows into groups based on a given value. When we do that, we can find statistics for each group.
In the SELECT, we list the ProductionYear and the following expressions:
COUNT(*)to count rows in each group.AVG(ProductionCost)to compute the average production cost in each group.AVG(Revenue)to calculate the average revenue for each group.
After FROM, we add the GROUP BY. Since we want to compute statistics for each production year, we use GROUP BY ProductionYear.
Exercise 7: Company Production Statistics
Exercise: For each company, select its name, the number of games it produced (as the NumberOfGames column), the average cost of production (as the AvgCost column). Note: Show only the companies that produced more than one game.
Solution:
SELECT Company, COUNT(*) AS NumberOfGames, AVG(ProductionCost) AS AvgCostFROM GamesGROUP BY CompanyHAVING COUNT(Company) > 1;
Explanation: In this query we select data from the Games table. We select Company, COUNT(*) to count rows for this company, and AVG(ProductionCost) to compute the average production cost for all games produced by this company.
We then use GROUP BY Company to group games produced by each company. Finally, we use the HAVING clause to limit the results to companies that produced more than one game.
Exercise 8: Identify Good Games
Exercise: We're interested in good games produced between 2000 and 2009. A good game is a game that has a rating higher than 6 and was profitable (earned more than its production costs).
For each company, show the company name, its total revenue from good games produced between 2000 and 2009 (as the RevenueSum column), and the number of good games it produced in this period (as the NumberOfGames column). Only show companies with good-game revenue over 4 000 000.
Solution:
SELECT Company, COUNT(*) AS NumberOfGames, SUM(Revenue) AS RevenueSumFROM GamesWHERE ProductionYear BETWEEN 2000 AND 2009 AND Rating > 6 AND Revenue - ProductionCost > 0GROUP BY CompanyHAVING SUM(Revenue) > 4000000;
Explanation: In SELECT, we list the Company to get the name of the company, COUNT(*) to count the number of games produced by this company, and SUM(Revenue) to compute the company’s total revenue.
In WHERE, we apply the row-level filters mentioned in the exercise:
ProductionYear BETWEEN 2000 AND 2009to find games produced between 2000 and 2009.Rating > 6to find games with rating above 6.Revenue - ProductionCost > 0to find games with more revenue than production costs.
We group the rows using GROUP BY Company. Finally, we use HAVING to find companies with total revenue above 4000000.
Exercise 9: Gross Profit by Company
Exercise: For all companies present in the table, show their name and the sum of gross profit over all years. To simplify this problem, assume that the gross profit is Revenue - ProductionCost; show this column as GrossProfitSum.
Make sure the results start with the company that had the highest gross profit.
Solution:
SELECT Company, SUM(Revenue - ProductionCost) AS GrossProfitSumFROM GamesGROUP BY CompanyORDER BY SUM(Revenue - ProductionCost) DESC;
Explanation: In the SELECT statement, we list the values Company and SUM(Revenue - ProductionCost). The expression Revenue - ProductionCost computes the gross profit for each game and SUM(Revenue - ProductionCost) sums this profit across multiple games.
We then group the data by company (GROUP BY Company), and order the results by the total gross profit in descending order. Finally, we order by gross profit; we specify descending order (10-1 instead of 1-10) so that the highest profit comes first.
You can find more exercises specifically for GROUP BY in 10 GROUP BY SQL Practice Exercises with Solutions
Section 3: The Art of the JOIN
In this section, we’ll focus on JOINs. We’ll work with a database with data about pieces of art. There are three tables in the database.
The table Artist has the following columns:
Id– The ID of a given artist.Name– The artist’s name.BirthYear– The year the artist was born.DeathYear– The year the artist died.ArtisticField– The artist’s preferred field (e.g. painting, sculpture).
The table PieceOfArt has the following columns:
Id– The ID of a given piece of artwork.Name– The work’s name.ArtistId– The ID of the artist who created the work.MuseumId– The ID of the museum that houses the work.
The table Museum consists of the following three columns:
Id– The ID of a given museum.Name– The museum’s name.Country– The country where the museum is located.
Exercise 10: List All Pieces of Art
Exercise: Show the names of all pieces of art, together with the names of their creators and the names of the museums that house the art.
Do not include lost works (i.e. those without a museum ID) and pieces with an unknown artist. Name the columns PieceOfArtName, ArtistName, and MuseumName.
Solution:
SELECT A.Name AS ArtistName, M.Name AS MuseumName, Poa.Name AS PieceOfArtNameFROM Museum MJOIN PieceOfArt Poa ON M.Id = Poa.MuseumIdJOIN Artist A ON A.Id = Poa.ArtistId;
Explanation: We want to list all pieces of art for which we have complete information (artist, museum). This tells us that we have to use INNER JOIN to join the tables.
We use the JOIN keyword, as its equivalent to INNER JOIN. We first join the tables Museum and PieceOfArt on the museum ID. Then we join the Artist table on the artist ID.
You can read about joining multiple tables in How to Join 3 Tables (or More) in SQL
Exercise 11: Works from 19th-Century Artists (and Later)
Exercise: Find artists who lived more than 50 years and were born after the year 1800. Show their name and the name of the pieces of art they created. Rename these columns ArtistName and PieceName, respectively.
Solution:
SELECT A.Name AS ArtistName, Poa.Name AS PieceNameFROM Artist AJOIN PieceOfArt Poa ON A.Id = Poa.ArtistIdWHERE DeathYear - BirthYear > 50 AND BirthYear > 1800;
Explanation: Here we join the tables Artist and PieceOfArt on the artist ID. In the WHERE clause, we filter for artists who lived longer than 50 years. We do this by computing the age of the artist with an expression DeathYear - BirthYear and filtering it using the comparison DeathYear - BirthYear > 50.
We also filter for artists from the 19th century or later with the condition BirthYear > 1800. We combine the two conditions with an AND operator.
Exercise 12: Artists' Productivity
Exercise: Show the names of artists together with the number of years they lived (name the column YearsLived) and the number of pieces they created (name the column NumberOfCreated).
Only show artists who created at least one piece of art.
Solution:
SELECT A.Name, DeathYear - BirthYear AS YearsLived, COUNT(Poa.ArtistId) AS NumberOfCreatedFROM Artist AJOIN PieceOfArt Poa ON A.Id = Poa.ArtistIdGROUP BY A.Name, DeathYear - BirthYear;
Explanation: This SQL query selects the artist’s name, the number of years they lived (DeathYear - BirthYear), and the total number of pieces they created COUNT(Poa.ArtistId).
The data is joined on the artist ID between the Artist table and the PieceOfArt table. The results are grouped by the artist's name and their lifespan.
Two things are important to note here. First, we have to include the DeathYear - BirthYear expression in the GROUP BY clause, since this expression is unaggregated. Otherwise the database could throw an error. (You can read about the error in How to Fix a 'Not a GROUP BY Expression' Error and How to Solve the Error “must appear in the GROUP BY clause”.)
Second, since we’re using INNER JOIN, we’re already guaranteed to get artists who have created at least one piece of art. Artists with no artwork will be omitted by this JOIN.
You can find more SQL JOIN exercises in SQL Joins: 12 Practice Questions with Detailed Answers
Section 4: A Cart Load of Data
In this final section, we’ll work with data from a store database. It has five tables:
Categories stores the names of product categories:
Id– The ID of a given categoryName– The category name.
Products stores product info:
Id– The ID of a given product.Name– The product’s name.CategoryId– The ID of the category the product belongs to.Price– The product’s price.
Clients stores basic shopper info:
Id– The ID of a given client.FirstName– The client’s first name.LastName– The client’s last name.
Orders records basic order info:
Id– The ID of a given order.ClientId– The ID of the client who placed the order.Year– The year the order was placed.
Finally, the table OrderItems contains data about which items make up each order. This is an associative table that connects data from the Orders and Products tables. It consists of the following columns:
OrderId– The ID of the order.ProductId– The ID of the product in the above order.Quantity– The quantity of the product in this order.Price– The total price for the product in this order.
Exercise 13: Revenue for each order
Exercise: For each order, select its ID (name the column OrderId), the first and last name of the client who placed this order, and the total revenue generated by this order (name the column Revenue).
Note: The revenue for the order is the sum of the Price column for each item in the order.
Solution:
SELECT O.Id AS OrderId, C.FirstName, C.LastName, SUM(Oi.Price) AS RevenueFROM Orders AS OJOIN OrderItems AS Oi ON O.Id = Oi.OrderIdJOIN Clients AS C ON O.ClientId = C.IdGROUP BY O.Id, C.FirstName, C.LastName;
Explanation: In this query, we join the tables Orders, OrderItems, and Clients.
In SELECT, we select four expressions: the ID of the order, the first and last name of the client, and the sum of all order item prices. In GROUP BY, we group the data by order ID and the client’s first and last names. This gives us the total revenue for each order with information (first and last names) about the client.
Exercise 14: Who Repurchased Products?
Exercise: Select the first name and last name of the clients who repurchased products (i.e., bought the same product in more than one order). Include the names of those products and the number of the orders they were part of (name the column OrderCount).
Solution:
SELECT Cli.FirstName, Cli.LastName, P.Name, COUNT(O.Id) AS OrderCountFROM Clients cliJOIN Orders O ON Cli.Id = O.ClientIdJOIN OrderItems Oi ON O.Id = Oi.OrderIdJOIN Products P ON P.Id = Oi.ProductIdGROUP BY Cli.FirstName, Cli.LastName, P.NameHAVING COUNT(O.Id) > 1
Explanation: Here we join four tables: Clients, Orders, OrderItems, and Products.
In SELECT, we select the first and last name of the client, the name of the product, and the count of orders in which this product was purchased by this client. We group the table by client data (first and last name), and by the product name. This way, purchases by this client of this product are grouped together.
In HAVING, we filter for rows where the count of orders is higher than 1. This lets us select clients who purchased the same product in more than one purchase.
Exercise 15: How Much Did Each Client Spend per Category?
Exercise: Select the first and last name of each client, the name of the category they purchased from (in any of their orders), and the total amount of money they spent on this product category (name this column TotalAmount).
Solution:
SELECT Cli.FirstName, Cli.LastName, C.Name, SUM(OI.Price) as TotalAmountFROM Categories AS CJOIN Products AS P ON C.Id = P.CategoryIdJOIN OrderItems AS OI ON P.Id = OI.ProductIdJOIN Orders AS O ON O.Id = OI.OrderIdJOIN Clients Cli ON Cli.Id = O.ClientIdGROUP BY Cli.FirstName, Cli.LastName, C.Name
Solution: We join the tables Categories, Products, OrderItems, Orders, and Clients. We select the first and last name of the client, the name of the category and the sum of order items prices. This allows us to compute how much money each client spent in each category.
We group the results by the first and last name of the client and by the name of the category. This way, all purchase items for each client and each category are grouped together.
Further SQL Server Practice
I've had quite the experience exploring the diverse world of data with T-SQL. From uncovering the secrets behind cat behaviors to analyzing game strategies and appreciating art through numbers, SQL has been my trusty tool. It's changed the way I look at questions and discover the stories hidden in data around us.
Would you like more SQL Server practice? Check those awesome resources:
- Learning SQL? 12 Ways to Practice SQL Online
- How to Create Your Own Database to Practice SQL
- SQL Server Cheat Sheet
If you're ready to deepen your skills, our SQL Practice Set in MS SQL Server course is just what you need. It's designed to strengthen your foundation and boost your ability to analyze and interpret data effectively. With each exercise, you'll feel more confident and skilled in tackling complex queries.
For those wanting to explore every aspect of SQL, our comprehensive SQL from A to Z in MS SQL Server track covers everything from the basics to advanced techniques. It's a perfect learning path for anyone aiming to master SQL Server and make informed decisions based on data insights. Whether you're a beginner or looking to brush up on your skills, these resources will help you succeed in the world of data.
Don't wait any longer—start mastering SQL Server today and unlock the full potential of your data!
