Scribd
Upload
7 views2 pages

SQL Problem Solving Functions

The document outlines various SQL problem-solving functions and their use cases in data engineering, categorized into basic queries, string functions, aggregate functions, grouping, joins, window functions, date functions, data cleaning, set operations, and common table expressions (CTE). Each category includes specific functions and their purposes, such as retrieving data, filtering, aggregating, and manipulating strings and dates. This serves as a comprehensive guide for utilizing SQL effectively in data-related tasks.

Uploaded by

ahmed23012558
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views2 pages

SQL Problem Solving Functions

The document outlines various SQL problem-solving functions and their use cases in data engineering, categorized into basic queries, string functions, aggregate functions, grouping, joins, window functions, date functions, data cleaning, set operations, and common table expressions (CTE). Each category includes specific functions and their purposes, such as retrieving data, filtering, aggregating, and manipulating strings and dates. This serves as a comprehensive guide for utilizing SQL effectively in data-related tasks.

Uploaded by

ahmed23012558
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Problem Solving Functions & Use Cases (Data

Engineering)

1. Basic Queries
- SELECT – Retrieve data from table
- DISTINCT – Remove duplicates
- WHERE – Filter rows
- AND / OR / NOT – Logical conditions
- ORDER BY – Sort results
- TOP / LIMIT – Get top rows
- IN – Match values in list
- BETWEEN – Range filtering
- IS NULL – Check null values

2. String Functions
- LIKE – Search for text pattern
- PATINDEX – Search pattern (SQL Server)
- CHARINDEX – Find position of text
- LEN – Get string length
- LEFT / RIGHT – Extract characters
- SUBSTRING – Extract part of string
- REPLACE – Replace text
- TRIM – Remove spaces
- LOWER / UPPER – Change case

3. Aggregate Functions
- COUNT – Number of rows
- SUM – Total values
- AVG – Average
- MIN – Minimum
- MAX – Maximum

4. GROUP BY
- GROUP BY – Aggregate per group
- HAVING – Filter groups

5. JOINS
- INNER JOIN – Matching rows
- LEFT JOIN – All from left + matches
- RIGHT JOIN – All from right + matches
- FULL JOIN – All rows both tables

6. Window Functions
- ROW_NUMBER – Unique rank per row
- RANK – Rank with gaps
- DENSE_RANK – Rank without gaps
- LAG – Previous row
- LEAD – Next row
- SUM OVER – Running total

7. Date Functions
- GETDATE – Current date
- DATEADD – Add time
- DATEDIFF – Difference between dates
- YEAR / MONTH / DAY – Extract date parts

8. Data Cleaning
- COALESCE – Replace NULL
- ISNULL – Replace NULL
- CAST / CONVERT – Change data type
- NULLIF – Convert value to NULL

9. Set Operations
- UNION – Combine results
- UNION ALL – Combine including duplicates
- INTERSECT – Common rows
- EXCEPT – Difference between tables

10. CTE
- WITH (CTE) – Temporary result set
- Recursive CTE – Hierarchical data

You might also like