--{{0}}--
This template enables you to use SQLite databases directly in LiaScript via sql.js. You can create, query, import, and export databases interactively in your Markdown documents.
Try it on LiaScript:
See the project on Github:
https://github.com/liaTemplates/SQLite
--{{1}}--
Like with other LiaScript templates, there are three ways to integrate SQL.js:
{{1}}
-
Load the latest macros via (this might cause breaking changes)
import: https://raw.githubusercontent.com/liaTemplates/SQLite/main/README.mdor the current version 0.0.1 via:
import: https://raw.githubusercontent.com/LiaTemplates/SQLite/0.0.1/README.md -
Copy the definitions into your Project
-
Clone this repository on GitHub
--{{0}}--
Executes SQL code blocks against the default in-memory database. Each statement is run in sequence, and the results are rendered as HTML tables or status messages. You have to call the macro with a database name, this way different databases can be referenced in the course.
Example:
```SQL
CREATE TABLE hello (a int, b char);
INSERT INTO hello VALUES (0, 'hello');
INSERT INTO hello VALUES (1, 'world');
SELECT * FROM hello;
```
@SQL.run(hello-db)
```SQL
INSERT INTO hello VALUES (2, 'more');
INSERT INTO hello VALUES (3, 'updates');
```
@SQL.run(hello-db)Result:
CREATE TABLE hello (a int, b char);
INSERT INTO hello VALUES (0, 'hello');
INSERT INTO hello VALUES (1, 'world');
SELECT * FROM hello;@SQL.run(hello-db)
INSERT INTO hello VALUES (2, 'more');
INSERT INTO hello VALUES (3, 'updates');@SQL.run(hello-db)
--{{0}}--
If you want to focus on one aspect of the SQL queries and want to hide a part, you can use @SQL.run2, which is similar to @SQL.run, but it allows to run two code blocks in a row. While you can define with the plus and minus signs, which to show and which to hide.
Example:
```SQL -populate
-- Create sales table
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
salesperson TEXT NOT NULL,
region TEXT NOT NULL,
amount NUMERIC NOT NULL,
sale_date DATE NOT NULL
);
-- Insert sample data
INSERT INTO sales (salesperson, region, amount, sale_date) VALUES
('Alice', 'North', 12500, '2023-01-05'),
('Bob', 'South', 8700, '2023-01-10'),
('Carol', 'East', 15200, '2023-01-12'),
('Dave', 'West', 7300, '2023-01-15'),
('Alice', 'North', 9800, '2023-02-03'),
('Bob', 'South', 11600, '2023-02-08'),
('Carol', 'East', 14100, '2023-02-15'),
('Dave', 'West', 9200, '2023-02-20'),
('Alice', 'North', 16700, '2023-03-05'),
('Bob', 'South', 10300, '2023-03-12'),
('Carol', 'East', 12800, '2023-03-18'),
('Dave', 'West', 8500, '2023-03-25');
```
```SQL +query
-- 1. Running total of sales by salesperson
SELECT
salesperson,
region,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY salesperson
ORDER BY sale_date
) AS running_total
FROM sales
ORDER BY salesperson, sale_date;
```
@SQL.run2(sales)Result:
-- Create sales table
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
salesperson TEXT NOT NULL,
region TEXT NOT NULL,
amount NUMERIC NOT NULL,
sale_date DATE NOT NULL
);
-- Insert sample data
INSERT INTO sales (salesperson, region, amount, sale_date) VALUES
('Alice', 'North', 12500, '2023-01-05'),
('Bob', 'South', 8700, '2023-01-10'),
('Carol', 'East', 15200, '2023-01-12'),
('Dave', 'West', 7300, '2023-01-15'),
('Alice', 'North', 9800, '2023-02-03'),
('Bob', 'South', 11600, '2023-02-08'),
('Carol', 'East', 14100, '2023-02-15'),
('Dave', 'West', 9200, '2023-02-20'),
('Alice', 'North', 16700, '2023-03-05'),
('Bob', 'South', 10300, '2023-03-12'),
('Carol', 'East', 12800, '2023-03-18'),
('Dave', 'West', 8500, '2023-03-25');-- 1. Running total of sales by salesperson
SELECT
salesperson,
region,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY salesperson
ORDER BY sale_date
) AS running_total
FROM sales
ORDER BY salesperson, sale_date;@SQL.run2(sales)
--{{0}}--
You can use this macro to fetch an exported SQLite database file from a given URL and loads it as a database instance. The link macro notation can be used therefor in order to translate relative paths correctly.
Usage:
@[SQL.load(employees)](./sql.db)
```SQL
-- Query the data
SELECT
department,
COUNT(*) as employee_count,
ROUND(AVG(salary), 2) as avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
```
@SQL.run(employees)-- Query the data
SELECT
department,
COUNT(*) as employee_count,
ROUND(AVG(salary), 2) as avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;@SQL.run(employees)
--{{0}}--
In order to export the current database to a file, you can use the following command:
``` SQL
-- Create a simple employees table
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT,
salary NUMERIC,
hire_date DATE
);
-- Insert sample data
INSERT INTO employees (name, department, salary, hire_date) VALUES
('Alice Smith', 'Engineering', 85000, '2020-01-15'),
('Bob Johnson', 'Marketing', 72000, '2019-03-20'),
('Carol Williams', 'Engineering', 92000, '2018-11-07'),
('Dave Brown', 'Finance', 115000, '2017-05-12'),
('Eve Davis', 'Engineering', 110000, '2021-08-30');
EXPORT;
```
@SQL.run(employees2)-- Create a simple employees table
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT,
salary NUMERIC,
hire_date DATE
);
-- Insert sample data
INSERT INTO employees (name, department, salary, hire_date) VALUES
('Alice Smith', 'Engineering', 85000, '2020-01-15'),
('Bob Johnson', 'Marketing', 72000, '2019-03-20'),
('Carol Williams', 'Engineering', 92000, '2018-11-07'),
('Dave Brown', 'Finance', 115000, '2017-05-12'),
('Eve Davis', 'Engineering', 110000, '2021-08-30');
EXPORT;@SQL.run(employees2)
--{{0}}--
In order to import a previously exported database file, you can use the following command. This will open a file picker dialog to select the database file.
Example:
``` SQL
IMPORT;
SELECT name, sql
FROM sqlite_master
WHERE type='table';
```
@SQL.run(import)Result:
IMPORT;
SELECT name, sql
FROM sqlite_master
WHERE type='table';@SQL.run(import)
{{1}}
--{{1}}--
Additionally you can import an existing database from a http(s) URL:
``` SQL
IMPORT https://example.com/path/to/your/database.db;
SELECT name, sql
FROM sqlite_master
WHERE type='table';
```
@SQL.run(import)