In today’s data-driven world, turning raw data into helpful insights is crucial—and SQL is the key tool for this. Whether you’re a data engineer creating ETL pipelines or an analyst making reports, knowing how to work with data using SQL is essential. In this post, we’ll cover 10 essential SQL queries commonly used in real-world data transformation. These examples will help you improve operations, maintain data quality, and maximize your datasets.
Table of contents
- Filtering Rows
- Sorting Data
- Aggregation
- Joining Tables
- Subqueries
- CASE Statements
- UNION and UNION ALL
- Window Functions
- Pivoting and Unpivoting
- Common Table Expressions (CTE)
- Conclusion
- References
Filtering Rows
Filters rows that meet specific criteria. It uses the WHERE condition to filter rows based on a specified condition. It shows in the result set Only rows that satisfy the condition.
SELECT * FROM TABLE1 WHERE Condition;Sorting Data
The ORDER BY clause sorts the result set on one or more columns in ascending (default) or descending order based on one or more columns.
SELECT * FROM TABLE1 ORDER BY Column1, Column2;Aggregation
Summarizing data using aggregate functions. Aggregate function COUNT and AVG are used to summarize data. The GROUP BY clause groups the result set by one or more columns.
SELECT Column, COUNT(*), AVG(Value)
FROM TABLE1
GROUP BY Column;Joining Tables
Combining data from multiple tables. The INNER JOIN combines rows from two tables based on a related column (in this case, where Table1.ID is equal to Table2.ID).
SELECT * FROM Table1
INNER JOIN Table2
ON Table1.ID = Table2.ID;Subqueries
Using a query within another query. A subquery is a query nested within another query. The outer query selects rows where a column matches values returned by the inner query.
SELECT * FROM TABELE1
WHERE Column IN (SELECT Column FROM TABLE2 WHERE Condition);CASE Statements
Conditional transformations within a query. CASE statements perform conditional transformations within a query. Depending on specified conditions, different values are returned for a new column
SELECT Column,
CASE WHEN Condition1 THEN 'Result1'
WHEN Condition2 THEN 'Result2'
ELSE 'DefaultResult'
END AS TransformedColumn FROM TABLE1;UNION and UNION ALL
Combining the results of two or more SELECT statements. UNION and UNION ALL combining the results of two or more SELECT statements. UNION removes duplicate rows, while UNION ALL including all rows.
SELECT Column FROM Table1 UNION SELECT Column FROM Table2;Window Functions
Performing calculations across a specified range of rows. Window functions perform calculations across a specified range of rows defined by the OVER clause. Examples include calculating running totals or averages.
SELECT Column, SUM(Value)
OVER (PARTITION BY PartitionColumn ORDER BY OrderColumn)
AS RunningTotal FROM TABLE1;Pivoting and Unpivoting
Transforming rows into columns (pivoting) or vice versa (unpivoting). Pivoting transforms rows into columns, while unpivoting transforms columns into rows. Below, you’ll find examples of PIVOT and UNPIVOT clauses.
-- Create a new table to store the PIVOT results
CREATE TABLE pivot_table AS
SELECT *
FROM
(SELECT ord_num, ord_amount, agent_code
FROM orders)
PIVOT
(SUM(ord_amount)
FOR agent_code IN ('A010' AS A010, 'A011' AS A011, 'A004' AS A004));
-- Create a new table to store the UNPIVOT results
CREATE TABLE unpivot_table AS
SELECT ord_num, ord_amount, agent_code
FROM
(SELECT ord_num, A010, A011, A004
FROM pivot_table)
UNPIVOT
(ord_amount FOR agent_code IN (A010, A011, A004));
-- Optional: Display the contents of the new tables
SELECT * FROM pivot_table;
SELECT * FROM unpivot_table;
Common Table Expressions (CTE)
These simplify complex queries by defining temporary result sets. CTEs provide a way to create a temporary result set. Later, you can use it in the SELECT, INSERT, UPDATE, or DELETE statement.
WITH CTE AS ( SELECT Column FROM TABLE1 WHERE Condition )
SELECT * FROM CTE;Conclusion
These queries cover the common or frequent data transformation tasks. Depending on your specific use case, you may need to adapt and combine these techniques for more complex transformations.
Srinimf 