70-461 Session 3: Querying Microsoft SQL Server (T-SQL code)

Using T-SQL in SSMS, add constraints, create views, add triggers. SQL Server 2012, 2014, 2016, 2017, 2019, 2022

What you'll learn:

• identify structural problems in databases
• create UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY constraints to more to stop erroneous data from being added.
• create views, to allow for encapsulation of SELECT statements and provide higher security.
• create triggers, to allow for code to be automatically run after or instead of a DML command.
• complete objectives 2, 3, 4 and 5 and work towards other objectives from Microsoft Certification 70-461 "Querying Microsoft SQL Server 2012"

This course is the foundation for the Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012/2014". Please note - this certificate is no longer being offered by Microsoft. However, the exam requirements allow you to have a good understanding of T-SQL.

We'll start off by creating views, which enable us to store these SELECT queries for future use. We'll add security and create indexed views.

We'll then create AFTER and INSTEADOF triggers, which allow for code to be automatically run when INSERTing, DELETEing or UPDATEing data. We'll look at what happens when triggers run triggers, and how to handle multiple rows in a session.

We'll look at the database that we previously developed, and see what is wrong with it. To solve these problems, we'll add some constraints, such as UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY constraints. This will stop erroneous data from being added some data.

No prior knowledge other than what we covered in Sessions 1 and 2 is required. However, prior experience with Excel or Access would be beneficial.

There are regular quizzes to help you remember the information.

Once finished, you will know what how to write triggers, views and constraints, and we'll have expanded on our current knowledge of T-SQL.