Introduction.

Query language (QL) is a computer programming language that is used to request and retrieve data from the database and information systems via queries. The terms Query language, data query language, database query language are used interchangeably referring to the same notions. One of the most  broadly used query languages is Structured Query Language (SQL) the basic syntax of which is shown below in Figure 1:

Spreadsheets are widely used for computation, organization, analysis and storage of data in tabular form. Some of the popular spreadsheet tools include Microsoft Excel (online and desktop), LibreOffice Calc (online and desktop) and Google sheets (online).

Google sheets is a spreadsheet alternative by Google. It originated from XL2Web, a web-based spreadsheet application developed by 2Web Technologies company which was acquired by Google in 2006. It allows users to upload spreadsheets saved as using as spreadsheet formats such as Excel, Calc and work on them. It is also possible to save the original the documents using xlsx, ods and other file extensions. One of the most powerful formula in Google Sheets is Query function which can be used to solve both basic problems and more complex data analysis problems.

The syntax of queries

The syntax of queries is very similar to SQL syntax. But, unlike SQL the syntax it is limited and some advanced features of SQL such as  JOINS are not present. In order to retrieve data, clauses are used. The clauses have different usages as described in Table 1.

Table 1.

The clauses are optional. But if you decide to use many of them together the order of the clauses must be followed as described in Figure 2:

Figure 2.

Clauses.

Let’s explore the clauses in detail. Figure 3 is the screenshot of sample sheet is populated with the data that will be manipulated. Also, link to the actual spreadsheet is provided in bibliography list.

Figure 3. Sample data.

Select.

As mentioned above all clauses are optional. Whole data in the sheet can be retrieved either by simply omitting the SELECT clause (Figure 4) or using SELECT with asterisk symbol (Figure 5).

Figure 4. Omitting SELECT CLAUSE.

Figure 5. SELECT clause with "*".

With these queries the first table is basically copied. The output will be the same table with the same values in it. We can also grab data from other sheets. In order to get data from another sheet the name of sheet must be mentioned before the range. We can also retrieve specific columns that we want using the syntax shown in Figure 6.

Figure 6. Retrieving column in desired order.

Note that the order of columns is arbitrary meaning that you can retrieve them the order you want them to appear as an output (Figure 7).  Also, in this example, the data was retrieved from the first sheet of workbook.

Figure 7. The output of the recent query.

Where.

Next clause of of Query function is WHERE clause which returns only rows that match a condition. If omitted, all rows are returned. And if there is a need to use SELECT clause, WHERE clause must follow the SELECT clause. In this case the syntax will be the same as described I in Figure 8.

Figure 8. Retrieving data from another sheet that meets a specific condition.

In this example, the order which has the total value of 189.05 was retrieved. WHERE clause can also be used with comparison operators (less than or equals to <=, less than <, greater than >, greater than or equals to >=, equals to =, not equals to != or <>), logical operators (and, or, not) and reserved words such as contains, starts with, ends with, matches and like. Note that string matching is case sensitive, so upper() and lower() functions can be used. In order to compare null,  is null and is not null are used. In the first example, the query is used to retrieve the values where the value of total sales is greater than 1000. As seen from the output (Figure 9) there is only one case where this condition is true.

Figure 9.  WHERE clause with comparison operator “>”. (Data was sorted in order to easily compare the input and output. ).

In the second example, sales from only the region East were chosen. The result is limited with the Unit value to be less than 30 (Figure 10).

Figure 10.  WHERE clause with comparison operator “=” and logical operator “and”.

In the third example, all the data in column D retrieved which have the letter “P” either at the beginning or  end. The arguments don’t need to one character long. Longer strings can also be used in this case (Figure 11).

Figure 11. WHERE clause with the reserved word “contains”.

Upper() and lower() methods turn given parameter letter into uppercase and lowercase letters respectively and retrieve values that meet that condition. Note that upper() lower() functions inside QUERY function can take only one character string arguments unlike regular upper() and lower() functions that shown in Figure 12.

Figure 12. Upper function used in Google Sheets cell separately.

The upper and lower functions used inside QUERY function are enclosed in single quotes, not in double quotes. As seen the output shown in Figure 13 the function converted lowercase “p” given as as a parameter and return values which contains uppercase “P”.

Figure 13. Where clause, “contains” and upper() method.

THE END OF PART 1. TO BE CONTINUED.

Bibliography.

1. Sample data: https://www.contextures.com/xlsampledata01.html#download (accessed: 27/03/2022) 
2. https://www.techopedia.com/definition/3948/query-language (accessed: 27/03/2022)
3. https://en.wikipedia.org/wiki/Query_language#:~:text=Query%20languages%2C%20data/%20query%20languages,Structured%20Query%20Language%20(SQL (accessed: 27/03/2022)
4. https://en.wikipedia.org/wiki/Google_Sheets (accessed: 28/03/2022)
5. https://carbon.now.sh/?bg=rgba%28171%2C+184%2C+195%2C+1%29&t=seti&wt=none&l=sql&width=337&ds=true&dsyoff=20px&dsblur=68px&wc=true&wa=false&pv=56px&ph=56px&ln=false&fl=1&fm=Hack&fs=14px&lh=133%25&si=false&es=2x&wm=false&code=SELECT%2520*%250AFROM%2520tablename%253B (28/03/2022)
6. https://en.wikipedia.org/wiki/Spreadsheet#History (accessed: 28/03/2022)
7. https://www.youtube.com/watch?v=oCKvAcXTLZo (accessed: 28/03/2022)
8. https://developers.google.com/chart/interactive/docs/querylanguage (accessed: 28/03/2022)