SQL Problems

PRASHANT SINGH
Last Updated: May 13, 2022

Introduction

Subquery in SQL

As the name suggests, a subquery in SQL is a query within another query. Subqueries in SQL are also known as inner queries or nested queries. They are the tool for performing the operations in multiple steps. Subqueries are embedded within the WHERE clause.

The following example is a basic subquery in SQL (or inner query in SQL):

Consider a table named Employee in the Database with the following record

Emp_ID

Name

Age

Address

Salary

1

Aman

22

Kanpur

27000

2

Luv

24

Gwalior

25500

3

Ajay

25

Delhi

22000

4

Nitya

26

Surat

12500

5

Vivek

22

Indore

25000

6

Ayushi 

21

Kota

20000

Let us run the following subquery for the above Employee Table.

SELECT * From Employee 

WHERE Emp_ID IN ( SELECT Emp_ID From Employee WHERE Salary > 22000 );

This query will give the following result:

Emp_ID

Name

Age

Address

Salary

1

Aman

22

Kanpur

27000

2

Luv

24

Gwalior

25500

5

Vivek

22

Indore

25000

SQL queries example Problems

Now let us see more examples to understand Queries and Subqueries in SQL better.

Example 1:

Consider a table Employee_backup with a similar structure as the Employee table(as shown in the above example). We can use the following query to copy the complete Employee table into the Employee_backup table:

INSERT INTO Employee_backup

SELECT * From Employee 

WHERE Emp_ID IN ( SELECT Emp_ID From Employee );

After execution of the above query, the Employee_backup table will look like this in the Database:

Emp_ID

Name

Age

Address

Salary

1

Aman

22

Kanpur

27000

2

Luv

24

Gwalior

25500

3

Ajay

25

Delhi

22000

4

Nitya

26

Surat

12500

5

Vivek

22

Indore

25000

6

Ayushi 

21

Kota

20000

It will be the same as the Employee table.

Example 2:

Let's take another example to understand subqueries in SQL. Consider the Employee table in the Database:

Emp_ID

Name

Age

Address

Salary

1

Aman

22

Kanpur

27000

2

Luv

24

Gwalior

25500

3

Ajay

25

Delhi

22000

4

Nitya

26

Surat

12500

5

Vivek

22

Indore

25000

6

Ayushi 

21

Kota

20000

Assume we have a table named Employee_backup, which is the backup of the above Employee table(as explained in the above example).

We will run the following queries having UPDATE statement on the above Employee table. This query updates the Salary of Employees by 0.5 times in the Employee table for all the employees whose Age is greater than 22.

UPDATE Employee SET Salary = Salary * 0.5

WHERE Age in ( SELECT Age From Employee_backup WHERE Age>22 );

The result of the above query is:

(Employees having Emp_ID 2,3,4 are affected as their Age is greater than 22)

Emp_ID

Name

Age

Address

Salary

1

Aman

22

Kanpur

27000

2

Luv

24

Gwalior

12750

3

Ajay

25

Delhi

11000

4

Nitya

26

Surat

6250

5

Vivek

22

Indore

25000

6

Ayushi 

21

Kota

20000

Example 3:

In this example, we will learn how to delete the records from the Employee table for the customer whose Age is greater than 22.

Here, we have an Employee table and an Employee_backup table. Now, the query will be:

DELETE From Employee 

WHERE Age IN ( SELECT Age From Employee_backup WHERE Age>22 );

This query would impact a total of three rows, and the Employee table would have the following records:

Emp_ID

Name

Age

Address

Salary

1

Aman

22

Kanpur

27000

5

Vivek

22

Indore

25000

6

Ayushi 

21

Kota

20000

Rules for subqueries in SQL

The following are some rules that subqueries in SQL must follow:

  • Subqueries in SQL must be enclosed with brackets ().
  • A subquery can have only one column in the SELECT clause unless it has multiple columns in the main query to compare its selected columns.
  • We cannot immediately enclose a subquery in a set function.
  • Subqueries in SQL that return more than one row can only be used with multiple value operators such as the IN operator.

FAQs

  1. What is SQL?
    SQL( Structured Query Language) lets you access and manipulate the databases.
    SQL can retrieve, create, update, insert and delete data from the database. SQL ( Structured Query Language) is the standard language for dealing with Relational Databases.
     
  2. What does the SELECT statement do?
    The SELECT statement is used to fetch information from a given table in the Database in a table.
    We can use various operators with the SELECT statement. They are logical, comparison, and arithmetic operators. It is often used with the WHERE clause to specify certain conditions. A select statement inside a select statement(or query) is known as a nested query.
     
  3. What is a nested query?
    A nested query is another name for a subquery (a query within a query). They are the tool for performing the operations in multiple steps.
     
  4. Is Subqueries must be enclosed with brackets ()? 
    Yes, subqueries must be enclosed with brackets ().

Key Takeaways

This blog introduces the Subqueries in SQL(Structured Query Language) that are queries within queries. We take multiple examples to understand  queries and subqueries in SQL with their output. These examples will help you to practice the concept of queries and subqueries in SQL.

Visit here for the top 100 SQL problems asked in various service-based and product-based companies like Google, Microsoft, Infosys, IBM, etc.

Click here to learn more about different topics related to database management systems.

Also, try and explore CodeStudio to practice programming problems for your complete interview preparation.

Was this article helpful ?
1 upvote