It is just sitting there FULL of data.

I do not know about you, but I REALLY want to scan the data that lies within.

To get a look inside that database, I need to write a query.

A very precise query.

So let’s do this.

Let’s learn about the SELECT statement.

I’m going in…

Hold my drink.

Databases can have many, MANY tables.

With the SELECT command, you can retrieve data from one table …

Or multiple tables with a technique called “JOINS.”

Today we will focus on using the SELECT statement to get data from a single table.

We will push the SELECT statement to its limit….

And then we will push some more.

We will use a table called “earthquake.”

It contains data on all earthquakes with a magnitude of 5.5 or greater

from 1969 through 2018.

This table contains 10 columns: earthquake_id

occurred_on latitude

longitude depth

magnitude calculation_method

network_id place, and

cause This data was retrieved from the US Geological

Survey, one of the top sources for information about tectonic activity.

The earthquake_id is the primary key of this table.

The primary key is a value that uniquely identifies each row in the table.

The next 4 columns tell you when and where the earthquake occurred, with the depth measured

in kilometers.

The “calculation_method” is a code for the formula used to compute the magnitude.

Do not fear the math.

Embrace it.

The “network_id” is an alternative key generated by the contributor of the data.

The “place” is a human-readable value of where on Earth the quake occurred.

And then there is the “cause”, which we will discuss later.

A CSV of the data used in this video is available from Socratica on Github.

It is only 2 megabytes in size and is waiting for you to download.

So create your table and import the data, because it’s time to shake, rattle, and

roll… and query.

I will be using pgAdmin since I am using Postgres as my database.

But all examples should work on any newfangled relational database these

the kids are using days.

For our first query, we will select all data from the earthquake table.

This query contains two essential parts.

The SELECT statement lists the columns you want data for...

And the FROM statement specifies which tables to select data from.

When you use an asterisk, that instructs the database to return all columns.

Even the shy ones.

Now execute.

How do you like them apples?

You can see that all 10 columns were included.

In many SQL tools, the first column is the record or row number.

This is not data from the table.

It simply numbers the results for you.

If you are using pgAdmin, you can click the “Messages” tab to see how long the query

took, and how many rows were affected.

And we see there are 23,119 rows in this table.

Let me get this straight.

There have been over 23,000 Earthquakes of magnitude 5.5 or greater in the past 50 years.

That is more than one per day.

Easy there, Mother Earth.

Maybe try some Hot Yoga...

But there is another way to find the number of rows in this table: use the COUNT function.

That is right, my friends, there are functions you can use in your queries!

For this query, we will select a COUNT rather than the data itself.

COUNT is a function, and the input can be either an asterisk, or the name of a column.

We will use an asterisk to ensure we count all rows.

Execute…

The result is a single number: 23,119.

Notice that the name of the column in the rowset is “count”.

This is the name of the function, not the name of any column in our table.

I would like to raise one more issue.

Speed…

Let us execute the first query again And note how many milliseconds it took

to execute.

But if you use the count function … it is noticeably faster.

To a single person, the difference may not be perceptible.

But if your database is being used by thousands or millions of users, then milliseconds matter.

So strive for peak performance.

You want to be a lean, mean, clean coding machine...

For our next query, we will be more selective in how we use SELECT.

Instead of fetching all columns, let us be more specific.

This time we will only select the magnitude… place… and occurred_on columns.

Execute…

We can select the columns in any order that we please.

If you would prefer, you could select the “place” before the “magnitude.””

Execute…

And admire...

In addition to the SELECT and FROM keywords, there is a third valuable part of queries:

the WHERE clause.

You specify the COLUMNS you want with the SELECT keyword …

… but you specify the ROWS with the WHERE clause.

For example, let us select all earthquakes that occurred on or after January 1, 2000.

Execute…

If you scan the rows with your eyeballs, you will see that all of the earthquakes listed

did occur on or after January 1, 2000.

The database obeyed our command, like a tiny little digital puppy that is well trained

in SQL.

Who’s a good boy?

Who fetched all that data for me??

Let us now answer a very specific question using SQL: what was the largest earthquake

in 2010?

First, we need our query to only select quakes from 2010.

We do this by requiring the “occurred_on” field to be on or after January 1, 2010, and

also on or before December 31, 2010.

Execute…

This query returned 559 rows.

I do not know about you, but I am in no mood to scroll through 559 rows to try to find

the largest Earthquake.

And with SQL, we do not have to.

We can improve our query by sorting the quakes by magnitude.

To do this, add an “ORDER BY” clause.

After ORDER BY, specify the column to sort on.

Execute…

The query did sort by magnitude, but it sorted in ascending order.

Let me get this straight - to find the answer, I would have to scroll to the END of the results.

I refuse...

Instead, let us sort by magnitude in DESCENDING order.

To do this, add the DESC keyword after the column name.

… Execute.

And there we have it.

The largest earthquake in 2010 was an 8.8 quake off the coast of Chile that happened

on February 27.

While we answered our question, there is still room for improvement.

The database returned 559 rows, but we were only interested in the first one.

We can take pity on our computer and restrict the number of rows returned with the LIMIT

keyword.

At the end of the query, write LIMIT and the number of rows you would like.

To answer our question, we only need one row.

Execute…

There we have it.

A clean, efficient query that answers our question.

This query showcases the structure of many queries.

To fetch data from a table, start your query with “SELECT” and specify the columns

you want.

An asterisk means you want all columns.

Next, the FROM keyword is used to pick the table.

You can restrict which rows are returned with a WHERE clause.

There can be one or more search criteria separated by logical words like “AND” or “OR”.

Now that you have chosen the rows and columns, you can sort the data

with an “ORDER BY” clause.

You can sort a column in ascending or descending order.

And if you want to restrict the number of rows returned by your query, specify a LIMIT.

Congratulations!

I did a great job.

You have now seen the main parts of a SELECT query.

But this is only the beginning.

There is much more you can do.

So get ready to take your SELECT skills from here to here.

Because after a few more of our videos, you will be in a SELECT group of engineers.

Soon there will be no LIMIT to WHERE you will go.

At this point, most YouTube channels would ask you to Like, Subscribe, Comment, and Share.

But we order our calls to action by ACTION, so please Comment, Like, Share, and Subscribe.