Derived Table in DB2 you can call as a temporary table. When the main query ends, the derived table also ends. With SELECT statement, you can create a derived table.
Key Interview Points on Derived Table
- Exists only within a query
- Materialized by a SELECT Statement inside a query
- Space comes from the user’s space
- Deleted when the query ends
(select avg(salary) as avg_salary from employee_table)
Here, sriniderived is the derived Table.
The avg_salary is the column name. The query inside the ‘()’ is called SELECT statement for derived table.
SQL Query for Derived Table in Other way
with sriniderived(avgsal) as
(select avg(salary) from employee_table)
The name with clause is “sriniderived” you can call it as derived table.
The SQL query with select statement in “()” is for dervied table. That means derived table will form here.
Next you need to give SELECT * statement to retrieve the data.