On the Subject of SQL - Basic

How fun is it to ride through a large table of numbers to find similarities...

This module is a SQL code simulator. To disarm it, produce a SQL query that outputs a given result. The input data is in this document, use it to reproduce the given result. Use the documentation to build a SQL query that will reproduce the expected result from the input data.

The Goal button

Press the Goal button to see what results the module expects. The expectation is a table of numbers with anywhere from 1 to 4 rows and 2 to 3 columns. Below are a few examples:

The Check button

Upon pressing the Check button, the module compiles the SQL query and executes it against the input data. The result is compared to the goal table. If it matches, the module is disarmed; otherwise, a strike is registered.

Disarming the module

To disarm the module, find the numbers from the goal table in the input data. The rows are always from top to bottom, but the columns can be reordered. Example:

The input data

The input data used to disarm the module is shown below. Find the values from the goal table inside this table and craft a SQL query that generates the same output.

On the Subject of SQL

SQL is a query language that describes how to manipulate sources of data and generate a result. It comprises many different keywords, only a small subset of which is used by this module.

How SQL is processed

SQL is processed as follows:

1. Each row is compared against the WHERE clause (described below). If a row complies with the WHERE clause, it is kept for further processing.
2. Each row that passes the WHERE clause is then processed against the SELECT clause (described below).
3. Finally, the LIMIT clause (described below) can be used to discard a certain number of rows by skipping the first X rows from the result and then taking the next Y lines after that.

SELECT X, Y, Z

The SELECT clause chooses which columns to return (and in what order). The module offers up to 3 simple column references. Each column can be clicked to cycle between columns A to G and a “-” which disables that selection. An example of this follows:

WHERE X operation Y

WHERE introduces “filters”. The rows are tested against the condition. A row is kept in the output only if it complies with the filter condition. In this simple SQL emulator, only comparisons of columns to fixed values are possible, such as: A < 2, C = 3, F >= 5. The possible operators are simple, familiar mathematical operators such as:

• “=”: Equals
• “<>”: Not equals
• “<”: Less than
• “<=”: Less than or equal to
• “>”: Greater than
• “>=”: Greater than or equal to

A basic example of a single filter follows:

 

Note: Conditions can be applied to any column, not just the SELECTed ones.

WHERE X AND/OR Y

If two conditions are required to achieve the result, click on the button below the WHERE label to add a 2^nd filter. The two filters can be connected using AND (both conditions must pass to keep the row) or OR (at least one condition must pass to keep the row). Examples:

Example data	Condition	Result
A B C 9 3 0 7 8 6 2 0 5 4 7 1	WHERE A < 7 AND B >= 3	A B C 9 3 0 7 8 6 2 0 5 4 7 1
A B C 9 3 0 7 8 6 2 0 5 4 7 1	WHERE B <> 8 OR C <= 2	A B C 9 3 0 7 8 6 2 0 5 4 7 1

LIMIT X, Y

LIMIT is followed by two values:

• First value: indicates the maximum number of rows to keep after applying conditions. If you select “All”, no rows are discarded.
• Second value: indicates the number of rows to skip after applying conditions. If you select “None”, no rows are discarded.

Note that the second value (the skip) is applied first by skipping that number of rows from the top of the results. Afterwards, the first value is applied to return a maximum of that many rows.

Examples:

Example data	Condition	Result
9 3 0 7 8 6 2 0 5 4 7 1	LIMIT 2, 0 (Limit of 2; skip none)	9 3 0 7 8 6
9 3 0 7 8 6 2 0 5 4 7 1	LIMIT 999, 1 (No limit; skip 1)	7 8 6 2 0 5 4 7 1
9 3 0 7 8 6 2 0 5 4 7 1	LIMIT 1, 2 (Limit of 1; skip 2)	2 0 5