Master Advanced SQL with Practical Exercises and Solutions
Advanced SQL Features Exercises with solutions [25 exercises with solution]
1. Use Window Functions (ROW_NUMBER, RANK, DENSE_RANK)
Write a SQL query to rank employees within each department based on their salary using window functions.
2. Create a Recursive Common Table Expression (CTE)
Write a SQL query to calculate the factorial of a number using a recursive CTE.
3. Pivot Data Using PIVOT Operator
Write a SQL query to pivot sales data from rows to columns.
4. Unpivot Data Using UNPIVOT Operator
Write a SQL query to unpivot sales data from columns back into rows.
5. Query JSON Data
Write a SQL query to extract specific fields from a JSON column.
6. Use NTILE() for Data Bucketing
Write a SQL query to divide employees into quartiles based on their salary using the NTILE() window function.
7. Create a Non-Recursive Common Table Expression (CTE)
Write a SQL query to calculate the total salary for each department using a non-recursive CTE.
8. Handle XML Data with XPath Queries
Write a SQL query to extract specific fields from an XML column using XPath expressions.
9. Combine PIVOT and UNPIVOT Operators
Write a SQL query to pivot sales data and then unpivot it back to its original form.
10. Query Nested JSON Arrays
Write a SQL query to extract elements from a nested JSON array.
11. Use LAG and LEAD Functions
Write a SQL query to compare an employee's salary with the previous and next employee's salary using LAG and LEAD.
12. Create a Recursive CTE for Hierarchical Data
Write a SQL query to retrieve all employees and their managers using a recursive CTE.
13. Use FIRST_VALUE and LAST_VALUE Functions
Write a SQL query to find the highest and lowest salaries within each department using FIRST_VALUE and LAST_VALUE.
14. Parse and Modify JSON Data
Write a SQL query to update a specific field in a JSON column.
15. Transform XML Data Using XQuery
Write a SQL query to extract and transform XML data into a tabular format using XQuery.
16. Use PERCENT_RANK and CUME_DIST Functions
Write a SQL query to calculate the relative rank and cumulative distribution of employees based on their salary.
17. Use STRING_AGG to Concatenate Strings
Write a SQL query to concatenate employee names within each department into a single string.
18. Handle JSON Arrays with Nested Queries
Write a SQL query to extract all skills from a nested JSON array and count their occurrences.
19. Use GROUPING SETS for Multi-Level Aggregation
Write a SQL query to calculate total sales at multiple levels (e.g., by year, region, and overall).
20. Transform Data Using PIVOT with Dynamic Columns
Write a SQL query to dynamically pivot sales data into columns based on unique years.
21. Use JSON_QUERY to Extract Nested Objects
Write a SQL query to extract nested objects from a JSON column.
22. Use FOR XML PATH to Generate XML Output
Write a SQL query to generate XML output from relational data using FOR XML PATH.
23. Analyze Data Using PERCENTILE_CONT and PERCENTILE_DISC
Write a SQL query to calculate the median salary within each department using PERCENTILE_CONT and PERCENTILE_DISC.
24. Use CROSS APPLY with Table-Valued Functions
Write a SQL query to join a table with a table-valued function using CROSS APPLY.
25. Use MERGE Statement for Upsert Operations
Write a SQL query to perform an upsert operation (insert or update) using the MERGE statement.
More to Come !
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics