The convention is to capitalize the actual SQL keywords. Differentiate them from table and column names. This makes it easier for people to read the statements

SELECT

SELECT column_name FROM table_name

The most commonly used statement in SQL and is used to retrieve information from a database. It can be combined with other statements to perform more complex queries.

If you need more than one column, separate the column names by a comma. Example: SELECT column1, column2 FROM table1

If you need the entire table, replace the column name with an asterisk (*). This will query everything, which means increased traffic between the database server and your application. This slows down your getting your results

SELECT DISTINCT

SELECT DISTINCT column_name FROM table_name

or

SELECT DISTINCT(column_name) FROM table_name

This lists only the unique values within a column. If the column has three values that are the same, the result will only show one of those values. For example, if a store sells many colours of pants, the DISTINCT SELECT keywords will return all the different types of colours under the column, inside the table. This answers the question: WHAT ARE THE UNIQUE AMOUNTS

COUNT

SELECT COUNT(column_name) FROM table_name

This returns the number of rows that match a specific condition. Because on its own it will simply return the number of rows in a table, COUNT is best used in combination with other SQL keywords.

SELECT COUNT(DISTINCT column_name) FROM table_name is a better use of COUNT, as this statement would give us the total number of columns that had unique values. It will not tell us what the values are, but it tells us how many unique values there are. This answers the question: HOW MANY UNIQUE AMOUNTS ARE THERE?

SELECT WHERE

SELECT column1, column2 FROM table_name WHERE conditions

WHERE allows us to specify conditions on columns for the rows that we want to be returned. It filters out the rows that do not meet those specific conditions.

Comparison Operators

OPERATOR	DESCRIPTION
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
!=	Not equal to

Logical Operators

These allow us to combine multiple comparison operators

AND

OR

NOT

ORDER BY

SELECT column1, column2 FROM table_name ORDER BY column1 ASC/DESC

This sorts rows by column value. This can be in either ascending or descending order. This is for both alphabetical and numerical values.

The default sorting is ascending if none is specified.

If you order by more than one column, then the first sort will be by the first column that was stated, then within that first sort, a secondary sorting will occur based on the values in the second sort column.

You do not need to SELECT a column for you to be able to use the ORDER BY query on it, but it is not recommended. Makes better business logic to SELECT all the columns that you will ORDER BY

Example: SELECT name, surname, mark FROM grade_8_a ORDER BY surname, mark

The above query will sort the learners in Grade 8A bur their surnames, and if any learners have the same surname, those will be sorted by their marks.

The ASC or DESC can come after each column name if you want to sort each by a different rule. As in you want to sort the first column in ASC order, while the second column should be in DESC order

Example: SELECT name, surname, mark FROM grade_8_a ORDER BY surname ASC, mark DESC

LIMIT

SELECT * FROM table_name ODER BY column_name ASC LIMIT 5;

This command lets us limit the number of rows returned for a query. Useful for seeing the general layout of a table. It is also useful when combined with ORDER BY. This gives the top search results for a given query, based on the ORDER BY statement.

It goes at the very end of a query because it is the last command that should be run.

BETWEEN

SELECT * FROM table_name WHERE column_name BETWEEN value AND value;

SELECT * FROM table_name WHERE column_name NOT BETWEEN value AND value;

This measures a value against a range of values.

Value BETWEEN low AND high is actually the same as WHERE value >= low AND value <= high.

Value NOT BETWEEN low AND high is actually the same as WHERE value <low OR value > high.

BETWEEN is inclusive, while NOT BETWEEN is exclusive of the values given.

IN

SELECT column_name FROM table_name WHERE column_name IN (‘value’, ‘value’);

This is used when you want to check for multiple possible options. It checks if a value is included in a list of multiple options (NOT IN can also be used to check if the values are not included). This returns all the values that meet the criteria

LIKE and ILIKE

These are used when we want to match against a general pattern within a string. We can use wildcard characters to match against different sequences of characters.

-Percent % matches against any SEQUENCE of characters. Eg: WHERE name LIKE ‘A%’ gives all the names that start with a capital A. WHERE name LIKE ‘%a’ gives names that end with an a. The number of letters before or after doesn’t matter.

-Underscore _ matches against any SINGLE character. Eg: WHERE title LIKE ‘Harry Potter ’ will return results that have a single character in the position of the underscore. Like ‘Harry Potter 1’.

We can also combine these to get more complex results.

-WHERE name LIKE ‘_her%’ will give results from ‘Cheryl’ to ‘Theresa’. As long as there is one character before ‘her’ and unlimited characters after the ‘her’

LIKE is case-sensitive. ILIKE is not