Find information about the data

- [Instructor] Instead of just getting back records that match our queries, we can use some other functions and commands to change how the data is represented and to find out more about the data itself. First, let's take a look at ways of describing the data by finding out how many records a response contains and how long certain fields are. I'll write a simple query here to get the first name of everyone in our database. Select first_name from people.

Alright, but let's add first_name here again and wrap it up with the length function which will tell us how long the information in a given field is rather than the value of the field itself. Here I can see Janice's name is six characters long. Wanda is five, Laura is five and so on. We can use that on some other fields too if we wanted to explore a little bit. It can be helpful to get a listing of unique values in a field as well.

We have a thousand rows here, but I know there is many people with the same first or last name. Let's take a look at only pulling out the unique values, or the values that are distinct from one another. With a function called distinct. I'll change my query and use the distinct function on the first name field, and when I run this, I have 198 rows.

This has filtered the information that I get back from the database, and I have only one result for each unique name in the database. If it finds a second instance of a name that it's already seen, it just ignores it. So I get one result if there's one name, and one result if there's a hundred records with the same name in the field that I'm asking about. I can make that a little bit more clear with an order-by clause here at the end, to double check that we don't have any duplicates.

This is also helpful by oh seeing what all the values in the given field might be. Names of course will vary, but if we wanted to check that we really do only have, say shirt and hat in the field called shirt or hat, we can take a look at the distinct values there. I'll change to that field, and I'll get rid of my order-by clause. I'll run the query, and yeah, sure enough, only hat and shirt exist in that shirt or hat field.

We can also use the count function to see how many records match a given criteria. If I wanted to count how many people are from California in our database, I could write select count star from people where state equals California. And I see that I have 127 records where the state is California.

I can use the star here inside of the count function, because the count function gives us the number of rows that match a condition. So I don't need to specify a particular field in there if I just care about the number of responses. In this example, I get back the number of rows that match regardless of which field I asked for inside of the count function. We'll use count more later on. And it'll start to become more useful combined with some other approaches.