Retrieving Specific Columns of Information2:53 with Andrew Chalkley
You don't have to retrieve all the information from a table. You can retrieve only the information you want from the columns you want and filter out the rest.
Retrieving a single column:
SELECT <column name> FROM <table name>;
SELECT email FROM users; SELECT first_name FROM users; SELECT name FROM products; SELECT zip_code FROM addresses;
Retrieving multiple columns:
SELECT <column name 1>, <column name 2>, ... FROM <table name>;
SELECT first_name, last_name FROM customers; SELECT name, description, price FROM products; SELECT title, author, isbn, year_released FROM books; SELECT name, species, legs FROM pets;
See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.
In the previous video we used the select star from table query to retrieve all 0:00 information in a table, but you may not want to bring back all the information. 0:05 Often you want a small subset of information. 0:10 This can be particularly helpful if you don't want to bring back sensitive 0:13 information. 0:17 Like the addresses or dates of birth. 0:18 Let's take a look at some simple ways to retrieve only some 0:21 of the information in a table. 0:24 Let's start with all the information from the patrons table. 0:26 And we can filter it down from there. 0:30 The asterisk, or star, is short hand for all of the column named in the table. 0:34 Now, what if I wanted to retrieve only the emails from our patrons table? 0:39 We still want to select something from our patrons table. 0:44 So our query mostly stays the same. 0:48 All we need to do is change the asterisk, remember? 0:50 An asterisk is shorthand for everything. 0:54 We can replace it with the column name we want. 0:57 In this case, it's email. 1:00 As you can see this selects all the emails from our patrons table. 1:05 Let's try one more. 1:10 What if we wanted all the first names in our patrons table. 1:12 You can probably guess what we need to do. 1:15 You just to write the name of the first name column first underscore name 1:17 between the select and from keywords. 1:22 Okay, let's change that back. 1:27 Having either all columns or a single column returned back has limited utility. 1:33 Remember, the power of SQL is that you can retrieve any subset of information 1:38 from a table without being destructive to the underlying data. 1:43 Nothing is deleted, it's just presented differently. 1:47 Let's say I wanted to send out a library newsletter talking about the new book 1:52 available to loan. 1:56 How do I go about retrieving both the first name and 1:58 email address of each patron? 2:00 In other words, 2:02 how do I select two columns of data from the database with one query? 2:03 SQL makes this kind of task pretty easy on us. 2:08 I can simply separate the name for 2:11 each column I want with a comma in the SQL query. 2:13 The great thing is that I can change the ordering of the columns how I see fit. 2:19 Since the recap, the area between the SELECT and 2:34 the FROM keywords is the columns you want to get the information from. 2:37 SELECT * FROM patrons is a lot shorter than including all of the column names. 2:42 You're a better person than me if you can remember all the column names. 2:46 Let's do some challenges to solidify what we've learned here. 2:49
You need to sign up for Treehouse in order to download course files.Sign up