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