Basic SQL Queries

Owned by Sergey Schiller (Unlicensed)
Last updated: Jun 11, 2022
6 min read

To create basic reports, user needs a basic understanding of how sql queries work. Today there are several SQL dialects, the documentation will detail queries in the T-SQL dialect, which is used in the MS SQL Server DBMS.

Below will be described main sql commands that are required for basic work with sql scripts - SELECT, FROM, WHERE, JOIN, GROUP BY, ORDER BY.

An excellent SQL manual with examples and the ability to try different commands is given at link.

SELECT

SELECT - this is the basic command used to select data from the database. This command is usually used with the FROM command, which specifies which table to fetch data from. It is also possible to select from several tables - this will be described in the section JOIN. The default SELECT query looks like this:

SELECT column1, column2, ...
FROM table_name;

column1, column2, ... in this query, these are the names of the fields (columns) in the table from which user wants to get data. In case user needs to select all data, use *:

SELECT * FROM table_name;

For example, the query result SELECT sku_id, article_name FROM products; there will be two columns with the SKU code and the name from the PRODUCTS table:

And the query result SELECT * FROM products; there will be all columns from the PRODUCTS table:

When creating SELECT queries, it is advisable to assign the aliases to columns (names):
SELECT column_1 SKU, column_2 STORE FROM table_name;.
In this example, SKU and STORE are aliases of columns column_1 and column_2 - they will be further used in the work with the report designer.

Learn more about SELECT and try how to work with it at link. User can learn more about the concept of aliases at link.

In addition to the standard field selection with SELECT, you can also select specific data. Following are the basic functions that can be used directly in a SELECT query.

  • SELECT DISTINCT - used to select unique values for a field. for instance:
    SELECT DISTINCT column_1 FROM table_name;.
    This query will print only unique values in column_1.
    Learn more about SELECT DISTINCT and try how to work with it at link.

  • SELECT TOP - is used to select the first N values from a field. For instance:
    SELECT TOP 100 column_1 FROM table_name;.
    This query will print only the first 100 values in column_1.
    Learn more about SELECT TOP and try how to work with it at link.

  • MIN/MAX - used to display the smallest or largest values in a column. For instance:
    SELECT MIN(column_1) FROM table_name;.
    This query will print the smallest value in column_1.
    Learn more about MIN / MAX and try how to work with it at link.

  • COUNT, AVG, SUM - COUNT () counts the number of rows in a column; AVG () displays the average of the column; SUM () - displays the sum of all values.
    SELECT COUNT(column_1) FROM table_name;.
    This query will print the number of rows in column_1.
    SELECT AVG(column_1) FROM table_name;.
    This query will print the average in column_1.
    SELECT SUM(column_1) FROM table_name;.
    This query will print the sum of all values in column_1.
    Learn more about COUNT, AVG, SUM and try how to work with it at link.

WHERE

The WHERE command is used to create a selection condition:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

For example, if you need to select all values that are greater than 10, then the WHERE clause should be indicated:
SELECT column1
FROM table_name
WHERE column1 > 10;

Also, the WHERE command has mathematical operators such as greater than, less than, not equal, and so on. They are used when setting a condition.

Learn more about WHERE, available operators and try how to work with it at link.

There can be several conditions in WHERE, for their definition the logical operators "AND", "OR", "NOT" are used.
If it is necessary that both one and the other conditions are met, AND is used.
If it is necessary that one of several conditions be met, OR is used.
If you want the data to be displayed when the condition is not met, NOT is used.

Learn more about AND, OR, NOT and try how to work with it at link.

The IN and LIKE operators are comparison operators in the WHERE clause.

  • IN allows sampling when there are multiple values, for example:
    SELECT SKU_ID, ITEM_CODE FROM PRODUCTS WHERE SKU_ID in (2,4,8)
    This query will display only those SKUs whose SKU_ID is 2, 4, or 8.
    Learn more about IN and try how to work with it at link.

  • LIKE allows user to search for a given pattern in data from a column, for example:
    SELECT SKU_ID, ARTICLE_NAME FROM PRODUCTS WHERE ARTICLE_NAME like (%Ham%)
    This query will display those SKUs with the word "Ham" in their name.
    Learn more about LIKE and try how to work with it at link.

Also in WHERE, user can work with columns that have NULL values (a field that has no value). The standard WHERE clauses do not work with NULL. You must use the IS NULL and IS NOT NULL operators. Learn more about IS NULL and IS NOT NULL and try how to work with it at link.

ORDER BY

ORDER BY command allows user to sort the data output according to certain rules.

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER_BY column1 asc/desc

Learn more about ORDER BY and try how to work with it at link.

JOIN

The JOIN command is used after the FROM command and is used to display columns in a query from different tables that are related to each other. So for example, if there is a PRODUCTS table with SKU_ID, GROUP_ID columns and there is a GROUPS table with GROUP_ID and GROUP_NAME columns, the JOIN will allow user to select SKU_ID and GROUP_NAME data from different tables, while linking them by GROUP_ID.

There are several types of JOINs:

  • (INNER) JOIN - can be used with or without the INNER prefix - displays only those records that have the same values in both tables;

  • LEFT JOIN - displays all records from the left table (as specified by FROM) and corresponding records from the right table (as specified by JOIN);

  • RIGHT JOIN - displays all records from the left table (as specified by JOIN) and corresponding records from the right table (as specified by FROM);

  • FULL JOIN - displays all records if there is a match in the left or right table;

Learn more about JOIN and try how to work with it at link.

User can study and try each type of JOIN in more detail by following the links: (INNER) JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.

GROUP BY

The GROUP BY command is used after the WHERE command and is used to group data by similarity. So, for example, user can group all products that have the same code. It is used if there are aggregating functions after SELECT in the query - such as COUNT, MAX, MIN, SUM, AVG, etc.

SELECT column1, sum(column2)
FROM table_name
WHERE condition
GROUP BY column1
ORDER_BY column1 asc/desc

A good example of using GROUP BY in case user needs to display, for example, sales for 4 weeks (from 10th to 13th week of 2019) in the context of the selected SKU for two Stores.
In the SALES_SKU table, sales are broken down by week, we need to sum the sales for 4 weeks to get the sales for 4 weeks. For such a case, the request will look like this:

SELECT sku_id, store_id, sum(sales_volume) vol
FROM sales_sku
WHERE sku_id = 93 and week between 201910 and 201913 and store_id in (1,2)
GROUP BY sku_id, store_id
ORDER_BY vol asc

The result of executing such a query is as follows:

It is important that GROUP BY must be specified not only by SKU_ID, but also by STORE_ID, since these two fields are not used in the aggregation function. And if user select only GROUP BY SKU_ID, then the system will give an error that STORE_ID is not applied neither in GROUP BY nor in any other aggregation function.

Learn more about GROUP_BY and try how to work with it at link.