Summary
SELECT s.sname FROM supplier s JOIN catalog c ON s.sid = c.sid JOIN parts p ON c.pid = p.pid WHERE p.color = 'red'Notes
Transcript
which is most likely coming from supplier entity. And then looking for, Um... Fats. When I look here, parts is a What pass is this one? Okay, the color which is red coming from parts. So, probably your query should look More like... Pick it from two. Begin from two cables. So it's going to be like select. Select Select I don't know if I have a black one. So, sorry. Somebody who's trying to answer that question?
Number one. Select From Wait. So... The first thing you need to take care of is which table is this coming from. You most likely gonna be picking from Supplier, so you come here, supplier. You can put the alias alias as S. I'm in putts. I use parts as p. Okay. Where is the air conditioned? The color is red. Colour is some colours red So where Most likely Remember we are reading, we are writing code that runs.
Even if you're gonna miss this You lose points Okay.
Close. If you are signing something It needs to be that stream neutral. So somebody can say, "Okay, what I mean, I want names."S name. Estos es ne. Supplier names? From sub-flyer Who supplied Salmon-rated PUNTS! Sun-red Sun-red parts I go back to my schema And... I see. Supplier names come from supplier, good. Color coming from parts good. They told me that cost So...
Is this correct? Sorry?
Why?
We have to join PNS.
How can we... Ok, let's continue Finish it, how did you do the pianist? What did I put here?
Point speed on. Yes?
Okay, you need to look at it. How can I continue when we are joining?
Start from part B, right side of part B.
Um. Yeah, okay.
Yeah, on.
On, what is joy? On goes with joy. I'm using Cartesian. This is a Cartesian, so I don't have to use join.
Yeah, but you're writing from the syntax for SQL.
You want me to write one here? Okay, let me write what you say.
You can remove that and you can start after wear also.
Wait, okay here. Okay. So you basically say I should say n.
Oh, yeah. Uh-huh. Join our team.
Okay, let me write. Joint.
Part B. That's E, R. It's not all clear. PID? PID. Equal to? We don't have BID. and We have a good bowl of sultanes. I mean, we don't have-S name in parts, right? So how we can get from parts table without joining supplies and parts? Okay.
So that's where I'm going. So he finally found out that he cannot do these two. We cannot strike the pain. And this we call upon the catalog. But I'm going to find people, continue, going to say this one is equal to PID. No, SPID. So Please understand the schema. So I just wanted you to know that, hey, But the fact that you see that The moment you see this, this is a bridge table For these two people to talk, they go through the catalogue So you are asking me to pick one A name from supplier and pass on this, the catalogue must be there.
Common Catalog See, it is possible now. Okay, it's possible and he can now go on to come here and say PID C.P.I.D. and you join also the other one So I wanted to use Cartesian.
I didn't want to use the join.
So I just have to say and-So PID is going to be, let me start with SSID is equal to C.SID. Eight. H p.p.i.d is equal to p dot p-It's going to be c dot-Thank you. So this is now possible. And I'll take you For people who want to understand the schemas very well, When you look at the schema you just need to know that hey, this is a bridge entity. A bridge entity is the one that comes in the middle. So we have two.
This is supplier This is supplier and this is the ERD. This is a player, they're joining with a Clutch. Supplier joining with parts is Many to many. Okay, this is many to many. Many to many. A many-to-many is never implemented. Many to many,Easy. It's broken down into the one-to-many. So you come here You have to bring it A brick entity Which is catalog? This can be anything. So if I am connecting My ELD is going to look like that.
Okay. It is coming to breaking and that's why you see the entries here. The entries here are primary keys of those parts disconnecting. That's why we call it a bridge entity. Yes, it can have independent, it can have It can have attributes we call relational attributes. This cost It's useless if you don't show me some information from supplier and some information in parts. So... If you're using UML notation, this will be the right way of representing.
So I have primary key here, SID. Do you have 4A key here? F key is none Um.
We have this name An address, I think. Here we have PK is PID FK is none. McKin. and For catalog we have PK is PID plus SID. And if K One is PID.
FK2 FK2 is SID And the cost is relationship I see Of course, since this is a many-to-many, this is going to be 1.0.star, which is many, this is 1.0.1, this is 1.0.1, and this is 1.0.2. Buzz Tatters And this can be supplier, supplies And this can be a verb like, the catalog has so many parts. So, Where is this line? This piece, SID is going to talk to this SID. This SIB Let me just say this is SID, this is PID.
This SID talks to this SID as a foreign kid I saw in the link. And then this PID talks to this. PID and that's how they take. If you understand this, Okay, there are questions that are gonna be like the first one It's going to basically read you to something like this. They say, get me supplier's name, full supplier. Some red hearts. You know that pig. I want to pick this one And this one, we have here color I want this and this, I have to make sure this is involved.
The middle entity is evolved. And that's why this is possible. Now. You understanding the ERD, you understanding the Um... That's too well. And also you going to understand the relation algebra. How do you write the relation of algebra of this state 28? You're gonna write this in bits When I say in beats I mean... In segments, in confidence. So because this is like a complex, It's like a complex Statement So we always start from the inputs from represent from So you can come here and say we are letting supplier V is POTS.
BP Talk to love. BC, okay. So this is a Cartesian. Okay, so that means you're talking about is Cartesian P.
Station C.
These I want immediately to bring them fine Um... Condition. I'm being SID and that is this This is That is the sigma that's going to tell us, that's going to Indicate The sigma is going to indicate the conditions and I have two conditions I want to.
I'm saying This S has SID. which is equated to the C that has this ID.
And this and in version algebra Is that out of it? and then I have the P, PID is equal to C dot PID Then I am going to first raise this in the version 1. So that is relation 1. So what have I accommodated? I have accommodated this and this. I have one more condition to go. Go to sleep. So my next one, I'm going to test my next one with color. So My input is gonna be my intermediate. My intermediate, R1.
So I will say R1. Is where we're gonna come from and I am going to Only filter the pink colorBe kind. But now there's going to be R1, the color is R1 color. Ezekiel 2Great. And I'm going to raise this as relation two. Intermediate 2. So this is taken care of. I'm only left with a projection. Which I'm going to call my "Out of Three." So, ah, this is, yeah, so R project R2 and projection is like this.
I'm projecting R2 dot s name This is R3 and R3 is my final result.
Final result.
So, this is the easiest way you can break down something. Start with the input. Go with the condition where Okay, then projection. Now I guess you have revised Aggregation. If somewhere is aggregated, I hope you revise group by. If you've noticed, this document has all those things. This document that you see here, The document that you see here has various examples. Okay, you can also look at... Yes, so...
Yeah, how do you read this? So I just showed you this part and this part, but the solutions are down here. So number one, our number one solutions are here. They show you the relational algebra and then also the calculus, but pay attention to RA and S2L. So They wrote the S2L, another person may want to write their S2L differences. So you can see their S2L are the same. Again. Number two, if you want to go to number two, number two is right here.
You want to see if the SID supplier will supply some red or green. So look at the S2L for number 2 is this one. S12 for number 2 is here. The relational algebra is something like this. It has so many examples of different schemas and that. So those are just ten questions. For that one problem, you can look at another problem when they give you a different relationship schema. flight aircraft certified employees.
Okay, if you look at that schema, can you draw an ERD like this? Can you interpret it? Can you know which are bridge entities and which are not bridge entities? If you can understand that, then it's going to be easy. If I'm going to ask you to get me data, if I say get me data from Um... From employee and flight. Employee and Flights Employee and flights. You have to go through, what is ERD? Employee and then flight.
So this Satified is to be there. You know? This naked temple has to be there. So you always need to be-and that's where people lose points. So many questions here. And that you can, So always pay attention to this, you just need to sit down and then restart it. I am not going to tell you this but yeah, sit down, study this, Fubin, these are not the many of these. So this is just a Some document I uploaded, I don't know if you've been studying it that way, but this is something that I think It's worth looking at.
The other one is this document here. So there are two books talking about relation algebra. Okay, in that folder And this is also pretty much the same thing. If you want to start from the first Translating SQL into Russian Algebra, you can read everything from the first, but I mean, you know, you'll be given a schema, So you made that an interview. They give you a schema and they say normalize the schema.
You know... So they want to know if you know Um. Some principles. Okay, so these two documents including they have solutions. They should be able to guide you on how to write. Again, I have just shown you some basics of that, but pay attention to aggregate queries. If I come here and say group by, How is this going to change? Okay. If I come and give an aggregation here, I want S name group by and also aggregate.
Maybe I'm saying count. Count.
C cost. Osam.
Sea Cost How is this gonna change? Okay. If I want to filter on a group by havingHopping.
Okay. So understanding the schema is very important, otherwise you're going to be answering something that is off. So...
Natural shine. I don't know if you're doing this. Nice to join. Major J.T. That means you don't need this to be there. So if you have a larger joint T How would this look like? This will look like just that. Okay. Let me try to group by-Goodbye, B. You can see the group by a bee. If you're having a proof state you're going to filterWe're going to fluta. And we're going to throw time. Being a In this group by we want to filter By bee.
just getting it started but I mean if you have any query for example If we're gonna have So if you have a query like this So you want to write it down in-The easiest wayYou should stand by. So you want to first make this first relation, r1. He's picking from employee. So we're going to call it E. E2, like they call it, And it's an aggregation. You say it's a max month. So... This one, I don't know which side you're...
not out. Let me call it Y. So this is the input. Um... This is it. And we'll say We are getting the mats Maxima. Okay. Mars. And this is A2 dot Salary. This whole thing is projecting only that So, just aggregation sometimes is just projection. You don't have to You don't have to... Come here, can you project it? Projecting these and Giving all of these, you might give this whole thing at five years.
So this whole thing, someone can say, I want it to be renamed as Max.
So it's pointless for you to come here and say max salary. Okay, this aggregation alone is a projection. So from here, just say this is R1. This is your fascination. Your first lesson is going to be pointing to this attribute, max salary. Okay, then the outer query The altar query is picking from EE, employee E. So you can go on and say eat. Um... Where? That means we're going to use sigma.
to the original E employee table. So you can first call this R2. So somehow this has to If you want to get rid of If you want to get rid of The refining, refining where say PID is equal to PID in the other one. Yeah, so this will show you that, hey, this is how they join and you can see I'm using all So the wear is done. The other thing you want to do is project. You're going to project?
So if you ever come up with a complex If you ever come up with a complex attribute, complex schema, a SUR for man, you need to understand how You break that into two components and then you Alright, so I think we're going to stop here. So if you ever come up with a complex If you ever come up with a complex attribute, complex schema, a SUR for man, you need to understand how You break that into two components and then you Alright, so I think we're going to stop here.
γ grouping-attributes, aggregate-functions (Relation)
Relax로 공부할 것