Python #8: Loops

Loops are exactly what they are. They make a line of code run repeatedly so you don’t have to type it hella times. Just like putting a song on repeat so you don’t have to keep starting it over. We can also call it an automated copy-paste.

fruits = ["apple", "banana", "orange"]
for fruit in fruits:
    message = fruit + " is a fruit"
    print(message)

Output:

apple is a fruit
banana is a fruit
orange is a fruit

This code saves us from having to print each of those fruits by themselves. Imagine all the space that’d take up.

To use this function, after you define a list, you’d first type ‘for’, then set a variable to access each item in the list. Just how that list was called ‘fruits’, it makes the most sense to call our variable ‘fruit’. You want it to access a fruit in the fruits. You can name it whatever you want, just use common sense for the sake of organization. Then you add ‘in’ after the variable, add the list name with a colon at the end, then finish with an indented function of whatever you want it to do. In this case it was to insert the list item (the fruit) into a message and print that message.

Here’s a math one with adding all the numbers in a list.

numbers = [1, 2, 3]
total = 0
for n in numbers:
    total = total + n
print(total)

Output: 6

So this one confused me because I thought it would print it separately like with the fruits. First, ‘total’ is not an actual function, it’s just a variable name. So when the code runs its first iteration with the first list item, 1, that adds 0+1 which makes the total 1. Now when it moves on to its second iteration with 2, it carries over that previous total, so that adds 2+1=3. Again, for the third and last iteration, it carries that last total 3, and adds by the 3 in the list, which makes 6.

Even outside of bracketed lists, you can use a loop to break down all the characters in a string

for letter in "abc":
    print(letter)

Output:
a
b
c

‘Letter’ is just a variable name. You could type anything and it’ll still break down that string, long as you use it consistently.

While

‘While’ loops will repeat a code as long as a condition is satisfied

i = 0
while i < 20:
    print(i)
    i = i + 2

Output:
0
2
4
6
8
10
12
14
16
18

This code makes sure that i+2 will iterate on each until it reaches 20. So again, we’re telling the code to run its iterations as long as it meets a certain condition. It’s formally called a ‘while’ loop, but you can keep a mental note that its function is an ‘as long as’. As long as ‘i’ is less than 20, it will continue to iterate.

Practically, this can be used to prompt a program to do or not do something if that condition wasn’t met. Like in some shooter games, if your health gets low, the screen turns red. So the developer would use a ‘while’ loop like “as long as health is less than 30, make the screen do this”. Or for something more universal, as long as you don’t put the correct password, the program will keep prompting you to try again. Or even as long as the wrong password is put more than 4 times, it will prompt you to reset it, or even lock you out from trying again.

Extra Tips

There’s a built-in ‘range’ function that can generate numbers from 0 up to whatever number you set it to.

for number in range(5):
    print(number)

Output:
0
1
2
3
4

The number you set it to is where it stops, if you want to see that number itself, you’ll have to go up one.

It’s simpler and cleaner than using a ‘while’ loop like we just did.

for i in range(10):
    print(i)

vs

i = 0
while i < 10:
    print(i)
    i = i + 

Output:
0
1
2
...
9

but I’ll have to learn some other variations of that.

Instead of starting from 0, we can set the range to start from another number

for a in range(2, 9):
    print(a)

Output:
2
3
...
8

You can still put your limit number, just make sure you precede it with the starting number and separate it with a comma

We also got nested loops:

for i in range(3):
    for j in range(3):
        print(i + j)

The ‘i’ is the heading loop and the ‘j’ is nested inside of it. So when we add those ranged variables together, it’ll output each combination of those ranges. The output would only show the results, I just put the equations there to illustrate how it’d work.

Output: 
i=0, j=0: 0 + 0 = 0
i=0, j=1: 0 + 1 = 1
i=0, j=2: 0 + 2 = 2
i=1, j=0: 1 + 0 = 1
i=1, j=1: 1 + 1 = 2
i=1, j=2: 1 + 2 = 3
i=2, j=0: 2 + 0 = 2
i=2, j=1: 2 + 1 = 3
i=2, j=2: 2 + 2 = 4

We can append an item to a list, which is another form of adding to a list. I’ll have to figure out what the real difference is.

result = [1, 2]
result = result + [3]
print(result)

result = [1, 2]
result.append(3)
print(result)

Output: [1, 2, 3]

These are 2 separate codes, I just put them in the same block. They output the same result.

Continue & Break

The ‘continue’ keyword can skip an iteration in a loop.

for i in [1, 2, 3]:
    if i == 2:
        continue
    print(i)

Output:
1
3

Here it’s added as a simple indentation. It can also apply to any ‘if’ condition we set for it. While it’s formally called ‘continue’ we can look at it as a ‘skip’ function.

There’s also a ‘break’ keyword that will stop the loop before it ends. So while ‘continue’ is for skipping an item in the middle of a list, ‘break’ will shorten that list at whatever point you set it to.

colors = ["red", "green", "blue"]
for color in colors:
    if color == "green":
        break
    
    print(color)

Output:
red

In this code, I break the list at ‘green’, so it will only show any items before ‘green’.

I’m officially at that point where I just feel stuck. I needed help with most of the challenges, some I had to skip over. I’m past halfway in the course so I’m just gonna tough it out to the end and come back to tie it together.

Politics #1: Intro – Ignorant Impression

What follows is my ignorant, superficial impression of what politics is all about. My only formal education on this was a 10th-grade government class, and I don’t remember anything from that but a few vocabulary words. I’m going off of mainstream media and hearsay. I’m just laying this out as a starting point. Here we go:

There are 3 levels of American politics, national, state, and city. The leaders of these are the president, the governor, and the mayor.

These leaders were sworn in through a democratic process where eligible citizens vote for who they would like to see in this position.

On the national scale, there are 3 branches of government: legislative is Congress who writes the laws. Executive is the president who accepts or declines these laws. Judicial is the courts who uphold and enforces these laws.

I have no further knowledge on the state and city scale.

There are 2 political parties:

The Democrats, also known as the left, liberals, or blue. Their goal is to maintain the government in the citizen’s best interest. They lean towards moral relativism. They despise judgment except when it comes to people judging others. They take a motherly approach to economic hardship. If something bad happens, it’s not your fault. We’re gonna kiss your booboo and give you everything you need to get back on your feet. They prefer to give you a fish.

And the Republicans, also known as the right, conservatives, or red. Their goal is to preserve the tradition of the government as it was formed by the forefathers. They lean towards moral absolutism, that certain things are objectively wrong, and nothing is above judgment, for otherwise we would have no standards. They take a fatherly approach to economic hardship. If something bad happens, it’s your fault, whether it really was or wasn’t, you need to take responsibility for your own life. They prefer to teach you how to fish.

I felt dumb as hell saying all of that because I know it’s mostly wrong. I’m ashamed that I’m this ignorant, but we’re gonna clear it up.

Now that we’ve established the big dogs, I want to talk about me and you all as citizens. So there’s obviously a hierarchy. Certain people are in charge and some have more authority over others. As citizens, I would hope we have some kind of authority too. The conventional form of us exerting this authority is to vote.

I’m not convinced my vote matters. This mainly pertains to the presidential election. State and city, I’m a little more confident it does, but even that’s not exempt from corruption. The electoral college is the root of my skepticism. I’m gonna make a separate post on that.

Past that, my biggest issue with American citizens in politics is most of us don’t like to follow through on things. There’s more to caring about an issue than just saying you care, you go to do the work too.

Next time you’re around somebody who’s ranting about politics and you want them to shut up, just ask for a solution; ask for the logistics, “How exactly would we do this, step-by-step?”. Watch how fast all that ‘passion’ dries up. They get uncomfortable and dismissive.

We can’t stay in theory all day. We got to put things into practice at some point. And on that point, since most people talk about politics in the way I described, I don’t see why everyone’s vote is equal. If you don’t have direct experience in a field, or at least study it, why do you have the power to make a decision on something you know nothing about? Or to get deeper, not even just not knowing about it, but don’t have to face the consequences for? And this applies to me too! I don’t claim to have a better answer yet, but I want to take the time to understand it.

I’ll list my concerns in my ‘book intro’ post.

Before I move on, I want to stress that it’s not my end goal to get us to not vote. I’m not saying we should just sit back and do nothing at all. I want us to be able to do something, and I’m gonna figure out how.

Politics #3: Intro – Book Intro & Final Concerns

To dive into this subject I’ll be reading On Politics by Alan Ryan.

I chose this because it seems comprehensive and should give me a good overview of what politics is. It’s 1,000 pages and spans from ancient Greece to the Christian church to the Renaissance to Marx and closes out in the present. So even if it doesn’t show me everything I’m definitely covering a lot of ground.

So the introduction starts off laying out ancient Greece and Persia to be the two ends of the political spectrum.

By Greece, we’re mainly speaking of Athens. That’s whose government we’re looking at it when comparing it to today. Athens was a democracy, where eligible citizens could represent their views for any policy changes. ‘Eligible’ means native men. Women, slaves, and foreigners were left out, so it wasn’t as loose a democracy as it is today. Persia was a monarchy with a king who had the final say. Citizens had little to no room to participate.

So the root of the whole philosophy of politics is what is the best way for people to live amongst each other? As individuals, we have the right to do what we want for ourselves. The issue is, we live in shared spaces. Even if we do something that’s for ourselves, it most likely affects someone else in some way. You may own a piece of land, but if you want to start digging for whatever you’re doing and you end up hitting a power line that takes out the whole block, affecting land you don’t own, that’s no longer an individual right. This applies to all political issues. Drunk driving is a prime example. The only reason there’s a law on that is because the driver isn’t the only person that gets hurt. Again, going back to moral relativism. If we don’t have a universal system for how we decide to do things, then it’s just one giant war of “my feelings are more valid than yours”. Do you see that ending peacefully?

Past us simply having a system, there has to be a form of mediation that balances the people. If everybody is a mediator, then there is no

If you’ve ever played rec sports, you know inevitably there’s gonna be a dispute over something. “His foot was on the line!” “He fouled me” “That’s an illegal move!” Every single time one of these happens, one side ends up saying, “Alright man, you got that. Let’s just move on”. This is a rec center, where you just go for a workout or to have fun. Do you see that happening in pro sports when there’s money and glory on the line? Look how heated they get in playoff or championship games. Now raise that to real life when there are resources on the line, or social consequences (i.e. crime, poverty, or even the safety of our kids). There is no compromise with that. Most people aren’t just gonna wave that off. The only answer at that point is violence. Athens had their own democracy, but all of that went away when Philip II of Macedon conquered it.

So as we go through this, understand no matter how complicated or exhausting or boring this gets… the only alternative is for us to kill each other… and I don’t think any of us wants it to come to that.

So before I start, I’m gonna list some things I hope to learn from studying this subject. Some of this overlaps with law, and I do plan on studying that, but for now I’ll try to keep this on politics

  • What is freedom, liberty, and justice? How do different definitions lead to different systems?
  • How do I know for sure that my vote matters?
  • If I do vote, yet my politician does not keep to their promises, what exactly can I do, step-by-step, to hold them accountable for this?
  • What exactly can a person do to be an active citizen? I want a list. “Voice your opinion” is not enough, nor can everybody become a politician
  • What systems are in place to prevent corruption?
  • How exactly did Blacks earn the right to vote?
  • Evolution of American politics from the first colonizers all the way to now.
  • Pros and cons of other countries’ political systems

Politics #2: Intro – Electoral College

Once again, this is my ignorant impression. I’m not saying this is how it actually works. I’m just laying it out as a starting point.

As we know in the 2016 election, Clinton won the popular vote, meaning more citizens voted for her, yet Trump still won. This doesn’t happen often, but the fact it still happened is concerning.

So my current understanding of the electoral college is each state has a set number of electors. These electors make the final decision on whether a state votes for a presidential candidate. There are 538 total electors across all 50 states. A candidate needs 270, just over half, to win the election.

This is my analogy for why I don’t get this. If there are 4 of us in a car and I’m driving. I ask the 3 of you passengers where yall want to go. All 3 of you agree on the same place, but guess what? I’m driving. So we go where I want to go. Given that scenario, would you say those 3 passengers were heard? Would you say they mattered? Blow that up to millions of passengers and about five hundred drivers, that’s the electoral college.

So why do we have the electoral college? Why can’t the citizens just vote for who they want? The mainstream answer is that this supposedly levels the playing field for all states.

What does this mean? Not all states have the exact same amount of citizens. The big 4 states with the most citizens, are California, New York, Texas, and Florida. Some are worried that these big states can skew the results and leave the smaller states underrepresented.

Let’s do some math here. There are roughly 330 million people in this country. Let’s start with the big 4. Texas is 29 million, New York State is just under 20 million, California is just under 40 million, and Florida is about 22 million. I rounded all those but that total is roughly over a third of the country. Now the electors. Texas has 38, New York State has 29, California has 55, and Florida has 29. That 151 total electors for the big 4 states. Just under a third of all electors compared to just over a third with the US population. So I’ll give it that, the electoral college does cut down on that disproportion a little bit. It’s actually closer to a quarter (off 17) than it is to a third (off 29).

Either way, even if you had all 4 states going for you, you still can’t win solely off of them. And another assumption from Republicans is that these big states are mostly Democrat, so if we allowed the populous to win, the Dems would have those states in their favor. That’s an oversimplification because one, not every single citizen in those states is going to vote Dem. Two, if they’re so worried about that, the electoral college could possibly work against them. Say those states do get majority blue, but still a fair amount of red voters, the electoral college can only choose one party, so you just nullified all those red voters in that big state.

And even funnier, we don’t even vote for the electors, we vote for a slate of electors. This ‘slate’ is selected by a party, but if the party is chosen by the electors… where do the citizens come in?

So my ultimate point is if the electors are the ones making the final decision, why are we even taking the time to vote? Again, I’m not saying the answer is for us to just sit back and do nothing, but I can’t be that crazy for thinking this when I just laid this out. For all that, we’re not giving them votes, we’re giving them suggestions; suggestions aren’t final

This is obviously a big subject, and I want us to understand it. We’ll come back to it eventually.

Python #7: Lists

Using lists in Python makes life easier. It saves us from having to call a function for multiple items, we can just batch them together so it only has to be called once. Though this only works if each item is being called the same way.

We can check if an item is in a list with the ‘in’ keyword, that returns a Boolean (True or False) result.

def test(x):
    numbers = [11, 22, 33, 44]
    return x in numbers

I didn’t really get this one because I thought we were checking if an actual item is in the list. I’ll figure it out later.

Here’s a code if I wanted a Boolean return on whether a list called ‘numbers’ has a length of 3 or less

def is_short(numbers):
    if len(numbers) <= 3:
        return True
    else:
        return False

We’re coming back to the len() function

For combining lists, it’s done the same way as any other concatenation, with the plus sign. If you’re calling a parameter to combine a list, make sure you’re setting a ‘result’ behind it and returning that result.

In Python, or I think programming in general, we count from 0. So the first character (or ‘index’ is the more formal term) will be 0.

If I want to extract a value from a list:

xs = [7, 8, 9]
x = xs[1]
print(x)

Output: 8

Mind you the 1 in the second line is asking for the second index in that list, which is 8.

You can change an item in a list

colors = ["red", "green", "blue"]
colors[1] = "purple"
print(colors)

Output: ['red', 'purple', 'blue']

I’ll admit I had to glaze over a lot of the challenges for this. This’ll be one of the main things I have to go back over

Python #6: Conditionals & Comparisons

Last post with the Booleans I said the code will only run as true if all the qualities are satisfied. I should’ve said conditions. I couldn’t think of the word at the time, but that’s what I meant.

So passwords are a common example of conditionals at work.

if password == "1234":
    grant_access()

The grant_access function would only be called if that given password is inputted.

This seems a lot more practical for automating tasks or even setting an approve/deny function like with the password

Here’s a cool one for setting the max health in a video game

def max_health(mode):
    if mode == "easy":
        return 100
    elif mode == "medium":
        return 75
    else:
        return 35

‘elif’ is for ‘else if’ that acts as an alternative in case the preceding ‘if’ is false, meaning the game is not on easy mode. The ‘else’ is for any mode that is not easy or medium. If there was only a hard mode, we would’ve just put ‘hard’, but we used the ‘else’ for the sake of learning more from this example.

Comparisons

In comparisons, we use the greater than and less than signs.

def is_young(age):
    if age < 18:
        return True
    else:
        return False

This code runs if the inputted age is under 18.

The same goes for the ‘or equals too’ (<=/>=) signs. You can also use these in place of an OR. And you can use the ‘not equal to’ (!=) in place of a double equals.

Here’s a code with comparisons at work in the case of automating a stock portfolio. We want to buy at 100, hold if between 100-150, and sell when over 150

def decide(price):
   if price < 100:
      return "buy"
   if price >= 100 and price <= 150:
       return "hold"
   if price > 150:
      return "sell"

Don’t forget to put the colon after your ‘if’ statements, and keep the ‘r’ in ‘return’ lowercase.

Python #5: Types & Casts, Booleans

Types & Casts

So again the main 2 types of values are integers (numbers) and strings (text)

We can also invert one type into the other by using the ‘int’ or ‘str’ classes.

x = 42
print(str(x))

Output: 42

This ‘x = 42’ is an integer. We can convert it to a string by using ‘str’. I know it seems like a function but it’s not technically one, it’s a class. And either way the end result is still an integer so using the string convert in this case looks redundant, but down the line, we’re gonna have to incorporate variables into strings.

def say_age(years):
    print("You are " + years + " years old")

say_age(99)

In this code, we’re trying to concatenate the ‘years’ variable with the strings before and after it. Problem is, ‘years’ is an integer, as we have the 99 at the bottom there. Concatenation requires all the operands to be the same types, yet with ‘years’ by itself, we’re trying to force an integer into the string. That’s like me asking what’s 2+5*d; not ‘d’ as a variable but as a letter, that doesn’t make sense.

def say_age(years):
    print("You are " + str(years) + " years old")

say_age(99)

Output: You are 99 years old

So this is when we use the ‘str’ class to convert it to a string. Don’t forget, even though 99 is a number, thus an integer, if you want to place that into a string, it has to be a string to fit in. Python types are bigots, they want to stay with their own kind.

Booleans

A regular equal sign just assigns a value to a variable. A double equal sign will test if 2 values are equal.

print(42==42)
print("sam"=="sat")

Output: True  False

Obviously both 42s are the same, thus they’re true, but ‘sam’ and ‘sat’ aren’t the same, thus they’re false.

There’s little point in doing this for single values because we can just look and tell if they’re the same or not. This really becomes useful when it comes to equations.

print((8+2)*3==50-20)

Output: True

These are 2 different equations, but they have the same result, 30, so they output as true.

Keep in mind, Python follows PEMDAS too. You see I parenthesized the 8+2 then I multiplied it by 3. If I left the parentheses out, it would multiply 2*3 first then add the 8, which is 14, a different result.

On the flip side, while double equals tests if they’re the same, we can use an exclamation point and equal to test if they’re not the same.

print(3+4!=5+5)

Output: True

These 2 equations don’t have the same result, so this outputs as true since we’re testing if they’re not the same.

There are also ‘and’ & ‘or’ values in Booleans.

Say I asked you to go to the store and get me a diet Coke. ‘Diet’ and ‘Coke’, those are 2 qualities that need to be satisfied.

is_coke = True
is_diet = False

print(is_coke and is_diet)

Output: False

So you go to the store, you see a Coke, but it’s not diet, and I’m serious about my diet. If we print that regular Coke using this ‘and’ value, it’ll output as false, because it doesn’t satisfy all the qualities.

If I decide to compromise that I do want a diet soda, but I could settle for a regular Coke, we’d use the ‘or’ value

is_coke = True
is_diet = False

print(is_coke or is_diet)

Output: True

Since only one of these qualities has to be satisfied now, and one has been, this code will output as true now.

To recap, ‘and’ means all qualities have to be satisfied, ‘or’ means at least one of them does. And keep in mind that ‘True’ and ‘False’ are set values recognized by Python. If for some reason you choose to name a variable ‘true’ or ‘false’, you’ll have to capitalize the first letter when using the values ‘True’ or ‘False’ if you want Python to recognize it.

2 more variants. You can negate (or invert) a Boolean value by putting a ‘not’ in front of it.

x = not(True)
print(x)

Output: False

Lastly, instead of writing ‘True’ or ‘False’, you can use a ‘bool’ function followed by a parenthesized 0 or 1

x = bool(0)
y = bool(1)

print(x)
print(y)

Output: False  True

‘bool’ is not just a variable name in this case, it’s a built-in function. 0 will always represent false and 1 will represent true

Python #4: User-defined Functions

Last post we went over built-in functions like ‘print’, ‘len’, and ‘min’. There are also user-defined functions we can use to do what we want.

def f(x):
    result = x+2
    return result

print(f(1))
print(f(2))
print(f(10))

Output: 
3
4
12

So the ‘def’ is the keyword for ‘define’. After you define the function and assign it to a parameter (the x), you can pair the function with any value you want like you see in the 3 prints.

That parameter ‘x’ is the variable of a function, but not the exact same as a regular variable like we went over. They are both used to store values. I know it’s not strictly used for that but it seems like a parameter’s main purpose is to save you from constantly redefining a regular variable.

def h(a, b):
    result = a + b
    return result

print(h(1, 2))
print(h(5, 7))

Output:
3
12

In this code we use ‘h’ as the function. It doesn’t have to be ‘f’, it’s just formal to do it like that.

Again, the ‘a’ and ‘b’ are parameters of this function, and the result will add them together. Just how you see in the prints, you can assign any values to that ‘a’ and ‘b’ and the function will do the adding for you. This is much more efficient than like we did for the first ever variable I showed yall:

whatever = 25
print(whatever)

That’s cool for that one number, but if I wanted to redefine that, I’d have to write it again to override it. This gets time-consuming when dealing with complex code.

def h(a, b):
    result = a + b
    return result
    print("word")

print(h(1, 2))
print(h(5, 7))

Output:
3
12

Back to the functions. The ‘result’ and ‘return’ are indented to tell the system they’re a part of the function. The ‘print’ at the end isn’t necessary, but I put it there to show that it wouldn’t show in the output because it’s under the ‘return’. The code follows an order of execution. It will not acknowledge anything that comes after that return. So keep that in mind as you go on.

And to clear this up, ‘print’ is only meant to directly show a value on the screen. ‘Return’ closes out the function and tells it to store those values as is. You see I still have to print the function despite having the return.

I’ll have to come back over this to understand functions better. It’s getting tough but I can’t just glaze over it.

SQL #5: JOIN normalization

As data gets more complex, not everything can be put into one table. So we use a process called normalization to split the data into multiple tables. This is mainly done to minimize redundancy and improve data integrity; plus otherwise you’d have a bunch of columns going all the way down.

Like an online store for example: you’d have a table for customers and their info, orders and their details, and inventory with quantities and other stuff. That’s one big ass table if you put all those together, and tedious to navigate through.

We’re still going to use the Pixar movies table, but we’re gonna add a box office table. The columns will be its movie_id (its place on the table), quality rating on a 10-point scale, and domestic and international sales.

So first task, I simply want to see each movie’s domestic and international sales. Remember, this data is in 2 different tables, I want to see it summarized into one without all the extra columns.

SELECT title, domestic_sales, international_sales 
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id;

As always, SELECT the columns I want to see. The data is FROM the ‘movies’ table and we JOIN the ‘boxoffice’ table. Strangely, without the last line, the ‘ON’ clause, it’ll show the sales numbers, but each movie is duplicated and set with all those numbers. Basically, out of the 14 movies and 14 sales on both tables, leaving that line out will create every possible combination of those movies and numbers. 14 x 14 that’s 196 entities on our summarized table, we only need to see the 14 once.

So with the ON clause we use the movies.id, in the context of the ‘boxoffice’ table, it uses the movies’ ID to set it with it’s sales, not the actual title. Then we match that with the ‘boxoffice.movie_id’ so it’ll show each movie as is. Remember we can’t see the ID numbers in this final because we didn’t SELECT to view it, but it still exists in the original tables we’re pulling from.

Next task, I want to only see the movies who did better internationally than they did domestically.

SELECT title, domestic_sales, international_sales
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id
WHERE international_sales > domestic_sales;

The code is pretty much the same. We just add the ‘WHERE’ clause and set the international to greater than domestic.

And lastly, I want to list all the movies by rating in descending order.

SELECT title, domestic_sales, international_sales
FROM movies
  JOIN boxoffice
    ON movies.id = boxoffice.movie_id
ORDER BY rating DESC;

Top chunk is still the same, just add the ‘ORDER BY’ clause and set it. And this has to be at the bottom too, I tried doing it just between FROM and JOIN and it wouldn’t go.

SQL #4: DISTINCT, Ordering, & Review

Next we have the DISTINCT function. Like I said, real-world data is messy, so you’re gonna have scattered entities and duplicates.

For my basketball example, I couldn’t really think of a case where you would see a duplicate of anything, so I’m gonna use sqlbolt’s example of Pixar movies. In this case, we want to see all the directors listed in the table in alphabetical order.

SELECT DISTINCT director FROM movies
ORDER BY director ASC;

So ‘movies’ would be the table name, and ‘director’ is the column we’re selecting from. Now in the table, the directors listed have multiple movies, for example, John Lasseter made Cars, Toy Story, and A Bug’s Life. So if we didn’t put the ‘DISTINCT’ command there, it would show his name 3 times. We don’t need to see that.

The ‘ORDER BY’ command is self-explanatory, and the ‘ASC’ at the end is for ascending. So this will show all the directors in alphabetical order from a-z. You could leave it out and it’ll still do it that way but it’s good to show it as a formality. If I wanted it listed z-a, or descending order I’d use the ‘DESC’ command, make sure you type the ‘e’. ASC for ascending, DESC for descending

Next, we want to see the last 4 movies that came out, ordered from newest to oldest.

SELECT title, year FROM movies
ORDER BY year DESC
LIMIT 4;

So obviously we want to see the title and the year. Since we want to see the most recent year, we use the descending order because the newest year would be the highest number, so it’s going down. And the ‘limit 4’ is self-explanatory.

Lastly, the ‘limit 4’ would only show the first 4 in the table. If I wanted to see the next 4, without seeing that first 4, I’d add an OFFSET command.

SELECT title, year FROM movies
ORDER BY year DESC
LIMIT 4 OFFSET 4;

The ‘OFFSET 4’ is what tells it to leave out that first 4. Of course if I wanted to see both I’d just do ‘LIMIT 8’

No we’re gonna review all we learned in a new table.

This next table is a list of cities. Its columns are the city name, its country, population, latitude, and longitude. We’ll name the table ‘na_cities’ as in ‘North American cities’.

First task. Let’s say I want to see all the US cities by their latitude from north to south

SELECT city, latitude FROM na_cities
WHERE country = "United States"
ORDER BY latitude DESC

Once again, SELECT command, ‘city’ and ‘latitude’ are the columns we want to view. The WHERE specifies US cities, otherwise it would show cities from all countries. And the ORDER BY sorts the latitude for us. Higher latitude is more north, so since I want to view from north to south, I’d use the DESCending order so it’ll start from the highest latitude.

Next, I want to see all cities that are west of Chicago from west to east

SELECT city, longitude FROM na_cities
WHERE longitude < -87.6298
ORDER BY longitude ASC;

Now with longitude, the higher number is more east. So since Chicago’s is negative 87, if I want to see what’s west of that I’d use the less than sign, so it’ll show all longitudes that are less than Chicago’s -87.

Again we want to see these cities from west to east. So from Chicago, we’re not starting with the closest city that’s west of it, we want the westernmost city, which means we use ascending order, starting from the lowest number. Again, the lower the longitude, the more west, so the city with the lowest longitude in this table is Los Angeles. Every city after that, with higher longitudes, is moving back east towards Chicago, because, I’ll say it one more time, we have to list these cities from west to east. I know this is anal but this stuff matters in a real-world scenario.

Next, I want to see the 2 largest cities in Mexico by population.

SELECT city, population FROM na_cities
WHERE country LIKE "Mexico"
ORDER BY population DESC
LIMIT 2;

SELECT and WHERE speak for themselves. I want it to start with the largest city, so we use DESCending order. And I only want to see the 2 largest so ‘LIMIT 2’

Lastly, I want to see the third and fourth largest US cities by population.

SELECT city, population FROM na_cities
WHERE country LIKE "United States"
ORDER BY population DESC
LIMIT 2 OFFSET 2;

First 3 lines have been gone over enough. Just remember, since I want to see the third and fourth cities on this list, I only want to see 2 cities, hence the ‘LIMIT 2’, and since it’s third and fourth, I do ‘OFFSET 2’ to cut off the first 2 cities, leaving the third and fourth.