Creating and Inserting Data into SQL Tables: A Step-by-Step Tutorial

In this tutorial, we will walk through creating and populating SQL tables with sample data for a hypothetical database that tracks employee demographics and salaries. The two images provided showcase SQL queries and results, offering a clear visual guide. Let’s dive into each step!

---

Step 1: Creating the “EmployeeDemographics” Table

The first table, EmployeeDemographics, is designed to store information about employees’ personal details, such as their name, age, and gender.

SQL Query to Insert Data

Here’s the SQL code snippet used to populate the EmployeeDemographics table:

INSERT INTO EmployeeDemographics VALUES
(1001, 'Muhammet', 'sahin', 21, 'Male'),
-- Additional rows are commented out in the provided script
-- (1002, 'Dwight', 'Schrute', 29, 'Male'),
-- (1003, 'Angela', 'Martin', 31, 'Female'),
-- (1004, 'Toby', 'Flenderson', 32, 'Male'),
-- (1005, 'Michael', 'Scott', 35, 'Male'),
-- (1006, 'Meredith', 'Palmer', 32, 'Female'),
-- (1007, 'Stanley', 'Hudson', 38, 'Male'),
-- (1008, 'Mahmut', 'Kebap', 31, 'Male');

After executing this query, the data for the employees is inserted into the table. The results section shows that there are duplicate entries for the first row, which may require cleaning to ensure data integrity.

Step 2: Creating the “EmployeeSalary” Table

Next, the EmployeeSalary table is used to store job titles and corresponding salaries for employees.

SQL Query to Insert Data

The following SQL query adds data into the EmployeeSalary table:

INSERT INTO EmployeeSalary VALUES
(1001, 'Salesman', 45000),
(1002, 'Receptionist', 36000),
(1003, 'Salesman', 63000),
(1004, 'Accountant', 47000),
(1005, 'HR', 50000),
(1006, 'Regional Manager', 65000),
(1007, 'Supplier Relations', 41000),
(1008, 'Salesman', 48000),
(1009, 'Accountant', 42000);

This query ensures that each employee has an associated salary and job title. As shown in the second image, the data structure is clear, with fields for EmployeeID, JobTitle, and Salary.

Key Observations

1. Duplicate Entries: In the EmployeeDemographics table, there are multiple rows for EmployeeID 1001. This highlights the importance of maintaining unique primary keys.
2. Data Validation: When populating tables, ensure that there is no duplication or mismatch in the relationships between tables. For instance, the EmployeeID in both tables should align correctly.

---

Tips for Managing SQL Tables

• Primary Keys: Always define primary keys to avoid duplicate entries.
• Referential Integrity: Use foreign keys to link related tables, ensuring data consistency.
• Commenting Code: As seen in the examples, commenting out rows can help manage data insertion during debugging or testing.

This tutorial offers a foundational look at creating and populating tables in SQL. With these concepts, you can manage and query relational databases effectively.

Original source material: https://www.youtube.com/watch?v=RSlqWnP-Dy8