Wednesday, August 7, 2024

SQL Sub Query

A subquery or inner query or nested query is a query within another SQL query. 

We can use subquery with select, update, insert and delete statements.

In the subquery, order by command cant be used, but Group by command can be used to perform the same function as order by command.

A subquery can be placed as follow:

  • Where clause
  • From clause
  • Having clause
Syntax:
Select columns From table_name
Where col_nane operator (subquery).

Examples with Where clause:

Get names of all the students who scored more than class average marks.

Select name, marks from student
Where marks > (select avg(marks) from student);

Find the names of all students with even roll numbers.

Select name, rollno from student
Where rollno in (select rollno from student where rollno % 2 = 0);

Example with From clause:

Find the max numbers from the students of Delhi.

Select max(marks) 

From (select * from student where city = 'Delhi' ) as temp;

Example with select:

Select (select max(marks) from student ), name

From student.

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...