sql assignment solutions

• SQL Cheat Sheet
• SQL Interview Questions
• MySQL Interview Questions
• PL/SQL Interview Questions
• Learn SQL and Database
• SQL Exercises
• SQL Concepts and Queries
• SQL - SELECT LAST
• SQL for Data Science
• SQL Query Interview Questions
• 7 Best Books for SQL
• Nested Queries in SQL
• Oracle Interview Experience
• TCS NQT interview Experience
• Shell India Interview Experience
• DE Shaw Interview Experience
• TCS NQT Interview Experience
• Sapient Interview Experience | Set 4
• TCS Ninja Interview Experience
• Infosys InfyTQ Interview Experience
• SAP Labs Interview Experience | Set 7

SQL Exercises : SQL Practice with Solution for Beginners and Experienced

SQL ( Structured Query Language ) is a powerful tool used for managing and manipulating relational databases. Whether we are beginners or experienced professionals, practicing SQL exercises is important for improving your skills. Regular practice helps you get better at using SQL and boosts your confidence in handling different database tasks.

So, in this free SQL exercises page, we’ll cover a series of SQL practice exercises covering a wide range of topics suitable for beginners , intermediate , and advanced SQL learners. These exercises are designed to provide hands-on experience with common SQL tasks, from basic retrieval and filtering to more advanced concepts like joins window functions , and stored procedures.

Table of Content

SQL Exercises for Practice

Sql practice exercises for beginners, sql practice exercises for intermediate, sql practice exercises for advanced, more questions for practice.

Practice SQL questions to enhance our skills in database querying and manipulation. Each question covers a different aspect of SQL , providing a comprehensive learning experience.

We have covered a wide range of topics in the sections beginner , intermediate and advanced .

• Basic Retrieval
• Arithmetic Operations and Comparisons:
• Aggregation Functions
• Group By and Having
• Window Functions
• Conditional Statements
• DateTime Operations
• Creating and Aliasing
• Constraints
• Stored Procedures:
• Transactions

let’s create the table schemas and insert some sample data into them.

Create Sales table

Create Products table

This hands-on approach provides a practical environment for beginners to experiment with various SQL commands, gaining confidence through real-world scenarios. By working through these exercises, newcomers can solidify their understanding of fundamental concepts like data retrieval, filtering, and manipulation, laying a strong foundation for their SQL journey.

1. Retrieve all columns from the Sales table.

Explanation: This SQL query selects all columns from the Sales table, denoted by the asterisk (*) wildcard. It retrieves every row and all associated columns from the Sales table.

2. Retrieve the product_name and unit_price from the Products table.

Explanation:

This SQL query selects the product_name and unit_price columns from the Products table. It retrieves every row but only the specified columns, which are product_name and unit_price.

3. Retrieve the sale_id and sale_date from the Sales table.

This SQL query selects the sale_id and sale_date columns from the Sales table. It retrieves every row but only the specified columns, which are sale_id and sale_date.

4. Filter the Sales table to show only sales with a total_price greater than $100.

This SQL query selects all columns from the Sales table but only returns rows where the total_price column is greater than 100. It filters out sales with a total_price less than or equal to $100.

5. Filter the Products table to show only products in the ‘Electronics’ category.

This SQL query selects all columns from the Products table but only returns rows where the category column equals ‘Electronics’. It filters out products that do not belong to the ‘Electronics’ category.

6. Retrieve the sale_id and total_price from the Sales table for sales made on January 3, 2024.

This SQL query selects the sale_id and total_price columns from the Sales table but only returns rows where the sale_date is equal to ‘2024-01-03’. It filters out sales made on any other date.

7. Retrieve the product_id and product_name from the Products table for products with a unit_price greater than $100.

This SQL query selects the product_id and product_name columns from the Products table but only returns rows where the unit_price is greater than $100. It filters out products with a unit_price less than or equal to $100.

8. Calculate the total revenue generated from all sales in the Sales table.

This SQL query calculates the total revenue generated from all sales by summing up the total_price column in the Sales table using the SUM() function.

9. Calculate the average unit_price of products in the Products table.

This SQL query calculates the average unit_price of products by averaging the values in the unit_price column in the Products table using the AVG() function.

10. Calculate the total quantity_sold from the Sales table.

This SQL query calculates the total quantity_sold by summing up the quantity_sold column in the Sales table using the SUM() function.

11. Retrieve the sale_id, product_id, and total_price from the Sales table for sales with a quantity_sold greater than 4.

This SQL query selects the sale_id, product_id, and total_price columns from the Sales table but only returns rows where the quantity_sold is greater than 4.

12. Retrieve the product_name and unit_price from the Products table, ordering the results by unit_price in descending order.

This SQL query selects the product_name and unit_price columns from the Products table and orders the results by unit_price in descending order using the ORDER BY clause with the DESC keyword.

13. Retrieve the total_price of all sales, rounding the values to two decimal places.

This SQL query calculates the total sales revenu by summing up the total_price column in the Sales table and rounds the result to two decimal places using the ROUND() function.

14. Calculate the average total_price of sales in the Sales table.

This SQL query calculates the average total_price of sales by averaging the values in the total_price column in the Sales table using the AVG() function.

15. Retrieve the sale_id and sale_date from the Sales table, formatting the sale_date as ‘YYYY-MM-DD’.

This SQL query selects the sale_id and sale_date columns from the Sales table and formats the sale_date using the DATE_FORMAT() function to display it in ‘YYYY-MM-DD’ format.

16. Calculate the total revenue generated from sales of products in the ‘Electronics’ category.

This SQL query calculates the total revenue generated from sales of products in the ‘Electronics’ category by joining the Sales table with the Products table on the product_id column and filtering sales for products in the ‘Electronics’ category.

17. Retrieve the product_name and unit_price from the Products table, filtering the unit_price to show only values between $20 and $600.

This SQL query selects the product_name and unit_price columns from the Products table but only returns rows where the unit_price falls within the range of $50 and $200 using the BETWEEN operator.

18. Retrieve the product_name and category from the Products table, ordering the results by category in ascending order.

This SQL query selects the product_name and category columns from the Products table and orders the results by category in ascending order using the ORDER BY clause with the ASC keyword.

19. Calculate the total quantity_sold of products in the ‘Electronics’ category.

This SQL query calculates the total quantity_sold of products in the ‘Electronics’ category by joining the Sales table with the Products table on the product_id column and filtering sales for products in the ‘Electronics’ category.

20. Retrieve the product_name and total_price from the Sales table, calculating the total_price as quantity_sold multiplied by unit_price.

This SQL query retrieves the product_name from the Sales table and calculates the total_price by multiplying quantity_sold by unit_price, joining the Sales table with the Products table on the product_id column.

These exercises are designed to challenge you beyond basic queries, delving into more complex data manipulation and analysis. By tackling these problems, you’ll solidify your understanding of advanced SQL concepts like joins, subqueries, functions, and window functions, ultimately boosting your ability to work with real-world data scenarios effectively.

1. Calculate the total revenue generated from sales for each product category.

This query joins the Sales and Products tables on the product_id column, groups the results by product category, and calculates the total revenue for each category by summing up the total_price.

2. Find the product category with the highest average unit price.

This query groups products by category, calculates the average unit price for each category, orders the results by the average unit price in descending order, and selects the top category with the highest average unit price using the LIMIT clause.

3. Identify products with total sales exceeding $500.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, calculates the total sales revenue for each product, and selects products with total sales exceeding 30 using the HAVING clause.

4. Count the number of sales made in each month.

This query formats the sale_date column to extract the month and year, groups the results by month, and counts the number of sales made in each month.

5. Determine the average quantity sold for products with a unit price greater than $100.

This query joins the Sales and Products tables on the product_id column, filters products with a unit price greater than $100, and calculates the average quantity sold for those products.

6. Retrieve the product name and total sales revenue for each product.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, and calculates the total sales revenue for each product.

7. List all sales along with the corresponding product names.

This query joins the Sales and Products tables on the product_id column and retrieves the sale_id and product_name for each sale.

8. Retrieve the product name and total sales revenue for each product.

This query will give you the top three product categories contributing to the highest percentage of total revenue generated from sales. However, if you only have one category (Electronics) as in the provided sample data, it will be the only result.

9. Rank products based on total sales revenue.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, calculates the total sales revenue for each product, and ranks products based on total sales revenue using the RANK () window function.

10. Calculate the running total revenue for each product category.

This query joins the Sales and Products tables on the product_id column, partitions the results by product category, orders the results by sale date, and calculates the running total revenue for each product category using the SUM() window function.

11. Categorize sales as “High”, “Medium”, or “Low” based on total price (e.g., > $200 is High, $100-$200 is Medium, < $100 is Low).

This query categorizes sales based on total price using a CASE statement. Sales with a total price greater than $200 are categorized as “High”, sales with a total price between $100 and $200 are categorized as “Medium”, and sales with a total price less than $100 are categorized as “Low”.

12. Identify sales where the quantity sold is greater than the average quantity sold.

This query selects all sales where the quantity sold is greater than the average quantity sold across all sales in the Sales table.

13. Extract the month and year from the sale date and count the number of sales for each month.

14. Calculate the number of days between the current date and the sale date for each sale.

This query calculates the number of days between the current date and the sale date for each sale using the DATEDIFF function.

15. Identify sales made during weekdays versus weekends.

This query categorizes sales based on the day of the week using the DAYOFWEEK function. Sales made on Sunday (1) or Saturday (7) are categorized as “Weekend”, while sales made on other days are categorized as “Weekday”.

This section likely dives deeper into complex queries, delving into advanced features like window functions, self-joins, and intricate data manipulation techniques. By tackling these challenging exercises, users can refine their SQL skills and tackle real-world data analysis scenarios with greater confidence and efficiency.

1. Write a query to create a view named Total_Sales that displays the total sales amount for each product along with their names and categories.

This query creates a view named Total_Sales that displays the total sales amount for each product along with their names and categories.

2. Retrieve the product details (name, category, unit price) for products that have a quantity sold greater than the average quantity sold across all products.

This query retrieves the product details (name, category, unit price) for products that have a quantity sold greater than the average quantity sold across all products.

3. Explain the significance of indexing in SQL databases and provide an example scenario where indexing could significantly improve query performance in the given schema.

With an index on the sale_date column, the database can quickly locate the rows that match the specified date without scanning the entire table. The index allows for efficient lookup of rows based on the sale_date value, resulting in improved query performance.

4. Add a foreign key constraint to the Sales table that references the product_id column in the Products table.

This query adds a foreign key constraint to the Sales table that references the product_id column in the Products table, ensuring referential integrity between the two tables.

5. Create a view named Top_Products that lists the top 3 products based on the total quantity sold.

This query creates a view named Top_Products that lists the top 3 products based on the total quantity sold.

6. Implement a transaction that deducts the quantity sold from the Products table when a sale is made in the Sales table, ensuring that both operations are either committed or rolled back together.

The quantity in stock for product with product_id 101 should be updated to 5.The transaction should be committed successfully.

7. Create a query that lists the product names along with their corresponding sales count.

This query selects the product names from the Products table and counts the number of sales (using the COUNT() function) for each product by joining the Sales table on the product_id. The results are grouped by product name using the GROUP BY clause.

8. Write a query to find all sales where the total price is greater than the average total price of all sales.

The subquery (SELECT AVG(total_price) FROM Sales) calculates the average total price of all sales. The main query selects all columns from the Sales table where the total price is greater than the average total price obtained from the subquery.

9. Analyze the performance implications of indexing the sale_date column in the Sales table, considering the types of queries commonly executed against this column.

By comparing the execution plans and analysis results of these queries, we can evaluate the performance implications of indexing the sale_date column. We’ll be able to observe differences in factors such as the query execution time, the type of scan used (sequential scan vs. index scan), and any additional costs associated with using the index.

10. Add a check constraint to the quantity_sold column in the Sales table to ensure that the quantity sold is always greater than zero.

All rows in the Sales table meet the condition of the check constraint, as each quantity_sold value is greater than zero.

11. Create a view named Product_Sales_Info that displays product details along with the total number of sales made for each product.

This view provides a concise and organized way to view product details alongside their respective sales information, facilitating analysis and reporting tasks.

12. Develop a stored procedure named Update_Unit_Price that updates the unit price of a product in the Products table based on the provided product_id.

The above SQL code creates a stored procedure named Update_Unit_Price. This stored procedure takes two parameters: p_product_id (the product ID for which the unit price needs to be updated) and p_new_price (the new unit price to set).

13. Implement a transaction that inserts a new product into the Products table and then adds a corresponding sale record into the Sales table, ensuring that both operations are either fully completed or fully rolled back.

This will update the unit price of the product with product_id 101 to 550.00 in the Products table.

14. Write a query that calculates the total revenue generated from each category of products for the year 2024.

When you execute this query, you will get the total revenue generated from each category of products for the year 2024.

If you’re looking to sharpen your SQL skills and gain more confidence in querying database s, consider delving into these articles. They’re packed with query-based SQL questions designed to enhance your understanding and proficiency in SQL .

By practicing with these exercises, you’ll not only improve your SQL abilities but also boost your confidence in tackling various database-related tasks. The Questions are as follows:

• How to Insert a Value that Contains an Apostrophe in SQL?
• How to Select Row With Max Value in SQL?
• How to Efficiently Convert Rows to Columns in SQL?
• How To Use Nested Select Queries in SQL
• How to Select Row With Max Value on a Column in SQL?
• How to Specify Condition in Count() in SQL?
• How to Find the Maximum of Multiple Columns in SQL?
• How to Update Top 100 Records in SQL?
• How to Select the Last Records in a One-To-Many Relationship Using SQL Join
• How to Join First Row in SQL?
• How to Insert Row If Not Exists in SQL?
• How to Use GROUP BY to Concatenate Strings in SQL?
• How Inner Join works in LINQ to SQL
• How to Get the Identity of an Inserted Row in SQL
• How to Declare a Variable in SQL?

Mastering SQL requires consistent practice and hands-on experience. By working through these SQL practice exercises , you’ll strengthen your skills and gain confidence in querying relational databases.

Whether you’re just starting or looking to refine your expertise, these exercises provide valuable opportunities to hone your SQL abilities. Keep practicing , and you’ll be well-equipped to tackle real-world data challenges with SQL.

Please Login to comment...

Similar reads, improve your coding skills with practice.

What kind of Experience do you want to share?

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

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected]

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail: [email protected]

Top Tutorials

Top references, top examples, get certified.

• How it works
• Homework answers

SQL Assignment Help

Structured Query Language is a powerful computer language for database management. The language is often taught at various levels in learning institutions. All the solutions you need in SQL are available here from our experts.

SQL project problems encountered by programmers:

• many SQL language concepts are difficult to grasp;
• difficulties in debugging applications;
• all SQL projects are time-consuming and demand great precision.

Arranging the contents in an SQL project can be quite cumbersome because of the many language elements that have to be keyed in. Wrong input of the language elements can make the SQL project null and finding what caused the problem can be super difficult. When doing SQL projects, you may do not know which language elements to use. A problem in an SQL assignment may not require the use of computer software to solve it. Much of it may require manual writing of the language elements. This may not be easy for the writer, but we are here to give you a solution to do SQL assignments. Our team of experts will handle your SQL assignment, and you can be sure you will get a high score for your SQL assignment.

Find quick SQL solutions with our help:

• Get working programs designed by expert SQL programmers;
• Learn how to accomplish SQL tasks through original samples;
• Quick assistance with completely custom SQL projects.

If you're asking yourself “Where can I get help to do my SQL assignment?” relax; our company has the best programming experts to ensure that all the SQL assignments are done with the best quality. We provide you with great custom SQL assignments developed from scratch by our professional programming experts, who have full knowledge of all types of SQL programming language, deadline-oriented to deliver your SQL project solutions on time. Our degree-holding experts are aware of all programming languages related to qt. This ensures that the SQL assignments we develop for you are of high quality.

Assignment Expert provides professional and outstanding service with:

• Available representatives of online support (chat and email) 24/7;
• Discounts for regular customers;
• Secure payment methods and 100% confidentiality;
• SQL assignment help for professionals all over the world.

Assignment Expert's services can successfully write codes of different programming languages, including SQL projects. SQL projects from our programming specialists are always correct and precise. Our experts are ready to help anyone do assignments, no matter your specializations or deadlines, as we value our work and expertise. To program everything that you need is our aim, and we always strive for excellence.

• Programming
• Engineering

Who Can Help Me with My Assignment

There are three certainties in this world: Death, Taxes and Homework Assignments. No matter where you study, and no matter…

How to Finish Assignments When You Can’t

Crunch time is coming, deadlines need to be met, essays need to be submitted, and tests should be studied for.…

How to Effectively Study for a Math Test

Numbers and figures are an essential part of our world, necessary for almost everything we do every day. As important…

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.

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

SQL Practice Exercises with Solutions | SQL Queries Practical Exercise

Sql practice exercises with solutions :.

In my previous article i have given the different examples of SQL as well as most important complex sql queries for interview purpose .I would like to combine all those examples and want to make one best article on SQL Practice Exercises with solutions.My main purpose writing this article on SQL Practice Exercises with solution is to get idea about different SQL real world examples as well as user can easily implement it in day to day life.These are the scenarios which are useful for real world industry. I have already written article on real world industry examples of SQL .I want to add some queries from that article also.There are following SQL Practice Exercises with Solutions which are mostly used in day to day life in world of programming.

Important Queries for SQL Practice Exercises with Solutions :

Example 1 : how to create table with same structure with data.

Let us consider that user wants to create a replica of table named ‘Student’.

Create table Student_Replica as Select * from Student;

Explanation :

The above query will create the same table as student named ‘Student_Replica’ with its data.

Example 2 : How to create table with same structure without data?

Let us consider that user wants to create a replica of table named ‘Student’ without having data.

Create table Student_Replica as Select * from Student where 1=2;

The above query will create the same table as student named ‘Student_Replica’ without data.It is possible due to the condition 1=2. The condition 1=2 means True=False which is always False condition.So with using the above query user can create duplicate table structure without data.

Example 3 : How to display Last 10 records from Student table.

There are some situations where user needs to fetch some last records from the table.The following query will fetch the last records from the table.

Select * from Student S where rownum <=10 union select * from (Select * from Student S order by rowid desc) where rownum <=10;

Here we are using simple logic of union and order by the 10 records from student table.

Example 4 : How to fetch first 5 highest marks with Student table.

There are so many examples where user needs to fetch the highest values from the specified table.Following query will fetch the first 5 highest marks from student table.

select min(marks)from(select distinct marks from Student order by marks desc)where rownum<=5;

Explanation:

In above example we are using the rownum concept as well as we are using inner view of descending marks from student table.

Example 5: How to display 1 to 100 numbers with using query.

There are scenarios where user wants to display 1 to 100 numbers with using the query.

Select level from dual connect by level <=100;

In this example user needs to use the concept of hierarchical queries. With using the level attribute of hierarchical query user can display first 100 numbers.

Example 6 : How to find the duplicate row count from specific table.

This example is most important example for real world scenarios.There are so many times where user needs to find out the duplicate row count from the table.

Select Employee_no, count (Employee_no) from Employee Group by Employee_no Having count (Employee_no)>1 Order by count (Employee_no) desc;

In this example we need to use the Count function as well as group by and having. You need to use order by clause as well.

Example 7 : How to delete duplicate rows from the table.

Using above query we find out the duplicate record count from the table. There are situations where user needs to find out the duplicate rows as well as delete those rows. Following query is useful in that case.

Delete FROM Employee WHERE ROWID <> (Select max (rowid) from Employee b where Employee_num=b.Employee_num);

Here we are using the <> operator from SQL to delete duplicate rows from the table.

Example 8 : How user can display following structure with using single SQL Query.

We can not use dual table to perform this operation. We need to use Employee table with data more than 4 to perform this.

SELECT lpad (‘$’, ROWNUM,’$’) FROM Employee WHERE ROWNUM <4;

Here we are using lpad() function to fetch dollar symbol.

Example 9 :How to check for Email is correct or wrong with using single query.

User needs to use regular expression function to check the Email validation with single query.

SELECT Email FROM Employee where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

These are above some SQL Practice Exercises with Solutions which are highly used in real life scenarios.In out SQL Practice Exercises with Solutions we are using the Regular expression function named REGEXP_LIKE to check for Email validation.

FOR ANY SQL SUPPORT CONTACT : [email protected]

Example 10 : How to fetch maximum salary of Employee and minimum salary of Employee together from Employee table.

This is bit tricky question.We can not use the 2 aggregate functions together.

Select max (salary) from Employee

Select min (salary) from Employee;

The example is pretty simple where user needs to use the Set operators from SQL .Here user needs to use union set operator.

Example 11 :  List the Students whose name starts with P and surname starts with S.

The Like operator will help to achieve this,

Select * from Students where name like ‘P%’ and surname like ‘S%’;

The SQL Like special character is most used wildcard to fetch data with specific condition in the table.

Example 12 :How to fetch last record from Student table.

This is also most common SQL Practice Exercises with Solutions where user needs to fetch the last record from the table,

Select * from Student where rowid = select max(rowid) from Student;

The records are stored in to table according to the rowid of the table. So User needs to fetch the maximum of row id record from Student table.

Example 13 : How to fetch all the Student who took admission at 2016.

There are so many scenarios where user needs to fetch the record according to admission year or joining date.

select * from Student where To_char(Joining_date,’YYYY’)=’2016′;

The above example uses the To_Char function to fetch the specific year from the date.

Example 14 : What is query to display odd records from Student table.

This query is also most important and most used SQL Practice Exercises with Solutions to display odd as well as display Even records from the specific table,

Select * from(Select rownum as rno,S.* from Student S) where Mod(rno,2)=1;

In this example user needs to use the Rownum as well as Mod functions to check out the odd records from the table.

Example 15 : What is query to display even records from Student table.

This query is also most important and most used SQL Practice Exercises with Solutions to display even as well as display odd records from the specific table,

Select * from(Select rownum as rno,S.* from Student S) where Mod(rno,2)=0;

In this example user needs to use the Rownum as well as Mod functions to check out the even records from the table.

Example 16 : How to find out manager name and employee name from same table.

This is scenario with self join in SQL.Following query will find out the Manager name with Employee name from Employee table,

Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;

In this query user is using the self join to fetch the records from the table.

These are some most important SQL Practice Exercises with Solution.I will update the examples of SQL Practice Exercises with Solutions on weekly basis so that user will get the best SQL queries for practice.Hope you like this article on SQL Practice Exercises with Solutions.If you like this article or if you have any questions or queries regarding the same kindly comment in to comment section.

4 Replies to “SQL Practice Exercises with Solutions | SQL Queries Practical Exercise”

Hello how many years of experience person can solve above questions.

can you share more questions to below email id [email protected]

Sure… You can solve problems with 5 years experience.

You can check following links :

You can refer this : https://www.complexsql.com/category/this-category-includes-performance-tuning-articles-of-sql/ https://www.complexsql.com/real-time-scenarios-in-sql-queries/#:~:text=There%20are%20real%20world%20situations,needs%20to%20delete%20duplicate%20rows .

https://www.complexsql.com/pl-sql-examples/

hello.. can you share one real time project please so that it can be helpful for us in interview. [email protected] thanks

Hope this helps!

Regards, Amit

Comments are closed.

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

Revising the select query ii easy sql (basic) max score: 10 success rate: 98.68%, select all easy sql (basic) max score: 10 success rate: 99.53%, 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.52%, weather observation station 1 easy sql (basic) max score: 15 success rate: 99.41%, weather observation station 3 easy sql (basic) max score: 10 success rate: 98.00%, weather observation station 4 easy sql (basic) max score: 10 success rate: 98.71%, weather observation station 5 easy sql (intermediate) max score: 30 success rate: 94.37%, cookie support is required to access hackerrank.

Seems like cookies are disabled on this browser, please enable them to open this website

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Where can I find exercises to practice SQL statements? [closed]

Do you know where I can find some practice SQL problems where I can write select statements?

• 1 Look at my comments below the chosen answer. You might get better stuff there than the junk at sql.ru, w3 schools etc. –  Trojan.ZBOT Commented Dec 30, 2013 at 3:38
• HackeRank has a good collection of questions : hackerrank.com/domains/sql/select –  saruftw Commented Feb 20, 2017 at 9:41
• Check out my book SQL Practice Problems , available on Amazon. I wrote it specifically because it's easy to find SQL keyword and syntax info, but difficult to find good real-world practice in writing select statements. –  Sylvia Commented Jun 7, 2017 at 16:24

4 Answers 4

SQL exercises or you can create a test table with fake data and manipulate that. Personally, I learn better with hands-on activity, by playing with the SELECT statements myself before even practicing an online guide. However, not everyone is the same.

Here are a few other links to check out:

• SQLCourse - Interactive for beginners.
• SQLCourse2 - It's awesome you can try and sharpen your skills.

• 2 In one word - yuck ! Only if you can get the databases used, then go for examples like these - google.com/search?q=sql+assignments+with+solutions Or based on the public AdventureWorks DB for MS-SQL server - cuttingedgecourse.com/CIS310/Assignments.htm –  Trojan.ZBOT Commented Dec 30, 2013 at 3:26
• Or search google like this - sql assignment site:.edu –  Trojan.ZBOT Commented Dec 30, 2013 at 3:33
• 1 This website, for example has IMDB (movies) website data and questions - courses.cs.washington.edu/courses/cse444/06wi/hw/hw1.htm data for questions is here - courses.cs.washington.edu/courses/cse444/06wi/hw/IMDB.zip –  Trojan.ZBOT Commented Dec 30, 2013 at 3:35
• 3 This is the most complex query in page 10 of the "advanced course" - SELECT customers.customerid, customers.firstname, customers.lastname, items_ordered.order_date, items_ordered.item, items_ordered.price FROM customers, items_ordered WHERE customers.customerid = items_ordered.customerid; Glance these sqlCourse links, but don't expect to get any real knowledge out of them. –  Trojan.ZBOT Commented Dec 30, 2013 at 4:19

Look the line of books by Celko, it has lots of examples and exercises.

You can find some here as well.

• 1 Although really, I would download SQL Server Express, download the AdventureWorks db, and get a beginning SQL book like "Beginning SQL" –  davecoulter Commented Mar 9, 2010 at 23:46

Try SQL Exercises . Free and interactive.

• 2 Already posted in accepted answer. –  ctomek Commented Feb 18, 2017 at 20:29

Not the answer you're looking for? Browse other questions tagged sql or ask your own question .

• Featured on Meta
• Upcoming sign-up experiments related to tags
• The return of Staging Ground to Stack Overflow
• Should we burninate the [lib] tag?
• Policy: Generative AI (e.g., ChatGPT) is banned
• What makes a homepage useful for logged-in users

Hot Network Questions

• George Martin story about a war in which he mentions airplanes called Alfies (meaning Alphas, I think)
• Do IDE data lines need pull-up resistors?
• Aligning definition of terms of a sequence
• Why are there no Goldstone modes in superconductor?
• Cleaning chain a few links at a time
• Synthesis of racemic nicotine
• Why did Geordi have his visor replaced with ocular implants between Generations and First Contact?
• Tombs of Ancients
• Have children's car seats not been proven to be more effective than seat belts alone for kids older than 24 months?
• Trying to determine what this item is
• How do I pour *just* the right amount of plaster into these molds?
• Applying Bayesian probability to a generalized Monty Hall problem
• What could explain that small planes near an airport are perceived as harassing homeowners?
• Is physics limited to smooth sets?
• "All due respect to jazz." - Does this mean the speaker likes it or dislikes it?
• what is the difference between prayer (προσευχῇ) and prayer also translated as supplication (δέησις) in Philippians 4:6?
• In By His Bootstraps (Heinlein) why is Hitler's name Schickelgruber?
• Should mail addresses for logins be stored hashed to minimize impact of data loss?
• Is Légal’s reported “psychological trick” considered fair play or unacceptable conduct under FIDE rules?
• Stiff differential equation
• Should I accept an offer of being a teacher assistant without pay?
• What to fill in the document number while applying for Schengen visa "C"?
• What kind of sequence is between an arithmetic and a geometric sequence?
• How to model an optimization problem with mutual exclusivity of two variables, without introducing integer variables?

18 SQL Questions for Beginners: Theory and Practice

• sql practice

Table of Contents

SQL Practice for Beginners

Question 1: elements of an sql query, question 2: filtering data in an sql query, data for questions 3 – 6, question 3: select cats of a given age and breed, question 4: list cats whose favorite toy is a ball, question 5: find the most bored cat, question 6: select cats that love teaser toys, question 7: the role of join, question 8: types of joins, data for questions 9 – 12, question 9: find artists born after 1800 and the art they created, question 10: select all pieces of art and their location, question 12: list pieces of art created by unknown artists, question 13: aggregate functions and the role of group by, question 14: where vs. having, data for questions 15 – 18, question 15: calculate the average production cost of good games, question 16: provide game production statistics by year, question 17: calculate the gross profit per company, question 18: identify good games, get more basic sql practice.

Whether you're starting or refreshing your SQL skills, join us as we work through these 18 SQL practice questions for beginners.

SQL, or Structured Query Language, is a programming language used to define, retrieve, and manipulate data in relational databases. It provides an intuitive syntax of SQL statements and keywords that create, modify, and query relational databases.

This article focuses on reviewing and practicing the basics of SQL. We’ll start by reviewing the SELECT statement and its required and optional components for fetching data from a single table. Following that, we’ll delve into JOINs , which allow us to merge data from two or more tables. Finally, we’ll demonstrate how to aggregate and group data to perform more advanced analysis. This can help you review your SQL knowledge before an interview or a test – or simply refresh and consolidate your skills.

This article showcases SQL practice exercises from our interactive  SQL Practice Set course. The course offers over 80 hands-on practice exercises that cover different SQL topics: single table queries, joins, aggregation and grouping, subqueries, and more.  If you want to practice more on your own, we encourage you to check out our SQL Practice track.

All our SQL practice courses provide exercises based on real-world datasets, so you can practice SQL in realistic scenarios. The courses are grouped into different topics – e.g. single table queries, joins, aggregation and grouping, and subqueries – so you can choose what you want to practice.

Let’s get started.

The SQL practice exercises in this article cover the basics of querying data. We’ll review:

• Single table queries – Querying data from a single table using the SELECT statement.
• JOINs – Joining data from multiple tables using various JOINs.
• Aggregating and grouping data – Putting data into groups based on defined columns and compiling statistics.

Single Table Queries

We’ll start by reviewing the basics of querying data from a single table and imposing custom conditions on data columns.

List all elements in an SQL query.

The SELECT statement consists of the following components:

• SELECT column_name(s) – Defines the data columns shown in the output.
• FROM table_name – Defines the database table from which data is selected.
• WHERE column_name = value – Filters the output data based on stated conditions (optional).
• GROUP BY column_name(s) – Groups data based on distinct values (optional).If you’re using aggregate functions , you must use the GROUP BY clause.
• HAVING – Filters data after it has been processed by GROUP BY (optional); you can use this to impose conditions on aggregate functions.
• ORDER BY column_name [ASC | DESC] – Orders output data by a defined column in ascending or descending order (optional).

Both the SELECT and FROM clauses are easy to grasp, as SELECT lists data columns and FROM defines the data table. In the case of the WHERE clause, there are a variety of conditions you can impose on columns, which we’ll review in the next question.

You can read more about the basic query elements in our article Enumerate and Explain All the Basic Elements of an SQL Query .

These are the elements of an SQL query in order of appearance: SELECT , FROM , WHERE , GROUP BY , ORDER BY , and HAVING .

How do you filter data in an SQL query using custom conditions?

To impose custom conditions on data columns, we use the WHERE clause. For example, if you want to select people older than 18, use the WHERE clause as follows:

The WHERE clause conditions typically involve comparisons or logical operations and depend on the data type stored in the column.

• Numeric data types: =, <> or !=, >, <, >=, <=
• Text/string data types: =, <> or !=, LIKE, IN, NOT LIKE, NOT IN
• Date and time data types: =, <> or !=, >, <, >=, <=, BETWEEN, NOT BETWEEN
• Boolean data types: =, <> or !=
• Operators used to check for NULL values: IS NULL, IS NOT NULL
• Logical operators used to combine multiple conditions: AND, OR, NOT

You can read more about filtering data in our articles How to Write a WHERE Clause in SQL and Using AND, OR, and NOT Operators in SQL .

The WHERE clause is used to filter data by imposing conditions on data columns.

In the exercises 3 – 6, we’ll use the cat table. It has the following columns:

• id – The id of a given cat.
• name – The cat’s name.
• breed – The cat’s breed (e.g. Siamese, British shorthair, etc.).
• coloration – The cat’s coloration (e.g. calico, tabby, etc.).
• age – The cat’s age.
• sex – The cat’s sex.
• fav_toy – The cat’s favorite toy.

Select the ID and name for every Ragdoll cat that is either 1) younger than five years old, or 2) older than ten years old.

Explanation:

As the instruction says, we select the id and name columns from the cat table.

Next, we use the WHERE clause to impose conditions:

• On the age column:

We want to select cats that are younger than 5 ( age < 5 ) or older than 10 ( age > 10 ), so we use the OR keyword and enclose both conditions in parentheses.

Why do we need parenthesis? Well, we want to impose this composite condition on the age column. What if we do not include parenthesis? The parenthesis will be implicitly imposed on the last two conditions, like this: age < 5 OR (age > 10 AND breed = 'Ragdoll') . This will cause an incorrect result.

• On the breed column:

We want to select cats of the Ragdoll breed; therefore, we simply define the condition as breed = 'Ragdoll' . Note that text values in SQL are enclosed in single quotes (').

This exercise demonstrates a composite condition that uses logical operators ( AND , OR ) and mathematical comparison operators ( < , > , = ).

Select all data for cats whose breed starts with 'R', whose favorite toy starts with 'ball', and whose coloration ends with an 'm'.

Here, we select all data columns ( * ) from the cat table.

We want to impose conditions on the literal values of the breed , colorations , and fav_toy columns. To do that, we’ll use pattern matching; in SQL, % is a wildcard character that stands for any sequence of characters.

The breed column value should start with an 'R'. Therefore, we use a pattern that indicates a value starting with 'R' and followed by any number of characters (defined by % ). If we want to impose such a condition on a literal value, we must use the LIKE keyword: breed LIKE 'R%' .

Similarly, we want the favorite toy name to start with 'ball'; therefore, the condition is fav_toy LIKE 'ball%' .

And it’s the same again for the coloration column. We want the literal value to end with an 'm', so the % character goes in front: coloration LIKE '%m' .

You can read more about using the LIKE operator in our articles What Do the Operators LIKE and NOT LIKE Do? and How to Use LIKE in SQL .

Select the names of all male cats that don't have a favorite toy – that is, the value of the field fav_toy is NULL .

As the instruction says, we select the name column from the cat table.

We want to select only male cats; therefore, we define a condition on the sex column as sex = 'M' . You need to be familiar with the data stored in the cat table to define this condition – i.e. to know that the sex column stores the value ‘F’ for female cats and ‘M’ for male cats.

As we’re looking for the most bored cat, we need to define a condition that says the fav_toy column must have no value, or be NULL . We do this with fav_toy IS NULL .

Working with NULLs is quite complicated in SQL. For more details, we recommend the articles:

• What Is a NULL in SQL?
• How to Use Comparison Operators with NULLs in SQL
• How to Find Records with NULL in a Column
• How to Filter Rows Without NULL in a Column

Select the ID, name, breed, and coloration of all cats that:

• Are females.
• Like teaser toys,
• Are not of the Persian or Siamese breeds.

In this exercise, we select the id , name , breed , and coloration columns from the cat table. Then we impose the following conditions:

• On the sex column: We want to select female cats; hence, the condition is sex = 'F' .
• On the fav_toy column: We want to find cats that like teaser toys, so the condition is fav_toy = 'teaser' .
• On the breed column: We want to select any breed except for Persian and Siamese. To do that, we use the NOT LIKE keyword and enclose the entire composite condition in parenthesis ( breed NOT LIKE 'Persian' AND breed NOT LIKE 'Siamese' ).

Great work! You’ve completed the section on selecting data from a single table with various filter conditions. Let’s move on to working with multiple tables.

Data from Multiple Tables: SQL JOINs

Now you know how to select data from a single table. But what if we want to select data from two or more tables? We need to join these tables based on common column values. This is where JOIN operations come into play.

What does JOIN do in SQL?

The JOIN clause is used to combine data from two or more tables.

You can use as many JOINs as you need. Below, we use two JOINs to combine data from three tables:

When joining tables, it’s best to use alias names for each table (here, t1 , t2 , and  t3 ). These alias names are used to refer to columns from each table.

To find out more about SQL JOINs, see our articles SQL INNER JOIN Explained in Simple Words and How to Join Two Tables in SQL .

JOINs are used to combine data from multiple tables.

List all types of JOINs available in SQL and briefly describe each one.

There are four types of JOINs: [INNER] JOIN , RIGHT JOIN , LEFT JOIN , and FULL [OUTER] JOIN . Each one provides different results.

A JOIN , also known as an INNER JOIN , is the most common type of join. It returns only the matching records from two or more tables.

A LEFT JOIN returns all the records from the left (first) table and the matching records from the right (second) table. If there are no matches in the right table, null values are included in the result set.

Read What Is a LEFT JOIN in SQL? for more details.

A RIGHT JOIN returns all the records from the right (second) table and the matching records from the left (first) table. If there are no matches in the left table, null values are included in the result set.

A FULL JOIN , also known as a FULL OUTER JOIN , returns all the records from both the left and right tables. It includes matching records from both tables and uses null values for non-matching records.

Read this article to learn more about FULL JOINs .

In summary, LEFT JOIN and RIGHT JOIN focus on one table as the primary source of data, while a FULL JOIN combines all the records from both tables. The choice of which JOIN to use depends on the specific data retrieval needs and the relationship between the tables involved.

To read more about different JOIN types, we recommend our articles SQL JOINs and SQL JOIN Types Explained . Our SQL JOIN Cheat Sheet summarizes the syntax of different types of  JOINs.

JOIN types include [INNER] JOIN , LEFT JOIN , RIGHT JOIN , and FULL [OUTER] JOIN .

In exercises 9 – 12, we’ll use the Museum dataset that consists of three tables.

The artists table contains the following columns:

• id – The database ID for a given artist.
• name – The artist’s name.
• birth_year – The year the artist was born.
• death_year – The year the artist
• artistic_field – That artist’s primary field (e.g. watercolor painting, sculpture, oil painting).

The museum table contains the following columns:

• id – The ID of a given museum.
• name – The museum’s name.
• country – The country where the museum is located.

The piece_of_art table contains the following columns:

• id – The ID of a given piece of art.
• name – The piece’s name.
• artist_id – The ID of the artist who created this piece.
• museum_id – The ID of the museum that has this piece in its collection.

For each artist who was born after the year 1800 and lived for more than 50 years, show their name and the name of the pieces of art they created. Rename the columns as artist_name and piece_name , respectively.

We select artist names (aliased as artist_name ) along with pieces of art they created (aliased as piece_name ). Therefore, we must join the artist table (aliased as a ) with the piece_of_art table (aliased as poa) on their common column that stores artist IDs ( ON a.id = poa.artist_id ).

We want to consider only artists who lived for more than 50 years. To define this condition, we’ll use the birth_year and death_year columns from the artist table as follows:

Also, we want to list artists born after 1800: birth_year > 1800 .

Check out this article about joining two tables in SQL to learn more.

Select the names of all pieces of art together with the names of the museums that house them and the countries in which these museums are located. Also show lost pieces of art (those without an associated museum).

As we want to select the names of art pieces and the names and countries of museums, we must join the piece_of_art table (aliased as poa ) with the museum table (aliased as m ) on the museum ID column ( ON poa.museum_id = m.id ).

We need to show all pieces of art, including the ones that are lost. Note that the lost pieces of art do not have any museum assigned. Therefore, we require a specific type of JOIN that selects all data from the piece_of_art table, regardless of whether it has any matching records in the museum table:

This LEFT JOIN ensures that we select all rows from the left table (here, piece_of_art ).

Check out this article on LEFT JOIN to learn more.

Question 11: List All Pieces of Art

Show the names of all pieces of art together with the names of their creators and the names of the museums that house these pieces of art. Omit lost works and pieces of art with an unknown artist. Name the columns piece_of_art_name, artist_name , and museum_name .

Here we select names of artists from the artist table, names of museums from the museum table, and names of art pieces from the piece_of_art table. Hence, we must join all three tables on their common columns:

• We join the museum table with the piece_of_art table on museum ID values.
• We join the artist table with the piece_of_art table on artist ID values.

Once we’ve joined all three tables, we can select the output values.

Note that we want to omit art pieces that do not have any museum or any artist assigned. Therefore, we use the standard JOIN (or INNER JOIN ) that joins data from tables only when there is a match in the column on which the JOIN is performed.

Follow this article on how to join 3 or more tables to learn more.

Check whether any pieces were created by unknown artists. Show the names of these pieces together with the names of the museums that house them.

We want to show the names of ‘unknown artist’ pieces along with the names of museums where the pieces are located. Hence, we join the piece_of_art table (aliased as poa ) with the museum table (aliased as m ) on the museum ID column ( ON poa.museum_id = m.id ).

As we’re looking for art pieces created by unknown artists, we include the following condition in the WHERE clause: poa.artist_id IS NULL .

Grouping and Aggregating Data

Aggregation and grouping are techniques used to organize data into groups based on defined criteria and perform calculations on the groups.

List the available aggregate functions and explain the role of the GROUP BY clause.

Aggregation involves applying mathematical operations to a set of values in a column. The more commonly used aggregate functions include SUM() , AVG() , COUNT() , MAX() , and MIN() .

For example, imagine a table that stores monthly sales values:

You can use the SUM() aggregate function to get the total sales, like this:

The output is as follows:

When we’re aggregating data, we also often segment data into groups based on distinct values in the column that is used to group data.

Grouping involves creating groups of data based on values in column(s) given as arguments to the GROUP BY clause.

For example, imagine you want to select sales per year. To do this, you have to group data by the year, like this:

If the column on which we group data has five distinct values, data will be grouped into five groups.

We recommend this article if you want to learn more about the GROUP BY clause.

Aggregation is about performing calculations on a set of values and grouping is about organizing data into groups based on specific criteria.

What is the difference between WHERE and HAVING ?

Both WHERE and HAVING are used to filter data by imposing certain conditions.

The difference is that WHERE is used to impose conditions on data columns (as you’ve seen in the Single Table Queries section) and HAVING is used to impose conditions on aggregate functions (as you’ll see in this section).

Read this article on WHERE vs. HAVING to learn more about the differences between these two clauses.

WHERE imposes conditions on columns. HAVING imposes conditions on aggregate functions.

In exercises 15 – 18, we’ll use the games table. It consists of the following columns:

• id – The ID of a given game.
• title – The game’s name (e.g. ‘Super Mario Bros’).
• company – The name of the company that makes this game (e.g. ‘Nintendo’).
• type – The type of game (e.g. ‘arcade’).
• production_year – The year when the game was created.
• system – The system for which a game was released (e.g. ‘NES’).
• production_cost – The cost of producing the game.
• revenue – The revenue generated by the game.
• rating – The rating given to this game.

Show the average production cost of games that were produced between 2010 and 2015 and were rated higher than 7.

To select the average production cost of games, we use the AVG() aggregate function on the production_cost column. This function takes all values present in the production_cost column and calculates the average.

As we are interested in games produced between 2010 and 2015, we must include this condition in the WHERE clause: production_year BETWEEN 2010 AND 2015 . That sounds just like plain English!

Also, we want to include only games with a rating higher than 7, so we add another condition in the WHERE clause: AND rating > 7 .

Check out this article on the AVG() function to see more examples.

For each year:

• Display the year ( production_year ).
• Count the number of games released in this year (name this count ).
• Show the average cost of production (as avg_cost ) for these games.
• Show the average revenue (as avg_revenue ) of these games.

We want to display different statistics per year; therefore, we need to GROUP BY production_year .

As we select from the games table, we use the COUNT() aggregate function to count games released per year. We use * as an argument because we want to count all rows (not values of a specific column). We alias it AS count .

Next, we want to display the average cost of production: AVG(production_cost). We alias it AS avg_cost.

Finally, we show the average revenue: AVG(revenue). We alias it AS avg_revenue.

For all companies present in the games table, show their name and their gross profit over all years. To simplify this problem, assume that the gross profit is equal to revenue minus the production cost of all games; name this column gross_profit_sum . Order the results so the company with the highest gross profit is first.

We select the company column from the games table. For each company, we sum the gross profit values ( revenue - production_cost ) produced by each game created by this company.

As we want to see the sum of gross profit per company, we must GROUP BY company. However, in this case, we use a different syntax: GROUP BY 1 , which means that we want to GROUP BY the 1 st column listed in SELECT .

Finally, we order the output in descending order based on the gross profit values per company.

We're interested in good games produced between 2000 and 2009. A good game has a rating higher than 6 and was profitable. For each company, show:

• The company name.
• The total revenue from good games produced between 2000 and 2009 (as the revenue_sum column).
• The number of good games the company produced in this period (as the number_of_games column).

Important: Only show companies with good-game revenue over 4,000,000.

This one is a bit trickier, as we need to create a query that uses WHERE , HAVING , aggregate functions, and grouping.

Let’s analyze the instructions step by step and translate it into SQL code.

WHERE -related instructions:

• games produced between 2000 and 2009 results in this condition being added to the WHERE clause: WHERE production_year BETWEEN 2000 AND 2009
• games rated higher than 6 results in this condition being added to the WHERE clause: AND rating > 6
• games that were profitable results in this condition being added to the WHERE clause: AND revenue - production_cost > 0 Remember, a profitable game means that the revenue is higher than the cost of production.

SELECT -related instructions:

• show company name results in this column being added to the SELECT statement: SELECT company
• show its total revenue (as revenue_sum ) results in this column being added to the SELECT statement: SUM(revenue) AS revenue_sum
• show the number of good games ( number_of_games ) results in this column being added to the SELECT statement: COUNT(company) AS number_of_games

GROUP BY- and HAVING -related instructions:

• for each company means that we calculate the statistics ( COUNT() and SUM() ) on a company So, we must group the data by company: GROUP BY company
• show companies with good-game revenue over 4,000,000 result in this condition being added to the HAVING clause: HAVING SUM(revenue) > 4000000

That’s how we dissected the instructions and translated them into SQL code.

This article covered the basics of SQL queries, including how to filter data, join multiple tables, order and sort output, and aggregate and group data.

Have you enjoyed the SQL practice exercises so far? All these exercises come from our SQL Practice Set course. For more SQL exercises, check out these LearnSQL.com practice courses:

• Basic SQL Practice: A Store
• Basic SQL Practice: University
• Basic SQL Practice: Blog & Traffic Data
• Basic SQL Practice: Run Track Through Queries!

You can buy each of these courses individually, or you can purchase our All Forever SQL package . It covers all 70+ SQL courses offered on our platform, including these practice courses, and all new courses we’ll add in the future.

And remember, practice makes perfect. Good luck on your SQL journey!

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

• ▼MySQL Exercises
• Introduction
• ▼DML and DDL
• Create Table statement
• Insert Into statement
• Update Table statement
• Alter Table statement
• ▼Exercises on HR Database
• Basic SELECT statement
• Restricting and Sorting Data
• MySQL Aggregate Functions
• ▼Exercises on Northwind Database
• Products Table

MySQL Exercises, Practice, Solution

What is mysql.

MySQL is the world's most widely used open-source relational database management system (RDBMS), enabling the cost-effective delivery of reliable, high-performance and scalable Web-based and embedded database applications. It is widely-used as the database component of LAMP (Linux, Apache, MySQL, Perl/PHP/Python) web application software stack.

The best way we learn anything is by practice and exercise questions. We have started this section for those (beginner to intermediate) who are familiar with SQL and MySQL . Hope, these exercises help you to improve your MySQL query skills. Currently following sections are available, we are working hard to add more exercises. Happy Coding!

Exercises on Data Manipulation Language (DML) & Data Definition Language (DDL)

• MySQL Create Table statement [20 Exercises]
• MySQL Insert Into statement [14 Exercises]
• MySQL Update Table statement [9 Exercises]
• MySQL Alter Table statement [15 Exercises]

Exercises on HR Database

• MySQL Basic SELECT statement [19 Exercises]
• MySQL Restricting and Sorting Data [11 Exercises with Solutions]
• MySQL Aggregate Functions and Group by [14 Exercises with Solutions]
• MySQL Subqueries [22 Exercises with Solution ]
• MySQL JOINS [13 Exercises with Solution]
• MySQL Date Time [21 Exercises with Solution]
• MySQL String Functions [17 Exercises with Solution]

Exercises on Northwind Database

• Exercises on Products Table [ 10 Exercises]

More to Come !

Structure of 'hr' database :

You may download the structure and data of the database used here

Click here to get Oracle Hr Database

Structure of 'northwind' database:

Click here to get Northwind sample databases for Microsoft SQL Server.

Follow us on Facebook and Twitter for latest update.

• Weekly Trends and Language Statistics