MySQL Query Tutorial

In the previous three articles, we explored the fundamentals of MySQL, including its introduction, key use cases and applications, commonly used query tools, data types, and essential query statements and clauses—complete with practical examples. These topics have laid a strong foundation for understanding how MySQL works. In this article, we will take a step further by creating a sample database and demonstrating how to run various SQL queries against it using real-world examples.

Table of contents:

1. Database creation
2. Create table and insert dummy data
3. Insert data in the table
4. Query examples
5. JOIN Query examples

1 – Database creation

In this tutorial, we’ll create a database for an eCommerce store named V-Store. This database will serve as the foundation for running various MySQL queries and retrieving data for analysis, helping you understand how to structure, query, and manage data effectively. Run the below query to create database named V-Store.

CREATE DATABASE VStore;

2 – Create tables

After database creation, we can create table in this database. Run the below query to create tables in this database.

CREATE TABLE admin (
    id INT AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(50),
    lastname VARCHAR(50),
    email VARCHAR(100),
    password VARCHAR(255)
);

You can create as many tables as you want.

CREATE TABLE orderitems (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pid INT NOT NULL,
    orderid INT NOT NULL,
    productprice DECIMAL(10, 2) NOT NULL,
    pquantity INT NOT NULL
);

I have create total 10 tables in this table. I will share the GitHub link at the end of this article for the full tables list.

3 – Insert data in the table

We can use INSERT MySQL statement to insert data in the table. Run the below query to insert data in the table.

INSERT INTO admin (firstname, lastname, email, password) VALUES
('John', 'Doe', 'john.doe@example.com', 'johnpass123'),
('Jane', 'Smith', 'jane@smith.com', 'smithpass012'),
('Alice', 'Johnson', 'alice.johnson@example.com', 'alicepassword12'),
('Bob', 'Williams', 'bob@williams.com', 'bobpass123'),
('Charlie', 'Brown', 'charlie@example.com', 'brownpass23'),
('Diana', 'Miller', 'diana@miller.com', 'miller123'),
('Ethan', 'Davis', 'ethan.davis@example.com', 'ethanpass'),
('Fiona', 'Garcia', 'fiona@garcia.com', 'fiona123'),
('George', 'Martinez', 'martinez@example.com', 'georgepass123'),
('Hannah', 'Taylor', 'hannah@taylor.com', 'hannah123');

4 – Query example

Now we can run different types of queries to retrieve data.

4.1 List all distinct states from usermeta

SELECT DISTINCT state FROM usersmeta;

4.2 Count how many users are in each Country

SELECT country, COUNT(*) AS user_count
FROM usersmeta
GROUP BY country
ORDER BY user_count DESC;

4.3 List users registered in 2025 only

SELECT * FROM users
WHERE YEAR(timestamp) = 2025;

4.4 Get total orders placed by each user

SELECT uid, COUNT(*) AS total_orders
FROM orders
GROUP BY id,
ORDER BY total_orders DESC;

4.5 Get users who placed more than 5 orders

SELECT uid, COUNT(*) AS total_orders
FROM orders
GROUP BY uid
HAVING total_orders > 5;

4.6 List total revenue from each payment mode

SELECT paymentmode, SUM(totalprice) AS total_revenue
FROM orders
GROUP BY paymentmode
ORDER BY total_revenue DESC;

4.7 List number of reviews per product ID

SELECT pid, COUNT(*) AS total_reviews
FROM reviews
GROUP BY pid
ORDER BY total_reviews DESC;

4.8 Find products with more than 10 reviews

SELECT pid, COUNT(*) AS review_count
FROM reviews
GROUP BY pid
HAVING review_count > 10;

4.9 Top 5 most recently added products

SELECT * FROM product
ORDER BY id DESC
LIMIT 5;

4.10 List number of items per order

SELECT orderid, COUNT(*) AS total_items
FROM orderitems
GROUP BY orderid;

4.11 Get average quantity per order item

SELECT AVG(pquantity) AS avg_quantity
FROM orderitems;

4.12 Find orders placed in the last 7 days

SELECT * FROM orders
WHERE timestamp >= NOW() - INTERVAL 7 DAY
ORDER BY timestamp DESC;

5 – JOIN Query examples

5.1 – List all products with their category name

SELECT p.id, p.name AS product_name, c.name AS category_name, p.price
FROM product p
JOIN category c ON p.catid = c.id;

5.2 – Get all orders with user email and total price

SELECT o.id AS order_id, u.email, o.totalprice, o.orderstatus, o.timestamp
FROM orders o
JOIN users u ON o.uid = u.id;

5.3 – Get latest order tracking for each order

SELECT ot.*
FROM ordertracking ot
JOIN (
SELECT orderid, MAX(timestamp) AS latest
FROM ordertracking
GROUP BY orderid
) latest_tracking ON ot.orderid = latest_tracking.orderid AND ot.timestamp = latest_tracking.latest;

5.4 – List all users with their full profile (from usersmeta)

SELECT u.id, u.email, um.firstname, um.lastname, um.city, um.country, um.mobile
FROM users u
LEFT JOIN usersmeta um ON u.id = um.userid;

5.5 – Get wishlist items with product details for a specific user

SELECT w.uid, p.name AS product_name, p.price, w.timestamp
FROM wishlist w
JOIN product p ON w.pid = p.id
WHERE w.uid = 105;

5.6 – Get total sales for each product

SELECT p.name, SUM(oi.pquantity) AS total_quantity_sold, SUM(oi.pquantity * oi.productprice) AS total_sales
FROM orderitems oi
JOIN product p ON oi.pid = p.id
GROUP BY p.id;