you have an e-commerce website where customers can place orders for products. You have two tables in your database: orders and inventory. The orders table contains information about each customer order, such as the order number, order date, product number, quantity and total amount (we are limiting one product per order). The inventory table contains information about the products you sell, such as product name, product ID, and the number of items in stock.

You want to ensure that the inventory is updated accurately when a customer places an order, so you need to use a transaction to ensure the integrity of the data.

 

1. Should create a database with the name: ADTLab3. 

2. Should create two tables - Orders, and Inventory. Add sample data to Inventory. Submit the screenshots of successful query execution, and the screenshot of inventory table after inserting data.

-- Create a table for orders

CREATE TABLE Orders (

     order_id INT IDENTITY(1,1) PRIMARY KEY,

     order_date DATE,

     product_id INT,

     quantity INT,

     total DECIMAL(10,2)

);

 

-- Create a table for inventory

CREATE TABLE Inventory (

   Productid INT IDENTITY(1,1) PRIMARY KEY,

   product_name VARCHAR(50),

   stock_count INT,

   price DECIMAL(10, 2)

);

 

--Insert sample data into inventory table

INSERT INTO Inventory (product_name, stock_count, price) VALUES ('Shirt', 50, 19.99);

INSERT INTO Inventory (product_name, stock_count, price) VALUES ('Pants', 30, 29.99);

INSERT INTO Inventory (product_name, stock_count, price) VALUES ('Socks', 100, 4.99);