Nuclear is a day Yeah Write a query to display the Florida description for the class description and product price of all products which are shipped Okay so we have So you use these four tables by because we want product description I think product description is in the table Products product class description is in the table for the class Product price isn’t fraud A table of all products which are shipped and I think ship business or the right terms and order Header So let’s look at the diagram So we want product class description product price So these are the three things that we need but we want only for orders which are shipped.
Okay, So our debts ship this from the order so we have to join this product with the order header, Okay And then from the order header, we will get our order status which is shipped And uh why we have to join it with order items Was it anything here Product price which is shipped They brought the ridicule to you Product idea Ito product table mayhem four Oh ha Product market Your product idea or that idea handling caught They have the ship that you have all your hands Okay so that’s why we have to join four tables Okay so let’s quickly do that Celek Let’s first joined papers from broad ups Product Join the broader class on P and PC on Be dark for the class court Is he called to PC Dark Okay now I’m going to tell you one more thing Now the way we know how to join papers So there is one more method off joining the paper.
Then we know that Then the column There is a column of common column there in the two papers for the class court and for the class code here there is one more met occurred which you can use so people pay attention Look I’m doing it So instead of writing these two lines I will just simply write this line natural joint and get rid off this thing huh When we say natural joint What a school thinks natural means the same keys in the stable and then the same keys in this table Whatever common key it finds it automatically Joints huh No no Again one by one That the way I told you Not like natural joined this natural joint Uh huh Yes What even But he’s that same The column Name is same column Name needs to be same If it’s the friend you can use natural joint who live in the column names our C Huh Remember when we were doing the dot manager I physical to eat dot employees I didn’t There we can use it because the column names are different But when were you doing either or department I physical toe DDR department I did there we can use.
Okay so let’s try this one Okay Unless let’s ah b dawg product I d Oh my be dot product description coma pc dot product class description Okay then Natural joint Now let’s see if there is common key support the tidy and the product idea in order items So there is a common key I can do natural joint Okay So what was the question was right Equity to display the product description product class description and product price What is the product price There is a product price in the product table only I think so Let me change this a little bit So let me do or dot or I don’t ordo I d So for this order they have placed How many products Um 1234 by four items they have placed in the first order One was the TV one was in front sleepwear.
Samsung Galaxy and four-level premium chair And this was the product price And so what else were they Only want to see where the products are shipped Okay so let me show the ship also What is shipping That is order status Right And that is in the hole I table order items stable I think No our district is in order Header story Okay so ship ship ship but there must be few which were not shipped in process We don’t want the products which are in process Okay so we want to get rid of those so we just put a filter their Order status is a call to Yep Okay So before there were 205 rose only now 1 68 rows and hotels Oliver bitter ship bets it So this is my quickie Okay I’m gonna go tired also Should we go third and then take a break Okay So natural joint I told you normally how will doe Normally we will use the simple inner joint right.
So we warned Don’t find out Select Plus name Last name the Bosman name So because we want the five men name we want to join employee table with the department table So this is how I start You do right one dog department I d is equal Toby dot department idea Initially we should start with This is the logic, Okay A sometimes we may he dot employee I d is a call to something else dot manager I d When the column names are different Vendor column names are same We can use natural joint But when the column names off joining are different Then we could not use natural joint because here the column names are seen We can replace this These two lines with this line from and e you can give in Yes You can’t give alias It’s up to you Natural joint department B and you don’t need to give on what.
Because natural joint or automatically thinks that there is some common column between the two papers It automatically picks department I d from employees department I d from department Enjoy So like in this example if I write on on on the query becomes a little complicated Okay so we can use lend the also so we can use natural joint But have you have to be 100% confident that the name of the columns in those two tables are Exactly since only then natural Joinville book How envy All there should be only even call them That can’t be two columns of two Collins again It will get confused OK so only one common column He couldn’t do the third one Then we go for a break then go Don’t use natural joint Go by simple joint and palette on which column you want to join.
Okay The tired one Write a query to display the customer I d customer name email and order details And they have given the order details Sub tota product wanted at tow product price for all customers even if they have not ordered any items Okay Huh So what do you think Even if they have not ordered any items Okay so customer places order So online customer and the order header These are the two called to Tabor’s which has information about what order I have pleased Okay so then I joined these two tables Let’s say there is a customer who has not placed an order yet I have joined flip card today only I have not placed and the order But the flip card CEO wants to find out the list off all of its customers Even if they have placed in order.
If their place in order tell me the order detail if they have not placed an order Still I want to see those customers which are there which have signed it on my website Okay if I do simple joined here in our joint order header and all line customer You think you will get the customer which have not placed an order No you will not get it Because in a joint means only when I have the customer I d here in the order header because I’m joining It will only join the that it will give me the Calman things Customers who have placed an order Only those one he’s saying Give me the customers who have not placed an order Give me those customers also So what will we do huh We will look if we’re keeping the customer table on left France I we will place then left outer Okay let’s quickly build this one This looks like a complicated one but I’m telling you to step by step if you do it it’s not that difficult.
So let’s just first select this customer idea Customer name Email Select customer I d Customer name Email from Oh line customer Okay because tomorrow Okay so lets Koncak customer f name goes to my l name and call it well name customer email everybody Okay Palau just customer information I want his order information So order details I want this information or the right day Product description Product quantity Okay lets me show Let me see the order one Order head Oh order I d Mama what is a product description that we don’t have Okay so let me first join this on product quantity Also they want so shouldn’t or doing it.
Okay, we have to join in on that online customer natural joint or does head Oh right So calling customer natural joint order Heather it was joined it on the customer I d That order Heather Natural joined with order items that will join it on the order I d This is customer This is uh order head Oh and this is order items Okay I have to give customer I dc dot advisedly complain and uh order I d I’ll also start and let’s try to run this one So we’re getting some data and then we want from the order items Rod that I d broader quantity And I’ve also want product description Right So for that I have to again do natural join with the table product and then I’m going toe product table and then product description Hey product ideas in many So let’s be dark product tidy Let’s run this fun Oh broad So this person has placed an order.
This is a product I did This is a product description and these are the number of products he has ordered Everybody getting it right Okay What else So we have done the customer I d customer name email or the righty for the description Further quantity Okay so let’s try now Product quantity in two Product price subtotal It’s a portal related Let’s just first find out this column Okay So product quantity into product price And then this is okay for the country Prior price And be only and we want it subtotal sub total means some Okay Sapkota Based on what Let’s see Oh for all customers even if they have not ordered any items.
So let’s don’t group by customer um for all costs up to okay Oh actually it’s not some Let’s not do some Okay so this is a product quantity and this is the product price and we’re doing multiplication or board to get okay Because the person has ordered three in front seats It has given me to 50 multiplied by 37 $50 for the product quantity into product price So subtotal doesn’t mean sub total It means that we’re doing that subtotal by doing product quantity at two product price Okay And then they’re saying even if they have not ordered any items So you will see here people only who has placed an order Let’s say there are a few people 205 rose They have not placed in order yet So what we do we cannot go Ah the natural joint here So we have to do here Full not for left out of joint So before there were 205 rows.