- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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 |
- Related Articles
- What is the purpose of COALESCE function? Explain with the help of an example.
- Describe the process of neutralisation with the help of an example.
- Explain SHARED, UPDATE and EXCLUSIVE locks with the help of an example
- Explain join operations with the help of an example in DBMS
- Explain the concept of LOCK PROMOTION with the help of an example
- Purpose and usage of SAVEPOINT in COBOL-DB2 program
- Explain the concept of DYNAMIC SQL in DB2 with the help of an example
- What is symbiotic relationship? Explain with the help of an example
- What are COLUMN functions in DB2? Explain with the help of an example
- Purpose and usage of ROW-ID and SEQUENCE in a DB2
- Example and usage of JOINS in DB2
- What is the purpose and usage of SCROLLABLE CURSOR in COBOLDB2 program?
- With the help of an example, explain how diffusion of gases in water is essential?
- What is Fixed-list SELECT? Give the syntax and explain with help of an example
- How do metals react with dilute acids? Explain with the help of an example.

Advertisements