Let's try a few more complex queries, so right now we have this animal table that has three rows in
it and each row consists of two columns, ID, an animal name, and I'm going to get rid of this entirely.
I'm going to do it.
First of all, I'll show you how you can empty a table.
I can just say delete from animals and hit return and then or hit and run your query.
I'm about to execute a delete statement without a where clause.
Yes, that's exactly what I want to do.
I want to delete everything in that table.
So if we look at the animals table now, select staff from animals, it'll be empty.
And it is now you don't have to do that if you just want to get rid of the table, I can just say drop
table animals and that will delete the table.
And if there's anything in it, obviously that's gone as well.
So it is gone.
So now if I look at my my tables and refresh this.
Right, click on it, there's nothing there.
All right, so let's create some new tables just to show some more complex queries.
So I'm going to create a table called People and I'll do it the using the graphical user interface here
by saying create a new table and I will call that table people.
So where is it here?
New table.
This is it.
And we'll go to properties and I'll call this people and I'll add a column of typed serial, which I'll
call ID.
And the type will be cereal, so cereal there is and cereal there and we'll give it a length of 11:00
once again and we'll make it not know, OK, and then I'll add another column called First Name.
Verkerk or VirTra, I'll make the link to 55 and I'll make it not null.
And I'll create a third column called Last Lasting.
And again, I'll at length 255, which is long enough for our purposes, and click, OK, and I'll save
this table safe.
So I now have a table and you notice when you go to save it, we could have typed it out like this.
That's the sequel syntax for creating a table.
But we're just using a graphical user interface to make it a little simpler for us.
So we now have this table.
So now I'm going to create another table by clicking on the tables over here, create new table.
And I will call this emails and I'll create a column in this table called Idy.
And once again, that'll be of type serial cereal.
So let's find Serial right there, their length 11 not.
OK, and I will create another column called People, Heidi.
And this will also this won't be a type serial.
This will be a type integer.
So we'll choose a eight, I'll make it length 11 and not know, OK, and then I'll create another third
field called email address and that will be of type.
I'll make it length 255, which should be long enough for email addresses, make it not null and click.
OK, so let's save this one now.
So now I have.
Two tables, if I refresh this by clicking on it and using refresh, you'll see that I have emails and
I have people, so let's go put some content into these.
So we'll do it using the command line, using Sindhi, using direct SQL.
First, let's insert a person and I will say, well, first of all, let's have a look at the structure
of people.
So People has columns.
It has its first name.
Last name.
OK, so I want to say insert into people and then I'll put the columns I want to populate, which will
be just first name and last name values.
And then because this is text, I have to put single quotes around it.
I'll put you on for the first name and Smith for the last name and I'll run that.
So now I have some data in people.
So if I look at the data here, right here, there's John Smith back to our Shell script or to our school
editor.
And let's do a second one and we'll call this one Mary Jones, and we'll save that or run that.
And back under here we have our people.
We should have them and refresh this.
Undo when I refresh this.
I have Mary Jones.
OK, so now I have two people in my people table in our emails table, we currently have nothing.
If we look at data, there's nothing in there.
So let's go insert some data.
Now, I have two people in my people table.
We have John with an idea of one and Mary with an idea of two.
I want to give John an email and I'm not going to put it in the people table because he might have more
than one email address.
So let's insert into emails.
And now I want to put in people are dying and I want to put email address values.
And because John is 81, I just put 81 and free email.
I put John Smith, Daudzai and I will put a semicolon at the end and run that comment.
Now, I want to give John a second email address, Gmail dot com, which obviously is not a real email.
Somebody probably has that, but it doesn't matter.
We're just using this for testing purposes now.
He has to email address and let's give Mary one as well.
So Mary is 82 and the ID for people ID matches the ID column in people, so we'll give her email address
Mary at Jones dot com and run that.
Now let's go look at our emails and refresh this.
And there is so John has two male email addresses, and that's linked to him via this I.D., the ID
and the People People I.D. column in the emails table matches the ID column in the people's table in
the same way 82 is for Mary.
And her ID here in the Lady column is two and that matches idea of two in the people's table.
So let's see what we can do with that.
Now what I want to do is I want to write a query that says, give me all of John's email addresses and
that is enough.
I can say select email address from emails where people ID equals one and that is John's ID.
So when I run this, I should just get his two email addresses and they're there.
And if I want to get Mary's email addresses, I can say I'd love to because that's her people.
And if I run that, I should just get her email addresses.
Well, what if I want to get John's first name, his last name and only one of his email addresses?
I can do that, too.
I can say select.
And what I want to select is from the the people's table.
So I'm going to use the prefix of P, which will be defining in a minute.
Peter, first name, P Dot, last name, either email address from people P and that's where the P comes
from.
This is the table I want to select from and actually select for more than one table.
But the people table has the prefix or the shorthand or the reference of P.
That's why the P is here in the P is here and now.
I need to join another table left, join emails E on how am I joining those tables.
I want to join them on either email or people.
ID is equal to the are p dot ID.
Let's see what happens when we do this run.
And what it says is John Smith has an email at John Smith, John Smith, John Smith also has an email
of John at Gmail dot com, but Mary only gets one entry.
And that's because when I join the people's table with the emails table, there is only one matching
entry in emails.
So she only gets it once.
What if I want to limit this and just get the emails for John and I want his first name and last name
at the same time.
I can just anywhere close where?
P ID is equal to one and run that, and now I just get on if I changes where to where p ID equals two,
I can run that.
I can even do it this way.
Where.
Peter, first name is equal to John and Peter last name is equal to Smith, and if I run that, there
we are.
So there is how you join tables now.
We could do this with anything we wanted to.
We could have another table.
Let's create one.
Let's go to tables, create a new table, and we'll call this one under properties, phone numbers for
phones.
And it's going to have a column create a new column.
We'll call that column ID.
The data type will be serial.
The length, again, will be 11 and not know, which means you can't try to insert something into the
table without populating that field and here it's being populated for us because it's a serial type
and that will create a new column.
Once again, we'll call it people ID.
The type will be int eight.
So let's find it eight.
Indicate the length will be 11 and it will be not known.
OK.
And we actually want to create one more column and that will be phone number.
And that'll just be a marker.
Not no.
OK, save this table and previews it for us.
That's exactly what I want.
So let's save that and go back to our school window.
And this time we'll insert some phone numbers.
So insert into phones.
And the only popular columns I want to populate are people, ID and phone number.
And the values will be people ID for John, his phone number is going to be 555, dash five five five,
dash one, two, three, four.
And we'll save that or run that.
And then we'll do the same thing for Mary, but hers will be four, three, two, one and run that.
And now when I refresh my tables, we should have phones.
So let's look at phones and let's look at the data and they're there back to our school script.
Let's modify this.
So we're going to make this a little easier to read.
I'll make it select these things and then I'll put Frum on.
It's online and I want to select from people and I'm on a left.
Join emails and I'll put we're way down here and we'll do another joint left.
Join Fontes and we'll call it Pitou Osgoode on Peter ID for people ID is equal to Pitou Daudt people.
Eddie and I will modify this to include the phone number, which will be Pitou phone number.
So let's make sure we got the syntax right.
We have Peeter first name for the people table paedo, last name for the people table Iida, email address
for the emails table and paedo Pitou phone number.
And you notice there's no comma after the last part of my select.
That's important.
We're selecting it from people.
Good for making those two.
Joynes good.
Now let's fix up our workless.
Let's get it just for John to start with where John are, where people dot ideas equals one that you
get me to read.
And it does John Smith with his two phone numbers, and if I change this to two to get married, we
should get one row and there it is, Mary Jones.
So that's how we can do more complex queries.
We can even do it this way.
We can again leave the where clause off entirely and run this.
And we get John we get to John's in America, but what if I want these in a particular order?
Well, after the where clause, if we have one, I'm going to leave it up right now.
We can also have an order clause ordered by and what do you want to order.
But I want to order by from the people table last name and run that.
And now we'll sort on the last name.
And therefore, Mary Jones comes first.
Perfect.
So that is how we can do a slightly more complex queries.
Now, the best way to get used to writing SQL queries is to actually write them to do something useful.
So we're going through an overview of the syntax right now.
But when we start actually writing the application, the business logic for our bookings application,
we're going to be writing a lot of queries and those queries will become increasingly complex as time
goes on.
But the basic syntax behind it all is exactly what we're covering right here.
There are other other kinds of Joynes we can do an inner join an outer, join the left, join a right,
join a natural join.
But don't worry about those.
As they come up, we'll go through them and learn the syntax and see how all of this works.
All right.
That's enough for this time around.
It's just about time to try to connect our code to the database.