Lesson Weekend

In this lesson, we'll briefly explore some of the most common SQL clauses and operators.

SQL Command Format


The basic format for a SQL command looks like this:

SELECT <column> FROM <tables> WHERE <condition> AND <condition> ORDER BY <column> <asc or desc>;

Not all of these clauses are required but this is a template for what's possible.

SELECT


SELECT chooses the fields we want to retrieve from the database. We'll often use an * to denote we want to display all columns for entries our query returns. However, we could say something like this:

SELECT description FROM items;

This will return only data in the description column of our items table.

FROM


FROM specifies the table we are querying. Here's an example:

SELECT * FROM items;

Here we are specifying that we want to return all columns from the items table. We could do the following to retrieve data from a categories table instead:

SELECT * FROM categories;

WHERE


The WHERE clause allows us to filter our query further. For example, we could say the following:

SELECT * FROM items WHERE categoryId=5;

This will return all columns of data for all Item objects with a categoryId column value of 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

The following query will return all columns (*) for all entries in the kittens table where the age column does not equal 1:

SELECT * FROM kittens WHERE age <> 1;

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

SELECT * FROM kittens WHERE age != 1;

> Greater Than

This works the same as the > operator in C#. 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

This operator is the same as the < operator in C#. 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

This operator is the same as the >= operator in C#. 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

This is the same as the <= operator in C#. 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%';

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. When using the [charlist] wildcard, we need to use the regex operator, RLIKE. It operates in much the same way, it just has a little different syntax. Look into the documentation to see proper implementations for each!

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 RLIKE '^[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 RLIKE '^[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


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.

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

Note that the query looks exactly the same as our previous query other than the fact that it includes an ORDER BY clause at the end.

Here's another example. We can select all columns for all entries in the kittens table whose age value is greater than 2. Then we can order the kittens alphabetically by name:

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

ASC / DESC

We can also specify whether we want the order to be ascending or descending.

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 if the table might contain 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, and so on.

Lesson 15 of 36
Last updated more than 3 months ago.