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.

Cet exercice fait partie du cours

Hierarchical and Recursive Queries in SQL Server

Afficher le cours

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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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;

Modifier et exécuter le code