Querying Microsoft SQL Server 2012 - (Exam No. 70-461)
3.8 (78 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.
610 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Querying Microsoft SQL Server 2012 - (Exam No. 70-461) to your Wishlist.

Add to Wishlist

Querying Microsoft SQL Server 2012 - (Exam No. 70-461)

Master in SQL Server 2012 in a step-by-step manner
3.8 (78 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.
610 students enrolled
Created by Compaq learning
Last updated 3/2015
English
Learn Fest Sale
Current price: $10 Original price: $85 Discount: 88% off
2 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 6.5 hours on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • List different SQL data types
  • Make, amend and delete various database structures
  • Modify constraints
  • Make new data storing procedures
  • Maximize querying
  • Work with various functions
  • Know to handle errors
  • Manage transactions
View Curriculum
Requirements
  • Knowledge of TSQL, javascript and C++ will be an added advantage for any particpant who is taking up this course. awareness about RDMS would be an added advantage.
Description

Querying Microsoft SQL Server (Exam 70-461) training has been designed to help learners ace the Microsoft SQL Server 70-461 exam. The course is based on the curriculum prescribed by Microsoft.

Taking leap into the data management system had Microsoft come up with a new, authentic and infallible version of SQL Server, Microsoft SQL Server 2012. It offers a trustworthy storage of data for not so important websites and other desktop applications. In this course you will be introduced to the construction and usage of databases in this platform.

In this course, students who are preparing themselves for this exam will be showcased with all the necessary technical skills to write basic Transact-SQL queries for Microsoft SQL Server 2012. Also, another point that adds value to this course is that it is the basis for all other SQL Server-related disciplines—Database Development, Database Administration, and Business Intelligence—are few among them. This course is more or less a guide that will help you with exam 70-461 preparation, known as Querying Microsoft SQL Server 2012.

It is the advent of cloud that has made this exam so popular amongst the professionals. Apart from cloud, other enterprises are considers SQL Server 2012 as a strong platform as there are many other trivial systems founded on it.

By the end of the course you will be trained in creating as well as managing databases in SQL Server 2012. Apart from learning about various database objects including tables, columns, keys and others, there will be an overall understanding of how these objects help store data by providing a well-constructed structure. As add-ons you will also know to query for stored data, add new data, update and delete existing and unwanted data.

All these and more are combined in 137 lecture with demos to help you out. There are end of the lesson quiz sections which will check your understanding of the lessons.

15 hours of learning provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012. Passing the Querying Microsoft SQL Server 2012 (70-461) exam is a requirement to getting MCSA certified, which in turn is a prerequisite to achieve an MCSE certification.

Who is the target audience?
  • This is intended for professionals who want to excel in querying. Therefore working professionals like database administrators, developers, system engineers and business professionals can take up this exam. This the first level so if you're looking for an advanced version this course is not for you.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 115 Lectures Collapse All 115 Lectures 06:25:33
+
Introduction to SQL Server 2012
12 Lectures 30:32

Get introduced to SQL Server 2012

Preview 00:48

Understand RDBM. RDBMS is an acronym for Relational Database Management System and is the most popular and widely used database system in the world.

Preview 03:14

SQL Server 2012 is available in 6 different editions. You can pick the one that matches what you need as well as your budget.

Preview 04:51

Main components of SQL Server

Preview 01:58

SQL Server Management Studio, or SSMS in short, is probably the most important tool in the product.
SQL Server Tools
02:06

SQL Server Management Studio, or SSMS in short, is probably the most important tool in the product. SQL Server Management Studio, also referred to as SSMS, is the main management tool of the SQL Server product.

SQL Server Management Studio
01:55

Learn how to use SSMS to manage the Database Engine and what all you can do with it.

Managing Databases
01:46

The Object Explorer, as shown in the first screenshot, is a pane that appears on the left side of the SSMS window.
SSMS Windows
03:22

Explore different parts of SSMS and take a look at some of the SQL Server components that are available.

Demo - Exploring SSME
02:12

There are many different structures that are stored within a database file,

Understanding Database Objects
03:25

We will see how you can create and manage certain database objects using the SSMS tool.

Demo - Creating Database Objects using SSMS
03:21

Discuss the main points of this lesson.

Conclusion
01:34

Quiz
3 questions
+
Working with T-SQL
14 Lectures 52:21
A table is the basic form of data storage units. All data in a database is stored in tables that you create.
Working with T - SQL
07:03

Explore some of the database objects and structures in a pre created database.

Demo Viewing a Table Structure
02:00

The Structured Query Language – popularly known as SQL – is the main language for talking to RDBMS. SQL, which is different from SQL Server, the product, is a unique computer language. It is

Querying
01:40

Microsoft's flavor of SQL is called T-SQL which is a short form for Transact SQL. T-SQL contains many extensions that are specific to SQL Server and obviously works with them.

T - SQL
03:03

Work with a simple query in T-SQL to see what it allows us to do.

Demo Writing a Simple Query
00:55

Introduces you to some T-SQL statements that allow to manage objects like databases and tables in an SQL Server platform.

Managing Database Objects
03:09

You will see how we can manage different database objects in SQL Server using the CREATE, ALTER and DROP commands.

Demo - Add, Modify, and Delete Database Objects
03:37

Explain constraints, what they are, the different types that are available, and why they are used.

Constraints
08:22

See how to use different constraints and the effect they have on data within the table.

Demo - Adding Constraints
08:01

SELECT is an important query command.

SELECT
03:44

Look at some simple uses of the SELECT statement.

Demo - Querying Data from Tables
03:13

The CASE extension is one that lets you change the way a value in a column is displayed in the result.

CASE
02:19

See how you can use the different CASE extensions for more advanced scenarios involving the SELECT statement.

Demo - Advanced Queries
03:33

Conclusion
01:42

Quiz
3 questions
+
Understanding SQL Data Types
11 Lectures 41:48

When you store data in a table under different columns, each column is allowed to be for a single data type.

Understanding SQL Data Types
04:25

The default set of string based data types stores simple character strings.
String Types
04:42

MAX Setting
04:04

You will see how you can work with String data types in SQL Server and store different types of string data within them.

Demo - Using Character, UNICODE, and Binary Strings
04:14

The first subcategory of numeric data types is the ones that store exact numeric values. These have further subcategories under them as well.

Numeric Types
03:16

See how you can work with numeric data and store different numeric values in a table.

Demo - Working with Numeric Data
01:46

look at the Date types that are available.
Date and Time
03:59

See how we can use different date and time types as well as the effect that changing the date format has on the database in understanding dates.

Demo - Working with Date and Time
03:18

Most of the advanced data types are meant for special purposes and scenarios.

Advanced Data Types
05:56

Look at some of the advanced data types available in SQL Server.

Demo - Exploring Some Advanced Data Types
05:20

Conclusion
00:48

Quiz
3 questions
+
Sorting and Filtering
9 Lectures 36:56

Learn how you can use the ORDER BY clause to sort results of a query and some of its different forms that you can use.

Sorting and Filtering
05:54

Learn to specify multiple columns to sort on in an ORDER BY clause by separating the column names with commas. The sort will be performed in the order of the columns given.

Sorting with ORDER BY, and Performing Multiple Column and Ascending - Descending
02:35

Extended Capabilities for Sorting
06:42

See how to use the TOP, OFFSET and FETCH clauses for paging records.

Demo - Using TOP, OFFSET, and FETCH
02:50

A single condition is the simplest form of input for the WHERE clause.
Filtering Results
08:03

Use the WHERE clause, different predicates and operators to filter search results.

Demo - Filter using WHERE, and Different Predicates and Operators
03:54

Understand some advanced ways of filtering data in SELECT queries for different types of results.

Advanced Filtering
03:29

See how to use Unicode strings in queries as well as the ANY and ALL clauses.

Demo - Unicode, ANY, and ALL filters
02:03

Conclusion
01:26

Quiz
3 questions
+
Query Multiple Tables using JOINs
11 Lectures 30:45

See how you can query from multiple tables at the same time and the way that SQL Server handles this.

Query Multiple Tables using JOINs
03:13

To understand how SQL Server handles queries from multiple tables, one must first understand why you use multiple tables in the first place. This is a core concept of RDBMSs called “normalization”.

Multiple Tables
03:45

See how querying works when using multiple tables simultaneously.

Demo - Querying Multiple Tables without a JOIN
02:31

Look at the different types of JOIN and the differences between each and why each should be used with examples.

Using JOIN
02:09

Simple Join
00:56

Demo - Using Implicit Join between Tables
01:42

The CROSS JOIN is the simplest form of a JOIN. When you use a CROSS JOIN in a SELECT statement, it is functionally identical to the case of simply providing the names of the tables in the FROM clause.

JOINs
07:42

See how using different types of JOINs show different results.

Demo - Using CROSS, INNER, and OUTER JOINs
03:31

Learn how you can create advanced JOINs using certain keywords.

Advanced JOINs
03:24

See how the join hints are used in queries.

Demo - Using MERGE, HASH, and LOOP
00:54

Conclusion
00:58

Quiz
3 questions
+
Modifying Table Data
11 Lectures 44:52

See how data can be added to a table using the INSERT command. You will also see its many variant syntaxes that allow you to add data in different ways to the table.

Modifying Table Data
05:53

Learn how to add records to a table using the different forms of the INSERT statement.

Demo - Using Different Forms of INSERT to Add Data to a Table
04:26

The third method of bulk adding data is helpfully called an obvious BULK INSERT. This is most useful when the data is external to the database.

Other Methods to Add Data
04:58

See how you can use the SELECT, SELECT INTO and BULK INSERT commands to add multiple records to a table in one go.

Demo - Using Bulk Adding Methods
05:49

Know the UPDATE statement allows you to modify existing data in a table.

Modifying Data in a Table
02:45

Learn how to update records in a table using the UPDATE command and specify records to update using its WHERE clause.

Demo - Modifying Data with UPDATE
01:37

Know how to remove data – that is, records – from a table in SQL using the DELETE statement.

Removing Data from Tables
04:12

See how records can be deleted from a table using the DELETE and TRUNCATE statements.

Demo - DELETE and TRUNCATE Data from Tables
03:38

Learn an advanced feature of SQL Server that lets you synchronize data between two tables. This is done using a new statement called MERGE that combines the power of the INSERT, UPDATE and DELETE statements.

Synchronizing Data between Tables
04:34

See how we synchronize data between two tables using the MERGE statement along with the 2 rules.

Demo - Synchronizing Data using MERGE
05:56

Conclusion
01:04

Quiz
3 questions
+
Programming in SQL Server
17 Lectures 59:16

Learn about functions, what they are, how they work and how you can use them. Also, see the different categories of functions that exist and look at some of the most commonly used functions within those categories.

Programming in SQL Server
03:15

A function is a method that adds a specific functionality to a SQL Server statement.
Function Categories
01:47

The LEN function returns the length of a string.
String Functions
01:57

Conversion functions are an important part of SQL Server. By default, you cannot mix data types within a statement.

Conversion Functions
02:18

The Logical functions are used to perform logical choices in SQL.

Logical Numeric Functions
01:43

Date and time functions are a very important category of functions and are used quite extensively. While mostly used to manipulate dates, the functions here can also manipulate time values as well.

Date and Time Functions
01:54

See how to use many different functions from different categories. You'll see how to use them singly with a value as well as with column in table with many rows of data.

Demo - Using Different Functions in SQL Server
05:01

Learn about grouping data, the need to do so and an introduction to special functions called Aggregate functions or simply Aggregates.

Working with Groups and Aggregates
02:34

The Aggregate functions, or as they're commonly known – aggregates are special functions in SQL Server. These work over groups of data rather on individual rows unlike other functions.

Aggregate Functions
02:14

See some examples of grouping and using different aggregates to see an overview of data.

Demo - Grouping and Using Different Aggregates
02:20

Get introduced to using variables in SQL Server programming and the different ways and places you can use them in.

Variables and Batches
05:58

See how to declare variables and use them in different places and create and use batches.

Demo - Using Variables and Batches
03:00

Control flow statements let you modify this behavior a little bit by giving a different direction to the statement execution.

Control Flow Statements
06:01

See how to use different control flow statements like the GOTO, IF and WHILE along with enclosing statement batches within a container.

Demo - Using Control Flow Statements
05:07

Learn about Stored Procedures – an advanced programming construct in SQL that lets you define a batch of statements that can be reused whenever you want.

Working with Stored Procedures
07:10

Show you how to create a stored procedure, execute it and then modify it to use parameters to make it even more useful.

Demo - Working with Stored Procedures
06:11

Conclusion
00:46

Quiz
3 questions
+
Writing Advanced Queries in T-SQL
11 Lectures 35:27

Understand why you may need to use them and the different types of subqueries that are possible.

Writing Advanced Queries in T - SQL
05:38

Take a look at writing subqueries and see how they work.

Using Subqueries Demo
02:49

Table Expressions are basically a part of a statement that returns a table rather than a value.
Table Expressions
04:34

Another way of using table expressions is to use a new feature called Common Table Expressions, or CTE in short.
Using Table Valued Expressions Demo
03:58

Set and Conditional Operators
04:35

See how table valued expressions can help write more complex queries easily.

Using Different Set Operators Demo
02:01

There are a number of Window functions in SQL Server.
Window Functions
03:23

See different window functions like RANK and DENSE RANK and the difference between them.

Using Window Functions Demo
03:01

User Defined Functions, commonly referred to as UDFs are custom functions you can create for your own specific use.

Creating User - Defined Functions
01:32

See how you can create a custom User Defined Function and how it can be used.

Create and Use UDF Demo
02:51

Conclusion
01:05

Quiz
3 questions
+
Data Manipulation
7 Lectures 23:48

The PIVOT operator is basically a way to take raw data and perform some analysis on it by following some rules.

Data Manipulation
03:51

See how we can use the PIVOT operator to quickly analyze data from a table

Using PIVOT to Analyze Data Demo
03:48

In a normal grouping operation using a GROUP BY you might recall that you can specify multiple grouping columns at a time.

Grouping Sets
03:57

See how grouping sets can enhance the way that you use multiple groups.

Grouping Sets Demo
03:35

Transaction is a unit of work in SQL – a batch of statements – that must complete successfully for the entire batch or not at all.

Handling Transactions
04:04

See how transactions can be used in queries for changes in the database.

Using Transactions Demo
03:46

Conclusion
00:47
+
Advanced Querying
12 Lectures 29:48

Learn about system databases and what they are used for in SQL Server.

Advanced Querying
02:22

The first and probably the most important system database is called “master”. The master database is critical as it holds information about the server itself.

Master Database
04:43

System stored procedures are like normal user defined custom stored procedures. The difference being that firstly, they're part of the master database and therefore unmodifiable.

System Stored Procedures
02:59

See the system databases and work with a couple of system stored procedures.

Work with System Stored Procedures Demo
01:36

Learn about Query Plans, one of the ways of analyzing your queries and seeing if you need to optimize them.

Query Plans
02:43

See the query plans that are used in each query you execute.

Viewing Query Plans Demo
02:16

Indexes are used to find data within a table. Think of the table index as an index within a book. If you need to search for a term within a book, having an index quickly lets you lookup the term and the places where it can be
found.

Manage Indexes
03:21

Views are yet another form of a virtual table – but where the structure is saved into the database itself.

Manage Views
01:24

See why indexes are useful and how we can create them. Also see how we can create a new View and use it.

Using Indexes and Views Demo
02:49

Windows Azure SQL Database – also called WASD – is a cloud based SQL platform. This is part of the larger
Windows Azure infrastructure services which provide a number of different capabilities on the cloud platform.

Windows Azure SQL Database
01:16

Take a quick walkthrough of setting up a new database and server in the Windows Azure cloud.

Overview of Windows Azure SQL Database Demo
03:21

Conclusion
00:58

Quiz
3 questions
1 More Section
About the Instructor
Compaq learning
4.1 Average rating
300 Reviews
2,530 Students
6 Courses
One stop to learn and succeed

With a clear motto to upgrade the skills of students from high school goers to professionals, Compaq learning started its tutoring journey in 2013. But within two years, an effort by three IT professionals that started as a blog went on to become one of the largest course developing company in the e learning industry.

We kick started our stint in the e-learning world by mentoring high school students. The passion for teaching and mentoring students gave us the confidence and courage to plunge into the e learning industry and provide various guidance for high school students to IT professionals. Until now we have trained around 15,000 students in various subjects ranging from simple mathematics to android app development. We have a group of fine instructional designers, graphic designers and SMEs who ensure that our products are original and valuable.