SQL and Relational DB: Working Example

We've now seen the foundations of a Relational Databases and how SQL is used to query for data. We've discussed how a database is structured into tables consisting of different elements that interconnect and create relationships between the data, how a table is structured into columns for each field including a primary key to keep each record unique, and how each record is structured into unique rows.

There can be thousands and thousands of records/rows in a table which make up the data, and how SQL is essentially used to filter a subset of that data from various tables to query in order to be used by applications or reports etc. The SELECT statement is a very common SQL keyword that is used heavily used for the purpose of querying. There also many advanced concepts in SQL that allows the customization of a query to get a specific set of data from a database, which we will dive deeper in future articles.

Working Example

To further understand these SQL concepts and introduce new concepts, let's work through a theoretical relational database as a practical example.

Let's create a simple example for a database related to an online store. We'll design tables for customers, orders, order details, and products. The idea is that customers are ordering products through an online ordering system. The system, behind the scenes handles the necessary queries which we will discuss.

Example Database Schema:

Our database is set up to handle our data with the following business logic:

• Customer places an order, a customer can place many orders - 1:Many relationship
• An order can consists of many products. A certain product can be placed by various order - Many:Many relationship
• When we have a Many:Many relationship, there is an ambiguity we need to fix to maintain data integrity and eliminate redundancy. This is where we create an intermediary table called OrderDetails table which allows us to create a unique relationship between every Order and every Product in an order.
• We use Foreign Keys to reference unique ID in other tables in order to create that link/relationship. For example, an Order has a Customer associated with it, therefore we have a column for CustomerID in the Order table. This is the foreign key that links to the original CustomerID in the Customer table
• This process of eliminating redundancy follows the concept of Normalization in a database
• Normalization is the process of organizing data in a relational database. It includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. We'll discuss this concept in future articles.

1. Customers table

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

2. Orders table

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

3. Products table

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

4. OrderDetails Table

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    TotalPrice DECIMAL(10, 2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

We use the Data Definition Language in SQL (create table) to set up the tables for the database according to the schema we designed above. We set the column fields and the data-type along with the primary and foreign keys used to reference each table to create the proper relationships

Querying our Data

Now imagine that our database is set up and the records were populated with customer order data.

For example, let's say in an online store a product was restocked. The product table would be updated from the internal application to update or insert the data into the database. If it is a new product, then the application would use the INSERT operation. If it was an existing product in the system and there was a sale occurring, there would be an UPDATE on the price field. You may understand this concept, so on and so forth. A similar concept applies if it is a new customer registering an order, or places an order. It all comes down to the application (Online Ordering System) handling all the possible use cases/business logic

Let's say we want to list all the Customers from the Customers table

SELECT * FROM Customers;

This query uses the SELECT * statement. The * indicates we want to return all the columns FROM the Customers table and return all records. Where as:

SELECT firstName from Customers;

This query would indicate we are only returning the firstName column of all records from the Customer table.

The results of these queries would create a table of data that adheres to the queries logic and requirements. Be mindful of querying an entire table since it will return all the records, some tables can have thousands or millions rows of data depending on the data a table contains.

Customers Table and Data:

Sample Queries to test

Retrieve All Customers:

SELECT * FROM Customers;

Find Order Details for a Specific Order:

SELECT Products.ProductName, OrderDetails.Quantity, OrderDetails.TotalPrice
FROM OrderDetails
JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE OrderDetails.OrderID = 101;

A few additional concepts are seen in the above query since we are executing a JOIN between two tables. (More on JOINS later). Sometimes we reference a column with its table name prepended in the query since joining two tables may have the same column name, and creates an ambiguity between two tables. So we write our query specific to the table we are referencing: tableName.column -> OrderDetails.Quantity This lets the database know exactly which column from the specific table we are referencing otherwise we might receive an error.

Calculate Total Order Value:

SELECT SUM(TotalPrice) AS TotalOrderValue
FROM OrderDetails
WHERE OrderID = 101;

SQL also gives us the ability to run functions and aggregations on our data, as we see the SUM() function and set an alias for the column name for our result using the AS keyword. These functions are available in various DBMS systems in which the syntax can vary. This is very useful for more complex queries or running analyses on our data.

List Products and Their Prices:

SELECT ProductName, Price FROM Products;

Update Customer Email:

UPDATE Customers SET Email = 'john.doe@example.com' WHERE CustomerID = 1;

Delete an order:

DELETE FROM Orders WHERE OrderID = 102;

These queries and would result in a set data to be retrieved from all the data in the database, along with a few Update and Delete operations.

We can also see a JOIN operation in the query where we connect two or more different tables to find the relevant data between those two tables that are joined on the foreign keys. Also we can see the WHERE clause in the SQL which allows a query to filter on a certain condition based on the data. For example, we may want information on a certain unique record or a certain customer, so we query based on customer_id = 1 to only return that specific customer.

WHERE clause are not specific to only unique records, they can be utilized to find similar record, for example querying for a products where the price is $100

SELECT * FROM Products WHERE price = 100;

This would list all the different products in that table meeting that condition where the exact price is $100.

Essentially we are giving instructions to find the relevant data that an application may use whether it is in an online ordering system that can process this data or much more. The possibilities are endless and it all comes down to a business's requirements.