SQL - with cte (Common Table Expression) - Day21

Опубликовано: 03 Июнь 2026
на канале: Learn with RD

144

‪@LearnWithRD0529‬
--------employee3 scripts -------

Create the Employee3 table
CREATE TABLE Employee3 (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50)
);

-- Insert some sample data into the Employee table
INSERT INTO Employee3 (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'John', 'Doe', 'HR');
INSERT INTO Employee3 (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'Shane', 'Warne', 'HR');
INSERT INTO Employee3 (EmployeeID, FirstName, LastName, Department)
VALUES (2, 'Jane', 'Smith', 'IT');
INSERT INTO Employee3 (EmployeeID, FirstName, LastName, Department)
VALUES (3, 'Bob', 'Johnson', 'Finance');

------other table scripts-----------
=============================================
-- Create the Customers table
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50)
);

-- Create the Orders table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderAmount DECIMAL(10, 2)
);

CREATE TABLE REWARDS(
CUSTOMERID INT,
ORDERID INT,
ORDERAMOUNT INT,
REWARD_POINTS INT
);
=====================================================

-- Insert some sample data into the Customers table
INSERT INTO Customers (CustomerID, CustomerName)
VALUES (1, 'Alice');
INSERT INTO Customers (CustomerID, CustomerName)
VALUES (2, 'Bob');
INSERT INTO Customers (CustomerID, CustomerName)
VALUES (3, 'Charlie');

-- Insert some sample data into the Orders table
INSERT INTO Orders (OrderID, CustomerID, OrderAmount)
VALUES (101, 1, 100.50);
INSERT INTO Orders (OrderID, CustomerID, OrderAmount)
VALUES (102, 1, 75.25);
INSERT INTO Orders (OrderID, CustomerID, OrderAmount)
VALUES (103, 2, 200.00);
INSERT INTO Orders (OrderID, CustomerID, OrderAmount)
VALUES (104, 2, 50.75);
INSERT INTO Orders (OrderID, CustomerID, OrderAmount)
VALUES (105, 3, 300.90);

-- Insert some sample data into the REWARDS table
INSERT INTO REWARDS VALUES(1,101,100.5,10);
INSERT INTO REWARDS VALUES(1,102,75.25,7.5);
INSERT INTO REWARDS VALUES(2,103,200.5,20);
INSERT INTO REWARDS VALUES(2,104,50.75,5);
INSERT INTO REWARDS VALUES(3,105,300.9,30);
==================================================