Query In DBMS
FREE
A Query in DBMS is used to retrieve or modify data stored in the database. In simple words, a query is like a question or a request for any operation on a particular data object. Queries traverse a database and may retrieve data according to the requirements specified. This retrieved data may be displayed in the form of tables ( as in SQL ) or as graphs, pictorials, etc according to the query language used. Queries may also update, delete, or add data items to a database.
What is a Query?
A Query in DBMS can be simply defined as a request or a question. Say you wish to check your marks in your last exam and you ask the same from your teacher. That request can be termed as a query. What if you are not sure about your marks and you wish to request a recheck? That request can also be termed a query.
In simplest terms, a query in DBMS is any request or question. In our daily lives, we use queries all the time; from asking a simple question to performing a Google search, all such requests can be termed as queries.
In the context of a database consisting of a large amount of data, queries are used to retrieve or modify data within the database. Query languages like SQL, XQuery, GraphQL, etc all provide various syntaxes for various queries.
What is a Database Query?
A Database Management System or DBMS stores and manages data for easy access and storage. Any database management system is required to provide many methods to store, handle, and query data. Database management systems do so with the help of Query languages like SQL, XQuery, etc. which provide a number of clauses that help store, manipulate, and view data.
SQL or Structured Query Language stores data in the form of databases that comprise tables of rows and columns. Being a Query language, SQL provides a large number of commands or clauses to help manage data. In simplest terms, SQL queries are a collection of SQL commands or clauses and user specifications that help make a request to the SQL client. SQL queries can be broadly divided into two types:
- SELECT Query: Such Queries are used to retrieve or select data from a database.
- ACTION Query: Such Queries are used to make changes to the database.
How do Queries Work?
A Query in DBMS may be used to perform a large number of operations on data. The most frequent use of Queries is to retrieve data from a database according to certain specifications that are specified using a combination of a number of Queries and subqueries.
SQL SELECT Queries are executed by filtering data from the database according to the requirements that a specified using clauses like FROM, WHERE, DISTINCT, etc. This filtering is done in the following three phases:
- Row filtering is done by executing the FROM, WHERE, GROUP BY, HAVING clauses.
- Columns are filtered by SELECT clause.
- Row filtering is done once again by executing the DISTINCT, ORDER BY, LIMIT clause.
For example, we can use SQL to look for the FirstName of employee where gender is female and age is above 30 in a database named Employees using the following Query
In the above SQL query, firstly the table is filtered with the FROM clause. This is followed by filtering the required rows with the help of the WHERE clause. The required columns are finally filtered with the help of the SELECT clause. This result may be again filtered if there exist other clauses like DISTINCT, LIMIT, etc.
Queries in DBMS may also be used to create or modify tables using clauses like CREATE, DELETE, UPDATE, ALTER etc.
Types of SQL Queries
SQL queries may be divided into two broad types:
- SELECT Query
- ACTION Query
SELECT Query
The SELECT Query in DBMS is used primarily to retrieve information from a database or to search for a particular record within a database. It is also used to filter entries in a database. The select query displays data in a tabular format in SQL but may also be used to display data in graphical or pictorial formats using other languages like GraphQL, etc.
SELECT Queries in SQL consist of a number of clauses like SELECT, FROM, WHERE, HAVING, GROUP BY, DISTINCT, JOIN, etc. These clauses help us specify certain conditions that may be used to select particular entries from a database.
Action Query
The Action Query in DBMS is used to perform certain operations on the database such as insertion, deletion, modification, alteration, etc. Action queries are used to change the database in one way or another.
Action Queries in SQL consist of commands like CREATE, DROP, ALTER, TRUNCATE, INSERT, UPDATE, DELETE, etc. These commands are used to create SQL tables and make changes to the tables.
Example of a Database Query
Let us take the following table titled Employees
| EmpID | FirstName | LastName | Age | Gender |
|---|---|---|---|---|
| 102 | Ryan | Johnson | 25 | Male |
| 133 | Olivia | Baker | 31 | Female |
| 121 | Boris | Reeves | 25 | Male |
| 178 | Ryan | Wilson | 33 | Male |
| 101 | Kevin | Martin | 35 | Male |
We shall see the use of SELECT Queries and Action Queries in the following examples
Select Query
-
Select all elements where the Gender is Female
Output
EmpID FirstName LastName Age Gender 133 Olivia Baker 31 Female -
Select age and gender from employees where the First name is Ryan and the Last name is Wilson
Output
Age Gender 33 Male -
Select all elements where the age is greater than 30
Output
EmpID FirstName LastName Age Gender 133 Olivia Baker 31 Female 178 Ryan Wilson 33 Male 101 Kevin Martin 35 Male
Action Query
-
Insert a record into Employees
Output
EmpID FirstName LastName Age Gender 102 Ryan Johnson 25 Male 133 Olivia Baker 31 Female 121 Boris Reeves 26 Male 178 Ryan Wilson 33 Male 101 Kevin Martin 35 Male 129 Reyah Amor 24 Female -
Update a record of Employees where Employee ID is 102
Output
EmpID FirstName LastName Age Gender 102 Ryan Johnson 27 Male 133 Olivia Baker 31 Female 121 Boris Reeves 26 Male 178 Ryan Wilson 33 Male 101 Kevin Martin 35 Male 129 Reyah Amor 24 Female -
Delete records of Employees where FirstName is Ryan
Output
EmpID FirstName LastName Age Gender 133 Olivia Baker 31 Female 121 Boris Reeves 26 Male 101 Kevin Martin 35 Male 129 Reyah Amor 24 Female
What is a SubQuery
A SubQuery in DBMS is a Query that is written inside another query. It is also termed a Nested Query or Inner 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.
A SubQuery is basically a SELECT statement embedded within a clause of another SQL statement. A subquery can be placed in:
- SELECT clause
- FROM Clause
- WHERE Clause
- HAVING Clause
For example, we may use a subquery to select an entry from the Employees table where the FirstName is the same as that of an Employee whose EmpID is 102 as:
Output
| EmpID | FirstName | LastName | Age | Gender |
|---|---|---|---|---|
| 102 | Ryan | Johnson | 25 | Male |
| 178 | Ryan | Wilson | 33 | Male |
In the example above, the SELECT statement within parenthesis is the inner query or subquery. It returns the FirstName of Employees where EmpID is 102 and that FirstName is used in the outer query.
A SubQuery in DBMS may be used in a SELECT, INSERT, UPDATE, or DELETE statement. The following guidelines are to be followed while 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.
Types of SubQueries
There are various types of subqueries that are differentiated based on the number of rows columns or tables referenced within the query. They are as follows:
Single Row Subqueries:
They return only single rows and use single-row comparison operators.
For example, the following query has a subquery that returns a single row "Emp_Name" which is directly returned to the outer query. It selects records of Employees where the Emp_Name is the same as that of an Employee with ID 7.
Multiple Row Subqueries:
They return multiple rows and use multiple row comparison operators like IN, ANY, and ALL.
For example, the following query has a subquery that returns multiple rows which are compared using IN in the outer query. It selects the records of employees whose salary lies within the minimum salaries of each department.
Multiple Column Subqueries:
They return multiple columns which are compared with multiple columns in the parent query.
For example, the following query has a subquery that returns both the Salary as Well as the Name columns from employees which are then compared to Salary and Name columns in the parent Query using IN. It selects the records of Employees whose Salary and Name are the same as that of Employees whose ID is 7.
Correlated Subquery:
They reference one or more columns from the table referred to in the parent query and are compared to each row of the parent query. A correlated query is evaluated once for each row processed by the parent query.
For Example, the following query has a subquery that returns the average salary of Employees per department and is then compared to each row in the parent query. It selects the records of all employees who earn more than the average salary in their department.
Conclusion
- A Query in DBMS may be a request or a question that is used to perform various operations in a database.
- There are two types of Queries in DBMS, Select Queries and Action Queries.
- Select Queries are used to retrieve information from a database using clauses like SELECT, FROM, WHERE, LIKE, etc.
- Action Queries are used to make changes to a database like updating, modifying, or deleting data from a database. Action Queries uses clauses like CREATE, DELETE, UPDATE, DROP, etc.
- SubQueries are Queries that are nested within other queries. They are also called Inner Queries or Nested Queries
- In Nested queries, the inner queries are executed first before the parent query so that its result can be used by the parent query.