SQL - Zero to Hero: Introduction to SQL Queries for Business

1. Course Introduction: Hi, I'm Jeremy. Welcome to my beginner's course on SQL. In this course, we'll be going through the basics of writing and formatting queries in SQL. Sql is becoming a required skill for many data-centric fields and for good reason. Instead of pulling reports from ancient software and manipulating it manually in Excel, SQL allows you to create customizable and repeatable queries that directly access the database and can be refreshed almost instantaneously. Sql will level up your skills in data analytics, boost your resume to the top of the list and make you a data machine without further ado. Let's move on to the course. 2. The SELECT Statement - Intro: In this section, we'll be walking through the first part of the query, the select statement. The select statement marks the beginning of a query and starts the syntax for selecting columns from the desired table. Next, we can add in the columns that we'd like to select from the table. There are a few different formatting options for the column selection portion of the query. For shorter queries, you can keep all the column names in line with the select keyword. But for longer queries, it's common to separate out the column names onto a new line, even adding indentation two column names to further organize, let's list out the columns that we'd like returned from the query. Adding a comma after each column. First is invoice date. Then we have customer, item number, sales, cost, and margin. One call out here if you would like to select all columns from the table, since you are either unsure what's in the table or truly need all columns, you can use an asterisk instead of listing out the columns. Once you've listed all the columns that you'd like to return, you now have to tell the query what table you are selecting columns from. To do this, we'll use the from command followed by the table name. In our case, we'd like to pull from the invoice table. So we will add from invoice to the query. Okay, great. Now we're ready to execute the query and see if we got the results we were expecting. Perfect, the result set pulled in exactly how we expected. Let's move on and learn more about column aliases. 3. The SELECT Statement - Column Aliases: In the last section, we learned about the basics of querying data from the database. All columns that we queried and returned in the results were the same as what they were listed as in the table. Well, this doesn't have to be the case. Sql allows us to customize column names to help organize the data and allow us to return a result set that is more useful to us or our end-user. Let's begin by bringing up the query that we were working on. In this query, we simply had the word margin listed as that is how it is listed in the table as well. What if we knew that the margin column in the table should actually be categorized as contribution margin. But it just wasn't set up that way. This is where we can use a column alias after the word margin. Let's add the command az, followed by the text contribution margin in single quotes. Let's execute this query and see what we get for results. Awesome. Now we have our margin column renamed to contribution margin. Now, if we go back to our contribution margin alias, I want to talk through another method that may be more common than what we used. Instead of using single quotes, which denotes a text string and allows you to add spaces or special characters. You can type the alias without the quotes. And instead of adding a space, just use an underscore to separate contribution margin. This allows you to skip creating a text string alias and keeps the code more readable. In my experience, text strings are preferred when you are sending the end-user a raw version of the query that has been exported. And standard aliases are preferred when you are going to do further processing of the data, but still need to clear column headers than what's in the original table. Now that we've clarified that our margin column in our table is actually contribution margin. Our end-user might want to know what the contribution margin percentages. If we look through the table on the left panel, I don't see anything that says contribution margin per cent. But SQL does have syntax to help us. Right below the contribution margin alias. Let's add another line. In this line, Let's add margin divided by sales, followed by the as command. And then our alias name, contribution margin per cent in single quotes. Let's quickly walk through what's happening here. We're having SQL perform a calculation. Sql is creating a column that is taking margin and dividing it by sales. Once that calculation is done, it is assigned to the alias contribution margin per cent. Awesome. Alright, let's move on to the next video and learn about the where clause. 4. The WHERE Clause: In the last section, we learned the basics of querying columns that we needed from the database table. We also learned how to customize columns so that we would have a clearer and easier to read results set. Up to this point, we have been querying all records or rows in the database for the columns that we requested. In this video, we're going to learn how to filter our results down to the exact data that we're looking for using the where clause. Let's start by pulling up our query. We'll begin by adding the where command on a new line after the from command. After the where command, we will add filters that we'd like to apply to our result set. The first filter, let's only show data for customer a. We can do this with the syntax customer equals a in single quotes. Before adding another filter, we will use the AND operator to provide separation between filter statements. Now we can add the second filter. For this filter, let's only show data where the sales are greater than zero. Since we don't want to include credits or returns in our result set. We will do this with the syntax, sales is greater than zero. Lastly, let's add one more filter where contribution margin per cent is greater than zero. We will do this by first adding the N command and then using the syntax margin divided by sales, it's greater than zero. Now, this last filtering statement may look odd to you. Why wouldn't we just use the contribution margin per cent column that we created previously. The reason that we don't is because calculated columns or column aliases can be referenced within the same query that are created in. I will explain more on when they can be referenced in future videos. Great, we have all of our filters. Let's execute the query and see if we got what we expected. Cool, Everything looks good. Let's move on and learn about joins. 5. The JOIN Clause: In the previous sections, we learned how to query data from one database table invoice. In this section, we're going to learn how to query data from an additional table with the help of the join clause. The join clause allows us to bring in data from outside tables through a common key or column. I want to emphasize that for adjoined to work, the outside table must share a common column with our base table. There are a few different types of join clauses. Inner join, left outer join, right outer join, and full outer join. Let's take a deeper look at each of these to get a better sense of when they would be used. The left outer join returns all rows from the base or left table and only matching rows from the joint or right table. The right outer join returns all rows from the joined or right table and only matching rows from the base or left table. The full outer join returns matching rows from either the base left table or the joined right table. The inner join returns rows that have matching values in both the base left table and the joined right table. In our query, we would like to join the customer table in with our invoice table so that we can access more details specific to the customers. For these purposes, we would like to join all records from both the base left table invoice and the joined right table customer. Therefore, we are going to use the inner join. The inner join begins with the inner and joined commands followed by the table that we are joining in customer. Next, the join clause wants to understand what columns from the joint and base tables are common and will be used as a bridge to access the join table data. We will be using name in the customer table and customer in the invoice table, as we saw above, the syntax for joining the columns starts with the ON clause, followed by either the base or join table. Dot column is equal to the base or join table.com. My preference is to start with the join table, but it doesn't matter in this case. Let's start with the join table. So the syntax will be customer name is equal to invoice dot customer. Now that we've joined the customer table in with the invoice table, we have access to all of the columns within the customer table. Let's go back up to where we selected the columns we wanted and add a few more. Now, since we're dealing with two tables of information or Syntax could potentially change slightly. If there are columns that exist in both tables with the same name, we must differentiate them somehow. And c equals answers. To use the syntax table dot column to clarify, unless there are duplicate columns between the two tables or more in later, more advanced queries, you do not need to follow this syntax, but I find it useful in a lot of cases since it helps organize the query. Let's add the customers city with customer dots city and the customers state with customer dot state, great. Let's execute the query and see if the query runs properly. Awesome, we got the results that we were looking for. There's one additional layer to the join clause that I want to point out. Looking at our query, we are joining based on one column from the customer table name and one column from the invoice table of customer. If e.g. joining based on one column doesn't provide a unique enough connection. Meaning there could be duplicate data by just using one column. You can join the new table with multiple columns. To join based on multiple columns in the joint or base tables, we would simply add the and operator and repeat the syntax for the second set of joining columns. There are also more complicated ways to join tables when you are using concatenated columns or nested queries. But we'll go through those in more advanced videos. Okay, let's move on to the last step where we can learn to organize our data. 6. ORDER BY Keywords: Welcome to the last and easiest section. In this section, we will learn the last step of the query, which will be organizing the row data in the results set using the order by keywords. Order by is as simple as it sounds. It is asking for the input on how to organize the data. If the query has no order by keywords, the data will not be organized in any particular order. We begin with the order BY keyword, followed by a list of column names separated by commas and either ASC or DESC for ascending or descending order for each column, the query will be executed an ordered by the order of the columns in the list. Note that if a column is listed without the ASC or DESC keywords, the column will default to ascending order. And that's it. You now have an organized dataset that you can export. I want to take this time to thank you for watching this course. If you like, the content, hit the Follow button so that you can be the first to hear about new course releases. Thanks again and have a great day.