Answers of the questions

Inventory Items:

Question: What are the names and quantities of all items in the inventory?

SELECT item_name, quantity FROM inventory_items;

Question: What is the total value of the inventory (quantity * price) for each item?

SELECT item_name, quantity * price AS total_value FROM inventory_items;

Question: Which items are running low in stock (quantity < threshold)?s

SELECT item_name, quantity FROM inventory_items WHERE quantity < threshold;

Question: What is the average price of all items in the inventory?

SELECT AVG(price) AS average_price FROM inventory_items;

Question: Which items have not had any transactions in the last month?

SELECT i.item_name FROM inventory_items i LEFT JOIN stock_transactions st ON i.item_id = st.item_id WHERE st.transaction_date IS NULL OR st.transaction_date < DATE_SUB(NOW(), INTERVAL 1 MONTH);

Stock Transactions:

Question: What are the total quantities of items that were received (IN) or issued (OUT) for a given period?

SELECT transaction_type, SUM(quantity) AS total_quantity FROM stock_transactions WHERE transaction_date BETWEEN 'start_date' AND 'end_date' GROUP BY transaction_type;

Question: Which items have had the most transactions (IN or OUT) in the last month?

SELECT item_id, COUNT(*) AS transaction_count FROM stock_transactions WHERE transaction_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH) GROUP BY item_id ORDER BY transaction_count DESC LIMIT 1;

Reports:

Question: What are the names and descriptions of all reports generated?

SELECT report_name, report_description FROM reports;

Question: When was the last report generated, and what was its name?

SELECT report_name, MAX(report_date) AS last_generated_date FROM reports;

Question: How many reports were generated in the last week/month?

SELECT COUNT(*) AS report_count FROM reports WHERE report_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

Suppliers:

Question: What are the names and contact information of all suppliers?

SELECT supplier_name, supplier_contact FROM suppliers;

Question: Which supplier has the highest number of items supplied?

SELECT s.supplier_name, COUNT(st.item_id) AS items_supplied FROM suppliers s JOIN stock_transactions st ON s.supplier_id = st.supplier_id GROUP BY s.supplier_id ORDER BY items_supplied DESC LIMIT 1;

Question: Which supplier has the highest total value of items supplied?

SELECT s.supplier_name, SUM(i.quantity * i.price) AS total_value_supplied FROM suppliers s JOIN inventory_items i ON s.supplier_id = i.supplier_id GROUP BY s.supplier_id ORDER BY total_value_supplied DESC LIMIT 1;