70-461, 761: Querying Microsoft SQL Server with Transact-SQL
The course prepares you for the 70-461 certification exam, but even if you’re not planning to take the exam, it provides invaluable knowledge for anyone working with SQL Server.
It starts by guiding you through downloading and installing SQL Server and SQL Server Management Studio (SSMS) on your computer.
You’ll learn how to create databases, tables, and perform basic queries using SELECT statements.
The instructor takes you step-by-step, explaining concepts clearly using simple language and examples.
As you progress, you’ll dive deeper into working with various data types like integers, strings, and dates.
The course covers essential T-SQL functions for manipulating data, such as mathematical, string, and date functions.
You’ll also learn how to handle NULL values and concatenate strings with numbers.
One of the course’s strengths is its focus on JOINs, a crucial concept in SQL.
The instructor explains different types of JOINs (INNER, LEFT, RIGHT, CROSS) and how to combine data from multiple tables effectively.
You’ll also learn how to find missing data and update or delete existing data using T-SQL statements.
The course dedicates significant time to creating and modifying constraints like unique, default, primary key, and foreign key constraints.
You’ll understand how these constraints ensure data integrity and learn to implement them using T-SQL statements.
Another highlight is the comprehensive coverage of views, triggers, and stored procedures.
You’ll learn how to create, alter, and secure views, as well as how to implement different types of triggers (AFTER and INSTEAD OF) for auditing and data manipulation purposes.
The course also teaches you how to create and use stored procedures, including handling errors and returning data.
As you progress, you’ll explore advanced topics like combining datasets using UNION, INTERSECT, and EXCEPT, as well as pivoting and unpivoting data.
The course also covers analytical functions like PARTITION BY, RANK, and NTILE, which are essential for complex data analysis.
The instructor doesn’t stop there; you’ll also learn about managing transactions, indexes, and query optimization techniques.
The course covers isolation levels, locking mechanisms, and how to read and interpret query plans to identify performance bottlenecks.
Throughout the course, you’ll work with practical examples and coding exercises, solidifying your understanding of the concepts.
The instructor’s clear explanations, combined with the hands-on approach, make it easy to follow along and grasp even the most complex topics.
SQL Server Integration Services (SSIS) - An Introduction
This course starts by guiding you through the installation process for all the necessary software, including Visual Studio, SQL Server Developer, and SQL Server Management Studio.
Once you have the environment set up, you’ll dive into the core functionality of SSIS.
You’ll learn how to create simple extract and load tasks using the OLE DB Source and Destination components.
The course covers data transformations like derived columns, conversions, and handling implicit data type changes.
As you progress, you’ll explore more advanced transformations such as aggregations, multi-casting, conditional splits, error handling, and data sampling techniques.
You’ll also learn how to work with different data sources like Excel files, text files, and even Microsoft Access databases.
It teaches you how to merge data from multiple sources using the Union All and Merge transformations.
You’ll also learn about auditing data flows and adding different types of destinations for your transformed data.
Beyond just data flows, you’ll gain experience with control flow tasks, variables, and even scripting components in SSIS packages.
The course provides plenty of hands-on practice activities to reinforce the concepts you’ve learned.
Mastering SQL Server 2016 Integration Services (SSIS)-Part 1
This course starts with an overview of SSIS and its role in data migration, then dives into hands-on exercises to reinforce the concepts.
You’ll learn how to install the necessary software like SQL Server, SSMS, Visual Studio, and SSDT.
The course also covers setting up a sample database to practice with.
From there, you’ll explore various data migration techniques, including the Import Export Wizard.
A significant portion focuses on working with Data Flow Tasks, which are essential for transforming and moving data.
You’ll gain experience with transformations like CharacterMap, CopyColumn, DerivedColumn, Sampling, Sorting, UnionAll, Multicast, ConditionalSplit, Aggregation, Auditing, Exporting/Importing Columns, Merging, Joining, Lookup, Caching, Fuzzy Lookup, Fuzzy Grouping, RowCount, OLEDBCommand, Pivoting, Unpivoting, TermExtract, and TermLookup.
The course also covers Control Flow tasks like BulkInsert, SequenceContainer, ExecuteProcess, ExecutePackage, and working with parameters.
You’ll learn about FileSystem, WebService, XML, and DataProfiling tasks, as well as various transfer tasks for databases, objects, jobs, logins, and error messages.
Containers and enumerators are explored in-depth, including For Loop, For Each File, Item, Variable, Node List, SMO, and ADO enumerators.
You’ll work with configuration files, tables, and creating dynamic packages, connection managers, and tasks.
Event handling techniques like precedence constraints, log providers (text, XML, Windows Event Log), and handling events are covered.
The course also delves into WMI tasks like WMIDataReader and WMIEventWatcher, as well as message queuing techniques.
Maintenance planning tasks like BackupDatabase, SQL Server Agent Jobs, Execute T-SQL, Update Statistics, HistoryCleanup, ShrinkDatabase, RebuildIndexes, ReorganizeIndex, and CheckDatabaseIntegrity are included.
You’ll learn about checkpoints, working with various SSIS sources and destinations (XML, RAW files, ADO.NET, RecordSet, DataReader), and incremental data loading techniques.
The CDC components are explored for initial loads, incremental inserts, updates, and deletes.
Finally, the course covers deploying SSIS packages and executing them manually, through a scheduler, or via T-SQL.
Complete Microsoft SQL Server Database Administration Course
This course starts with an introduction to databases, SQL, and different database management systems, with a focus on Microsoft SQL Server.
You’ll learn how to set up a virtual environment using VMWare or VirtualBox, and install Windows Server as well as SQL Server itself.
The course covers different versions and editions of SQL Server, guiding you through the installation and configuration process step-by-step.
Once the setup is complete, you’ll dive into the fundamentals of databases, including data storage, tables, keys, relationships, and normalization.
You’ll create your first database, tables, and populate them with data, getting hands-on experience with SQL commands like SELECT, INSERT, UPDATE, and DELETE.
The course then moves into SQL Server administration topics.
You’ll explore SQL Server’s system databases like master, tempdb, and msdb, and learn about the internal architecture of SQL Server.
Database backup and restore procedures are covered in-depth, including full, differential, and transaction log backups, as well as point-in-time recovery.
User management is another key area, covering security models, authentication modes, principals (logins, users, roles), and permissions management using GRANT, DENY, and REVOKE.
You’ll also learn about the SQL Server Agent for scheduling jobs, alerts, and monitoring.
Advanced topics include high availability, replication (with a lab on setting up transactional replication), log shipping, query tuning, and encryption methods like Transparent Data Encryption (TDE).
You’ll gain practical experience through various labs and exercises throughout the course.
SQL Server Administration Part1
In this course you will learn essential skills for setting up and configuring SQL Server, including creating virtual machines, installing SQL Server, and working with domains and Active Directory.
One of the key focuses is understanding SQL Server’s core components, such as system databases, data and log files, and the transaction log.
You will gain hands-on experience with critical tasks like creating databases, managing tempdb, attaching and detaching databases, and implementing different types of backups (full, transactional log, and differential).
The course dives deep into backup and restore operations, covering GUI and T-SQL methods, as well as using differential backups and recovery modes.
You will also learn to automate these tasks with SQL Server Agent and Maintenance Plans, ensuring efficient database management.
Security is another crucial aspect covered, with demonstrations of users, logins, roles, and permissions.
You will understand how to manage security using T-SQL, select authentication modes, and implement best practices for securing your SQL Server environment.
Throughout the course, you will work with virtual machines and tools like VMware Pro Player, providing a practical learning experience.
MS SQL Server: Learn MS SQL Server from Scratch
You’ll start by learning the fundamentals of databases, SQL, and the SQL Server ecosystem.
This includes understanding what a database is, how relational database management systems (RDBMS) work, and the role of SQL queries.
From there, you’ll dive into the core concepts of database normalization and normal forms, which are essential for designing efficient and effective databases.
The course will guide you through installing SQL Server Express Edition 2019 and familiarizing yourself with SQL Server Management Studio (SSMS), the primary tool for interacting with SQL Server databases.
You’ll learn how to create, alter, and drop databases, as well as tables within those databases.
The course covers data types and constraints, including foreign keys, unique constraints, and check constraints.
Quizzes are included to reinforce your understanding of these foundational topics.
Once you’ve mastered the basics, the course moves on to manipulating data within SQL Server.
You’ll learn how to insert, update, and delete data from tables, as well as how to use the AdventureWorks sample database for practice.
The course covers select statements, aliases, where clauses, and operators, giving you the skills to retrieve and filter data effectively.
Sorting and grouping data are essential skills for any SQL developer, and the course dedicates a section to these topics.
You’ll learn how to sort and group data using various techniques, with quizzes to test your knowledge.
The course also covers wildcard operations, like and case statements, and conversion functions like convert and cast.
You’ll learn how to retrieve data from multiple tables using different types of joins, including inner, left, right, full, and self joins.
Set operations, such as union, intersect, and except, are covered, as are subqueries, which allow you to embed queries within other queries.
The course also teaches you how to insert, update, and delete data using subqueries.
Date and time functions, string functions, and math functions are all covered, giving you a well-rounded set of tools for working with different types of data.
The course also covers SQL transactions and transaction commands, which are essential for maintaining data integrity.
Finally, you’ll learn about schema objects, user management, logins, permissions, and roles, equipping you with the skills to manage and secure SQL Server databases effectively.
70-462: SQL Server Database Administration (DBA)
In this course you will learn how to install and configure SQL Server, manage databases, implement security measures, optimize performance, and troubleshoot issues.
The course starts by guiding you through the installation process, including choosing the right hardware, planning for capacity, and setting up SQL Server components like Visual Studio SSDT.
You’ll learn how to migrate data from other sources and implement a migration strategy.
Once SQL Server is set up, you’ll dive into database management.
This includes configuring databases, managing file space, implementing compression techniques, and working with features like partitioning and contained databases.
The course covers backup and restore strategies, teaching you how to perform backups, restore databases, and implement point-in-time recovery.
Security is a crucial aspect, and you’ll learn how to create login accounts, manage access to the server and databases, implement auditing strategies, and monitor for elevated privileges or unauthorized access attempts.
You’ll also explore server roles, database roles, and schemas for controlling permissions.
Performance optimization is another key area covered.
You’ll learn about indexes, including how to implement, maintain, and optimize them.
The course also teaches you how to diagnose and resolve concurrency issues like blocking, deadlocking, and locking problems.
Troubleshooting is an essential skill for DBAs, and you’ll learn how to collect and analyze data using tools like SQL Server Profiler, Extended Events, and System Monitor.
You’ll learn to identify bottlenecks and performance issues related to CPU, memory, and I/O.
The course also covers SQL Server Agent, teaching you how to create, maintain, and monitor jobs and alerts.
You’ll learn about features like RAISERROR for generating custom error messages and event alerts.
Throughout the course, you’ll have opportunities to practice what you’ve learned through hands-on activities, ensuring you gain practical experience in addition to theoretical knowledge.
SQL & Database Design A-Zâ„¢: Learn MS SQL Server + PostgreSQL
This course starts with the basics of installing the necessary software on Windows and Mac, then dives into writing your first SQL queries.
You’ll learn how to use SELECT statements, filters with WHERE, logical operators like AND and OR, and wildcards for pattern matching.
The course covers working with different data types like numbers, text, and dates.
You’ll learn how to perform calculations, order results, handle null values, and do implicit type conversions.
Practical examples like analyzing console game sales data reinforce the concepts.
Moving on, you’ll explore database theory fundamentals like structured vs. unstructured data, database elements, relational keys, functional dependencies, and the relational data model itself.
This lays the groundwork for understanding joins, which are covered in-depth with inner, outer, cross joins, handling duplicates, and joining on multiple fields.
A real-world vet clinic database provides hands-on practice.
Creating tables is another key part of the curriculum, covering CREATE, INSERT, ALTER and DROP statements for defining and modifying database structures.
You’ll also learn database design principles like normalization up to the 3rd normal form, applying the concepts to an online clothing store database.
Throughout, you’ll work with both PostgreSQL/PgAdmin and Microsoft SQL Server/SSMS, ensuring you gain experience with two widely-used database platforms.
The course is very hands-on with challenges and datasets provided to solidify your learning.
SQL Server Performance Tuning Part 1
You will start by setting up a virtual machine and installing SQL Server 2016 along with SQL Server Management Studio (SSMS).
After restoring a database, the course dives into the fundamentals of SQL performance tuning.
You will learn about hardware components and their impact on performance.
The course covers autogrowth settings, which can significantly affect database performance.
Indexes, a crucial aspect of query optimization, are explained in-depth.
You will understand clustered and non-clustered indexes, B-trees, index anatomy, and different types of indexes.
The course then delves into the query optimizer, exploring operators like table scans, sorts, and joins.
You will learn how to read and interpret execution plans, a vital skill for performance tuning.
Statistics, another essential component of the optimizer, are also covered.
Moving on, you will study locking and blocking, two critical concepts in multi-user environments.
The course explains deadlocks and how to analyze wait statistics to identify performance bottlenecks.
It also covers recompilations, which can impact query performance.
Finally, you will learn about tempdb, a crucial database for internal operations in SQL Server.
Throughout the course, you will encounter quizzes to reinforce your learning.
Microsoft SQL Server Reporting Services (SSRS): 2022 edition
You’ll start by learning how to install the necessary software, including SQL Server, Visual Studio, and the AdventureWorks sample database.
The course then dives into the fundamentals of creating reports using SSRS.
You’ll learn how to use the Report Wizard to quickly generate reports, as well as how to build reports from scratch.
Along the way, you’ll practice formatting data, sorting and grouping, controlling visibility, and working with table headers.
As you progress, you’ll explore more advanced techniques like creating drill-through reports, categorized reports, adding parameters, and linking reports with hyperlinks.
The course also covers adding sub-reports, page headers and footers, drop-down parameter lists, and handling null values.
You’ll gain experience with conditional formatting, indicators, gauges, totals, interactive sorting, and document maps.
Additionally, the course teaches you how to create graphical reports such as pie charts, bar charts, and matrices, as well as how to map data visually.
The course delves into object properties, allowing you to customize the appearance and behavior of various report elements.
You’ll also learn about functions in SSRS, including program flow functions (IIF, CHOOSE, SWITCH), aggregate functions, math functions, text functions, and date/time functions.
Throughout the course, you’ll have the opportunity to reinforce your learning with several practice activities, each followed by a solution walkthrough.