Introduction to SQL Databases course
This course is aimed at people looking to move into a database professional role or whose job role is expanding to encompass database elements. The course describes fundamental database concepts including database types, database languages, and database designs.
COURSE OUTLINE
1 – INTRODUCTION TO DATABASES
• Introduction to relational databases
• Other types of database
• Data analysis
• Database languages
• Lab : Querying SQ Server
2 – DATA MODELLING
• Data modelling
• ANSI/SPARC database model
• Entity relationship modelling
• Lab : Entity relationship modeling
3 – NORMALIZATION
• Why normalize data?
• Normalization terms
• Levels of normalization
• De-normalization
• Lab : Normalizing raw data
4 – RELATIONSHIPS
• Schema mapping
• Referential integrity
• Lab : Designing relationships
5 – PERFORMANCE
• Indexing
• Query performance
• Concurrency
• Lab : Query performance
6 – DATABASE OBJECTS
• Tables
• Views
• Stored Procedures, Triggers and Functions
• Lab : Using SQL Server
Developing SQL Databases course
This course allows the trainee to build and implements databases across organizations and who ensure high levels of data availability.
COURSE OUTLINE
1 – Introduction To Database Development
Navigate the SQL Server Platform
Perform SQL Server Database Development Tasks
2 – Designing & Implementing Tables
Design, Create and Alter Tables
Understand the Different Data Types
Create and Manage Schemas
Lab: Designing and implementing tables
3 – Advanced Table Designs
Partition and Compress Data
Create Temporal Tables
Lab: Using advanced table designs
4 – Ensuring Data Integrity Through Constraints
Enforce Data Integrity
Add and Test Constraints
Implement Data Domain, Entity and Referential Integrity
Lab: Using Data Integrity Through Constraints
5 – Introduction To Indexes
Understand Core Indexing Concepts
Learn about Data and Index Types – Including Heaps, Clustered, Nonclustered, Single Column and Composite Indexes
Develop Heaps and Clustered and Covered Indexes
Lab: Implementing Indexes
6 – Designing Optimized Index Strategies
Learn About Index Strategies, Managing Indexes and Execution Plans
Work with the Database Engine Tuning Advisor
Use the Query Store to Identify and Fix Queries
7 – Columnstore Indexes
Interpret, Create and Work with Columnstore Indexes
Build a Memory Optimized Columnstore Table
Lab: Using Columnstore Indexes
8 – Designing And Implementing Views
Create and Manage Views
Understand Performance Considerations for Views
Set up Standard and Updateable Views
Lab: Designing and Implementing Views
9 – Designing And Implementing Stored Procedures
Create and Work with Stored Procedures
Build and Implement Parameterized Stored Procedures
Control Execution Context
Lab: Designing and Implementing Stored Procedures
10 – Designing And Implementing User-Defined Functions
Understand the Types of Functions, Alternatives and Key Considerations for Implementation
Design and Implement Scalar and Table-Valued Functions
Lab: Designing and Implementing User-Defined Functions
11 – Responding To Data Manipulation Via Triggers
Design and Implement DML Triggers
Learn about Advanced Trigger Concepts
Create, Test and Improve Audit Triggers
Lab: Responding to Data Manipulation by Using Triggers
12 – Using In-Memory Tables
Work With Memory-Optimized Tables
Establish Natively Compiled Stored Procedures
Lab: Using In-Memory Database Capabilities
13 – Implementing Managed Code In Sql Server
Learn about CLR Integration in SQL Server
Implement and Publish CLR Assemblies
Lab: Implementing Managed Code in SQL Server
14 – Storing And Querying Xml Data In Sql Server
Understand XML and XML Schemas
Store XML Data and Schemas in SQL Server
Implement the XML Data Type
Use the Transact-SQL FOR XML Statement
Get Started with XQuery and Shredding XML
Lab: Storing and Querying XML Data in SQL Server
15 – Storing And Querying Spatial Data In Sql Server
Store and Query Spatial Data
Work with SQL Server Spatial Data Types
Apply Spatial Data to Applications
Lab: Working with SQL Server Spatial Data
16 – Storing And Querying Blobs And Text Documents In Sql Server
Understand Considerations for Working with BLOB Data
Store Unstructured Data Using FILESTREAM
Perform Full-Text Searches
Lab: Storing And Querying Blobs And Text Documents In SQL Server
17 – SQL SERVER CONCURRENCY
Learn how to Create Concurrency in SQL Server
Execute Transactions and Lock Internals to Establish Concurrency
Lab: SQL Server Concurrency
18 – Performance And Monitoring
Work with Extended Events
View and Interpret Live Query Statistics and Metrics
Optimize Database File Configuration
Lab: Monitoring, Tracing, and Baselining
Lab: Optimizing Indexes
Understand Core Indexing Concepts
Learn about Data and Index Types – Including Heaps, Clustered, Nonclustered, Single Column and Composite Indexes
Develop Heaps and Clustered and Covered Indexes
Lab: Implementing Indexes
Learn About Index Strategies, Managing Indexes and Execution Plans
Work with the Database Engine Tuning Advisor
Use the Query Store to Identify and Fix Queries
7 – Columnstore Indexes
Interpret, Create and Work with Columnstore Indexes
Build a Memory Optimized Columnstore Table
Lab: Using Columnstore Indexes
8 – Designing And Implementing Views
Create and Manage Views
Understand Performance Considerations for Views
Set up Standard and Updateable Views
Lab: Designing and Implementing Views
9 – Designing And Implementing Stored Procedures
Create and Work with Stored Procedures
Build and Implement Parameterized Stored Procedures
Control Execution Context
Lab: Designing and Implementing Stored Procedures
10 – Designing And Implementing User-Defined Functions
Understand the Types of Functions, Alternatives and Key Considerations for Implementation
Design and Implement Scalar and Table-Valued Functions
Lab: Designing and Implementing User-Defined Functions
11 – Responding To Data Manipulation Via Triggers
Design and Implement DML Triggers
Learn about Advanced Trigger Concepts
Create, Test and Improve Audit Triggers
Lab: Responding to Data Manipulation by Using Triggers
12 – Using In-Memory Tables
Work With Memory-Optimized Tables
Establish Natively Compiled Stored Procedures
Lab: Using In-Memory Database Capabilities
13 – Implementing Managed Code In Sql Server
Learn about CLR Integration in SQL Server
Implement and Publish CLR Assemblies
Lab: Implementing Managed Code in SQL Server
14 – Storing And Querying Xml Data In Sql Server
Understand XML and XML Schemas
Store XML Data and Schemas in SQL Server
Implement the XML Data Type
Use the Transact-SQL FOR XML Statement
Get Started with XQuery and Shredding XML
Lab: Storing and Querying XML Data in SQL Server
15 – Storing And Querying Spatial Data In Sql Server
Store and Query Spatial Data
Work with SQL Server Spatial Data Types
Apply Spatial Data to Applications
Lab: Working with SQL Server Spatial Data
16 – Storing And Querying Blobs And Text Documents In Sql Server
Understand Considerations for Working with BLOB Data
Store Unstructured Data Using FILESTREAM
Perform Full-Text Searches
Lab: Storing And Querying Blobs And Text Documents In SQL Server
17 – SQL SERVER CONCURRENCY
Learn how to Create Concurrency in SQL Server
Execute Transactions and Lock Internals to Establish Concurrency
Lab: SQL Server Concurrency
18 – Performance And Monitoring
Work with Extended Events
View and Interpret Live Query Statistics and Metrics
Optimize Database File Configuration
Lab: Monitoring, Tracing, and Baselining
Lab: Optimizing Indexes