sql assignment for practice

Basic SQL Query Practice Online: 20 Exercises for Beginners

• sql practice

These 20 exercises are just what beginners need for SQL query practice. Try to solve each of them, and then look at the solutions. If something needs to be clarified, there are explanations for each solution.

In this article, there’ll be less talking than usual. Instead, you’re going to write the answers to SQL practice queries . (Don’t worry; we’ve included the solutions if you get stuck.) The whole point is to give you, as a beginner, plenty of opportunities for SQL query practice.

I’ve selected twenty examples from our Basic SQL Practice: Run Track Through Queries! . If you feel you need to practice more by the end of the article – I recommend that wholeheartedly! – you’ll find almost 100 more interactive SQL exercises in that course. They cover topics like querying one table, using JOINs , sorting data with ORDER BY , aggregating data and using GROUP BY , dealing with NULLs , doing mathematical operations, and writing subqueries.

These are all topics that any SQL beginner should know well before going on to the more complex topics. The best way to learn anything in SQL is to consistently write your own code. That way, you’ll master SQL syntax and its basic features; plus, you’ll understand problem-solving. After all, the whole point of knowing SQL is knowing how to use data to solve problems.

And you could go even further! We have the SQL Practice track and the Monthly SQL Practice course for yet more SQL query practice.

With that being said, let’s dive straight into the SQL practice, starting with the dataset.

Exercise #1: Show the Final Dates of All Events and the Wind Points

Exercise #2: show all finals where the wind was above .5 points, exercise #3: show all data for all marathons, exercise #4: show all final results for non-placing runners, exercise #5: show all the result data for non-starting runners, exercise #6: show names for men’s discipline runs under 500 meters, exercise #7: sort country names and abbreviations, exercise #8: sort athletes’ first and last names, exercise #9: sort final results over three hours, exercise #10: show top 3 athletes’ names and places, exercise #11: show all marathons with their competition name, competition year, and discipline name, exercise #12: show mo farah’s scores for all disciplines, exercise #13: show the competitions’ names and the number of events, exercise #14: show the most popular athlete names, exercise #15: show each country and the number of athletes who finished without a place, exercise #16: calculate the average pace for each run, example #17: find all faster-than-average times for 1,500 meter runs, exercise #18: find all athletes who ran in at least two events in a competition, exercise #19: show runners who only finished first, exercise #20: find all the athletes who didn’t start and who won at least once, sql query practice.

The dataset contains data about the finals of track running competitions across athletics championships: Rio de Janeiro Olympic Games in 2016, London IAAF World Championships in Athletics in 2017, and Doha IAAF World Championships in Athletics in 2019.

Data is stored in six tables: competition , event , discipline , final_result , athlete , and nationality . The schema is shown below:

The competition information is stored in the table competition . It has the following columns:

• id – The ID of the competition and the primary key of the table.
• name – The competition's name.
• start_date – The competition's first day.
• end_date – The competition's last day.
• year – The year during which this competition occurred.
• location – The location of this competition.

Here’s the data from the table.

The table discipline holds information for all running disciplines. It has these columns:

• id – The ID of the discipline and the primary key of the table.
• name – The discipline's name.
• is_men – TRUE if it's a men's discipline, FALSE if it's a women's.
• distance – The discipline's distance, in meters.

This is a snapshot of the first five rows of the data:

The next table is event , which stores  information about each particular event:

• id – The ID of the event and the primary key of the table.
• competition_id – Links the event to a competition.
• discipline_id – Links the event to a discipline.
• final_date – When this event's final was held.
• wind – The wind points during the final.

Here are the first five rows of this table:

The data about each athlete is in the table athlete :

• id – The ID of the athlete and the primary key of the table.
• first_name – The athlete's first name.
• last_name – The athlete's last name.
• nationality_id – The athlete's nationality.
• birth_date – The athlete's birth date.

These are the first five rows:

The nationality table contains country information:

• id – The ID of the country and the primary key of the table.
• country_name – The country's name.
• country_abbr – The country's three-letter abbreviation.

Here is a five-row snapshot of this table:

The last table is final_result . It contains information about the participants and their results in a particular event:

• event_id – The event ID.
• athlete_id – The athlete’s
• result – The time/score for the athlete (can be NULL).
• place – The place achieved by the athlete (can be NULL).
• is_dsq – TRUE if d i sq ualification occurred.
• is_dnf – TRUE if the athlete d id n ot f inish the run.
• is_dns – TRUE if the athlete d id n ot s tart the run.

Here’s the snapshot:

Now that you’ve had a good look at the dataset, let’s start our basic SQL query practice! All the exercises will require you to know some SQL, so make sure you know all the basic elements of an SQL query .

Exercise: Find the final dates of all events and the wind points.

Explanation: The data you need is in the table event . You have to select two columns from it: final_date and wind. You do that by writing the first column in the SELECT statement. Next, you write the second column name and separate the column names with a comma.

Finally, you reference the table in the FROM clause.

Exercise: Show all the finals’ dates with a wind stronger than 0.5 points.

Explanation: First, select the column final_date from the table event . With that, you’d get a list of all the finals. However, you don’t need the whole list – only those finals where the wind was stronger than 0.5.

So, you need to filter data using the WHERE clause. In it, you write the column name you want to filter; in this case, it’s the column wind . To get the wind above 0.5, use the ‘greater than’ ( > ) logical operator.

Exercise: Show the discipline data for all marathons.

Explanation: To select all the columns, you don’t have to write their names explicitly. There’s a shorthand for ‘all columns’ called asterisk ( * ). Instead of the columns’ names, just put an asterisk in SELECT .

Then, as you want data from the table discipline , you reference it in FROM .

Finally, you have to filter the data. Use  WHERE and the LIKE operator . This operator looks through textual data in the column and returns all rows containing the text in the WHERE condition. In other words, the condition will look for the word ‘Marathon’. You must put the word in single quotes.

However, you don’t know the exact name of the discipline; you just know it has to contain that word. It can be anywhere in the discipline name: at the beginning, middle, or end. To look anywhere in the string , put the modulo ( % ) operator before and after the word you’re searching.

Exercise: Show all the data for final results for runners who did not place.

Explanation: You need all the columns, so use an asterisk in SELECT and reference the table final_result in FROM.

You need to show only those results where runners ended without a place. You will use WHERE this time, too, and filter on the column place . If a runner ends without a place, then the column place will be empty (i.e. NULL). You need the IS NULL operator after the column name to return all these rows.

Knowing what a NULL is in SQL  would be a good idea before using the IS NULL operator.

Exercise: Show all the results data for runners that didn’t start the run at all.

Explanation: Select all the columns from the table final_result using an asterisk and referencing the table in FROM .

Then, you want to use WHERE and filter the column by is_dns . If the runner didn’t start the race, this column will have the TRUE value. So, you need to use the IS TRUE operator after the column name.

Output: Here’s the whole output:

Exercise: Show only the men’s discipline names where the distance to be run is less than 500 meters.

Explanation: First, select the column name from the table discipline .

You again need to filter data – this time, by putting two conditions in WHERE .

The first condition is that it’s a male discipline. So, you have to filter the column is_men using the IS TRUE operator. Then you add the second condition: the values in the column distance have to be below 500. This condition uses the less than operator ( < ). Since both conditions have to be satisfied, separate the conditions using the AND operator.

Exercise: Show all the countries’ names and abbreviations. Sort the output alphabetically by country name.

Explanation: Select the country name and its abbreviation from the table nationality .

To sort the output, use the ORDER BY clause. You want to sort by country name, so write country_name in ORDER BY . The output should be sorted alphabetically, so use the keyword ASC (ascending) after the column name.

Output: Here are the first five rows of the output:

Exercise: Show every athlete’s first and last name. Sort the output descendingly by the athlete’s first name. If multiple athletes have the same name, show their surnames sorted descendingly.

Explanation: Select the first and last name from the table athlete .

Then, add the ORDER BY clause. First sort by the first name descendingly, adding DESC after the column name. The second sorting condition sorts by the last name, also descendingly. Again, write the column name and add DESC . The conditions have to be separated by a comma.

Exercise: For all final results, show the times that are at least three hours. Sort the rows by the result in descending order.

Explanation: Select the column result from the table final_result .

Then, use WHERE to find the results that are below three hours. You can use the ‘greater than or equal’ ( >= ) and INTERVAL operators.

The data in the result column is formatted as time. So, you need to use INTERVAL to get the specific part (interval) from that data. In this case, it’s three hours. Simply write ‘3 hours’ after INTERVAL .

Finally, sort the output descendingly by the result.

Exercise: For every athlete ever on the podium (i.e. finished in the top 3), show their last and first name and their place.

Explanation: In this exercise, you need to use data from two tables: athlete and final_result . So, let’s start the explanation from the FROM clause.

You reference the athlete table and give it an alias ‘a’, so you won’t need to write the table’s full name elsewhere in the query. To get data from another table, too, you need to join the tables. In this case, use JOIN , which will return only the matching rows from both tables. You do that by simply referencing the table final_result in JOIN and adding the ‘fin’ alias.

Next, you have to specify the joining condition using the keyword ON . The tables are joined on shared columns: id from athlete and athlete_id from final_result . You’re looking for rows where the values in these two columns are equal, so put an equal sign ( = ) between them. In front of each column name, put the table alias followed by a dot so the database knows which table that column is in.

Now that you have joined the tables, you can select the columns. In front of each column name, put the table alias for the same reason as explained earlier. Now, you have the athletes’ last and first names and their places.

As a last step, simply filter data using WHERE and the column place . You’re looking for podium finishes, so the values must be equal to or less than three. Use the ‘less than or equal’ ( <= ) operator.

This SQL query practice requires you to know SQL JOINs. If you’re still unsure how they work, look at these SQL JOINs practice questions before you go to other exercises.

Exercise: Show all marathons, the name (rename this column competition_name ) and year of the competition, and the name of the discipline (rename this column discipline_name ).

Explanation: This exercise shows how to join multiple tables. The principle is the same as with two tables. You just add more JOINs and the joining conditions.

In this case, you join the competition and event tables where e.competition_id equals the c.id column .

Then, you need to add the discipline table to the joining chain. Write JOIN again and reference the table discipline . Add the joining condition: the column discipline_id from the event has to be equal to the id column from the discipline table.

Now, select the required columns, remembering to put the table alias in front of each column. Alias competition_name and discipline_name using the keyword AS to give them the column names described in the instructions.

Finally, filter the results to show only marathon disciplines.

Exercise: Show Mo Farah's (athlete ID of 14189197) scores for all disciplines. Show NULL if he has never participated in a given discipline. Show all the male disciplines' names, dates, places, and results.

Explanation: Join the tables discipline and event on the columns discipline_id and id . You need to use LEFT JOIN . This type of join will return all the rows from the first (left) table and only the matching rows from the second (right) table. If there are no matching rows, the values will be NULL . This is ideal for this exercise, as you need to show all disciplines and use  NULLs if Mo Farah has never participated in the discipline.

The next join is also a LEFT JOIN . It joins the table event with the table final_result . The first joining condition here joins the tables on the columns event_id and id . You also need to include the second condition by adding the keyword AND . This second condition will only look for Mo Farah’s data, i.e., the athlete with the ID of 14189197.

As a last step, use WHERE to find only men’s disciplines.

Exercise: Show all the competitions’ names and the number of events for each competition.

Explanation: First, show the column name from the table competition and rename the column to competition_name .

Then, use the aggregate function COUNT(*) to count the number of events that were held. The COUNT() function with an asterisk will count all the rows from the output, including NULLs. For better readability, we alias the resulting column as events_held .

The tables we join are competition and event . Finally, to get the number of events per competition, you need to GROUP BY the competition name.

Exercise: Show the most popular athlete names. Names are popular if at least five athletes share them. Alongside the name, also show the number of athletes with that name. Sort the results so that the most popular names come first.

Explanation: First, select the first names and count them using COUNT(*) . Then, group by the first name of the athlete. Now you have all the names and their count.

But you need to show only those names with a count above five. You’ll achieve that by using the HAVING clause. It has the same use as WHERE, but HAVING is used for filtering aggregated data.

Finally, sort the output by the name count from the highest to the lowest. You can’t simply write the name_count column name in ORDER BY because sorting is done before aggregation; SQL won’t recognize the column name. Instead, copy COUNT(*) and sort descendingly.

This exercise shows a typical SQL problem that requires filtering data with an aggregate function .

Exercise: Show all countries with the number of their athletes that finished without a place. Show 0 if none. Sort the output in descending order by the number of athletes and by the country name ascendingly.

Explanation: You have to keep all rows from the nationality table, so you need to LEFT JOIN it with the athlete table. You do that where id equals nationality_id . Then, LEFT JOIN another table where id from the athlete table equals athlete_id from the final_result table.

Because you need all the nationality rows, you can’t use the IS NULL condition in WHERE . There’s a solution: move it to the ON clause, and you’ll get all the values where the place is NULL .

Now, you can select the column country_name . Also, use the COUNT() function on the athlete_id column to get the number of athletes who finished without a place. You can’t use COUNT(*) here because it would’ve counted f, and you need the count of concrete athletes.

To get the count value by country, group the output by country name.

Finally, sort the output by the number of athletes descendingly and by the country name ascendingly.

Exercise: Calculate the average pace for each run and show it in the column named average_pace .

Explanation: To get the average pace by run, you need to divide the result by the distance. This is what the above query does, but with two tweaks.

First, you need to multiply the distance by 1.0. You do that to convert the distance to a decimal number. Without that, the division might return a different result, as the result will be divided by the whole number. The second tweak is that you divide the distance by 1,000. By doing this, you’ll convert the distance from meters to kilometers.

Now that you have the calculation, give this column the alias average_pace .

The rest of the query is what you already saw in previous examples: you’re joining the table event with the table discipline and then with the table final_result .

Output: Here are the first five rows from the output:

Exercise: Output the times for all 1,500-meter runs. Show only times that are faster than the average time for that run.

Explanation: You need to know SQL subqueries to solve this exercise. Their basic definition is that they are queries within a main query. Let’s see how this works!

Select the result column from the table final_result . Then, JOIN the table with event and then with the discipline table.

After that, you have to set two conditions in WHERE . The first one selects only distances that are equal to 1,500.

The second one looks for data where the result is below the total average for 1,500-meter runs. To calculate the average, use a subquery in the following way.

In the parentheses after the comparison operator, write another SELECT statement ( i.e., a subquery). In it, use the AVG() aggregate function to calculate the average result. The rest of the query is the same as the main query; you’re joining the same tables and using the same filtering condition in WHERE .

Output: Here are the first few rows from the output:

Exercise: Output a list of athletes who ran in two or more events within any competition. Show only their first and last names.

Explanation: Start by selecting the first and the last name from the table athlete .

Then, use WHERE to set up a condition. We again use a subquery to return data we wanted to compare, this time with the column id. However, in the previous example, we used the ‘less than’ ( < ) operator because the subquery returned only one value. This time, we use the operator IN , which will go through all the values returned by the subquery and return those that satisfy the condition.

The condition is that the athletes compete in at least two events within a competition. To find those athletes, select the column athlete_id and join the tables event and final_result . Then, group the results by the competition and athlete IDs. This example shows you can group the output by the column that is not in SELECT . However, all the columns that appear in SELECT have to also appear in GROUP BY .

Finally, use HAVING to filter the data. Count the number of rows using COUNT(*) . That way, you’re counting how many times each athlete appears. Set the condition to return only those athletes with a count equal to or above two.

Output: Here’s the output snapshot.

Exercise: Show all runners who have never finished at any place other than first; place was never missing for them. Show three columns: id , first_name , and last_name .

Explanation: For this solution, you need to use the EXCEPT set operator. The set operators are used to return the values from two or more queries. EXCEPT returns all the unique records from the first query except those returned by the second query.

The first query in the solution looks for those athletes who finished first. To get these values, select the required columns from the table athlete . Then, join the table with the table final_result . After that, set the condition in WHERE to find only the first places.

Now, write the EXCEPT keyword and follow it with the second query.

The second query is almost the same as the first one. The only difference is two conditions in WHERE .

The first condition returns all the places that are not the first by using the ‘not equal’ ( != ) operator. The second condition looks for the non- NULL places, i.e., the place was never missing for that athlete. The conditions are connected using OR because one of those conditions has to be true; the athlete can’t finish below first place and also not finish at all.

Note that for set operators to work, there has to be the same number of columns of the same data type in both queries.

Exercise: Output the athletes who didn’t start at least one race and won at least one race. Show three columns: id , first_name , and last_name .

Explanation: This exercise uses another set operators. This time, it’s INTERSECT , which returns all the values that are the same in both queries.

The first query in the solution lists the athlete IDs and first and last names. The tables athlete and final_result are joined on the columns id and athlete_id from the tables.

The condition in WHERE looks for rows with TRUE as a value in the column is_dns , i.e., the column that shows whether the athlete started the race.

As in the previous example, write the set operator and then the second query.

The second query is the same as the first one, except for WHERE . The filtering condition will find the athletes who finished first.

Together, these two queries output the athletes that didn’t start the race at least once but also finished first at least once.

From Basic SQL Query Practice to Becoming an SQL Master

You have to start from somewhere. These 20 basic SQL query practices are ideal for building foundations before learning more advanced concepts.

You learned plenty as you practiced writing queries that used WHERE , ORDER BY , JOINs , GROUP BY , and HAVING . I also showed you several examples of dealing with NULLs, doing computations, writing subqueries, and using set operators. The queries in this article have been taken from our Basic SQL Practice: Run Track Through Queries! You’ll find more basic SQL exercises there. And if you want more practice, check out our SQL Practice track, which contains 9 SQL practice courses for beginners.

Add 20 basic SQL query examples and 10 beginner SQL practice exercises to the mix, and you’ll be equipped with an intermediate level of SQL proficiency.

You may also like

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query

SQL Tutorial

Sql database, sql references, sql examples, sql exercises.

You can test your SQL skills with W3Schools' Exercises.

We have gathered a variety of SQL exercises (with answers) for each SQL Chapter.

Try to solve an exercise by filling in the missing parts of a code. If you're stuck, hit the "Show Answer" button to see what you've done wrong.

Count Your Score

You will get 1 point for each correct answer. Your score and total score will always be displayed.

Start SQL Exercises

Start SQL Exercises ❯

If you don't know SQL, we suggest that you read our SQL Tutorial from scratch.

Kickstart your career

Get certified by completing the course

COLOR PICKER

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

[email protected]

Top Tutorials

Top references, top examples, get certified.

Advertisements

• Oracle / PLSQL
• Web Development
• Color Picker
• Programming
• Techie Humor

Tutorial Resources

• Practice Exercises

• AND & OR
• COMPARISON OPERATORS
• IS NOT NULL
• SELECT LIMIT

SQL Advanced

• ALTER TABLE
• CREATE TABLE
• CREATE TABLE AS
• GLOBAL TEMP
• PRIMARY KEY

SQL Functions

SQL: Practice Exercises for SELECT Statement

If you want to test your skills using the SQL SELECT statement, try some of our practice exercises.

These practice exercises allow you to test your skills with the SELECT statement. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer.

Get started!

Return to Tutorial

Practice Exercise #1:

Based on the employees table below, select all fields from the employees table whose salary is less than or equal to $52,500 (no sorting is required):

Solution for Practice Exercise #1:

The following SQL SELECT statement would select these records from the employees table:

These are the results that you should see:

Practice Exercise #2:

Based on the suppliers table below, select the unique city values that reside in the state of California and order the results in descending order by city :

Solution for Practice Exercise #2:

The following SELECT statement would select these records from the suppliers table:

Practice Exercise #3:

Based on the customers table and the orders table below, select the customer_id and last_name from the customers table and select the order_date from the orders table where there is a matching customer_id value in both the customers and orders tables. Order the results by customer_id in descending order.

Solution for Practice Exercise #3:

The following SQL SELECT statement would select these records from the customers and orders table (using an INNER JOIN ):

Practice Exercise #4:

Based on the customers and orders table from Practice Exercise #3, select the customer_id and last_name from the customers table where there is a record in the orders table for that customer_id . Order the results in ascending order by last_name and then descending order by customer_id .

Solution for Practice Exercise #4:

The following SQL SELECT statement would select the records from the customers and orders table (using the SQL EXISTS clause ):

Or alternatively you could exclude the ASC keyword for customer_name in the ORDER BY clause . Both of these SELECT statements would generate the same results:

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy .

Copyright © 2003-2024 TechOnTheNet.com. All rights reserved.

SQL EXERCISES

• 30 Exercises: agregate functions, order, group by, having , boolean, joins.
• 14 Exercises: select, filtering, scalar functions, group by, joins, subquery, tables, DDL.
• Beginner – Intermediate
• 400 Exercises: sql queries, filtering, sorting, multiple tables, joins, subqueries.
• 140 Exercises
• 40 Exercises: select, variables, subqueries, joins, aggregation, data modification.
• 100 Exercises
• 20 Exercises: select, sum, count, joins, nulls.
• 20 Exercises/projects/challenges
• Intermediate
• 60 Exercises: multiple tables queries.
• 50 Exercises
• 1 Challenge: Football World Cup 2014
• 27 Practice exams: databases
• 16 Skills evaluation tests
• 7 Evaluation questions
• 45 Interview questions
• 20 Interview questions. 10 Exercises
• 4 Exercises & Mock  interview questions: joins and sub queries.
• 50 Theory questions
• 15 Theory questions: MySQL certification
• Challenge & Quiz
• Intermediate – Advanced
• 50 Exercises: multiple table queries
• 10 Exercises: subqueries, joins.
• Beginner – Intermediate – Advanced
• 190 Exercises
• 30 Exercises/Labs
• 20 Challenges
• 12 SQL Server Developer questions.

Terms of Use

Shopping cart

• SQL Server training
• Write for us!

Learn SQL: Practice SQL Queries

Today is the day for SQL practice #1. In this series, so far, we’ve covered most important SQL commands ( CREATE DATABASE & CREATE TABLE , INSERT , SELECT ) and some concepts ( primary key , foreign key ) and theory ( stored procedures , user-defined functions , views ). Now it’s time to discuss some interesting SQL queries.

Let’s take a quick look at the model we’ll use in this practice.

You can expect that in real-life situations (e.g., interview), you’ll have a data model at your disposal. If not, then you’ll have the description of the database (tables and data types + additional description of what is stored where and how the tables are related).

The worst option is that you have to check all the tables first. E.g., you should run a SELECT statement on each table and conclude what is where and how the tables are related. This won’t probably happen at the interview but could happen in the real-life, e.g., when you continue working on an existing project.

Before We Start

The goal of this SQL practice is to analyze some typical assignments you could run into at the interview. Other places where this might help you are college assignments or completing tasks related to online courses.

The focus shall be on understanding what is required and what is the learning goal behind such a question. Before you continue, feel free to refresh your knowledge on INNER JOIN and LEFT JOIN , how to join multiple tables , SQL aggregate functions , and the approach to how to write complex queries . If you feel ready, let’s take a look at the first 2 queries (we’ll have some more in upcoming articles). For each query, we’ll describe the result we need, take a look at the query, analyze what is important for that query, and take a look at the result.

SQL Practice #1 – Aggregating & LEFT JOIN

Create a report that returns a list of all country names (in English), together with the number of related cities we have in the database. You need to show all countries as well as give a reasonable name to the aggregate column. Order the result by country name ascending.

Let’s analyze the most important parts of this query:

• We’ve used LEFT JOIN ( LEFT JOIN city ON country.id = city.country_id ) because we need to include all countries, even those without any related city
• We must use COUNT(city.id) AS number_of_cities and not only COUNT(*) AS number_of_cities because COUNT(*) would count if there is a row in the result (LEFT JOIN creates a row no matter if there is related data in other table or not). If we count the city.id , we’ll get the number of related cities
• The last important thing is that we’ve used GROUP BY country.id, country.country_name_eng instead of using only GROUP BY country.country_name_eng . In theory (and most cases), grouping by name should be enough. This will work OK if the name is defined as UNIQUE. Still, including a primary key from the dictionary, in cases similar to this one, is more than desired

You can see the result returned in the picture below.

SQL Practice #2 – Combining Subquery & Aggregate Function

Write a query that returns customer id and name and the number of calls related to that customer. Return only customers that have more than the average number of calls of all customers.

The important things I would like to emphasize here are:

• Please notice that we’ve used aggregate functions twice, once in the “main” query, and once in the subquery. This is expected because we need to calculate these two aggregate values separately – once for all customers (subquery) and for each customer separately (“main” query)
• The aggregate function in the “main” query is COUNT(call.id) . It’s used in the SELECT part of the query, but we also need it in the HAVING part of the query (Note: HAVING clause is playing the role of the WHERE clause but for aggregate values)
• Group is created by id and customer name. These values are the ones we need to have in the result
• In the subquery, we’ve divided the total number of rows ( COUNT(*) ) by the number of distinct customers these calls were related to ( COUNT(DISTINCT customer_id) ). This gave us the average number of calls per customer
• The last important thing here is that we used the CAST operator ( CAST(… AS DECIMAL(5,2)) ). This is needed because the final result would probably be a decimal number. Since both COUNTs are integers, SQL Server would also return an integer result. To prevent this from happening, we need to CAST both divider and the divisor as decimal numbers

Let’s take a look at what the query actually returned.

In today’s SQL practice, we’ve analyzed only two examples. Still, these two contain some parts you’ll often meet at assignments – either in your work, either in a testing (job interview, college assignments, online courses, etc.). In the next part, we’ll continue with a few more interesting queries that should help you solve problems you might run into.

Table of contents

• Recent Posts

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020

Related posts:

• Learn SQL: How to Write a Complex SELECT Query
• Learn SQL: Join multiple tables
• Learn SQL: Aggregate Functions
• Learn SQL: Set Theory
• Top SQL Server Books

Revising the Select Query I Easy SQL (Basic) Max Score: 10 Success Rate: 95.96%

Revising the select query ii easy sql (basic) max score: 10 success rate: 98.69%, select all easy sql (basic) max score: 10 success rate: 99.54%, select by id easy sql (basic) max score: 10 success rate: 99.66%, japanese cities' attributes easy sql (basic) max score: 10 success rate: 99.59%, japanese cities' names easy sql (basic) max score: 10 success rate: 99.53%, weather observation station 1 easy sql (basic) max score: 15 success rate: 99.42%, weather observation station 3 easy sql (basic) max score: 10 success rate: 97.98%, weather observation station 4 easy sql (basic) max score: 10 success rate: 98.73%, weather observation station 5 easy sql (intermediate) max score: 30 success rate: 94.34%.

SQL & Databases: Download Practice Datasets

Published by SuperDataScience Team

Welcome to the data repository for the SQL Databases course by Kirill Eremenko and Ilya Eremenko. The datasets and other supplementary materials are below. Enjoy!

Section 1: Introduction

• No dataset required

Section 2: It's Super Easy to Get Started

Section 3: preparation.

• Section 3 – The Challenge
• Section 3 – PostgreSQL Upload Code
• Section 3 – MS SQL Upload Code
• Consumer Complaints (csv file 14.7 MB)
• Consumer Complaints (zip file 2.5 MB)
• Data Source: http://www.consumerfinance.gov/data-research/consumer-complaints/

Section 4: Basics of SQL

• Here we will continue working with the Dataset from the previous section.

Section 5: Working with Data

• Section 5 The Challenge
• Section 5 – PostgreSQL Upload Code
• Section 5 – MS SQL Upload Code
• Console Games Sales
• Console Dates

Section 6: Fundamentals of Database Theory

• Will be added soon.

Section 7: Joining Tables in SQL

• Section 7 – The Challenge
• Section 7 – PostgreSQL Upload Code
• Section 7 – MS SQL Upload Code
• Procedures History (Updated)
• Procedures Details

Section 8: Creating Tables in SQL

Section 9: database design.

• Section 9 – The Challenge
• Section 9 – PostgreSQL Upload Code
• Section 9 – MS SQL Upload Code
• Online Store Sales (OLTP database example)