Retrieving data

1. Retrieving data

Let's look at some more advanced methods of retrieving data.

2. Customizing output

In a real-world scenario, you may need to modify how data is displayed. There are a number of ways to accomplish this. In this video, you will learn how to suppress duplicates, manipulate strings, which are text values, perform calculations with numbers and dates using arithmetic expressions, and order data.

3. DISTINCT

By default, the results of a query are displayed without eliminating duplicate rows. This query displays all billing countries from the Invoice table. Notice that the country names are repeated. To eliminate duplicate rows in the result, you can include the DISTINCT keyword immediately after SELECT. Let's look at the results. Note that the Invoice table actually contains 412 rows, but there are only 24 unique countries in this table.

4. Manipulating strings - Concatenation operator

You can link columns to other columns by using what's called a concatenation operator. Operators are symbolic characters that specify the action to be performed. This operator consists of two pipes. Columns on either side of the two pipes are combined to make a single output column. In the example, FirstName and LastName from the customer table are concatenated. Note that when concatenating the columns, no space was added between them. We can fix this by using literal character strings.

5. Manipulating strings - Literal character strings

A literal character string is a character that is included in the SELECT statement that is not a column name. It is printed for each row. Let's look at the same example as before. We will add 'Full name' followed by a colon and a blank space, using empty quotes, between the first and last name. Note that character literals must be enclosed in single quotation marks, so make sure to always use single quotes and refrain from using double quotes when working with strings. Let's look at the output. This looks much better.

6. Manipulating strings - Alternative quote operator

But what if you want to include a single quotation mark in the character literal? Let's look at the example. If you would do the same as before, the second quotation mark is interpreted as the end of the character string and an error is shown.

7. Manipulating strings - Alternative quote operator

This is why you will need to use a quote operator, q, followed by a quotation mark delimiter. In the example, square brackets are used to denote the start and end point of the string but you can use any convenient delimiter. Let's look at the results. This is a really clear overview of employees and their titles.

8. Arithmetic expressions

So far, you have seen how to manipulate strings but you can also create expressions with numbers and dates by using arithmetic operators. An expression is a combination of values, operators and SQL functions. There are four arithmetic operators available in SQL. You can add, subtract, multiply, and divide.

9. Arithmetic expressions

Let's try to figure out what the duration of a track is in seconds. To do this you will need to divide the values in the Milliseconds column by a thousand. When looking at the output we can see the new column. Looks like this rendition of The Star Spangled Banner is a short one.

10. Ordering rows

The order of rows that are returned in a query result is undefined. ORDER BY can be used to sort the rows. In this query, the employee data is sorted by HireDate. Employees that were hired first are shown on top. Looks like the first employee was hired on April 1st 2002.

11. Ordering rows

To reverse the order in which rows are displayed, you can use DESC after the column name. You can sort query results by more than one column. In the ORDER BY clause, specify the columns and separate them using commas.

12. Let's practice!

Let's start querying!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.