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

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

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.