====== Learning Tabulify - Step 7 - How to execute a Query ======

===== Query =====
For ''Tabulify'', a  [[docs:resource:query|query]] is a [[docs:resource:script|script]] that when executed returns a [[docs:resource:content|content resource]].

In the case of a [[docs:resource:select|Sql Query (ie a SELECT statement)]], the result is a ''data set''  in [[docs:resource:tabular|tabular format]].

The [[docs:resource:selector|selector]] of a [[docs:resource:query|query]] is known as a [[docs:resource:script_selector|script selector]]. This ''selector'' is composed of a [[docs:resource:data_selector|data selector]] inside its expression.

The syntax is:
<code bash>
(dataSelector)@connection
</code>
where:
  * ''dataSelector'' is a [[docs:resource:data_selector|data selector]] that locates a [[docs:resource:resource|data resource]] that contains the [[docs:resource:query|query]] as [[docs:resource:content|data]]
  * ''@connection'' defined the [[docs:connection:connection|connection]] where the [[docs:resource:query|query]] will run.

In the next steps, we will show this concepts by running a query of the [[docs:system:tpcds|Tpcds]] benchmark.

===== Query 11 =====

[[docs:system:tpcds|Tpcds]] offers 100 queries that are all located in the [[docs:connection:tpcds_query|tpcds-queries]] connection


We will execute the ''query 11'' that lists the customers that have generated growing sales from one year to another in the ''s'' and ''w'' sales type.


Below is the 20 first lines shown with the [[docs:tabli:data:head|head command]].
<unit>
<code bash>
tabli data head --limit 20 sqlite/query_11.sql@tpcds_query
</code>
<console sql>
The first 20 rows of the data resource (sqlite\query_11.sql@tpcds_query):

with year_total as (
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total
       ,'s' sale_type
 from customer
     ,store_sales
     ,date_dim
 where c_customer_sk = ss_customer_sk
   and ss_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
</console>
</unit>
  * To execute the ''query 11'', we just need to wrap the previous selector and use it in any other command. The query data resource will be then:
<code bash>
(sqlite/query_11.sql@tpcds_query)@connection
</code>
===== Printing the query =====

[[docs:tabli:data:print|Printing]] the query against [[docs:connection:howtos|the sqlite connection]] will show us the customers that have generated growing sales from one year to another in the ''s'' and ''w'' sales type.

<unit>
<code bash>
tabli data print (sqlite/query_11.sql@tpcds_query)@sqlite
</code>
<console>
customer_id        customer_first_name   customer_last_name   customer_email_address
----------------   -------------------   ------------------   ----------------------------------
AAAAAAAABMDAAAAA   Jose                  Guzman               Jose.Guzman@stJp.edu
AAAAAAAAFLAAAAAA   Joseph                Riley                Joseph.Riley@znxF.com
AAAAAAAAJGBAAAAA   Bonnie                Harrison             Bonnie.Harrison@AKYSOPlHEYkd.com
AAAAAAAAKMDAAAAA   Alexander             White                Alexander.White@7v2XsptkFrc50K.org
AAAAAAAAOFCAAAAA   Timothy               Grogan               Timothy.Grogan@pn3NH2BDng.org
</console>
</unit>

===== A query is just a data resource =====

<note important>
Don't forget that a [[docs:resource:query|query]] is just a [[docs:resource:resource|data resource]]. It can therefore be used on every command as any other [[docs:resource:resource|data resource]].
</note>

==== How to copy a query into another table ====

For instance, if you want to [[docs:tabli:data:copy|copy]] its output to a [[docs:resource:table|table]] called ''query_11'', you would issue the following command:
<unit>
<code bash>
tabli data copy (sqlite/query_11.sql@tpcds_query)@sqlite  query_11@sqlite
</code>
</unit>
And the content of the new [[docs:resource:table|table]] is just the same than the output  of the ''query 11''.
<unit>
<code bash>
tabli data print query_11@sqlite
</code>
<console>
customer_id        customer_first_name   customer_last_name   customer_email_address
----------------   -------------------   ------------------   ----------------------------------
AAAAAAAABMDAAAAA   Jose                  Guzman               Jose.Guzman@stJp.edu
AAAAAAAAFLAAAAAA   Joseph                Riley                Joseph.Riley@znxF.com
AAAAAAAAJGBAAAAA   Bonnie                Harrison             Bonnie.Harrison@AKYSOPlHEYkd.com
AAAAAAAAKMDAAAAA   Alexander             White                Alexander.White@7v2XsptkFrc50K.org
AAAAAAAAOFCAAAAA   Timothy               Grogan               Timothy.Grogan@pn3NH2BDng.org
</console>
</unit>

==== How to download a query as a CSV ====

For instance, if you want to [[docs:tabli:data:copy|copy]] its output:
  * to a [[:docs:resource:csv|CSV]] called ''query_11.csv''
  * into the [[:docs:connection:cd|current directory (cd)]]
you would issue the following command
<unit>
<code bash>
tabli data copy (sqlite/query_11.sql@tpcds_query)@sqlite  query_11.csv@cd
</code>
</unit>
And the content of the new csv is also just the same than the output  of the ''query 11''.
<unit>
<code bash>
tabli data print query_11.csv@cd
</code>
<console>
customer_id        customer_first_name   customer_last_name   customer_email_address
----------------   -------------------   ------------------   ----------------------------------
AAAAAAAABMDAAAAA   Jose                  Guzman               Jose.Guzman@stJp.edu
AAAAAAAAFLAAAAAA   Joseph                Riley                Joseph.Riley@znxF.com
AAAAAAAAJGBAAAAA   Bonnie                Harrison             Bonnie.Harrison@AKYSOPlHEYkd.com
AAAAAAAAKMDAAAAA   Alexander             White                Alexander.White@7v2XsptkFrc50K.org
AAAAAAAAOFCAAAAA   Timothy               Grogan               Timothy.Grogan@pn3NH2BDng.org
</console>
</unit>

===== Next =====
Now that we can query our data, we are going to learn how to modify the content of a table

<btn>
[[8_data_manipulation|How to modify the content of a table]]
</btn>