What is CTE in SQL SERVER PART 9 With Example Code

It seems like you're working on finding the third-highest salary from the NewStaff table using different methods including Common Table Expressions (CTEs) and subqueries. Here's a breakdown and correction of your SQL statements

-- Create table NewStaff
 
CREATE TABLE NewStaff
(
     ID INT PRIMARY KEY IDENTITY,
     FirstName NVARCHAR(max),
     LastName NVARCHAR(max),
     Gender NVARCHAR(max),
     Salary INT
)
 
GO
 
 
-- Insert new values into NewStaff (changed values)
INSERT INTO NewStaff VALUES ('A', 'EEE', 'Male', 73000)
INSERT INTO NewStaff VALUES ('B', 'FF', 'Female', 58000)
INSERT INTO NewStaff VALUES ('C', 'HH', 'Male', 49000)
INSERT INTO NewStaff VALUES ('D', 'KKK', 'Female', 73000)
INSERT INTO NewStaff VALUES ('E', 'LL', 'Male', 53000)
INSERT INTO NewStaff VALUES ('F', 'PP', 'Female', 35000)
INSERT INTO NewStaff VALUES ('G', 'TT', 'Male', 42000)
INSERT INTO NewStaff VALUES ('H', 'XX', 'Male', 89000)
GO
 
Select Max(Salary) from NewStaff
 
 
SELECT TOP 1 SALARY
FROM (
      SELECT DISTINCT TOP N SALARY
      FROM NewStaff
      ORDER BY SALARY DESC
      ) RESULT
ORDER BY SALARY
 
 
WITH RESULT AS
(
    SELECT SALARY, DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK FROM NewStaff
)
 
SELECT TOP 1 SALARY FROM RESULT WHERE DENSERANK = N
 
WITH RESULT AS
(
    SELECT SALARY,ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER FROM NewStaff
)
 
SELECT SALARY FROM RESULT WHERE ROWNUMBER = 3
 
 
 
 
 
WITH cte_name (column1, column2, ...) AS (
    -- CTE query
    SELECT column1, column2, ... FROM your_table WHERE conditions
)
 
-- Main query
 
SELECT * FROM cte_name;
 
 
 
-- Sample Employee table
 
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(MAX),
    LastName NVARCHAR(50),
    Salary DECIMAL(10, 2),
    DepartmentID INT
);
 
 
 
INSERT INTO Employee (EmployeeID, FirstName, LastName, Salary, DepartmentID)
VALUES
    (1, 'A', 'RR', 50000.00, 1),
    (2, 'B', 'DD', 60000.00, 1),
    (3, 'C', 'WW', 55000.00, 2),
    (4, 'D', 'QQ', 70000.00, 2);
 
 
 
-- Using a CTE to calculate total salary by department
WITH DepartmentTotalSalary AS (
    SELECT
        DepartmentID,
        SUM(Salary) AS TotalSalary FROM Employee GROUP BY DepartmentID )
 
SELECT
    E.EmployeeID,
    E.FirstName,
    E.LastName,
    E.Salary,
    DTS.TotalSalary
FROM
    Employee E
JOIN
    DepartmentTotalSalary DTS ON E.DepartmentID = DTS.DepartmentID;
 
 
 
-- Sample Employee table
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(10, 2),
    DepartmentID INT
);
 
 
INSERT INTO Employee (EmployeeID, FirstName, LastName, Salary, DepartmentID)
VALUES
    (1, 'A', 'EE', 50000.00, 1),
    (2, 'B', 'OO', 60000.00, 1),
    (3, 'C', 'RR', 55000.00, 2),
    (4, 'D', 'LL', 70000.00, 2);
 
 
-- Using a CTE to calculate total salary by department
WITH DepartmentTotalSalary AS (
    SELECT DepartmentID, SUM(Salary) AS TotalSalary FROM Employee GROUP BY DepartmentID
)
 
SELECT
    E.EmployeeID,
    E.FirstName,
    E.LastName,
    E.Salary,
    DTS.TotalSalary
FROM
    Employee E
JOIN
   DepartmentTotalSalary DTS ON E.DepartmentID = DTS.DepartmentID;
 
 
 
-- Create a temporary table
CREATE TABLE #TempTable (
    ID INT,
    Name VARCHAR(50),
    Value INT
);
 
 
 
-- Insert some data into the temporary table
INSERT INTO #TempTable (ID, Name, Value)
VALUES (1, 'John', 100),
       (2, 'Jane', 150),
       (3, 'Bob', 200);
 
 
-- Display the original data
SELECT * FROM #TempTable;
 
-- Use a CTE with UPDATE and SELECT to modify the values
WITH CTE AS (
    SELECT ID, Name, Value * 2 AS NewValue FROM #TempTable
)
 
UPDATE #TempTable
SET Value = CTE.NewValue FROM
#TempTable
JOIN CTE ON #TempTable.ID = CTE.ID;
 
-- Display the updated data
SELECT * FROM #TempTable;
 
 
-- Drop the temporary table when done
DROP TABLE #TempTable;
 
 
-- Create a temporary table
CREATE TABLE #TempTable (
ID INT, Name VARCHAR(50), Value INT
);
 
 
 
-- Insert some data into the temporary table
INSERT INTO #TempTable (ID, Name, Value)
VALUES (1, 'John', 100),
       (2, 'Jane', 150),
       (3, 'Bob', 200);
 
 
-- Create another table for demonstration
CREATE TABLE OtherTable (
    ID INT,
    Multiplier INT
);
 
 
 
-- Insert some data into the other table
INSERT INTO OtherTable (ID, Multiplier)
VALUES (1, 2),
       (2, 3),
       (3, 4);
 
 
-- Display the original data in both tables
SELECT * FROM #TempTable;
SELECT * FROM OtherTable;
 
 
 
-- Use a CTE with UPDATE and JOIN to modify the values
WITH CTE AS (
    SELECT
        T.ID,
        T.Name,
        T.Value * O.Multiplier AS NewValue
    FROM
        #TempTable T
    JOIN OtherTable O ON T.ID = O.ID
)
 
UPDATE #TempTable
SET
    Value = CTE.NewValue
FROM
    #TempTable
JOIN CTE ON #TempTable.ID = CTE.ID;
 
 
 
-- Display the updated data
SELECT * FROM #TempTable;
 
 
 
-- Drop the temporary tables when done
DROP TABLE #TempTable;
DROP TABLE OtherTable;