 What is going on everybody? My name is Alex Freeberg and today we are going to be walking through some leak code interview questions. Now if you don't know what leak code is, leak code is a website where you can go and practice interview questions, mostly technical coding interview questions. The website is geared towards software engineers, but a lot of other jobs have technical interview questions. And so it is a great place to go on and practice your interview questions so that you can make sure that you pass that part of the interview. Now, before we jump onto my screen, we start working through these problems together. I just got to say, look, I had a tough week. And so I know you're looking at me and you're like, why are you wearing that shirt? It doesn't look good on you. I don't care. All right, just bear with me. I hope you can get past the fact that this is a different shirt that I normally wear. And I hope that, you know, we can destroy some of these leak code questions to really boost my self-esteem for this week. So without further ado, let's jump on my screen. And I'm going to show you what leak code looks like very quickly. And then we are going to jump right into these questions. So when you first log on, this is what the homepage looks like. And there are options like explore problems, mock, contest and discuss. Some of these are things like a mock interview. So you can go into this one and you can actually take a mock interview. Obviously the ones that you can take are free right off the bat. And they're just completely random questions. And, you know, it is what it is, but you can also pay for it. And you can do things like the Google interview or Facebook or Amazon interviews. And so, you know, this might be useful to you. This is a good place to say, I am not sponsored in any way by this. I just think leak code is a pretty cool website that you can check out. I never paid for this. I always use the free version. I just took the easy medium. And then I attempted the hard ones back in the day. But, you know, it's up to you if you want to pay for it or not. Where we're going to be going today is this problem section. Like I said in the beginning, this is mostly for software engineers. And so there are things like algorithms, shell concurrency. You will most likely, unless you are wanting to learn those things or study those things, you are most likely not going to be doing that. What you will be doing is going into this database section. And so when you click on that, it will bring you to database. And this is primarily SQL. And when you go into each of these, you can use Microsoft SQL syntax. You can use MySQL or Oracle syntax. For all of them, I am going to use MySQL syntax or I'm going to use Microsoft SQL syntax just because that's what I'm super familiar with. I use it all day every day. But you can use whichever one feels most familiar to you. So I've already pulled up five questions up here. And when I asked this in the community chat, somebody was like, Hey, Alex, you know, when you're doing these problems, walk us through what you're thinking so that we can understand kind of how you think about these problems before you do them. I will definitely do that. So before we get into our first question, I will say that we are only doing the easy difficulty today. In future videos, I believe doing the intermediate, I think it's intermediate or medium, and then the hard. So the really difficult ones. But for this video, the very first video, I am only doing the easy one. So if they look really easy to you, that's fantastic. If they look difficult, you know, that's also really good because you're here, you're learning. And so we can look at them together and I'll walk through my process with you. With that being said, let's jump into our first question. So the first problem we have is swap salary. Let's read through the problem. And then let's try to figure this out. So the very first one says given a table salary, such as the one below that has m equals male and f equals female, swap all f and m values, i.e. change all f values to m and vice versa with a single update statement. And no intermediate temp table. Okay, so we can't use a temp table. And we're using an update statement, which means this isn't like a select statement, I'm guessing. Note that you must write a single update statement, do not write any select statement for this problem. So we are actually, you know, imagining this data is in a table. And then we're actually updating it to change the actual values to these new values. So it says we're swapping the values. So where it says female, it's going to be male. And that's exactly what it says down here. So after we do it, where a is m, now a is f. So you can't use a temp table. Yeah, so it looks like it's just going to be an update statement. I'm guessing. I'm guessing this is going to be really easy, which is just going to update it. So use a case statement where it's m updated to f words f updated to m. That's where my mind is at first, because it's, you know, it's pretty simple. It's pretty simple. And that I don't think I could do it any other way. In a in an update statement without using a case statement. So I'm going to start writing that out. And then we'll see where that takes us and see if that's the right answer. Full disclosure, each of these problems has a solution and discussing discussion section. I have not looked at any of these solutions or any of the discussions, because I want to try to figure this out with you. But afterwards, we will check those just to make sure that I was right or see if there was any other options that I could have done that I didn't think of. All right, so let's start writing this out. I believe the table was salary. So we're going to say update salary. And we're going to set the sex is going to be equal to and like I said before, I think we got to use a case statement for this one. I'm going to do parentheses case. Actually, let's go down. And this may not be proper syntax. I'm just trying to make more visual for you guys. So case when sex is equal to f, then we're going to make it female or male. And then I don't want to do another one statement because my other one statement would be when sex equals female make it when it's male make it female. Since there's only two options, I'm just going to set I'm just going to do else and then do so if the sex is female, we're going to change it to male. If it's anything else, which means the only other option is m, we're going to change it to female. And then we'll do and this should work. But let's run it and we can test it. That's what this does the run code it tests your code and makes it makes sure it's correct. So let's see. So right here my output is values one a equals f. So that's correct to be is m. So I'm comparing it to here right now, because this is the after statement f and m. So I believe this is correct. Let's try submitting it. And it should be correct. But again, this is my first time submitting it. And that was a success. So that was that was correct. I will say that I was using t SQL syntax. If you're using my SQL, which I personally wouldn't be using Oracle Oracle is not my favorite. And you guys know that. If you are using my SQL, your syntax is going to look different. And I'm not going to do both of those syntaxes. But maybe on the intermediate or the difficult or the hard whatever those are, I might use my SQL just so you can see the syntax for those as well. But just know if you're trying to use t SQL, this is what it's going to look like. Let's look really quick at the solution. And I'm gonna see if that's kind of what they had. So yeah, so they had an update in case when and this is my SQL syntax. So it's a little bit different than what I wrote. But it does the exact same thing. So I basically did the exact same thing, but in t SQL. So you're right here, you can see the my SQL syntax that I was just talking about. With that being said, let's go on to the next question. The next question is not boring movies. Let's read through this one together. It says x city opened a new cinema. Many people would like to go to the cinema. The cinema also gives out poster indications with movie rings and descriptions. Please write a SQL query to output movies with an odd numbered ID. Okay. Any description that is not boring, order the results by rating. So the first thing that stands out to me is this odd numbered ID. I've done stuff like this before. And so I know that that might not be something that most people know how to do. If you're just learning the basics. So I'll show you how to do that. The second part is just not boring. And that's really easy. We're just going to do where description does not equal boring. So the first part that we want to do is we want to basically select everything. We want to do ID movie description and rating. And so we're going to select everything. And we're going to do that from cinema. And what we're going to do is say where the ID, and there's this thing called, well, it's a percent sign. And so we're gonna say where it basically is like where it's divisible by two. So if the answer is, if we're looking at four, and it is divisible by two, we don't want that one. We only want, I mean, make sure we only want odd numbered ID, and a description is not boring. So we don't want the ID was four, two, six, eight, 1012. So we only want one, three and five. So if it's divisible by two, and equals zero, I believe, then we don't want it. Well, actually, we want to write where it's not equal. So we would do that. And then we'd say, and description does not equal, and I'll just do it in a different way, does not equal boring. So let's run this really quick. I think it's as easy as that. But we'll see. So that is the wrong answer. Let's see what I did wrong here. So my output is one and five. And there's is five and one. Okay, so let me see what I did wrong. Okay, so I need to order the results by rating. I completely did not read that in the problem. But I'm seeing that in the output, they have it ordered by the rating needs to be first, the highest rating is first. So that's my fault. So all I need to do is say order by and then rating, rating, descending. And descending means highest first, ascending means lowest first. Let's run this again. And now it should work. But let's see. So yeah, it works. Let's submit it. And it should work as well, I'm guessing. And then we will go over to the solution. Yeah, so it works. Let's go over to the solution and see what they did. Okay, so they're using a mod function, not something that I've used a lot. But it's very similar to what it's, it's basically the exact same thing just in a different way than what we did. Yes, they took mod, they looked at the ID, they use two as the number that they're according, they're dividing by, which is literally the same thing as this over here. Or it's not equal to boring order by rating descending. Yeah, that order by rating descending got me at the end, because I did not read it thoroughly. Always read your problems thoroughly, let this be a lesson to you. I thought I had it right. I didn't read the problem the whole way. And I messed up and I, you know, if I'd submitted that as my actual answer, I would have gotten it wrong. So there you go, let's move on to our next problem. The next question is employees earning more than their managers. And that is always trouble. Let's read through this problem really quick. It says the employee table holds all employees, including their managers, every employee has an ID and there is also a column for the manager ID. Okay, so we're looking at ID and some of these people I see. So some of these people are the managers so like Sam and Max, they have their own salaries and they're the bosses of Joe and Henry, giving the employee table right a SQL query that finds out employees who earn more than their managers. For the above example, Joe was the only one. And Joe earned 70 and Sam earned 60. So that's what we're trying to find. We're trying to find this one guy. How do we go about this? There's a few ways that we can do this. My first option is I think a sub query, you know, where we we say like the salary from this table is greater than the salary and then within the sub query rate, another SQL script. So in that's kind of like almost like a self join almost. So we could also do a self join. We could do like a temp table. We have a few different options. The way that another way is just tying it to itself. So doing it maybe I even already just said that doing a self join. That's the one I think I'm that makes the most sense to me. Again, I just want to keep it the easiest. And so since it's all contained in one table, I think a self join would be easy and then just say where the salary from this guy is greater than the other guy. So let's start writing this out because I need to I need to kind of see it before I fully can make sure it's correct. Okay, we're selecting the name as employee because our employee right here is our output. And that's going to be from the employee table. And we're going to be joining it to itself. So let's do a let's also do a here. So we'll do a and then we'll join on employee. And we'll say B. And we're going to do that on a dot manager ID. I'm fairly certain is equal to B dot ID. And the reason I did that is because we're trying to match this manager ID back to its own ID so we can look at its salary. So I have to say in the first table, which is our left table, we're looking at manager ID and then we're saying, okay, we have a three and a four. And we need to tie it back to the original ID, which is here to look at its salary, I believe, we're going to see if I'm right. And then we'll say where the first salary is in the first table. Let's do it. I'm going to I'm going to test this out. And we'll see if this works is better bigger than B dot salary. So for the first table, you know, I believe, I believe we'll be looking at these for these for everybody. And then the second one will only be looking at words three and four. Let's run this and see what our output is. And that'll give us a hint whether I'm on the right track. Okay. So I got it correct. I got to be honest, though, I wasn't 100% sure in that. And so if I got it wrong, I would have just made whatever my output was, if I made a small error, I would have just would have corrected it. So let's submit that, just make sure that's correct. And all right, so success. Now let's go look at the solution that they had. So approach is using the where clause. So they are using again, this is my sequel. So they're using select everything from so it looks like they're tying it to itself as well. Yeah, so they're just tying it to itself. And then, okay, so it looks like they're yeah, they're using our Cartesian join, which if you don't know what a Cartesian join is, it is going to well, it doesn't have a good description. A Cartesian join is going to match every single possibility. So take number one. So you're there's four rows here. And then the table that we're tying it to itself is four rows over here. So column or row one, we're doing 1234, row two 1234. So each row in table one is getting matched to every row on the next table. And that's what we're seeing right here. And then they're saying, we're the manager ID is tied to the ID, and then we're the salary. So somewhat similar, very similar output, obviously, in the similar end result, they just did it in a different way. They kind of started it with a Cartesian join. And then at the very end, we basically did the same thing. Okay, and there's also, there's also right here. This is my cell. Well, what was this one? This is an algorithm. And this one is a MySQL. I don't know what that means, to be honest. I don't know what that means. But the MySQL is basically very similar to what we wrote. So, yeah, that's that one. I'm glad I got right on my first try. To be honest, I didn't think I was going to get it right, because I thought I was messing up something with the joins there. But that's how you do it. And that's the MySQL output as well. If you were curious as to that one, let's move on to the next one. Our next one is duplicate emails. And let's look at this one really quick. It says write a SQL query to find all duplicate emails in a table named person. Okay, and here's our duplicate. One, three. For example, your query should return the following from or for the above table. And so we're literally just identifying it. Okay. All emails are in low case. I don't know if that's important, we'll find out. My initial thought is I could use some type of sub query. Again, sub query attempt table would probably work for this when you're working with which one table is that's pretty easy to do. Another way would be using having statement and I would just do a group by a group by on on the email. And then I can't do it in the where statement because you can't in aggregate functions, you can't say where that aggregate function is greater than one or two, you do that in the having statement. So I think that's the way to go. And that's the easiest to me, I don't want to write all this extra stuff, you know, join it to itself do a sub query where I have to write out another select statement, I think having is again, is just the easiest way to go. So I'm going to go that way. So we're going to select email comma count of email. And we'll do from person. And we'll do group by email. And we'll say having no, no, no. So I can't do that. Real quick, the output is literally only email. So it's not going to have a count in there. But this should still work. Because when you do a group by it is almost like using a distinct statement in a way, except you can run aggregate functions on those things. So some averages, max min counts, for example, but I don't have to I don't necessarily have to have it in my output. But I think I should still be able to do count of having count of email. And I should be able to words greater than one. So if it's only one, it shouldn't be in my output. If it's greater than one, it has to it should be in my output. I don't know if this is actually going to work. I haven't done something like this in a while. Okay, so it does work. And that's my output. I could go on. I could have gone a lot of different routes with that. And to me that just made the most sense. And let's see what this one says. It says select. Oh, maybe they maybe I did do it wrong. Oh, no, they're saying group by and a temporary table. So selecting email to do the group by so this is exactly what I was going to have in my in my output originally that I changed it. And what they did is they put this statement within a parentheses and used as their temp table. And then selected the email from that word, you know, num equals one. So they basically did the same. They didn't do the same thing. Their main statement was very similar to what we had. Except they decided to go to temp table route, which is totally fine. And this is the one that we used, which was the having statement and they wrote it almost identical to what we had. So there you go, there's some options. And that's how I did it. Let's go on to our very last one. And let's see what this one is. So our next problem is combined two tables. So I'm already imagining some type of joint or union of some shape or form. It gives us a table, the person table. And there is is literally giving us the schema for it. So person ID first name, last name. And then the next one is address. So there's a dress ID person ID. Okay, I'm guessing those are the same city and state. A dress ID is the primary key for the scale. Okay, that says write a sequel query for a report that provides the following information for each person in the person table, regardless, if there is an address for each of these people. So that part is really important, I'm sure. But let me just think about it for a second. So it says regardless, if there is an address. So even if you know, we're trying to take into account, so I'm guessing it's sometimes like left join, where we're looking at everything in here, but even if they don't have an address ID, we still want that person in there. So let's start writing this out, because it may make more sense once I start running it out. Again, when I think out loud, it may not be 100% accurate until I write it out. So we're selecting first name, first name, last name, city, state. So okay, and they're literally the same. So first name, last name, city, and state. Okay, and then we're just going to do from, what's this called? From person. And we have to join these together. So let me just do first, oops, first, you can do as as well. If you want to do as, I don't normally do those unjoins. And then we'll join it to address. And we'll just do add. And then we'll do like that. No, we'll do like that. Then we're gonna do that on person.person ID is equal to address, address dot person ID. So that part is really easy. But now I need to go back. First name, last name, city, state. So if there were overlapping, if I was including the person ID in here, since there's a person ID here and a person ID here, I would need to specify which person ID I was pulling from. So I would need to do like ADDR person ID. But since I'm not doing that, since I'm not including something that overlaps in both tables, I don't need to do that. And so for the sake of saving time, I'm not going to. And then was there anything else, regardless, if there's an address for each of those people? And again, I think this is a left join. Let's run it and just see what happens. Okay. So the values were Alan Wang, no, no, Alan Wang, no, no. So I got I got it correct. My, my guess was accurate. Again, if I had gotten this wrong, I would have showed you and we would have just looked at it and figured it out. So let's submit this really quick. And should be correct. And then we'll go look at the solution. So the solution is an outer join. Oh, a left outer join. So exactly. We use an outer join instead of the default inner join. So the outer join being the left join. And it looks literally almost the exact same as what I had, except the way you do joins is just a little different in my SQL. So we got that one correct. I don't, I don't think that one was super difficult. I think that one was probably the easiest one. So saving the easiest for last. Fantastic. I love it. So I hope that that was helpful. I think that was helpful for me and myself esteem, because I think I got all of them right. Or maybe I got one wrong and fixed it. But you know, all that being said, those were the easy problems. So if you are able to do all of those, if you at least knew how to do those, and you know how to do those easy ones, that's fantastic. That's a really good start. I definitely had to think about some of those a little bit. In the next one, we're going to move on to the medium or the intermediate ones. And I'm gonna see if I can do those again. You know, I just, I haven't done leak code in a while. And so a lot of these problems are completely new to me. And so, you know, I, I may struggle with them as well. And if, if so, if that happens, you guys can struggle along with me and correct me in the comments if you guys know how to do it and I just wasn't doing it correctly. So with that being said, thank you guys so much for joining me. I really appreciate you watching this through with me. This was something that I thought would be really fun and, you know, something that everybody would enjoy or get something out of. If you haven't tried leak code, try it out. You know, you may find it's a little bit more difficult than you think. Or it's a little bit easier than you think and you're, you're good to go. I will say that if these are easy for you, like they just come super naturally, these are probably the type of questions or the difficulty I would imagine in a lot of data analyst interviews. I wouldn't imagine them going super a lot more difficult than this to be honest. But we'll, in the next video, whenever I do my next leak code, medium difficulty one, I will find out if that, if that is the difficulty that you should be aspiring to. I know when I was first studying, I did easy and medium. I never, I don't think I even, maybe I attempted hard and it was just too difficult for me. It'll be really interesting making that video to see if I can actually do it. But I couldn't do it when I originally was studying. Like then this was like, you know, two years ago. So with that being said, hope this video was helpful. Thank you guys so much for watching and I will see you in the next video.