Inserting Sample Data into a Database Using SQL Queries
Published: 2024-02-24 11:08:33

In this blog post, we'll cover the process of inserting sample data into a database using SQL queries and then retrieving that data. We'll use the example of a customer database with `Customers` and `Orders` tables, demonstrating how to insert data into these tables and then retrieve it.

## Setting Up the Database Schema

Let's start by defining the database schema with two tables: `Customers` and `Orders`.

```sql
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    Email VARCHAR(255)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
```

## Inserting Sample Data

Now, let's insert some sample data into the `Customers` and `Orders` tables.

```sql
-- Inserting 50 sample customers
INSERT INTO Customers (CustomerID, CustomerName, Email)
VALUES
(1, 'John Doe', 'john.doe@example.com'),
(2, 'Jane Smith', 'jane.smith@example.com'),
(3, 'Alice Johnson', 'alice.johnson@example.com'),
(4, 'Michael Brown', 'michael.brown@example.com'),
(5, 'Emily Wilson', 'emily.wilson@example.com'),
(6, 'David Taylor', 'david.taylor@example.com'),
(7, 'Sarah Martinez', 'sarah.martinez@example.com'),
(8, 'James Anderson', 'james.anderson@example.com'),
(9, 'Jennifer Thomas', 'jennifer.thomas@example.com'),
(10, 'Daniel White', 'daniel.white@example.com'),
(11, 'Jessica Lee', 'jessica.lee@example.com'),
(12, 'Matthew Harris', 'matthew.harris@example.com'),
(13, 'Olivia Clark', 'olivia.clark@example.com'),
(14, 'Andrew King', 'andrew.king@example.com'),
(15, 'Sophia Wright', 'sophia.wright@example.com'),
(16, 'Ethan Hill', 'ethan.hill@example.com'),
(17, 'Isabella Green', 'isabella.green@example.com'),
(18, 'William Baker', 'william.baker@example.com'),
(19, 'Ava Adams', 'ava.adams@example.com'),
(20, 'Alexander Allen', 'alexander.allen@example.com'),
(21, 'Mia Parker', 'mia.parker@example.com'),
(22, 'Michael Scott', 'michael.scott@example.com'),
(23, 'Abigail Evans', 'abigail.evans@example.com'),
(24, 'Jayden Murphy', 'jayden.murphy@example.com'),
(25, 'Grace Turner', 'grace.turner@example.com'),
(26, 'Logan Hall', 'logan.hall@example.com'),
(27, 'Chloe Young', 'chloe.young@example.com'),
(28, 'Christopher Wright', 'christopher.wright@example.com'),
(29, 'Natalie Rodriguez', 'natalie.rodriguez@example.com'),
(30, 'Aiden Martinez', 'aiden.martinez@example.com'),
(31, 'Zoe Martinez', 'zoe.martinez@example.com'),
(32, 'Madison Rivera', 'madison.rivera@example.com'),
(33, 'Benjamin Reed', 'benjamin.reed@example.com'),
(34, 'Avery Mitchell', 'avery.mitchell@example.com'),
(35, 'Elijah Perez', 'elijah.perez@example.com'),
(36, 'Liam Campbell', 'liam.campbell@example.com'),
(37, 'Charlotte Flores', 'charlotte.flores@example.com'),
(38, 'Scarlett Collins', 'scarlett.collins@example.com'),
(39, 'Mason Washington', 'mason.washington@example.com'),
(40, 'Lucas Parker', 'lucas.parker@example.com'),
(41, 'Aria Cooper', 'aria.cooper@example.com'),
(42, 'Harper Morris', 'harper.morris@example.com'),
(43, 'Evelyn Rogers', 'evelyn.rogers@example.com'),
(44, 'Jackson Sanchez', 'jackson.sanchez@example.com'),
(45, 'Amelia Long', 'amelia.long@example.com'),
(46, 'Ella Peterson', 'ella.peterson@example.com'),
(47, 'Daniel Wright', 'daniel.wright@example.com'),
(48, 'Nathan Ramirez', 'nathan.ramirez@example.com'),
(49, 'Lily Hill', 'lily.hill@example.com'),
(50, 'Carter Murphy', 'carter.murphy@example.com');

-- Inserting 50 sample orders
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
(101, 1, '2024-02-20', 150.00),
(102, 1, '2024-02-22', 75.50),
(103, 2, '2024-02-21', 200.00),
(104, 3, '2024-02-23', 120.25),
(105, 4, '2024-02-24', 80.00),
(106, 5, '2024-02-25', 100.75),
(107, 6, '2024-02-26', 300.50),
(108, 7, '2024-02-27', 50.25),
(109, 8, '2024-02-28', 150.00),
(110, 9, '2024-02-29', 200.00),
(111, 10, '2024-03-01', 75.50),
(112, 11, '2024-03-02', 125.25),
(113, 12, '2024-03-03', 90.00),
(114, 13, '2024-03-04', 180.75),
(115, 14, '2024-03-05', 220.50),
(116, 15, '2024-03-06', 150.25),
(117, 16, '2024-03-07', 100.00),
(118, 17, '2024-03-08', 75.50),
(119, 18, '2024-03-09', 200.75),
(120, 19, '2024-03-10', 90.50),
(121, 20, '2024-03-11', 180.25),
(122, 21, '2024-03-12', 300.00),
(123, 22, '2024-03-13', 50.75),
(124, 23, '2024-03-14', 75.50),
(125, 24, '2024-03-15', 150.25),
(126, 25, '2024-03-16', 180.50),
(127, 26, '2024-03-17', 220.75),
(128, 27, '2024-03-18', 90.00),
(129, 28, '2024-03-19', 100.75),
(130, 29, '2024-03-20', 125.50),
(131, 30, '2024-03-21', 75.25),
(132, 31, '2024-03-22', 90.00),
(133, 32, '2024-03-23', 150.75),
(134, 33, '2024-03-24', 200.50),
(135, 34, '2024-03-25', 80.25),
(136, 35, '2024-03-26', 150.00),
(137, 36, '2024-03-27', 100.75),
(138, 37, '2024-03-28', 180.50),
(139, 38, '2024-03-29', 220.25),
(140, 39, '2024-03-30', 90.00),
(141, 40, '2024-03-31', 100.75),
(142, 41, '2024-04-01', 75.50),
(143, 42, '2024-04-02', 200.25),
(144, 43, '2024-04-03', 150.00),
(145, 44, '2024-04-04', 100.75),
(146, 45, '2024-04-05', 125.50),
(147, 46, '2024-04-06', 75.25),
(148, 47, '2024-04-07', 90.00),
(149, 48, '2024-04-08', 150.75),
(150, 49, '2024-04-09', 200.50),
(151, 50, '2024-04-10', 80.25);
```

## Retrieving Sample Data from the Database

Now that we have inserted sample data into the database, let's explore how to retrieve this data using SQL queries.

### Retrieving Customer Data

To retrieve customer data from the `Customers` table, we can use a simple `SELECT` query:

```sql
SELECT * FROM Customers;
```

This query will return all the rows from the `Customers` table, displaying the `CustomerID`, `CustomerName`, and `Email` for each customer.

### Retrieving Order Data

To retrieve order data from the `Orders` table along with the corresponding customer information, we can use a `JOIN` operation:

```sql
SELECT 
    o.OrderID,
    c.CustomerName,
    o.OrderDate,
    o.TotalAmount
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;
```

This query will return the `OrderID`, `CustomerName`, `OrderDate`, and `TotalAmount` for each order, with the customer information joined from the `Customers` table based on the `CustomerID` foreign key relationship.

## Conclusion

In this guide, we've covered the process of inserting sample data into a database using SQL queries and then retrieving that data. By following these steps, developers can populate their databases with test data and extract valuable insights using SQL queries.

Thank you for reading!