Submit a request

SQL Training - Basics I

Avatar
Kepion Support
  • Updated

Introduction

Structural Query Language (SQL) is a standard language for accessing and manipulating databases. SQL statements are used to perform tasks such as updating data on a database. 

 

In this first of three 'lessons,' we will review how to perform queries with the following functions: SELECT, WHERE, JOIN, UNION, and GROUP BY.

 

Note: To follow along with this guide, install the Integrated Financial Planning application to Kepion.  

 

 

Before you start

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

 

Upon opening SSMS, you will be prompted to connect to your SQL Server. Ensure the following information is correct:

 

  • Server type set to Database Engine.
  • Server name can be "." or "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 set to Windows Authentication or assigned login depending on how SSMS was set up.

 

SQLsignin.png

 

Once everything is entered correctly, select Connect.

 

In the Object Explorer pane, expand Databases.

 

folder.png

 

Right-click the database that you would like to query—in our case, Integrated Financial Planning. Select New Query.

 

Query.png

 

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

 

Results.png

 

 

SELECT

We can query the records in a table using a SELECT statement. In other words, use a SELECT statement to view data from a database table. 

 

To view all the columns in a table:

 

SELECT * FROM [dbo].[D_Entity];

 

mceclip3.png

 

To select a specific column in the table:

 

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

 

mceclip4.png

 

Or to select multiple columns:

 

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

 

mceclip5.png

 

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 filter out countries outside of the Americas:

 

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

 

mceclip6.png

 

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

 

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

 

mceclip7.png

 

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

 

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

 

mceclip8.png

 

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

 

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

 

mceclip9.png

 

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 together rows that have the same values. 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 GROUP BY statement with a COUNT function to list the number of countries in each area:

 

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

 

mceclip10.png

 

 

JOIN

JOIN statements combine 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];

 

mceclip11.png

 

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];

 

mceclip12.png

 

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]

 

mceclip13.png

 

UNION ALL returns all rows, including duplicates.

 

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

 

mceclip14.png

 

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

 

mceclip15.png

 

 

Next steps

Continue learning about SQL in our SQL Training - Basics II article. 

Related articles