First Steps

Our first step is to log into Insights. If you are having difficulty doing this you will probably need to put in a ticket with your I.T. department.

Once you have logged in, you should see the basic Insights page. On the left is a file tree with folders for holding queries. There may be a full list of folders or there may only be a few depending on how long your school has been using Insights and what folders you have permission to view.

Right now, the important folder is the "Your personal collection" link. If you click on the link, the right side of Insights will change to display your personal folders and queries. This is where you will be saving your work so you may want to take a moment to think about how you want to organize your folders.

Your First Query

In the upper right corner of Insights is a "New" button. If you press it you should get dropdown giving you the option to create a new report, SQL query, dashboard, or document. You want to choose a new SQL query. If that option is not available you probably do not have authorization to create an SQL query. You will need to contact your I.T. or management for the proper permissions.

If there is no problem, Insights will display an SQL editor. You will be doing most of your work in the code textbox that is displaying the prompt "SELECT * FROM TABLE_NAME".

SQL is a written language that allows data to be extracted from a database. It was created as a standardized way to extract data from a database. Our first query is just going to display some text.

The core of SQL statements are SELECT statements. In the Insights SQL text box enter the following code.

SELECT 'Hello, world!';

This statement starts with the word SELECT, hence the name select statement. The phrase 'Hello, world!' is enclosed in single quotes (or apostrophes). This tells Insights it is a string literal. String literals are not code that Insights should try to understand. It is a word or phrase that Insights should just accept.

The semicolon at the end of the query is the standard way of ending an SQL statement. In Insights it is not necessary. In many database applications you can chain several commands together if you end each one with a semicolon. But Insights will only execute the first statement.

To execute the code, press the Run Query button. Insights will output the words 'Hello, world!' in the output pane on the bottom of the screen. Congratulations! You have made your first query.

Your Second Query

Let's do something more substantive. We are going to list the financial aid funds. Those funds are in the rfrbase table. Replace the previous SQL with the code below.

SELECT * FROM rfrbase;

When you run this code the Insights output frame will display all of the financial aid funds.

A select statement starts with the word "SELECT" and then a list of the columns in the table we want to display. We want to select all of the columns so we use an asterisk (or star, *). This is a very common abbreviation for "all columns". After the columns we need the keyword FROM to tell Insights that now we will specify the table name. And then, of course, we use rfrbase as the table.

Insights is case insensitive. You can use either upper case or lower case letters. Traditionally, keywords are in upper case but this is just a convention. This gives you the ability to use upper and lower case to make your code more readable to us poor humans.

Note, other databases may define tables and columns as being case sensitive. In those cases "RFRBASE", "Rfrbase", and "rfrbase" would be different tables.

Now, let's let Insights create the same query for us. In the Insights search box enter "rfrbase". After a moment of thinking, Insights will display an entry for RFRBASE that tells us it is the Fund Base Data Table. Click on the "Rfrbase" link. Insights will display the table's data.

Press the Editor button in the upper right corner. Insights switches into graphical editing mode. On the right it will display the SQL code the graphical editor has created. Switch to full SQL mode by pressing the "Convert this report to SQL" button in the lower right corner. And let's take a look at the SQL Insights created.

SELECT
  "faismgr"."rfrbase"."rfrbase_fund_code" AS "rfrbase_fund_code",
  "faismgr"."rfrbase"."rfrbase_fund_title" AS "rfrbase_fund_title",
  "faismgr"."rfrbase"."rfrbase_fsrc_code" AS "rfrbase_fsrc_code",
  "faismgr"."rfrbase"."rfrbase_ftyp_code" AS "rfrbase_ftyp_code",
  "faismgr"."rfrbase"."rfrbase_activity_date" AS "rfrbase_activity_date",
  "faismgr"."rfrbase"."rfrbase_detail_code" AS "rfrbase_detail_code",
  "faismgr"."rfrbase"."rfrbase_fed_fund_id" AS "rfrbase_fed_fund_id",
  "faismgr"."rfrbase"."rfrbase_print_seq_no" AS "rfrbase_print_seq_no",
  "faismgr"."rfrbase"."rfrbase_vr_msg_no" AS "rfrbase_vr_msg_no",
  "faismgr"."rfrbase"."rfrbase_active_ind" AS "rfrbase_active_ind",
  "faismgr"."rfrbase"."rfrbase_xref_value" AS "rfrbase_xref_value",
  "faismgr"."rfrbase"."rfrbase_fund_title_long" AS "rfrbase_fund_title_long",
  "faismgr"."rfrbase"."rfrbase_user_id" AS "rfrbase_user_id",
  "faismgr"."rfrbase"."rfrbase_info_access_ind" AS "rfrbase_info_access_ind",
  "faismgr"."rfrbase"."rfrbase_surrogate_id" AS "rfrbase_surrogate_id",
  "faismgr"."rfrbase"."rfrbase_version" AS "rfrbase_version",
  "faismgr"."rfrbase"."rfrbase_data_origin" AS "rfrbase_data_origin",
  "faismgr"."rfrbase"."rfrbase_vpdi_code" AS "rfrbase_vpdi_code",
  "faismgr"."rfrbase"."rfrbase_guid" AS "rfrbase_guid",
  "faismgr"."rfrbase"."time_stamp" AS "time_stamp",
  "faismgr"."rfrbase"."tenant_id" AS "tenant_id",
  "faismgr"."rfrbase"."datasource_id" AS "datasource_id",
  "faismgr"."rfrbase"."dml_type" AS "dml_type",
  "faismgr"."rfrbase"."tenant_key" AS "tenant_key",
  "faismgr"."rfrbase"."last_updated_date" AS "last_updated_date"
FROM
  "faismgr"."rfrbase"
LIMIT
  1048575

Wow! That sure is a lot more code. Let's go through it step by step and break it down. Don't worry, you will see it is basically the same as our tiny little thing we wrote.

First is the SELECT keyword. No problem there. We already know this is how you usually start a query like this.

Next, instead of just using an asterisk to say "everything", Insights lists out each column. But it also renames each column. Notice how each column listing ends with the keyword AS followed by the name of the column. This is a column alias. It allows you to rename columns. So column "rfrbase_fund_code" can be renamed simply "fund".

But Insights is just renaming the column with the same name. We will use column aliases later to make the output pretty for the end user. But for now, lets just get rid of the clutter.

SELECT
  "faismgr"."rfrbase"."rfrbase_fund_code",
  "faismgr"."rfrbase"."rfrbase_fund_title",
  "faismgr"."rfrbase"."rfrbase_fsrc_code",
  "faismgr"."rfrbase"."rfrbase_ftyp_code",
  "faismgr"."rfrbase"."rfrbase_activity_date",
  "faismgr"."rfrbase"."rfrbase_detail_code",
  "faismgr"."rfrbase"."rfrbase_fed_fund_id",
  "faismgr"."rfrbase"."rfrbase_print_seq_no",
  "faismgr"."rfrbase"."rfrbase_vr_msg_no",
  "faismgr"."rfrbase"."rfrbase_active_ind",
  "faismgr"."rfrbase"."rfrbase_xref_value",
  "faismgr"."rfrbase"."rfrbase_fund_title_long",
  "faismgr"."rfrbase"."rfrbase_user_id",
  "faismgr"."rfrbase"."rfrbase_info_access_ind",
  "faismgr"."rfrbase"."rfrbase_surrogate_id",
  "faismgr"."rfrbase"."rfrbase_version",
  "faismgr"."rfrbase"."rfrbase_data_origin",
  "faismgr"."rfrbase"."rfrbase_vpdi_code",
  "faismgr"."rfrbase"."rfrbase_guid",
  "faismgr"."rfrbase"."time_stamp",
  "faismgr"."rfrbase"."tenant_id",
  "faismgr"."rfrbase"."datasource_id",
  "faismgr"."rfrbase"."dml_type",
  "faismgr"."rfrbase"."tenant_key",
  "faismgr"."rfrbase"."last_updated_date"
FROM
  "faismgr"."rfrbase"
LIMIT
  1048575

That's better. But what is up with all of the double quotes? Database objects such as tables and columns are enclosed in double quotes to explicitly tell the database they are the name of a database table or column. If a name has a space in it the double quotes are required. But none of these database objects have a space character. So we can get rid of them.

SELECT
  faismgr.rfrbase.rfrbase_fund_code,
  faismgr.rfrbase.rfrbase_fund_title,
  faismgr.rfrbase.rfrbase_fsrc_code,
  faismgr.rfrbase.rfrbase_ftyp_code,
  faismgr.rfrbase.rfrbase_activity_date,
  faismgr.rfrbase.rfrbase_detail_code,
  faismgr.rfrbase.rfrbase_fed_fund_id,
  faismgr.rfrbase.rfrbase_print_seq_no,
  faismgr.rfrbase.rfrbase_vr_msg_no,
  faismgr.rfrbase.rfrbase_active_ind,
  faismgr.rfrbase.rfrbase_xref_value,
  faismgr.rfrbase.rfrbase_fund_title_long,
  faismgr.rfrbase.rfrbase_user_id,
  faismgr.rfrbase.rfrbase_info_access_ind,
  faismgr.rfrbase.rfrbase_surrogate_id,
  faismgr.rfrbase.rfrbase_version,
  faismgr.rfrbase.rfrbase_data_origin,
  faismgr.rfrbase.rfrbase_vpdi_code,
  faismgr.rfrbase.rfrbase_guid,
  faismgr.rfrbase.time_stamp,
  faismgr.rfrbase.tenant_id,
  faismgr.rfrbase.datasource_id,
  faismgr.rfrbase.dml_type,
  faismgr.rfrbase.tenant_key,
  faismgr.rfrbase.last_updated_date
FROM
  faismgr.rfrbase
LIMIT
  1048575

We have gotten rid of a lot of visual clutter. But why is each column name prefixed with "faismgr"."rfrbase".? Databases are organized into separate folders similar to the way a file in a filesystem is inside a folder inside of another folder. The way tables are arrainged is part of the database schema.

In the Insights schema all of the financial aid tables are in the "faismagr" folder. We can describe the path like we do for files in a filesystem by replacing file backslashes to dots. So the rfrbase_fund_code column inside of the rfrbase table inside of the faismgr database is "faismgr.rfrbase.rfrbase_fund_code". Insights does not need this much specificity so let's get rid of the prefix.

SELECT
  rfrbase_fund_code,
  rfrbase_fund_title,
  rfrbase_fsrc_code,
  rfrbase_ftyp_code,
  rfrbase_activity_date,
  rfrbase_detail_code,
  rfrbase_fed_fund_id,
  rfrbase_print_seq_no,
  rfrbase_vr_msg_no,
  rfrbase_active_ind,
  rfrbase_xref_value,
  rfrbase_fund_title_long,
  rfrbase_user_id,
  rfrbase_info_access_ind,
  rfrbase_surrogate_id,
  rfrbase_version,
  rfrbase_data_origin,
  rfrbase_vpdi_code,
  rfrbase_guid,
  time_stamp,
  tenant_id,
  datasource_id,
  dml_type,
  tenant_key,
  last_updated_date
FROM
  faismgr.rfrbase
LIMIT
  1048575

Now we have a listing of the columns with a comma at the end of each column name except the last one. The commas are used to separate the column names. SQL does not care if the column names are on the same line or not. We stack the columns to make them easier for a human to read. The computer doesn't care. The following code is just as valid just harder to read.

SELECT rfrbase_fund_code, rfrbase_fund_title, rfrbase_fsrc_code,
rfrbase_ftyp_code, rfrbase_activity_date, rfrbase_detail_code,
rfrbase_fed_fund_id, rfrbase_print_seq_no, rfrbase_vr_msg_no,
rfrbase_active_ind, rfrbase_xref_value, rfrbase_fund_title_long, rfrbase_user_id,
rfrbase_info_access_ind, rfrbase_surrogate_id, rfrbase_version,
rfrbase_data_origin, rfrbase_vpdi_code, rfrbase_guid, time_stamp,
tenant_id, datasource_id, dml_type, tenant_key, last_updated_date
FROM faismgr.rfrbase LIMIT 1048575

After the column listing is the keyword FROM and the name of the table where the columns live. In many databases the database must be specified or it will not be able to find the table. Insights knows where to find the data so instead of FROM "faismgr"."rfrbase" we can just use FROM rfrbase.

The final statement is a LIMIT statement. This tells Insights how many records to retrieve. If we change it to LIMIT 10 it will only retrieve the first 10 records. This is almost completely optional so we can remove it. But remember this statement. When you are developing your SQL you might want to temporarily add a LIMIT statement to keep your query from taking an excessive time to run.

We can clean up the whitespace in the code to make it readable and we have our final code. Start a new SQL query in Insights, copy the code, and paste it into the Insights SQL editor. Press the run query button and you will see it creates the same output as SELECT * FROM rfrbase.

SELECT
    rfrbase_fund_code,
    rfrbase_fund_title,
    rfrbase_fsrc_code,
    rfrbase_ftyp_code,
    rfrbase_activity_date,
    rfrbase_detail_code,
    rfrbase_fed_fund_id,
    rfrbase_print_seq_no,
    rfrbase_vr_msg_no,
    rfrbase_active_ind,
    rfrbase_xref_value,
    rfrbase_fund_title_long,
    rfrbase_user_id,
    rfrbase_info_access_ind,
    rfrbase_surrogate_id,
    rfrbase_version,
    rfrbase_data_origin,
    rfrbase_vpdi_code,
    rfrbase_guid,
    time_stamp,
    tenant_id,
    datasource_id,
    dml_type,
    tenant_key,
    last_updated_date
FROM rfrbase