SQL Tutorial: E-commerce Database Project 🚀

Welcome to our SQL tutorial, where we'll dive into building an E-commerce Database! By the end of this project, you'll have a solid understanding of SQL and how to apply it to real-world scenarios. Let's get started!

What is SQL? 💡

SQL (Structured Query Language) is a language used to communicate with databases and retrieve, manipulate, and insert data. It's essential for managing data in applications and websites, including e-commerce platforms.

Table Types in SQL 📝

There are two main table types in SQL: Tables and Views. Tables store data, while views provide a virtual table based on the result-set of an SQL statement.

Creating Tables ✅

Now, let's create some tables for our e-commerce database.

sql

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

CREATE TABLE Orders (
    id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (product_id) REFERENCES Products(id),
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

CREATE TABLE Customers (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone_number VARCHAR(20)
);

💡 Pro Tip: Tables help us organize data and make it easier to work with. The CREATE TABLE command is used to create a new table, and FOREIGN KEY helps establish relationships between tables.

Inserting Data 🎯

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

sql

INSERT INTO Products (id, name, price, quantity)
VALUES (1, 'Laptop', 1000.00, 5),
       (2, 'Smartphone', 500.00, 10),
       (3, 'Headphones', 100.00, 20);

INSERT INTO Customers (id, first_name, last_name, email, phone_number)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', '123-4567-8901'),
       (2, 'Jane', 'Smith', 'jane.smith@example.com', '234-5678-9012');

INSERT INTO Orders (id, product_id, customer_id, order_date)
VALUES (1, 1, 1, '2022-01-01'),
       (2, 2, 2, '2022-01-02'),
       (3, 3, 1, '2022-01-03');

💡 Pro Tip: The INSERT INTO command is used to add new rows to a table.

Querying Data 🎯

Now, let's query our data!

sql

SELECT * FROM Products;
SELECT * FROM Customers;
SELECT * FROM Orders;

💡 Pro Tip: The SELECT command retrieves data from a table. The * symbol selects all columns.

Advanced Queries 🎯

Let's try some advanced queries:

Find the average price of all products:

sql

SELECT AVG(price) FROM Products;

Find the total number of products sold by John Doe:

sql

SELECT COUNT(*) FROM Orders WHERE customer_id = 1;

Find the most expensive product:

sql

SELECT name, MAX(price) FROM Products;

Quiz 🎯

That's all for today! In the next lesson, we'll dive deeper into SQL and learn more advanced concepts. Until then, keep practicing and happy coding! 🚀