Content
You’ll also see how to use the results of aggregation functions to create new calculations and how to deal with Nulls in the columns you’re aggregating. This video explains the basics of adding calculations to an SQL query for Excel. You’ll learn how to add columns to the Select list and assign aliases to them. You’ll learn the basic arithmetic operators, what BODMAS and PEDMAS are and how to control the order of evaluation in a complex expression. You’ll see how to add calculations to the Where and Order By clauses.
You’ll learn how to create a connection object and construct a connection string to talk to the closed Excel file. You’ll learn how to query the schema of the workbook to return a list of the worksheets and loop through SQL Server 2014 Lessons this list to read the name of each sheet. As a bonus you’ll learn about the SQL Union All statement to select data from multiple tables at the same time and how to add criteria to a query using the Where clause.
Module 11: Performing Ongoing Database Maintenance
This video explains how to write inner joins in your SQL queries for Excel files to look up related information in different worksheets. You’ll learn a bit about relational database design including what primary keys, foreign keys and one-to-many relationships are. You’ll see how to join two tables using an inner join to match rows based on a common value.
- Keep track of your employees’ progression and performance in your own workspace.
- You’ll also learn how to create a recordset object and populate it with data using an SQL Select statement.
- The increased functionality will streamline your ETL processes and smooth out your workflow, but the catch is that your workflow must change.
- It provides knowledge and skills for using SQL Server products and tools, as well as features new to SQL Server 2014 in particular.
New tools come with new best practices, and Professional Microsoft SQL Server 2014 Integration Services will keep you ahead of the curve. SQL Server MVP Brian Knight is the most respected name in the business, and your ultimate guide to navigating the changes to use Microsoft SQL Server Integration Services 2014 to your utmost advantage. You need to know it inside out, and you must understand the full potential of its capabilities in order to use it effectively. Professional Microsoft SQL Server 2014 Integration Services is your roadmap to understanding SSIS on a fundamental level, and setting yourself up for success. SQL Server replication enables you to copy and distribute data and database objects to other computers and locations in your enterprise, which can improve availability and scalability. This module provides an overview of SQL Server replication and explains the agents used to implement replication.
Lab: Importing and Exporting Data
You’ll learn about the IN clause and how to reference other workbooks, using a derived table subquery which can be joined to other tables in a query. Learn how to use IIf and Switch functions to test conditions and produce different results depending on the answer. You’ll see how to create calculated columns using the IIf and Switch functions, as well as how to use the functions to control the sort order of your query results. You’ll learn how to use nested IIf and Switch functions and how to write more complex logical tests using the AND, OR, IN and BETWEEN operators. Learn how to select the top rows from an Excel table an SQL query. You’ll learn how to select a specific number of rows from the top of a list and how to use the Order By clause to influence which rows appear at the top. You’ll see how to deal with tied rows by including a tie-breaker field and how to select a percentage of the rows from a table.
Describe how to use SQL Server AlwaysOn Failover Cluster Instances to maintain high availability for SQL Server instances. Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.
Lab: Performing Ongoing Database Maintenance
Our courses can be adapted to meet your individual project or business requirements regardless of scope. Write correlated subqueries which return scalar or multi-valued results. Use the HAVING clause to filter out groups based on a search condition. Basic knowledge of the Microsoft Windows operating system and its core functionality.
Finally, you’ll learn how to use the Is Null and Is Not Null operators to find or ignore rows with missing values. This video demonstrates how to use the IN clause (not to be confused with the IN operator!) in an ADODB SQL query to select data from multiple files in the same query. You’ll learn how to union tables from multiple Excel workbooks, both with and without header rows. Learn how to write expressions to manipulate text in your SQL queries for Excel files.
