For Lab 5.2, you are provided with the directory.
When we look at the directory-- let me go out to the Lab
folder--
we can see we have com.textmeinc.textme2.
Then you have a whole bunch of directories in here.
The first thing you need to do is find the database.
And then, you get to put the pieces together.
So open your database and DB Browser for SQLite.
Or I guess, you could technically
throw it in Physical Analyzer-- whatever you want to do.
I'm choosing DB Browser.
I'm going to start with ZMESSAGE.
And the first thing I want you to note
is how many of them exist.
So you can see 45.
That number should not change.
Then going to Execute SQL, and start walking your way
through this query.
Now, if you look at the lab in the workbook,
we are going to assist you with the columns you need.
So we're telling you to return a query that's
going to show you Message Sender, Message Timestamp,
apply the correct timestamp and Message Body.
And then we want you to add the name of the attachment.
And then we want you to add the sender of the attachment.
And then, as a bonus, can you write
a statement that makes the bad timestamps go away?
Because remember how null timestamps could be bad?
You could also see the sponsor or the bot messages that
are, like, welcome to Facebook.
Say Hello to your new friend.
They may not have legitimate timestamps.
Make them go away.
If you are new to writing queries,
do not be afraid to just copy what we provide to you
and paste it.
You definitely can do that.
I don't want you to think that you cannot leverage this
e-workbook the way you should.
Getting started in SQL queries is not always easy.
It's very frustrating.
Do not torture yourself if you're new.
Just practice a ton.
We are now going to review Lab 5.2.
If you're not ready, simply press Pause,
and then continue when you are.
So the first thing we're doing is we're looking at ZMESSAGE,
and we tell you the columns that we're interested in.
Also, notice that there's ZSENDER and Z3_SENDER.
You need to make sure that you're using the correct one.
So if you're not sure which one, you
may have to go back and forth between the table that
stores the user account information and here.
We give you a hint telling you to use ZSENDER.
So I'm going to go into Execute SQL.
I'm going to open a new tab, and I'm
going to do select ZSENDER--
actually, you know what?
I'm going to do star, so I can see everything,
because I like to do that-- from ZMESSAGE.
I like having it below, so it's easier to see.
So here, I'm going to do Z_PK.
I'm going to do ZSENDER.
I need ZTIMESTAMP, and I need ZBODY.
And I think that covers everything we ask you to apply.
So now, it is narrowed down.
If we look, let's look at our magic number--
45.
That's good.
Nothing has changed.
We can now rename our columns.
So I'm going to go in here, and I am going to name them-- it
may be different than what is in the lab
because I'm just going with this on the fly--
primary key AS From, AS Timestamp, AS Message,
whatever you want to call them.
Boom, they look nice across the bottom.
Now, we have to deal with that timestamp.
It starts with a 5, so we know it's a Mac datetime stamp.
So in front of ZTIMESTAMP, I'm going to do datetime.
But before I do this, do you notice how
this one has a negative number?
That's going to cause us problems.
That's the bot saying, about friends, like, invite friends.
And then there's this other one down here that's
sponsored that starts with a 2.
That's not a valid time stamp.
Timestamps don't start with 2.
So we need to consider that for our bonus.
So we have datetime ZTIMESTAMP plus 978307200.
Unix epoch, AS Timestamp.
And now, we have real timestamps,
but look at this one--
1/1/1970.
Again, that's going to confuse people.
And then, I think we have this futuristic one.
Yes.
Look at that.
That's absurd-- 1/1/9990--
not helpful.
But we're going to ignore that for now.
Now, what we need to do is continuing on the lab.
It is going to tell us that it wants us to do some joining.
So in order to do some joining, we have to take our table name,
and I'm going to copy it in front of the tables I have,
so I don't get confused as to where
I'm pulling each column from.
And again, this is not necessary.
It's just really helpful if you need to troubleshoot.
And don't forget timestamp.
When we look at the colorization,
your query doesn't change at all.
Everything is good.
Now, it wants us to add attachment.
So we have to go in to Browse Data,
and let's look at ZATTACHMENT.
So in ZATTACHMENT, we probably want this ZNAME.
That would be helpful.
It's telling us what the name of the attachment is.
But we have to figure out what it has in common with ZMESSAGE.
So we can see, ZMESSAGE has this.
It also has a primary key.
And when we start looking at this information,
we see ZMESSAGE Z_PK.
We see ZMESSAGE ZGUID, ZBODY, ZLOCATION.
So we need to figure out what it has in common.
So it seems like ZMESSAGE Z_PK is
going to go with zattachment z message.
So this number right here.
So let's try this and see if it works.
So I'm going to say--
and I'm going to do something wrong.
I want to show you what it looks like.
I'm going to do left join ZATTACHMENT
on ZATTACHMENT.ZMESSAGE equals ZMESSAGE.Z_PK.
And I'm going to press Enter.
And everything is going to go well, but nothing has changed.
We still have 45 messages.
You can join tables all day.
But unless you tell it what you want to join,
it's not going to be helpful.
So what I want to do up here is then say ZATTACHMENT.ZNAME AS
Attachment Name, comma.
And let's see if that worked.
There we go.
So now, we have that information.
The biggest mistake is not adding
in what you actually want to.
All right, so now, we have this outlier right here.
Look at that from.
We need to know who sent these messages.
So again, we have to find what's in common.
We go back.
We look.
There's ZCONTACT.
So here, we can see some names--
Mr. Lloyd Christmas.
We see some phone numbers.
We have ZVOICEMAIL, ZCALL, ZFAVORITE.
So it seems like ZCONTACT is going to be our table.
So how do we know what is in common?
We look at ZCONTACT.
We want ZUSERNAME.
Maybe we want ZEMAIL.
You can have as many things as you want--
ZPHONENUMBER.
So we start looking, and we see a Z_PK.
So let's see, in ZATTACHMENT, if anything's going to jump out.
ZSENDER-- so it's probably ZMESSAGE ZSENDER
equals ZCONTACT Z_PK.
Let's try it.
No punctuation.
Left join ZCONTACT on ZCONTACT.Z_PK equals
THEMESSAGE.ZSENDER.
Now again, what did I do wrong?
I didn't say what I wanted from ZSENDER.
So I need to say that from ZSENDER, I
want a specific item.
So if we go back to ZSENDER, let's see what our options are.
ZCONTACT-- let's start with ZUSERNAME.
And you can put this wherever you want.
So I'm going to say, ZCONTACT ZUSERNAME as from, comma--
now, we have a name.
I would get rid of this other from just
so it's not confusing.
But let's say, you wanted to add ZCONTACT.ZEMAIL,
you can do whatever you want at this point.
You can keep adding because it's already joined.
And the more you add, the more that will appear.
You could have phone number--
ZCONTACT dot-- and I think you get the point.
Except, I created something that doesn't exist.
Do you see my error?
Now, I have to go back to ZCONTACT
and see what it was really called.
This a good example.
ZTEXTMENUMBER, interesting.
OK, let's do that one.
So we find our error.
And I have a number in there twice.
All the squiggles go away.
It's happy again.
So you will find the typos.
That's also a perfect example of what
if I didn't put ZCONTACT in front of it,
and then I didn't know why that column was an issue?
I'd have to go back through every single table I
was joining to find it.
But because I had ZCONTACT in front of it,
I could easily isolate that.
So now, it looks like we have our query.
We have to do a final thing here.
We need to look at a K statement.
Can we make that timestamp go away--
so the bad timestamp?
What I would recommend you do is copy this.
So do Control-A, Control-C, and open a new tab,
if this is new to you.
So in the new tab, we need to do a case for this datetime.
So in front of datetime, we're going to do case.
And in here, I'm going to say when--
let's see-- ZMESSAGE.ZTIMESTAMP is greater than zero,
so it'll get rid of that negative 1.
And ZMESSAGE.ZTIMESTAMP is less than--
I think it's a billion.
Then, do datetimes ZMESSAGE.
Do our normal query.
But what we have to do is say what to do if it's not that.
So we say, then do that conversion, else,
what do you want to say?
Invalid timestamp end as timestamp.
Let's see how it did.
There we go-- invalid timestamp.
Let's see if we got the other one--
Invalid timestamp, success.
And just practice these.
Honestly, it will make things the easiest as possible.
Now, you have a few questions.
The first one is, how many messages contain attachments?
Right now, we have 45 showing.
Your easiest way to fix this--
take away the word left.
It only shows attachments.
We have 17 rows.
All of our analysis from this point is on attachment,
so I'm actually going to leave that join change.
So I changed it from left join to join.
Now, it's going to ask us a lot of questions
about the attachments.
So I am going to order by attachment name, descending.
And now, we can answer some of these questions that
are going to be asked.
So we have specifically about emoticons.
So what can you find?
So if you look through here, we can see emoticons_sulky,
happyshy.
You could actually go out to the directory
and find them and see them here.
We are asked specifically about this file attachment, 96AA.
What is it, this JPEG?
Again, go back out to the documents.
And we can see the photo of a dog.
We are asked about Google.
Are there any locations that are shared?
So if we go back in, we could see
that Google Maps was shared.
You're going to find the information
of the coordinates where latitude
and longitude in the top right.
So hopefully, that lab went well for everyone.
Just keep practicing.
Remember, you have your course notebook.
You have your e-workbook that you can copy and paste from.
We want you to become fluent with SQLite,
but it does take practice.