SQL Tutorial: Ordering and filtering

Want to learn more? Take the full course at https://learn.datacamp.com/courses/introduction-to-sql-serverat your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---
We've seen how to limit the number of rows returned using TOP. Now, we'll learn more advanced filtering and sorting, using WHERE and ORDER BY.

Tables comprise of rows and columns, and you will usually see your query results displayed in a tabular format.

But we should bear in mind that queries return specific SETS of data that meet the conditions we specify , and that sets have no default order.

There is no guarantee our query will return the results in the same order each time.

However, we can make this happen using ORDER BY.

To order our query results, we write ORDER BY below FROM, then list the columns we want to sort by.

Here, we sort by year_intro, then the product_id column.

Notice rows 4-6, and 9 -10, have the same values in the year_intro column, so the product_id column determines their final row position

We can ORDER BY any column - including columns that do not appear in the SELECT part of the query.

Here we add DESC after the year_intro column in the ORDER BY clause. This sorts the results in descending order.

We can sort by multiple columns.
On the left,the columns are first sorted by year_intro in descending order, and then channels in ascending order.
Year_intro is listed first in the ORDER BY clause, so it takes precedence when sorting the rows.
Then the channels column is sorted in ascending order, for each year.
On the right, both columns are sorted in descending order.

We can also order string or text fields.
On the left we see name_alias sorted from A-Z, and from Z-A on the right.

You won't usually want to return all the rows in your table. You will have specific information you need to extract. For this, you need to use the WHERE clause, which we place underneath the FROM section of the query.

We use the WHERE clause to ensure we only return rows that meet our desired criteria, as shown in the following examples.

We can filter based on numeric values, and also strings or dates.
We use single quotes to specify the text or date value we want to use as our filtering criteria.

As with ORDER BY, we can use a column that is not in the main SELECT statement, as part of our WHERE clause.

You can test for non-equality using the left and right arrows together.

You can use BETWEEN to return values in a range.
This is inclusive - this query will return every available value from 20 right through to 30.
You can negate this by prefacing BETWEEN with 'NOT', meaning these values will be excluded from the results.

NULLs occur when there is no value for a particular field, for one or more records.

Some columns are not allowed to have missing values.

However, it may be perfectly valid for other columns to contain NULLs.

A missing value is not necessarily a zero value, and we may need to be aware of the 'missingness' of the record - NULLS help highlight gaps in our data.

It's very useful to know how to retrieve NULLS, using IS NULL and how to filter them out, using IS NOT NULL.

OK, we've covered a lot. Let's go and try this out!

#SQLTutorial #DataCamp #SQLServer