databases like im 5: queries, viewing data, and an intro to clauses (article 3)

before i start, a couple things:

1. this series is purely about databases, data storage and retrieval, there is not a true relation to machine learning and deep learning so feel free to skip over it.
2. i am going to encountering a lot of this in my career, so i figured i would dive deeper and provide an amazing source for people like myself… i struggled in the beginning!
3. link to the previous articles: https://medium.com/@ailikeim5/list/databases-like-im-5-in-order-9f6713e5b469

prerequisite knowledge:

a. basic understanding of relation databases

b. basic understand of sql, postgres, and creating a database

anyways,

in the last article, we learned about sql

• our favorite standard database programming language.
• but, unlike most programming languages we are used too, it doesn’t need exact to the tea instructions and handles a lot of the hard work for us.

so sql is a bit magical like this crocodile:

• and although sql is a standard language, we ended up with a bunch of different flavors of it:

although we love to try different types of ice cream, our best choice without a doubt is to stick to one… so which one?

postgres is the easy choice of sql flavors!

• it is easy for beginners
• loved by developers
• and has so many cool features

and postgres’ logo is literally an elephant

how can you not love them:

inside postgres and pgadmin, we talked about queries

• the vessel for executing our sql code in our database

1. how to create and query a table with sql

2. some general datatypes for now:

3. constraints

4. filling in data

5. creating visual schemas:

and with this code we could create our database for our app:

and fill it in with some data:

we created an entire database, but we haven’t viewed what’s inside our tables.

when we want to view a table in the database, we can look directly into the database with a query called a:

select statement

• used to retrieve data from a database
• it has a super simple syntax.

note: this is incredibly important thing to grasp your head around because this is the basis of most of the queries you will do

meaning we can run the following query, we will see that we filled the user table in perfectly!

• this is showing us all the rows in our table!
• this might feel like its in reverse order at first, because it is!
• once again, although select * is a good visualization in this case, we want to avoid using it, and use just the column names instead.

here’s another example

• hopefully it starts to click!

clauses

• so selecting our table with this is nice, but in the future we are going to support queries for millions of users while they are actively using the database, want to view data from multiple tables, and have situations where we need to refine it our db down.
• we probably shouldn’t query an entire table!
• clauses are the beginning of this

before we dive into clauses, it is very important to understand

1. the order
2. how to read it and when to use

• pay attention to the plain english explanation at first
• and worry about the syntax as things go on

the very important order:

remember how i was saying it felt like select and from were in reverse order

• there’s actual merit to that statement

and this is how you should think about it:

book mentioned above, support the author!

• even if the syntax is in reverse, it will always be executed to this
• this is incredibly important!

how to read and when to use

1. from/joins

• i already explained a from statement, it is not rocket science!
• in other words -> what table can i find this in.

but we are going to postpone joins and seeing more beauty of borat for today :(

2. where

imagine:

• uber eats just informed us that one of our couriers stole food and deleted their account. leading them to lose all information about them but the carrier id.
• we want to find more information about them to report it to the police, and help the user involved in the situation.
• luckily, we stored their information in our database

we know the external courier id is “UE-Courier-123”

what’s the best tool for finding the couriers information in our database?

• these conditions will be very familiar with those with coding experience
• don’t worry, they are not rocket science:

• hopefully, this is pretty simple!
• again, don’t stress the syntax, it comes with time.

and so if we want to find more information about our thief , we can use a very simple query!

• although sql syntax is not really case sensitive, when we use a where clause it is!
• it is comparing 1 characters at a time and a capital Z will be different than lower case z
• remember characters are just numbers!

let’s look at another condition:

• just like the conditions before but finds where at least 1 is true!

1 more for now:

• there are lot of these but this is sufficient for now!

full book above!

3. group by:

• to best highlight group by, let’s add some additional data to our address table.

ok perfect, so let say we want to create a marketing campaign and target our most popular states.

we first need to get a count of each state

before we talk about group by, let’s talk about aggregates

aggregates

ah so if we want to count the number of unique or distinct states in our database, we can use these aggregates!

• but now, how do we calculate the count of individual states? like how many users are from florida, georgia, etc?
• we introduce group by

• the validity might feel a bit confusing, don’t worry, it has a lot to do with how the dbs process things. don’t question it!
• i am going to be showing smaller portions of the screenshots, so in reality the table is much bigger (33 rows)

a great way to picture group by is thinking about splitting into smaller tables, calculating aggregates for each group or sub table, and then combing those results into a single table:

book mentioned above

remember the order of how things are processed:

1. it will look for the table first
2. group it
3. and then aggregate and display it.

select happens last, so we cannot access the other parts of our address table due to nature of grouping this!

can we use group by without aggregates?

yes:

why?

let me make it clear:

group by is not really meant to be a sort, there is a better tool for the job!

(the order is so so so important to these things, so we can’t skip ahead. we will talk about the better tool later!)

4. having

remember that where clause

• now, that you understand grouping, we can introduce it’s twin, having

so in other words:

but they have some clear differences

remember before our group by returned all the states and their counts

but for our marketing campaign, we are really only interest in allocating our budget towards the right states, i.e one’s that have a high count

if we set a criteria for our, of let’s say greater 5, we can find our most popular states and filter the others out.

although all our users are from the usa, this is a great example of how we can use where with group by.

so would a query like this work???

we will save the solution for a future article, but understand the order of how things are executed is crucial…

it changes the meaning of everything!!

5. select

if you made it this far, and cannot comprehend select statements, you are probably on the same iq level as my friend beetle juice:

6. order by

so remember before, the group by clause was awesome with aggregate functions, but it was kinda useless without them.

that using it as a sort was like using a saw to cut bread:

it will do the job, but there is clearly the better option.

the better option:

let’s say we wanted to sort all the postal codes, very simple integers: (number)

so what about characters, character arrays, and text?

• we just compare 1 character at a time
• key idea: all characters are just numbers and capital letters come before lowercase letters, meaning a capital Z comes before a lowercase a

so we can introduce 2 important clauses very valuable for sorting characters/strings:

7. limit

• our database becomes huge, like millions of users
• but what if i just want to see 10 rows when i search

showing the first two addresses:

usinh offset for some more powerful search:

now, we have unlocked the true power of the sql infinity gauntlet:

ok, one more topic:

views

• views are called virtual tables meaning they are not real.

welp, that mind sound a bit confusing so let’s elaborate

• because databases are heavily optimized, and their nature, views provide a efficient alternative to looking directly at our tables
• when we create a view, we are basically creating a live photo in to the database so we can look directly into the real live data
• this allows mean we do not have access to modify the contents of the table.

• there are many benefits and use cases we will use in the future!

so anyways,

sql is a big struggle for me can’t lie. i feel like, as a programmer, it is harder to learn sql with a background in programming. it just does everything so different than what i am used, but that’s how it be doe.

mood:

have a bitchin day and

godspeed!