70-461 Session 3: Querying Microsoft SQL Server 2012 (T-SQL)
4.7 (53 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
499 students enrolled
Wishlisted Wishlist

Please confirm that you want to add 70-461 Session 3: Querying Microsoft SQL Server 2012 (T-SQL) to your Wishlist.

Add to Wishlist

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

Using T-SQL code in SSMS, add constraints, store queries in views, add triggers. SQL Server 2012, 2014, 2016 (70-761)
4.7 (53 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
499 students enrolled
Created by Phillip Burton
Last updated 6/2017
English
Curiosity Sale
Current price: $10 Original price: $50 Discount: 80% off
30-Day Money-Back Guarantee
Includes:
  • 3.5 hours on-demand video
  • 1 Article
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I 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"
View Curriculum
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?
Description

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.

Who 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
33 Lectures
03:32:21
+
Introduction
4 Lectures 10:34

We'll recap on what we learned in Sessions 1 and 2, and look forward to Session 3.

Preview 02:21

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).

Resources
00:10

What are we going to cover today, and how does that tie in the objectives in exam 70-461?

Preview 02:56

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.

Preview 05:07
+
Objective 4 - Create and modify constraints (simple statements)
12 Lectures 01:28:01

What do constraints? In broad terms, not allowing rogue data to be entered, or adding missing data. How are these constraints enforced?

What are constraints?
05:52

What unique constraints are - whether NULLs are allowed - how they are checked (index

Unique constraints - what are they?
02:57

We will add a unique constraint in our table, and test that it is working.

Unique constraints in action
10:15

First quiz of the day - let's see how much you can remember.

Unique constraints
3 questions

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

Default constraints - what are they?
02:42

We will add a default constraint into a table, add NULL data, and non-NULL data

Preview 10:05

A few questions about default constraints.

Default constraints
3 questions

We'll be looking at the concept of the Check constraint - what circumstances they should be used? - bespoke realistic limitations.

Check constraint - what are they?
03:24

We'll be adding a variety of check constraints, both in number, string and data fields, and testing them.

Check constraints - in practice
11:29

A couple of questions about what you've just learned.

Check constraints
3 questions

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

Primary key
06:24

We'll be adding a primary key using an IDENTITY default to the tblEmployee table, and testing it by adding additional employees.

Primary key - in practice
12:12

A few questions...

Primary Keys
3 questions

We'll be looking at foreign keys - what are they, and how do they relate to other tables

Foreign key - what is it?
08:46

We'l be adding a foreign key to tblTransaction, testing it, and updating the Database Diagram.

Foreign key - in practice
11:46

We've just done Objective number 4 - let's continue to Objectives number 2 and 3.

Summary
02:09
+
Objectives 2 and 3: Views
8 Lectures 57:13

How do you store a complex SELECT query? One way is by using a view. We'll see how to do that.

Creating views
09:23

We'll look at retrieving a previously stored view and altering it, and then deleting it.

Altering and dropping views
06:59

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.

Securing views
05:22

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.

Security
07:30

Views are often based on multiple tables. Can you add new rows or alter existing rows to the underlying tables in views?

Preview 10:59

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?

Deleting rows in views
03:05

We had a brief look at indexes earlier - we'll have a deeper at what they actually do.

What is an index?
05:32

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.

Creating an indexed view
08:23
+
Objective 5: Create and alter DML triggers
7 Lectures 51:32

We'll be looking why triggers were created, and the two types - INSTEAD OF and AFTER

What are triggers
05:32

We'll be adding some auditing to the tblTransaction by adding an AFTER trigger to show the rows which have been inserted or deleted.

Creating an AFTER trigger
05:45

As there are restrictions in adding rows to views, we'll use an INSTEAD OF trigger to replicate it instead.

Creating an INSTEAD OF trigger
11:36

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?

Nested triggers
10:15

Which columns have been updated? We'll have a look at UPDATE(), COLUMNS_UPDATED() and @@ROWCOUNT.

Update functions
11:06

Our INSTEAD OF trigger had an flaw - it could only delete one row at once. Let's correct that.

Handling multiple rows in a session
05:50

We'll just recap on what we have learned.

Summary
01:28
+
End of Session 3
2 Lectures 04:59

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.

BONUS - Recap, Look ahead and Voucher Code
03:05

We'll just recap on what we have learned in Session 3, and look at what we can learn in Session 4.

Well done!
01:54

Here's the questions...

Foreign Keys
1 question
About the Instructor
Phillip Burton
4.4 Average rating
3,293 Reviews
46,184 Students
25 Courses
Best Selling Instructor - over 45,000 students so far

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.