Building a customer and sales database with SQL
Introduction: In this article we explain step by step how to create a relational database that tracks customers, products, and sales using basic SQL statements. We also show queries to obtain useful information and highlight how Q2BSTUDIO can help with custom software solutions, custom applications, artificial intelligence, cybersecurity, and aws and azure cloud services.
1 Database and schema setup: First we create the database and a schema to organize the tables
SQL Example CREATE DATABASE jamii_db; CREATE SCHEMA customers;
Why use schemas: they allow grouping related tables and facilitate permission management and deployment in cloud environments such as aws and azure cloud services.
2 Creating tables with relationships: We define three tables with primary keys and foreign keys to maintain referential integrity
Table customer_info that stores customer data SQL CREATE TABLE customers.customer_info ( customer_id INT PRIMARY KEY, fullname VARCHAR(100), location VARCHAR(100) );
Table products that associates products with customers SQL CREATE TABLE customers.products ( product_id INT PRIMARY KEY, customer_id INT, product_name VARCHAR(100), price FLOAT, FOREIGN KEY (customer_id) REFERENCES customers.customer_info (customer_id) );
Table sales that records sales linked to products and customers SQL CREATE TABLE customers.sales ( sales_id INT PRIMARY KEY, product_id INT, customer_id INT, total_sales INT, FOREIGN KEY (product_id) REFERENCES customers.products (product_id), FOREIGN KEY (customer_id) REFERENCES customers.customer_info (customer_id) );
Key point: foreign keys ensure data integrity between tables and are essential for enterprise solutions that combine artificial intelligence and business intelligence.
3 Inserting sample data
Insert customers SQL INSERT INTO customers.customer_info (customer_id, fullname, location) VALUES (1, James Mwangi, Rwanda), (2, Akello Kel, Amboseli), (3, Judy J, Nanyuki), (4, Ahab Jez, Israel);
Insert products SQL INSERT INTO customers.products (product_id, customer_id, product_name, price) VALUES (1, 1, Laptop, 20000), (2, 2, Mouse, 1500), (3, 3, Charger, 4000);
Insert sales SQL INSERT INTO customers.sales (sales_id, product_id, customer_id, total_sales) VALUES (1, 1, 1, 300000), (2, 2, 2, 450000), (3, 3, 3, 100000), (4, 1, 1, 200000), (5, 2, 2, 350000), (6, 3, 3, 150000);
Note: the customer with customer_id 4 has no registered sales, useful for showing queries of customers without purchases and for retention actions through AI agents.
4 Basic and advanced queries
A Query all customers SELECT * FROM customers.customer_info;
B Get only names and locations SELECT fullname, location FROM customers.customer_info;
Query: What each customer bought SELECT c.fullname, p.product_name, s.total_sales FROM customers.customer_info c JOIN customers.sales s ON c.customer_id = s.customer_id JOIN customers.products p ON s.product_id = p.product_id;
Query: Total sales per customer SELECT ci.fullname, SUM(s.total_sales) AS sales_total FROM customers.sales s JOIN customers.customer_info ci ON s.customer_id = ci.customer_id GROUP BY ci.fullname ORDER BY sales_total DESC;
Query: Product with the highest revenue SELECT p.product_name, SUM(s.total_sales) AS sales_total FROM customers.sales s JOIN customers.products p ON s.product_id = p.product_id GROUP BY p.product_name ORDER BY sales_total DESC LIMIT 1;
Query: Customers without purchases SELECT fullname FROM customers.customer_info ci LEFT JOIN customers.sales s ON ci.customer_id = s.customer_id WHERE s.sales_id IS NULL;
5 Best practices and extensions: Use indexes on join columns to improve performance, apply validations in the data model, and automate backups in aws and azure cloud services. Combine this database with data pipelines and power bi to create dashboards that integrate business intelligence services and show insights driven by artificial intelligence and AI for companies.
About Q2BSTUDIO: Q2BSTUDIO is a custom software and application development company specialized in enterprise solutions, custom software, artificial intelligence, cybersecurity, AI agents, aws and azure cloud services, and business intelligence services. We offer consulting, custom application development, power bi integration, and secure cloud deployment. Our team designs architectures that combine artificial intelligence models with cybersecurity practices to ensure performance and protection.
Conclusion: We have shown how to build a relational database for customers and sales, how to insert data, and how to obtain insights with SQL queries. If you want to enhance your project with custom software, artificial intelligence, or cloud solutions, contact Q2BSTUDIO for a proposal that includes custom application development, AI agents, and power bi dashboards.
