Implementing CRUD operations for the project

Let’s Input Some Data in it.

-- Insert data into inventory_items table
INSERT INTO inventory_items (item_id, item_name, item_description, quantity, price)
VALUES
    (1, 'Item 1', 'Description of Item 1', 100, 10.99),
    (2, 'Item 2', 'Description of Item 2', 50, 20.49),
    (3, 'Item 3', 'Description of Item 3', 75, 15.99);

-- Insert data into stock_transactions table
INSERT INTO stock_transactions (transaction_id, item_id, transaction_type, quantity, transaction_date)
VALUES
    (1, 1, 'IN', 50, '2024-03-10 08:00:00'),
    (2, 2, 'OUT', 25, '2024-03-11 10:30:00'),
    (3, 1, 'IN', 30, '2024-03-12 14:45:00');

-- Insert data into reports table
INSERT INTO reports (report_id, report_name, report_description, report_date)
VALUES
    (1, 'Report 1', 'Description of Report 1', '2024-03-10 12:00:00'),
    (2, 'Report 2', 'Description of Report 2', '2024-03-11 12:00:00');

-- Insert data into suppliers table
INSERT INTO suppliers (supplier_id, supplier_name, supplier_contact)
VALUES
    (1, 'Supplier 1', 'Contact info for Supplier 1'),
    (2, 'Supplier 2', 'Contact info for Supplier 2');

Now think…Can you answer below questions…

Let’s try answering these questions:

1. Inventory Items:

• Which items are running low in stock (quantity < threshold)?
• What is the average price of all items in the inventory?
• Which items have not had any transactions in the last month?

2. Stock Transactions:

• What is the total quantity of items received (IN) from each supplier?
• What is the total quantity of items issued (OUT) for each item?
• What is the total value of items issued (OUT) for each transaction type (e.g., sales, returns)?

3. Reports:

• How many reports were generated in the last week/month?
• What is the average number of reports generated per day?

4. Suppliers:

• Which supplier has the highest total value of items supplied?
• How many unique items does each supplier supply?
• Which supplier has the longest relationship (oldest transaction) with the organization?

5. General:

• What is the total value of items in stock for each item category?
• How many items have a quantity greater than a certain threshold?
• What is the total quantity and value of items in stock for each warehouse location (if applicable)?