# AlaSQL - Template This is a template for developing interactive SQL courses with LiaScript and [AlaSQL](http://alasql.org). __Try it on LiaScript:__ https://liascript.github.io/course/?https://raw.githubusercontent.com/liaTemplates/AlaSQL/master/README.md __See the project on Github:__ https://github.com/liaTemplates/AlaSQL --{{1}}-- There are three ways to use this template. The easiest way is to use the `import` statement and the url of the raw text-file of the master branch or any other branch or version. But you can also copy the required functionionality directly into the header of your Markdown document, see therefor the [last slide](#4). And of course, you could also clone this project and change it, as you wish. {{1}} 1. Load the macros via `import: https://raw.githubusercontent.com/liaTemplates/AlaSQL/master/README.md` 2. Copy the definitions into your Project 3. Clone this repository on GitHub ## `@AlaSQL.eval` --{{0}}-- Add the macro `@AlaSQL.eval` to the end of every SQL code-block that you want to make executable and editable in LiaScript. The given code gets evaluate by the AlaSQL interpreter and the result is shown in a console below. ``` sql CREATE TABLE test (language INT, hello STRING); -- insert dummy values INSERT INTO test VALUES (1,'Hello!'); INSERT INTO test VALUES (2,'Aloha!'); INSERT INTO test VALUES (3,'Bonjour!'); SELECT * FROM test WHERE language > 1; ``` @AlaSQL.eval --{{1}}-- Since AlaSQL has its own "runtime-environment", you can use as many scripts and only have to take care of the names. {{1}} ``` sql /* select some stuff */ SELECT * FROM test WHERE language > 1; ``` @AlaSQL.eval ## `@AlaSQL.eval_with_csv` --{{0}}-- Use `@AlaSQL.eval_with_csv` with two subsequent code-blocks, whereby the first block is used to define a table and the second the CSV data with a header in the first line. [PapaParse](https://www.papaparse.com) is applied for CSV-parsing, see the next section for the implementation. ``` sql CREATE TABLE one; -- ? gets replaced by the values in data.csv INSERT INTO one SELECT * from ?; ``` ``` text -data.csv Region,Country,Item Type,Sales Channel,Order Priority,Order Date,Order ID,Ship Date,Units Sold,Unit Price,Unit Cost,Total Revenue,Total Cost,Total Profit Middle East and North Africa,Libya,Cosmetics,Offline,M,10/18/2014,686800706,10/31/2014,8446,437.20,263.33,3692591.20,2224085.18,1468506.02 North America,Canada,Vegetables,Online,M,11/7/2011,185941302,12/8/2011,3018,154.06,90.93,464953.08,274426.74,190526.34 Middle East and North Africa,Libya,Baby Food,Offline,C,10/31/2016,246222341,12/9/2016,1517,255.28,159.42,387259.76,241840.14,145419.62 Asia,Japan,Cereal,Offline,C,4/10/2010,161442649,5/12/2010,3322,205.70,117.11,683335.40,389039.42,294295.98 Sub-Saharan Africa,Chad,Fruits,Offline,H,8/16/2011,645713555,8/31/2011,9845,9.33,6.92,91853.85,68127.40,23726.45 Europe,Armenia,Cereal,Online,H,11/24/2014,683458888,12/28/2014,9528,205.70,117.11,1959909.60,1115824.08,844085.52 Sub-Saharan Africa,Eritrea,Cereal,Online,H,3/4/2015,679414975,4/17/2015,2844,205.70,117.11,585010.80,333060.84,251949.96 Europe,Montenegro,Clothes,Offline,M,5/17/2012,208630645,6/28/2012,7299,109.28,35.84,797634.72,261596.16,536038.56 Central America and the Caribbean,Jamaica,Vegetables,Online,H,1/29/2015,266467225,3/7/2015,2428,154.06,90.93,374057.68,220778.04,153279.64 Australia and Oceania,Fiji,Vegetables,Offline,H,12/24/2013,118598544,1/19/2014,4800,154.06,90.93,739488.00,436464.00,303024.00 Sub-Saharan Africa,Togo,Clothes,Online,M,12/29/2015,451010930,1/19/2016,3012,109.28,35.84,329151.36,107950.08,221201.28 Europe,Montenegro,Snacks,Offline,M,2/27/2010,220003211,3/18/2010,2694,152.58,97.44,411050.52,262503.36,148547.16 Europe,Greece,Household,Online,C,11/17/2016,702186715,12/22/2016,1508,668.27,502.54,1007751.16,757830.32,249920.84 Sub-Saharan Africa,Sudan,Cosmetics,Online,C,12/20/2015,544485270,1/5/2016,4146,437.20,263.33,1812631.20,1091766.18,720865.02 Asia,Maldives,Fruits,Offline,L,1/8/2011,714135205,2/6/2011,7332,9.33,6.92,68407.56,50737.44,17670.12 Europe,Montenegro,Clothes,Offline,H,6/28/2010,448685348,7/22/2010,4820,109.28,35.84,526729.60,172748.80,353980.80 Europe,Estonia,Office Supplies,Online,H,4/25/2016,405997025,5/12/2016,2397,651.21,524.96,1560950.37,1258329.12,302621.25 North America,Greenland,Beverages,Online,M,7/27/2012,414244067,8/7/2012,2880,47.45,31.79,136656.00,91555.20,45100.80 Sub-Saharan Africa,Cape Verde,Clothes,Online,C,9/8/2014,821912801,10/3/2014,1117,109.28,35.84,122065.76,40033.28,82032.48 Sub-Saharan Africa,Senegal,Household,Offline,L,8/27/2012,247802054,9/8/2012,8989,668.27,502.54,6007079.03,4517332.06,1489746.97 ``` @AlaSQL.eval_with_csv --{{1}}-- Afterwards you can again use `@AlaSQL.eval` to define code for analyzing the newly integrated table. {{1}} ``` sql /* select some stuff */ SELECT Region FROM one; ``` @AlaSQL.eval {{1}} ``` sql SELECT * FROM one Where Region == "North America"; ``` @AlaSQL.eval ## Implementation --{{0}}-- The code shows the implementation of the two AlaSQL macros. The scripts load the JavaScript libraries for AlaSQL and PapaParse. PapaParse is used to parse a given string of CSV. ``` js script: https://cdnjs.cloudflare.com/ajax/libs/alasql/0.4.11/alasql-worker.min.js https://cdnjs.cloudflare.com/ajax/libs/alasql/0.4.11/alasql.min.js script: https://cdnjs.cloudflare.com/ajax/libs/PapaParse/4.6.1/papaparse.min.js @AlaSQL.eval @end @AlaSQL.eval_with_csv @end ``` --{{1}}-- If you want to minimize loading effort in your LiaScript project, you can also copy this code and paste it into your main comment header, see the code in the raw file of this document. {{1}} https://raw.githubusercontent.com/liaTemplates/AlaSQL/master/README.md ## More Information To find out more about AlaSQL, visit the project site: http://alasql.org or the Wiki at: https://github.com/agershun/alasql/wiki