SQL ORDER BY: Tutorial for Beginners

Watson Tech World

Explore the basics of the SQL ORDER BY statement in this comprehensive beginner's tutorial. We'll start by comparing database sorting with familiar spreadsheet techniques, and then dive into practical examples using MySQL Workbench. Discover how to sort salary and star data, and learn the syntax for ascending and descending order sorts. Whether you're new to databases or just brushing up, this video is a clear guide to mastering SQL ORDER BY. Subscribe or follow for more!  ---  Code for video: https://go.watsontechworld.com/75-code-sql-order-by  Example SQL code p1: https://www.mycompiler.io/view/1xqg9LtuQx1  Example SQL code p2: https://www.mycompiler.io/new/sql  ---  💻 Get started with cloud or VPS hosting with DigitalOcean and receive a $200 credit for 60 days through our link 👇  https://watsontechworld.com/digitalocean  ---  Our website: https://watsontechworld.com  ---  00:00 Intro  00:33 What is the SQL ORDER BY keyword?  01:16 Examples of sorting in a spreadsheet  02:23 Intro of MySQL Workbench  02:58 ORDER BY examples with salary data p1  03:44 Explaining default sorting with ORDER BY with ASC  04:05 ORDER BY examples with salary data p2  06:46 Explaining the HYG star database  07:51 ORDER BY examples with star data  13:33 ORDER BY examples with salary data p3 with myCompiler  14:45 Conclusion  ---  #SQL #MySQL #SQLTutorial #SQLForBeginners #SQLOrderBy #OrderBy #Database #Programming #LearnSQL #tutorial #coding #MySQLWorkbench

Transcript

00:00Hi, everyone. In this video, I'm going to do a tutorial on SQL OrderBy.

00:07And I'm going to give you some examples like these.

00:13And I'm going to give you an overview of what it is, show you how to sort in a spreadsheet,

00:19which is very similar to sorting with OrderBy in SQL.

00:23I'll briefly mention about this star database, and then you'll have a link to where you can actually try out OrderBy by yourself.

00:33Okay, so let's get started. So first is, what is the OrderBy statement?

00:39It's basically a way to sort rows by giving columns.

00:44And it needs to come after another proper SQL query.

00:52And OrderBy in SQL is similar to sorting in a spreadsheet.

00:57And the basic syntax is some SQL query, OrderBy, some column.

01:03This means ascending or descending.

01:07And I'm going to show you several examples in this video, and hopefully you can get some practice with this.

01:15Okay. Now, what I quickly want to do is that, as I mentioned, a spreadsheet is very similar to SQL.

01:22Or at least, there's, anyways, what I'm going to show you is, let's say you have data like this.

01:30This is salary data. And let's say you want to order, so we have name, career, yearly salary, gender, age, age group.

01:38And let's say you wanted to sort by, for example, yearly salary.

01:43I'm using only office. So what I'm going to do here is, I'm going to do sort by yearly salary.

01:50And I'm going to do descending.

01:53So, for example, this person makes the most, and these two people make the least.

01:59That was sorting by this column.

02:02And we could have also sorted by age.

02:05Let's sort by age.

02:08And let's sort by age ascending.

02:11Okay. So you can see here, we have the youngest people at the top and the oldest at the bottom.

02:18And it's very similar in order by with SQL.

02:23Okay. So now I'm going to show you, this is called MySQL Workbench.

02:28And I'm going to show you running the commands directly.

02:33And then what's unique about MySQL Workbench is that you can have the view of showing the code

02:38and also see the output very easily.

02:41Okay. So let's get started.

02:42So I have these first two commands.

02:45And we don't need to...

02:48Oh, I can show you this.

02:49Show tables.

02:50This will...

02:51I'm going to use these two tables, salary and star data.

02:55And I'll discuss this.

02:57First, I want to show you the salary data.

02:59And it's the same as what I showed you.

03:02This is the salary data, the same data.

03:05And right now I just...

03:07I did select star from salary.

03:09And what that does is...

03:13And it's good form to capitalize the SQL key terms.

03:22Okay. So that shows you everything.

03:24Now, let's say for example, we have the name career, age, and yearly salary.

03:30We want to select these four columns from the salary column.

03:35Now, let's order by.

03:37That means sort by.

03:39And let's sort by age.

03:41And by default, it's always...

03:44I think I forgot to mention here.

03:46By default, order by will sort in ascending order.

03:49If you want to sort in descending order, you have to use the DESC command.

03:54And you can use the asc command or keyword.

03:59But it will make it...

04:01It's unnecessary, but it may make it more clear.

04:04Okay. So let's order by age.

04:06So we have this data.

04:08We see the youngest people are at the bottom.

04:11And then the oldest is at...

04:13Excuse me.

04:14The youngest is at the top and the oldest is at the bottom.

04:18Now, let's do similarly.

04:21But instead of ordering...

04:23Instead of sorting by the...

04:26By the age.

04:28Let's sort by the yearly salary.

04:30Ascending.

04:31And again, by default...

04:33By default, it's going to be this.

04:38So we don't actually need it.

04:40But later, we can actually use it if we want.

04:43So let's sort by the yearly salary.

04:45So the people with the lowest yearly salary are at the bottom.

04:50And then the highest...

04:53Okay.

04:54At the top is the lowest.

04:56And at the bottom is the highest salary.

04:59Now, let's do similarly.

05:02But instead of going from ascending.

05:05So instead of going from lowest salary to highest salary.

05:08Let's go from highest salary to lowest salary.

05:12And the DESC, that means descending.

05:15And that'll sort it by...

05:17So what it means is...

05:19Sort...

05:20Basically, it means sort by yearly salary.

05:22Descending.

05:23And the DESC is the...

05:26Word...

05:27Is the key term you need to use if you want to sort descending.

05:30Okay.

05:31Let's do that.

05:32Okay.

05:33So...

05:34Yearly salary is at the...

05:37Is descending.

05:38So the highest salaries are at the top.

05:41Going down.

05:42So descending.

05:43Now...

05:44Now we can do sort...

05:49We can do order by with two columns or more.

05:53So let's have the same columns.

05:56But now let's sort by gender.

05:58Ascending.

05:59Ascending.

06:00And then yearly salary descending.

06:04So that means...

06:07Females will go first.

06:09And then...

06:10We'll sort all the females from...

06:12Highest to lowest.

06:13And then we'll have the males from highest to lowest.

06:16So let's show you there.

06:18So again, the females are at the top.

06:20Because F comes before M in the alphabet.

06:24And then...

06:26Notice here we have...

06:27This person has the...

06:29Highest yearly salary.

06:31Followed by her.

06:32Followed by her.

06:33For females.

06:34And then for males.

06:36This person has the highest...

06:38Yearly salary.

06:39And then this person.

06:40And so on.

06:41So that's sorting by two columns.

06:44Now what I want to show you is...

06:47This star database.

06:49So...

06:50Go back here.

06:58Okay.

06:59So this is a very interesting database.

07:01And you're going to see the data.

07:03It has almost 120,000 stars.

07:05And it's...

07:06I think it's quite interesting.

07:08So let's...

07:10And the columns that I use...

07:12It actually has several different columns.

07:14But the columns I'm going to have...

07:17The proper star name.

07:18Which is the full name of a star if it exists.

07:21The parsecs from Earth.

07:23Light years from Earth.

07:24So one parsec...

07:26Is...

07:27Roughly 3.26156 light years.

07:32And I already converted it.

07:34We have the constellation full name.

07:36And the apparent magnitude.

07:38And the apparent magnitude is the brightness as seen from Earth.

07:41And the smaller the value.

07:43The higher the apparent...

07:44Apparent magnitude.

07:49Okay.

07:50So first let's select...

07:51All...

07:52Star from...

07:53Star data.

07:56And...

07:57We don't...

07:58We only get a thousand rows.

08:00Because actually in...

08:02MySQL Workbench...

08:03By default it's set up to limit to one thousand rows.

08:06There's actually over a hundred thousand rows.

08:08Let's run this second one here.

08:10Show how many rows are in the star data.

08:13Table.

08:14So we can do...

08:15Select count star.

08:16What that does is tell you how many rows are in...

08:20If you do a select star.

08:23And note here...

08:24Okay.

08:25Actually there's about 120,000.

08:27I guess there's 119.626 rows.

08:34So that's quite a lot of data.

08:36And for a spreadsheet, I think this would be humongous.

08:39And it would not be a good use case for a spreadsheet.

08:43This is a good use case for a database because there's so many rows.

08:47Okay.

08:48So...

08:49Now what I want to do is show you here.

08:53We're going to mainly use these four columns.

08:56The star name.

08:57Light years from Earth.

08:59Constellation full name.

09:01Apparent magnitude.

09:02And...

09:04What I want to do is sort it by light years from Earth.

09:08Ascending.

09:09Or...

09:10Actually this...

09:11I'll show you the same thing in a moment.

09:15This term is actually unnecessary, but we can still run it.

09:18Okay.

09:19So...

09:20Sol...

09:21This...

09:22What this means is our own sun.

09:24And light years from Earth...

09:26It says...

09:28It has zero...

09:29Technically...

09:30Yeah.

09:31It would be a very...

09:32In terms of light years, the sun is practically right next to us.

09:35Light years are an incredibly long distance.

09:40Basically the sun is what?

09:41Eight or nine light minutes away.

09:45Okay.

09:46So...

09:47It's null because we don't consider it part of the constellation.

09:50And the apparent magnitude is this.

09:52So it's the brightest star we have.

09:56And I sorted it by the light years from Earth.

10:00So...

10:01These stars...

10:02Proxima Centauri...

10:03Proxima Centauri...

10:06Is the closest star to us outside of our own sun.

10:10And then these two stars...

10:12Actually all these three stars are part of the...

10:15A trinary star system.

10:17And these aren't actually exactly...

10:20This...

10:21I think this is just because the...

10:24When I converted it from...

10:26Parsecs...

10:27To this...

10:28These...

10:29These two stars are very close to each other.

10:31But not...

10:32They're not exactly the same distance.

10:34I think it's just a limit of the...

10:36Conversion.

10:37And anyway...

10:38So we have the...

10:39All these different things.

10:41And the...

10:43You can see here...

10:44So these are the...

10:45We have Bernard star that's about six light years away.

10:50Then Wolf...

10:51Three...

10:52Five...

10:53Nine...

10:54And then we have these different ones.

10:56Sirius...

10:57Ross...

10:58One...

10:59Five...

11:00Four...

11:01And so on.

11:02So I think this database is very interesting.

11:04Now let's do this.

11:05Let's sort by apparent magnitude...

11:07Ascending.

11:08So what this means is the...

11:09We're going to sort by...

11:11The brightest stars...

11:13As seen from Earth.

11:15And again the apparent magnitude is...

11:20Brightness as seen from Earth.

11:23So...

11:24The brightest star of course is our own...

11:27Soul.

11:28Our own Sun.

11:29Then we have Sirius.

11:30And Sirius is the brightest star...

11:32In the night sky.

11:34Okay again we're ignoring our own star...

11:37Which is the Sun.

11:38So Sirius is the brightest star in the night sky.

11:41And then we have...

11:42Conopus...

11:43Octaurus...

11:45And so on.

11:46Vega.

11:47So it's very interesting.

11:48And if you're into astronomy...

11:50You can learn a lot from this database.

11:51It's very interesting.

11:52Now what I want to do is...

11:54Select those similar things.

11:56But now...

11:57Let's make sure the constellation name...

12:00Is not null.

12:01So that's what...

12:02This...

12:03We don't want null.

12:04And we want to sort by...

12:06We're going to do a group by.

12:08So we're going to group...

12:09We're going to select the constellation full name...

12:12And the average light years...

12:13Grouped by the constellation full name.

12:18And...

12:19What this shows you here...

12:20Is that these different constellations...

12:22And the average light years from Earth.

12:24And a lot of these have hundreds of stars in them.

12:26Or even thousands of stars.

12:29And this one here...

12:31It does a group by...

12:32And group by the...

12:35Average light years from Earth.

12:38Or is that the same...

12:40Or just the same one there.

12:42And then we do a group by...

12:43The constellation full name.

12:45And then we do the...

12:46What this is going to show you...

12:48Is the average apparent magnitude.

12:50What's very interesting is that most of the...

12:53Constellations are...

12:55Very similar apparent magnitude.

12:58With the average that is.

13:01And...

13:02Let's see here...

13:05And finally...

13:06I'm running out of time here.

13:08What this is going to do...

13:10Is...

13:11Let's do the last one here.

13:14We're going to select...

13:16Constellation name.

13:17Apparent...

13:18Magnitude.

13:19Group by constellation.

13:20Full name.

13:21Sorted by average apparent magnitude.

13:23And make sure the constellation is not null.

13:29And...

13:30This is the average apparent magnitude again.

13:33Now what I'm going to do is...

13:35I'm going to give you this code.

13:36You can run on this...

13:37From this site.

13:38And then what you can do is...

13:39Go to this URL...

13:41And run the code yourself.

13:43So...

13:44I'm going to show you here.

13:45We're going to do...

13:46Show all the salary data.

13:49Now I can comment that out.

13:51And let's say for example...

13:52You want to sort by the age.

13:55And this can...

13:57You see here...

13:58We have the youngest at the top.

13:59The oldest at the bottom.

14:01And I'm doing the same commands as I did before.

14:04For the salary data.

14:06And then for example...

14:08Let's say...

14:09Order by the yearly salary.

14:11So that would be...

14:12Yearly salary ascending.

14:14So the...

14:16Basically the people of the lowest salary...

14:18Are at the top.

14:19And the highest salary...

14:20At the bottom.

14:21And then...

14:22If we want to do it descending.

14:23So on the other order.

14:24From...

14:25Highest to lowest.

14:26We've got this.

14:27So this person is...

14:28Makes the most...

14:29Per year.

14:30And then next this person.

14:31And then this person.

14:32And then this person.

14:33And so on.

14:34So this is...

14:35The person's name.

14:36Their gender.

14:37Career.

14:38And so on.

14:39Okay.

14:40I hope you enjoyed this video.

14:41If you liked it...

14:42I'd appreciate it if you could subscribe to...

14:43Or follow this channel.

14:44And see you in the next one.

14:45And I'll put a link to...

14:46Some of this data.

Category

Transcript

Recommended