INTRODUCTION
If you are familiar with SQL SERVER application, you must be knowing the meaning of the Query word.
Queries are mostly concerned with the Databases.
If we try to define a query, it can be simply defined as in the following statement.
QUERY IN A DATABASE IS A STATEMENT, WHICH RESEMBLES ENGLISH LANGUAGE, QUERIES OR ASKS FOR CERTAIN ACTION ON THE DATABASE AND PERFORMS THE ACTION.
Database is a big table which accommodates data of any length and offer great scalability and tools for managing the data easily.
The capability can be understood by just imagining that a complete article of the dynamic websites resides in a single field of a database. [ Some more information is given in the next section ]
On the same lines GOOGLE has developed Google Visualization API Query Language.
This query language has much similarity with the SQL server query language.
This language can be used in our GOOGLE SHEETS with the help of a function named QUERY which simplifies the fetching of data in multiple ways from our tables.
So, in this article, we’ll learn the basics of this Google VISUALIZATION API QUERY LANGUAGE and will learn the use of this language with the help of various practical examples.
WHAT IS THE PURPOSE OF GOOGLE QUERY LANGUAGE ?
GOOGLE QUERY LANGUAGE or GOOGLE VISUALIZATION API QUERY LANGUAGE or simply the GOOGLE QUERY LANGUAGE helps us to manipulate the data from the DATA SOURCE with the help of a query.
The Google Query language is much beneficial for getting the data ready for the Visualizations i.e. the charts. Hence the word Visualization used in its proper name.
The charts needs the data to be in a specific format such as some charts need the data to be in two columns.
One with the field names and other with the values.
Or a combo chart will need the data with three columns and so on.
The data source is not going to be optimized or formatted as per the requirement.
In such cases we can always use the Query to get the data for the visualization purpose which is very helpful.
The other way to do so is by getting the table and editing it manually whereas the Query will perform the task in a very small time and without much ado.
We’ll see that with the help of the examples.
THE CLAUSES [ STATEMENTS ] IN GOOGLE QUERY LANGUAGE
The following list shows the standard CLAUSES in GOOGLE QUERY LANGUAGE.
These are the simple clauses which will make our queries for the simple but powerful operations.
- SELECT
- WHERE
- GROUP BY
- PIVOT
- ORDER BY
- LIMIT
- OFFSET
- LABEL
- FORMAT
- OPTIONS
DATA MANIPULATION FUNCTIONS
IN ADDITION TO THIS, WE HAVE DATA MANIPULATION FUNCTIONS. THESE FUNCTIONS HELP IN THE PROCESS OF MANIPULATING THE DATA SUCH AS SUMMING UP THE VALUES, AVERAGING, USING DIRECT OPERATORS, USING YEAR, TIME FUNCTIONS ETC.
- AGGREGATION FUNCTIONS
- SCALAR FUNCTION
- ARITHMETIC OPERATORS
LANGUAGE ELEMENTS-
THE ELEMENTS WHICH WILL BE USED WHILE CREATING THE QUERIES SUCH AS STRINGS, BOOLEAN, NUMBERS ETC.
- LITERALS
- IDENTIFIERS
- CASE SENSITIVITY
- RESERVED WORDS
So, these were the basic clauses of the Google Query Language which we can use to create our queries and retrieve the data.
EVERY CLAUSE OR KEYWORD IS DESCRIBED IN DETAILS IN SEPARATE ARTICLES
Let us take a few examples to learn a bit about the queries.
We’ll be using the queries on our data in the Google Sheets using the Query function.
QUERY FUNCTION
A brief reference of the SYNTAX FORMULA of QUERY FUNCTION is
=QUERY( DATA ON WHICH QUERY WILL BE APPLIED, QUERY STATEMENT, NUMBER OF HEADER ROWS ABOVE THE DATA OR -1 FOR AUTO DETECTION )
CLICK HERE FOR MORE DETAILS.
EXAMPLE DATA:
We have created a sample table with raw data and we’ll be using Queries to extract the data from this table.
The table is shown below.
| EMP_ID | EMP_NAME | AGE | EXPERIENCE | FIELD |
| 156 | ALBERT | 30 | 2 | RESEARCH |
| 266 | DOMINIC | 24 | 9 | HR |
| 359 | PRADEEP | 25 | 6 | OPERATIONS |
| 458 | ASHIMA | 30 | 3 | SALES |
| 460 | DANIEL | 25 | 6 | HR |
| 523 | DANNY | 25 | 6 | OPERATIONS |
| 541 | SAMMY | 23 | 7 | OPERATIONS |
| 568 | JORDAN | 23 | 5 | SALES |
| 602 | CLAY | 24 | 3 | SALES |
| 717 | KIMS | 25 | 10 | OPERATIONS |
| 805 | LESLEY | 26 | 10 | RESEARCH |
| 885 | KIM | 28 | 6 | RESEARCH |
| 949 | BRIAN | 27 | 7 | OPERATIONS |
EXAMPLE 1: EXTRACT ALL THE EMPLOYEES LIST FROM THE GIVEN TABLE.
SOLUTION:
We can get all the data with the help of the query which we are going to use using the QUERY FUNCTION.
FOLLOW THE STEPS TO GET ANY COLUMN FROM THE TABLE
- Simply click on the cell where you want the result.
ALWAYS TAKE CARE THAT YOU HAVE ENOUGH AREA [ ROWS AND COLUMNS ] FOR THE RESULT OF THE QUERY OTHERWISE IT'LL WRITE OVER THE EXISTING DATA.
- Enter the function as =QUERY( DATA TABLE RANGE, “SELECT COLUMN NAME”,-1 OR NOTHING OR HEADER ROWS COUNT )
- For our example, the function becomes =QUERY(B6:F18,”SELECT C”,-1)
- As soon as we are done entering the function, press enter the complete column will populate.
- You can see how easy it becomes to handle the data with a single function.
EXAMPLE 2: EXTRACT ALL THE EMPLOYEES HAVING THE AGE MORE THAN 30 YEARS
SOLUTION:
We can solve this problem with the help of filter function or by simply putting a query.
In this example, we’ll make use of the QUERY and extract the relevant data.
FOLLOW THE STEPS TO GET THE EMPLOYEES WITH THE AGE MORE THAN 25 YEARS
- Simply click on the cell where you want the result.
- Enter the function as =QUERY( DATA TABLE RANGE, “SELECT EMP NAME WHERE AGE > 25”,-1 OR NOTHING OR HEADER ROWS COUNT )
- For our example, the function becomes =QUERY(B6:F18,”SELECT C WHERE D>25″,-1)
- As soon as we are done entering the function, press enter the complete column will populate.
- You can see how easy it becomes to handle the data with a single function.
CAN WE PERFORM THE SAME ACTIONS WITH THE FUNCTIONS WHICH WE PERFORMED WITH THE QUERIES? Yes, absolutely we can! For example, in the sample problems discussed above, we can do the same with the help of Filter function but using the Query is really simple. In the example, you can see the effectiveness of the query. There is no problem while phrasing the Query too. It is pretty simple. Secondly, you need to not to take any care of the cell addresses or so. The Queries help you to extract the useful data from a big data within fractions of seconds.
This was just a demonstration of the power of the QUERY LANGUAGE.
There is a complete list of articles showing you the usage of all the clauses of QUERY LANGUAGE.