Inserting Sample Data into a Database Using SQL Queries

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.

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.

-- 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:

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:

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!

Blog