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

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.55%, 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.60%, japanese cities' names easy sql (basic) max score: 10 success rate: 99.54%, weather observation station 1 easy sql (basic) max score: 15 success rate: 99.43%, weather observation station 3 easy sql (basic) max score: 10 success rate: 97.98%, weather observation station 4 easy sql (basic) max score: 10 success rate: 98.73%, weather observation station 5 easy sql (intermediate) max score: 30 success rate: 94.33%.

If you're seeing this message, it means we're having trouble loading external resources on our website.

If you're behind a web filter, please make sure that the domains *.kastatic.org and *.kasandbox.org are unblocked.

To log in and use all the features of Khan Academy, please enable JavaScript in your browser.

Unit 3: Intro to SQL: Querying and managing data

About this unit.

  • Welcome to SQL (Opens a modal)
  • Creating a table and inserting data (Opens a modal)
  • Challenge: Book list database (Opens a modal)
  • Querying the table (Opens a modal)
  • Challenge: Box office hits database (Opens a modal)
  • Aggregating data (Opens a modal)
  • Challenge: TODO list database stats (Opens a modal)
  • S-Q-L or SEQUEL? (Opens a modal)
  • Project: Design a store database (Opens a modal)

More advanced SQL queries

  • More complex queries with AND/OR (Opens a modal)
  • Challenge: Karaoke song selector (Opens a modal)
  • Querying IN subqueries (Opens a modal)
  • Challenge: Playlist maker (Opens a modal)
  • Restricting grouped results with HAVING (Opens a modal)
  • Challenge: The wordiest author (Opens a modal)
  • Who issues SQL queries? (Opens a modal)
  • Calculating results with CASE (Opens a modal)
  • Challenge: Gradebook (Opens a modal)
  • Project: Data dig (Opens a modal)

Relational queries in SQL

  • Splitting data into related tables (Opens a modal)
  • JOINing related tables (Opens a modal)
  • Challenge: Bobby's Hobbies (Opens a modal)
  • Joining related tables with left outer joins (Opens a modal)
  • Challenge: Customer's orders (Opens a modal)
  • Joining tables to themselves with self-joins (Opens a modal)
  • Challenge: Sequels in SQL (Opens a modal)
  • Combining multiple joins (Opens a modal)
  • Challenge: FriendBook (Opens a modal)
  • Project: Famous people (Opens a modal)
  • More efficient SQL with query planning and optimization (Opens a modal)

Modifying databases with SQL

  • Using SQL to update a database (Opens a modal)
  • Changing rows with UPDATE and DELETE (Opens a modal)
  • Challenge: Dynamic Documents (Opens a modal)
  • Altering tables after creation (Opens a modal)
  • Challenge: Clothing alterations (Opens a modal)
  • Make your SQL safer (Opens a modal)
  • Project: App impersonator (Opens a modal)

Further learning in SQL

  • What to learn next (Opens a modal)

Python and Excel Projects for practice

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.

facebook_logo

Terms of Use

Python and Excel Projects for practice

Shopping cart

  • Skip to main content
  • Skip to secondary menu
  • Skip to primary sidebar
  • Skip to secondary sidebar

TechBeamers site logo

TechBeamers

Tutorials for beginners to learn Python, Java, Selenium, C, C++, CSharp, Angular, PHP, JavaScript, Agile, Manual and Automation Testing

Top 100 SQL Query Interview Questions and Answers in 2024

Jan 06, 2024 by Meenakshi Agarwal Leave a Comment

Hey Friends, we’ve brought you the top 100 SQL query interview questions and exercises for practice. The first 50, in this post, are the most frequently asked SQL queries and the remaining 50 are the tricky SQL queries for interview . So, start with the readymade SQL script provided to create the test data. The script includes a sample Worker table, a Bonus, and a Title table with pre-filled data. Just run the SQL script, and you are all set to get started with the SQL queries. Practice as much as you can before you face these questions in interviews with top IT MNCs like Amazon, Flipkart, Facebook, etc.

Get Started with 100 SQL Query Interview Questions.

We recommend that you first try to form queries by yourself rather than just reading them from the post. This tutorial includes SQL scripts to create the test data. So, you can use them to create a test database and tables. Once you have done enough SQL practice, then also check out another post on SQL interview prep for QA and DBAs .

Let’s prepare sample data for SQL practice.

Sample table – worker, sample table – bonus, sample table – title.

To prepare the sample data, you can run the following queries in your database query executor or on the SQL command line. We’ve tested them with MySQL Server 5.7 and MySQL Workbench 6.3.8 query browser. You can also download these tools and install them to execute the SQL queries.

SQL Script to Seed Sample Data.

Once the above SQL runs, you’ll see a result similar to the one attached below.

SQL Query Questions - Creating Sample Data

Start Practice with 100 SQL Query Interview Questions.

Below are the 50 commonly asked SQL queries in interviews from various fields. For the second part including 50 tricky SQL queries for interview, read from below.

Must Read: 50 Tricky SQL Queries for Practice Before Your Interview

Q-1. Write an SQL query to fetch “FIRST_NAME” from the Worker table using the alias name <WORKER_NAME>.

The required query is:

Q-2. Write an SQL query to fetch “FIRST_NAME” from the Worker table in upper case.

Q-3. write an sql query to fetch unique values of department from the worker table., q-4. write an sql query to print the first three characters of  first_name from the worker table., q-5. write an sql query to find the position of the alphabet (‘a’) in the first name column ‘amitabh’ from the worker table..

  • The INSTR does a case-insensitive search.
  • Using the BINARY operator will make INSTR work as the case-sensitive function.

Q-6. Write an SQL query to print the FIRST_NAME from the Worker table after removing white spaces from the right side.

Q-7. write an sql query to print the department from the worker table after removing white spaces from the left side., q-8. write an sql query that fetches the unique values of department from the worker table and prints its length., q-9. write an sql query to print the first_name from the worker table after replacing ‘a’ with ‘a’., q-10. write an sql query to print the first_name and last_name from the worker table into a single column complete_name. a space char should separate them., q-11. write an sql query to print all worker details from the worker table order by first_name ascending., q-12. write an sql query to print all worker details from the worker table order by first_name ascending and department descending., q-13. write an sql query to print details for workers with the first names “vipul” and “satish” from the worker table., q-14. write an sql query to print details of workers excluding first names, “vipul” and “satish” from the worker table., q-15. write an sql query to print details of workers with department name as “admin”., q-16. write an sql query to print details of the workers whose first_name contains ‘a’., q-17. write an sql query to print details of the workers whose first_name ends with ‘a’., q-18. write an sql query to print details of the workers whose first_name ends with ‘h’ and contains six alphabets., q-19. write an sql query to print details of the workers whose salary lies between 100000 and 500000., q-20. write an sql query to print details of the workers who joined in feb 2021., q-21. write an sql query to fetch the count of employees working in the department ‘admin’..

At this point, you have acquired a good understanding of the basics of SQL, let’s move on to some more intermediate-level SQL query interview questions. These questions will require us to use more advanced SQL syntax and concepts, such as GROUP BY, HAVING, and ORDER BY.

Q-22. Write an SQL query to fetch worker names with salaries >= 50000 and <= 100000.

Q-23. write an sql query to fetch the number of workers for each department in descending order., q-24. write an sql query to print details of the workers who are also managers., q-25. write an sql query to fetch duplicate records having matching data in some fields of a table., q-26. write an sql query to show only odd rows from a table., q-27. write an sql query to show only even rows from a table., q-28. write an sql query to clone a new table from another table..

The general query to clone a table with data is:

The general way to clone a table without information is:

An alternate way to clone a table (for MySQL) without data is:

Q-29. Write an SQL query to fetch intersecting records of two tables.

Q-30. write an sql query to show records from one table that another table does not have., q-31. write an sql query to show the current date and time..

The following MySQL query returns the current date:

Whereas the following MySQL query returns the current date and time:

Here is a SQL Server query that returns the current date and time:

Find this Oracle query that also returns the current date and time:

Q-32. Write an SQL query to show the top n (say 10) records of a table.

MySQL query to return the top n records using the LIMIT method:

SQL Server query to return the top n records using the TOP command:

Oracle query to return the top n records with the help of ROWNUM:

Now, that you should have a solid foundation in intermediate SQL, let’s take a look at some more advanced SQL query questions. These questions will require us to use more complex SQL syntax and concepts, such as nested queries, joins, unions, and intersects.

Q-33. Write an SQL query to determine the nth (say n=5) highest salary from a table.

MySQL query to find the nth highest salary:

SQL Server query to find the nth highest salary:

Q-34. Write an SQL query to determine the 5th highest salary without using the TOP or limit method.

The following query is using the correlated subquery to return the 5th highest salary:

Use the following generic method to find the nth highest salary without using TOP or limit.

Q-35. Write an SQL query to fetch the list of employees with the same salary.

Q-36. write an sql query to show the second-highest salary from a table., q-37. write an sql query to show one row twice in the results from a table., q-38. write an sql query to fetch intersecting records of two tables., q-39. write an sql query to fetch the first 50% of records from a table..

Practicing SQL query interview questions is a great way to improve your understanding of the language and become more proficient in using it. However, in addition to improving your technical skills, practicing SQL query questions can also help you advance your career. Many employers are looking for candidates who have strong SQL skills, so being able to demonstrate your proficiency in the language can give you a competitive edge.

Q-40. Write an SQL query to fetch the departments that have less than five people in them.

Q-41. write an sql query to show all departments along with the number of people in there..

The following query returns the expected result:

Q-42. Write an SQL query to show the last record from a table.

The following query will return the last record from the Worker table:

Q-43. Write an SQL query to fetch the first row of a table.

Q-44. write an sql query to fetch the last five records from a table., q-45. write an sql query to print the names of employees having the highest salary in each department., q-46. write an sql query to fetch three max salaries from a table., q-47. write an sql query to fetch three min salaries from a table., q-48. write an sql query to fetch nth max salaries from a table., q-49. write an sql query to fetch departments along with the total salaries paid for each of them., q-50. write an sql query to fetch the names of workers who earn the highest salary..

Must Check: SQL Programming Test with 20+ Basic to Advanced Queries

Summary – Master SQL with Practice Questions.

We hope you enjoyed solving the SQL exercises and learned something new along the way. Stay tuned for our next post, where we’ll bring you even more challenging SQL query questions to sharpen your proficiency.

Thanks for reading! We hope you found this tutorial helpful. If you did, please consider sharing it with your friends and colleagues. You can also follow us on our social media platforms for more helpful resources. And if you’re looking for more information on this topic, be sure to check out the “You Might Also Like” section below.

-TechBeamers.

Reader Interactions

Leave a reply.

Your email address will not be published. Required fields are marked *

  • SQL Server training
  • Write for us!

Esat Erkec

SQL Variables: Basics and usage

In this article, we will learn the notions and usage details of the SQL variable. In SQL Server, local variables are used to store data during the batch execution period. The local variables can be created for different data types and can also be assigned values. Additionally, variable assigned values can be changed during the execution period. The life cycle of the variable starts from the point where it is declared and has to end at the end of the batch. On the other hand, If a variable is being used in a stored procedure, the scope of the variable is limited to the current stored procedure. In the next sections, we will reinforce this theoretical information with various examples

Note: In this article examples, the sample AdventureWorks database is used.

SQL Variable declaration

The following syntax defines how to declare a variable:

Now, let’s interpret the above syntax.

Firstly, if we want to use a variable in SQL Server, we have to declare it. The DECLARE statement is used to declare a variable in SQL Server. In the second step, we have to specify the name of the variable. Local variable names have to start with an at (@) sign because this rule is a syntax necessity. Finally, we defined the data type of the variable. The value argument which is indicated in the syntax is an optional parameter that helps to assign an initial value to a variable during the declaration. On the other hand, we can assign or replace the value of the variable on the next steps of the batch. If we don’t make any initial value assigned to a variable, it is initialized as NULL.

The following example will declare a variable whose name will be @VarValue and the data type will be varchar. At the same time, we will assign an initial value which is ‘Save Our Planet’:

Declaring a SQL variable with initial value

Assigning a value to SQL Variable

SQL Server offers two different methods to assign values into variables except for initial value assignment. The first option is to use the SET statement and the second one is to use the SELECT statement. In the following example, we will declare a variable and then assign a value with the help of the SET statement:

Assigning  a value to SQL variable through SET statement

In the following example, we will use the SELECT statement in order to assign a value to a variable:

Assigning  a value to SQL Variable through SELECT statement

Additionally, the SELECT statement can be used to assign a value to a variable from table, view or scalar-valued functions. Now, we will take a glance at this usage concept through the following example:

Assigning  a value to SQL Variable from a table

As can be seen, the @PurchaseName value has been assigned from the Vendor table.

Now, we will assign a value to variable from a scalar-valued function:

Assigning  a value to SQL Variable from a scalar-valued function

Multiple SQL Variables

For different cases, we may need to declare more than one variable. In fact, we can do this by declaring each variable individually and assigned a value for every parameter:

Multiple SQL variables

This way is tedious and inconvenient. However, we have a more efficient way to declare multiple variables in one statement. We can use the DECLARE statement in the following form so that we can assign values to these variables in one SELECT statement:

Multiple SQL Variables usage with SELECT statement

Also, we can use a SELECT statement in order to assign values from tables to multiple variables:

Multiple SQL  variable usage

Useful tips about the SQL Variables

Tip 1: As we mentioned before, the local variable scope expires at the end of the batch. Now, we will analyze the following example of this issue:

SQL variable scope problem

The above script generated an error because of the GO statement. GO statement determines the end of the batch in SQL Server thus @TestVariable lifecycle ends with GO statement line. The variable which is declared above the GO statement line can not be accessed under the GO statement. However, we can overcome this issue by carrying the variable value with the help of the temporary tables:

SQL variable usage in the diffrent scopes

Tip 2: Assume that, we assigned a value from table to a variable and the result set of the SELECT statement returns more than one row. The main issue at this point will be which row value is assigned to the variable. In this circumstance, the assigned value to the variable will be the last row of the resultset. In the following example, the last row of the resultset will be assigned to the variable:

Value assignment to SQL variable from a table which has multiple rows

Tip 3: If the variable declared data types and assigned value data types are not matched, SQL Server makes an implicit conversion in the value assignment process, if it is possible. The lower precedence data type is converted to the higher precedence data type by the SQL Server but this operation may lead to data loss. For the following example, we will assign a float value to the variable but this variable data type has declared as an integer:

SQL variable implicit conversion

In this article, we have explored the concept of SQL variables from different perspectives, and we also learned how to define a variable and how to assign a value(s) to it.

  • Recent Posts

Esat Erkec

  • SQL Unit Testing reference guide for beginners - August 11, 2023
  • SQL Cheat Sheet for Newbies - February 21, 2023
  • SQL Practice: Common Questions and Answers for the final round interviews - January 26, 2023

Related posts:

  • SQL Server PRINT and SQL Server RAISERROR statements
  • What to choose when assigning values to SQL Server variables: SET vs SELECT T-SQL statements
  • SQL varchar data type deep dive
  • SQL Convert Function
  • What is causing database slowdowns?

SQL & Databases: Download Practice Datasets

Published by SuperDataScience Team

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

Section 1: Introduction

  • No dataset required

Section 2: It's Super Easy to Get Started

Section 3: preparation.

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

Section 4: Basics of SQL

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

Section 5: Working with Data

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

Section 6: Fundamentals of Database Theory

  • Will be added soon.

Section 7: Joining Tables in SQL

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

Section 8: Creating Tables in SQL

Section 9: database design.

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

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

+= (Addition Assignment) (Transact-SQL)

  • 13 contributors

Adds two numbers and sets a value to the result of the operation. For example, if a variable @x equals 35, then @x += 2 takes the original value of @x , add 2 and sets @x to that new value (37).

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation .

expression Is any valid expression of any data type in the numeric category except the bit data type.

Result Types

Returns the data type of the argument with the higher precedence. For more information, see Data Type Precedence (Transact-SQL) .

For more information, see + (Addition) (Transact-SQL) .

Compound Operators (Transact-SQL) Expressions (Transact-SQL) Operators (Transact-SQL) += (String Concatenation Assignment) (Transact-SQL)

Was this page helpful?

Submit and view feedback for

Additional resources

SQL Joins: 12 Practice Questions with Detailed Answers

Author's photo

  • sql practice

In this article, we dig into our SQL JOINS course and give you 12 join exercises to solve. But don’t worry – all the exercises have solutions and explanations. If you get stuck, help is there! This is, after all, made for practicing and learning. 

SQL joins can be tricky. It’s not just the syntax, but also knowing what joins to use in what scenarios.

Joins are used when combining data from two or more tables in SQL. The tables can be joined in several ways, and, depending on the tables, each way of joining them can result in a completely different result.  There’s no other way to learn this than practice. Yes, you can read explanations and typical uses of SQL joins. That helps, for sure! But practice builds on that through problem-solving and repetition, which makes your knowledge stick. The more you practice, the greater the possibility that the real-life data problems you’ll have to solve will be similar or completely the same as what you’ve already done!

And practice is what we’ll do in this article! We’ll show you exercises for basic and more advanced SQL joins uses. If you like them, you’ll enjoy our SQL JOINs course even more, as all the exercises are taken from there. In total, the course offers you 93 SQL joins exercises. They cover topics ranging from the types of joins in SQL, to filtering data, joining more than two tables, self-joining a table, and using non-equi joins.

OK, so let’s introduce the datasets and start exercising, shall we? Feel free to help yourself with the SQL JOIN Cheat Sheet as you go.

List of Exercises

Here's a list of all exercises in the article:

Exercise 1: List All Books and Their Authors

Exercise 2: list authors and books published after 2005, exercise 3: show books adapted within 4 years and rated lower than the adaptation, exercise 4: show all books and their adaptations (if any), exercise 5: show all books and their movie adaptations, exercise 6: show all books with their reviews (if any), exercise 7: list all the books and all the authors, exercise 8: show products under 150 calories and their department, exercise 9: list all products with their producers, departments, and carbs, exercise 10: show all the products, prices, producers, and departments, exercise 11: list all workers and their direct supervisors, exercise 12: show cars with higher mileage than a specific car.

INNER JOIN is a type of SQL join that returns only the matching rows from the joined tables.

To show you how this works, we’ll use Dataset 1 from the course.

The dataset consists of four tables: author , book , adaptation , and book_review .

The first table shows the author data in the following columns:

  • id – The author’s unique ID within the database.
  • name – The author’s name.
  • birth_year – The year when that author was born.
  • death_year – The year when that author died (the field is empty if they are still alive).

Here are the table’s first few rows:

The second table, book ,  shows details about books. The columns are:

  • id – The ID of a given book.
  • author_id – The ID of the author who wrote that book.
  • title – The book’s title.
  • publish_year – The year when the book was published.
  • publishing_house – The name of the publishing house that printed the book.
  • rating – The average rating for the book.

These are the first five rows:

The adaptation table has the following columns:

  • book_id – The ID of the adapted book.
  • type – The type of adaptation (e.g. movie, game, play, musical).
  • title – The name of this adaptation.
  • release_year – The year when the adaptation was created.
  • rating – The average rating for the adaptation.

Here’s a snapshot of the data from this table:

The final table is book_review . It consists of the following columns:

  • book_id - The ID of a reviewed book.
  • review - The summary of the review.
  • author - The name of the review's author.

Here’s the data:

Exercise: Show the name of each author together with the title of the book they wrote and the year in which that book was published.

Solution explanation: The query selects the name of the author, the book title, and its publishing year. This is data from the two tables: author and book . We are able to access both tables by using INNER JOIN . It returns only rows with matching values (values that satisfy the join condition) from both tables.

We first reference the table author in the FROM clause. Then we add the JOIN clause (which can also be written as INNER JOIN in SQL) and reference the table book .

The tables are joined on the common column. In this case, it's id from the table author and author_id from the table book . We want to join the rows where these columns share the same value. We do that using the ON clause and specifying the column names. We also put the table name before each column so the database knows where to look. That’s primarily because there’s an id column in both tables, but we want the id column only from the author table. By referencing the table name, the database will know from which table we need that column.

Solution output:

Here’s the output snapshot. We got all this data by joining two tables:

Exercise: Show the name of each author together with the title of the book they wrote and the year in which that book was published. Show only books published after 2005.

Solution explanation: This exercise and its solution are almost the same as the previous one. This is reflected by the query selecting the same columns and joining the tables in the same way as earlier.

The difference is that the exercise now asks us to show only books published after 2005. This requires filtering the output; we do that using the WHERE clause.

WHERE is a clause that accepts conditions used to filter out the data. It is written after joining the tables. In our example, we filter by referencing the column publish_year after WHERE and using the comparison operator ‘greater than’ ( > ) to find the years after 2005.

The output shows only one book published after 2005.

Exercise: For each book, show its title, adaptation title, adaptation year, and publication year.

Include only books with a rating lower than the rating of their corresponding adaptation. Additionally, show only those books for which an adaptation was released within four years of the book’s publication.

Rename the title column from the book table to book_title and the title column from the adaptation table to adaptation_title .

Solution explanation: Let’s start explaining the solution from the FROM and JOIN clauses. The columns we need to show are from the tables book and adaptation . We reference the first table in FROM and the second in JOIN .

In the ON clause, we equal the two book ID columns and specify the table of each column. This is the same as earlier, only with different table and column names.

Now, we need to select the required columns. The thing here is there’s a title column in both tables. To avoid ambiguity, a best practice is to reference the table name before each column in the SELECT .

Note: The above is mandatory only for ambiguous columns. However, it’s a good idea to do that with all columns; it improves code readability and the approach remains consistent.

After selecting the columns, we need to rename some of them. We do that using the keyword AS and writing a new column name afterward. That way, one title column becomes book_title , the other becomes adaptation_title . Giving aliases to the column names also helps get rid of ambiguity.

Now we need to filter the output. The first condition is that the adaptation had to be released four years or less after the book. We again use WHERE and simply deduct the book publish year from the adaptation release year. Then we say that the difference has to be less than or equal to ( <= ) 4.

We also need to add the second condition, where the book has a lower rating than the adaptation. It’s simple! The question implies that both the first and the second conditions have to be satisfied. The clue is in AND , a logical operator we use for adding the second condition. Here, it uses the ‘less than’ (< ) operator to compare the two ratings.

The output shows three book–adaptation pairs that satisfy the conditions.

Now that you get the gist of INNER JOIN , let’s move on to LEFT JOIN . It’s a type of outer join that returns all the columns from the left (the first) table and only the matching rows from the right (the second) table. If there is non-matching data, it’s shown as NULL .

You can learn more in our article about LEFT JOIN .

Exercise: Show the title of each book together with the title of its adaptation and the date of the release. Show all books, regardless of whether they had adaptations.

Solution explanation: We first select the required columns from the two tables. Then we join book (the left table) with adaptation (the right table) using LEFT JOIN . You see that the SQL join syntax is the same for INNER JOIN . The only thing that changes is the join keyword.

Note: SQL accepts both LEFT JOIN and LEFT OUTER JOIN . They are the same command.

The output snapshot shows the required data, with some of the data shown as NULL . These are the books without the adaptation.

Exercise: Show all books with their movie adaptations. Select each book's title, the name of its publishing house, the title of its adaptation, and the type of the adaptation. Keep the books with no adaptations in the result.

Solution explanation:

The question asks to show all the rows, even those without any adaptations. It’s possible that there are books without adaptations, so we use LEFT JOIN .

We first select the book title, its publishing house, its adaptation title, and its type.

Then we join book (the left table) with adaptation (the right table) using LEFT JOIN . We join the tables on the book ID. All the books that don’t satisfy the conditions will have NULL s as an adaptation title and type.

We filter data using WHERE . The first condition is that the adaptation type has to be a movie, so we equal the type column with a movie using the equal sign ( = ).  Note: When using text data in the WHERE condition, it must be enclosed in single quotes ( '' ).

The second filtering condition is added using the logical operator OR. It says that the type can also be NULL if it’s not a movie. The exercise asks us to keep books with no adaptations in the results.

Here’s the output snapshot. You can see that it shows only books adapted as movies or not adapted at all.

Where there’s LEFT JOIN , there’s also RIGHT JOIN , right? Despite being the LEFT JOIN's mirror image, it’s still a part of the SQL joins practice.

It’s a type of join that returns all the columns from the right (the second) table and only the matching rows from the left (the first) table. If there is non-matching data, it’s shown as NULL .

Exercise: Join the book_review and book tables using a RIGHT JOIN . Show the title of the book, the corresponding review, and the name of the review's author. Consider all books, even those that weren't reviewed.

We first select the required columns. Then we do as we’re told: join the tables using RIGHT JOIN . We join the tables on the book ID. The table book is the right table; we want all the data from it, regardless of the reviews.

As you can see, the syntax stays the same as in INNER JOIN and LEFT JOIN .

Note: SQL accepts both RIGHT JOIN and RIGHT OUTER JOIN .

The query returns all the book titles, their reviews, and authors. Where there’s no review or author information, a NULL is shown.

Here’s another join type that’s useful in some scenarios: the FULL JOIN . This is a LEFT JOIN and RIGHT JOIN put together. It shows matching rows from both tables, rows that have no match from the left table, and rows that have no match from the right table. In short, it shows all data from both tables.

You can read more about how and when to use FULL JOIN .

Exercise: Display the title of each book along with the name of its author. Show all books, even those without an author. Show all authors, even those who haven't published a book yet. Use a FULL JOIN .

Solution explanation: The question requires showing all books, but also all authors – FULL JOIN is perfect for doing this elegantly.

We select the book title and the author's name. Next, we FULL JOIN the table book with the table author . The joining condition is that the author ID has to be the same in both tables. Again, the syntax is the same as in all the previous join types.

Note: SQL accepts both FULL JOIN and FULL OUTER JOIN.

The output shows all the books and all the authors, whether the authors or books exist in both tables or not.

Joining 3 or More Tables

Yes, SQL joins allow for joining more than two tables. We’ll see how to do that in this part of the SQL joins practice. You can find a more detailed explanation of multiple joins here .

We also need a new dataset, so let’s introduce it.

The first table in the dataset is department . Its columns are:

  • id – The unique ID of the department.
  • name – The department name, i.e. where a particular type of product is sold.

Here’s the data from the table.

The second table is product , and it consists of the following columns:

  • id – The ID of a given product.
  • name – The product’s name.
  • department_id – The ID of the department where the product is located.
  • shelf_id – The ID of the shelf of that department where the product is located.
  • producer_id – The ID of the company that manufactures this product.
  • price – The product’s price.

Here’s the data snapshot:

The next table is nutrition_data . Its columns and data are given below:

  • product_id – The ID of a product.
  • calories – The calorific value of that product.
  • fat – The amount of fat in that product.
  • carbohydrate – The amount of carbohydrates in that product.
  • protein – The amount of protein in that product.

The fourth table is named producer . It has the following columns:

  • id – The ID of a given food producer.
  • name – The name of the producer.

Below is the data from this table:

The last table in the dataset is sales_history . It has the following columns:

  • date – The date of sale.
  • product_id – The ID of the product sold.
  • amount – The amount of that product sold on a particular day.

Here’s the data, too:

Exercise: List all products that have fewer than 150 calories. For each product, show its name (rename the column product ) and the name of the department where it can be found (name the column department ).

Solution explanation: The general principle of how you join the third (fourth, fifth…) table is that you simply add another JOIN . You can see how it’s done in this article explaining multiple joins . We’ll do it the same way here.

We first join the department table with the product table on the department ID using JOIN . But we also need the third table. To get the data from it, we just add another JOIN , which will join the product table with the nutrition_data table. The syntax is the same as with the first join. In this case, the query joins the tables on the product ID.

Then we use WHERE to find products with fewer than 150 calories. We finally select the product and department names and rename the columns as per the exercise instructions.

Note: You probably noticed both selected columns have the same original name. And you also noticed we solved this ambiguity by putting some strange short table names in front of all the columns in the query. These shortened names are table aliases, which you give by simply writing them after the table name in FROM or JOIN . By giving aliases to the tables, you can shorten the tables’ names. Therefore, you don’t have to write their full names (sometimes they can be really long!), but the short aliases instead. This saves time and space.

The output shows a list of the products and the department they belong to. It includes only those products with fewer than 150 calories.

Exercise: For each product, display the:

  • Name of the company that produced it (name the column producer_name ).
  • Name of the department where the product is located (name it department_name ).
  • Product name (name it product_name ).
  • Total number of carbohydrates in the product.

Your query should still consider products with no information about producer_id or department_id .

Solution explanation: The query selects the required columns. Then it joins the table product with the table producer on the producer ID using LEFT JOIN . We choose this type of join because we have to include products without producer data.

Then we add another LEFT JOIN . This one adds the department table and joins it with the product table. Again, we choose LEFT JOIN because we need to show products that don’t have a department.

There’s also a third join! We simply add it to the chain of the previous joins. It’s again LEFT JOIN , as we add the nutrition_data table and join it with the product table.

This is an interesting topic to explore, so here’s an article that explains multiple LEFT JOINs to help you with it.

The output shows all the products with their producer and department names and carbohydrate amounts:

If you need more details, please read how to LEFT JOIN multiple tables in SQL .

Exercise: For each product, show its name, price, producer name, and department name.

Alias the columns as product_name , product_price , producer_name , and department_name , respectively. Include all the products, even those without a producer or department. Also, include the producers and departments without a product.

Solution explanation: This exercise requires using FULL JOIN , as we need all the data from the tables we’ll use: product , producer , and department .

The syntax is the same as in the previous examples. We just join the different tables ( product and producer ) on the producer ID and use a different type of join:  FULL JOIN .

The second FULL JOIN joins the product table with the department table.

After selecting the required columns and renaming them, we get the following output.

The solution shows all the data from the selected tables and columns:

A self-join is not a distinct type of SQL JOIN – any join can be used for self-joining a table. It’s simply a join used to join the table with itself. By giving different aliases to the same table, it’s treated as two different tables when self-joined.

For more details, check out our illustrated guide to the SQL self-join .

The dataset for this example consists of only one table: workshop_workers . It has the following columns.

  • id – The worker’s ID.
  • name – The worker’s first and last name.
  • specialization – The worker's specialization.
  • master_id – The ID of the worker's supervisor.
  • experience – The worker's years of experience.
  • project_id – The ID of the project to which the worker is currently assigned.

Exercise: Show all workers' names together with the names of their direct supervisors. Rename the columns  apprentice_name and master_name , respectively. Consider only workers who have a supervisor (i.e. a master).

Solution explanation: Let’s start with explaining the self-join. The general principle is the same as with regular joins. We reference the table in FROM and give it an alias, apprentice . Then we use JOIN and reference the same table in it. This time, we give the table the alias master . We’re basically pretending that one table has the apprentice data and the other has the master data.

The tables are joined on the master ID from the apprentice table and the ID from the master table.

This example is a typical use of a self-join: the table has a column ( master_id ) that references another column from the same table ( id ). Both columns show the worker’s ID. When there’s NULL in master_id , it means that the worker doesn’t have a master. In other words, they are the master.

After self-joining, we simply select the required columns and rename them.

The output shows all the apprentices and their direct supervisors.

Non-Equi Joins

The final topic we’ll tackle in this SQL joins practice are non-equi joins. The joins we used so far are called equi-joins because they use the equality sign ( = ) in the joining condition. Non-equi are all other joins that use any other operators – comparison operators ( < , > , <= , >= , != , <> ), the BETWEEN operator, or any other logical condition – to join tables.

We’ll use the dataset consisting of two tables. The first table is car . Here are its columns:

  • id – The car’s ID in the database.
  • model – The car’s model.
  • brand – The car’s brand.
  • original_price – The original price of that car when new.
  • mileage – The car’s total mileage.
  • prod_year – The car’s production year.

The data looks like this:

The second table is charity_auction with these columns:

  • car_id – The car’s ID.
  • initial_price – The car’s initial (i.e. starting) price.
  • final_price – The actual price when the car was sold.
  • buyer_id – The ID of the person who bought the car.

Exercise: Show the model, brand, and final price of each car sold at the auction. Consider only those sold cars that have more mileage than the car with the id = 4 .

Solution explanation: We select the car model, brand, and final price.

In the first JOIN , we join the car table with the charity_auction table. The tables are joined where the car IDs are the same. This is our regular equi JOIN .

We add the second JOIN , which is a self-join. It adds the table car again, so we can filter the data using the non-equi join condition. The condition will return all the cars from the car table and all the cars from the car2 table with the lower mileage. This is a non-equi condition as it uses the ‘greater than’ ( > ) operator. The syntax is the same, but there’s > instead of = this time.

Finally, we need to filter data using WHERE . We’re not interested in comparing the mileage of all cars. We want to show the cars that have a mileage higher than the car with id = 4 . This is what the first filtering condition does.

We add another filtering condition that says the final price shouldn’t be NULL , i.e., the car has to have been sold in the auction.

The result shows two cars:

SQL JOINs Practice Makes Perfect. More Practice? Perfect-er!

Twelve SQL join exercises is a solid amount of practice. Through these exercises, you could learn and practice all the most common join topics that trouble beginner and intermediate users.

Now, you just need to keep going! When you practice even more, you become even perfect-er. So if you liked our exercises, you can get more of the same in our SQL JOINS course or the article about the SQL JOIN interview questions .

Hope you ace all the exercises that await you there!

You may also like

sql assignment

How Do You Write a SELECT Statement in SQL?

sql assignment

What Is a Foreign Key in SQL?

sql assignment

Enumerate and Explain All the Basic Elements of an SQL Query

SQL Tutorial

Sql database, sql references, sql examples, sql operators, sql arithmetic operators, sql bitwise operators, sql comparison operators.

Advertisement

SQL Compound Operators

Sql logical operators.

Get Certified

COLOR PICKER

colorpicker

Report Error

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

[email protected]

Top Tutorials

Top references, top examples, get certified.

IMAGES

  1. SQL-Assignment 3

    sql assignment

  2. sql-assignment-3-part-2

    sql assignment

  3. SQL+Assignment+Answers

    sql assignment

  4. SQL Practice Problems(3)

    sql assignment

  5. SQL Assignment 1 With Solution

    sql assignment

  6. SQL Assignment 3

    sql assignment

VIDEO

  1. SQL

  2. SQL : Introduction

  3. Chapter06-Basic SQL-15 Additional features of SQL

  4. SQL

  5. SQL Introduction

  6. SQL (Structured Query Language) Class13

COMMENTS

  1. SQL Exercises

    You will get 1 point for each correct answer. Your score and total score will always be displayed. Start SQL Exercises Good luck! 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 SQL course

  2. SQL Exercises, Practice, Solution

    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.

  3. Basic SQL Query Practice Online: 20 Exercises for Beginners

    Tihomir Babic sql practice sql basics These 20 exercises are just what beginners need for SQL query practice. Try to solve each of them, and then look at the solutions. If something needs to be clarified, there are explanations for each solution. In this article, there'll be less talking than usual.

  4. 10 Beginner SQL Practice Exercises With Solutions

    Exercise 1: Selecting All Columns From a Table Exercise 2: Selecting a Few Columns From a Table Exercise 3: Selecting a Few Columns and Filtering Numeric Data in WHERE Exercise 4: Selecting a Few Columns and Filtering Text Data in WHERE Exercise 5: Selecting a Few Columns and Filtering Data Using Two Conditions in WHERE

  5. Solve SQL

    Prepare SQL SQL Revising the Select Query I EasySQL (Basic)Max Score: 10Success Rate: 95.99% Solve Challenge Revising the Select Query II EasySQL (Basic)Max Score: 10Success Rate: 98.69% Solve Challenge Select All EasySQL (Basic)Max Score: 10Success Rate: 99.55% Solve Challenge Select By ID EasySQL (Basic)Max Score: 10Success Rate: 99.66%

  6. Advanced SQL Practice: 10 SQL Practice Exercises with Solutions

    Section 1: Advanced SQL JOIN Exercises In the following advanced SQL exercises, we'll use a sportswear database that stores information about clothes, clothing categories, colors, customers, and orders. It contains five tables: color, customer, category, clothing, and clothing_order. Let's look at the data in this database.

  7. Free SQL exercises

    Create an inner join in a query, then change it to an outer join to show categories having no events. Join two tables together in SQL, using alias table names. Link the continent, country and event tables with inner joins, and then filter by fields from 2 tables. Use inner joins to link four tables to show Dr Who enemies by author.

  8. Learn SQL: Practice SQL Queries

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

  9. Introduction to Structured Query Language (SQL)

    Learn about the basic syntax of the SQL language, as well as database design with multiple tables, foreign keys, and the JOIN operation. ... Access to lectures and assignments depends on your type of enrollment. If you take a course in audit mode, you will be able to see most course materials for free. To access graded assignments and to earn a ...

  10. Twenty-five SQL practice exercises

    Towards Data Science · 25 min read · Sep 16, 2020 -- 43 Photo credit: Unsplash Introduction S tructured query language (SQL) is used to retrieve and manipulate data stored in relational databases. Gaining working proficiency in SQL is an important prerequisite for many technology jobs and requires a bit of practice.

  11. Intro to SQL: Querying and managing data

    Unit 1 Intro to JS: Drawing & Animation. Unit 2 Intro to HTML/CSS: Making webpages. Unit 3 Intro to SQL: Querying and managing data. Unit 4 Advanced JS: Games & Visualizations. Unit 5 Advanced JS: Natural Simulations. Unit 6 HTML/JS: Making webpages interactive. Unit 7 HTML/JS: Making webpages interactive with jQuery. Unit 8 Meet the professional.

  12. Top 100 SQL Problems

    Top 100 SQL Problems. We have collected a variety of SQL practice problems for each and every topic of SQL. You can appear in top tech companies by practicing consistently and precisely every day. Try to solve all SQL practice problems. To level up your SQL skills, you can solve advanced SQL interview questions from the real world.

  13. Where can I find exercises to practice SQL statements?

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

  14. = (Assignment Operator) (Transact-SQL)

    The assignment operator can also be used to establish the relationship between a column heading and the expression that defines the values for the column. The following example displays the column headings FirstColumnHeading and SecondColumnHeading. The string xyz is displayed in the FirstColumnHeading column heading for all rows.

  15. SQL Practice, Exercises, Exams

    Beginner 30 Exercises: agregate functions, order, group by, having , boolean, joins. 2) Ram Keden Views: 13567 Beginner 14 Exercises: select, filtering, scalar functions, group by, joins, subquery, tables, DDL. 3) W3resource Views: 11546 Beginner - Intermediate 400 Exercises: sql queries, filtering, sorting, multiple tables, joins, subqueries.

  16. Variables (Transact-SQL)

    Declaring a Transact-SQL Variable. The DECLARE statement initializes a Transact-SQL variable by: Assigning a name. The name must have a single @ as the first character. Assigning a system-supplied or user-defined data type and a length. For numeric variables, a precision and scale are also assigned.

  17. SQL Query Questions You Need to Practice for Interview

    Q-4. Write an SQL query to print the first three characters of FIRST_NAME from the Worker table. Ans. The required query is: Select substring (FIRST_NAME,1,3) from Worker; Q-5. Write an SQL query to find the position of the alphabet ('a') in the first name column 'Amitabh' from the Worker table. Ans.

  18. SQL Practice for Students: 11 Basic SQL Practice Exercises with Solutions

    This interactive online course is designed for people who know SQL and are looking for practice exercises that cover single table queries, joins, sorting, grouping, aggregation, and more. It's part of our SQL Practice track, which contains more SQL practice courses for beginners. LearnSQL.com lets you learn SQL by writing SQL code on your own.

  19. MySQL Exercises, Practice, Solution

    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.

  20. SQL Variables: Basics and usage

    SQL Server offers two different methods to assign values into variables except for initial value assignment. The first option is to use the SET statement and the second one is to use the SELECT statement. In the following example, we will declare a variable and then assign a value with the help of the SET statement:

  21. SQL & Databases: Download Practice Datasets

    Welcome to the data repository for the SQL Databases course by Kirill Eremenko and Ilya Eremenko. The datasets and other supplementary materials are below. Enjoy! Section 1: Introduction. No dataset required; Section 2: It's Super Easy to Get Started. No dataset required;

  22. Addition Assignment) (Transact-SQL)

    Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric. Adds two numbers and sets a value to the result of the operation. For example, if a variable @x equals 35, then @x += 2 takes the original value of ...

  23. SQL Joins: 12 Practice Questions with Detailed Answers

    join joins sql practice In this article, we dig into our SQL JOINS course and give you 12 join exercises to solve. But don't worry - all the exercises have solutions and explanations. If you get stuck, help is there! This is, after all, made for practicing and learning. SQL joins can be tricky.

  24. SQL Operators

    W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.