OpenLLM-France/wikimedia huggingface dataset:
# SQL Dialects Reference/Select queries/Subqueries
### Subqueries in FROM clause

| Standard | Subquery (ANSI calls it <derived table>) alias is mandatory: SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions |
| DB2 | ? |
| Firebird | ? |
| Ingres | ? |
| Linter | ? |
| MonetDB | SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions Subquery alias is mandatory WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions |
| MSSQL | SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions Subquery alias is mandatory WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions |
| MySQL | ? |
| Oracle | SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) WHERE conditions |
| PostgreSQL | SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions Subquery alias is mandatory WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions |
| SQLite | SELECT … FROM (SELECT …) [AS alias] WHERE … WITH cte AS (SELECT …) SELECT … FROM cte WHERE … |
| Virtuoso | ? |
* ↑ https://jakewheat.github.io/sql-overview/sql-2011-foundation-grammar.html#table-primary
# SQL Dialects Reference/Transactions
## Transactions

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Start | START TRANSACTION [transaction characteristics] | Implicit | SET TRANSACTION | ? | ? | BEGIN TRAN[SACTION] | BEGIN [WORK] START [TRANSACTION] | START TRANSACTION BEGIN TRANSACTION | Implicit | BEGIN [WORK|TRANSACTION] START TRANSACTION | BEGIN [TRANSACTION] | Implicit |
| ? | Commit | COMMIT [WORK] | COMMIT [WORK] | COMMIT [WORK] | [EXEC SQL] COMMIT [WORK] | ? | COMMIT [WORK] COMMIT TRAN[SACTION] [transaction_name] | COMMIT [WORK] | COMMIT [WORK] [AND [NO] CHAIN] | COMMIT [WORK] | COMMIT [WORK|TRANSACTION] | COMMIT [TRANSACTION] END [TRANSACTION] | COMMIT WORK |
| ? | Rollback whole transaction | ROLLBACK [WORK] | ROLLBACK [WORK] | ROLLBACK [WORK] | [EXEC SQL] ROLLBACK [WORK] | ? | ROLLBACK [WORK] ROLLBACK TRAN[SACTION] | ROLLBACK [WORK] | ROLLBACK [WORK] [AND [NO] CHAIN] | ROLLBACK [WORK] | ROLLBACK [WORK|TRANSACTION] | ROLLBACK [TRANSACTION] | ROLLBACK WORK |
| ? | Define a savepoint x, while inside a transaction | SAVEPOINT x | SAVEPOINT x ON ROLLBACK RETAIN CURSORS | SAVEPOINT x | SAVEPOINT x | ? | SAVE TRAN[SACTION] x | SAVEPOINT x | SAVEPOINT x | SAVEPOINT x | SAVEPOINT x | SAVEPOINT x | N/A |
| ? | Rollback to given savepoint x | ROLLBACK [WORK] TO SAVEPOINT x | ROLLBACK [WORK] TO SAVEPOINT x | ROLLBACK [WORK] TO [SAVEPOINT] x | [EXEC SQL] ROLLBACK [WORK] TO x | ? | ROLLBACK TRAN[SACTION] x | ROLLBACK [WORK] TO [SAVEPOINT] x | ROLLBACK [WORK] [AND [NO] CHAIN] TO SAVEPOINT x | ROLLBACK [WORK] TO [SAVEPOINT] x | ROLLBACK [WORK|TRANSACTION] TO [SAVEPOINT] x | ROLLBACK [TRANSACTION] TO [SAVEPOINT] x | N/A |
| ? | Release (forget) savepoint x | RELEASE SAVEPOINT x | RELEASE [TO] SAVEPOINT x | RELEASE SAVEPOINT x | N/A | ? | N/A | RELEASE SAVEPOINT x | RELEASE SAVEPOINT x | N/A | RELEASE [SAVEPOINT] x | RELEASE [SAVEPOINT] x | N/A |
| ? | Prepare transaction named id for two-phase commit | ? | ? | N/A | ? | ? | ? | ? | N/A | ? | PREPARE TRANSACTION id | N/A | ? |
| ? | Commit prepared transaction named id | ? | ? | N/A | ? | ? | ? | ? | N/A | ? | COMMIT PREPARED id | N/A | ? |
| ? | Rollback prepared transaction named id | ? | ? | N/A | ? | ? | ? | ? | N/A | ? | ROLLBACK PREPARED id | N/A | ? |
| | Start | Commit | Rollback | Prepare | Execute prepared |
| Linter | Implicit | a COMMIT statement is executed any DDL statement is executed any statement is executed in AUTOCOMMIT mode | a ROLLBACK statement is executed a user process is terminated abnormally or disconnects without COMMIT/ROLLBACK | ? | ? |
# SQL Dialects Reference/Write queries/Replace query
### Replace query

Replace query inserts new row if no row with such primary key exists or updates existing row if it does. SQL:2003 standard introduced a MERGE statement to implement such functionality, while other implementations provide similar queries named "REPLACE" or so-called "Upsert" query (a portmanteau of UPDATE and INSERT).
| Standard | MERGE statement can be used to do a replace query: MERGE INTO table_name1 USING table_name2 ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT columns VALUES (values) Note that MERGE is much more powerful than just doing replace queries. |
| DB2 | MERGE statement MERGE INTO phonebook AS p USING ( VALUES ('john doe', '1234' ) ) AS v(name, extension) ON ( p.name = v.name ) WHEN MATCHED UPDATE SET p.extension = v.extension WHEN NOT MATCHED INSERT VALUES ( v.name, v.extension ) |
| Firebird | MERGE statement MERGE INTO phonebook B USING ( SELECT name FROM phonebook WHERE name = 'john doe') E ON (B.name = E.name) WHEN MATCHED THEN UPDATE SET B.extension = '1234' WHEN NOT MATCHED THEN INSERT (name, extension) VALUES ('john doe', '1234); Non-standard simplified form: UPDATE OR INSERT INTO phonebook (name, extension) VALUES ('john doe', '1234') MATCHING (name) |
| Ingres | ? |
| Linter | ? |
| MonetDB | ? |
| MSSQL | MERGE statement (from version SQL Server 2008) DECLARE @UnitMeasureCode nchar(3) = 'ABC' DECLARE @Name varchar(25) = 'Test name' MERGE INTO Production.UnitMeasure AS target USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name) ON (target.UnitMeasureCode = source.UnitMeasureCode) WHEN MATCHED THEN UPDATE SET Name = source.Name WHEN NOT MATCHED THEN INSERT (UnitMeasureCode, Name) VALUES (source.UnitMeasureCode, source.Name) |
| MySQL | Allows 3 syntaxes: non-standard REPLACE query, (since 4.1) INSERT ... ON DUPLICATE KEY UPDATE, and a variant on IF EXISTS. REPLACE [INTO] table [(columns)] VALUES (values) INSERT INTO table (columns) VALUES (values) ON DUPLICATE KEY UPDATE column1=value1, column2=value2 IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' ) THEN UPDATE phonebook SET extension = '1234' WHERE name = 'john doe' ELSE INSERT INTO phonebook VALUES( 'john doe','1234' ) END IF |
| Oracle | MERGE statement MERGE INTO phonebook B USING ( SELECT name_id FROM phonebook WHERE name = 'john doe') E ON (B.name = E.name) WHEN MATCHED THEN UPDATE SET B.extension = '1234' WHEN NOT MATCHED THEN INSERT (B.name, B.extension) VALUES ('john doe', '1234); Multi-statement form: IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' ) UPDATE phonebook SET extension = '1234' WHERE name = 'john doe' ELSE INSERT INTO phonebook VALUES( 'john doe','1234' ) |
| PostgreSQL | Since version 9.5 INSERT INTO...ON CONFLICT... syntax inspired by MySQL: INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname; |
| SQLite | REPLACE statement: REPLACE [INTO] table [(columns)] VALUES (values) (always deletes the old row) |
| Virtuoso | ? |
# SQL Dialects Reference/Write queries/Save results from select query as new table
### Save results from select query as new table

Problem: save results from an arbitrary SELECT query (SELECT columns FROM table ...) as a new permanent table new_table.
| Standard | ? |
| DB2 | ? |
| Firebird | ? |
| Ingres | ? |
| Linter | CREATE TABLE new_table AS SELECT columns FROM table ... |
| MonetDB | CREATE TABLE new_table AS SELECT columns FROM table ... WITH DATA |
| MSSQL | SELECT columns INTO new_table FROM table ... |
| MySQL | CREATE TABLE new_table [AS] SELECT columns FROM table ... |
| Oracle | CREATE TABLE new_table [AS] SELECT columns FROM table ... |
| PostgreSQL | SELECT columns INTO new_table FROM table ... CREATE TABLE new_table [AS] SELECT columns FROM table ... |
| SQLite | CREATE TABLE new_table [AS] SELECT columns FROM table ... |
| Virtuoso | CREATE TABLE new_table [AS] SELECT columns FROM table ... |
# SQL Dialects Reference/Write queries/Update returning
### Update returning

This variant of UPDATE statement allows to return the values from the row(s) affected by the update.
| Standard | No support in SQL:2003 |
| DB2 | UPDATE ... RETURNING ... INTO ... since 9.7. Only returns the updated values, see the documentation. |
| Firebird | UPDATE ... RETURNING ... since 2.1. Can return both old and new values using old. or new. prefixes but for at most one row, see the documentation. |
| Ingres | ? |
| Linter | ? |
| MonetDB | ? |
| MSSQL | UPDATE ... OUTPUT ... since MS SQL Server 2005. Can return both old and new values using inserted. or deleted. prefixes, see the documentation. |
| MySQL | No support in 5.7. |
| Oracle | UPDATE ... RETURNING ... INTO ... Supports retrieving values of multiple rows with BULK COLLECT INTO, see the documentation. |
| PostgreSQL | UPDATE ... RETURNING ... since 8.2. Only returns the updated values, see the documentation. |
| SQLite | No support in 3.8. |
| Virtuoso | ? |
# SQL Dialects Reference/Select queries/Hierarchical Queries
### Hierarchical Queries

Hierarchical queries are a way to extract information from a table that is linked with itself.
Let's say we have the following table:
If we have the following values:
The values that "hang" from node 50 are the values 50, 51, 52, 53, 54 but not 5 nor 10.
* DB2
or
* Firebird / InterBase
* Ingres, MySQL, MSSQL
* PostgreSQL
* SQLite
* Oracle, Linter
* ↑ MS SQL does not allow the RECURSIVE keyword
# SQL Dialects Reference/Select queries/Select without tables
### Select without tables

Sometimes one needs to execute SQL scalar expressions without table context, i.e. make a query that would act as normal SELECT operator, evaluate given comma-separated expressions and return a table with single row and one or multiple columns (one for every individual expression). Obviously, expressions can't reference any columns from the tables, as there are none.
An example is determining the value of a mathematical function, using Oracle syntax:
| Standard | ? |
| DB2 | VALUES SELECT expressions FROM sysibm.sysdummy1 |
| Firebird | SELECT expressions FROM rdb$database |
| Ingres | SELECT expressions |
| Linter | SELECT expressions |
| MonetDB | SELECT expressions VALUES ("expressions") |
| MSSQL | SELECT expressions VALUES (expressions) |
| MySQL | SELECT expressions SELECT expressions FROM dual VALUES row_constructor_list |
| Oracle | SELECT expressions FROM dual |
| PostgreSQL | SELECT expressions VALUES (expressions) |
| SQLite | SELECT expressions VALUES ("expressions") |
| Virtuoso | ? |
# SQL Dialects Reference/Write queries/Insert results from select query into existing table
### Insert results from select query into existing table

Problem: Insert results from a given select query (SELECT columns FROM table ...) into existing table (existing_table) of the same or compatible structure (as query result).
| Standard | INSERT INTO existing_table SELECT columns FROM table ... |
| DB2 | INSERT INTO existing_table SELECT columns FROM table ... |
| Firebird | INSERT INTO existing_table SELECT columns FROM table ... |
| Ingres | ? |
| Linter | INSERT INTO existing_table SELECT columns FROM table ... |
| MonetDB | INSERT INTO existing_table SELECT columns FROM table ... |
| MSSQL | INSERT INTO existing_table SELECT columns FROM table ... |
| MySQL | INSERT INTO existing_table SELECT columns FROM table ... |
| Oracle | INSERT INTO existing_table SELECT columns FROM table ... |
| PostgreSQL | INSERT INTO existing_table SELECT columns FROM table ... |
| SQLite | INSERT INTO existing_table SELECT columns FROM table ... |
| Virtuoso | INSERT INTO existing_table SELECT columns FROM table ... |
# SQL Exercises

The purpose of any exercise is to steadily develop skills and to acquire the automatic algorithms for fulfilling certain operations. As for the SQL language, practical exercises are intended to enable the database programmer to quickly devise SQL queries in order to solve practically any problem, by having already studied similar problems in exercises. This book provides such a collection of practical solved SQL exercises.
For an introduction to the SQL language, please refer to the wikibook Structured Query Language.

## Contents

    Cover · Contributors
* The computer store
* Employee management
* The warehouse
* Movie theatres
* Pieces and providers
* Scientists
* Planet Express
* The Hospital
# Question Writer Tracker Manual/Publishing/Introduction
## Introduction to Publishing on Question Writer Tracker

You can use Question Writer Tracker to publish quizzes authored with Question Writer directly to the internet. You'll find the option second in the Publish menu. It's a one-step process - click to publish and it will upload your quiz and give you back the web address which allows your quiz to be accessed online.
# Question Writer Tracker Manual/Results
## Results

* Introduction
* Order Quiz Results by Time Spent
* Question Response Chart
* Sort Candidates
Go to Introduction, Basic Navigation, Controlling and Monitoring Access, Publishing, Frequently Asked Questions
# Question Writer Tracker Manual/Results/Question Response Chart
## Question Response Chart

Question Writer Tracker allows you to visualize the response data for each of your questions. You can view the volume of responses as a pie chart and the scores achieved as a bar chart. This visualization gives you an easy way to see how people are responding and scoring on any given question.
You can access this chart by clicking on the question mark icon beside the quizzes listed on the Results page. This gives you a complete list of the questions in that quiz. If you double click on a question you will get a response chart for that question.
# Question Writer Tracker Manual/Publishing/Pop-Up Quiz Window
## Pop-Up Quiz Window

It's usually a good idea to use a pop-up window when linking to a quiz, as it will create maximum space, and removes the URL bar and the navigation buttons which can interfere with the quiz's own navigation. Question Writer Tracker generates HTML code to create a pop-up window for your quiz.
Open the Quizzes page in your Question Writer Tracker account and select your quiz. The HTML code is in the box at the bottom of the page. You can copy and paste the HTML code directly into your own webpage.
# Question Writer Tracker Manual/Results/Introduction
## Introduction to Results

When you login to your Question Writer Tracker Account and select Results you will see the results screen with a line for each quiz that you have published with the results server in the setup.
At the top you will see New - View Results by Candidate. If you click on this you will see a listing of all the candidates that have sent results along with the number of result sets that you have received for them and the date of their last submission. If you click on a candidate's name you will see a list of each quiz they have completed, their score, percentage mark, time taken to complete and the date and time that the results were received. You can then select a quiz and see the summary report for that quiz.
To the right of the quiz title you can see 3 icons:
* A red cross – this will remove the quiz results from the server
* A white question mark in a blue circle – this will take you to a list of the questions and a breakdown of the responses (see picture below)
* A green Excel icon - for exporting your results
If you have entered a Question Title in the question Metadata (professional version only) it will appear here, if not Question Writer will automatically the title as the first 60 characters of the actual question.

### Exporting Quiz Results

When you click on the export to .csv file option a download window will appear on your screen asking if you want to open or save the file. If you click Open, Excel will be opened and your results appear, with a line for each quiz taker. You can then manipulate the data as you wish.
If you have set an essay type question you can enter the mark for each participant and then recalculate the total score.

### View Results online

If you click on the quiz title you will be taken to the results page.
Clicking on any of the column headings will sort the results in ascending order and then with another click descending order according to that column, ie alphabetical in order of name or number order for the % or pass score etc.
Clicking on the red cross beside the result will delete that individual result.
Clicking on the result will produce a Summary Report for that individual.
If you click on any of the questions within that Summary Report you will be shown an analysis of all answers given to that question.
# Question Writer Tracker Manual/Results/Order Quiz by Time Spent
## Order Quiz by Time Spent

Here's another useful little feature - you can order the result sets by the amount of time the user spent taking the quiz on Question Writer Tracker. You can usually identify a user who got bored during a quiz or otherwise didn't make a proper effort at a quiz by looking at the overall time spent on the quiz.
Delete these response sets to improve the quality of the data. This allows you to better compare the relative difficulty of questions in your quiz or the overall averages without the 'click-thru' attempts polluting the data.
# Quizbowl science/Biology/Epinephrine

Epinephrine or adrenaline is an amino acid derivative hormone (polar). It is released from the adrenal medulla.

## Functions

* Acts on liver cells to increase blood glucose by:
** increasing glycogen breakdown and
** increasing gluconeogenesis.
* Acts on muscle cells to make ATP by
** increasing glycogen breakdown
** increasing glycolysis
** increasing beta-oxidation (breaking down fatty acids to get energy)
Basically, the liver makes glucose, and the muscle breaks down that glucose, so ATP is available for fight-flight.

## Mechanism

Epinephrine binds a G-protein coupled receptor, activating adenylate cyclase, which produces cAMP from ATP. This causes protein kinase A to become more active. PKA phosphorylates:
** makes glycogen synthase less active
** activates phosphorylase kinase -- makes glycogen phosphorylase more active (involved in glycogen breakdown -- glycogenolysis)
# Quizbowl science/Biology/Glucagon

Glucagon is a GPCR. It is released from the alpha cells of the Islets of Langerhans of the pancreas.

## Function

Glucagon responds to low glucose levels. It acts on liver cells to increase blood glucose levels by:
* inhibiting glycolysis and
* activating gluconeogenesis

## Mechanism

Protein Kinase A is activated. It:
* inhibits PFK-2 -- less F-2,6-BP made
* activates FBPase-2 -- more F-2,6-BP degraded
Results in lower F-2,6-BP:
* allosterically activates FBPase-1 -- increased gluconeogenesis (more glucose)
* less active PFK-1 -- decreased glycolysis
==Sources
* ↑ https://www.youtube.com/watch?v=1ImdVTWKiqs&list=PLmGAunhTA6--y7K04aQx1ZKhEuc-gsQIl&index=79
# Question Writer Tracker Manual/Results/Sort Candidates by Results
### Sort Candidates by Result

If you click on View Quiz Results By Candidate - this will give you the view of the candidates who have taken quizzes, the number of result sets, and the date the last set of results was submitted. Clicking on a candidate's name takes you to a page with the list of quizzes with the summary of results and dates/times.
# Quizbowl science

* Quizbowl science/Biology/cAMP
* Quizbowl science/Biology/Epinephrine
* Quizbowl_science/Biology/Glucagon
# Reaktor/Modules/Delay
## Delay Modules

* Single Delay
* Multi-Tap Delay
* Diffuser Delay
* Grain Delay
* Grain Cloud
* Unit Delay
# Reaktor/Modules/Event Processing
## Event Processing Modules

* Accumulator
* Counter
* Randomizer
* Frequency Divider
* Ctrl. Shaper 1 BP
* Ctrl. Shaper 2 BP
* Ctrl. Shaper 3 BP
* Logic AND
* Logic OR
* Logic EXOR
* Logic NOT
* Order
* Iteration
* Separator
* Value
* Merge
* Step Filter
* Router M->1
* Router 1, 2
* Router 1->M
* Timer
* Hold
* Event Table
# Reaktor/Modules/Auxiliary
## Auxiliary Modules

* Tapedeck 1-Ch
* Tapedeck 2-Ch
* Audio Voice Combiner
* Event V.C. All
* Event V.C. Max
* Event V.C. Min
* A to E
* A to E (Trig)
* A to E (Perm)
* A to Gate
* To Voice
* From Voice
* Audio Smoother
* Event Smoother
* Master Tune/Level
* Tempo Info
* Voice Info
* Tuning
* System Info
* Note Range
* Midi Channel
* Snapshot
* Set Random
* Unison Spread
* Snap Value
# Reaktor/Modules/Event V.C. Min
## Comments

* The G/gate input on this module is not a gate at all. Were it an actual gate you would expect any update at the In port on a voice for which gate is "1" to trigger an update at the output. Instead it functions more like a trigger, but stranger still, it works like trigger inputs on audio modules as it triggers on a rising edge (going from 0 to 1 or presumably any other positive value). This is unlike every other event "trigger" input (except Event V.C. Max), which of course trigger on any event, or any positive event, but in any case do not require sending a 0 followed by a 1.
* This module also does not work properly at initialization time, perhaps a side effect of the bizarre behavior of the G input.
# Reaktor/Modules/Filter
## Filter Modules

* HP/LP 1-Pole
* HP/LP 1-Pole FM
* Allpass 1-Pole
* Multi 2-Pole
* Multi 2-Pole FM
* Multi/Notch 2-Pole
* Multi/Notch 2-Pole FM
* Multi/LP 4-Pole
* Multi/LP 4-PoleFM
* Multi/HP 4-Pole
* Multi/HP 4-Pole FM
* Pro-52
* Ladder
* Ladder FM
* Peak EQ
* Peak EQ FM
* High Shelf EQ
* High Shelf EQ FM
* Low Shelf EQ
* Low Shelf EQ FM
* Differentiator
* Integrator

## Filters in general

* Filter design
* Low-pass filter
* High-pass filter
* Band-pass filter
* Introduction to Digital Filters a sample chapter from The Scientist and Engineer's Guide to Digital Signal Processing by Steven W. Smith
# Reaktor/Modules/Constant

Constant is really just a number that does not change, it can be of any value and is used for various calculations within Reaktor
# Reaktor/Modules/Event V.C. Max
## Comments

* The G/gate input on this module is not a gate at all. Were it an actual gate you would expect any update at the In port on a voice for which gate is "1" to trigger an update at the output. Instead it functions more like a trigger, but stranger still, it works like trigger inputs on audio modules as it triggers on a rising edge (going from 0 to 1 or presumably any other positive value). This is unlike every other event "trigger" input (except Event V.C. Min), which of course trigger on any event, or any positive event, but in any case do not require sending a 0 followed by a 1.
* This module also does not work properly at initialization time, perhaps a side effect of the bizarre behavior of the G input.
# Reaktor/Modules/In Port

The maximum number of In Ports in one design page is 40 (from Leviathan User's Guide and Event Logic tutorial v2.0, page 30)

### Reasons for illegal connections (red cross on module's input port)

* Audio output can not be connected to Event input and vice versa. You can change Audio signal to Event with A to E module
* Polyphonic outputs cannot be connected to monophonic inputs. You can use Audio Voice Combiner
* Polyphonic signals cannot be send between instruments. You can use Send and Receive instead, but it will cause 30 msec latency