Reading data with SQL - Amazon Web Services (AWS) Tutorial

- We're going to talk about the SELECT statement in this episode. The SELECT statement is used to read data from your databases. You can read from one table or from multiple tables, depending on your needs. Now there just a few major SELECT statement structures you need to understand in order to get started using them. They can be way more advanced than this, but this gives you a starting point to comprehend what you can accomplish with this powerful tool. Now, do keep in mind when you're using a SELECT statement, the word SELECT is very important here, because what you're doing is saying to the DBMS, select the data I'm about to describe for me. So you start with SELECT and then you describe what you want, from where you want it, and how you want it to be structured. And the database pulls all that information for you, and then returns it as what's called a recordset or result set. So let's take a look at what SELECT can do for us. So I actually am using the MySQL Workbench and I'm connected to an EC2 instance in AWS. So I happened to be connected to an EC2 instance that's running MySQL, and I'm using the Workbench on that same instance to work with it. Now, don't worry about how I got all that set up, we'll be looking at that throughout the course. Right now, I just want you to understand the actual SQL language and SELECT statements. So, the first thing I want you to know is I'm using a database here called sakila, and this is a demo database, a sample database for MySQL that comes right from mysql.com. And you can use this to be able to look at various information. Actually, when you install MySQL on a computer, it asks you if you want to install this sample data. If you say yes, it puts it on there for you, and it's there. So if you want to get this set up for yourself, launch EC2 instance running Windows Server in AWS, and then download from mysql.com the MySQL installer package. And when it asks if you want to actually have these sample data sets, just say yes, and it'll put it in there for you. So what I can do is expand tables here to see what I've got. And you can see, it looks like I've got some kind of a business, so I have staff, I have rentals and payments, I have inventory, and it looks like there's a film actors, categories, and texts, and country, and address. So, there's actor again. And if I look at film, you can see when I expand that out, that I have different columns, and the columns are description, release_year, original_language, length. Okay, so it looks like, maybe since there are rentals, I'm a movie rental company. Okay, so, you can get an idea of what it is by just looking at the data. Now, what we're going to do is run some queries against this data. So notice my very first query, probably the most simple query that you can write in SQL is a SELECT statement. SELECT * FROM sakila.customer; Well, let's break this apart. It's a SELECT statement. So I'm saying to the MySQL database, select something for me, asterisk, you may know from other systems you've used, the asterisk is a wild card. So I'm literally saying select everything. From where? Well, that's what the FROM clause does for me. FROM sakila.customer, what's that mean? Well, from the database named sakila or the schema called sakila in terms of MySQL, and the table called customer. So if I come over here and look at the customer table, expanding that out, and then I look at Columns, you can see the columns are customer_id, store_id, first_name, last_name, email, address_id, active, create_date, and last_update. And when I run this query, I'll simply highlight it. And then the lightning bolt here says, execute the selected portion of the script. So it only runs what I've highlighted. And notice, what do you know? Those exact columns that we saw over here on the left are actually now shown right here in my middle result set. So you can see that when I said select everything, it meant all of the columns, okay? The next SELECT statement, it says, SELECT first_name, last_name FROM the same location, sakila.customer. Well, if I run that, you'll see, I only get the first name and last name. So I've, instead of selecting everything, I've said, no, just give me the first and last name. Well, what if I don't want every record? What if I'm just interested in those with the last name that starts with S? Well, I could say SELECT everything FROM sakila.customer WHERE last_name is LIKE 'S%'. So what I'm saying is everything that starts with S because the percent sign is a wild card for any number of characters. So when I run that, you'll notice that I get all of these with the last name of S. Okay, well, what if I'm really interested in the email address as the filtering element? In other words, I'm saying, give me everything from sakila.customer, but only where the email starts with SCH. That's what I'm saying here. And you will notice in the results that we had before, there are some that start with SCH, if we were to scroll through this result set. Right here, you see Schilling, and Schmidt, and so forth. But notice I've got an extra line here, ORDER BY store_id. So notice over here is not ordered by the store_id, right? It's one, then two, then one. So, I'm saying, yes, get me everything from sakila.customer where the email has SCH as the starting string or a string inside of it, but then order it by the store_id. So sort it by the store_id. When we run that, you can see that all of the ones are first and then the two. So that also tells you that MySQL by default, when you say ORDER BY sorts ascending. If you wanted it to sort descending, you could put a DESC after store_id here, and it would accomplish that. All right, let's take look at something else. What if I'm not going to find everything I want in one table? What if some of what I want is in the customer table, and some of what I want is in the address table? See, I might need to join those together. So, yes, the email address is in the customer table. We see that column right here, but then there's this thing just called address_id. Well, if we come up here to the address table, you'll see that address_id again. And then we have an address, a city, a postal_code, and so forth. Okay, so if I want to get portions of their address as well, I need to get everything from customer and address. And that's where I get the magic of INNER JOIN. This joins two tables together. What I do is I say, give me everything from sakila.customer, space, and then some variable. I could have called this horse. I could call this anything I want. I've called it c1. And then I say, INNER JOIN sakila.address c2. Again, I could have called this cow, anything I want. I called it c2. And then what am I joining it on? C1, which is customer.address_id equals c2, which is address.address_id. So I'm saying join together all the records where the address_id is the same. And that brings everything back together into one result set. Let's see if it works. Are you holding your breath? That's always a good idea when you write a SELECT statement and you're not quite sure if it's going to work. We click the lightning bolt. And look at that, we have first_name, last_name, email address, the actual address of the user is right here, the district they're in, the city_id, which by the way, tells you if you really want to know the actual city instead of a number for the city. You've got to go somewhere else, right? City_id, well, it matches up with city table. And then there's a country_id that matches up with country table. So to really get the full set of their address into one result set, you need to join together four or five tables in this database. Okay, I told you, sometimes you want to hold your breath, just wait and see if it works, right? Well, I'm going to tell you a little secret. When I typed that SELECT statement, the first time, it didn't work. I hadn't typed that exact desired statement against a MySQL server in a little while when I typed that in. So I had to go and figure out, I'll know what did I type wrong and fix it. That's okay, you're never going to hurt data with SELECT statements. They are read only. Type away at them, have fun with them, use them and learn with them. And then the more you use them, the better you get at remembering how to use them.