1. DirectScale Help Center
  2. Resources
  3. General Resources
General Resources
See more

dSQL Explained

Updated

Turn your traditional SQL experience into effective query construction on the Corporate Office platform

Overview

In order to leverage caching simply and effectively, the Pillars (Corporate Office) system was built in a query language-agnostic way. The Corporate Office SQL (dSQL) interpreter parses and translates requests to leverage tables that are completely cached, supplemented by the tables that live in the persistent data store.

Because it is query language-agnostic, there are basic SQL keywords that must be used, which are translated by the interpreter to work with the RDBMS underpinning any given Corporate Office implementation.

This document's intent is to train and translate the reader's traditional SQL experience into effective query construction on the Corporate Office platform. The reader's ability to write effective queries leveraging dSQL will improve system performance, and reduce frustration, as they query for the data they seek.

Database Structure

Tables

Tables are a set of data elements organized by columns and rows. Relational Databases consist of multiple interrelated data tables, connected through keys.

Table Keys

A Primary Key is a column that uniquely identifies each record.

A Foreign Key is a column whose values have matching values in the primary key of another table.

Tables are connected or joined by keys. Typically you join tables using the Primary Key of one and the Foreign Key of another. 

Entity Relationship Diagram (ERD)

The Entity Relationship Diagram is a visual that contains every table in the database, including what columns are included in each individual table and the keys that can be used to join tables together. The tables are able to be joined by using the matching column colors between each table. This ERD will be made available to our customers very soon. Contact support for help with understanding table relationships.

SELECT

The SELECT keyword is used to select data from a database. 

To select all columns and rows from a table, you would input: 

SELECT * FROM CRM_Distributors

This would then return all columns and rows from the table you selected.

To select specific columns from a table, you would input:

SELECT record number FROM CRM_Distributors

This would return only the record number column.

In dSQL, the SELECT word is optional when selecting all columns in a table.

E.g.:

FROM CRM_Distributors is equivalent to SELECT * FROM CRM_Distributors

WHERE

The WHERE keyword is used to return data that meets only the specified criteria. 

E.g.:

SELECT * FROM CRM_Distributors WHERE recordnumber = 2

This will return all rows where the record number is 2.

Strings and Numbers

When you are trying to narrow down your search by specific characters or words, you will use a string. To indicate that you are using a string, you will put the word or character in single quotes. For example: If you were looking to narrow down your search to include only people with the name Sam, you would specify the word Sam in single quotes, 'Sam'.  

WHERE Column_Name = 'Sam'

If you are narrowing down your search by numbers, you do not need to include it in single quotes. 

E.g.: 

WHERE Column_Name = 4

AND/OR

The AND operator is used to return data based on if ALL the conditions you specify are true.

The OR operator is used to return data based on if ANY of the conditions you specify are true.

E.g.:

SELECT * FROM Users WHERE BackOfficeId = 1 AND Role = 'Administrator'

This will return all rows where back office ID is equal to one AND role is administrator. Both conditions must be met in order to be true.

SELECT * FROM Users WHERE BackOfficeId = 1 OR Role = 'Administrator'

This will return all rows where the back office ID is equal to one, OR all rows where role is set to administrator.

ORDER

When you need to sort or order your results in ascending or descending order, you would structure your query similar to the code below.

For descending order, the code would look like this:

SELECT * FROM CRM_Distributors WHERE last_modified > '2018-06-28' ORDER last_modified DESC

For ascending order, the code would look like this:

SELECT * FROM CRM_Distributors WHERE last_modified > '2018-06-28' ORDER last_modified ASC

MIN() and MAX()

The minimum, maximum, count, average, and sum functions are all aggregate functions. Aggregate functions perform calculations on sets of data and return one value.

  • MIN() is used to return the smallest value of a column.
  • MAX() is used to return the largest value of a column.

E.g.:

SELECT MAX(Amount) Greatest Amount FROM ORD_Payments

This will return the largest number in the Amount column. The "Greatest Amount" following the MAX() function creates the alias for the column holding the maximum amount selected. The alias is a temporary name for the column.

COUNT(), AVG(), and SUM()

  • COUNT() is used to return a value or count of the number of rows that meet a certain condition.
  • AVG() is used to return the average of a column.
  • SUM() is used to sum up a column.

The syntax for all three of these functions is similar.

E.g.:

SELECT AVG(Total) Average Total Cost FROM ORD_OrderTotals

This will return the average of the Total column. The "Average Total Cost" that follows the aggregate function is the alias. This creates a temporary name for the column holding the average that was calculated.

Selecting Dates

The following example is used to select dates from a table. When you put the date into string format, you must write it as 'yyyy-mm-dd'.

E.g.:

SELECT * FROM CRM_Distributors WHERE last_modified > '2018-06-28'

LIKE

The LIKE operator is used to search for data that follows a specified pattern. The example below will return all first names that begin with 'Sa'. The % will return the rest of the characters following the specified 'Sa'. The application for this is if you are looking for people with the names Sam, Sarah, Samantha etc.

E.g.:

SELECT * FROM CRM_Distributors WHERE FirstName LIKE 'Sa%'

IN

The IN operator is used to specify multiple values to return. In the example below, this is going to return all the rows that have the record numbers 2, 3, 4, and 5.

E.g.:

SELECT * FROM CRM_Distributors WHERE recordnumber IN (2,3,4,5)

Selecting Distinct Values

The DISTINCT keyword is used to return only distinct or different values from a table.

E.g.:

SELECT DISTINCT(AssociateType) FROM  CRM_Distributors

JOIN

The table join clause is used to combine two or more tables together, based on columns that are related. This becomes useful when you are seeking information from columns that appear on multiple tables. 

  1. To join tables, you start off by writing a regular query.
  2. Select the row(s) you want and the table to pull from.
  3. Here is where things get different. You will need to put an alias (temporary name) for the table you are pulling in. Below you can see the alias for the crm_distributors table is d. 
  4. You will then use the keyword JOIN and following that you will select the other table you want to join, as well as put an alias on that. (The alias on the second table is o). 
  5. After that you will use the keyword ON, which is how the tables will be connecting to each other. You will connect the tables based on keys which are highlighted on the ERD, they will connect based on matching colors. 
  6. As you see below, you will use the alias first, then a period, followed by the key highlighted on the ERD: o.distributorid. 
  7. You will set this equal to the other key and follow the previous step in putting the alias first then followed by the key. As you run this query, it will pull in all related columns from the tables.

E.g.:

SELECT * FROM CRM_Distributors d JOIN ORD_Order o ON o.distributorid = d.recordnumber

GROUP BY

GROUP BY function is used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group results by one or multiple columns.

E.g.:

SELECT COUNT(recordnumber) Number of Each OrderType, OrderType FROM ORD_Order GROUP BY OrderType

"Number of Each OrderType" above is creating a new temporary name for the column that will display the Count of record numbers that have each order type. This is known as an alias.

Subqueries

A subquery is a SQL query nested inside a larger query.

E.g.:

SELECT * FROM ORD_Order WHERE TotalCV = (SELECT MAX(TotalCV) FROM ORD_Order)

This will return all rows from the orders table where the Total CV is equal to the maximum amount in the table. A reason behind writing this into a subquery is you have the power to return the entire row instead of just returning the single value for what the maximum is.

Other Keywords

  • NULL - A field with a NULL value is a field with no value.
  • IS - Used to select where a column or data point is null.

E.g.: 

SELECT * FROM Users WHERE BackOfficeId IS null
  • ISNOT - Used to select where a column or data point is not null.

E.g.: 

SELECT * FROM Users WHERE BackOfficeId ISNOT null
  • NOTIN - Used to select values that are not contained within the specified values.

E.g.: 

SELECT * FROM Users WHERE recordnumber NOTIN (9,12)
  • NOTLIKE - Used to select values that are not similar to the stated value.

E.g.: 

SELECT * FROM Users WHERE Username NOTLIKE 'a%'
  • < (less than)
  • > (greater than)
  • <> (Not equal to)
  • <= (Less than or equal to)
  • >= (Greater than or equal to)
  • = (Equal to)

Comments

0 comments

Please sign in to leave a comment.

Related articles