📊 Build Report Views in Oracle SQL: Practice Tutorial

📊 Practice: Build Report Views in Oracle SQL

Views help create reusable, readable reports in Oracle SQL. Below are steps to build simple report views and use inline views to generate detailed reports.

1. Create a simple report view

CREATE VIEW EMP_SALARY_REPORT AS
SELECT EMP_ID, EMP_NAME, DEPT_ID, SALARY
FROM EMPLOYEE
WHERE SALARY > 30000;

This view filters employees earning more than 30,000, handy for salary reports.

2. Query the report view

SELECT * FROM EMP_SALARY_REPORT;

3. Create an inline view to summarize salary by department

SELECT dept_summary.DEPT_ID, dept_summary.TOTAL_SALARY, dept_summary.AVG_SALARY
FROM (
  SELECT DEPT_ID,
         SUM(SALARY) AS TOTAL_SALARY,
         AVG(SALARY) AS AVG_SALARY
  FROM EMPLOYEE
  GROUP BY DEPT_ID
) dept_summary
WHERE dept_summary.AVG_SALARY > 40000;

This query calculates total and average salary per department, filtering departments with average salary above 40,000.

4. Create a view using an inline view

CREATE VIEW DEPT_SALARY_REPORT AS
SELECT dept.DEPT_ID, dept.TOTAL_SALARY, dept.AVG_SALARY
FROM (
  SELECT DEPT_ID,
         SUM(SALARY) AS TOTAL_SALARY,
         AVG(SALARY) AS AVG_SALARY
  FROM EMPLOYEE
  GROUP BY DEPT_ID
) dept
WHERE dept.AVG_SALARY > 40000;

This view encapsulates the summary logic and can be reused anytime.

5. Query the department salary report

SELECT * FROM DEPT_SALARY_REPORT;

---

🧪 Practice building report views to simplify your SQL queries and enhance report generation.