SQL Tricks to Boost Your Skills from Beginner to Pro

 If you're looking to sharpen your SQL skills and move beyond the basics, you're in the right place. This post covers many practical tips and techniques that will help you write clearer, faster, and more efficient SQL queries. You'll learn how to use window functions effectively, simplify complex operations with pivoting, handle NULLs properly, rank and sort data like a pro, and much more.

Whether you’re preparing data for reports or optimizing your queries for better performance, these insights will help you level up your SQL game.

Understanding Aggregations and Window Functions in SQL

When calculating totals, such as summing salaries by department, SQL offers two common approaches: aggregate functions with GROUP BY and window functions with OVER (PARTITION BY).

• Aggregate function + GROUP BY: This collapses multiple rows into a single row per group. For example, SUM(salary) GROUP BY department_id will give you one total salary per department.
• Window function + PARTITION BY: Here, you calculate the sum within each partition (e.g., department), but the original rows remain visible. Using SUM(salary) OVER (PARTITION BY department_id) adds a rolling total or a running sum as a column, without losing individual employee details.

The key difference is that aggregates summarize and limit output rows to one per group, but window functions keep rows intact, adding extra insight for each row.

Example:

dept_id	employee	salary	SUM(salary) GROUP BY dept_id	SUM(salary) OVER (PARTITION BY dept_id)
1	John	50000	150000	50000
1	Jane	60000	150000	60000
1	Mike	40000	150000	40000
2	Lisa	70000	125000	70000
2	Mark	55000	125000	55000

Knowing when to use which depends on your goal:

• Use aggregate + GROUP BY when you want summarized results.
• Use window functions when you want calculations alongside original row details, like running totals or rankings.

Window functions often perform well and provide extra analysis flexibility.

Mastering the PIVOT Function in SQL

Sometimes you want to transform rows into columns for better readability, like turning departmental sales data into a table where each department is a column. This is where the PIVOT function shines.

How to Write a PIVOT Query

1. Start with your source table or a table expression.
2. Use the PIVOT keyword.
3. Inside parentheses, define:
   • The aggregation function, like SUM(salary).
   • The column you want to use for pivoting (e.g., department_name).
   • The pivoted values in square brackets, like [HR], [Finance], [IT].
4. Give the pivot table an alias after the pivot clause.

Example Syntax:

SELECT *
FROM
    (SELECT department_name, salary FROM employees) AS SourceTable
PIVOT
(
    SUM(salary)
    FOR department_name IN ([HR], [Finance], [IT])
) AS PivotTable;

This query converts department rows to columns with summed salaries for each.

Why Use PIVOT Instead of UNION ALL?

Without PIVOT, you might write long UNION ALL queries to get the same effect — listing each department’s total one by one. That can be tedious and slow.

Also, for generating quick temporary data, the VALUES function provides a neater alternative to lengthy UNION ALL statements.

Use PIVOT for:

• Summarizing data in cross-tab reports.
• Reshaping data from multiple rows into columns for easier analysis.
• Cleaner, maintainable SQL code.

Efficient Data Manipulation: Updating and Swapping Column Values

Swapping values between two columns in a table is a common task but can be tricky if you’re not sure how to write the update.

Swapping Two Columns in One Step

Suppose you want to swap column values ID1 and ID2 for all rows. You can do this with a single update statement:

UPDATE your_table
SET ID1 = ID2,
    ID2 = ID1;

This swaps the values in place for every record. Run the update again, and the values swap back.

If your database doesn’t allow this syntax, try alternate methods like using a temporary variable or columns.

Using VALUES Instead of UNION ALL for Temporary Data

Instead of writing tedious multiple UNION ALL queries to generate test data, the VALUES clause lets you quickly specify rows:

SELECT *
FROM (VALUES
  (1, 'A'),
  (2, 'B'),
  (3, 'C')
) AS TempTable(ID, Name);

This makes your code cleaner and easier to modify.

String Operations and Handling Nulls in SQL

Splitting Strings

You can use STRING_SPLIT(string, delimiter) to split a delimited string into rows, but be careful with syntax. The correct way to select values is:

SELECT value
FROM STRING_SPLIT('A,B,C', ',');

Common errors come from incorrect syntax or trying to call it wrongly.

Concatenating Strings with NULLs

String concatenation often produces NULL if any part is NULL. Instead, use the CONCAT function which ignores NULLs:

SELECT CONCAT(first_name, last_name) AS full_name FROM employees;

If you want to insert spaces between names and still handle NULLs, use CONCAT_WS (Concatenate With Separator):

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM employees;

Trimming Unwanted Characters

Use the TRIM function to clean up strings:

• TRIM(LEADING 'x' FROM string) removes characters from the start.
• TRIM(TRAILING 'x' FROM string) removes from the end.
• TRIM(BOTH 'x' FROM string) removes from both ends.

This helps clean inputs or remove unwanted whitespace.

Measuring Length Considering Trailing Spaces

LEN often ignores trailing spaces. To find the true length including trailing spaces, append a unique character and subtract one:

SELECT LEN(col + '|') - 1 FROM your_table;

This trick highlights strings with trailing spaces.

Ranking and Row Number Functions for Ordering Data

SQL provides several ways to rank or number rows based on ordering.

Differences Between RANK, DENSE_RANK, and ROW_NUMBER

• RANK(): Assigns the same rank to tied values but skips ranks afterward. For example, with two employees tied for rank 2, the next gets rank 4.
• DENSE_RANK(): Like RANK but does not skip ranks. The next rank after ties continues immediately.
• ROW_NUMBER(): Assigns a unique sequential number to each row regardless of ties.

Example:

salary	RANK()	DENSE_RANK()	ROW_NUMBER()
100000	1	1	1
90000	2	2	2
90000	2	2	3
80000	4	3	4

Sorting with NULL Values

By default, SQL Server sorts NULLs first in ascending order. If you want NULLs last, use conditional sorting:

ORDER BY CASE WHEN department_name IS NULL THEN 1 ELSE 0 END, department_name;

This moves NULL values to the bottom.

Simplifying Complex Conditional Logic with IIF and CASE

Using IIF for Short Conditional Logic

IIF(condition, true_value, false_value) offers a compact alternative to CASE for simple conditions.

Example: Mark employees as managers if manager_id >= 5:

SELECT employee_id,
       IIF(manager_id >= 5, 1, 0) AS is_manager
FROM employees;

CASE Executes Conditions in Order

CASE checks conditions sequentially and returns once a true condition is found.

If multiple conditions are true, only the first matching one applies.

Example:

SELECT CASE
         WHEN salary > 90000 THEN 'High'
         WHEN salary > 70000 THEN 'Medium'
         ELSE 'Low'
       END AS Salary_Level
FROM employees;

Generating and Managing Data Efficiently

Creating Tables Like Existing Ones

Use SELECT INTO to clone a table’s structure and data:

SELECT *
INTO new_table
FROM original_table;

To create an empty copy, add a WHERE 1 = 2 clause so no rows get selected.

Generating Number and Date Series

GENERATE_SERIES can produce a range of numbers or dates. For dates, generate numbers and add them as days:

SELECT DATEADD(day, number, '2024-01-01') AS DateValue
FROM GENERATE_SERIES(0, 365);

This handles leap years like 2024 with 366 days.

Inserting Data with Single Quotes

To insert text that contains single quotes, escape them by doubling:

INSERT INTO person(lastname) VALUES ('O''Hara');

Formatting Numbers with Leading Zeros

Use the FORMAT function to pad integers:

SELECT FORMAT(7, '000') -- Output: 007
SELECT FORMAT(77, '000') -- Output: 077

Rounding Numbers

• ROUND(value, decimals): Standard rounding.
• CEILING(value): Rounds up.
• FLOOR(value): Rounds down.

Performance and Database Management Tips

Using OUTPUT Clause to Track Changes

When inserting, updating, or deleting rows, OUTPUT returns affected rows:

INSERT INTO table1 (col1) OUTPUT inserted.* VALUES ('value');

UPDATE table1
SET col1 = 'newvalue'
OUTPUT inserted.col1, deleted.col1
WHERE col1 = 'oldvalue';

This helps audit changes as they happen.

Killing Long-Running Queries

To find and terminate slow queries:

1. Use sp_who2 to list active sessions.
2. Identify the SPID causing issues.
3. Run KILL <SPID> to stop it.

Renaming Tables

Use sp_rename to rename tables:

EXEC sp_rename 'old_table_name', 'new_table_name';

After renaming, queries using the old name will error.

Generating Scripts with Data in SSMS

Right-click on a database, choose Tasks > Generate Scripts. In Advanced options, select Schema and Data to export both table structure and rows.

Adding Check Constraints

To prevent invalid data like end_date < start_date:

ALTER TABLE your_table
ADD CONSTRAINT chk_dates CHECK (end_date >= start_date);

Existing bad rows must be removed before adding the constraint.

Advanced Data Filtering and Searching Techniques

Using LIKE with Wildcards

• % matches any number of characters.
• _ matches exactly one character.

To find names where the second last character is 'A':

WHERE first_name LIKE '%A_'

Character Ranges in LIKE

Search for names starting with 'N' or 'R' quickly:

WHERE first_name LIKE '[NR]%'

Use [^] to find strings with characters outside specified ranges.

Searching Across Multiple Columns

Instead of multiple OR conditions, use:

WHERE 'Main Street' IN (address1, address2, address3);

This returns customers whose address contains 'Main Street' in any address column.

Useful SQL Functions and Tricks to Know

Using LEAD and LAG Functions

Retrieve next or previous row values with:

LEAD(column, offset, default) OVER (ORDER BY col)
LAG(column, offset, default) OVER (ORDER BY col)

offset skips multiple rows, default replaces nulls when no prior or next row exists.

Handling Exponential Numbers

Convert exponential strings to floats before assigning to numeric columns:

CAST(your_exponential_value AS FLOAT)

Simplifying Date Formatting

Use CONVERT to get year and month without complex string operations:

CONVERT(varchar, your_date, 112) -- Returns YYYYMMDD format

Masking Sensitive Data

Use functions within ALTER TABLE to mask columns like email for privacy, restricting sensitive info exposure.

Retrieving Table Metadata

Use the system stored procedure:

EXEC sp_help 'table_name';

This returns column info, keys, constraints, and other metadata.

To find tables with a specific column name, query:

SELECT table_schema, table_name
FROM information_schema.columns
WHERE column_name LIKE '%product%';

Handling NULLs and Data Integrity in SQL Queries

NULLs in Filters and Counts

Remember WHERE department_name <> 'HR' excludes NULL rows because NULL comparisons return unknown (not true).

As a result, counts may be unexpected.

Proper NULL Handling Strategies

Use:

WHERE department_name <> 'HR' OR department_name IS NULL;

Or use COALESCE to replace NULLs for comparisons.

Ordering with NULLs

SQL Server puts NULLs first by default. Push them to the end with:

ORDER BY 
  CASE WHEN department_name IS NULL THEN 1 ELSE 0 END,
  department_name;

COUNT and NULLs

COUNT(column) skips NULLs, but COUNT(*) counts all rows.

---

Wrapping It Up

These SQL tricks will make your queries cleaner, more efficient, and easier to maintain. From understanding sums to mastering pivot tables, handling NULLs, and writing concise conditional logic, these tips cover a broad range of everyday SQL tasks.

---