SQL Bootcamp - Episode 1 - HackerRank

Description

Learn basic SQL Server coding concepts. Commands taught: SELECT FROM WHERE.

Transcript

Today we're going to be practicing our SQL skills with the hacker rank website SQL stands for structured query language and it's used for getting data out of a database when you first go to the hackerrank.com website hackerrank.com you'll notice that it has a section for candidates the rest of it is for companies because companies use this for hiring people we're just going to use the candidate section so first you'll want to create an account or log in and it's totally free so don't worry about that okay I'm logged in and you can see I have a list of topics I can practice today we're just going to be doing SQL but if you're interested in other programming Technologies you can practice those here as well once I click on SQL I have a list of challenges the first ones on the list just say easy if I scroll down eventually I'll get to medium and hard challenges as well so we'll just start with the top one when it opens up you'll see on the left side the question I'm supposed to solve and on the right side where I type my answer and this is not just a normal text field where I can type an answer it actually is connected to a real database engine so I can actually execute the code and see what results I get so that I can verify if my result is correct and I can debug if my result is wrong to figure out what I'm doing wrong the first thing you're going to want to do when you open up a new challenge is on the top right corner pick which database engine you want to use it does matter which one you pick because the syntax is slightly different the SQL language is fairly standardized but there are slight differences between them so if you're familiar with a specific syntax and you pick the wrong engine the commands you're used to using are not going to work now if you've never used SQL before you probably don't know which one is the best to pick in general I would say avoid db2 because that one's not very popular any of the other three you'll be fine with and my personal favorite one is actually called postgres and it's not on this list so you can practice and hear with that but if you downloaded postgres and practice with it on your own you could do that as well but if you're just learning as a student and you're hoping to get a job rather than picking what's your favorite favorite which one's the funnest is probably best to think about the job opportunities so the way you would do that is in a separate browser tab I'm going to pull up indeed.com and you'll notice I can search for a specific technology to see what jobs are available so if I pick db2 you'll see there's 4 000 jobs so it's not that it's not used at all but that's not a very high number because that's searching the entire United States if I put in MySQL I have 17 000 jobs so it's about four times as many jobs so I guess that could mean you have a four times higher chance of getting hired but you might also have more competition so there's not necessarily any guarantee because maybe there's more programmers also know MySQL SQL Server which is Microsoft's it's more popular than MySQL it has 30 000 jobs and Oracle is even higher than that it's at 65 000 almost and the one I prefer which is postgres is about as popular as MySQL so it has 10 000 actually a little bit less of MySQL the reason I prefer postgres both postgres and MySQL are free so that's very useful if you're doing a personal project SQL server has a free version but it's limited so if you're doing a startup company it would work great for a year or so and then once you start making money you would need to use the more advanced features and you'd have to start paying whereas postgres and MySQL are free forever I honestly don't know as much about Oracle so I don't know if it has free options but the crazy thing to me with postgres is I actually think it's more powerful and better than SQL server in most ways there are a few downsides to it compared to SQL server but in general I like it better even though it's free so in my case I'm going to pick Microsoft SQL Server because that's what I'm most familiar with and the reason I'm most familiar with it is because I'm a full stack programmer and so I do websites mobile apps and backend code and I typically stick to the Microsoft stack for the entire stack so for the front end I'll use typescript instead of JavaScript because that's Microsoft's version of JavaScript which I think is actually a little bit better than the traditional JavaScript and then for the back end I'll use C sharp which is Microsoft's version of C plus so because I'm sticking with only Microsoft Technologies for the entire application it just has a little bit better integration so that's why I've chosen not to use Oracle most companies I've worked for because they use Microsoft they use Microsoft across the entire stack one other thing you might want to consider so maybe you're wondering should I learn databases or should I learn web app development or mobile app so programming is similar no matter which technology you're using the SQL syntax is very different than the C plus syntax because ones for databases and once for just normal applications but the way you learn to think logically about how to solve a problem that programmer mindset it's going to be similar and once you've learned one technology it's not too hard to learn another technology however something to think about is if you're first starting out you kind of want the lowest barrier to entry meaning that you don't want to pick something too complicated where you have to learn too many Technologies for example a full stack developer has to know front end and back end because they're doing both parts of a website and backend also includes databases so every programmer that does back-end or full stack also has to know SQL but there are jobs that are only SQL so if you start with just SQL you could get your first job and then afterwards you can learn back-end Technologies and get a job that uses both and then you can learn front end and get a job that uses all three or full stack so that's just an option if you don't want to bite off more than you can chew because if you're going to choose to just start with backend you're still going to have to learn SQL anyways so in order to help you think about this a little bit more I'm going to pull up a job salary guide so this is from a recruiting company called Robert Half so they work with a whole bunch of different companies and they try to find jobs for people and because of that they have a good idea of how much people get paid in each job and so if I scroll down and so you can download this it's just a PDF document from their website this is actually from the year 2021 so it's it's a year old um but if I scroll down okay here it lists job titles so you've got software developer which is someone that makes applications and the 25th percentile is going to be either someone that's a beginner or someone that just happens to work for a company that underpays their programmers and it's saying that the national average in the US is 97 000. so the 95th percentile would be someone that's really an expert in that field so maybe they have a lot of years experience or maybe they just happen to be working for a company that just pays really really well um and that's saying it could be up to 165 000. so that's kind of your range so um and there's very similar job titles so software engineer software developer there's honestly not really a difference between the two but people just like different terms and that's why their salaries are fairly similar but there are jobs that are very different so software developer or engineer is generic they could be doing websites that could be doing back-end apps that could be doing mobile apps it's not specialized usually the more specialized you are the more you get paid so mobile apps they don't have to do websites and they don't have to do you know back-end or database code necessarily but because they're specialized in one thing they actually get paid a little bit higher so the reason I bring this up we're actually talking about databases today and I mentioned that if you're going to be a full stack programmer you still have to learn SQL but you could get your first internship job with SQL only without having to learn the entire full stack of you know back-end language like C sharp and a front-end language like HTML CSS and JavaScript so for a database someone that just only knows SQL maybe that would be like a reporting analyst or a report writer they do have a slightly lower pay scale than what the software developer showed but it's still fairly close so that might be a good starting point if you just learned SQL and then you could if you decide you want to learn other types of programming languages you can move on from there so I think it's best to start with a goal in mind before you start studying because your path is going to be better you're not going to waste your time studying things that don't necessarily help you okay back to SQL so a database is essentially just storing a whole bunch of information and in this case the example it's giving me first let me change to Microsoft SQL Server before I forget the example data it's giving me is a table called City and it has Fields another day for that is columns and each column is like a variable so it has a data type so a city has an ID and an ID is a unique identifier that is easier for the computer to think about it so a concept or a record so an example for that in the US would be a social security number every person has a social security number and that's the way that they're uniquely identified and every database in the world when it adds your name to the system it's going to tie it to either something unique like social security number or like email address or like phone number because there might be another person with your same exact name and it needs to be able to distinguish between you so an ID field is like a unique identifier and so it's a number because part of what makes names difficult or ambiguous non-unique is the fact that they're letters and also letters are harder for a computer to process than numbers because computers are calculators behind the scenes so they process numbers faster than letters so anyways an ID because it's a number generally just automatically increments so the very first record you create so in this case City if the first city I create is Los Angeles it's going to be id1 if the Second City I create is Las Vegas it's going to be ID number two and then country code it says it's so varchar that stands for variable length character a character is one key on your keyboard so like the letter A or the letter B by variable length it means where it has in parentheses the 3 or the 17 that's the maximum length it could be so the city I don't know how long Las Vegas is let's count it out

okay Las Vegas is nine characters long so that fits within the 17 so that's totally safe it doesn't have to use the entire 17 that's because it's variable length um but if I tried to put in a super long city a city name like I don't know maybe Rio de Janeiro or something if it's longer than 17 it will truncate or cut off the end and so country code is three so that could be something like USA I'm not honestly sure what district would mean um and that's something to every company is going to have their own database and they're going to define the columns whichever way they want based on how their business runs so sometimes they're going to be columns that you don't know instinctively what they mean and you're going to have to ask a co-worker and it's just kind of tribal knowledge that gets passed down through generations of working at the company um so so I'm not sure what district means but hopefully the instructions will tell me if I need it and what to do use it for so obviously population is just going to be the number of how many people live in that City I'll just do a statement to query the data and we'll see what it looks like okay I'll explain what the selects star from City means in a minute but let me just show you the output first so I mentioned that the ID is a number so number six is the first number in here name is a variable length character of lengths max length 17. so Rotterdam is the first city which has id6 and this entire record or row is like one entry in that table and it's completely separate from the second entry or the second record in the table so a table has rows and it has columns a row is a grouping of columns that are all related to each other so this Rotterdam is related to Holland because it's inside of Holland whereas Scottsdale is related to Arizona so each record is separate and the columns are like attributes of the city that you're wanting to store or analyze so let's get back to to the actual SQL query there are multiple types of commands in SQL select means that I want to read the data update says I want to modify the data and that's modifying existing data insert says I want to add a new row to the data and delete says I want to remove a row from the data insert and delete are manipulating an entire row at a time update could modify just one column but it still is going to be specific to one row and you can also do multiple rows at the same time but you don't have to modify the entire record if you wanted you could just modify one column in that row but for now we won't talk about update insert or delete that's for another day select means I want to read data out of a table and I like to format like this the asterisk means the columns that I'm wanting to read and asterisk just means every column so it would be identical to me typing out each column one at a time and so the reason I like to format it this way is I think it's a little bit more readable to have the command as the first row the table as the third row and then in between them to have a list of columns with a tab and when you're only doing a very simple query it probably doesn't matter but once you get into more complex queries having your code formatted in a readable way is important so let me just run this a second time so that we can see that I still get the same type of data the six Rotterdam so if I hit Run Okay so uh here I got an error so it says runtime error your output it says invalid column name county code so I actually have a misspelling here it's country code so anytime you run an SQL statement or any programming code you want to read the error messages carefully to understand what you did wrong so let me run that again okay it's back to what I had before six Rotterdam so you can see that the asterisk returns every call column and here I've written them all out so it's the same thing so if I were to take population off right now I have five nine three three two one if I remove population when I run it that number should be missing in the results and here you can see it's missing an important thing to note is Select is not modifying the data the data is still the same in the database and in that table it's just modifying how I view the data so even though I removed population from my query it's not deleting that data out of the database it's just not showing it on the screen okay in the from command that just says which table I want to get the data out of and so here I've called it the name city which is from their example that's what the table is called so now let's read their instructions they say they want me to query all columns so I have written out all columns and although I could have used the asterisk here it's considered bad practice to use the asterisk so when you're just testing your code it's fine to use the asterisk as a way of being lazy but when you're ready to put your code into production you really should list out the specific column names and one reason is for readability because if more columns are added or removed in the future then this makes it obvious which columns you actually needed in some cases you may not actually need every column but the more important thing is there's a huge performance difference if you choose to only return the actual columns you need instead of all of them is significantly faster and I'll explain the reasons why later but as a standard good practice just always put your full column list or always specify the individual columns that you need don't use the asterisk unless you're just doing a quick test code and you're trying to be lazy okay next it says that I want all columns from the city table and I want the populations to be larger than 100 000. so if I rerun this with the population so we can see the numbers you can see that Rotterdam has a population 593 thousand so that is larger than one hundred thousand so I do want to keep that Row in the final output but it doesn't want me to display any small cities so Boulder Colorado only has 90 000 so that should be excluded and again I'm not deleting it from the table I'm just not showing it on the screen so pretend that you're making a report maybe the CEO of the company wants a report saying information about their customers or what cities they live in they might give you this kind of criteria you know tell me only big cities not small cities so the command to filter a result in a SQL query is called where and to filter you list the column that you want to run a condition on and then you use a mathematical symbol so in this case I'm going to do the greater than sign to say population is larger than and then I can just copy and paste this number or retype it and so essentially this is saying read these columns from the city table and filter based on population being larger than 100 000. so Boulder Colorado should be removed from the results but Rotterdam should stay included so let me run it okay it says I still have the wrong answer but Boulder Colorado was missing so let's look at the expected output to see what I did wrong in my case I have six rows in their case they only have five rows so I'm including one extra row that I'm not supposed to and then also okay at first I thought maybe it was sorted wrong because I noticed that Scottsdale was number two on the list and here it's number one on the list but everything else is in order so it looks like the only extra one I have is just Rotterdam so I should reread my instructions to see what I missed and this is one very important thing about making reports in a company is you need to have really good attention to detail to understand what requirements the business is asking you for before you make the report because the business is not going to know the correct answer they're asking you to tell them the answer but if you misunderstand their requirements you could give them a completely wrong answer and they might not necessarily know but they could make the wrong decision based on that so you want to make sure your logic is correct and that it matches the requirements they gave you so I didn't finish reading the question that was my mistake it says the country code needs to be USA because it says All American cities so I included Holland so to include a second condition I'm going to use the and command and then I'll list my second condition so country code equals and then I use single quotes anytime you're using text you need to put in single quotes so that it knows where the text starts and ends and that's called a string if like a string of characters if it's text so I'm going to say USA so the equal sign is just doing the mathematical comparison of if when it's filled filtering these rows it's checking if the country code matches the value USA and in most programming languages letters are case sensitive so there's a difference between a lowercase USA and an uppercase USA or even a capital u lowercase s and capital A now that's true of most languages but SQL in particular at least Microsoft SQL Server by default is case insensitive meaning the case does not matter so it should be the same whether I do an uppercase or lowercase now that's the default it's possible that the database administrator changed that default so you should double check but in general you can plan to not worry about if the case is correct or not so let me run my code and it says I'm correct so that's great one tip you may want to consider anytime you're doing a string comparison depending on your database if you have a lot of dirty or bad data in your system if you don't have a database administrator that's checking to make sure that data is like verifying if the data is accurate then you could have mistakes you want to watch out for in particular with strings so as an example let's think of a different country code like maybe Canada I'm assuming that would be just CA well the country code says varchar 3 meaning I can have a maximum of three characters well I mentioned that the case insensitivity where ca uppercase and see a lowercase are the same however space is sometimes matter depending on the database engine you're using so the ca with a space would be considered different than CA without a space so that's something when you first start working with a new database you you should just double check if that situation happens maybe just pick a few random text columns and check if they have spaces at the end or write an example query and see if spaces are ignored or not but in case they're not ignored for your particular database you may want to run a trim command on that column and trim just removes any extra spaces so in that case if country code was CA with a space trimming it converts it to just CA so when it does the comparison it will Matt and I I wouldn't just automatically throw a trim on every column because it will slow down the processing I would only do that if I'm getting results that I don't expect and test that to see if it fixes the result but just something to keep in mind so it looks like this particular test is completed it says congratulations okay it says I earned 10 points and I don't exactly know what I can do with these points um but I think eventually I get a certificate I can put on my resume if I want it and I think maybe there's like some paid tests or something but you can use points to unlock the test or something so let's just move on to the next challenge okay this looks almost identical the city column is identical to the last one and it says in this case only the name field rather than all fields and it wants me to use a population of 120 000 instead a hundred thousand otherwise it looks the same it's still one Inc only USA or American city so I wish I had kept the old code in my clipboard so I could uh paste it and just modify it slightly but I can retype it that's fine okay this is almost identical to the last one I already showed in the previous example how I could modify which columns are being displayed so that's really the only change I made here and then I changed this from 100 000 to 120 000. and one thing to note I chose to use the casing on these words that is most readable so country and code are two different words but usually columns do not have spaces in them because variable names in programming languages normally do not like spaces so in order to make it readable when there's no space I choose to use an upper case when I start the word code however in the screenshot on the left you notice I can't select the text because it's an image but so it's more like a screenshot but they chose to uppercase everything so similarly like I mentioned before with case insensitivity it's not only true of just strings it's also true of variable name so and that's not true of every language and some languages if you change the casing of a variable it becomes a different variable but in this case it doesn't matter if I lower case or uppercase code inside of country code it still is the same column so you do want to write your code AS readable as possible and I always use uppercases when I'm doing keywords that are part of the language itself so name City population and country code are not part of the language those are parts of the database that I've defined tables and columns and then these values 120 000 in USA those are parameters that I'm filtering with and so those are also not part of the database language itself but and where from at select are part of the database language so I choose to uppercase them just to make it more obvious and readable and it's also nice that this editor color codes them in purple and the parameters at color Coast the red for text and green for a number every editor is going to be different in how it color code so let's run the code make sure I coded it correctly and then move on to the next one looks good submit I earns 10 points next challenge okay this one says all columns again for every Row in the city table same table and it looks like I have no criteria so I probably should start skipping ahead to more advanced queries because I've basically done the same thing three times in a row but the only difference here is I'm just not going to do a where Clause because I'm not doing any filter at all so it's not just USA this time it's every country and it's not only large population cities it's any population and out of laziness I'm going to use the asterisk because I want all columns and I just don't want to retype them all but as I said earlier in production I would specify the individual columns actually so I will run my code looks like I got it right I'll submit it I think the reason the submit and run are separate is I think the Run only executes one test and I think the submit executes multiple tests okay looks like I got it right next challenge okay this one's very similar to before I'm not gonna go into much depth on explaining it okay same as before the only difference is the where Clause criteria is slightly different in this case I'm looking for a specific I now something to note of here I chose to put the 1661 in quotes which means that this is actually a string or text but the ID is a num when you use the equal sign for comparison as well as any other type of math operation like a plus sign or a greater than sign the math operation only works if both sides are the same type of data so what it will do is it will automatically convert from one type of data to another if it needs to so in order to compare us a string of characters to a number it will either need to convert the string of numbers or the string of characters to a number or it'll need to convert the number to a string and the reason this matters is the number one 661 to us this looks like a number but because I put it in quote to the compute tutor it's thinking of these as letters because numbers only have meaning in math they don't outside of math numbers and letters don't have a difference in me and let me explain that a little bit better I'm going to open up a Google tab and search for something called an ASCII table okay this is an ASCII table and this is one possible way that the computer represents a character and so if I look for the letter a as an uppercase like unfortunately I can't highlight but if you look at my mouse because it's an image I can't highlight it if you look at my mouse the letter A corresponds to the decimal number 65. you can ignore HX stands for hexadecimal OCT stands for I don't know how to pronounce it octal octal so we count in tens so DEC for decimal stands for 10 I think in Latin hex stands for 16 and OCT stands for eight so think about like a hexagon or octagon so hexagon hexadecimal and octal are different ways the computer can count decimal is how as humans we prefer to count so just ignore the hexadecimal and octal but the letter a is 65 but that's an uppercase a the lowercase a is a 97 and then the actual number is zero when it's a number it's obviously zero but when it's considered a letter the letter uses the number 48. so that's probably a little bit weird to think about but the number zero when you convert it to a letter it's actually the number 48 because the number 0 stands for null which is like the absence of our letter and so ASCII can also do characters that you can't visibly see like for example the enter key I believe that's 13 so right here number 13 is CR which stands for carriage return or Enter key so if we go back to the database query I chose to put this in a string so as I mentioned it's going to have to convert this automatically to a number or to an integer uh or convert the ID from a number two test it should still work but that was laziness on my part or maybe bad practice on my part to put in quotes because when it has to convert between data type it actually can slow down the query and it but the nice thing is it was able to do it for me automatically now if I wanted to I could convert it myself so int stands for integer and that's just a number that can't have a decimal because decimals are harder for the computer to process and so if you want to use decimal you use a different data type for that the computer tries to be as efficient as possible so if you know that you're never going to have a decimal you specify your variable in a regular programming language or your column in in a database language you specify as an integer so that it can avoid doing the extra processing required for decimal so the cast command allows you to convert between data types so if I run this again I'll still get the same result but internally it was already doing this cast for me and so even if I explicitly tell it to cast it's still slower just like the automatic cast so ideally when I do a comparison I should make both data types the same I should type as a number rather than as text and I do that just by removing the quotes So then when I run it'll still give me the same results but it will be slightly faster and sometimes speed doesn't matter computers are very fast you know the difference between one second and two seconds is not very much to a human but it's a lot to a computer in Fact Computers a lot of time might even be like one thousandth of a second which is called a millisecond and so when you think about a database is handling maybe a website like Amazon they maybe have millions of people all ordering things at the same time so every millisecond counts because if you optimize your code the website and database can handle more users at the same time however programmers are also expensive we looked at salary guides earlier and saw that the average programmer makes about a hundred thousand or more a year in the USA so you know if you can do a very simple fix to make your code faster you should definitely do it but if it's going to take you a month of extra work to optimize for a faster code it may not necessarily be worth it you would want to you know analyze the value that it brings to the business you know how much money is it going to save them as opposed to how much money they're paying you in salary because you know computers are not too expensive if if it's going to take you a month to optimize the code it might be cheaper to just buy a faster server so just some thoughts uh so if you choose to do optimization when they're not actually necessary then that's called premature optimization but in general the more you practice with writing good code you'll tend to just write it in an optimal way the first time without actually having to spend a lot of time analyzing it or thinking critically about it it should just come natural with practice this so at least when you're learning you you know you want to just use best practices from the beginning so you don't learn bad practice so I'm going to submit my code and move on to the next one okay this is almost the same as before it just wants me to use Japan for the country code it says all attributes so I'm not going to explain the query it should be pretty obvious and I'm surprised it's not working oh I don't know what happened but somehow on the top right corner it got changed to db2 and I mentioned this earlier every SQL language has a slightly different syntax I've never used db2 before so I don't know exactly what's wrong here I'd have to kind of read the error code and analyze it but if I switch it back to SQL server and it's going to make me retype it it should work this time I think perfect submit next challenge now you may want to I feel like this gave me the exact same challenge I just barely did oh no instead of all attributes it wants just the names so you may want to pause before I start typing and think in your head of how you would code it because you're going to learn more from figuring it out yourself than you are from just copying my answer or reading my answer but we haven't learned any new Concepts here so I'm not going to explain the code I'm just move on to the next test next challenge okay good we're finally getting to a new type of table so it says query a list of city and state from the station table and here's the table so since it doesn't actually need the latitude or longitude yet I don't really need to worry about that and it uh but just the city and state columns and it doesn't have any criteria it's asking me for either okay next challenge says query a list of city names from station for cities that have an even ID number print the results in any order but exclude duplicates from the answer okay we are getting into slightly more advanced SQL here so whenever you're writing a query and you're not exactly sure how to solve it then you want to just break it down into smaller pieces one at a time so for example it's asking me for an even ID number so like even versus odd so even would be 0 2 4 6 and then the results can be sorted in any order but then the duplicates need to be excluded so I'm not going to worry about any of this yet I'm just going to worry about the first piece which says query a list of city names from station so select City from station that part you should already be familiar with we've done lots of times before and the reason I'm just doing one piece at a time is because as a question gets too long it can feel overwhelming and so don't get scared just break it down into small pieces and do one piece at a time so next it says it wants me to filter for just ones that have an even ID number so if I'm filtering I know I want to use the where command and I know I'm using the ID column the only question is how do I determine if it's even or odd okay so you may not be familiar with the mathematical way of how to determine if a number is even or odd but there is a concept called a modulus and the other word for that is remainder so when you divide something it has a remainder if it doesn't evenly divide so if I divide something by 2 so use the forward slash to do divisions if I divide the number four by two it's going to equal 2. but if I divide the number 5 by 2 it's going to equal 2.5 so the remainder is this 0.5 and the other way to write this remainder would be the number one because it's kind of saying 5 minus 1 divided by 2 equals 2 meaning that if you subtract 1 from it it now divides evenly there's no decimal if that makes sense so the remainder is how many numbers away it was from dividing evenly um so in the case where it divides evenly the remainder is going to be zero because it's zero away from dividing evenly it already divides evenly it doesn't need to subtract anything subtracting zero does nothing so instead of actually doing division here I'm going to use the modulus command which gives me the remainder and an SQL Server you do that with the percent sign so if I say take the ID column run the modulus of it using the number two as the value you're dividing with then whatever that remainder is check if it equals zero now if I wanted to make this slightly more readable I could put parentheses around this because that's kind of saying to group this whole thing so that the equal sign is comparing this entire group against the zero the parentheses are not necessary because it already Works uh it already knows to group them but if you didn't know the order of operations of how programming languages do comparisons you might read it and think that it's checking if two equals 0 and that afterwards whatever the answer was it's doing the modulus between ID and that answer of 2 equals zero so even if the parentheses are not required because you already know the default order of operations so basically modulus has a higher precedence than equals it can make it more readable if you add parentheses so it's kind of a personal preference it's up to you and especially when you're going to see more complicated uh filters and you have multiple filters with and or with or the parentheses help you to group individual criterias so that it's easier to read them so let me run this code it should fail because I have not yet done the duplicate check um however I can at least see if the data is working correctly uh or if my logic is working correctly so even though it only wants me to return the city I'm going to temporarily return the ID and rerun it so I can make sure that I only have um even numbers and it looks like I do 794 824 those all look even to me and you can comment out individual lines of code uh with the double hyphen or Double Dash and a comment means to ignore that line of code so without deleting this code I could run it without the where Clause so that way I could see if the odds appear by getting rid of that filter and here I can see that I I am getting odd number 603 619 and you'll also notice this scroll bar is really long it's returning 400 500 rows uh if I wanted it to just be slightly faster if I'm just doing a quick test I can do something like top one or top ten so that when I run it my scroll bar doesn't return 500 results so here you can say I only have 10 and that's important because in in particular when you're working at a real company and you're analyzing data the tables may have millions or even billions of rows and if you don't do a top 10 and you're just doing an example query to analyze some data you might only actually need to visually look at the first few to analyze it logically but if you're downloading a million records that's going to use a lot of internet connection and it's gonna use a lot of processing power on the database and so you don't want to waste resources so putting in that top and a number like 10 is is valuable some people use a higher like top 100 but honestly I always use 10 because that's how much you can actually see on the screen at a time your eyes are you're probably not actually going to analyze that many rows most of the time okay so I believe this logic is correct the only thing I'm missing is this duplicate and by doing it one piece at a time it prevented me from getting overwhelmed trying to do too much at once so now I'm just going to make them get rid of the duplicates so the distinct that does a duplicate check now I do need to remove this ID column for two reasons one is the ID in most tables we'll get into this a little bit later but in most tables the ID column is already going to be unique so by me when I do distinct it's actually checking all of the columns in one big comparison to see if they're unique so even if the city was not unique if the ID was unique then uh because it's comparing them both simultaneously it's going to say that the ID is unique so this distinct is not going to filter anything out um but also I need to remove the ID because the answer said they only wanted me to return the city names so as an example if I had Las Vegas with the ID one and I had Las Vegas with the id2 if I run distinct on these two rows they they're already considered as a whole the whole entire row is already considered unique um and so distinct would not get rid of any duplicates but if I remove the ID now these rows are identical and so distinct would get rid of the one duplicate so it would only have one value returned so I think this should work now let's just see if I pass the test okay it says I got it right okay it says I got my first star um maybe that means I'm at a good stopping point maybe um I've done the basics of SQL I'm not sure so I think I'll stop right here um feel free to keep going on your own if you'd like I'll do another video in a few days going over the next set of queries and my hope is if with this entire series I'll do basic in one video intermediate in another video and just keep going and once you've watched them all you should be an expert and be able to get a job in SQL report writing if you wanted to so thanks for watching give me a comment below if you have any questions or suggestions for the next video thanks