70-461 Session 3: Querying Microsoft SQL Server 2012

Using T-SQL code in SSMS, we'll add constraints to data, store SELECT queries in views, and add triggers.
4.3 (12 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.
183 students enrolled
$19
$20
5% off
Take This Course
  • Lectures 33
  • Length 3.5 hours
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 7/2015 English

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

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?

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.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
02:21

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

Article

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

02:56

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

05:07

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)
05:52

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

02:57

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

10:15

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

3 questions

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

02:42

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

10:05

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

3 questions

A few questions about default constraints.

03:24

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

11:29

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

3 questions

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

06:24

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

12:12

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

3 questions

A few questions...

08:46

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

11:46

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

02:09

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

Section 3: Objectives 2 and 3: Views
09:23

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

06:59

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

05:22

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.

07:30

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.

10:59

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

03:05

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?

05:32

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

08:23

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
05:32

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

06:13

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

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?

10:15

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

11:06

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

05:50

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

01:28

We'll just recap on what we have learned.

Section 5: End of Session 3
03:05

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.

01:54

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

1 question

Here's the questions...

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Phillip Burton, SQLIntro.com

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.

Ready to start learning?
Take This Course