On the Subject of SQL - Cruel

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 3 rows and 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 numbers from the goal table in the input data that could indicate what options were used. The rows are always used 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. Then, all rows are grouped together based on the GROUP BY clause (described below). You must provide 1 group by expression.
3. Each group’s rows are then processed against the SELECT clause and aggregated as configured (described below). You must aggregate all but 1 expression, that one expression has to be the one you use in the GROUP BY clause.
4. 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.

GROUP BY X

The GROUP BY clause chooses which column to use to create groups of rows. When you group by a certain column, all the values for that column are inspected and then, groups are made from those values. Then, you apply aggregators (See below) to the group of rows. For example:

SELECT Aggregators

This module allows usage of SELECT aggregators. Aggregators are group functions used to simplify groups of rows into single values. To use aggregators, you must use a GROUP BY expression. Here are a few examples of how aggregators affect groups of rows:

Example data	Condition	Result
A B C = 0 9 5 9 8 A B C = 3 7 8 1 8 A B C = 7 7 5 1 2 9 2	SELECT MIN(A), MAX(B), C	9 8 0 1 8 3 1 5 7
A B C = 0 9 5 9 8 A B C = 3 7 8 1 8 A B C = 7 7 5 1 2 9 2	SELECT COUNT(A), AVG(B), C	2 6 0 2 8 3 3 3 7
A C = 0 9 9 A C = 3 7 1 A C = 7 7 1 9	SELECT SUM(A), C	18 0 8 3 17 7

Note: The AVG aggregator will be rounded down automatically because the resulting decimal values are casted to integers, therefore decimals are lost in the process. You will never see decimal numbers in this module.