Query Data with the Select Command in SQL

Instructor: [00:00] With data inter table the quickest way to pull it all out is to write select star from user's. As you can see, we have two rows of data instead of our user's table. One of the most common tasks when working with SQL databases is to query the data from the tables using this select statement. The select statement is very powerful and has lots of clauses and functions that can be used with it.

[00:23] At a minimum, all that is needed is what columns we want to work with and from which table these columns exist on. The asterisk states that we want all the columns within the defined table. We can also select out specific columns by comma separating them.

[00:39] If we ask for a column that does not exist from our table, we're going to see an error. If we ask for a middle name from our user's table we'll see that the column "middle name" doesn't exist in our table. However, SQL databases such as SQLite, MySQL, and Postgres have open functions.

[00:57] We can use one of these functions within the query and our database will treat it as a column and input a value within each row. Even though current time is not a column within the user's table, it is a built-in function within Postgres so it's handling it for us.

[01:13] We can also alias our columns within the query. If we add, say, first name as first name, last name as last name, and current time as time, when we run this query we'll see that we've changed the way our column names have rendered. This is critical to know when working with packages within languages like C#.

[01:36] We need column names to match properties of classes. One common function you'll probably use a lot when working with SQL is the count function which simply returns the number of rows within our table. It doesn't matter if you use the star or any combination of columns as long as they exist within the table it's going to give us a count of rows.

[01:57] Finally, we have the ability to do some filtering of data when pulling it out at the column level. Let's insert another set of data where the first name and last name matches data that already exists. For first name and last name, we're going to insert Tyler Clark.

[02:13] If we selected our data from our user's table we'll find that we have two rows that have the matching first name and last name of Tyler Clark. If we use the built-in distinct function on the first name column of the user's table we'll see that we only get two rows back. It's removing the duplicate, Tyler, from the table. The same thing can be done for last name.

[02:38] What's great is SQL gives us the ability to combine functions together so we can get a count of all the distinct last names within our user's table, which is two and compare that against all the rows within our user's table, which is three.