Good morning, I will start my second lecture, but before that let me recapitulate the important concept that I discussed in my last lecture. After the brief introduction, I discussed diodes, the very important parameter in high frequency conversion is, one is the reverse recovery current and the reverse recovery time. These two are very important. How these parameters are used in design process? I will discuss at the appropriate time. Let me cover DC to DC conversion, let me and DC to AC conversion and when we are doing design of these converters towards the end, I will tell you how to take care of these parameters. Then I discussed about a BJT. Generally, NPN types are popular. Though the popularity of, though this BJTs are not very popular, they were popular in the 80s, I wanted you to appreciate, in order to appreciate MOSFET and IGBT, one need to know BJT. The problem with the BJT is, we found that as the power rating increases, the gain decreases. Basically, BJT is a current fed device. So, that if the gain comes down, the base current required to drive the BJT increases. So, therefore, in my control circuit or in my base drive circuit, I may require, I am using the word may, I may require a small power transistor to drive another power transistor. Basically, BJT is a current fed device and gain falls as the power rating increases. So, generally the BJT is operated in quasi saturation region. See, BJT has an excellent output characteristics. Output characteristics in the sense, during conduction, I can reduce or I can control the on-state voltage drop. I can reduce it. I can bring down to as low as VCE sat. Fine, my switching frequency may come down. That is the second issue. Then, it is possible to reduce the on-state conduction, on-state losses. The problem with BJT is, it is a current fed device and I require a large gate drive current. Now, let us see another device which is very popular is, especially in DC to DC conversion is MOSFET, power MOSFET. So, in 1978, 25 ampere power MOSFET invented. As of now, see the current rating, 200 volts, 500 ampere, semi-chron MOSFETs are available, 60 volts, 1000 ampere by semi-chron. These are available. See, low voltage, high current. Generally, MOSFETs are used in relatively low voltage at the high current application. Basically, they are very fast devices. Very popular in DC to DC conversion, MOSFET stands for metal oxide semiconductor field, in fact transistor, very fast device. It is a majority carrier device. It is a majority carrier device. Yeah, like BJT is again a non-latching device. I need to have a continuous gate drive. But what sort of a gate drive? We will see. The three terminals that we use in a BJT is base emitter collector, whereas in MOSFET, drain, source and gate. And gate signal is generally applied between gate and source. See the structure. We have an N plus, highly doped, N minus, lightly doped, P junction and N plus junction, gate, source and drain. Now, let us see, we will start from, current flow is from drain to source. We will start from drain to source. Let us see what happens. There is a N, P. So, see here D terminal is here N, cathode of a diode. There is anode here. Then again P N, again N, again N, P N and we reach the source. So, if you see two diodes, if I just see the structure, there are two diodes are connected back to back. So, looks like there cannot be a current flow. If I just see the structure, because two diodes are connected back to back, then, but then MOSFETs are do used in DC-DC conversion. Therefore, it has to conduct. So, how is that possible? Now, let us see what happens when I apply a suitable biasing voltage. See, gate is insulated from the rest of the device. I am telling, gate is insulated from the rest of the device. So, therefore, there is no steady state current, something equivalent to a parallel plate capacitor. So, it is only a displacement current can flow like a parallel plate capacitor. So, MOSFET is in cutoff when gate to source voltage is less than the threshold value. Again, this threshold value one need to see from the data sheet depending upon the voltage and power level. This value does change, but then when the applied voltage V G is greater than the threshold value, what happens is this. A small channel is formed between, I will tell you, a channel is formed between it and source. A channel, once the channel is formed, the electron starts flowing and therefore, the current. When the applied voltage is higher than the gate to threshold value, a channel is formed, electron starts flowing and therefore, the current. So, on conduction or during conduction, how does the MOSFET look like? It is like a flow of water through pipe, it faces only the resistance. So, therefore, during conduction MOSFET appears as if it is a resistive element. So, on state losses are I D square, I D is the drain current into R D S on drain to source resistance R D S on. So, if I compare BJT and MOSFET during conduction mode, on state losses in a MOSFET are slightly higher because I told you it is appears as a purely resistive element R D S on. So, the if I draw the omic, the V I care to say they look something like this, something similar to a BJT active and this region is known as the omic region. V and I is approximately a straight line, approximately a light line. The slope of this is the R D S on, R D S on. So, generally we operate the MOSFET in this zone. See here on on state, the channel of the device behaves like a resistance is R D S on, it is the slope of V D S divided by delta V D S divided by delta I D conduction. During conduction, conduction power loss is given by I D square into R D S on. So, outputs like for a for a for a given power level, if I use a BJT, on state power losses are slightly less. So, in other words, output state of a BJT is slightly superior compared to that of a MOSFET. Now, what happens to the input stage? In other words, in other words, the driver circuit. I told you here the structure looks gate is gate and source are isolated. There is gate is insulated from the rest of the device insulated. So, if I apply a potential across it, only the displacement current can flow, something similar to a parallel place capacitor. So, how do I turn it on? By suitably applying a voltage across a gate to source. In other words, I need to charge, I have to charge the gate to source capacitor. I will repeat, I have to charge the gate to source capacitor. So, input impedance at the gate source is tends to infinity. So, in other words, I require a very small steady state current, very small, practically negligible. It is a voltage driven device. It is not a voltage fed device. It is not a current fed device unlike a BJT. So, how do I increase the turn on time of a BJT? I said turn on time of a BJT can be decreased by supplying a high pulse of base current during turn on. So, during turn on period you supply a slightly high pulse current, whereas in a MOSFET, faster that I charge the capacitor, the faster I charge the capacitor between gate to source, faster is the turn on. So, by the way, how do I charge the capacitor at a very fast rate? So, maybe I need to supply initially a pulse current. That current has to flow from the control circuit. Fortunately, that is only during starting this, the charging of the capacitor that is very small. Of course, one way how do I charge this capacitor? Suppose I have, see here I have a problem. If I take gate and source, if this is a voltage source, the rate at which I charge this capacitor will determine the T on. So, if I, of course, I need to connect a very small resistance to limit the starting current. Otherwise, current is limited by the so called the internal resistance of this voltage source. So, the value of this R, external R will determine the charging rate. Smaller the resistance, faster is the turn on, but the smaller the resistance, higher is the peak pulse current that has to flow through this source and this may be a switch. Of course, this switch is generally realized by gates. So, you need to take care of while selecting this R. Maybe sometime later I will discuss the gate drive circuit and how to choose this value of R. So, gate drive requirement for a MOSFET is very minimal compared to or the power rating of a gate of the gate drive circuit for a MOSFET is very small compared to that of a BJT. Basically so happens that it is a voltage fed device and BJT happens like a current fed device. By the way, if you see the structure here, structure here, there is a inherent body diode between drain and source. Something like this, if you see the structure, the MOSFET has a built in body diode, but then what is being done is the speeds are not very comparable with that of a MOSFET. So, generally an external body diode during fabrication, during fabrication, during fabrication this diode is incorporated there. So, how is the characteristic similar to that of a MOSFET? Safe operating area of a MOSFET is a majority carrier device. So, therefore, it has a positive resistance coefficient, positive resistance coefficient. In other words, as the temperature increases, resistance increases. So, therefore, paralleling is easy compared to that of a BJT. BJT paralleling is difficult because it is a minority carrier device and it has a negative resistance coefficient. And if I see the safe operating area of a BJT, there are four zones. They are current limit, voltage limit, power limit and the fourth one is the secondary breakdown. Secondary breakdown is due to the negative resistance coefficient. Whereas, the secondary breakdown is absent in MOS. There are only three zones, current limit, voltage limit and this is the junction temperature limit. Just show you some of the parameters of a MOSFET IRF 640. See, for a drain current of 18 amperes, RDS on is of the order of 0.18 ohms. So, on state power loss is assuming that drain current is 18 amperes. Of course, 18 is the rated current. We may not be able to supply that it may not be able to carry 18 amperes during normal condition. So, I DS square 18 square into RDS on. So, that is the on state power loss and one has to choose the heat sink accordingly. Now, see here the gate to threshold voltage to turn it on is of the order of plus or minus 20 volts. Plus or minus 20 volts to turn it on and to turn it off. See, here the timings turn on delay 14 nanoseconds, rise time 51 nanoseconds, turn off delay 45 nanoseconds and fall time is of the order of 36 nanoseconds. You just compare yesterday I did I think I gave you the characteristics of a 8 ampere BJT and today you compare I am discussing the 18 ampere MOSFET and compare the timings turn on delay. They are in terms of nanoseconds and see here gate to source charge is 13 nanoculums from there you can calculate the capricent and therefore, the current that is required to charge that capacitance. So, if I compare the difference between a MOSFET and a BJT current control device is a voltage control device minority carrier device is a majority carrier device. Therefore, negative resistance and a positive resistance coefficient has secondary breakdown no secondary breakdown paralleling is difficult paralleling is easy on state power loss B C sat into I C is low I D squared R D S on is higher than on state loss of BJT turn off time is higher because if I saturate it as what is known as a storage time plus the fall time whereas, it is a very fast device gate draw requirement as minimum and MOSFET and you are much higher compared to in BJT is much higher compared to MOS. So, the problem with the MOS is the on state power loss is there a way out yes what is there is what is known as a cool MOS as the name suggests it has a very low on state resistance conduction losses are very low, but then they are very expensive. So, that is about MOSFET. So, we have discussed BJT and MOSFET BJT seem to be having excellent output characteristics in other or the on state power loss seem to be low, but then gate draw requirement is high whereas, MOS seem to have an excellent input characteristics input impedance at the gate is really tends to infinity. So, gate draw requirement is minimum there, but then during conduction on state power losses are high. So, what could be an ideal device according to me in the ideal device is a device which is having the input characteristics similar to that of a MOS and output characteristics similar to that of a BJT. So, therefore, hence the name insulated gate bipolar transistor and IGBT insulated gate something similar to MOSFET output is a BJT bipolar junction transistor. I said in insulated gate in MOS structure current is only due to the majority carrier device, but then in BJT sorry in IGBT BT stands for bipolar junction transistor. So, there is going to be I need to modify the existing MOS structure. So, what price that I am paying we will see by the way this was invented by Jainth Baliga in 1983. Here is the structure of an IGBT I have added or there is another P structure here P layer n plus n minus P plus n minus this is the MOS structure there is an additional P layer P plus. So, what happens when I apply a suitable gate to source voltage here what will happen now poles start moving towards n electrons start flowing to n minus region that is. So, therefore, n minus layer receives electrons from n plus as well as poles from P plus some of them recombine and remaining electrons are collected at the source. So, junctions of poles are injected in n minus region some electrons combined with the holes remaining holes are collected at the source. Now, because of this P structure we have a P plus and n plus structure. So, junction J 1 now can block a negative voltage come on in the sense this voltage if I apply a negative voltage it can this junction J 1 if I connect drain to a negative of the source and if I make source positive this junction gets reverse biased and therefore, it can block a negative voltage can block a negative voltage. But then if you see the structure I have a P plus and n plus. So, plus indicates is a highly doped. So, therefore, the reverse breakdown voltage of this junction is relatively low relatively low. So, the V I characteristics something similar to that of V J T, but then we had a, but then control parameter is the gate current here it is a V G S gate to source voltage this is for the comparison between I G B T and a MOS see the modified structure see the slope of this line and the slope of this line. So, on straight losses in I G B T are slightly less compared to that of a MOS by the way coming back to the structure do I require this n plus layer what if I or what happens if you remove this n plus layer it will still work like an I G B T same thing P n P n now there is P plus n plus n minus P plus n minus. So, if I if there is no n plus layer I have only P plus and n minus n minus indicates is a lightly doped. So, so if that is the case this junction J 1 can block a higher voltage. Now, due to this n plus layer this J 1 cannot block a higher voltage. So, n plus layer between P plus and n minus drift is not essential for operation of an I G B T some I have just n minus layer. So, those are known as non punch through non punch NPT I G B T. So, if both are present n plus as well as n minus are present they are known as punch through I G B T's punch through I G B T's, but then what are the advantages of NPT NPT. So, non punch through wherein there is no n plus it can block a higher voltage. Now, so it is almost symmetrical both it can block positive as well as negative voltage it is what is known as a symmetrical device. Symmetrical device whereas, whereas if there is a n plus layer it can block a higher voltage in the positive when it is forward biased and it can block a smaller voltage when it is reverse biased. So, it is a non symmetrical device. So, in other words NPT you may be able to use it in AC supply, but then a punch through you cannot use it. So, see the structure here there is no n plus here this is n plus. So, this is a symmetrical device both it can block positive as well as negative voltage this device cannot block negative voltage because of this junction or if at all if it blocks it is a very small voltage. Now, turn on and turn off an I G B T turn off mode turn on something similar to that of a mass now turn off there are there is a mass as well as the BJT in an I G B T both are combined there are two distinct turn off intervals during turn off. See here drain current and drain to source voltage I am we are reducing it. So, first the channel the whether the MOSFET the current due to the MOSFET effect reduces the channel disappears quickly MOSFET blocks, but then the or this is due to the majority carriers, but then minority carriers in n minus layer gradually recombine that is something known as the BJT current or what is known as the tail current. It takes see minority trapped in n minus layer they gradually combine it takes significant time to recombine or this process is relatively slow, but that but that the, but then the voltage across the device starts increasing. So, during this period of BJT current what is known as the tail current voltage across the device as as increased it is in the process of blocking mode voltage is high current is still finite. So, they are going to be of substantial as significant power loss during turn off. So, tail current is a very important parameter and that determines the turn off process. So, I D is relatively slow known as a train current voltage this period should be very small since VDS has attained reasonably a high value losses are high, but then this time punch through I G B T has a a smaller tail time why there is an n plus layer n plus layer n minus layer the minority carriers are trapped it takes time to recombine, but then here the concentration is high. So, therefore, it takes lesser time to recombine. So, tail current tail or this T F fault time is relatively smaller in case of punch through a BJT punch through I G B T just see the time storage time something similar to that of a BJT MOSFET current as reduces faster and this current take its own time. This process is there in almost all the devices even is there in GTO I have not covered GTO because I do not think the GTO is on this way out or it is by the way it is not very popular there is a reason I have not covered safe operating area I G B T that is about it finally, the smart power modules they have the entire bridge driver circuit as well as protection circuit just the power modules if there are only power module I need to design a driver circuit as well as the protection circuit all this protection circuit have to be designed in the gate driver itself. Whereas, a smart power module everything is incorporated or fabric during the fabrication so, size of the entire power electronic equipment reduces significantly. So, one such IPM is all the terminals protection gate drive circuit are brought out these are three output terminals plus minus and the driver terminals they are brought out in part modules. So, that is about devices we started from one device single three leged element two power modules and from there to a spot smart power modules that is a significant reduction in size and therefore, the space that occupied by this devices this about the devices I will take up few questions. Okay regarding IPM modules where we can get the smaller power modules and India? No, no we have to import them there are Indian agents maybe I will I will. Okay sir thanks sir. Amritha Coimbatore please go ahead with your question. Sir, I just want to ask regarding the resistance which is used to limit the starting current when we are trying to charge the gate source capacitance. Yes, I will cover this at the appropriate time see there is a you need to pay a price you have to see the data sheet and you have to find out from there see here I need to charge this now you need to tell me how do I charge this capacitor at a very fast rate gate and source assume that there is a capacitor in fact there is one I need to charge this at a very fast rate ideal condition is hot and this external resistance tends to 0 are you with me? Take for example this is a voltage source I need to charge this capacitor. Okay now smaller the resistance faster the turn on faster the charging time smaller the resistance faster is the charging and first then therefore faster the turn on smaller the resistance higher is the current this switch and the source has to supply this source may be able to supply but then this switch that I have to realize through gates may not be able to supply that much of current. So, we need to see how much is the capacitance here and what sort of a gate or the driver circuit what sort of a buffer that I am using and based on that we can decide. Hang on for some time I did tell you that during design time I will tell you how to choose this value of R I did tell you that yeah I will tell you in the sense if you are using a gate that then you need to find out the fan out or fan in capacity of the of those gates or what you can do is I have a gate buffer then may be a small this is a buffer then may be a small signal transistor a small signal transistor and then connect a small suitable resistor and this I can directly connect a buffer to or through a small resistor here to a capacitor if this is not able to supply this current you use a small signal transistor and thereby put a resistor and you need to choose the first I need to know the type of MOS various capacity current carrying capacity of this and only then we can decide this value of R one have to see the data sheets I will do this I will take a typical example and solve it in the class do not worry. Sir we know very well about BZT's and MOSFET and nowadays light activated SCR is in market if we use that light activated SCR for the reception of the radiation from the solar radiation or is there any plan in future to produce SCR triac those devices for power electronic applications. See I do not know in the sense what happens in the future I do not know but then one thing is for sure SCR is a basically an ideal device I do not require a continuous gate drive my continuous just a pulse I require just a sharp pulse during torno so my entire gate drive circuit the size of the gate drive circuit reduces significantly if I come unfortunately when I converted to AC I need to have a grid there for for commutating the SCR line commutation so otherwise in a DC to DC conversion I cannot use SCR if I use then I have to use force commutation so therefore they are not being used that is besides reason we do not even teach force commutation circuit in the class also I do not know an ideal device could be yes torno of having a device which can be turned on by just by a pulse and turn off using gate I do not know time will tell time will tell whether such devices will come Vijay Vada whereas in schematic you have shown that the drain is connected to the body which one is right which one though there is a no difference between as for the geometries there will be no difference between source and drain normally source will be shorter to see it is a it is a channel no no no listen it is a channel it is a bipolar it can it can it can conduct in both directions you have to suitably apply gate drive signal and therefore MOS can carry current in both directions no issues depending upon the biasing depending upon the biasing current can from source to drain both it is possible but then you need to apply suitable gate to gate signals it is the flow it is the flow of electrons or at the is the direct during conduction I said a channel is formed and the electrons are flowing it is something similar to that of a water flow through a pipe so it is it can current can flow in both the directions no issues. Shivaji university Kolhapur please go ahead with your question will you relate the two diode structure of MOSFET with the diagram what we have shown that is just from the structural point of view once you apply a gate to source signal that structure is not valid I told you a channel is formed and flow of currents starts see here this is the structure start from N to source N P P N so if if I just look at the structure yes there cannot be a power there cannot be a current flow but then things change when I apply a suitable voltage at the gate to gate between gate and source above the threshold a channel from and therefore a current starts flowing if I just look at the structure yes there cannot be a current flow because there are two diodes connected back to back. Sir generally this N plus is used for low resistance connection N plus is a highly doped only the doping level. Yes it is drain where it is connected to the drain but it is used generally used as a low low resistance connection part. Ok so so what. Then that side what we have shown that is C and N plus packet on. How does it matter see I can show only one layer does not matter N minus will determine or the thickness of N minus will determine the voltage rating. Yes KJ Somya please go ahead with your question. There is an inbuilt body drain on the MOSFET. So if reverse current has to flow through what it will flow through the diode or through the structure. That depends that depends on what exactly you want whether you want whether see it depends on the voltage when the diode the voltage drops of the diode or the voltage drop across the MOSFET are you with me diode also can carry current the MOSFET also carry current. Now now it all depends on the supply voltages power loss across a power loss during the conduction period of diode and power loss during the conduction of MOS that will determine whether you want to operate whether you want to make diode to conduct or to make MOSFET to conduct is entirely up to you is entirely up to you. If you do not apply suitable gate signal diode will carry current diode will start carrying current and you apply suitably bias the gate MOSFET MOSFET will start conducting current are you with me you can make diode if there is no gate current and there is a current flow that current diode starts conducting and if you apply in order to reduce the losses if you want to make the BJT to MOSFET to conduct you have to apply suitable gate drive to the MOSFET it is what is known as the synchronous converter. There is a question NPT IGBT or IGBT for solar inverter is my question do not worry let us see whether we will first we will discuss the inverter let us see whether it has to block a negative voltage or not and if it has to block negative voltage then we will choose the appropriate device let us not discuss or let us not try to try to find an answer to this question right now first we will see what sort of a voltage that device has to block in that solar inverter if it has to block a negative voltage then yes you have to choose an appropriate IGBT.


Game two of the Bemidji boys hockey season was bound to be a good one. The Jacks traveled the face Moorhead for a rematch of the Section 8 2A championship from a year ago. Third period, Jacks trailing by a goal, Brady Tatro gets denied at the net, but Nick Manasal is there to clean up and light the lamp. Bemidji ties it with just under three to go. But 52 seconds later, the Spuds would answer Cole O'Connell beats Matt Fitzgerald on the odd man rush. Spuds take the 2-1 lead, they'd add two empty netters, they take down Bemidji tonight by a final of four to one in that one. If you've enjoyed this segment of Lakeland News, please consider making a tax-deductible contribution to Lakeland Public Television.


What's the difference among like, be like, look like, and would like? This free English lesson is sponsored by the following English learning sites. In this video, you will learn about the differences among these expressions, like, be like, look like, and would like. Look at the following examples. Like, Jack likes apples. Be like, Jack is like his sister. Look like, Jack looks like his father. Would like, Jack would like some coffee. The word like can be used in different ways in English. Some people find this confusing. 1. Let's start with the verb like. The verb like indicates a preference for something. It is always followed by an object. Be careful with third person singular. Questions involve a form of do. The verb like indicates a preference for something. Use it with an object. For example, I like grapes. Subject verb object. When speaking generally, you can use the plural form of count nouns. I like computers. Use third person singular with he, she, it, and singular nouns in the present tense. My daughter likes animals. She really likes stuffed animals. In questions, use some form of do. Do you like seafood? With non-count nouns, when speaking generally, no article is needed. When forming questions, use does with third person singular nouns and pronouns in the present tense. Does John like pancakes? Yes, I think he does. Big mistake 1. Do not use very before like. Use it after the object. I very like sports. Incorrect. I like sports very much. I like them a lot. I really like sports. Big mistake 2. Don't use a form of be with like when indicating preference. Is Mindy like shoes? Mindy is like shoes. Both of these are incorrect. Correct. Does Mindy like shoes? Yes, Mindy likes them a lot. Big mistake 3. Don't forget to use a form of do in questions with like. What you like to drink? Incorrect. What do you like to drink? Correct. Big mistake 4. Do not use like without an object. Not even when answering questions. Do you like books? Yes, I like. Incorrect. Yes, I do. Yes, I like them. I like books. Number 2. Be like. Be like indicates that something is similar to something else, usually in personality or some other characteristic. Sarah is like her sister. They are both talkative. The word like here is actually a preposition. The main verb is be. The verb must agree with its subject, especially in the present tense. The word like does not change. George is like the professor. We are like our parents. I am like my big brother. 3. Look like. Look like means that something resembles something else, usually physically or in an appearance. The first dog looks like the second dog. Just remember, the main verb look carries the tense and number. It may change but the word like in this situation does not. He looks like John T. Volta. They look like movie stars. Yesterday I looked like a clown. 4. Would like. The word like is used to express a choice or politely indicate a need. It is often used when offering something to guests. Would like can be shortened to would like. For example, I would like a cup of coffee, please. I'd like a cup of coffee, please. This is more common. Use would like when offering something to guests. Would you like a drink? Yes, I'd like a glass of milk. Test yourself. Tell whether the following sentences are correct or incorrect. Number 1. Jackie is like Kung Fu. Incorrect. Jackie likes Kung Fu. Correct. 2. Gary like oranges and bananas. Incorrect. Gary likes oranges and bananas. 3. Rosie looks like her mother. Correct. Rosie looks like her mother. She resembles her mother. 4. Candice very likes spaghetti. Incorrect. Candice likes spaghetti very much. I'd like a glass of water, please. Correct.  It's your turn to talk. Make a few sentences using these expressions. I like. But I don't like. I like something very much. Pause the video and practice. Talk about others' preferences. My best friend likes. She likes. My cat likes. My parents like. They like. Go ahead and pause the video if you'd like more time. Talk about similarities and personality. My best friend is like someone else. My brothers are like other people. Who are you like? I am like. Go ahead and pause the video to talk more. Talk about resemblances. My sister looks like someone else. My brothers look like other people. Who do you look like? I look like. You can pause the video if you'd like more time. Note about informal usage of like. Be careful. You may hear some people, especially teenagers, informally insert the word like into their spoken sentences and phrases. This is generally not considered good usage and should be avoided in formal situations. Example. I was like so happy to see my friend. That's not good. Better. I was so happy to see my friend. Here's a free lesson about this topic. Click on this link for more information about like in English. For more practice talking about what you like and dislike, click on the link below. Thanks for watching! Learn more English at eslgold.com


Just a quick little video for you here today to provide a final call out for the three major Prizes that I'll be giving away on this channel one week before Christmas. You see a week from today I have a resource lined up. This is going to be quite a job to extract all my subscribers into a Excel spreadsheet and then we're going to put that Excel spreadsheet into a live draw to announce the winner of a road bike valued at $2,899 Australian dollars. That's the Merida Sculptura 4,000 now I don't have the Merida anymore. I had to hand it back That's because I'm giving away a brand spanking new one to a subscriber So the giant which is my bike is just here for show today But it is perched on a magnificent Westwood road bike stand which is part of the giveaway and I'll get into the details on that very shortly Just wanted to mention that I have a 15% Christmas coupon code for an online Cycling apparel retailer called a Prevello So if anyone looking to purchase some cycling Christmas gifts, whether that be a t-shirt like what I'm wearing now a Cycling kit some caps all these very cool I'm not flexible at the moment aerodynamic socks you can purchase them as well Just know that any purchases you make through a Prevello that is an affiliate code So not only do you get a discount, but a little bit of commission comes back to helping support the channel So the three prizes are number one the Merida Sculptura 4,000 as I mentioned it's valued at $2,899 AUD now if you don't want the Sculptura for whatever reason Merida or Advanced traders that's a distributor for Merida in Australia are happy to provide something from their fleet Within the same price bracket or I'm happy to just give you the cash So you can buy something different now originally I did mention throwing in some wheels as an alternative option too But I've ran out of time to organize so I apologize for that now to be in the draw to win the first prize She's got some notes here. You need to be a subscriber of the channel your profile needs to be set to Public not private I'll put a link below if you want to figure if your public versus private And it needs to be public before we start the extraction process between the 13th 14th and 15th of December That's US time after the 15th of December any new subscribers Will not be in this specific draw, but I'm hoping to do other draws in the future now number two is this Magnificent Westwood road bike stand need to be both a subscriber and also on my weekly email newsletter list to win this prize which also applies for prize number three a set of Sun Guard Pacebreakers which are cycling glasses that you can custom build yourself as mentioned to be in the draw for prizes two and Three I'll take these off now need to jump on my email newsletter list I just want to emphasize I don't spam people over there I do an email once a week just to provide a cycling training tip to help improve road cycling performance now I'll be drawing a subscriber for prize one and I'll be using the emails for prizes two and three now It's hard to marry up the emails to subscribers So I'm just going to go with the honesty policy for prizes two and three if you're on my email News at a list I'm just going to assume you're a subscriber as well now if you don't come forward within the first 30 days of Me announcing the winner I'm going to have to do a redraw of whatever that prize may be and a video will be published One week before Christmas on this channel to announce all winners and if you want further details I did provide another video probably two months ago now on this I'll put a little thumbnail up here somewhere you can click on that and I'll link to it below and I'll catch everyone in the next video


u haven't seen that video I would strongly recommend to watch that video so it would be very helpful to be understand so first of all we need to write unpivot then we have to specify the value so value column should be number of days and for what we want to get this number of days so we have to specify two location so this is our two location then we have to use in operator and then we have to specify the column name the first is Mumbai and second is Bangalore and third is Delhi then Lucknow and then Kolkata so we have to specify unpivot so I am using an alias un now I am going to execute this query once I would execute this query then we can see we are getting location and then number of days then two location and in our slide we can see so from location to location and then the value so this is our value column so we are getting the output now I gonna insert in a temporary table into as temp so 15 rows affected now we need to apply join between invoice table and this unpivot output table so select as tick from this is our invoice table now we need to apply left join because we want to get all the informations from invoice table doesn't matter it would be a label on this temporary table or not so this is our temporary table I am going to put an alias as T and this is our invoice table so I want to put an alias with I and then I dot this is our form location should be equals to T dot this is from location and then I dot this is our two location should be equals to T dot two location so I wanna execute this selected statement once this selected statement would be executed then we can see number of days are coming over here from location to location all the informations are coming but we don't want all the information we want all the information from invoice table and just want to get the number of days values from this temporary table so T dot number of days now we can execute and we can see number of days are coming but we want to get expected delivery date so we need to use date add function then we need to add days then increment values should be number of days then we want to add this date and now we can execute and we can see it's coming expected delivery date expected delivery date now we can execute and we can see our output and we can compare between slide and our output as we have achieved so here we can see our invoice number I1 expected delivery date is 17 so it's same and second 21 and then third 22 and then again 22 so we are getting same output as we have explained in our slide so I hope you have understand how we can get this output by using unpivot operator so if you like this video please subscribe our channel to get many more videos and press bell icon to get notifications for new updates if you need this script it would be available on my facebook page you can copy from there thank you so much for watching hello friends welcome to SS Unitec my name is Susheel Singh and this is continuation of sico server interview questions and answers video series so today we are going to discuss what are the real-time use of row number except delete duplicates so there are a lot of articles or label on internet by which you can see row number is used to delete duplicates but here we have totally different scenario so let's move to another slide so this is your slide as you can see we have input table that contains four columns our first column is member number second column is account number third column is entry date and last column is amount so this table contains the information of account transactions so in our output table again we have four columns first is member number second is account number fourth is entry date and last one is amount so all these columns which is our label in our input table are reflected in our output but we want to get the latest payment information like when we talk about member number one and account number one then we have two rows in our input table and first rows entry date is 29 and second rows entry date is 30 so 30 is the latest payment date so we want to consider in our output when we talk about member number two and account number one then in our input table we can see again we have two rows first rows entry date is 29 and second rows entry date is 31st so 31st is the maximum entry date so this row will move to our output and when we talk about member number two and account number two and member number three and account number three then we have only single row in our input table so these rows will move to in our output table so how we can achieve this so first we can use aggregate function with group by to get the output but this query will not good for the performance as we are going to deal with account transaction table because this is very heavy table and it contains millions of rows so how we can achieve this without using group by and aggregate functions so here we can use row number and row number is also very good for the performance so let's move to SQL server management studio to write the code as I am using SQL 2014 you may have another versions of SQL server but query would be same for all the versions of SQL server so here we need to create the table then we need to execute this creative statement so command completed successfully so table has been created and then we need to insert record on it so we need to execute this query so six rows affected so in our slide as we can see we have six rows so these two has the same metadata so we can select and we can compare if required then the account history and here so how we can write query to get the output so first of all we need to create row number so we are going to use row number and then we need to specify over as this is a syntax for row number and here we want to partition by on the behalf of member number and account number so we have to do the partition so partition by then member number and account number and then we need to apply order by with this entry date so this is our order by with entry date and we want to by decreasing order and we can specify alias name with rw so here we can execute and we can check our output so here we can see we are getting so this is our first row and we can see member number one and account number one we are getting entity 30 and amount 140 and row number is one so in our output we can see 140 is our amount and entity is 30 so we are getting where row number is one so this is our first row and when we talk about our second row when member number two and account number one so this is our member number two and account number one so it's going to return entity is 31st and amount is 480 and we can compare so entity is 31st 7 and 480 is our amount so we are getting same metadata so these two rows are our label in our output output table so this is our output table and we can see we are getting same metadata or we can do the filter by row number so we can use city so this is our with density and as and we can close the parenthesis and here we can select by the city so this is our city and we can apply filter where row number is equals to one then it will reflect the latest payment information so it's going to return four rows which we have seen in our slide so if you are not familiar with cte then we can move this cte with our drive table so this is our drive table and we can move it over here and here we can use a select elastic form and we want to get the data from this drive table so we have to specify an agas name with where row number this would be equals to one and we can execute this query so here we are getting same data as we have specified with cte if you are not familiar with cte then you can use the our second query so I hope you have understand how we can use the row number and it's very good for the performance because here we are not going to apply group by or aggregate when we are dealing with very big table it's very bad for the performance by which we are going to apply group by and aggregate functions so we can apply row number to get the output and it's very good for the performance so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos if you need this script it will be available on my facebook page you can come from there thank you so much for watching hello friends welcome to SS Unitech my name is Susheel Singh and this is continuation of SQL Server interview questions and answers so today we gonna discuss how we can apply conditional update in a single query so if you haven't watched part 25 of this video series so I would strongly recommend to watch that video where you can understand how we can apply row numbers so let's start with our question so this is your question as you can see we have two input tables first table contains the information of employee it has three columns first is employee ID second is employee name and third is employee address as we can see employee address is blank and we want to update this employee address from employee address change table so our second table is employee address change table as we can see it has three columns first is employee ID second is employee address and third is address change date so we want to pick the maximum address change date from each employee and want to update in our employee table as we can see for employee ID one employee name is Harvard we have three rows in our employee address change table first is Washington second is New York third is Chicago and address change date is 12th of December 2013 then 14th of March 2014 16th of April 2016 so as we can see 16th of April 2016 is the maximum address change date between these three so address will be picked for this date and address is Chicago so we want to update this Chicago in our employee address table as we can see in our output table employee ID one employee name Harvard and address is Chicago when we talk about employee ID two employee name is Sandhya and it also has two rows in our employee address change table first is Delhi second is Noida employee address change date for Delhi is 14th January 2015 and for Noida 18th February 2018 so 18th February 2018 is the maximum between these two so address will be picked for this date and address in Noida so Noida will be updated in our employee table so how we can write a update statement to update the address column from employee table with the largest address change of all the employee from employee address change table with a single update statement so let's move to SQL Server Management Studio as I'm using SQL 2014 you may have another version of SQL Server but query would be same for all the versions so now I'm going to create the table so first employee table has been created successfully then I want to create employee address change table so command completed successfully so table has been created successfully and now I want to insert record on it so four row factor so data has been inserted in employee table and then I want to insert record on employee address change table so seven row factor and now we can select and check the record first from employee table so we are getting the data and employee address is blank for initial and we need to update this from employee address change table this is our employee address change table so as we can see we have same metadata as we can say in our slide so now I want to copy and go to Excel to understand how we can write the code so if we create one more column with the name of row number or anything else and then we can do the partition on this employee ID so this is our employee ID and we can do the partition and then if we generate a serial number by which if the maximum entry date then we need to one then two and then three in decreasing order then we can update our employee table very easily so for employee two as we can see this is our employee two and this is about the maximum so should be one and then it should be and for employee three as we can see so this is our maximum so it should be one and then two and if we can apply filter when row number is one then we can compare so now we can see we are getting only three rows and these three rows should be updated in our employee table if we can compare in our slide so this is our slide as we can see Chicago, Noida so Chicago, Noida and Manchester so all these entries are available with a single employee ID so now we can able to update our employee table so how we can write the code to generate this row number so go to SQL Server Management Studio and here first of all we need to generate a row number so I want to use row number so this is our row number and then over and we want to do partition on the employee ID so partition by this is our partition by employee ID and then we want to do order by this employee address change column in decreasing order and we can put an alias with a row number and now I am going to execute so now we can compare this is about the maximum address change rate for employee one so we are getting row ID one and this is our maximum for employee ID two so we are getting one and this is our maximum for ID three so we are getting row number one so now we can put this query in a derived table so this is our derived table and we want to update this employee table so we need to write update statement so first of all we need to write update then set and then from this employee as we can put an alias name with the a and we want to update this a table and what column we want to update we want to update address column from which we want to update from b table and then we can see and we need to put an alias name now we are able to deploy address so where a dot employee ID should be equals to b dot employee ID so and now we need to apply only those rows where rw value should be one so it should be one and now we can execute this update statement so three rows affected and now we can select our employee table so this is our employee table as we can see we are getting sikago noida and Manchester go to our slide this is our slide and we are getting the same metadata as we can see over here by using this update statement so this is our single update statement by which we can update our employee table so i hope you have understood how we can update on conditional basis if you like this video please subscribe our channel to get many more videos if you need this script it will be available on my facebook page you can copy from there thank you so much for watching hello friends welcome to ss unit x so see this side and this is continuation of sikgo server interview questions and answers so today we're gonna discuss one more question so here we want to aggregate based on multiple tables so what it means let's move to another slide where we will understand about the question so here as you can see we have two input tables first is related to student fees and second is related to student charges in our student fees we can see we have only two columns and in charges again we have only two columns it's possible that any student can contain only fees and contains only in charges and it may contains both so in our output we want to display all the records like we can see in our fee table we don't have student id3 but in our output we are getting three in our charges table we don't have id4 but in our output we are getting four if the charges is not available for that student then that column value will contain zero in our output and if the fee is not available in our input table for that student then that would be zero in our output so go to on ssms to write the query so here i have already typed the query to create the table and insert record on it so i gonna execute these queries to create the table so tables have been created successfully now i gonna insert record on it so we can execute our insert statement as we can see records have been inserted successfully on these two tables now we can execute our selected statement to check the records so as we can see we are getting the same number of records as we have displayed in our slide so now i gonna copy these records and go to on excel to understand the logic so this is for fee and second we need to copy for charges as well so we can copy we can paste in excel so as we can see here we have fee and here we have charges and in our output we want student id then fee then charges so we want these three columns in our output so we can copy this and paste in our output and in our charges we are getting zero and when we talk about charges then we can copy our charges and we can paste it here and this is for charges so we can cut these values and paste in our charges and this time fee amount would be zero now if we can apply aggregate with this output then we will get our actual output as we can see student id one then we will talk about the charges then we have these two values in our charges so our output fee would be 300 when we look in our charges then we can see we have all these three values so output would be 500 so in our charges we will see 500 next our student id two then we can see output would be 400 for fee so we can write 400 and then when we look in charges then we can see we are getting 600 so charges would be 600 then we look in id three so here we can see in our id three once we talk about fee then fee values are zero so fee would be zero then we need to look in charges so here we can see 850 so this is our logic by which we can get our output so go to on ssms to write the query by which we can get the output so first I want to select a stick from student fee table so here we can see we are getting student id fee and zero value for the charges so this is our charges now we can execute this selected statement then we are getting the output now I want a union with the charges table as well so this is for union all so here we can execute then we can see we are getting student id and charges so we need to write the column names so this is for student id then we have our second column with the fee so fee value is zero for the charges table and then we need to write the charges so this is our charges now I want to execute this selected statement so as we can see we are getting the intermediate output as we have seen in our excel now we can use our derived table so this is our derived table and I can put an alias with a and here I want to select student id then we have fee so we can write fee then we have charges so we can write charges but here we want to use aggregate function with some as fee then here we can write some for the charges so this is our charges and we are getting these values from this derived table as we have used aggregate function so we need to specify our group by with student id now I want to execute this query so as we can see we are getting one two three and four all the student IDs so by using this query simply we will get our output so I hope you have understand how we can write the code to get the output if you need this script it will be available on my facebook page you can copy from there if you like this video please subscribe our channel to get many more videos don't forget to press the bell icon to get the notification of our newly uploaded videos thank you so much hello welcome to ss unity so see on this side and this is continuation of sico server interview questions and answers so today we gonna discuss about how we can get the comma separated values and this is very important interview question generally interviewer ask this question to us in real time you may have faced this scenario many times so let's start with our question so as you can see we have input table that contains six columns first is id then the training name classroom start time duration and weekdays and in our output we want five columns first is training name then classroom start time duration and weekday in our weekday as we can see we are getting the comma separated values so as we can see in our sequence over so we have total four rows so our first four rows if we have monday tuesday wednesday and friday so we are getting four days in our output these days are coming in a single column with comma separated values and for msba as we can see our last three rows then the all the values are same except id and weekdays but id we don't want in our output we just want only weekday in our output so we will get monday thursday and friday in our output by comma separated values so let's move to sequo server management studio where we will write the query to get the output so as i am using sequo 2014 you may have another versions of sequo server but query would be same for all the versions so this is our query for creating the table so i gonna execute this query to create the table command completed successfully so table has been created now i gonna insert record on it so we can insert records so as we can see seven rows affected so records have been inserted successfully then we need to check the record so as we can see we are getting seven rows as we have seen in our slide so before going forward let's discuss about stuff and for example path basically stuff is used for the masking as we can see i wanna create a scenario by which we will use stuff so this is our stuff then here we need to specify the expression by which we want to apply masking so i wanna use abc at the rate abc then we need to specify the starting position so i wanna start from one then how many characters we want two characters so ab will be masked by here we need to specify the expression value by which we want to apply mask so i just wanna use star i wanna execute or select a statement then we can see ab has been removed and we got the value as star so this is for the stuff next i wanna discuss about for XML path so what for XML path does so let's select this table so select star from our comma separated table and we want to apply comma separated values for weekday then we want to add comma here we need to specify for XML path so i wanna use for XML then path here we need to specify bracket then i wanna execute our selected statement for for XML path so here as we can see we are getting the value in XML so we are getting all the values by comma separated but here we can see we are getting one extra comma so by using stuff function we will replace this comma here i wanna put in a drive table so this is our drive table and i wanna put an alias with a then select i wanna use stuff then we need to start the bracket so this is the expression where we want to replace our first comma by blank so we need to specify comma then starting position should be one then again comma then how many characters we want so we want to replace only one character then here we need to specify the replaced value so that is blank now i wanna execute this selected statement so as we can see we are getting the comma separated values as we were expected so this is our for XML path now i wanna write the query to get doubt so select star form table name comma separated value so here as we can see we are getting all the columns but in our output we don't want all the column we just want training then classroom then start time then duration and then we want comma separated value for with this so here i wanna use this for XML path so i can copy and paste that value over here so here we need to specify our bracket so this is our subquery so this time once we will execute this then we can see we are getting all the values by comma split it but we just want to comma separated values where the training for our inner query would be equals to our outer query here we can use alias with a for our outer query i wanna use alias with b and here we can apply the condition where a dot training should be equals to b dot training now i gonna execute our selected statement so as we can see we are getting the comma separated values but the data is going to be repeated so we don't want to repeat a data so we can use distinct now i wanna execute our selected statement which we have created so here we are getting the values so MSBI as equals then classroom start time duration then in our last column we are getting the week days by comma separated values so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos if you need this script it will be all about or my facebook page you can copy from there thank you so much for watching hello friends welcome to ss unit app so see on this side and this is continuation of sequence over interview questions and answers so today we gonna discuss a very important interview question that is related to dead time calculations so let's start with our question so here we have total three questions this is our first question as we can see we have an input table that contains four columns first is id name date of birth and date of joining and in our output we want all the columns but we want to get those employees those contain the date of joining and day of date of birth are same for id 3 date of joining is 2015 08 and 20 and date of birth 1991 06 and 20 so 20 is same for date of birth and date of joining so we want to get in our output as same in case of id 4 and 5 so we want to get these employees in our output so go to our sequence of management studio to write the query for getting this output so this is our query to create the table so i gonna create the table so we can execute our creative statement so as we can see commands completed successfully so table has been created successfully now i gonna insert record on it so we can execute our insert statement so five rows affected so data have been inserted successfully in our table now we can check the data so select star from our table so we can execute our selected statement so as we can see we are getting the data question is we want both employees which day of date of birth and day of date of joining are same we can calculate day for date of birth should be equals to day of date of joining now we can execute our selected statement so as we can see we are getting three employees for id 3 4 and 5 go to our slide as we can check we are getting the same metadata as we are getting in our sequence over so this is our question one let's move to question two so here we want to get those employees which day of date of joining and day of date of birth are same and month of date of joining and month of date of birth are same for id 4 as we can see in our date of birth we have april and in our date of joining we have april so months are same and once we move to our date then we can see 10 in our date of birth and 10 in our date of joining so condition are going to match so this record will be moved to our output let's move to id 5 as we can see month of date of birth is told and month of date of joining is told so our first condition is going to satisfy now move to our second condition day of date of birth and day of date of joining so this condition is also going to be satisfied so this record will move to our output so go to our sequence of management studio to write the query so here we need to write select star from our table and this time we want to get days of date of birth should be equals to day of date of joining this is our condition one and in our condition two months of date of birth should be equals to month of date of joining now we can execute our selected statement which we have created right now so as we can see we are getting id 4 and 5 in our slide as you can see we are getting id 4 and 5 so this is query by which we can get our output let's move to our last question so in this question we want to get minimum age employees detail which has completed three years in the organization as we can see let's assume our current date is 2018 04 and 21 so this is our current date once we compare between current date to our date of joining then we will get id 3 4 and 5 once we compare between all these three IDs then we can find id 3 has the minimum date of birth so this will move to our output so how we can get this output by writing the query so go to our sequence of management studio here we need to write the query so I want to use select a stick from our table so here we want those employees which have been completed three years in the organization so we need to use date the formula and here we need to specify the interval so our interval in years so we can specify y and y then we need to specify starting date that is our date of joining then end date so as we can specify 2018 04 and 21 and here we need to specify if this value is greater or equals to 3 so we want those employees detail so we are getting three employees with id 3 4 and 5 so these three employees have been completed three years in the organization and here we want to generate custom by using row number for the minimum as employee so we need to specify over then we need to specify order by and date of birth in decreasing order and we can specify an alias name with rw now we can execute our selective statement so as we can see we are getting a row number with one two and three one has the minimum is two is the second highest and three is the maximum so we want to get only those employee which rw is one so we can use ct with ct temp as so this is a syntax for ct and here we want to select only those employees where rw value are equals to one now we can execute our selected statement as we can see we are getting id 3 so this is our minimum as employee which has been completed three years in the organization so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos don't forget to press the bell icon to get the notification of our newly uploaded videos thank you so much hello welcome to ssunitak so see on this side and this is continuation of sickle server interview questions and answers so today we gonna discuss about a very tricky question where we want to apply filters by status so let's move to another slide where we will understand about the question so here as we can see we have input table that contains two columns first is customer id second is product id and we want to extract those records where customer contains product id a and b and not contains c so as we can see for id 1 we are having product id a b d and e it have a and b but it doesn't have c so we want in our output let's move to id 2 so for customer id 2 we can see we are having a b c so we are having our first condition a and b but our second condition is not going to satisfied because it has c so we don't want in our output let's move to id 3 so in case of id 3 we are having a b x y so it has a and b but it doesn't have c so we want customer id 3 in our output so how we can achieve this output let's move to sickle server management studio where we will write the query to get this output so as i am using sickle 2014 you may have another versions of sickle server but query would be same for all the versions so here we need to execute our first query to create the table so i gonna execute our creative statement for creating the table of customer product table so command completed successfully so table has been created successfully now we need to insert the record on it so once we execute our insert statement then we can see 11 rows affected so data have been inserted successfully in our table now we can select data from the table and we can check so select star from our table then we can execute our select statement now we can see we are having same metadata as we have displayed in our slide so what approach we will follow so let's comping this record so go to on excel to understand about the logic so here i wanna use self going so we can paste one time then second time and then our third time so this is for a and this is for b so once we apply energy and between our a table and b table and in our a table we will check record for the a and in our b table we will check record for the b if a is our label in our table a and b is our label in our table b then we want both records so in our output we will get customer id then the product id so here we can see we are having one with a in our a table and in our b table we are having one with b so in our output we will get one once we move to id2 then we can see we are having a and we are having b so we will get in our output once we move to our c then we are having a in our a table and b in our b table so three will also move in our output in our third table which is our c table and it will pick a record for the c and then we can apply left join once we apply left join if the customer id is null in our c table then we want those customer id's so customer id1 doesn't have product idc so we want in our output so our customer id1 will be reflected from here once we move to customer id2 then we can see it has c so we don't want in our output once we move to third then it also doesn't have c so we want in our output so in our output we will get one and three and we can apply sub query from our table a where customer id is one and three so we want those records so this is the approach we will follow so go to our sequo so management studio here we can write the query to get the output so as we have discussed this is our table a and then we can apply inner join so this is our inner join with the same table and this time this is for b and here we want to condition if the a dot customer id is equals to b dot customer id and a dot product id should be equals to a and b dot product id should be equals to b so we want those records once we execute then we can see we are getting one two three now we need to apply left join so this is our left join with the same table and this time this is for c and we can apply a dot customer id should be equals to c dot customer id and we want c dot product id should be equals to c now we can execute our selected statement so here we are getting customer id null two and null for table c so once we apply filter where c dot customer id is null then we will get our output as we were expected now here we can select our customer id we can execute our selected statement so we are getting id one and three now we can select the data from our customer product table and where customer id in this selected statement now we can execute and we can check so we are getting id one and three so we are getting this output by using subquery now we want this output without using our subquery so we can copy our selected statement which we have written and I want to put this selected statement in a derived table and put an alias with b and here I want to use select a stick from our table which is our customer product table and we can put an alias with a and now we can use inner join with our table b on a dot customer id should be equals to b dot customer id and here we want to get records from table a only now we can execute our selected statement so we are getting same metadata as we were getting by using subquery and here we are getting this output by using joins so this is from join according to interval question we can write these queries to get the output so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos don't forget to press the bell icon to get the notification of our newly uploaded videos thank you so much hello welcome to ss unity so she'll decide and this is continuation of sequo server interview questions and answers so today we're gonna discuss about one more interview question that is related to how we can deal with logical combination of words so before going forward if you haven't watched part 29 of this video series so I would strongly recommend to watch that video where you can understand how we can get the comma separated values so let's start with our question so as we can see we have input table that contains two columns first is id second is value and in output we have two columns first is id as second is value so as we can see in our input first three rows contains gagan gagan and gagan next three rows contains deep deep deep then we are having sing sing sing so we want to rotate these values in a single column by gagan deep sing then gagan deep sing and then again gagan deep sing so how we can get the output from this input so let's move to sequo server management studio to write the query for getting this output so as I am using sequel 2017 you may have another version of sequo server but query would be same for all the versions so first of all I would like to execute our first statement that is for creating the table so table has been created successfully next I just want to insert record on it so as we can see nine rows affected so data have been inserted successfully on this table now we can execute our selected statement to check the data so we are having the data as we were seen in our slide now we can copy this data and what approach we will follow for that go to on excel and here I want to paste our input table and here as we can see we are having id and value if we will generate a new column with row number and we will do the partition on the value and we want to generate one two and three then again one two and three and then one two and three as we seen we are getting the comma separated values in our last to last video so here we can get one then gagan then comma then deep then again comma then same and for two and three we will get the same data so this time comma will be replaced by blank so by using this approach we will get our output so go to on sequo server management studio to write the query for getting this output so here I want to generate a new column with row number and then over as we have discussed we need to use partition by on the value then order by id now I can execute and we are getting the data now we need to insert these records in a template table so first of all I would like to create a template table so create table tmp and here we want id which is our integer value then we have value that is our string value so where car hundred and then for our last this is our row number will integer and we want to create a primary key on the id then I need to execute this query to create the template table so template table has been created successfully now I need to insert a record on it so insert into our template table and we are getting data from this selected statement so as we can see nine rows affected now we can copy and I want to use select as tick from our template table so here we need to row number we need to use space separated value so I want to use stuff then select then we can specify space and we want to add with value from our template table and then we can put an alias with I where I dot rw should be equals to O dot rw and here we can specify for the XML path here we want to replace extra space which we are getting at the starting of this subquery so we want to start from one then how many characters we want to replace we want to replace only one character from which we want to replace we want to replace by blank now I can close the bracket now I can put an alias with s now we can execute our selected statement so we are getting duplicate values so for that we can use distinct and now we can execute so we are getting the values as we were expected so as we can see in our slide we are getting same output so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos don't forget to press the bell icon to get the notification of our newly uploaded videos thank you so much


What's the word, y'all? Today, we're trying to figure out what team is going to win the 2022-2023 NBA Championship. Because right now, I can't call it. It's funny, though. All season long, from the offseason, I've been sending the Bucks and my pick to win the championship. And no matter the ups, the downs, the injuries to Chris Middleton, this, this, this, no matter what, I didn't waver. I thought that the Bucks were the best team in basketball. And a few weeks before the playoffs started, we dropped the video and we were trying to determine what a real contender was. And we looked at the data over the last 20 to 30 years in the NBA season and saw that if you want to win the championship, you have to be top 10 in the office, top 10 in the defense. You have to have a player of this caliber. You have to win X amount of games in the regular season. You have to be this seed. It was a lot of stipulations in this formula. And the Bucks then meet the formula to say that they're going to win the championship. But I was like, wow, it ain't no big deal. I still believe in the Bucks. I should have looked at the formula and paid as a steward. Because this is a lot different than if the Bucks would have got to the finals and lost in 6-7. The boys got ran out of the gym by the 8-seed, you know? So maybe we should be looking at that.  I'm not an eye test guy or analytics guy. I think I still write in the middle. I think that they both have their own place. But the data told us that the Bucks weren't a contender. They didn't end up being it. Again, of course, injuries mattered a lot. Giannis practically missed three games out of five. Still, they lost. That's all, whatever. So now I'm trying to figure out all the teams remaining. What team am I confident in being the champions? The short answer is I'm not confident in anybody. And I think that's what makes a good league. Before we go any deeper, I want to let you know this hat that we've been wearing in every video almost for the last, I don't know, year or so, is back on sale. It is a very small batch. I'm be honest with you, we ain't print 5,000 of these. 1,000, not even 1,000, not even 500 of these. You know what I'm saying? This is a very, very limited drop. So if you're interested in getting your very own enjoy hat, it is live as of right now. Once I press upload to this video, it is live. So get in while you can. I've seen three of them in a while in my lifetime, which is crazy. This is me walking around and be like, I made that hat. You can join in too and get in because I don't know how many other drops you're gonna be doing. So don't miss out, link is in the description. So I'm saying that this makes for a good league because I, or I guess majority of us probably have gone through so many different eras of basketball that was, we know what team's gonna win. East is usually a LeBron lead team. And we have a general idea of what team is gonna win the West and that would have been, at least in recent history, the Golden State Warriors. And not saying that all the other series weren't fun or it didn't matter, but again, you knew that LeBron, and it wasn't a team out East. No matter how much we try to convince ourselves that the Raptors are gonna do it, or no matter how much we convince ourselves that the Bulls would do it in the early Heedle days, we felt pretty damn confident that LeBron was coming out of Eastern Conference and the Warriors are doing a thing, oh, and then they added Kevin. So it was like, as long as these two teams stay healthy, we know who's gonna be in the finals and we played 82 games, plus all of these postings of games for the end result to be which of the two teams are better. And I can't say I hated that either. You know what I'm saying? I think that it led to moments where when those juggernauts were on the ropes, it was as fun as can be. When Ricky Tatum went to the Conference Finals and dunked on Bronn, I was, I didn't know the word. Jubilant? Maybe, I don't know if I'm using that right. When the Houston Rockets went up 3-1 on the Warriors, I'm like, yeah, the juggernaut is falling. So I enjoyed that version of ball too. But this version, where like this is, I would say these last two years at least, have been the most amount of parody I have experienced as a Die Hard NBA fan. And I saw a tweet the other day that had the opposite opinion. That was like, hey, I liked it better when it was one team that we saw as dominant and we wanted to see that dominance and get knocked off. So I could see it both ways. But the parody for me makes the NBA playoffs way more enjoyed, at least it has been this year. It reminds me of, Kenny Baseball is a channel on this YouTube platform that I've uploaded on twice as the MLB season has started. But it reminds me of Baseball, where we've seen plenty of times to our history where a wildcard team that had to win to get in gets ultra hot and find themselves in the World Series. And it happened last year with the Phillies, right? A wildcard team got hot, made their way to the finals, they went, but they made their way to the finals and those type of runs are just as fun as any. We haven't had that version of parodies just yet. Because Game of Basketball is so talent oriented. There is a form of luck, but it's a lot different than like a baseball. Not luck, but like, he's on fire. It's not like NBA Jam, where in Baseball, you can get a hitter that's like, for this week, he's killing everything. And I guess you don't really have that same level of anything in the NBA. Maybe this is the year that we see a wildcard team, in this case a playing team, making a deep run with the Miami Heat doing everything or the Lakers being up 3-2 on the Memphis Grizzlies. But it makes for fun because we will have situations like this year where the 1C goes down early and now we just looking around like, whew, what team do we trust most the next? Next the most, whatever. Because I'm looking at the teams that have advance and of course each one of these teams has done something to impress, but does it feel like these teams have done enough to impress for me to say, definitely, this is the team I think is gonna win the NBA Championship and that's hard for me to do. Oh, I do want to say this though. I should have said this at the beginning of the playoffs because I know there are new people coming in and out. Just because I predict a team to win a series doesn't mean I'm rooting for that outcome. Being right, I don't know if you've been around for some time, you know that being right is not necessarily super important to me, right? The perfect example is that I picked the Cavaliers to beat the Knicks in 7. I thought that the Cavaliers would be a better team and seriously, obviously I was completely wrong. The Knicks ran over the Cavaliers in 5, but I did see some people that thought that me picking the Cavs was saying that, oh yeah, Cavs gang, what is they mantra of it? I don't know, I don't care. You know, if the Bulls are not involved, 97% of the time I do not care about the outcome. I just won the basketball and I would argue this series gave us the basketball. From the Knicks for sure, not from the other team. So I don't make my predictions in the idea that I have to be right or that I necessarily want to be right because it's not super important to me. I just try to give well thought out insightful opinions and sometimes they come true and sometimes they don't and I think that's okay. But if we do want to get into the numbers without my right versus wrong when predicting the series. Shout out to my guy over at TTW Clips. He watched every single prediction video from 2018 to 2022 to say that I shoot 79.5% from the field when predicting the series. So my field goal percentage has gone down because of this playoff run, but again, I don't care. So what team's going with the championship? Again, I do not know. I'm looking at our candidates right now. We have six teams advanced already. We still have to figure out what's going on between the Warriors and the Kings. With the Warriors being up 3-2 and having the next game at Chase, I don't know what's going to happen, but the Warriors are very, very good at home. And it felt like they found a new recipe for success. And with the Aaron Foxx injury and some bonus, not playing like the all NBA caliber version of himself, if I was going to make a prediction how the series ends, it's going to be with the Warriors winning. The Lakers are up 3-2 against the Grizzlies. We just saw Jha and Desmond Bain have their first game where they both had a really good game. So that's a plus, but it is a 3-2 series. I'm not going to Google it, but I feel like LeBron James' late team is never blowing a 3-1 lead. I could be mistaken here, but it feels like that's not the case. Even though 20 year into the game, LeBron is not the same. And you're kind of seeing that over the last couple of games, something that makes me not want to pick them to win the championship either. Those series aren't even wrapped up yet. So I don't even want to really touch on those teams because anything can happen. Like a 3-1 comeback is not, again, nothing that's too out of the ordinary. Well, we know the Nuggets have advanced and we know the Suns have advanced. Even trying to predict that series is hard for me. So I can't definitely say that one team is the championship team because I don't even know who I think is going to win this series. I think that both of these teams have their pros and their cons. For example, the Phoenix Suns, we're going to see a lot of pick and roll. I mean, they're a pick and roll heavy team to start off with, but now that we got Nicola Jokic as the defensive guy there in a lot of these cases, I think that they can abuse that. But in a superior numbers game, a mathematic equation, the Nuggets are going to get way more 3-point shots than the Suns in the series. And they just got to make more. You know what I'm saying?  But they just, they got the 3-point shot and they got a few people off the bench that are really good. But then the Suns got the star power. Devin Booker has been the second best player in the playoffs so far behind Jimmy Butler. So again, I can't call this series. So since I can't call that individual series, I can't call one of those two teams as my definite championship team. You see what I'm trying to say? Nothing right now feels great. And I think that's what makes it good. I think a lot of people look at the 2-3 matchup between the Celtics and the 76ers and say, hey, whoever wins that series is probably going to go on to win our Eastern Conference and that team is going to represent the East out in the finals and boom, that team might be the NBA champion. But I think both of these teams have done enough to be like, yeah, okay, cool. If you look at my predictions beforehand, I saw the Bucks as the favorite, but I also added the 76ers and the Celtics as contenders because both teams have showed us throughout the course of 82 game season that they have what it takes to win a championship, at least come close to it, when the Eastern Conference. But neither of these two teams have me super excited about this series as far as like, oh yeah, I think that the Celtics go win in this one. I think the 76ers go win in this one. I'll give you the reasons why. The Celtics did not have a good series against the Atlanta Hawks. They won, obviously, they were the better team and they had the moments down the stretch that mattered more than the Atlanta Hawks and that's how you win basketball games. But I didn't see enough in this and say, oh, that's the dominant force that's going to win the Eastern Conference. Same thing with the 76ers. Now 76ers is for a swipe that is the best you can do, right, so Kenny, what kind of gripe do you have about that? Jordan and Beds knee, he won't be won a brace. They say he's close to 50, 50. It's like, man, if they were completely healthy, maybe I'll feel more confidence in them, but he's not going to be completely healthy. And these two fan base between the Knicks and the Heat on Cloud9 as they should be. They were the, both of those teams were the underdog. If you look at every prediction for the Cavs, Knicks, majority of people pick the Cavs. Go to ESPN, go to BleacherPoor, all these experts, not all of them, majority of them pick the Cavaliers to win a series and the Knicks to carry them in five. Everybody, everybody picked the Milwaukee Bucks and the Heat to carry them five. So both of these fan bases on Cloud9 and they deserve to do that, but I feel as though I can't say that these teams are the championship winner either. Did I say anything in this video? Probably not. All I'm saying is basketball is great and parody is great and that's all I care about.