SQL Problems

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

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

This query will give the following result:

Must Read SQL Clauses

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:

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

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:

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.

The result of the above query is:

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

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:

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

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.

Recommended topics, DCL Commands in SQL and Tcl Commands in SQL

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