A CTE for high-paid IT-positions

In the previous exercise, you created a CTE to find IT positions. Now, you will combine these results with another CTE on the salary table. You will use multiple CTE definitions in a single query. Notice that a comma is used to separate the CTE query definitions. The salary table contains some more information about the ID and salary of employees. Your task is to create a second CTE named ITsalary and JOIN both CTE tables on the employees ID. The JOIN should select only records having matching values in both tables. Finally, the task is to find only employees earning more than 3000.

This exercise is part of the course

Hierarchical and Recursive Queries in SQL Server

View Course

Exercise instructions

Define the second CTE, ITsalary, with the fields ID and Salary.
Find salaries above 3000.
Combine the two CTEs using a JOIN of matching IDs and select the name, salary, and position of the selected employees.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

WITH ITjobs (ID, Name, Position) AS (
    SELECT 
  		ID, 
  		Name,
  		Position
    FROM employee
    WHERE Position like 'IT%'),
    
-- Define the second CTE table ITsalary with the fields ID and Salary
___ (___, ___) AS (
    SELECT
        ID,
        Salary
    FROM Salary
  	-- Find salaries above 3000
    WHERE ___ ___ 3000)
    
SELECT 
	ITjobs.NAME,
	ITjobs.POSITION,
    ITsalary.Salary
FROM ITjobs
    -- Combine the two CTE tables the correct join variant
    ___ ___ ITsalary
    -- Execute the join on the ID of the tables
    ON ITjobs.ID = ITsalary.ID;

Edit and Run Code