How To Make Subquery In Oracle
Today, I want to talk about to make Subquery in Oracle. A subquery is a SELECT statement that nested inside another statement such as SELECT, INSERT, UPDATE, or DELETE. You need this operations for showing datas from your database in a mixed format. Now lets start making a subquery in Oracle.
Assume that you have to pull the maximum salaried person from a person table. We can do it with this query.
1 2 3 4 5 6 7 |
SELECT id, name, salary FROM person WHERE salary = ( SELECT MAX( salary ) FROM person ); |
We will find the highest salary with max (salary) function in the subquery. Than we will get the highest salaried person info with where condition.
Now lets give another example of subquery in select clause. The following statement returns the person name, salary, and the average salary of persons according to their categories:
1 2 3 4 5 6 7 8 9 10 |
SELECT name, salary, ROUND( ( SELECT AVG( salary ) FROM person p1 WHERE p1.cat_id = p2.cat_id ), 2) avg_salary FROM person p2 ORDER BY name; |
This will be the last example I will give you on subquerying in Oracle. The following statement returns the top 10 orders with the highest values.
1 2 3 4 5 6 7 8 9 |
SELECT order_id, order_value FROM (SELECT order_id, SUM( quantity * unit_price ) order_value FROM order_items GROUP BY order_id ORDER BY order_value DESC ) WHERE rownum |
Thats all for subquerying in Oracle. If you have interested about MS SqlServer you can have a look at my other post Using Stored Procedures In MSSQL
1 Response
[…] You can have a look at my other post about How to make subquery in Oracle. […]