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 SQL statements and clauses, along with practical examples. These topics have provided a strong foundation for understanding how MySQL works. In this article, we will take the next step by creating a sample database and demonstrating how to execute various SQL queries using real-world examples.
If you have not checked my previous article about MySQL Database examples then you can check it here.
Table of contents:
1 – Database Creation
In this tutorial, we’ll create a sample database for an eCommerce store named V-Store. This database will serve as the foundation for running various MySQL queries and working with sample data. It will help you understand how to structure, query, and manage data effectively.
Run the following query to create the VStore database:
CREATE DATABASE VStore;2 – Create Tables
After creating the database, the next step is to create tables within it. Tables are used to store and organize data in a database. You can create as many tables as needed based on the requirements of your application.
For example, the following query creates an admin table:
CREATE TABLE admin (
id INT AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(50),
lastname VARCHAR(50),
email VARCHAR(100),
password VARCHAR(255)
);Similarly, we can create an orderitems table using the following query:
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
);For this tutorial, we will create a total of 10 tables for the VStore database. To keep the article concise, we have shown only a couple of table creation queries here. You can find the complete list of tables and their SQL queries in the GitHub repository linked at the end of this article.
3 – Insert Data into the Table
Once the tables have been created, the next step is to insert data into them. MySQL provides the INSERT INTO statement for adding new records to a table.
For example, the following query inserts 10 records into the admin 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');Since the id column is defined as AUTO_INCREMENT, MySQL will automatically generate a unique ID for each record. Therefore, we don’t need to include the id column in the INSERT statement.
4 – Query Examples
Now that we have created the database, tables, and sample data, we can run different types of SQL queries to retrieve, filter, group, and analyze the data.
4.1 – List All Distinct States from Usersmeta
The DISTINCT keyword can be used to retrieve unique values from a column.
SELECT DISTINCT state FROM usersmeta;4.2 – Count How Many Users Are in Each Country
The GROUP BY clause allows us to group users by country and count the number of users 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
We can use the YEAR() function to retrieve users who registered during a specific year.
SELECT * FROM users
WHERE YEAR(timestamp) = 2025;4.4 – Get the Total Orders Placed by Each User
We can use COUNT() and GROUP BY to count the number of 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
The HAVING clause can be used to filter grouped results. The following query returns users who have placed more than five orders.
SELECT uid, COUNT(*) AS total_orders
FROM orders
GROUP BY uid
HAVING total_orders > 5;4.6 – Get Total Revenue for Each Payment Mode
The SUM() function can be used to calculate the total revenue for each payment mode.
SELECT paymentmode, SUM(totalprice) AS total_revenue
FROM orders
GROUP BY paymentmode
ORDER BY total_revenue DESC;4.7 – List the Number of Reviews for Each Product
The following query counts the number of reviews associated with each product.
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
Using GROUP BY and HAVING, we can find products that have received more than 10 reviews.
SELECT pid, COUNT(*) AS review_count
FROM reviews
GROUP BY pid
HAVING review_count > 10;4.9 – Get the 5 Most Recently Added Products
The following query sorts products by their ID in descending order and returns the latest five records.
SELECT * FROM product
ORDER BY id DESC
LIMIT 5;4.10 – Get the Number of Items in Each Order
This query counts the number of items associated with each order.
SELECT orderid, COUNT(*) AS total_items
FROM orderitems
GROUP BY orderid;4.11 – Get the Average Quantity per Order Item
The AVG() function calculates the average value of the pquantity column.
SELECT AVG(pquantity) AS avg_quantity
FROM orderitems;4.12 – Find Orders Placed in the Last 7 Days
We can use NOW() and the INTERVAL keyword to retrieve orders placed within the last seven days.
SELECT * FROM orders
WHERE timestamp >= NOW() - INTERVAL 7 DAY
ORDER BY timestamp DESC;5 – JOIN Query Examples
JOIN queries are used to retrieve related data from multiple tables. In the following examples, we will use different types of JOINs to combine data from the VStore database.
5.1 – List All Products with Their Category Name
The following query joins the product and category tables to display each product along with its category name and price.
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
This query joins the orders and users tables to retrieve order details along with the email address of the user who placed each order.
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 the Latest Order Tracking Record for Each Order
The following query retrieves the most recent tracking record for each order by using MAX() with a subquery.
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
This query uses a LEFT JOIN to retrieve users along with their profile information stored in the usersmeta table. Users are included even if they do not have a corresponding profile record.
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
The following query retrieves wishlist items along with the corresponding product name and price 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
This query joins the orderitems and product tables and uses aggregate functions to calculate the total quantity sold and 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;If you enjoyed this article, we’d love to hear your thoughts. Feel free to share your feedback, questions, or suggestions in the comments section below.
If you have any questions or would like to get in touch with us, please don’t hesitate to reach out us.
1 thought on “MySQL Query Tutorial”