Lesson Wednesday

As you've already seen, SQL is a really powerful tool. In this lesson, we'll briefly explore some of the most common SQL clauses and operators. We can use these to quickly pinpoint very specific information in our databases.

Read through this lesson to simply familiarize yourselves with the types of queries you can execute in SQL. Then, refer back whenever necessary.

SQL Command Format

The basic format for a SQL command looks something like this:

SELECT <expressions> FROM <tables> WHERE <conditions> AND <conditions> ORDER BY <expressions> <asc or desc>;

Now, not all of these clauses are required, for instance, you probably haven't used ORDER BY or AND yet, but this is a bit of a template for what's possible using SQL.

SELECT

Chooses the fields we want to display/return. We'll usually use an * to denote we want to display all columns for entries our query returns. However, we could say something like

SELECT descriptions FROM tasks;

to return only data in the descriptions column of our to_do database instead of _all columns that correspond to each Task.

FROM

Specifies which table SQL should look at. When we say

SELECT * FROM tasks;

...we return all (*) columns for all entries on the tasks table. If we said:

SELECT * FROM categories;

We would instead be instructing SQL to return all (*) columns for all entries in the categories table.

WHERE

The WHERE clause allows us to filter our query down to specific conditionals. For example, we could say

SELECT * FROM tasks WHERE categoryId=5;

to return all columns of data for all Task objects whose categoryId column reads 5.

WHERE clauses may contain the following operators:

= Equals

This is used to create queries seeking entries where a specific column matches the exact information from the query. For example, the following query will return all columns (*) for all entries in the kittens table whose age column equals 1:

SELECT * FROM kittens WHERE age = 1;

<> Not Equal

In some versions of SQL, this may be written as !=. For example, the following query will return all columns (*) for all entries in the kittens table whose age column does not equal (or, equals anything but) 1:

SELECT * FROM kittens WHERE age <> 1;

In some versions of SQL, this same query would be written like this:

SELECT * FROM kittens WHERE age != 1;

> Greater Than

This works the same as the > operator used in Java. For example, the following query will return all columns (*) for all entries in the kittens table whose age column contains a value *greater than 2:

SELECT * FROM kittens WHERE age > 2;

< Less Than

Same as the > operator in Java. The following query returns all columns (*) for all entries in the kittens table whose age column contains a value less than 7:

SELECT * FROM kittens WHERE age < 7;

>= Greater Than or Equal To

Same as the >= operator in Java. The following query returns all columns (*) for all entries in the kittens table whose age column contains a value greater than or equal to 9:

SELECT * FROM kittens WHERE age >= 9;

<= Less Than or Equal To

Same as the <= operator in Java. The following query returns all columns (*) for all entries in the kittens table whose age column contains a value less than or equal to 9:

SELECT * FROM kittens WHERE age <= 9;

BETWEEN

BETWEEN denotes a specific range with upper and lower limits. The word AND is included between the upper and lower limits of the range. The following query returns all columns (*) for all entries in the kittens table whose age column contains a value between 1 and 3:

SELECT * FROM kittens WHERE age BETWEEN 1 AND 3;

LIKE

The LIKE clause looks for a specified pattern. There are a few options with the LIKE clause. The basic construction looks like this:

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

The pattern part can differ. There are several wildcard symbols we can use to describe different patterns for SQL to search for.

% Wildcard

The % symbol is used as a wildcard before and/or after the pattern.

The following query returns all columns (*) for all entries in the kittens table whose name column contains a value beginning with s :

SELECT * FROM kittens WHERE name LIKE 's%';

Whereas the following returns all columns (*) for all entries in the kittens table whose name column contains a value ending with burt :

SELECT * FROM kittens WHERE name LIKE '%burt';

_ Wildcard

The _ symbol is a substitute for a single character.

The following query returns all columns (*) for all entries in the kittens table whose name column contains a value beginning with any single letter, but followed by "amson":

SELECT * FROM kittens WHERE name LIKE '_amson';

[charlist] Wildcard

The [charlist] wildcard allows us to set a list or range of characters to match. It can be combined with another wildcard character, too.

The following query returns all columns (*) for all entries in the kittens table whose name column contains a value beginning with either a, b, or s:

SELECT * FROM kittens WHERE name LIKE '[abs]%';

Additionally, the following returns all columns (*) for all entries in the kittens table whose name column contains a value beginning with either a, b, or c:

SELECT * FROM kittens WHERE name LIKE '[a-c]%';

IN

The IN keyword is used with the WHERE clause to specify multiple possible values for a column.

The following query returns all columns (*) for all entries in the kittens table whose color column contains either grey, or orange:

SELECT * FROM kittens WHERE color IN ('orange', 'grey');

AND

A little less common than WHERE, the AND clause allows us to add another criteria to WHERE.

The following SQL statement queries the database for all columns (*) corresponding with all entries in the kittens table whose name value begins with s AND whose value in the color column contains either grey, or orange:

SELECT * FROM kittens WHERE name LIKE 's%' AND color IN ('orange', 'grey');

ORDER BY

SQL's ORDER BY can be used to sort data returned in ascending or descending order based on a column. It is added to the end of a query.

We can use the same exact example query from above, but also specify the database to return the kittens in order of their ids:

SELECT * FROM kittens WHERE name LIKE 's%' AND color IN ('orange', 'grey') ORDER BY id;

Or, we can select all columns (*) for all entries in the kittens table whose age value is greater than 2, then order the kittens in alphabetical order by their names:

SELECT * FROM kittens WHERE age > 2 ORDER BY name;

ASC / DESC

We can also specify whether we're ordering by ascending or descending order, like this:

SELECT * FROM kittens WHERE age > 2 ORDER BY name DESC;

The query above select all columns (*) for all entries in the kittens table whose age value is greater than 2, then orders the kittens in reverse alphabetical order by their name values.

SELECT DISTINCT

We can use SQL to efficiently reduce our dataset down to only unique values. This is especially useful when we didn't prevent users from entering duplicates.

SELECT DISTINCT name FROM kittens;

The query above can also be combined with many other queries on this page - such as alphabetical sorting, WHERE, ORDER BY, etc.