sql online assignments

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.

• SQL Cheat Sheet
• SQL Interview Questions
• MySQL Interview Questions
• PL/SQL Interview Questions
• Learn SQL and Database

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.

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 30.

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.

Query without indexing:.

Query with Indexing:

This format clearly displays the operations and details of the query execution plan before and after indexing.

Without indexing, the query performs a full table scan, filtering rows based on the sale date, which is less efficient. With indexing, the query uses the index to quickly locate the relevant rows, significantly improving query performance.

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.

• How to Get a Free SSL Certificate
• Best SSL Certificates Provider in India
• Elon Musk's xAI releases Grok-2 AI assistant
• What is OpenAI SearchGPT? How it works and How to Get it?
• Content Improvement League 2024: From Good To A Great Article

Improve your Coding Skills with Practice

What kind of Experience do you want to share?

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.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.52%, 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: 98.04%, weather observation station 4 easy sql (basic) max score: 10 success rate: 98.72%, weather observation station 5 easy sql (intermediate) max score: 30 success rate: 94.43%, cookie support is required to access hackerrank.

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

• SQL Worksheet SQL Worksheet
• Previous Sessions
• Previously Viewed
• Utilization
• Schema Schema
• My Scripts My Scripts
• My Tutorials My Tutorials
• Code Library Code Library

Learn and share SQL

Running on Oracle Database 19c

Featured Scripts and Tutorials

Introduction to sql.

This tutorial provides an introduction to the Structured Query Language (SQL), learn how to create tables with primary keys, columns, constraints, ind...

SQL Macros - Creating parameterised views

This tutorial explains how to create a parameterized view using SQL Macros. The examples use the built-in sales history schema so there are no setup s...

How to Find Gaps in Dates with SQL Pattern Matching

This shows you how to find gaps in rows containing start/end dates using match_recognize. You can make this a reusable SQL fragment by placing this in...

Simple Explain Plan

This script explains the plan for a query of the sh.sales and sh.products tables. Both statements must be executed at the same time in order to get t...

19C LISTAGG DISTINCT

The LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword. The LISTAGG aggregate function orders the rows...

How to Make Reusable SQL Pattern Matching Clauses with SQL Macros

An overview of how to combine SQL pattern matching with SQL macros to create reusable code fragments.

2,247,855 scripts, 6,341 likes, 1,078 published scripts, 7,110 new scripts created in the last 7 days.

Advertisement

• 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 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 Exercises
• Introduction
• Retrieve data from tables
• Boolean and Relational Operators
• Wildcard and Special operators
• Aggregate Functions
• Formatting query output
• Query on Multiple Tables
• FILTERING and SORTING on HR Database
• SUBQUERIES on HR Database
• JOINS on HR Database
• SQL User Management
• ▼Movie Database
• BASIC QUERIES
• ▼Soccer Database
• ▼Hospital Database
• ▼Employee Database
• ▼AdventureWorks Database
• ▼SQL Challenges
• Challenges-1
• ..More to come..

SQL Exercises, Practice, Solution

What is sql.

SQL stands for Structured Query Language and it is an ANSI standard computer language for accessing and manipulating database systems. It is used for managing data in relational database management system which stores data in the form of tables and relationship between data is also stored in the form of tables. SQL statements are used to retrieve and update data in a database.

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 . Hope, these exercises help you to improve your SQL skills. Currently following sections are available, we are working hard to add more exercises. Happy Coding!

You may read our SQL tutorial before solving the following exercises.

List of SQL Exercises

• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• BASIC, SUBQUERIES, and JOINS [41 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• AdventureWorks Database:
• AdventureWorks Database [200 Exercises]
• SQL Challenges-1:
• SQL Challenges-1 [77 Exercises]
• More to come!

Structure of inventory database :

Structure of HR database :

Structure of movie database :

Structure of soccer database :

Structure of employee database :

Structure of hospital database :

Syntax diagram of SQL SELECT statement

You may download the structure and data of the tables of database on which SQL Exercises are built.

Please note that PostgreSQL 9.4 is used and the file which you would download is generated using pg_dump

Follow us on Facebook and Twitter for latest update.

• Weekly Trends and Language Statistics

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 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.

Online SQL Editor

Available tables.

MySQL Practice: Best Exercises for Beginners

• online practice

Table of Contents

A Note on Our MySQL Practice Exercises

Dataset: cats, exercise 1: list all cats, exercise 2: select younger cats, exercise 3: list all ragdoll cats, exercise 4: select cats whose favorite toy is a ball, exercise 5: find older cats with a favorite toy, dataset: games, exercise 6: order data by cost and rating, exercise 7: order high-rated games by production cost, exercise 8: count games produced per year, exercise 9: count profitable games by type and company, exercise 10: list the number of games and average cost, dataset: employees, exercise 11: list salary grades for all employees, exercise 13: list all benefits for employee 5, exercise 12: show employees and direct managers, exercise 14: show benefits and how many employees receive them, exercise 15: show benefits not received by any employee, embrace the power of mysql practice exercises.

These 15 MySQL exercises are especially designed for beginners. They cover essential topics like selecting data from one table, ordering and grouping data, and joining data from multiple tables

This article showcases beginner-level MySQL practice exercises, including solutions and comprehensive explanations. If you need to practice …

• Selecting data from one table
• Ordering and grouping data
• Joining data from multiple tables

… these 15 tasks are just for you!

Most of the exercises come from our SQL Practice Set in MySQL , which provides a complete training environment where you can run queries and see the output data. Upon enrolling in this course, you’ll have access to 88 interactive beginner-friendly exercises to get you started towards SQL mastery.

While doing the exercises in this article, you may find this MySQL Cheat Sheet helpful. It contains a quick reference guide for MySQL syntax.

Continuous practice makes perfect, so the only way to learn and master your SQL skills is through practice. Following and solving practice exercises is the key to perfecting your SQL skills.

Let’s get started.

The exercises in the following sections use different datasets and cover distinct topics, including selecting data from one table, ordering and grouping data, and joining data from multiple tables.

Each section contains five exercises that include the following:

• The text of the exercise, which describes what data to fetch from the database.
• The solution query.
• A discussion on how the solution works and how the query was constructed.

We encourage you to solve the exercise on your own before looking at the solution. If you like these exercises, sign up for our SQL Practice Set in MySQL course and solve all 88 exercises!

Section 1: Selecting Data from One Table

We’ll start with the most basic SQL syntax, which is the SELECT statement. It selects data from a single table. You can use the WHERE clause to filter data based on defined conditions.

Let’s introduce the dataset and get to the MySQL practice exercises.

In this section, we’ll use the cat table. It consists of the following columns:

• id – The ID number of a given cat.
• name – The cat’s name.
• breed – The cat’s breed (e.g. Siamese, Ragdoll, etc.).
• coloration – The cat’s coloration.
• age – The cat’s age.
• sex – The cat’s sex.
• fav_toy – The cat’s favorite toy.

Select all data from the cat table.

Explanation:

We use the SELECT statement to define the columns to be outputted by the query. Here, we want to select all columns, so we use the asterisk character ( * ), which stands for all columns.

The FROM clause specifies the name of the table from which data is extracted.

It works just like we’d say it – we select all the columns from the cat table.

Select the name, breed, and coloration for every cat that is younger than five years old.

Again we use the SELECT statement to define the columns to be output by the query – here, the name , breed , and coloration columns.

It is followed by the FROM clause, which tells the database to get the data from the cat table.

Finally, we define the condition in the WHERE clause. As we want to select only cats that are younger than five years old, we impose a condition on the age column to be less than 5: WHERE age < 5 .

Select the ID and name for every cat that is of the Ragdoll breed.

We select the id and name columns from the cat table.

Next, we define a condition in the WHERE clause. As we want to list all cats of the Ragdoll breed, we impose a condition on the breed column to be equal to Ragdoll: WHERE breed = 'Ragdoll' . Note that in SQL we must enclose text strings in single quotation marks.

Select all data for cats whose:

• Breed starts with an 'R'.
• Favorite toy starts with the word 'ball'.
• Coloration name ends with an 'm'.

We use the SELECT statement to select all data. The asterisk character ( * ) stands for all columns. Therefore, SELECT * selects all columns available in the cat table, which is defined in the FROM clause.

Here, we define multiple conditions on different columns. We use the percentage character ( % ), which is a wildcard that stands for any sequence of characters.

• The breed column value must start with an 'R' followed by any sequence of characters: breed LIKE 'R%' .
• The fav_toy column value must start with the word 'ball' followed by any sequence of characters: fav_toy LIKE 'ball%' .
• The coloration column value must end with an 'm' preceded by any sequence of characters: coloration LIKE '%m' .

Since all these conditions must be applied at the same time, we combine them in the WHERE clause using the AND operator. This ensures that all conditions hold true for the selected data rows.

Select all data for cats that are:

• More than 10 years old.
• Either of the Ragdoll or Abyssinian breeds.
• Have a known favorite toy.

Again we select all columns from the cat table using an asterisk character ( * ) listed along the SELECT statement. Then we impose the conditions on the columns:

• We want to select cats that are older than 10, so we impose a condition on the age column: age > 10 .
• We want to select cats that are of Ragdoll or Abyssinian breeds, so we impose a condition on the breed column: ( breed = 'Ragdoll' OR breed = 'Abyssinian' ). Please note that this is a composite condition and we must enclose it in parentheses. The two parts are combined with the OR operator because we want to select either Ragdoll or Abyssinian cats.
• We want to select cats that have a favorite toy. Therefore, we impose a condition on the fav_toy column: fav_toy IS NOT NULL . We use the IS NOT NULL expression to ensure that selected cats have a favorite toy assigned.

To apply all three conditions at the same time, we combine them using the AND operator.

Section 2: Ordering and Grouping Data

Now that we’ve recalled how to query data from a table and filter it by imposing different conditions, let’s move on to ordering, aggregating, and grouping data.

In this section, we’ll use the games table, which has the following columns:

• id – The ID of a given game.
• title – The game’s title.
• company – The name of the company that produced the game.
• type – The game’s genre.
• production_year – The year when the game was created.
• system – The system on which the game is played.
• production_cost – The cost of producing this game.
• revenue – The revenue generated by the game.
• rating – The game’s

Select all data from the games table and order the results by the production cost from cheapest to most expensive. If multiple games have the same production cost, order the results by ratings, from best to worst.

We select all columns from the games table: SELECT * FROM games .

Next, we order the output data using the ORDER BY clause and the production_cost column. As we want it to be in ascending order, we follow the column name with the ASC keyword: ORDER BY production_cost ASC .

The secondary column by which we want to order the output data is the rating column. To order the data from best to worst rating (that is, in descending order), we use the DESC keyword: rating DESC .

Show the production cost values of games that were produced between 2010 and 2015 and were rated higher than 7. Order data by the cheapest to the most expensive production cost.

We select the production_cost column from the games table.

As we want to select only games produced between 2010 and 2015 and rated higher than 7, we impose conditions on the production_year and rating columns and combine them with the AND operator: production_year BETWEEN 2010 AND 2015 AND rating > 7 .

Finally, we order the output data by the production_cost column. We add the ASC keyword (which stands for ascending)  to order the data from cheapest to most expensive production costs.

For each year, display:

• The year ( production_year ).
• How many games were released in this year (as the count column).
• The average production costs per game for that year (as the avg_cost column).
• The average revenue per game for that year (as the avg_revenue column).

We select the following from the games table:

• The production_year
• The count of all games produced in that We use the COUNT() aggregate function , passing an asterisk ( * ) as its argument, to count all rows.
• The average production cost in that We use the AVG() aggregate function, passing the production_cost column as its argument, to calculate the average production cost.
• The average revenue within a given year. We use the AVG() aggregate function, passing the revenue column as its argument, to calculate the average revenue.

As we want to calculate these statistics (count and average) for each year, we need to group all data by the production_year column. We use the GROUP BY clause, passing production_year as its argument, so the data is put into as many groups as there are distinct values in the production_year column.

Count how many games of a given type and produced by a given company turned out to be profitable (their revenue was greater than their production cost). Show the number of games (as number_of_games ) and the company and type columns.

We select the company and type columns and the count of all games of each type produced by a given company. Therefore, we must list both the company and type columns in the GROUP BY clause so the groups are based on combinations of unique values from the company and type columns.

As we only need the profitable games, we impose a condition in the WHERE clause that ensures the revenue column value is greater than the production_cost column value. If that is not the case, this game will not be counted.

For each company, select its name, the number of games it produced (as the number_of_games column) and the average cost of production (as the avg_cost column). Show only companies that produced more than one game.

• The company name.
• The number of games produced by the company using the COUNT() aggregate function. Please note that you can use either the company column or an asterisk character (*) as an argument to COUNT() . The difference is that COUNT(*) counts all rows and COUNT(company) counts all rows where the company column is not null. In this case, the company column stores values for each row, so we can use the two options interchangeably. In most other queries, COUNT(column) and COUNT(*) are not interchangeable.
• The average production cost per company, using the AVG() aggregate function. Here, we pass the production_cost column as an argument to AVG() because we want to calculate an average for the values stored in this column.

As the SELECT statement lists one column ( company ) and two aggregate functions that perform calculations per company, we must group the output data by the company column.

To show only the companies that produced more than one game, we must impose a condition on the number_of_games column. However, we cannot use the WHERE clause because number_of_games is an alias name for the value calculated using the COUNT(company) aggregate function.

We use the HAVING clause to impose conditions on aggregate functions . This clause is processed after the GROUP BY clause. You can use either the alias name ( HAVING number_of_games > 1 ) or the aggregate function itself ( HAVING COUNT(company) > 1 ) to create such a condition.

Section 3: Joining Data from Multiple Tables

Now that we’ve reviewed how to query, filter, order, and group data from a single table, it’s time to move on to joining data from multiple tables and performing operations on joined data.

In this section, we’ll use the employees dataset that consists of the following tables:

• id – The ID of a given employee.
• first_name – The employee’s first name.
• last_name – The employee’s last name.
• salary – The employee’s salary.
• manager_id – The ID of this employee's manager.
• grade – The salary grade.
• lower_limit – This grade’s lower limit.
• upper_limit – This grade’s upper limit.
• salary_req – The minimum salary required to obtain a given benefit.
• benefit_name – The benefit name.

Select the first name, last name, salary, and salary grade of employees whose salary fits between the lower_limit and upper_limit from the salgrade table.

We want to select columns stored in the employee and salgrade tables. Therefore, we must join these two tables – or, as we do in this case, list them both in the FROM clause.

We select the first and last name and the salary from the employee table, and the grade column from the salgrade table. We list both these tables in the FROM clause.

We provide the join condition in the WHERE clause. The salary value from the employee table must fall between the lower_limit value and the upper_limit value from the salgrade table.

Show all benefits received by the employee with id = 5 . Select the first and last name of that employee and the benefits' names.

We select the first and last name from the employee table and the name of the benefit from the benefits table. Therefore, we must join the two tables using the JOIN statement.

To qualify for a certain benefit, the employee’s salary (stored in the salary column of the employee table) must be greater than or equal to the salary value required to obtain this benefit (stored in the salary_req column of the benefits table). Therefore, the join condition is exactly that: employee.salary >= benefits.salary_req .

As we want to show all benefits for the employee with id = 5 , we impose this condition in the WHERE clause.

Show each employee's first name, last name, salary, and the first name and last name of their direct manager in the same row. List only employees who have a direct manager.

Each record in the employee table contains a manager_id value that points to the id column of another employee who is this employee’s direct manager. Therefore, we must join the employee table with itself – i.e. a self-join.

The employee table aliased as e is used to select employees’ first name, last name, and salary. The employee table aliased as m is used to select the first and last name of the managers. This is defined in the ON clause, where we impose a join condition saying that the manager_id column from the e table must be equal to the id column from the m table. In effect, this makes the m table store managers of employees stored in the e table.

For each benefit, find the number of employees that receive it. Show two columns: the benefit_name and the count (name that column employee_count ). Don't show benefits that aren't received by anyone.

We want to count how many employees receive a given benefit. Therefore, we must join the benefits and employee tables.

To qualify for a certain benefit, the employee’s salary (stored in the salary column of the employee table) must be greater than or equal to the salary value required to obtain this benefit (stored in the salary_req column of the benefits table). Therefore, the join condition is exactly that: salary_req <= employee.salary .

This exercise brings together JOIN s and GROUP BY . We select the benefit name and the count of employees who receive a given benefit. Therefore, we must group the data by the benefit_name column.

For each benefit, find the number of employees that receive it. Show two columns: the benefit_name and the count (name that column employee_count ). Impose a condition to select only benefits that are not received by any employee ( employee_count = 0 ).

This exercise is analogical to the previous exercise.

To list all benefits – including the ones that no employee receives – we use the LEFT JOIN statement: benefits LEFT JOIN employee . It ensures that all rows from the left-hand side table (here, benefits ) are listed, no matter whether they match the join condition or not.

Next, to select only the employee_count values of zero, we must impose a condition on the COUNT(employee.id) aggregate function. To do so, we use the HAVING clause (introduced in exercise 10).

This article covered the basics of MySQL queries, including selecting and filtering data, ordering and grouping data, and joining multiple tables.

If you enjoyed the MySQL practice exercises showcased in this article, I suggest taking our SQL Practice Set in MySQL for more.

For additional help on SQL practice, check out these blog posts:

• SQL for Data Analysis: 15 Practical Exercises with Solutions .
• 10 Beginner SQL Practice Exercises with Solutions .
• How to Create Your Own Database to Practice SQL .
• 12 Ways to Practice SQL Online .

And remember, practice makes perfect.

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