From the course: Learning Data Analytics: 1 Foundations

Learning basic SQL statements

From the course: Learning Data Analytics: 1 Foundations

Learning basic SQL statements

“

- [Presenter] When learning data and certainly working with data, you will encounter SQL, pronounced sequel. This stands for Structured Query Language. It's an international standard for database manipulation where most analysts encounter SQL is that data tools have a version of SQL syntax that runs in the background with their data. And as you grow your skills, you may even be writing SQL queries yourself or using a GUI, Graphical User Interface that generates that program's version of SQL statements. There is so much more to SQL than just the basic select statement we will learn here. But as a data analyst and especially at the beginning, understanding a basic SQL query structure can certainly help you. I want to introduce you to four basic SQL statements for a select query. But before I do, let me give you an example of querying that we do in Excel all the time. You receive a spreadsheet and it has way more columns of data than what you actually need. You will go in and you will start to delete those columns so that you're just working with the data you need. This is a variation of a query. You're just going to end up with the information that you need and exclude all the other information. Okay, let me show you how we would do this in SQL. And we'll start with the two required statements of a select query, the SELECT and the FROM statement. Anytime I'm teaching new analysts, how to build their first SQL statements. I start with the SELECT statement and the FROM statement and I always make them start in notepad. I'm less concerned about the syntax because there are variations of syntax for SQL based on the program you're using and more concerned about the structure and understanding what each statement will do. So we'll start with the SELECT statement. So I'll type, SELECT and again, I'm going to list the fields that I want. These would be the columns that we would leave behind in our Excel spreadsheet. And instead of deleting columns, we just would never include them in the select statement. So I want my SalesOrder ID, my next field will be my OrderDate, my next field will be my DueDate, my ShipDate, my SalesOrderNumber Because I'm in a notepad, I'm just go ahead and press Enter here. I also want to include my CustomerID. I want to include my SubTotal, my TaxAmount, my Freight and my TotalDue. Now it's not enough to have a SELECT. We also have to have the FROM and FROM is where we put the table or view that the information is stored in. So I'll go ahead and press Enter. So in our example here, we're going to use SalesOrder header because that's the name of the table that holds these fields. Okay, simple enough, right? So the SELECT statement gives us the list of fields to include and the FROM statement tells the SQL statement where to look where it's from. Let's talk about another common statement, the WHERE. The WHERE statement is a filter. So I'll go to my next line and I'll type WHERE. I only want to say orders where the total due is greater than 5,000. So I'll type TotalDue is greater than 5,000. So now I'm pulling just the fields in the select from the table called SalesOrder header where the total due is greater than 5,000. One more important statement is the sort order which is the ORDER BY statement. So I'll do ORDER BY, I want to actually order this by date. So I'll go ahead and type OrderDate. If I also wanted to include an additional sort, I could type comma and let's just say OrderDate and CustomerID and it would sort in the order of the list. So it would sort OrderDate and then CustomerID. Okay, maybe we want it to be opposite of that, maybe we want our customers to be sorted first. So I would do CustomerID and then OrderDate. Okay, there is another type of select statement. Go ahead and enter a couple of times. I'll do SELECT all. So the asterisk represents all this means whatever table I put in the from, it will pull literally every field. But if I wanted them sort, I could go ahead and include it as well. One key thing to remember is that the order of these matter as well. I can never put the ORDER BY and then the WHERE. I would always want to be sure that I've selected it, tell the statement where it's from, filtered the information and then I sort it. Just a few things to point out. SQL is a standard and the syntax for each program may vary a little not unlike a dialect of a language. There are some consistency in the statements like the SELECT and FROM and the WHERE in ORDER BY. Remember, only SELECT and FROM are required WHERE an ORDER BY are optional, and don't forget the order you have to do WHERE before you do ORDER BY but you don't have to have either one or you can have one or the other. The rules of your organization will dictate to you whether you can write your own SQL statements. It depends on which software you have. However, you should be able to read them because they provide vital information on where the data is stored and which fields you might need. If you want to learn more about SQL querying, check out some of the other courses on SQL in the LinkedIn Learning Library.

Contents