70-461 Session 3: Querying Microsoft SQL Server 2012
Use the coupon code SQLINTRO to save 50%
About This Course
This course is the foundation for the Microsoft Certificate 70-461: “Querying Microsoft SQL Server 2012”.
Rather than present one huge course, this course takes some of the basics and then goes in depth. In Session 1, we learned all about dates, strings and number data types, and in Session 2 we constructed SELECT queries using SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY, and JOINing multiple tables together.
Take This Course
(That’s $3 per hour!)
We’ll now use that data to create views, which enable us to store these SELECT queries for future use, and triggers, which allow for code to be automatically run when INSERTing, DELETEing or UPDATEing data.
We’ll look at the database that we developed in session 2, and see what is wrong with it. We’ll add some constraints, such as UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY constraints, to stop erroneous data from being added some data. By doing this, we will complete objectives 2, 3, 4 and 5 from the 70-461 exam.
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.
Video: 3.5 hours
Skill Level: Beginner Level
Includes: Lifetime access
30 day money back guarantee!
Available on iOS and Android
Certificate of Completion
Preview video 13
(Why not view the below video full screen? Click the YouTube logo in the bottom-right hand corner.)
Want to see more preview videos?
What are the requirements?
You need to know all about data types and functions.
You also need to know about how to create SELECT statements and INSERT, DELETE and UPDATE statements.
If you don’t, then you should take my Session 1 or Session 2 course first.
There is a 30-day money back guarantee of this Udemy course.
Why not have a look at the curriculum below and see what you can learn? If you already know them, please see my later sessions.
What am I going to get from this course?
- 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”
What is the target audience?
- This course is for you if want a refresher course in SQL, or are learning SQL for the first time.
- This course is for you if you are working towards the requirements for exam 70-461, or if you just want to expand your knowledge of T-SQL.
- If you are starting from the beginning, then you should look at my session 1 or 2 courses before taking this session 3.
- If you want more advanced topics, then please look at my later sessions.
Do you have a question – why not ask it in the box below?
70-461 Session 3: Querying Microsoft SQL Server 2012 | Udemy
|Section 1: Introduction|
|We’ll recap on what we learned in Sessions 1 and 2, and look forward to Session 3.|
|All the code that we are going to type can be found in a Word document, which is included in the resources to this lecture.To get it, click on “View Resources”, and then click the download button (a button with a down arrow).|
|What are we going to cover today, and how does that tie in the objectives in exam 70-461?|
|We’ll have a look at the database that we built up in Day 1, and recap on some of the problems that we found.|
|Section 2: Objective 4 – Create and modify constraints (simple statements)|
|What do constraints? In broad terms, not allowing rogue data to be entered, or adding missing data. How are these constraints enforced?|
|What unique constraints are – whether NULLs are allowed – how they are checked (index|
|We will add a unique constraint in our table, and test that it is working.|
|Quiz 1||3 questions|
|First quiz of the day – let’s see how much you can remember.|
|We’ll define what a default constraint is, why it can be useful (for datestamps, users, primary keys), and how it can be defined when creating the table, and added subsequently to existing columns and new columns|
|We will add a default constraint into a table, add NULL data, and non-NULL data|
|Quiz 2||3 questions|
|A few questions about default constraints.|
|We’ll be looking at the concept of the Check constraint – what circumstances they should be used? – bespoke realistic limitations.|
|We’ll be adding a variety of check constraints, both in number, string and data fields, and testing them.|
|Quiz 3||3 questions|
|A couple of questions about what you’ve just learned.|
|We’ll define what a primary key is, why it is important, whether it needs to be only on one column, and how it can be defined, both when creating the table and how it can be added subsequently to an existing column and a new column|
|We’ll be adding a primary key using an IDENTITY default to the tblEmployee table, and testing it by adding additional employees.|
|Quiz 4||3 questions|
|A few questions…|
|We’ll be looking at foreign keys – what are they, and how do they relate to other tables|
|We’l be adding a foreign key to tblTransaction, testing it, and updating the Database Diagram.|
|We’ve just done Objective number 4 – let’s continue to Objectives number 2 and 3.|
|Section 3: Objectives 2 and 3: Views|
|How do you store a complex SELECT query? One way is by using a view. We’ll see how to do that.|
|We’ll look at retrieving a previously stored view and altering it, and then deleting it.|
|There are two types of securing views. One is to stop anyone from reading it; the other is to stop anyone from altering the tables it needs. We’ll look at both types.|
|We’ll look at what security credentials are needed to read a view, and whether you need permissions to read the underlying tables and other objects.|
|Views are often based on multiple tables. Can you add new rows or alter existing rows to the underlying tables in views?|
|Can you delete rows in more than one underlying table in views, and can you delete rows in tables that are not visible in views?|
|We had a brief look at indexes earlier – we’ll have a deeper at what they actually do.|
|It’s not as easy to create an indexed view as it is to create an indexed table. Let’s go through all the changes we have to make.|
|Section 4: Objective 5: Create and alter DML triggers|
|We’ll be looking why triggers were created, and the two types – INSTEAD OF and AFTER|
|We’ll be adding some auditing to the tblTransaction by adding an AFTER trigger to show the rows which have been inserted or deleted.|
|What if a trigger inserts a row which starts another trigger. We’ll have a look at how to deal with nested triggers. Also, can a trigger call itself repeatedly?|
|As there are restrictions in adding rows to views, we’ll use an INSTEAD OF trigger to replicate it instead.|
|Which columns have been updated? We’ll have a look at UPDATE(), COLUMNS_UPDATED() and @@ROWCOUNT.|
|Our INSTEAD OF trigger had an flaw – it could only delete one row at once. Let’s correct that.|
|We’ll just recap on what we have learned.|
|Section 5: End of Session 3|
|We’ll look back at this session, look forward to the next session, and give you a voucher code so that you can save money on your next sessions.|