Duration 8 days – 56 hrs.
Overview
This comprehensive 8 days training course is designed for individuals seeking proficiency in querying Microsoft SQL Server databases using Transact-SQL (T-SQL). Participants will embark on a journey from fundamental query concepts to advanced data retrieval techniques. The course combines theoretical knowledge with hands-on exercises to ensure a practical understanding of Transact-SQL.
Objectives
- Comprehend the basics of relational databases and SQL.
- Develop proficiency in writing basic T-SQL queries.
- Explore advanced querying techniques, including subqueries and joins.
- Utilize set operators for data retrieval.
- Master the manipulation of SQL Server data types.
- Leverage T-SQL functions for efficient data retrieval and manipulation.
- Learn to insert, update, and delete data using T-SQL.
- Manage transactions to maintain data integrity.
- Create and modify tables with T-SQL.
- Implement data integrity using constraints.
- Delve into advanced querying techniques such as Common Table Expressions (CTEs).
- Optimize queries for performance using indexing strategies and execution plans.
- Gain proficiency in database programming concepts.
- Use stored procedures, functions, and triggers effectively.
- Understand error handling mechanisms in T-SQL.
- Implement exception management to handle unexpected situations.
- Explore security features in SQL Server.
- Implement secure authentication and authorization practices.
- Utilize window functions for advanced data retrieval and analysis.
- Implement ranking and aggregate window functions.
- Understand and implement dynamic SQL for flexible query construction.
- Safely execute dynamic SQL statements.
- Identify and resolve performance bottlenecks.
- Apply optimization techniques for efficient query execution.
- Learn to query and manipulate XML and JSON data in SQL Server.
- Understand the integration of XML and JSON with T-SQL.
- Implement full-text search capabilities.
- Optimize full-text queries for efficient information retrieval.
- Embrace best practices for T-SQL development.
- Apply industry standards for maintainable and efficient code.
Audience
- Database Developers: Developers who are responsible for creating and optimizing queries to interact with SQL Server databases.
- Database Administrators (DBAs): DBAs who manage SQL Server databases and need a deep understanding of T-SQL for query optimization and troubleshooting.
- SQL Server Developers: Developers who specifically work with SQL Server as their database platform and want to enhance their T-SQL querying skills.
- Data Analysts: Data analysts seeking to retrieve and analyze data from SQL Server databases using T-SQL.
- Business Intelligence (BI) Professionals: I professionals who work with SQL Server as part of their data analysis and reporting tasks.
- IT Professionals and System Administrators: IT professionals and system administrators who want to understand and support SQL Server databases in their environments.
- Technology Managers: Managers overseeing technology teams involved in database development and management.
- Software Engineers and Architects: Software engineers and architects interested in database design and interaction with SQL Server databases.
- Students and Aspiring Database Professionals: Students pursuing a career in database management or related fields.
- Business Analysts: Business analysts involved in querying databases to extract meaningful insights for decision-making.
- Anyone Working with SQL Server Databases: Individuals from various roles who interact with SQL Server databases and want to improve their T-SQL querying skills.
Pre- requisites
- Must know how to use computer
- Proficiency in spreadsheets is an advantage.
- No prior knowledge of SQL Server required.
- SSMS installed on Windows PC/Laptop
Course Content
Module 1: Introduction to SQL, SQL Server, MS SQL
- Database Normalization
- Types and functions in Querying in SSMS
Module 2: Table Manipulation
- SELECT statement
- Summarizing and Ordering
- Summarizing with conditions
- Adding Table
- Find, delete and update data
Module 3: Constraints and Indexing
- Constraints
- Create, alter and securing views
- Adding and deleting index
- Create and alter triggers
Module 3: Constraints and Indexing
- Constraints
- Create, alter and securing views
- Adding and deleting index
- Create and alter triggers
Module 4: Working with datasets and procedures.
- UNION and UNION ALL
- CASE statement
- MERGE Statement
- Implementing TRY/CATCH/THROW
Module 5: Query Functions
- Implementing aggregate queries
- Ranking functions
- Analytic functions
- Grouping and spatial aggregates
Module 6: Query Techniques and Common Table Expressions
- Sub queries clauses (WHERE, NOT, ANY, SOME, ALL, FROM)
- Corelated subqueries
- Grouping numbers
- Common table expressions (CTE) and recursive CTE
Module 7: Advanced Data Manipulation and Query Techniques in Database Systems
- Scalar Functions
- Synonyms and Dynamics
- GUIDs and sequences
- Query and manage XML data converting tables to XML
- Shredding XML data
- Manipulating JSON
- Creating, dropping temporal tables
- Querying temporal data at a point of time
Module 8: Database Optimization Techniques
- Manage transactions.
- Query plans, heaps and scans
- Clustered, non-clustered and filtered indexes
- Different join types and HASH join in action
- Nested Loops and Merge Joins
- Other query optimization
