SQL Training - Basics I

Welcome to the SQL Training series. To follow along with the series, please ensure that you have the Integrated Financial Planning Application installed into Kepion. To download the application, go to the Sample Application page. In the first article, we will review how to perform queries with the following functions: SELECT, WHERE, JOIN, UNION, and GROUP BY.

• SQL Training - Basics I
• SQL Training - Basics II
• SQL Training - Basics III

 

 

Before You Begin

We will be using SQL Server Management Studio (SSMS) for all our SQL queries.

When logging into SSMS, Make sure the following information is correct:

• Server type must be Database Engine.
• Server name can be ".", "localhost", the machine's name if Kepion is running on your personal machine. If Kepion is running in a different location, the machine's IP address is required.
• Authentication: Windows or assigned login depending on how SSMS was setup.

 

 

Once everything is entered correctly, press Connect. In the Object Explorer pane, select the expand box for Databases.

 

 

Right-click the database that you would like to query. Select New Query.

 

 

This will open up a side window where you can enter in text. Enter the Execute button when your query is ready. Your results should be on the Results tab of your query window.

 

 

 

SELECT

We can query the records in a table using a SELECT statement.

 

To select all the columns in a table:

 

SELECT * FROM [dbo].[D_Entity];

 

To select a specific column in the table:

 

SELECT [MemberName] FROM [dbo].[D_Entity];

 

Or to select multiple columns:

 

SELECT [MemberName], [Area], [Local Currency] FROM [dbo].[D_Entity];

 

Tip: Wrapping column and table names in brackets allows spaces between words and helps with readability. When specifying table names, also include the schema, which in this case is [dbo].

 

 

WHERE

We can filter the results of a query by adding a WHERE clause.

 

To see only only American entities:

 

SELECT [MemberName], [Area], [Local Currency] FROM [dbo].[D_Entity] 
WHERE [Area] = N'America';

 

We can also filter by columns not included in the query:

 

SELECT [MemberName], [Area], [Local Currency] FROM [dbo].[D_Entity] 
WHERE [MemberId] > 3;

 

We can use LIKE for pattern matching (the % symbol is used as a wildcard):

 

SELECT * FROM [dbo].[D_Entity]
WHERE [MemberLabel] LIKE N'A%';

 

And we can use IN to match a set of values:

 

SELECT * FROM [dbo].[D_Entity]
WHERE [MemberLabel] IN (N'Argentina', N'Australia');

 

Note: When writing strings, always use the N' ' notation, as this denotes that the subsequent string is in Unicode.

 

 

GROUP BY

The GROUP BY statement groups rows that have the same values together. GROUP BY often needs to be combined with functions like SUM, COUNT, or MAX when working with value columns, to determine the aggregation method.

 

Here, we could combine this with a COUNT function to list the number of countries in each area:

 

SELECT [Area], COUNT([Area]) FROM [dbo].[D_Entity] 
GROUP BY [Area];

 

 

JOIN

A JOIN combines rows from different tables, based on one or more columns. As the tables we're joining may share columns with the same name, we may need to specify which table each column belongs to by writing [table_name].[column_name].

 

After specifying a table, we can give it an alias, or temporary name, to make the statements easier to read and write. In this statement, we've given the two tables the aliases DE and HE, respectively. 

 

To join D_Entity with H_Entity_Entity along the MemberIds, we could write:

 

SELECT * FROM 
[dbo].[D_Entity] DE INNER JOIN [dbo].[H_Entity_Entity] HE
ON DE.[MemberId] = HE.[MemberId];

 

We can also give columns an alias. To display [MemberLabel] as 'Entity' we could write:

 

SELECT DE.[MemberLabel] [Entity] FROM 
[dbo].[D_Entity] DE INNER JOIN [dbo].[H_Entity_Entity] HE
ON DE.[MemberId] = HE.[MemberId];

 

Tip: Use aliases that are short and descriptive.

 

 

UNION

UNION and UNION ALL combine multiple result-sets (tables, views, queries, etc.) together, which have the same set of columns.

 

UNION returns only distinct rows:

 

SELECT * FROM [dbo].[D_Entity]
UNION
SELECT * FROM [dbo].[D_Entity]

 

UNION ALL returns all rows, including duplicates.

 

SELECT * FROM [dbo].[D_Entity]
UNION ALL
SELECT * FROM [dbo].[D_Entity]

 

Tip: Typically in Kepion, you'll need to use UNION ALL when defining SQL calculations.

 

 

Challenge

1. SELECT all columns and records from [dbo].[F_Financial Statement_CoreMG_Actual].

2. Modify the query to just show the columns [AccountID], [TimeID], [Value].

3. Modify the query by joining to [dbo].[D_Account] (the [AccountId] is related to the [MemberId] in the [D_Account] table). Include the [D_Account].[MemberLabel] with the alias [Account].

4. Modify the query by joining to [dbo].[D_Time] (the [TimeId] is related to the [MemberId] in the [D_Time] table). Include the [D_Time].[MemberLabel] with the alias [Time].

5. Modify the query to only show the columns [Account], [Time], and [Value] using the [MemberName] from the D_ tables, instead of the [MemberLabel].

6. Apply a WHERE clause to only show records from [Account] related to 'Health Insurance'.

7. Apply a GROUP BY clause to SUM records by [Account], [Time].