כארמל

CREATE TABLE Users(
        id int PRIMARY KEY,
        name varchar(255),
        address varchar(255),
        email varchar(255)
    );

CREATE TABLE Orders(
        id int PRIMARY KEY,
        user_id int,
        total_price double,
        FOREIGN KEY (user_id) REFERENCES Users(id)
    );


CREATE TABLE Products(
        serial_code int PRIMARY KEY,
        name varchar(255),
        description TEXT,
        price double,
        quantity int,
        size varchar(255)
    );


CREATE TABLE Order_Products(
        id int,
        user_id int,
        serial_code int,
        PRIMARY KEY (id, serial_code)
    );




-- Users
INSERT INTO Users (id, name, address, email)
VALUES
    (1, 'John Doe', '123 Main St, City, Country', '[email protected]'),
    (2, 'Jane Smith', '456 Elm St, City, Country', '[email protected]'),
    (3, 'Bob Johnson', '789 Oak St, City, Country', '[email protected]');

-- Products
INSERT INTO Products (serial_code, name, description, price, quantity, size)
VALUES
    (101, 'Product A', 'This is product A', 50.2, 100, 'Medium'),
    (102, 'Product B', 'Product B description', 30.6, 50, 'Large'),
    (103, 'Product C', 'Product C details', 49.99 ,75, 'Small');

-- Orders
INSERT INTO Orders (id, user_id, total_price)
VALUES
    (1, 1, 150.99),
    (2, 2, 75.50),
    (3, 3, 210.25);

-- Order_Products
INSERT INTO Order_Products (id, user_id, serial_code)
VALUES
    (1, 1, 101),
    (2, 1, 102),
    (3, 2, 101),
    (4, 3, 101),
    (5, 3, 103);


1. we get order_id = 6, user_id = 1, product_id_1 = 101, product_id_2 = 102
   update the database:

        First updating Order_Products:
            INSERT INTO Order_Products (id, user_id, serial_code)
                VALUES (6, 1, 101), (6, 1, 102);

        Then update Orders:
        A.  INSERT INTO Orders (id, user_id, total_price)
            SELECT 6, 1, sum(price) from Products where serial_code =
                                        (SELECT serial_code from Order_Products where id = 6);

        B.  INSERT INTO Orders (id, user_id, total_price)
            SELECT 6, 1, sum(price) from Products where Products.serial_code IN
                                    (SELECT serial_code FROM Order_Products WHERE id = 6);


    Ask Carmel what is the difference between A. and B. and what is 'IN'?