CREATING DERIVED TABLES Oracle SQL

Understanding Derived Tables:

Derived tables, also known as inline views, are temporary result sets generated within the scope of a larger query. They are defined in the FROM clause of a SELECT statement and serve as a way to organize and structure your data before applying additional transformations or aggregations.

Example,.

 

CREATE TABLE TBL_DEPARTMENT AS
(SELECT DEPARTMENT
FROM
(SELECT DEPARTMENT, COUNT(*) AS NUMBER_PER_DEPARTMENT
FROM TBL_EMPLOYEE
GROUP BY DEPARTMENT))
;

Benefits of Using Derived Tables:

Improved Code Readability: Break down complex queries into smaller, more manageable parts.
Reusability: Easily reuse derived tables in multiple queries, reducing redundancy.
Performance Optimization: Allow the query optimizer to work more efficiently by providing a structured view of the data.

Best Practices:

Keep it Concise: Create derived tables that serve a specific purpose, avoiding unnecessary complexity.
Use Meaningful Aliases: Choose clear and concise alias names to enhance code readability.
Test Performance: Monitor the performance impact of derived tables, especially in large datasets.

Add a Column to a Table

EXAMPLE

This SQL code retrieves data from two tables, tbl_department (aliased as D) and tbl_employee (aliased as E). The goal is to get information about employees and their respective departments while ranking them based on their employee numbers within each department. The final result is filtered to include only the top 5 employees in each department.

The INNER QUERY Explained

Outer Query

  • The outer query selects all columns (*) from the result set of the inner query.
  • It filters the results to include only those where MY_RANK is less than or equal to 5, effectively retrieving the top 5 employees in each department.
  • The final result set is ordered by DEPARTMENT and EMPLOYEE_NUMBER.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top