Mastering SQL Tables and Data Manipulation: A Practical Step-by-Step Guide

SQL Table and Data Manipulation refers to the techniques used to create, transfer, modify, and transform data within relational databases. These operations help database professionals organize information efficiently, automate reporting, and support business decision-making. In this guide, you’ll learn SQL Table and Data Manipulation concepts such as SELECT INTO, INSERT INTO SELECT, CASE statements, and PIVOT/UNPIVOT operations with practical examples.

Why SQL Table and Data Manipulation Is Important

Data manipulation is one of the most important skills in SQL because organizations continuously create, update, and transform data.

• Faster reporting
• Better data organization
• Easier data migration
• Improved business analytics
• Efficient database management

Common SQL Table and Data Manipulation Operations

SQL provides several techniques for manipulating tables and data:

• Creating tables from query results
• Copying data between tables
• Conditional data transformation
• Converting rows into columns
• Converting columns into rows
• Aggregating and restructuring data

1. SELECT INTO

The SELECT INTO statement creates a new table from a SELECT query and copies data into it from an existing table. It’s commonly used for backup or temporary data storage.

Syntax (SELECT INTO):

SELECT column1, column2, ...
INTO new_table
FROM existing_table
WHERE condition;

Employees Table:

Query:

SELECT EmployeeID, Name, Salary
INTO HighSalaryEmployees
FROM Employees
WHERE Salary > 5500;

Output (HighSalaryEmployees Table):

Note: SELECT INTO is fully supported in SQL Server, but in MySQL, you may need to use CREATE TABLE … AS SELECT instead.

2. INSERT INTO SELECT

The INSERT INTO SELECT statement copies data from one table to another existing table. This is particularly useful when appending data from one table to another.

Syntax (INSERT INTO SELECT):

INSERT INTO existing_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

Example:

HighSalaryEmployees Table (Already Existing):

Query:

INSERT INTO HighSalaryEmployees (EmployeeID, Name, Salary)
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > 5500;

Output (HighSalaryEmployees Table):

Note: SQL syntax for INSERT INTO SELECT is consistent across SQL Server, MySQL, and PostgreSQL.

3. CASE Statement

The CASE statement allows conditional logic in SQL queries, useful for conditional aggregation and data transformation within queries.

Syntax (CASE Statement):

SELECT column1,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ...
           ELSE result
       END AS new_column
FROM table;

Example:

Employees Table:

Query:

SELECT Name,
       Salary,
       CASE
           WHEN Salary > 6000 THEN 'High'
           WHEN Salary BETWEEN 5000 AND 6000 THEN 'Medium'
           ELSE 'Low'
       END AS SalaryLevel
FROM Employees;

Output:

Note: The CASE statement syntax is standard across SQL Server, MySQL, and PostgreSQL.

4. PIVOT and UNPIVOT

• PIVOT transforms rows into columns, commonly used for summarizing data by turning unique values from one column into multiple columns.
• UNPIVOT transforms columns into rows, reversing a pivoted dataset.

Syntax (PIVOT): Only fully supported in SQL Server natively.

SELECT column1, column2, ...
FROM (SELECT column1, column2 FROM table) AS source
PIVOT (
    AGGREGATE_FUNCTION(column) FOR column_name IN ([value1], [value2], ...)
) AS pivot_table;

Example (PIVOT in SQL Server):

Sales Table:

Query:

SELECT EmployeeID, [Jan], [Feb]
FROM (SELECT EmployeeID, Month, Sales FROM Sales) AS SourceTable
PIVOT (SUM(Sales) FOR Month IN ([Jan], [Feb])) AS PivotTable;

Output (Pivoted Sales Table):

MySQL and PostgreSQL Alternative:

Use GROUP BY with conditional aggregation for similar functionality.

Syntax (UNPIVOT): SQL Server syntax example:

SELECT column1, unpivoted_column, unpivoted_value
FROM table
UNPIVOT (unpivoted_value FOR unpivoted_column IN (column2, column3, ...)) AS unpivot_table;

Example (UNPIVOT in SQL Server):

Pivoted Sales Table:

Query:

SELECT EmployeeID, Month, Sales
FROM PivotedSalesTable
UNPIVOT (Sales FOR Month IN (Jan, Feb)) AS UnpivotTable;

Output (Unpivoted Sales Table):

Note: In MySQL and PostgreSQL, similar results can be achieved using JOIN or UNION ALL.

Conclusion

SQL Table and Data Manipulation plays a vital role in modern database management. Techniques such as SELECT INTO, INSERT INTO SELECT, CASE statements, and PIVOT/UNPIVOT operations help developers organize, transfer, and transform data efficiently. By mastering these SQL data manipulation techniques, you can improve reporting, simplify data migration, and build scalable database solutions.

Frequently Asked Questions