CTE Example in SQL

Sure! Here’s a complete overview of how to calculate salaries greater than the average salary using Oracle SQL, including the creation of a sample employees table, inserting data, and querying with Common Table Expressions (CTEs).

Step-by-Step Overview

Step 1: Create the Employees Table

You need to create the employees table with the required columns. The salary column should be of type NUMBER in Oracle.

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER(10, 2)
);

Step 2: Insert Sample Data

Next, populate the employees table with some sample data.

INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES
(1, 'John', 'Doe', 60000),
(2, 'Jane', 'Smith', 75000),
(3, 'Sam', 'Brown', 50000),
(4, 'Emily', 'Davis', 90000),
(5, 'Michael', 'Johnson', 120000),
(6, 'Sarah', 'Wilson', 45000),
(7, 'David', 'Lee', 80000);

Step 3: Query to Find Salaries Greater than Average Salary

Now, you can use CTEs to find employees with salaries greater than the average salary. Here are two approaches:

Approach 1: Using CTE with a Subquery

This approach calculates the average salary first and then compares individual salaries against that average.

WITH avg_salary_cte AS (
SELECT AVG(salary) AS avg_salary
FROM employees
)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT avg_salary FROM avg_salary_cte);

Approach 2: Using CTE with Window Function

This approach calculates the average salary using a window function, allowing you to keep the average salary in the same query context.

WITH employee_salaries AS (
SELECT employee_id, first_name, last_name, salary,
AVG(salary) OVER () AS avg_salary
FROM employees
)
SELECT employee_id, first_name, last_name, salary
FROM employee_salaries
WHERE salary > avg_salary;

Expected Output

For both queries, the output will list employees whose salaries exceed the average salary. Based on the sample data, the expected results would include:

Summary

Table Creation: Defined the employees table with necessary fields.

Data Insertion: Populated the table with sample employee data.

Salary Calculation: Used CTEs to calculate and retrieve employees with salaries greater than the average.

Feel free to modify or extend this overview based on your specific requirements or let me know if there are any other areas you’d like to explore!