Advanced SQL Techniques

Setup Requirements

• A computer with an internet connection
• A remove lab VM will be provided with all software preinstalled.

Database Design

• Principles of effective database design
• Entity-Relationship (ER) modeling and diagramming
• Normalization (1NF, 2NF, 3NF, BCNF) and its importance in reducing redundancy
• Denormalization for performance improvements in read-heavy systems
• Managing relationships between tables (one-to-one, one-to-many, many-to-many)
• Implementing primary and foreign key constraints

SQL Refresher

• Overview of SQL syntax and structure
• Review of SELECT, INSERT, UPDATE, and DELETE operations
• Understanding SQL data types and how to choose the right type
• JOIN operations: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN
• Using WHERE, ORDER BY, and GROUP BY clauses for query customization
• Introduction to aggregate functions like COUNT, SUM, AVG, MAX, and MIN

Using TOP and OFFSET-FETCH with the SELECT statement

• Explanation of the TOP clause and its uses in limiting results
• How to apply OFFSET and FETCH for pagination in results
• Optimizing queries with large datasets using OFFSET-FETCH
• Differences between TOP and OFFSET-FETCH for query performance
• Examples of real-world scenarios where these techniques are useful
• Combining OFFSET-FETCH with ORDER BY for sorted pagination

Grouping Sets and Pivoting

• Introduction to GROUP BY and HAVING clauses for grouping data
• Working with multiple grouping sets using GROUPING SETS
• Understanding the ROLLUP and CUBE operators for advanced grouping
• Creating pivot tables using the PIVOT and UNPIVOT operators
• Transforming rows into columns with PIVOT for clearer reporting
• Using aggregation in combination with grouping sets for complex analyses

Ranking and Windowing Functions

• Introduction to window functions and their syntax
• Using ROW_NUMBER(), RANK(), and DENSE_RANK() for ranking results
• Applying window functions across partitions of data with PARTITION BY
• Using NTILE() for dividing result sets into specified groups
• Utilizing LEAD() and LAG() to access data in preceding or following rows
• Combining window functions with aggregate functions for in-depth analytics

Derived Tables, CTE, and Subqueries

• Defining and using derived tables in SELECT queries
• Implementing Common Table Expressions (CTEs) for improved query readability
• Recursion in CTEs for hierarchical data processing
• Writing and optimizing subqueries in SELECT, FROM, and WHERE clauses
• Correlated vs non-correlated subqueries and their use cases
• Best practices for improving performance with CTEs and subqueries

Views

• Understanding the purpose of views in SQL and database abstraction
• Creating views to simplify complex queries and enhance security
• Updating, deleting, and inserting data through views (with limitations)
• Performance considerations when using views with large datasets
• Using indexed views for query performance optimization
• Managing and troubleshooting view dependencies in complex schemas

Functions

• Writing scalar functions to return single values
• Creating table-valued functions for returning result sets
• Using system functions in SQL for various data manipulations (e.g., DATE, STRING functions)
• Implementing error handling within functions
• Understanding performance implications when using functions in queries
• Best practices for creating reusable and efficient functions

Stored Procedures

• Overview of stored procedures and their role in database management
• Creating stored procedures with parameters for dynamic queries
• Handling error messages and exceptions within stored procedures
• Implementing transaction control (COMMIT, ROLLBACK) inside stored procedures
• Using temporary tables within stored procedures for intermediate data storage
• Optimizing stored procedures for performance and scalability

Using DML Statements

• Writing INSERT statements for adding new data to tables
• Best practices for UPDATE and DELETE statements, including WHERE clauses
• Using MERGE for combining INSERT, UPDATE, and DELETE operations
• Transaction management in DML operations with COMMIT and ROLLBACK
• Using OUTPUT clause to track changes made by DML statements
• Performance considerations when handling large datasets with DML operations

Conclusion