SQL Police Department

Where no case goes un-queried

Welcome back!

© Wrapped Castle Limited - 2020. All rights reserved.

Mission Brief

- Confidential -

Select a value and tag it with relevant type to complete the brief.

No query has been run yet,
no results to show.

The query didn't return any rows.
Please recheck the conditions.

Tag    as

Build a query by clicking the fragments below
Don't know SQL or need a hint?
Check out the guide   tab!
 
 
 
 

DISTINCTLIMITASCDESC
SELECTFROMWHEREGROUP BYHAVINGORDER BY
<>=!=NOTINBETWEENANDOR()

SQL Error

Select any of the topics below to expand for details.

What is SQL?

SQL stands for Structured Query Language. It's a language used to access and manipulate data stored in tables. Each table contains columns for the different fields, and rows for the different records.

Structure of an SQL statement

SQL statement is composed of an ordered list of clauses such as SELECT, FROM, WHERE etc. Each clause has its own syntax, and examples will be detailed below.

An SQL statement must always end with a semicolon ( ; ).

In some cases, pressing Enter won't execute your statement and the database prompt will wait until you terminate the statement with a ; followed by Enter.

SELECT all data in a table

SELECT *
FROM employees;
To show or choose data from a table, we use a SELECT statement. The statement above takes the empoloyees table (the FROM clause) and selects or displays all columns in it, denoted by the * after SELECT. All the rows in the employees table will be shown since we haven't limited the row selection in any way.

SELECT specific columns

SELECT name, age
FROM students;
While SELECT * returns all the columns in the table specified after FROM, instead of using * you can specify a list of columns separated by commas. The command above selects only the name and age of all students in the students table, but not their email, id, or any other column in the students table.

SELECT without duplicate rows

SELECT DISTINCT name
FROM students;
When selecting specific columns, we might get duplicate rows, i.e. rows containing the same values for all the fields. For example selecting student names from a table with more than one student named Bob. Following SELECT with the DISTINCT keyword will eliminate these duplicates.

Sort rows

SELECT name, age
FROM friends
ORDER BY name, age DESC;
To sort the rows in a specific order, add an ORDER BY clause with a list of comma separated fields each followed by ASC (the default) or DESC. The statement above displays names and ages of friends sorted alphabetically by name in ascending order and when two friends share the same name the rows are sorted by age in descending order.

LIMIT the number of rows

SELECT name, grade
FROM course_grades
ORDER BY grade DESC
LIMIT 5;
When a table contains too many rows or we are interested only in the top (or bottom) results we can limit the number of rows we get back. In the above example, after the sorting we get only the top 5 students with the highest grades in descending order.

Filter rows

SELECT product
FROM inventory
WHERE code = 'ABC123';
When you know what you look for, you can filter the returned rows by adding a WHERE clause after the FROM. The WHERE clause states a condition to hold true for all the rows to be returned. The query above will only show the products whose code is ABC123.

Conditions: numeric comparison

SELECT product
FROM inventory
WHERE amount <= 10;
Columns or fields that contain numeric values can be used in conditions with math comparison operators such as

  • =(equals)
  • <(less-than)
  • <=(less-than or equals)
  • >(greater-than)
  • >=(greater-than or equals)
  • !=(does not equal)
  • <>(does not equal)

The query above shows all products that have 10 or less items left in the inventory.

Conditions: string comparison

SELECT productName
FROM inventory
WHERE productName < 'B';
Columns or fields that contain string values can be used in conditions with math comparison operators such as

  • =(equals)
  • <(before in dictionary order)
  • <=(before or the same)
  • >(after in dictionary order)
  • >=(after or the same)
  • !=(does not equal)
  • <>(does not equal)

The query above shows all products whose name will start with the letter A (name comes before 'B' in dictionary order).

Note that string values in the condition should be put between single quotes. Also note that any uppercase letter is smaller (i.e. comes before) any lowercase letter.

Conditions: list possible values

SELECT product
FROM inventory
WHERE amount IN (1, 5, 10);
If a column can contain one of serveral possible values, you can filter them by using the IN or NOT IN operators. The comma separated list of values after IN enclosed by parenthesis specify all the values to match (or ignore).

The query above shows all products that have either 1, 5, or 5 units left in the inventory.

Conditions: inclusive ranges

SELECT product
FROM inventory
WHERE amount BETWEEN 5 AND 9;
To match rows with values within a given range, use the BETWEEN operator. Specify the range with minimum and maximum inclusive values. It works for numbers, strings, dates and time stamps.

To match rows with values outside of a given range, use NOT BETWEEN instead.

The query above shows all products that have at least 5 units left in the inventory, but no more than 9.

Note that amount BETWEEN 5 and 9 is the equivalent condition to amount >= 5 AND amount <= 9

Conditions: combining with AND

SELECT product
FROM inventory
WHERE amount < 5
AND price > 1
;
If you want to match rows according to more than one condition, where all conditions have to match, use the AND operator between the conditions. The row will match only if all conditions are truthy. If even one of the conditions is false the row would not match.

The query above shows all products that have less than 5 units left in the inventory that their price is also greater than 1.

Items with amount of 5 or more won't match even if their price is greater than 1. Moreover, items with less than 5 left won't match if their price is 1 or less.

Conditions: combining with OR

SELECT product
FROM inventory
WHERE amount < 5
OR price > 1
;
If you want to match rows according to more than one condition, where at least one of the conditions have to match, use the OR operator between the conditions. The row will match if one of the conditions is true even if all the other conditions are false. The row will not match only if all the conditions are false.

The query above shows all products that have less than 5 units left in the inventory as well as shows all the products that cost more than 1.

The only items not matching are those that both have 5 or more units left as well as costing 1 or less.

Conditions: mixing AND and OR

SELECT product
FROM inventory
WHERE amount < 5
OR name = 'paper'
AND price > 1
;
When using both AND and OR it's important to know that AND has higher precedence. That means that like in Maths where multiplication is evaluated before addition, the AND will be evaluated before the OR.

The query above matches all products that are named paper whose price is also higher than 1, in addition to products that have more than 5 units left in the inventory.

If we want the OR to be evaluated first, we can use brackets ( ).

SELECT product
FROM inventory
WHERE (amount < 5
OR name = 'paper')
AND price > 1;
This query will matches all products that have more than 5 units left or named paper, and in both cases the price has to be higher than 1.

Sending brief...

Awaiting response...

SOLVED

Qualified for next rank!

Try again

SQL Police Department

Please use device vertically

© Wrapped Castle Limited - 2020. All rights reserved.

    APPROVED

    You have been promoted to the rank of !

    Declined

    Solve more cases to qualify for the next rank and try again.

    Submitting test...

    Awaiting response...

    Logging in...

    Logging out...

    Buy now and enjoy
    longer access

    Licenses usually last 6 months but if you join today, the 6 months countdown will start only when game development ends!

    Now for only $20

    Please enter an email address to be used to login to the game, for password resets and license related emails.

    Choose a password with at least 8 characters, containing lower case letters, upper case letters and numbers.

    Thank you!

    Payment completed successfully.

    • Email:
    • Order ID:
    • Capture ID:

    Payment Failed

    The reason given was:

    If no funds were taken from your account please try again. If any funds were taken or the problem persists, please email .

    Well done!

    You have reached the last level released so far!

    But there is more to come!

    More levels are in the works, covering aggregate functions, grouping, joining multiple tables and more. You will be notified when new levels are released.

    In the meantime, you can practice. For the rest of this session, cases will be randomly selected from levels you've already done.