Query and Subquery in SQL

SQL Queries :

A query is an inquiry into the database using the select command. Consider the following relations

Employee
Eno Name Age Salary
1000 Ankit 24 30000
1001 Pooja 24 20000
1002 Komal 19 10000
1003 Surender 49 40000
Work_IN
Eno Dno
1000 10
1001 10
1002 11
1003 10
Department
Dno Dname City
10 Landline Bhiwani
11 Phone Rohtak

Example of SQL Queries :

Query 1 : Get full details of all employees.

Solution : SELECT * FROM Employee;

Query 2 : Get the age of all the employees.

Solution : SELECT Age FROM Employee;

Query 3 : Get the age of all the employees with no duplicates.

Solution : SELECT DISTINCT Age FROM Employee;

Query 4 : Get the employee number and name of the employees whose Salary is greater than 25000.

Solution : SELECT Eno, Name FROM Employee WHERE Salary>25000;

Division of SQL Queries :

Based on the filtering Activity, the SQL queries are divided into 5 groups :

  • Queries that uses basic search operations.
  • Queries that uses range operations. (Using BETWEEN & NOT BETWEEN operator)
  • Queries that uses composition operations.  (Using AND & OR operator)
  • Queries that uses pattern matching.  (Using %, _, and /(escape symbol))
  • Query that uses null and not null.

Sub-Queries or Nested Queries :

A subquery is a query within another query i. e. A query embedded in another query is called a subquery. It is also known as nested query. Subquery is used to return data that will be used in the main query as a condition, to further restrict the data to be retrieved.

Subqueries are of 2 types :

  • Independent Nested Query : Inner query doesn’t depend on outer query.
  • Co-Related Nested Query : Inner query uses attributes of outer query, hence dependent on outer query.

Examples of SubQueries :

Subquery 1: Find names of employees who work in Department number 11.

Solution : SELECT Name FROM Employee WHERE
           Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN 
                           WHERE Dno =11);

Subquery 2: Find name of employee whose basic pay is Greater then all basic pay of the employees working in Department number 10.

Solution : SELECT Name FROM Employee WHERE
           Salary > ALL(SELECT Salary FROM Employee WHERE Employee.Eno =
           (SELECT Work_IN.Eno FROM Work_IN WHERE Dno = 10))

Subquery 3 : Find the names of employees whose basic pay is greater than the average basic pay.

Solution : SELECT Name From Employee WHERE Salary > (SELECT AVG(Salary) FROM Employee);

Characteristics of subquery:

  • The inquiry will run first and substitute its results in outer query.
  • The variable from outer query can be used in the inner query but reverse is not true.
  • The subquery can be used in where and having clauses and sometimes in from clause also.

Classification of sub query

Types of Subquery - query and subquery in SQL

  • Uncorelated subquery : Inner query will run always only once. If it is independent of outer query then it is called uncorrelated subquery. For Example :
    SELECT Name FROM Employee WHERE
    Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN);
  • Corelated Subquery : Inner query will run some time as many times as the number of rows are available in `the outer query. If it is refer a variable in outer query then it is called correlated subquery. For Example :
    SELECT Name FROM Employee WHERE
    Employee.Eno = (SELECT Work_IN.Eno FROM Work_IN 
                      WHERE Dno =11);
  • Scalar subquery : It returns only one row and one column
  • Row subquery : It returns multiple columns but only one row
  • Table subquery :  It retrive multiple rows and multiple columns

Possible ways of writing sub query

The possible ways of writing sub query are :

  • By using IN predicate : (BETWEEN, LIKE IS NULL cannot be used with the subqueries).
  • By using quantifiers comparison predicates : (ANY, ALL)
  • By using exist and not exist

Restrictions for subquery

The following operators cannot be used in between main query and subquery:

  • BETWEEN and NOT BETWEEN operator
  • LIKE and NOT LIKE operator
  • IS NULL and IS NOT NULL operator

The following operators are normally used between query and subquery:

  • IN predicate : IN or NOT IN
  • Quantified comparison predicates : ANY, ALL, SOME
  • EXIST predicates: EXIST and NOT EXIST

Previous Home Next
Complete Format of SELECT Command Operators in SQL with Example

     

Incoming search terms:

  • queries and subqueries in sql in dbms

Leave a Reply