SQL Table and Data Manipulation: A Comprehensive Guide

Table and data manipulation in SQL provides ways to create new tables, transfer data between tables, and transform data using conditional logic and restructuring techniques. This article explores key statements for data manipulation, including SELECT INTO, INSERT INTO SELECT, the CASE statement, and PIVOT/UNPIVOT operations. Examples show syntax and output data structures, with database-specific syntax variations for MySQL, PostgreSQL, and SQL Server noted where relevant.

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

In SQL, effective data manipulation involves understanding various commands to create, transform, and manage data. Techniques like SELECT INTO for table creation, INSERT INTO SELECT for data transfers, and conditional expressions with the CASE statement provide powerful ways to manage data. Additionally, PIVOT and UNPIVOT (with conditional aggregation in MySQL and PostgreSQL) allow for dynamic restructuring of tables, optimizing how we analyze and present data. Mastering these commands equips you to handle complex data manipulation tasks efficiently across different SQL environments.