Mastering Database Management

0 of 78 lessons complete (0%)

Project: Inventory Managment

Implementing CRUD operations for the project

You don’t have access to this lesson

Please register or sign in to access the course content.

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)?