On the Subject of SQL - Evil

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 3 to 4 rows and 3 columns. Below are a few examples:

296
421
714
382
296
421
714

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

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. All rows are grouped together based on the GROUP BY clause (described below). You must provide 1 group by expression.
  2. 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.

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

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.