Categorizing Your Output with 'AS'4:33 with Andrew Chalkley
You're not restricted to displaying the column names from the table at the top of your results. You can specify your own names for result sets.
SELECT <column name> AS <alias> FROM <table name>; SELECT <column name> <alias> FROM <table name>;
SELECT username AS Username, first_name AS "First Name" FROM users; SELECT title AS Title, year AS "Year Released" FROM movies; SELECT name AS Name, description AS Description, price AS "Current Price" FROM products; SELECT name Name, description Description, price "Current Price" FROM products;
See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.
Let's select everything from our books table again. 0:00 We have an ID, which is a unique identifier for the database entry. 0:03 This is a common practice and guarantees that every row is not a duplicate of 0:07 another, and can be isolated from all other rows if needed. 0:12 We have the title, author, genre, and first in school published. 0:16 Remember, querying a table's contents allows us to present 0:22 the results of a database in many different ways. 0:26 The results are designed for humans to read. 0:29 But sometimes the column names aren't that friendly. 0:32 Let's say I was running a query for the library administrator. 0:35 They want a report of all the books in the database along with the year that they 0:38 were first published. 0:42 The column headers are all in lower case. 0:47 This may be distracting to the report reader. 0:50 We can change the title of any column, by using the keyword, as. 0:53 After the as, you can write what you want to alias the column name to. 0:58 Let's use Title with an uppercase T. 1:02 When we run the query now we get the results back with a capital T 1:06 in the titles column. 1:10 How about the first underscore publish column? 1:12 We can use the as keyword again but with the words first published. 1:15 If you try and execute this query, you'll get an error. 1:23 Error: near "Published": syntax error. 1:26 The key for us here is that it's a syntax error. 1:29 Remember the syntax is the grammar or 1:33 the rules surrounding the way we write programming code. 1:35 The computer doesn't understand the word Published. 1:39 It's expecting a from or a comma, followed by another column name. 1:42 We can't use multiple words like this when writing sequel queries. 1:47 So what do we do for something such our first published dailies, in our reports? 1:52 There is a way we can use some punctuation, 1:57 which is part of the sequel syntax, to solve this problem. 2:00 We can define column names with spaces by wrapping them in quotes like this. 2:03 The opening quote is just before the first letter, and 2:10 the closing quote after the last letter. 2:13 But here's another got ya. 2:17 Writing SQL in different databases like Microsoft SQL, or 2:19 PostgreSQL my require you to use different punctuation for writing aliases. 2:22 Some require single quotes, others even require square brackets. 2:28 The SQL we're using in SQL Playground can use double quotes, but just be mindful. 2:32 SQL in different settings may have these slight variations in syntax. 2:38 Most database documentations have help to guide you around these difficulties. 2:43 When we execute this query now, we see title and 2:47 first published with a space as the column titles. 2:51 There is a short hand for this syntax, where you remove the as keyword. 2:55 You can try it too. 3:02 See? 3:05 It's the same results. 3:06 I avoid this syntax because it's not very clear. 3:08 I just wanted to bring you up so 3:11 you weren't confused if you saw it somewhere else. 3:12 To recap, to alias a column name to something of your choosing, 3:15 type SELECT <column name> the keyword AS, followed by the new name. 3:19 So, if I were to bring back all patron names in a more user-friendly format, 3:27 I could do something like this. 3:32 With quotes around the first name and last name. 3:34 These characters surrounded by quote marks are called strings. 3:42 Meaning strings of characters. 3:46 Letters, numbers, special characters and spaces. 3:48 One final notes about the as keyword. 3:52 When you bring back data with a new label using the as keyword 3:55 it does not change the name of the column in the actual database. 3:59 For example, when we aliased our first underscore published column 4:03 as first published in our report. 4:07 The column name is always first_published. 4:10 Only in our report do we see the column represented as First Published. 4:14 This is why we call it an alias. 4:18 As an aside, the as keyword can be used to alias table names for 4:22 more complex queries working with multiple tables. 4:27 We'll be covering that in another course. 4:30
You need to sign up for Treehouse in order to download course files.Sign up