What is a Query and Subquery in DBMS?

In this blog, we will learn what query and subquery are? These terms are normally used in the DBMS. So, let's get started.

What is a Query?

In simple terms, we can explain query as questions or doubts. Query means finding information, asking questions about something, especially in order to express one's doubts about it or to check its validity or accuracy.

A Query is used to traverse over some data may be of small or large quantity to find the needed information.

Data is the facts and figures stored digitally or physically at a location. The data provides us with the base on which we run or perform our query. Books in a library can be considered as data. Information on the web can be considered as data. To look for the desired information in this heap of data is called a query.

We perform many queries in our day to day life. Asking questions is a form of query. Searching info on the web is a form of query. Looking for places on Map is a form of query. In each of these examples, we are looking for the required information in different ways. For asking questions we provide query in the form of voice while searching the web we enter keywords while looking for places on the map we enter the location. This is called a query language. A query language is a language which is used to specify our need in the retrieval procedures of information.

A database also contains a lot of data. It stores data in the form of tables. This form of data is used in many places because of its various benefits. A database query refers to the request of data or information from a database. The database consists of tables which contain records of various entities. Thus a database query is used to query the records of a table. A database query is performed using a database query language which generates data of different types according to function. A number of query language have been developed for different database engines and purposes, one such language is SQL which is the most widely used and well-known database query language which generates result in the form of rows and columns.

A database query can be of two types :

  • SELECT Query
  • ACTION Query.

SELECT Query: A select query is used to retrieve data from a single table of a combination of multiple tables.

SQL uses a SELECT statement to select, or extract, specific data based on the given base condition.

ACTION Query: The action query is used to perform operations on the database such as insertion, deletion, modification, alteration, etc.
This query changes the database in one way or another.

Example of database query:

Let's take an example of a database Company which consists of a table Employees. This "Employees" table stores the Emp_Id and Emp_Name of all the Employees of an Organization.

If we want to look at the data of the table Employees we need to run a database query which will return the data in a readable format.

To get the Employee's Emp_Id and Emp_Name of all the employees the SQL Query is: 

SELECT Emp_Id,Emp_Name FROM Employees;

We can further filter the results with a WHERE clause. WHERE Clause is used to specify the conditions or properties of the data we are interested in.

To get the details of Employees whose Emp_Id is greater than 100 the SQL Query will be : 

SELECT Emp_Id,Emp_Name FROM Employees WHERE Emp_Id > 100;

Other examples of Queries in SQL : 

INSERT INTO Employees(Emp_Id,Emp_Name,Salary) VALUES(7,"Ravi",20000) 
>>> Inserts data into Employees table.

UPDATE Employees SET Name="Ravi Kumar" WHERE Emp_Id=7
>>> Updates name of Employees to Ravi Kumar where Emp_Id = 7.

DELETE FROM Employees WHERE Emp_id=9;
>>> Delete details of Employees whose Emp_id is 9.

Different task can be performed with the help of queries and the primary purpose is to get information from the database according to the needs specified in the query.

With the help of queries, we can easily retrieve the stored information and perform time-consuming tasks in the database in a few seconds on the basis of our conditions.

Using the query saves us a lot of time because we don't see the information by going to all the rows and columns of each table of the database to retrieve information, we run a query statement and information in front of us. Queries also can perform calculations on our data or automate data management tasks.

This is all about a query. Now, let's learn about SubQuery.

What is SubQuery?

A Subquery is a type of query which is written inside another query. A subquery becomes a part of a larger query. A subquery is also called INNER QUERY OR NESTED QUERY. A subquery provides data to the main query also called the parent query or outer query.

A subquery is basically a SELECT statement that is embedded in a clause of another SQL statement. A subquery can be placed in:

  • SELECT clause
  • FROM Clause
  • WHERE Clause
  • HAVING Clause

of the parent query or outer query.

The inner query is executed once before its parent query so that the results of an inner query can be passed to the outer query.

Syntax: 

SELECT select_list
	FROM table
	WHERE expr OPERATOR (SELECT select_list FROM table);

Types of SubQuery

Single row subquery
  • Returns only single row.
  • Uses single row comparison operators
  • Examples: Display details of Employees whose Emp_Name is same as that of Employees with Emp_Id = 7. 
SELECT Emp_id,Emp_Name FROM Employees WHERE Emp_Name = (SELECT Emp_Name FROM Employees WHERE Emp_Id = 7);
Multiple row subquery
  • Returns more than one row.
  • Uses multiple row comparison operators like IN, ANY and ALL.
  • Examples: Display details of employees whose salary is the same as the minimum salary in each department. 
SELECT Emp_id,Emp_Name FROM Employees
	WHERE Salary IN (SELECT MIN(Salary) FROM Employees GROUP BY(Dept_Id));
Multiple column subquery
  • Returns more than one
  • There may be one or more rows.
  • Examples : Display details of employees whose salary and the name is the same as that of Employees with Emp_id = 7. 
SELECT Emp_id,Emp_Name FROM Employees
	WHERE (Salary,Name) IN (SELECT Salary,Name FROM Employees WHERE Emp_Id = 7);
Correlated subquery
  • Subquery references one or more column from the table referred in the parent query.
  • Correlated query is evaluated once for each row processed by the parent query.
  • Examples: Display details of all employees who earn more than the average salary in their department. 
SELECT Emp_Id,Emp_name FROM Employees emp
WHERE Salary > (SELECT avg(Salary) FROM Employees WHERE Dept_id = emp.Dept_id);

The subquery can be used inside a SELECT, INSERT, UPDATE or DELETE statement or inside another subquery. It can be used to perform the following tasks:

  • Return some record(s) to parent query.
  • Compare an expression to the result of the query.
  • Determine if an expression is included in the results of the query.
  • Check whether the query selects any rows

Some guidelines we need to consider when using Subqueries:

  • A subquery must be enclosed in parenthesis.
  • A subquery is always placed on the right side of the comparison operators.
  • Subqueries are not able to manipulate the results internally, therefore ORDER BY clause cannot be used in subqueries.
  • Single row operators(=,<>,>,<,>=,<=) are used with single-row subqueries and multiple row operators(IN, ANY, ALL) are used with multiple-row subqueries.

That's all about Queries and SubQueries. Hope you learned something new today.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here .

Keep Learning :)

Team AfterAcademy!