Setup and Introduction to SQL Fundamentals

Create a Table with SQL Create

Add Data to a Table with SQL Insert

Query Data with the Select Command in SQL

Update Data in a Table with SQL Update

Removing Data with SQL Delete, Truncate, and Drop

Keep Data Integrity with Constraints 

Organize Table Data with Indexes

Select Grouped and Aggregated Data with SQL

Conditionally Select out Filtered Data with SQL Where

Combining Tables Together with SQL Join Statements

Subquery Dynamic Datasets in SQL

SQL Fundamentals

Databases are not really good for anything if we are not able to get the data out. By using the select statement, we can pull out all of the data within our database table. Let's work with different queries that pulls out all of our columns, just some of the columns, and even filters out duplicate column values. 

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.

Course

SQL Fundamentals

Query Data with the Select Command in SQL

Share this video with your friends