Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
hi and welcome the module to Pleasant 1.4, the 1 to 1 relationship in more detail or continued.
00:07
So
00:08
I'm at a my sequel Demo database download site. This will be affiliated with the lesson as well for download, but you can also go to my sequel tutorial dot or slash my sequel,
00:19
Dash sample Dash database dot as PX to download as well.
00:24
So we're going to work with a skimmer that displays the 1 to 1 relationship.
00:32
And this is the schematic right here for the database.
00:35
So you'll see here between order details and orders
00:39
that and products that there is a 1 to 1 and only one relationship. Now what is the 1 to 1 and only relationship?
00:48
Well, if you think back to the employees related to the state that they were in, you could have had
00:54
a John Kate, a Bob that all lived in Arizona. They had a 1 to 1 relationship. They all lived in that one state,
01:02
now 1 to 1, and only one relationship would be like if we said, Well, no, you can't have three employees in one state. They all have to be in different states
01:11
now, in the context of living in the States, that doesn't make too much sense. But where that does make sense is online orders.
01:19
For example, if you place an order online, do some real Taylor. You'll get an order number from that retailer. That order number is yours and only yours.
01:30
Other customers won't have that order number. And that's a good thing. Because if you had a complaint about an order order got mixed up and customers were sharing order numbers,
01:40
it would be mass confusion, and that would be no good for any store.
01:45
So we're gonna take a look at this data base. We're gonna download it and the neat thing about these demo databases, and there's a lot of them out there. You can find him by just searching my sequel demo database, and they usually give you a nice kima. Their pre built their good representation of what you find out there in the wild and, uh, there
02:02
built following the ah, you know, custom design patterns and standards
02:07
that unfortunately are missing from the wild sometimes.
02:13
So if we scroll up here
02:15
when we click the download, my simple sample database
02:17
and click save
02:22
We're gonna Then
02:23
Little Earl Bouncing Mints. It's done.
02:25
Go to your downloads folder,
02:28
right click that
02:29
click Extract All
02:31
open the folder going and double Click that because we affiliated dot sequel to de Beaver. It should start D Beaver with that script open,
02:43
and hopefully
02:45
that doesn't take too long. Shouldn't take too long. Okay, we are good to go
02:49
now.
02:50
If we look do this file that we opened, we'll see that it's a great database.
02:55
Classic models
02:58
used classic models, and then it's gonna create those tables. In addition to creating those tables, it's gonna assert all this nifty fake data forced the query against and makes sense off
03:09
and maybe transform if we're filling. Ah, like having a good time.
03:14
So if you just opened up
03:19
de beber and then have it open, it's possible that this could have said none.
03:23
If it does, make sure you give this a click. You'll get this window. Select a connection, Do you? Ah, establishing the previous lesson and just click Select.
03:32
And once that's done place, you're cursed at the top of the script
03:38
and click the Execute sequel script button
03:42
and you'll see it run through and run. Everything
03:45
all right? That was quick. We're done.
03:46
So the next band, the data basis
03:50
left, clicked on the databases. Then right? Click,
03:53
click, refresh.
03:55
There's our classic models. Now click on that and right click is well and click set as active. That'll make sure that Thea
04:03
the ah, intelligence works correctly where it predicts and shows you what your options are when you're coding
04:11
we're gonna set that is active. Let's take a quick look at the tables.
04:15
Okay, Let's see if that matches the schematic. We were looking at those table names.
04:18
Make sure ah,
04:20
nothing weird is going on.
04:24
Okay, we take a look at this, will see that we got the orders. Order details, products. That's what we're interested in now, when you're right, enquiries. You want to focus on what you're trying to get out of it.
04:33
Every now and then, I see an individual who tries to connect every tables and make one super query that they can just select from. And what that usually results in is miss misunderstood relationships and connections that then skew the results in weird ways. So good
04:50
practice, especially when he knew, is just connected tables that you're interested in getting data out off for that report.
04:58
So we're interested in the products that order details and orders. The order details sits right in the middle,
05:03
So let's give that one a click. Left click, Right. Click view diagram.
05:11
All right, so we see those three same tables, we see the orders, the order details, the products dee beavers being kind to us and saying, Hey,
05:19
this is the keys on these tables or details. Has two keys. Orders has one key
05:25
products has one keep.
05:26
So let's get the quivering click. Sequel Editor,
05:30
new sequel editor.
05:32
And we're gonna type in select Star Star means everything. Select everything. Give me everything from
05:39
orders.
05:41
Okay?
05:43
All right. We're gonna start order. We're gonna work our way up.
05:46
Let's go in and run that and see what happens. Cursor at the top.
05:50
Pretty sure this fails. Let's try.
05:53
Nope, that worked. Okay, I know I've seen that feel before. Maybe they updated in a new version.
05:59
Okay, so that's the data we're getting out right now. That's from one table. That's good. But we need two tables. Let's go ahead and connect. Actually, only three But let's let's work our way up a proper way to format. This is comma because you're listing making a list just like the anglers English language. We use commas,
06:17
order details. That's what we want.
06:23
So if we run this,
06:26
we'll get a bunch of columns,
06:28
which is no good for us right now. So let's be more specific. Let's do orders.
06:32
Not.
06:34
This is where then tell us since comes in very helpful.
06:38
So just see. Ah, let's see, we want the order number
06:41
on. Then let's grab something from order details
06:46
dot
06:47
I'll stay up Coach.
06:50
Well, we need to make a relationship between the tables. Right now, there's no relationship established between the tables, and we'll get wrong results. Usually
06:59
there are a few use cases for not establishing relationship, but that's not the case here. We're gonna take
07:06
for our first constraint
07:09
and with the orders dot order number
07:14
equal
07:15
order details
07:17
dot order number
07:21
that's going to run this. We're selecting the order number.
07:25
The product code
07:27
from orders and order details tables two tables
07:30
where the order number on one table is equal to the order number on another table. Let's give that a run.
07:38
All right,
07:39
so if we look at this, will see that 10 123
07:43
had four products associated with that order number. So we know we're pretty confident. Anyway, when this customer placed this order, he ordered four things.
07:53
Now, let's go ahead and filter out the other order numbers by making an extra constraint.
07:58
We're gonna say where and
08:01
cause once you've used the key word where
08:03
to start
08:05
and then and to continue the constraints. So we're going to say and orders dot order number.
08:11
Oh, that's my orders, right? That's very important.
08:15
Got order number
08:18
equal
08:18
10. 123 No comments here.
08:22
De Beber is kind of Ah, this is one of the things I'm not too fond of. There's probably a way to turn it off, but in the database there is no comma in the data.
08:31
And if we put a common there,
08:33
they won't work.
08:33
See,
08:35
womb failed.
08:37
We'll get rid of that comet will work.
08:41
There we go. So there's those four. We've narrowed it down to justice order number. Let's find out what those products are. And to do that, we need to add one more table. The product stable. So comma
08:52
products.
08:54
There we go.
08:56
Now. We haven't connected
08:58
any relationship. What happens if without a relationship,
09:01
we run the quarry? Well, we're gonna see bad results. Let me show you what I'm talking about.
09:07
Boom. Okay, so what is going on here? Why do we get all these results? Fact that I can tell you right now are incorrect.
09:13
Well, what's going on here is because we haven't defined our relationship.
09:16
My sequel saying, Hey, you've added this table. You having to find a relationship? So you know what? I'm gonna D'oh!
09:24
I'm going to give you a record back for every product that exists in the products table because
09:31
you haven't stated explicitly what you're looking for.
09:35
And that's wrong. In most cases,
09:39
we may see a case where we want to do that, but that's not the case here. So we need to add that relationship to fix this. And I like to keep relationships to the better.
09:48
I'm sorry. I like to keep the relationships together
09:52
and the constraints at the bottom. So what we're gonna do is we're gonna go above the and orders dot Order number equals
09:58
10 1 23 and we're gonna
10:00
add the other relationship that the table has. So we're gonna
10:03
hit, enter right there, type the word. And again
10:07
we're gonna say
10:09
order details,
10:13
detail. Spell it right,
10:16
doc. Product code
10:18
equals
10:20
product.
10:24
That product heard.
10:28
Okay, so now we've established a relationship. Let's look at that skim again.
10:31
We could see that the product code is a key over here. We see the product code is a key. Over here, you can see we have a 1 to 1 and only one relationship again.
10:43
And when we run this query, we should see this clean up quite nicely. So let's give that a run.
10:50
And sure enough, it did it drop back down to the four records that we had before. But we don't have the product name. Let's bring that into the
10:58
into the decree. So it's a comma
11:01
table name
11:03
dot
11:07
There's our product name. Let's give that a click
11:09
and let's play that.
11:11
There we go. Okay, those are cars,
11:15
so this guy must have a fair amount of money because it would appear that within within order 10 123
11:22
this customer purchased four products and those four products were different types of cars. Anyways, this lesson starting to get a little bit long. We're at about 13 minutes.
11:35
I hope you enjoy this lesson and I will see you in the next lesson. Thank you.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor