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.

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.

Mandalika

Published on 30-Nov-2020 09:27:04

• Related Questions & Answers
• What is the purpose of COALESCE function? Explain with the help of an example.
• Explain SHARED, UPDATE and EXCLUSIVE locks with the help of an example
• Explain the concept of LOCK PROMOTION with the help of an example
• Explain join operations with the help of an example in DBMS
• Explain the concept of DYNAMIC SQL in DB2 with the help of an example
• What are COLUMN functions in DB2? Explain with the help of an example
• What is Fixed-list SELECT? Give the syntax and explain with help of an example
• What is the difference between DB2 JOIN and UNION? Explain with the help of an example
• Purpose and usage of SAVEPOINT in COBOL-DB2 program
• Example and usage of JOINS in DB2
• Purpose and usage of ROW-ID and SEQUENCE in a DB2
• What is the use of the VALUE function in a DB2? Explain with the help of an example
• What is the purpose and usage of SCROLLABLE CURSOR in COBOLDB2 program?
• What is CLUSTERED INDEX in DB2? Explain with the help of practical example.
• What is the usage and purpose of DCLGEN and host variables used in COBOL-DB2 program