Oracle SQL and PL/SQL
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
EMP table
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.
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.
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.
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.
Consider below EMP table structure
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
Installing oracle 11g on ubuntu
Subscribe to:
Posts (Atom)