Introduction to the SELECT Statement
A free video tutorial from Imtiaz Ahmad
Senior Software Engineer & Trainer @ Job Ready Programmer
4.6 instructor rating • 12 courses • 271,078 students
Learn more from the full courseMaster SQL For Data Science
Become a SQL query wizard and never be afraid to look at a large SQL query again!
09:03:36 of on-demand video • Updated November 2020
- Write complex SQL statements to query the database and gain critical insight on data
- Practice Every Step of the Way by Working Through 100+ Puzzles (with solutions)
- Transition from the Very Basics to a Point Where You can Effortlessly Work with Large SQL Queries
- Learn Advanced Querying Techniques
- Become a Master SQL Developer
English [Auto] Your hello they're welcome to this lecture in this lecture I'm going to explain to you about the select statement and I introduce that to you in the previous lecture. But I told you to not worry about it at the time. Now's the time to start delving deeper and understanding the select sequel statement. The select statement is a particular kind of statement it's often referred to as a query and the reason why they call it a query is because it's basically a question that you ask the database. It's information that you're trying to retrieve from the database and you formulate this select statement in such a way that you know you get the answer that you are looking for you get the information back the information that you were interested in retrieving. So this select statement is all about getting information and that's going to be 80 percent of the work that you perform as a data analyst or a data scientist. You have to become an expert at executing select statements in formulating these statements in such a way that you get the answer that you're looking for from the data. So that's what I'm going to talk about in this lesson and these statements can become arbitrarily complex they could be very simple and they can be very very complex and we're going to start from the simple ideas and you know build upon the select statement throughout the course and by the time you're done with this course you'll know everything there is to know about formulating good questions to ask the database. So let's get started here. Have the PGA admin tool opened up. Let's log in to our server that's supposed to press 10 and we can go to what we expand this further and go to the database that I created in the previous lecture and that was called course data. So once you click on it only then once you have to click on this first and then you are tools and then you open the query tool and this query tool is where we're going to be typing up all of the sequel statements. So you need to first select this particular database and then go to the query tool. Otherwise the query tool won't know which database you are trying to write the sequel statements against. OK. All right. So I introduced you to the simple select statement in the previous lecture and that went something like this. Select star from the particular table. So let's start with the Employees table and I'm going to just highlight this statement you can end it with a semi-colon. And that's a legal correct way of doing it. But if you're lazy like me sometimes you may end up ignoring that and we'll get into the details of why that's important later on. Don't worry about it for now. I'm just going to leave that out for now and highlight the statement and execute and you'll get to see all of the data that exists in this table. OK. You get all of the columns and pretty much all of the rows you could scroll down and you'll be able to see the rows that there are thousand rows of data. So we've got we've got a thousand employees and each employee is identifiable by the employee ID and you'll see that this is a unique number for every single employee. So what is the syntax. The select clause tells a database to retrieve information. All right. If this is the first clause in a sequel statement the database knows that OK whoever's writing this wants data to be retrieved. So that's what select means we're selecting data that we want to see. After that this star asterisk this is referred to as a wild card. And really this is taking the place of columns we can actually specify instead of this asterisk I can specify them. One of the columns such as employee I.D. or first name. Separating these two columns with commas and then finally if I wanted to get for example the department the employee works work. That's this column right here. So I'm selecting three columns and selecting the employee ID column which is right here. I'm selecting the first name column which is right here. And then finally I'm selecting the department column which is right here and I want to see only these three columns in the resultset. So when I run this query now and by the way when it gets this long of a query it's best practice to break it down into two lines like that. OK. And you can highlight this entire statement and hit the execute button. And now notice that we only get those three columns that we are trying to get information on the employee. The first name and the department. OK. So you could choose to either specify you know after the select clause you could choose to specify specific columns or you could just use the wild card which has this asterisk and that will give you all of the columns in that particular table. So when you were browsing around the database and trying to learn more about tables it's best practice to look at all the columns and you use this asterisk it's a nice shortcut. Otherwise you'd have to type out every single column of a given table and that could be tedious So if you want to just quickly get a glance at all the data on the table you use the asterisk. And when you want specific columns you state them right after the select clause and then after that. This from clause should be pretty obvious where you are selecting data from the employees table. OK I could change this. Instead of employees we've got another table that we created didn't we. We created the regions table so I can do select start from regions and if we highlight this this is going to get the data from the regions table. And of course I can you know change the columns that I want I can let's say I just want the region ID and the region name to be shown highlight that it execute and it shows only those two columns. Let's go back to the employees table for a second. Let me type in employees here. Now this region ID and region does not exist in employees self quite of course. If I run this query it's going to throw an error. Let me show you. Let's execute this and it's saying hey what does this region call them. It doesn't know where it's coming from because it doesn't exist in the Employees table. So let's actually retrieve columns that actually exist in the Employees table. So rather than typing them out I'm just going to do the star like that and hit execute. This gives me all the employees in the Employees table. So let's build upon this statement like I said this statement could get very very large and this is just the beginning. We're going to slowly build our vocabulary here in the select statement. The next thing is the where clause. OK. And this where clause basically filters specific records. Let's say let's say that you only want to see those employees that work in the sports department. OK. So this first record here Bernie. He works in the sports department and down here we've got the sports department again. Bertha works also in sports. And as you browse down you'll see a lot of employees working in the sports department. So if I wanted to create a query that only gives those employees that work in the sports department what I could do is in the WHERE clause I could say department is equal to sports and the way it's spelled here. Right. That's the exact spelling that I need to use of course. Right. Databases are not that smart. So you have to type in with the case sensitivity and everything you have to type in uppercase s and then lowercase sports like that. All right. Highlight this and hit execute. And there we go we get all the employees that work in sports and if you scroll down you'll notice that there's only 34 records or it's 34 employees work in the sports department and you can see right here the department is all sports some of the elitist sports I could change this and say hey I want to see furniture employees or its employees at work in the furniture department all they need to do is change that value to furniture and highlight this and how to execute. And now I'll get all of those employees that work in the furniture department. OK. And there's 43 of them that work in furniture. Now keep in mind that sequel The language is case insensitive meaning it does not care about the uppercase and lowercase of keywords. So the select is a keyword or it that's reserved by the sequel language. It's a clause. And I can change this to lowercase as well like that. And this query will run just fine. OK. As you can see and then this from clause I can you know do whatever I want here uppercase lowercase from. And it's going to work exactly as before. The employees table this is the name of the table and we can change this to say employees all in uppercase like that and this will continue to run successfully. The department column I could change just to say department in uppercase and this will run successfully. All right. So sequel is a case insensitive language but the data the data that's actually within these cells that is sensitive. OK. Data remember is the most sensitive thing in a database of course right. So for example if I changed this from capital F to lowercase f right. Even though we as developers know that OK we want all those departments or furniture to be shown here but the database is not going to understand it it's going to feel it's going to basically return no data. If this value doesn't exist in any of the departments. So let me show you let's highlight this and hit execute and boom there you go saying success we run total query running time blah blah blah. So it's saying zero rows affected. So nothing. No data was shown because the data that we're using here does not exist in the department column. OK. Only cells with the furniture with a capital F exist not with lowercase. So since sequel The language is case insensitive and the data is case sensitive. There are certain standards that developers have been using and you should follow those same standards. Sometimes I'm lazy and I lowercase things uppercase things when it comes to the actual language but of course the data is a very sensitive thing. I never mess up where I try not to mess up with searching for data and I always give the exact spelling and casing so that the queries work correctly. There is a standard that's used in the industry and that is these are reserved keywords such as select from where these are. These are sequel clauses or keywords. These should be in capital letters. Like that. OK there is no harm in not doing this but it's going to make your life as a developer easier. If the queries get large it's going to be easier to identify these keywords because they are capitalized. So select from where these are capital and the actual objects in the database such as you know tables table names. We won't capitalize them we'll leave them all in lowercase as well as the column names we'll leave them in lowercase. OK so this is a you know as you can see the query looks more pleasing compared to the way it was before right. It's just more organized this way. And when it gets more complex you're going to think this naming convention is standard because it's going to make it easier for you to read and understand queries that other people have written. And by the way I'm particularly lazy about including the semi-colon at the end but this is also a good standard that you should follow. Highlight this and hit run it will still run the query. It's good to have the semi-colon because if you have other queries following this one or other SQL statements to separate one statement from another. It's important to have this semi-colon. This marks officially the end of a particular statement. Now let's say that you're not sure about how furniture is spelled in a database and you may have you know you might be aware that other people may have misspelled the word furniture or lowercase some of the data that sort of thing. Instead of using the equal sign here you can actually use something called like. OK and this is not an exact match. It's a rough match and what's required in these quotes is a percentage sign like that. And within these percentage signs you can enter any part of the word that you are searching for so furniture. I could say an eye to you. All right. I know that this exists within the spelling of furniture. So if I highlight this and hit the execute button it's of course going to return me all the furniture's. All right. Now if I misspell something within this and I say and T right there is nothing with this spelling in this table right in this department column you won't find a word that contains this character set. So if you if you highlight this and execute of course no data is going to be returned. Now let me change this back to and I tell you this exists within furniture and outside of these parentheses I can put off with a capital right with a capital F and this still should work correctly. So let's highlight this and it execute and notice that it's able to pick up furniture. So what we're saying is give me all of those employees that work in a department that contains these characters. This character string or the first character should be a capital F followed by anything or this percentage sign refers to as a wild card. Any characters can fit into this and then it should. And in anything. All right. And then what it should have in the middle is this set of characters and I tell you this is important. All right. So this is of course going to work correctly every time. If I change this from a capital F to a lowercase f like this. Now we're going to have a problem like this and it execute and notice it's not returning anything. So let's continue on. We talked about how to filter words and you know character strings like this. And the next thing I want to show you is how to filter based on numeric conditions. So for example let's say you want to get all of those employees that have a salary greater than one hundred thousand. OK. Highlight this and hit execute and it's going to give me all of those employees that have a salary greater than a hundred thousand. And if you scroll all the way to the bottom you'll notice that we have to scroll to the left to get the wrong number and we've got 419 records here. So about half of our employees little less than half make over $100000. Let's see if there are employees that make exactly one hundred thousand. And for that we use the equal sign. So let's highlight this and hit execute. And there's no employee that makes exactly a hundred thousand in our data. So this greater than scientists is referred to as a greater than sign so what we're saying is we're salary is greater than one hundred thousand. Hopefully you remember this from math class. And then if we want those salaries that are less than this particular value we use the less than sign. So now we're searching for those employees that make less than $100000 salary. So let's highlight this candidate hit execute and let's scroll down to see how many employees make less than a hundred thousand and it's going to be five hundred and eighty one. So just like in math class you have less than or equal to. And you can do that by just using the equal sign right after the less than sign if you want to do greater than or equal to you just change that angle bracket in the opposite direction. So salary greater than or equal to this value salary less than or equal to this value you have those options here. All right so this is just the beginning of the select statement. We've got a lot more to cover and we're going to build on this going forward. So stay tuned. I'll see you in the next lecture.