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.
Understand RDBM. RDBMS is an acronym for Relational Database Management System and is the most popular and widely used database system in the world.
SQL Server 2012 is available in 6 different editions. You can pick the one that matches what you need as well as your budget.
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.
Learn how to use SSMS to manage the Database Engine and what all you can do with it.
Explore different parts of SSMS and take a look at some of the SQL Server components that are available.
There are many different structures that are stored within a database file,
We will see how you can create and manage certain database objects using the SSMS tool.
Discuss the main points of this lesson.
Explore some of the database objects and structures in a pre created database.
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
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.
Work with a simple query in T-SQL to see what it allows us to do.
Introduces you to some T-SQL statements that allow to manage objects like databases and tables in an SQL Server platform.
You will see how we can manage different database objects in SQL Server using the CREATE, ALTER and DROP commands.
Explain constraints, what they are, the different types that are available, and why they are used.
See how to use different constraints and the effect they have on data within the table.
SELECT is an important query command.
Look at some simple uses of the SELECT statement.
The CASE extension is one that lets you change the way a value in a column is displayed in the result.
See how you can use the different CASE extensions for more advanced scenarios involving the SELECT statement.
When you store data in a table under different columns, each column is allowed to be for a single data type.
You will see how you can work with String data types in SQL Server and store different types of string data within them.
The first subcategory of numeric data types is the ones that store exact numeric values. These have further subcategories under them as well.
See how you can work with numeric data and store different numeric values in a table.
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.
Most of the advanced data types are meant for special purposes and scenarios.
Look at some of the advanced data types available in SQL Server.
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.
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.
See how to use the TOP, OFFSET and FETCH clauses for paging records.
Use the WHERE clause, different predicates and operators to filter search results.
Understand some advanced ways of filtering data in SELECT queries for different types of results.
See how to use Unicode strings in queries as well as the ANY and ALL clauses.
See how you can query from multiple tables at the same time and the way that SQL Server handles this.
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”.
See how querying works when using multiple tables simultaneously.
Look at the different types of JOIN and the differences between each and why each should be used with examples.
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.
See how using different types of JOINs show different results.
Learn how you can create advanced JOINs using certain keywords.
See how the join hints are used in queries.
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.
Learn how to add records to a table using the different forms of the INSERT statement.
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.
See how you can use the SELECT, SELECT INTO and BULK INSERT commands to add multiple records to a table in one go.
Know the UPDATE statement allows you to modify existing data in a table.
Learn how to update records in a table using the UPDATE command and specify records to update using its WHERE clause.
Know how to remove data – that is, records – from a table in SQL using the DELETE statement.
See how records can be deleted from a table using the DELETE and TRUNCATE statements.
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.
See how we synchronize data between two tables using the MERGE statement along with the 2 rules.
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.
Conversion functions are an important part of SQL Server. By default, you cannot mix data types within a statement.
The Logical functions are used to perform logical choices in SQL.
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.
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.
Learn about grouping data, the need to do so and an introduction to special functions called Aggregate functions or simply Aggregates.
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.
See some examples of grouping and using different aggregates to see an overview of data.
Get introduced to using variables in SQL Server programming and the different ways and places you can use them in.
See how to declare variables and use them in different places and create and use batches.
Control flow statements let you modify this behavior a little bit by giving a different direction to the statement execution.
See how to use different control flow statements like the GOTO, IF and WHILE along with enclosing statement batches within a container.
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.
Show you how to create a stored procedure, execute it and then modify it to use parameters to make it even more useful.
Understand why you may need to use them and the different types of subqueries that are possible.
Take a look at writing subqueries and see how they work.
See how table valued expressions can help write more complex queries easily.
See different window functions like RANK and DENSE RANK and the difference between them.
User Defined Functions, commonly referred to as UDFs are custom functions you can create for your own specific use.
See how you can create a custom User Defined Function and how it can be used.
The PIVOT operator is basically a way to take raw data and perform some analysis on it by following some rules.
See how we can use the PIVOT operator to quickly analyze data from a table
In a normal grouping operation using a GROUP BY you might recall that you can specify multiple grouping columns at a time.
See how grouping sets can enhance the way that you use multiple groups.
Transaction is a unit of work in SQL – a batch of statements – that must complete successfully for the entire batch or not at all.
See how transactions can be used in queries for changes in the database.
Learn about system databases and what they are used for in SQL Server.
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.
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.
See the system databases and work with a couple of system stored procedures.
Learn about Query Plans, one of the ways of analyzing your queries and seeing if you need to optimize them.
See the query plans that are used in each query you execute.
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
Views are yet another form of a virtual table – but where the structure is saved into the database itself.
See why indexes are useful and how we can create them. Also see how we can create a new View and use it.
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.
Take a quick walkthrough of setting up a new database and server in the Windows Azure cloud.
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.