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:

296
421
714
382
296
421
29
42
71
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 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:

Goal table

296
421

Example input data

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

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
9302046
7861298
2054731
4715309
6279865
1937654
8543182

Remember:

SQL results are not necessarily in reading order. The rows will be 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. 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:

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.

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 2nd 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
930
786
205
471
WHERE A < 7 AND B >= 3
A B C
930
786
205
471
A B C
930
786
205
471
WHERE B <> 8 OR C <= 2
A B C
930
786
205
471

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
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