Modeling and Querying Relational Data Efficiently with SQL
A practical guide to SQL data modeling, referential integrity, and analytical queries on AWS. Covers DDL/DML/DQL templates and best practices for Amazon RDS and Aurora.
When building cloud applications on AWS using relational database services—such as Amazon RDS or Amazon Aurora—proper schema design and efficient SQL queries are critical for ensuring low latency and optimizing cloud costs.
In this article, I present a practical, reusable guide with core ANSI SQL patterns covering table creation, data insertion, referential integrity, and analytical queries with aggregations.
In this article, I present a practical, reusable guide with core ANSI SQL patterns covering table creation, data insertion, referential integrity, and analytical queries with aggregations.
1.Schema Structure (DDL)
Below is the DDL (Data Definition Language) model for creating a simplified relational schema with two linked tables (Categories and Products).
-- Create Categories table
CREATE TABLE Categories (
category_id INT PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(100) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Categories (
category_id INT PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(100) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create Products table with Foreign Key constraint
CREATE TABLE Products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(150) NOT NULL,
category_id INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_products_categories
FOREIGN KEY (category_id)
REFERENCES Categories(category_id)
ON DELETE RESTRICT
);
CREATE TABLE Products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(150) NOT NULL,
category_id INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_products_categories
FOREIGN KEY (category_id)
REFERENCES Categories(category_id)
ON DELETE RESTRICT
);
2.Sample Data Insertion (DML)
Below are the DML (Data Manipulation Language) statements to populate the initial schema with sample data for testing:
-- Insert categories
INSERT INTO Categories (category_name, description)
VALUES
('Electronics', 'Electronic devices and hardware gadgets'),
('Accessories', 'Computer peripherals and tech accessories'),
('Books', 'Physical books and tech e-books');
INSERT INTO Categories (category_name, description)
VALUES
('Electronics', 'Electronic devices and hardware gadgets'),
('Accessories', 'Computer peripherals and tech accessories'),
('Books', 'Physical books and tech e-books');
-- Insert products
INSERT INTO Products (product_name, category_id, price, stock)
VALUES
('RGB Mechanical Keyboard', 2, 299.90, 50),
('Wireless Mouse 1600 DPI', 2, 89.90, 120),
('27" Full HD Monitor', 1, 1199.00, 15),
('Clean Code (Book)', 3, 79.90, 40);
INSERT INTO Products (product_name, category_id, price, stock)
VALUES
('RGB Mechanical Keyboard', 2, 299.90, 50),
('Wireless Mouse 1600 DPI', 2, 89.90, 120),
('27" Full HD Monitor', 1, 1199.00, 15),
('Clean Code (Book)', 3, 79.90, 40);
3. Analytical Queries and Joins (DQL)
Below are practical DQL (Data Query Language) examples using relational joins (JOIN), aggregations (GROUP BY), filtering aggregated data (HAVING), and sorting:
Query A: Retrieve all products with their associated category names
SELECT
p.product_id,
p.product_name,
c.category_name,
p.price,
p.stock
FROM Products p
INNER JOIN Categories c ON p.category_id = c.category_id
WHERE p.is_active = TRUE
ORDER BY p.price DESC;
p.product_id,
p.product_name,
c.category_name,
p.price,
p.stock
FROM Products p
INNER JOIN Categories c ON p.category_id = c.category_id
WHERE p.is_active = TRUE
ORDER BY p.price DESC;
Query B: Aggregation by category (Product count & Inventory valuation)
SELECT
c.category_name,
COUNT(p.product_id) AS total_products,
AVG(p.price) AS average_price,
SUM(p.price * p.stock) AS total_inventory_value
FROM Categories c
LEFT JOIN Products p ON c.category_id = p.category_id
GROUP BY c.category_id, c.category_name
HAVING COUNT(p.product_id) > 0
ORDER BY total_inventory_value DESC;
c.category_name,
COUNT(p.product_id) AS total_products,
AVG(p.price) AS average_price,
SUM(p.price * p.stock) AS total_inventory_value
FROM Categories c
LEFT JOIN Products p ON c.category_id = p.category_id
GROUP BY c.category_id, c.category_name
HAVING COUNT(p.product_id) > 0
ORDER BY total_inventory_value DESC;
AWS Best Practices
When running these queries on Amazon RDS (PostgreSQL/MySQL) or Amazon Aurora, keep the following best practices in mind:
- Create Indexes: Ensure indexes exist on foreign keys and columns frequently used in WHERE and JOIN conditions (e.g., category_id).
- Monitor Performance: Use Amazon RDS Performance Insights to detect slow queries and bottleneck locks.
- Connection Pooling: Use Amazon RDS Proxy to manage DB connections efficiently when connecting from serverless applications like AWS Lambda.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article