- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Purpose and usage of subqueries in DB with the help of an example
Problem: What are DB2 subqueries and what is the use of subqueries? Explain subqueries with the help of an example on the ORDERS table.
Solution
A subquery in DB2 is a query within a query, i.e., nested query. A subquery is executed first followed by its parent query. We can have a subquery up to 15 levels.
For example, if we have below ORDERS table in DB2.
ORDER_ID | ORDER_TOTAL |
Z22345 | 1267 |
Z62998 | 6734 |
Z56902 | 8815 |
Z56911 | 78990 |
Z56915 | 432 |
Z56918 | 77453 |
If we want to extract all the orders from the ORDERS table which have ORDER_TOTAL more than overall average, then we can use the below sub query.
Example
SELECT ORDER_ID FROM ORDERS WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)
The result of the above query will be as below.
ORDER_ID | ORDER_TOTAL |
Z22345 | 78990 |
Z62998 | 77453 |
Advertisements