For me, the best part of digging into data isn't a prerequisite of gathering, loading or cleaning
the data, but when I actually get to interview the data.
Right.
So these are the moments when I discover whether the data is clean or dirty.
So whether it's a complete and most of all, when story and what story the data can tell.
So think of interviewing the data as a process, asking to interviewing a person applying for a job.
Right?
So you want to ask questions that reveal whether the reality of their expertise matches their resume.
Right?
So interviewing the data is exciting because you discover truth.
So, for example, you might find that half the respondents forgot to fill out the email.
Uh, for example here, let's actually let's open this with the query here, click right click on the
teachers.
And after that we waited data all rows here.
So for example, you might find that half of the respondents forgot to fill out the email, for example,
filled in the questionnaire.
Our mayor hasn't paid property taxes for the past five years.
Or you might learn that your data is dirty.
Names are spelled inconsistently, dates are incorrect or numbers doesn't jibe with your expectations,
so your findings become the part of the story.
In SQL interviewing data starts with the select keyword.
So.
Um, which retrieves the rows and columns from one or more of the tables in a database.
So a select statement can be a very simple as you can see, we can't delete this because we use the
select here by right clicking onto our teachers and clicking related data.
And here first 100 rows, if we click on that, you will see limit 100.
So we will use that select a lot.
So now we're going to firstly go to here tables and now we will click on the query tool.
And as you can see, we are using on a database of PostgreSQL, which is the database we want to make
the SQL statements on.
So here is the basic.
SQL syntax here.
So select this select from.
And here we will use the teachers.
Teachers.
And here, let's click run.
And here, as you can see, we we are seeing some of the here outputs here Caitlin Rush, Janet Smith,
Lee Reynolds, Samuel Cole, Samantha Bush, and here these are the salary of numeric and so on.
So this single line of code here shows the most basic form of SQL query and the asterisk following with
the actual let me get that pen.
So the pen or.
Ink right here.
So now we will we will draw some things on the screen.
Here.
So now.
Okay.
So here the asterisk on the screen.
Yeah.
So here, this wild card, which is like standing for value, it doesn't represent anything in particular
and instead represents everything the value could possibly be.
So here it shorthand for select all columns.
So if you had given a column name instead of the wild card here, this command would select a value.
So in that column here, so the from keyword here this from keyword indicates you want the query to
return data from a particular table.
So the semicolon after the table name tells the PostgreSQL it's the end of the query statement.
So let's use the select statement with the asterisk wild card on the teachers table you created in previous
lectures, and once again you open the Pgadmin, select the analysis, our Postgres database here,
and after that, open the query tool here and then execute the statement you are seeing here.
So remember, as an alternative to typing these statements into the query tool, you can also run the
code by clicking open file and navigating the place.
You have saved the code you downloaded from our attachment sections.
Right?
Always do this if you see the code is truncated with here.
So now we will once execute the query.
The result here in the query tools output page contains all the rows and columns you inserted into the
teachers table in previous lectures.
So these rows may not always appear in this order, but that's okay.
So note that the ID column here of here Big integer is automatically filled with sequential integers,
even though you didn't explicitly insert them.
Right?
So it's very handy.
This is auto incrementing integer acts as a unique identifier or key that not only ensures how how each
row in the table is unique, but also later gives us a way to connect this table to other tables in
the database.
So before you move on, note that you have two other ways to view all of the rows in a table using Pgadmin,
you can right click on the teachers table in the object tree and choose the view with ID and our rows.
And here you can see the.
At the same result here.
So.
As you can see here.
We can also, let's say, query a subset of columns here.
So often it's more practical to limit the columns, the query retrieves, and especially with a large
database so you don't have to write root access information.
So you can do this by naming columns separated by commas right after the select keyword here.
So we don't need that for now.
We will go to our here teachers that we used and executed our queries.
SQL queries here.
So now we will select.
So firstly, let's I want to introduce how this select works, how how we can, how we can query a subset
of columns here.
So now here, my first.
Alarm.
Another.
Alarm.
Super duper.
Fulham.
And from our table name.
In this case.
In this case, this is just an example here.
My super table name.
That's it.
With that syntax, the query will retrieve all rows from just those three columns.
So let's apply.
Let's apply this to the teachers table.
Perhaps in your in your.
Postgres.
You want to focus on teachers names, first names and salaries.
Right?
So in that case, you would select the relevant columns.
So we're going to use let's actually use also use the.
Names.
Last names.
And hire dates and salaries.
Right.
Let's use the for here.
So instead of doing this, we will use the select select for the last name.
Last name and then first name.
And then salary.
So actually before seller, let's print the higher date.
Higher date from teachers.
That's it.
Now let's run.
And here, as you can see, we have last name.
First name, higher date.
Oops, We forgot the salaries.
Right?
Salary.
Office insert.
Okay.
Salary and teachers.
Uh, from teachers.
From teachers.
That's it.
As you can see, last name, first name, hire, date and salary here.
So although these examples are basic, they illustrate a good strategy for beginning your interview
with data.
So generally it's wise to start your analysis by checking whether your data is present and in the format
you expect.
Right?
So which is a task well-suited to this select keyword our data in a proper format, complete with a
month, date and year or they entered as once regularly observed as text with the month and year old.
So does every row have values in all these columns and there mysteriously no last names starting with
letters beyond M Right.
So all these issues indicate potential hazards ranging from missing data to other records keeping somewhere
in the workflow.
So we are only working with a table of six rows.
But when you're facing a table of thousands or even millions of rows, it's essential to get a quick
read on your data quality and range of values it contains.
To do this, let's dig deeper and add several SQL keywords.