SQL Police Department
Where no case goes un-queried
Mission Brief
- Confidential -
Select a value and tag it with relevant type to complete the brief.
Don't know SQL or need a hint?
Check out the guide tab!
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.
SQL Police Department
Please use device vertically