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:

296
421
714
296
421
296

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:

Goal matrix

901
114
107

Values from the goal in column 3 seem to match column F!

This could mean we used column F to group values together!

Example input data

A B C D E F
9 3 8 6 9 1
9 3 0 4 3 1
1 2 1 6 2 4
1 7 0 4 3 7

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.

A B C D E F G
9502103
7831064
7574653
9804104
1272663
1831605
9272155
1572664
7804004
9201155

Remember:

SQL results are not necessarily in reading order. The rows will be processed from top to bottom, but the columns can be arbitrarily reordered in the SELECT clause in the query.

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:

A B C D E F G
9302046
7861298
2054731
4715309
6279865
1937654
8543182

SQL: SELECT D, A

Notice there is no 3rd expression. We used a “-”.

D A
29
17
42
54
96
71
38

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
  • “>”: Greather than
  • “>=”: Greather than or equal to

A basic example of a single filter follows:

A B C D E F G
9302046
7861298
2054731
4715309
6279865
1937654
8543182

SQL: WHERE C >= 5

A B C D E F G
7861298
2054731
6279865

 

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:

A B C D E F G
9502103
7831064
7574653
9804104
1272663
1831605
9272155

SQL: GROUP BY C

A B D E F G
C = 0
952103
984104
C = 3
781064
181605
C = 7
754653
122663
922155

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
95
98
A B
C = 3
78
18
A B
C = 7
75
12
92
SELECT MIN(A), MAX(B), C
980
183
157
A B
C = 0
95
98
A B
C = 3
78
18
A B
C = 7
75
12
92
SELECT COUNT(A), AVG(B), C
260
283
337
A
C = 0
9
9
A
C = 3
7
1
A
C = 7
7
1
9
SELECT SUM(A), C
180
83
177

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.

LIMIT X, Y

LIMIT is followed by two values:

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
930
786
205
471
LIMIT 2, 0
(Limit of 2; skip none)
930
786
930
786
205
471
LIMIT 999, 1
(No limit; skip 1)
786
205
471
930
786
205
471
LIMIT 1, 2
(Limit of 1; skip 2)
205