This course is the foundation for the Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012".
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
As there are restrictions in adding rows to views, we'll use an INSTEAD OF trigger to replicate it instead.
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?
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.
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.
We'll just recap on what we have learned in Session 3, and look at what we can learn in Session 4.
Here's the questions...
Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.
He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.
He has also developed expertise and programmes to catalogue and process and control electronic data, large quantities of paper or electronic data for structured analysis and investigation.
He is one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards and was one of Expert Exchange's top 10 experts for the first quarter of year 2015.
His interests are working with data, including Microsoft Excel, Access and SQL Server.