This is what a SELECT-type SQL query looks like:
SELECT * FROM entity ;
You will recall that SQL is used to communicate with RDBMSes. The form our communication takes is the query. Queries that start with SELECT
are in fact questions we are asking. When the RDBMS answers us, we say that it sends back (or returns) a table.
If we write and execute this particular query, the RDBMS will simply return all the rows and columns of the entity table.
This basic structure will enable us to perform all of the relational algebra operations we have covered so far.
So let’s get started! We’ll begin with projection.
Projection
The SELECT
keyword lets you choose which columns you want to display. This is called Projection.
For now, we have placed the *
character after SELECT
. The asterisk signifies that we want to retrieve all available columns.
However, we can replace this character with the names of the columns we would like to retrieve instead. Here’s an example:
SELECT id, name, status FROM entity ;
Go on, test it yourself in the console at the end of this chapter!
Restriction
You will recall that a restriction lets us limit our query to rows that meet a certain condition.
The WHERE
keyword is used to specify that condition.
Let’s begin our investigation! We are going to search for our mysterious company called Big Data Crunchers Ltd.
SELECT * FROM entity WHERE name = 'Big Data Crunchers Ltd.' ;
This query returns a table with one row corresponding to the company we are searching for.
Other possible SQL operators include:
comparison operators
Operator | Tests whether... |
| A is equal to B |
| A is different from B |
| A is greater than B / A is less than B |
| A is greater than or equal to B / A is less than or equal to B |
| A is between B and C |
| We’ll discuss this operator in a later chapter |
| A is found in the list (B1, B2, etc.) |
| A has no value |
Logical operators:
OR
AND
NOT
We can use these operators to make our condition a little more complex:
SELECT * FROM entity WHERE (id < 10000004 AND (NOT id < 10000000)) OR (name = 'Big Data Crunchers Ltd.');
This query will display all companies whose identifier is between 10000000 (inclusive) and 10000004 (not inclusive) as well as all companies whose name is Big Data Crunchers Ltd
. Take a break and read it again - it’s a brain-teaser!
The Cartesian Product
To perform a Cartesian product between two tables, we simply need to specify the two tables after the FROM
clause. What could be simpler?
SELECT * FROM entity, address ;
You can even specify more than 2 tables! The result will then be all of the possible combinations between all of the rows of each of the tables.
This query can be costly in terms of resources (processing time and RAM). Indeed, the number of rows returned will be equal to the number of rows of the entity table multiplied by the number of rows of the address table.
Exercise caution: this number can quickly explode!
Can I omit FROM
?
Good question!
A query without a FROM
statement will not be executed on any table. It will return only a single row. For example, the following query:
SELECT 45, 20, 'hello' ;
returns a row consisting of three attributes (three columns). The value of these will be 45
, 20
and hello
.
Projections: Scalar Functions and the AS Keyword
It is possible to apply functions to columns. For example, we can use the multiplication function, and multiply the identifier of all of the entities by 2 (why not?).
SELECT id * 2, name, status FROM entity ;
It is even possible to combine functions. For example, let’s calculate the absolute value of the opposite of the identifier multiplied by 2 (let’s go crazy!) :
SELECT ABS( (- id) *2 ) AS calcul_bizarre, name, status FROM entity ;
There are many types of functions, like string functions! If you want to retrieve the name of a company along with its status, you can append the status to the end of the string using the concatenation operator ||
. Note the parentheses around || status ||
:
SELECT name || '(' || status || ')' AS name_and_status FROM entity ;
-- Mysql version
SELECT concat(name,'(',status,')') AS name_and_status FROM entity ;
There are also date functions, and binary functions (that return TRUE
or FALSE
). Here’s an example:
SELECT CURRENT_DATE() > incorporation_date FROM entity ;
CURRENT_DATE()
returns today’s date. This is then compared to the attribute incorporation_date
with help of the >
operator. A value of TRUE
(or 1
, depending on your DBMS) is returned if incorporation_date is earlier than the current date, or FALSE
(or 0
) if it is not.