SQL for POS Systems: Concepts and Examples

Point of Sale (POS) systems rely heavily on databases to manage operations such as customer orders, inventory, and sales tracking. Hence, understanding core SQL concepts can help us design and query POS databases effectively.

Let us explore some essential SQL concepts with practical examples tailored to POS systems, along with a sample schema and dataset for practice.

Sample Schema and Dataset

Before diving into SQL queries, here’s a sample schema representing a POS system and some sample data to help you practice.

//Schema

-- Customers Table
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20)
);

-- Products Table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    stock INT
);

-- Orders Table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    status VARCHAR(20),
    payment_method VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Order Items Table
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    total_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Sales Table
CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    quantity INT,
    total_price DECIMAL(10, 2),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Online Sales Table (for UNION example)
CREATE TABLE online_sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE,
    total_amount DECIMAL(10, 2)
);

-- In-Store Sales Table (for UNION example)
CREATE TABLE in_store_sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE,
    total_amount DECIMAL(10, 2)
);

//Sample Data

-- Insert Data into Customers
INSERT INTO customers (name, email, phone) VALUES
('Alice Johnson', 'alice@gmail.com', '1234567890'),
('Bob Smith', 'bob@hotmail.com', '0987654321'),
('Charlie Brown', 'charlie@yahoo.com', '1112223333');

-- Insert Data into Products
INSERT INTO products (product_name, price, stock) VALUES
('Chocolate Bar', 2.50, 100),
('Vanilla Ice Cream', 3.00, 50),
('Milk Chocolate', 5.00, 200),
('Dark Chocolate', 4.00, 150);

-- Insert Data into Orders
INSERT INTO orders (customer_id, order_date, status, payment_method) VALUES
(1, '2025-01-01', 'Completed', 'Credit Card'),
(2, '2025-01-03', 'Processing', NULL),
(3, '2025-01-05', 'Pending', 'Cash');

-- Insert Data into Order Items
INSERT INTO order_items (order_id, product_id, quantity, total_price) VALUES
(1, 1, 2, 5.00),
(1, 2, 1, 3.00),
(2, 3, 3, 15.00),
(3, 4, 2, 8.00);

-- Insert Data into Sales
INSERT INTO sales (product_id, sale_date, quantity, total_price) VALUES
(1, '2025-01-01', 10, 25.00),
(2, '2025-01-02', 5, 15.00),
(3, '2025-01-03', 8, 40.00),
(4, '2025-01-04', 6, 24.00);

-- Insert Data into Online Sales
INSERT INTO online_sales (sale_date, total_amount) VALUES
('2025-01-01', 50.00),
('2025-01-02', 75.00);

-- Insert Data into In-Store Sales
INSERT INTO in_store_sales (sale_date, total_amount) VALUES
('2025-01-03', 100.00),
('2025-01-04', 150.00);

Now, having schema and dataset ready, we will dive to practice the SQL queries below.

1. Retrieving completed orders with basic Condition
In POS systems, we often need to filter records based on specific conditions, such as fetching all completed orders.

SELECT * FROM orders WHERE status = 'Completed';

2. Searching for products by keyword with LIKE Operator
Using the LIKE operator we find items matching specific patterns, such as all products containing the word "chocolate."

SELECT * FROM products WHERE product_name LIKE '%chocolate%';

3. Filtering orders by status using IN Operator
When filtering by multiple values, the IN operator simplifies queries. For example, fetching orders that are either 'Processing' or 'Pending.'

SELECT * FROM orders WHERE status IN ('Processing', 'Pending');

4. Analyzing sales within a date range with BETWEEN Operator
The BETWEEN operator helps in analyzing sales data over a specific period.

SELECT * FROM sales WHERE sale_date BETWEEN '2025-01-01' AND '2025-01-31';

5. Validating email domains using Regular Expression
Regular expressions (REGEXP) allow complex pattern matching, such as identifying customers using Gmail addresses.

SELECT * FROM customers WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\\.com$';

6. Identifyng incomplete orders with IS NULL or NOT NULL
Checking for orders without a payment method using IS NULL

SELECT * FROM orders WHERE payment_method IS NULL;

7. Combine data across tables using Join
Joins are essential for linking tables. For instance, displaying order items alongside product details.

SELECT oi.order_id, oi.product_id, p.product_name, oi.quantity, p.price
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY oi.product_id;

8. Finding top-selling products with Aggregate Functions and HAVING
Aggregate functions like SUM helps us summarizing data, while HAVING refines the results.

SELECT product_id, SUM(total_price) AS total_sales
FROM sales
GROUP BY product_id
HAVING total_sales > 20;

9. Merging online and in-store sales using UNION and UNION ALL
Combining results from multiple tables using UNION or UNION ALL

SELECT sale_id, sale_date, total_amount FROM online_sales
UNION ALL
SELECT sale_id, sale_date, total_amount FROM in_store_sales;

In conclusion, as SQL is the backbone of any POS system, it enables us to manage data efficiently and generate insightful reporting.

With the schema, dataset, and queries above, we can gain hands-on experience with essential SQL concepts, practice these queries and adapt them to our use cases to both sharpen our skills and achieve business goal to achieve client satisfaction.