Helps make queries snappier using an alias, and explains where aliases can and cannot be used within the query
- [Instructor] We're going to revisit a previous query and update it to make it quicker and easier to read. Now one thing I want to point out to you here is we have concat first name, last name here, and that is given as the column heading in the results set here, and similarly, length concat first name, last name is the column heading here. You'll be pleased to know there is a shortcut here. We can supply a column heading for the output here, which is called an alias, and we do that with the as keyword. So we can say as name, and then for this one, as len for length.
That's much better. Our column headings are nice and neat, but also this is useful for more than just presentation. We can use the alias names later in the same query, so here for instance we could say instead of concat first name, last name, we can say order by name, and that works just the same. Now what if we wanted to filter our results? We know that we can add a where clause here, so let's say where len is greater than 17.
Now I'll tell you now, this query is going to fail. Database tells us it doesn't recognize the column called len. In fact, you can't use the column alias in this way in the where clause, and the reason is the column value might not yet be determined when the where clause is executed, so the database software doesn't necessarily know what len is when it reaches this line of code, bu by the time it reaches the order by, it knows exactly what len is. We can still include the filter.
We just need a different approach, and that means copying the original statement in place of len like so. Now we can see that this one works. Aliases aren't case sensitive by the way, so I could change len there to be upper case, and even though it's lower case down here, when we run it, the whole thing still works. It's not good practice to mix them, but just to let you know that you can.
And we can apply an alias to a table name as well as to a field name. While we're on the subject of case, I've mentioned before that these keywords could be upper or lower case, and I'm just putting them in upper case to help readability. Now, depending on your database vendor and configuration, field and table names may be case sensitive. Generally speaking though, they're not. It's quite unusual to find that, so field names and table names and keywords, in fact the entire query, can all be written in lower case, and it will work just fine.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries