SQL Queries and Sub-queries

What is QUERY?

• A query is an operation that retrieves data from one or more tables or views.
• SELECT statement can be used for retrieving the data from various tables in a database.

Example:

<Employee> Table

Eid	Ename	Age	City	Salary
E001	ABC	29	Pune	20000
E002	PQR	30	Pune	30000
E003	LMN	25	Mumbai	5000
E004	XYZ	24	Mumbai	4000
E005	STU	32	Bangalore	25000

1. Selecting all columns (SELECT *)

SELECT * FROM Employee;

2. Displaying particular record with condition (WHERE)

SELECT Ename FROM Employee
WHERE City = 'Pune';
    
Output:

Ename
ABC
PQR

3. SELECT using DISTINCT

DISTINCT clause is used to eliminate the duplicate values from the table.

Example:
SELECT DISTINCT city FROM Employee;

Output:

City
Bangalore
Mumbai
Pune

4. SELECT using IN

'IN' determines whether a specified value matches any value in a sub-query or a list.

Example:
SELECT Eid, Ename FROM Employee
WHERE Salary IN (5000, 20000);

Output:

Eid	Ename
E001	ABC
E003	LMN

5. SELECT using BETWEEN

'BETWEEN' is used to get those values who fall within a range.

Example:
SELECT Eid, Ename, Salary FROM Employee
WHERE Salary BETWEEN 5000 AND 30000;
   
Output:

Eid	Ename	Salary
E001	ABC	20000
E002	PQR	30000
E003	LMN	5000
E005	STU	25000

NOT BETWEEN

Example:
SELECT Eid, Ename, Age FROM Employee
WHERE Age NOT BETWEEN 24 AND 25;
   
Output:

Eid	Ename	Age
E001	ABC	29
E002	PQR	30
E005	STU	32

6. SELECT using LIKE

• LIKE clause is used for comparing a value with similar values using wildcard operators (% and _ ).
• Suppose, if you want user name starts with 'S', then use 'LIKE' clause as follows,

Example:
SELECT Ename, City, Salary FROM Employee
WHERE Ename LIKE 'S%';

Output:

Eid	Ename	City	Salary
E005	STU	Bangalore	25000

LIKE Statements

Statements	Description
LIKE 'S%'	It finds any value which starts with 'S'.
LIKE '%S%'	It finds any value which have 'S' in any position.
LIKE '_SS%'	It finds any value which have 'SS' in the second and third positions.
LIKE 'S_%_%'	It finds any value which starts with 'S' and have at least three characters in length.
LIKE '%S'	It finds any value which ends with 'S'.
LIKE '_S%P'	It finds any value which have 'S' in the second position and ends with 'P'.
LIKE 'S___P'	It finds any value in a five digit numbers which start with 'S' and ends with 'P'.

Note: In the above LIKE statements, instead of 'S' and 'P' you can use any value according to your needs while writing the query. 'S' and 'P' are examples, mentioned for understanding.

6. SELECT using GROUP BY

GROUP BY clause is used to arrange the data into groups.

Example:
SELECT Eid, Ename, City, MAX (Salary) FROM Employee
GROUP BY Salary;

Output:

Eid	Ename	City	Salary
E004	XYZ	Mumbai	4000
E003	LMN	Mumbai	5000
E001	ABC	Pune	20000
E005	STU	Bangalore	25000
E002	PQR	Pune	30000

7. SELECT using ORDER BY

• ORDER BY clause specifies an order in which to return the rows.
• It is used to sort the data in ascending or descending order which is based on one or more columns.

Syntax:
SELECT <column-list>
FROM <table_name>
WHERE <condition>
ORDER BY <column1, column2, .. column_n> <ASC | DESC>;

Example:
SELECT Eid, Ename FROM Employee
ORDER BY Ename asc;

Output:

Eid	Ename
E001	ABC
E002	LMN
E003	PQR
E005	STU
E004	XYZ

8. SELECT using AND, OR and NOT

i. AND
AND requires that two conditions are true.

Syntax:
SELECT <list_of_column_name>
FROM <list_of_table_name>
WHERE <condition1> AND <condition2>;

Example:
SELECT * FROM Employee
WHERE Age= 30 AND City="Pune";

Output:

Eid	Ename	Age	City	Salary
E002	PQR	30	Pune	30000

ii. OR
OR requires that one of two conditions is true.

Syntax:
SELECT <list_of_column_name>
FROM <list_of_table_name>
WHERE <condition1> OR <condition2>;

Example:
SELECT * FROM Employee
WHERE City="Pune" OR City="Mumbai";

Output:

Eid	Ename	Age	City	Salary
E001	ABC	29	Pune	20000
E002	PQR	30	Pune	30000
E003	LMN	25	Mumbai	5000
E004	XYZ	24	Mumbai	4000

iii. NOT
NOT neglects the specified condition.

Syntax:
SELETE <list_of_column_name>
FROM <list_of_table_name>
WHERE NOT <condition>;

Example:
SELECT * FROM Employee
WHERE NOT City="Pune";

Output:

Eid	Ename	Age	City	Salary
E003	LMN	25	Mumbai	5000
E004	XYZ	24	Mumbai	4000
E005	STU	32	Bangalore	25000

SUB-QUERY

• Sub-query is a inner query within another query. It is used to return data in the main query as a condition to retrieved the data.
• Sub-queries are nested SELECT statement.
• It is a query within a query.
• Sub-queries are mostly appear within the WHERE or HAVING clause of another SQL statement.
• It defines with another SELECT statement with a FROM clause and optional WHERE, GROUP BY and HAVING clauses.
• It produces a single column of data as its result.
• In a sub-query, ORDER BY clause cannot be specified. ORDER BY clause is specified in the main query.
• Sub-query is always enclosed in parentheses.
• It cannot be a UNION, only a single SELECT statement is allowed.
• In a sub-query, 'SELECT *' cannot be used unless the referring table has only one column and nested query is evaluated first.

Example:
SELECT Ename, Salary FROM Employee
WHERE Salary IN
(SELECT MAX (Salary) FROM Employee);

Output:

Ename	Salary
PQR	30000

  
Following are the comparison operators where sub-queries are expressed as one SELECT statement connected to another,

Comparison Operator

Operator	Description
=	Equal to
< > or !=	Not equal to
>	Greater than
<	Less than
>=	Greater than Equal to
<=	Less than Equal to

Multiple-row Comparison Operator

Operator	Description
IN	Equal to any value retrieved in an Inner query.
NOT IN	Not equal to any value retrieved in an inner query.
= ANY	Equal to any value retrieved in an inner query – Logical OR.
> ANY, >= ANY	Retrieves any highest value.
< ANY, <= ANY	Retrieves any smallest value.
= ALL	Equal to all values retrieved in an Inner query – Logical AND.
> ALL, >= ALL	Retrieves all highest values.
< ALL, <=ALL	Retrieves all smallest values.