SELECT - INSERT INTO SELECT

Certainly! Let's create a practical example involving a source table and a target table. Imagine we have an e-commerce platform with a table that keeps track of all the products, and we want to create a promotional campaign for products that have been highly rated by customers.

First, we'll create a source table named Products that contains product information, including ratings:

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(255),
    Rating DECIMAL(3, 2)
);

Let's assume the Products table has the following data:

Next, we'll create a target table named Promotions where we want to insert products with a rating of 4.5 or higher for a special discount campaign:

create Promotions table without any values! it should be empty!(not always just for our purpose :) )

CREATE TABLE Promotions (
    ProductID INT,
    ProductName VARCHAR(255)
);

Now, we'll use the INSERT INTO SELECT statement to insert the ProductID and ProductName from the Products table into the Promotions table for products with a rating of 4.5 or higher:

INSERT INTO Promotions (ProductID, ProductName)
SELECT ProductID, ProductName
FROM Products
WHERE Rating >= 4.5;

After executing this query, the Promotions table will contain the following data:

This is a practical example of how the INSERT INTO SELECT statement can be used in the real world to manage promotions based on product ratings in an e-commerce scenario.