SQL Tutorial: Building an Inventory System Project 🎯

Welcome to CodeYourCraft's SQL Tutorial! Today, we'll be building an Inventory System that will help us manage items, track their quantities, and handle transactions. 📝

Why SQL?

SQL (Structured Query Language) is a powerful language designed to manage and manipulate databases. It's essential for developers to understand SQL to work on various projects, especially those involving data management. 💡

Creating Tables 📝

Before we start, let's create the tables for our inventory system.

sql

CREATE TABLE Items (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  quantity INT,
  price DECIMAL(10,2)
);

CREATE TABLE Transactions (
  id INT PRIMARY KEY,
  item_id INT,
  transaction_type VARCHAR(10),
  quantity INT,
  transaction_time TIMESTAMP
);

💡 Pro Tip: Always give your table names, column names, and data types careful consideration to ensure better performance and data organization.

Inserting Data 📝

Now that we have our tables set up, let's insert some data.

sql

INSERT INTO Items (id, name, quantity, price) VALUES (1, 'Laptop', 5, 1000.00);
INSERT INTO Items (id, name, quantity, price) VALUES (2, 'Monitor', 3, 300.00);

Querying Data 📝

Let's see how to retrieve data from our tables.

sql

SELECT * FROM Items;
SELECT * FROM Transactions;

Performing Transactions 📝

Now we can start performing transactions on our items.

sql

INSERT INTO Transactions (id, item_id, transaction_type, quantity, transaction_time) VALUES (1, 1, 'Sold', 1, NOW());

💡 Pro Tip: When inserting transactions, make sure to update the item's quantity.

Updating Data 📝

Let's update the quantity of a laptop after a sale.

sql

UPDATE Items SET quantity = quantity - 1 WHERE id = 1;

Deleting Data 📝

If we ever need to remove an item or a transaction, we can use the DELETE statement.

sql

DELETE FROM Items WHERE id = 2;

Joining Tables 📝

Let's join our tables to get a comprehensive view of our inventory.

sql

SELECT Items.name, Transactions.transaction_type, Transactions.quantity
FROM Items
JOIN Transactions ON Items.id = Transactions.item_id;

Conclusion 📝

That's it! You now have a basic understanding of an SQL Inventory System. As you continue to learn SQL, explore more advanced concepts like subqueries, triggers, and stored procedures.

Stay tuned for more tutorials here at CodeYourCraft! 💡