May include but is not limited to: LIKE, WHERE, ORDER BY, INTO
To retrieve data from an SQL database, you need to write SELECT commands. A minimal SELECT is as follows:
SELECT * FROM Table
Where * means all the columns in the given table. Typically, it’s not a good idea to retrieve all columns, because of the performance penalties, so you should replace the asterisk symbol with the concrete column names, like this:
SELECT Column1, Column2, Column3 FROM Table
So simple, so good. But generally, you need to filter your searches by some logic. The WHERE keyword does just this:
SELECT Column1, Column2 FROM Table
WHERE Column1 = 1
There are multiple further conditions can be applied to a WHERE clause, which forms a Boolean expression. These are the following: LIKE, BETWEEN, EXISTS, IS (NOT) NULL, CONTAINS. Let’s examine the LIKE clause deeper. It lets you find a matching character string in the column specified in the WHERE clause. It uses the following wildcards:
- %: replaces any number of characters in a string. An example: %ale which matches male, female and ale.
- _: replaces one character in the string. _ale matches male and pale, but not female or ale.
- : replaces any character within a specified range. Examples of use: [abc], [a-z], [0-3], [a, b, c].
- ^: matches any character which is not in the specified range.
The other operators are more straightforward, so I’d not waste more time on the WHERE clause, we’ll work with it for quite many times later.
The ORDER BY clause lets you specify an order to sort the retrieved data. You can sort by multiple columns, separating them with commas. There are two ways of ordering, ascending (ASC) and descending (DESC).
The last thing in this objective is the SELECT INTO statement. It looks like the following:
SELECT Column1, Column2 INTO Table2
And it creates a new table called Table2 from the Column1 and Column2 columns of Table1. Not so complicated, but don’t worry, it will be later.