3. Filter

It is a bit slow to fetch all the data from a table to get a single value. Can you imagine how slow Facebook would be if it returned its 2.3 billion users just to know whether you entered your password correctly? That is why the data has to be limited somehow. Luckily, SQL gives us filters to keep only what interests us, such as: limiting the number of rows returned (LIMIT), ordering the columns (ORDER BY) and creating conditionals (WHERE).

SELECT [columnas] FROM [tabla] WHERE [condicionales] ORDER BY [columna] ASC/DESC LIMIT [número de filas] OFFSET [posición];

It is not necessary to use all the tools, just what we need.

Limit

SELECT [columnas] FROM [tabla] LIMIT [número de filas] OFFSET [posición];

To list the first 5 results.

SELECT TrackId, Name, Composer FROM Track LIMIT 5 OFFSET 0;

1|For Those About To Rock (We Salute You)|Angus Young, Malcolm Young, Brian Johnson
2|Balls to the Wall|
3|Fast As a Shark|F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman
4|Restless and Wild|F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman
5|Princess of the Dawn|Deaffy & R.A. Smith-Diesel

With LIMIT we state the number of results we want. Here we say 5.

OFFSET indicates the position from which it will start counting. In the world of programming you start counting from 0, so we are telling it to start from the beginning.

A shortened version removes OFFSET, changes the order and adds a comma as a separator.

SELECT [columnas] FROM [tabla] LIMIT [posición], [número de filas];

SELECT TrackId, Name, Composer FROM Track LIMIT 0, 5;

How could I get rows 16, 17 and 18?

SELECT TrackId, Name, Composer FROM Track LIMIT 3 OFFSET 15;

16|Dog Eat Dog|AC/DC
17|Let There Be Rock|AC/DC
18|Bad Boy Boogie|AC/DC

Order

SELECT [columnas] FROM [tabla] ORDER BY [columna] ASC/DESC;

I order by name in ASCending order.

SELECT name FROM Playlist ORDER BY name ASC;

90’s Music
Audiobooks
Audiobooks
Brazilian Music
Classical
Classical 101 - Deep Cuts
Classical 101 - Next Steps
Classical 101 - The Basics
Grunge
Heavy Metal Classic
Movies
Movies
Music
Music
Music Videos
On-The-Go 1
TV Shows
TV Shows

I order by name in DESCending order.

SELECT name FROM Playlist ORDER BY name DESC;

TV Shows
TV Shows
On-The-Go 1
Music Videos
Music
Music
Movies
Movies
Heavy Metal Classic
Grunge
Classical 101 - The Basics
Classical 101 - Next Steps
Classical 101 - Deep Cuts
Classical
Brazilian Music
Audiobooks
Audiobooks
90’s Music

Conditionals

Just like a programming language, we can filter through comparisons or arithmetic operations. To do this we will always use WHERE before stating the instruction.

SELECT [columnas] FROM [tabla] WHERE [condicionales];

Comparison

SELECT [columnas] FROM [tabla] WHERE [columna] = [valor];

Example

SELECT BillingCity FROM Invoice WHERE Total = 1.98;

Berlin
Paris
Redmond
Cupertino
Sidney
...

LIKE

A way to search for words with a certain pattern. The key is to use the % symbol to indicate where it starts.

That starts with...

SELECT [columnas] FROM [tabla] WHERE [columna] LIKE 'texto%';

Example

We search for all the cities that start with R.

SELECT DISTINCT BillingCity FROM Invoice WHERE BillingCity LIKE 'R%';

Redmond
Reno
Rio de Janeiro
Rome

The DISTINCT prefix prevents results from being duplicated.

That end with...

SELECT [columnas] FROM [tabla] WHERE [columna] LIKE '%texto';

Example

We search for all the cities that end with o.

SELECT DISTINCT BillingCity FROM Invoice WHERE BillingCity LIKE '%o';

Oslo
Cupertino
Reno
Porto
...

That contain...

SELECT [columnas] FROM [tabla] WHERE [columna] LIKE '%texto%';

Example

We search for all the cities that contain ew.

SELECT DISTINCT BillingCity FROM Invoice WHERE BillingCity LIKE '%ew%';

Mountain View
New York

Do you want to ignore uppercase and lowercase? Add COLLATE NOCASE at the end. Example: SELECT AlbumId FROM Album WHERE Title LIKE 'b%' COLLATE NOCASE;

Logical operators

SELECT [columnas] FROM [tabla] WHERE [columna] [> < >= <=] [valor];

Example

SELECT BillingCity, Total FROM Invoice WHERE Total < 5;

Stuttgart|1.98
Oslo|3.96
Frankfurt|0.99
Berlin|1.98
Paris|1.98
Bordeaux|3.96
...

AND

Chain several conditions. All of them must be met to produce a result.

SELECT [columnas] FROM [tabla] WHERE [condicion] AND [condicion] AND ...;

Example

SELECT InvoiceId, BillingCity, Total FROM Invoice WHERE Total < 5 AND BillingCity = 'London';

43|London|1.98
140|London|1.98
163|London|3.96
237|London|0.99
...

OR

If one of the conditions is met it will be valid. It could happen that you have 21 conditions of which only 1 is met, and it would be accepted.

SELECT [columnas] FROM [tabla] WHERE [condicion] OR [condicion] OR ...;

Example

SELECT InvoiceId, BillingCity, Total FROM Invoice WHERE BillingCity = 'London' OR BillingCity = 'New York' OR BillingCity = 'Paris';

8|Paris|1.98
11|London|8.91
19|Paris|13.86
43|London|1.98
54|London|13.86
...

BETWEEN

It allows us to search for a value within a range.

SELECT [columnas] FROM [tabla] WHERE ([columna] BETWEEN [valor] AND [valor]);

Example

SELECT InvoiceId, BillingCity, Total FROM Invoice WHERE (Total BETWEEN 10 AND 12);

298|Redmond|10.91
311|Salt Lake City|11.94
312|Lisbon|10.91
...

GROUP BY

When you have many rows, some of them with repeated values, with SQL you can group them by marking the dominant columns.

SELECT [tabla.columnas] FROM [tabla_1] GROUP BY [tabla.columnas]

Let's group all the songs by their price.

SELECT TrackId, UnitPrice FROM Track GROUP BY UnitPrice;

1    0.99
2819    1.99

It returns the first result of each group. Now let's count.

SELECT COUNT(*), UnitPrice FROM Track GROUP BY UnitPrice;

COUNT(*) | UnitPrice
3290   0.99
213    1.99

We have obtained a list where we count the songs by price.

It is also possible to group by several columns. Let's calculate the average song price of each genre, from highest to lowest.

SELECT GenreId, AVG(UnitPrice) AS price FROM Track GROUP BY GenreId, UnitPrice ORDER BY price DESC;

GenreId | price
19  1.99
20  1.99
18  1.99
22  1.99
21  1.99
1   0.990000000000008
7   0.990000000000007
3   0.990000000000005
4   0.990000000000005
8   0.990000000000001
14  0.990000000000001
9   0.99
5   0.99
11  0.99
10  0.99
25  0.99
23  0.99
24  0.99
12  0.99
17  0.99
6   0.989999999999999
13  0.989999999999999
15  0.989999999999999
16  0.989999999999999
2   0.989999999999998

If you want to find out the name of the genre, you will need to merge the corresponding table (Joins lesson).

HAVING

Think of it as a WHERE, or a conditional, for GROUP BY.

SELECT [tabla.columnas] FROM [tabla_1] GROUP BY [tabla.columnas] HAVING [condicinales]

Continuing with the last example. What if I want to know the average price per genre only when it is greater than 1?

SELECT GenreId, AVG(UnitPrice) AS price FROM Track GROUP BY GenreId, UnitPrice HAVING price > 1;

GenreId | price
19  1.99
20  1.99
18  1.99
22  1.99
21  1.99

Activity 1

• Order the genres table (Genre) in alphabetical order.
• Cut the results so that only the first 6 are shown.

Pro:

• Show the last 2 rows of the table.

Activity 2

We go back to using the Customer table.

1. Show the users who have reported more than 3 incidents (the column where they are counted is SupportRepid)
2. Filter the previous results by those who live in Brazil.
3. Show the users whose postal code starts with 7.
4. Show the users who have a hotmail email.
5. Show the users born in the United States (USA) or Canada (Canada).
6. From the previous results, show those who have a gmail email.
7. Show the user who works at Apple (the Company column). Be warned that you don't know the company's format, it could be: Apple SL, Company Apple, APPLE...
8. Show the users who have reported between 3 and 4 incidents.

Activity 3

We will use the Employee table (employees).

1. Count the number of employees there are per city.
2. Count the number of employees there are per department.

Pro:

1. Show the age of each employee.
2. Calculate the average age per department (Title).
3. Count how many employees were hired, on average, per year.

Solutions