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 Begin
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.
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—in our case, Integrated Financial Planning. Select New Query.
This will open up a side window where you can enter in text. Click Execute when your query is ready. Your results should be in the Results tab of your query window.
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];
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 filter out countries outside of the Americas:
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 wild card):
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 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];
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];
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].
Next Steps
Continue learning about SQL in our SQL Training - Basics II article.