SQL Subquery

Subquery is a query with in a query.

Syntax:-

select * from table_name where column_name in (select column from table_name)
Highlighted part is called Subquery.

Examples:- 
Consider below table structures.

DEPT table

Deptno	Deptname	Location
1	HR	London
2	Marketing	Newyork
5	Management	Bejing

EMP table

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the details of all 'HR' employees.

Select * from EMP where deptno in (select deptno from DEPT  where deptname = 'HR');

Always Subquery will be executed first. If Subquery returns no data, main query also will not return any data.

2. Find the details of all 'Logistic' department employees if any.

Select * from EMP where deptno in (select deptno from DEPT  where deptname = 'Logistic');

The above query will not return any records because Subquery has no matching records 

3. Find the details of all employees earning lowest salary.

Select * from EMP where salary = (select min(salary) from EMP) ;  

4.  Find all employee details if there any Marketing dept

 Select * from EMP e where exists (select * from DEPT d where deptname = 'Marketing');   

Subquery returns more than one row error
Example 3 will return details of all employees earning lowest salary. While writing the subquery user forgot to mention min clause and submitted below query.

SQL> Select * from EMP where salary = (select salary from EMP) ;  

                                  *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

This error happened because main query expecting a single value from the Subquery but returning more than one row. When writing a single-row Subquery make sure it is returning single row.

SQL SOME operator

SOME operator is same as IN operator. This operator is rarely used.  

Read more about IN operator here

Syntax:

select columns from table_name where column_name = SOME ( values )

Examples:- 

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find all details of Bill, Solomon and Wendy employee. 

Select * from EMP where empname = SOME( 'Bill' , 'Solomon' ,'Wendy');

2. Find details of employee earning highest salary.

Select * from EMP where salary = SOME (select max(salary) from EMP) ;

SQL ANY operator

ANY operator is same as IN operator. This operator is rarely used in SQL.  

Read more about IN operator here

Syntax:

select columns from table_name where column_name = ANY ( values )

Examples:- 

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find all details of Bill, Solomon and Wendy employee. 

Select * from EMP where empname = ANY( 'Bill' , 'Solomon' ,'Wendy');

2. Find details of employee earning highest salary.

Select * from EMP where salary = ANY (select max(salary) from EMP) ;

SQL IN operator

IN operator uses to specify one or more values in where clause.

Syntax:

select columns from table_name where column_name IN ( values )

Examples:- 

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find all details of Bill, Solomon and Wendy employee. 

Select * from EMP where empname IN( 'Bill' , 'Solomon' ,'Wendy');

2. Find details of employee earning highest salary.

Select * from EMP where salary IN (select max(salary) from EMP) ;

3. Find details of employees earning less than highest salary.

Select * from EMP where salary NOT IN (select max(salary) from EMP) ;  

Not that NOT IN operator is a negation of IN operator. 

Installing Oracle 11g on Ubuntu

This article clearly describes installation of Oracle 11g release 2 on Ubuntu desktop
Installing oracle 11g on ubuntu

SQL COUNT function

COUNT function returns the total number from the expression. It is an example of aggregate functions.

Syntax:

1. select count(column_name) from table_name;
2. select count(*) from table_name;

Count(*) returns total number of rows.
Count(column_name) returns total number of rows have data. ie it ignores null columns.

Examples:-

Consider below EMP table structure

Empno Empname Manager Deptno 10 Bill 5 11 Solomon Bill 5 12 Susan Bill 5 13 Wendy Solomon 1 14 Benjamin Solomon 1 15 Tom Solomon 1 16 Henry Solomon 2 17 Robert Susan 2 18 Paul Solomon 2

1. Find the number of employees.

select count(*) from EMP;     

COUNT(*)

----------------

   9

2. Find the total number of managers.

select count(manager) from EMP;     

COUNT(MANAGER)

----------------

   8

3. Find all managers handling more than one employees.

select manager ,count(*) from EMP group by manager having count(*) > 1;

MANAGER  COUNT(*)
------------------ ----------
Bill                    2
Solomon         5

4. Find total number of managers.

select count(distinct manager) from EMP;

COUNT(DISTINCTMANAGER)
--------------------------------------------
    3

Related topics
AVG function
MAX Function

SQL AVG function

AVG function returns the average from the expression. It is an example of aggregate functions.

Syntax:

select avg(column_name) from table_name;

Examples:-

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the average salary among the employees.

select avg(salary) from EMP;     

AVG(SALARY)

------------------------

      9088.88889

2. Find the average salary among the employees in a department (Using where clause). 

select avg(salary) from EMP where deptno = 5;     

AVG(SALARY)

------------------------

      10666.6667

3. Find the average salary in each department (Using group by clause). 

select deptno,avg(salary) from EMP group by deptno;     

 DEPTNO  AVG(SALARY)

 ----------     ----------------------

 1              8033.33333
  2              8566.66667
  5              10666.6667

4. Find the all department details having average salary higher than 9000 (Using having by clause). 

select deptno,avg(salary) from EMP group by deptno having avg(salary) > 9000;


DEPTNO AVG(SALARY)
----------    ---------------------
 5             10666.6667

SQL MIN function

MIN function returns the minimum from the expression. It is an example of aggregate functions.

Syntax:

select min(column_name) from table_name;

Examples:-

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the lowest salary among the employees.

select min(salary) from EMP;     

MIN(SALARY)

------------------------

      7500

2. Find the lowest salary among the employees in a department (Using where clause). 

select min(salary) from EMP where deptno = 5;     

MIN(SALARY)

------------------------

      10000

3. Find the lowest salary in each department (Using group by clause). 

select deptno,min(salary) from EMP group by deptno;     

 DEPTNO  MIN(SALARY)

 ----------     -----------

 1               7500

 2               8500

 5               10000

 

SQL MAX function

MAX function returns the maximum from the expression. It is an example of aggregate functions.

Syntax:

select max(column_name) from table_name;

Examples:-

Consider below EMP table structure

Empno	Empname	Salary	Deptno
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2

1. Find the highest salary among the employees.

select max(salary) from EMP;     

MAX(SALARY)

------------------------

      12000

2. Find the highest salary among the employees in a department (Using where clause). 

select max(salary) from EMP where deptno = 1;     

MAX(SALARY)

------------------------

      9000

3. Find the highest salary in each department (Using group by clause). 

select deptno,max(salary) from EMP group by deptno;     

 DEPTNO  MAX(SALARY)

 ----------     -----------

1               9000

2               9500

5               12000