Query data by using SELECT statements

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
FROM Table1

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s