SQL Server : Il corso Masterclass 2022 (27 ore)

Master SQL Server: From Beginner to Expert - Comprehensive Training

Unlock the power of SQL Server with this in-depth and comprehensive course on Udemy! This course is designed for both beginners and experienced developers looking to master database management and optimization. While the lectures are in English, Italian subtitles are available for enhanced understanding.

Chapter 1: Transact-SQL Fundamentals & Advanced Techniques

This chapter lays the groundwork for SQL Server proficiency. You'll learn:

• SQL Server & Management Studio Installation: A step-by-step guide to installing SQL Server and Management Studio on your system.
• Database & Table Creation: Master the creation of new databases and defining tables, emphasizing best practices for database design.
• Data Manipulation (DML): Gain practical skills using fundamental commands like SELECT, UPDATE, INSERT, and DELETE for efficient data management.
• Advanced Functions: Explore powerful functions like DISTINCT, LIKE, and comparison operators to refine your queries.
• GROUP BY & JOIN Concepts: Deep dive into data aggregation with GROUP BY and combining data from multiple tables using various JOIN types (including INNER JOIN, LEFT/RIGHT JOIN, and FULL OUTER JOIN). A dedicated 1.5-hour section covers these crucial concepts.
• Advanced T-SQL Functions: Discover the utility of functions like CHOOSE, IIF, CASE WHEN, SUBSTRING, OFFSET FETCH NEXT ROWS, TRIM/LTRIM/RTRIM, and COALESCE.
• UNION & UNION ALL: Understand the differences and appropriate use cases for these powerful set operators.
• Data Conversion: Learn to use CAST and CONVERT for data type transformations, along with TRY_CAST and TRY_CONVERT for error handling.
• Views, Stored Procedures, Functions, Schemas & Triggers: Create and define these essential database objects for modularity and reusability.
• Advanced Ranking Functions: Master ROW_NUMBER, OVER, PARTITION BY, and other ranking functions with examples of ROWS/RANGE UNBOUNDED PRECEDING, FIRST/LAST VALUES, LAG, and LEAD.
• Common Table Expressions (CTEs): A dedicated 1-hour module exploring CTEs and derived tables – a powerful yet often underutilized feature.

Chapter 2: Advanced SQL Server Administration

This chapter focuses on maintaining and securing your SQL Server environment. You'll cover:

• Backup & Recovery: Learn the importance of regular backups and how to perform recovery operations in case of data loss.
• Data Corruption Detection & Repair: Identify and correct data corruption issues to maintain database integrity.
• Advanced Database Configuration: Explore advanced configuration options, including autogrow settings and security options.
• Security Features: Dive into advanced features like Change Data Capture (CDC), Row Level Security (RLS), and Dynamic Data Masking (DDM) to protect your data. A 3-hour section provides comprehensive coverage of database configuration and modification, including the impact of autogrow settings on performance. Learn about TRUSTWORTHY and DB_CHAINING options.
• Isolation Levels & Constraints: Understand different isolation levels (READ COMMITTED, SERIALIZABLE, SNAPSHOT) and various constraint types (PK, FK, CHECK, UNIQUE), as well as SEQUENCES and IDENTITY properties.
• Latest SQL Server Features: Explore recent additions like Change Data Capture (CDC), Row Level Security (RLS), Dynamic Data Masking (DDM), and Temporal Data introduced in SQL Server 2016.

Chapter 3: SQL Server Performance Optimization

Optimize your SQL Server for peak performance with this crucial chapter:

• Indexing: A 4-hour deep dive into indexing strategies. Learn how to implement indexes effectively to significantly improve server performance and avoid costly hardware upgrades.
• SARGable Queries: Discover how to rewrite SQL queries to be "SARGable" (Search ARGument able) for optimal performance.

Chapter 4: Ace Your SQL Server Interview

Prepare for SQL Server job interviews with this comprehensive review:

• Theoretical Questions: Build confidence by practicing common SQL interview questions.
• Common Interview Topics: Review key concepts like primary keys, indexes, OLAP vs. OLTP, the COALESCE function, and duplicate removal queries. Ideal for Java and Python developers transitioning to SQL.

Future Updates (2024)

• SQL Server Security Configuration: Learn how to