Retrieve Data Using SQL

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  

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

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!) :  o_O

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.

A Little Exercise!

The database is online. Get some practice by rewriting the queries found in this chapter. Don’t hesitate to change them, play with them, and “see what happens.” ;)

Interactive Console