SQL Interview: 30 Simple SQL Interview Queries in 2019 | Lukas Vyhnalek | Skillshare

SQL Interview: 30 Simple SQL Interview Queries in 2019

Lukas Vyhnalek, Microsoft Employee, Programming Teacher

SQL Interview: 30 Simple SQL Interview Queries in 2019

Lukas Vyhnalek, Microsoft Employee, Programming Teacher

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
3 Lessons (34m)
    • 1. 01 0 10

      9:50
    • 2. 02 10 20

      7:51
    • 3. 03 20 30

      16:15
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

151

Students

--

Projects

About This Class

Are you getting ready for an SQL interview or do you just want to check you SQL knowledge?

Then you have come to the right place.

In this course I will prepare you for a SQL interview. By showing you all kinds of SQL Interview questions that you may get on an SQL interview.

What is in the course?

  • 30 SQL Interview queries

I also explain how to solve each query in a video lectures, so that if you don't know how to solve some problem you have the opportunity to learn.

Everyone is afraid of failure and that is why after finishing this course you should be well prepared and ready for an SQL interview. This course is designed to help you master SQL interview question. SQL is one of the most highly in-demand skills in the computer science sector today.  So when you are able to write SQL queries you should be just fine.

NOTE: This course is not designed for beginners, I expect you know at least the basics of SQL

Meet Your Teacher

Teacher Profile Image

Lukas Vyhnalek

Microsoft Employee, Programming Teacher

Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

phone

Transcripts

1. 01 0 10: Well, hello, Gas. And well countered his lecture and lecture. We will talk about these 1st 10 questions. Okay, so let's get into it. I'm using K online compiler. You can use the same I at a link to this in the resource thing off this lecture. Okay, so let's get into it. First of all, it says the first task says delete table employees department and company. So let's do that. When you want to delete a table, you simply write drop day both and then the name so employees. Oh, like this employee, then we would also like to drop the table department, and we would also like to drop the table company. Yeah, come funny like this. So right now when I run this it when it will not work because there are no tables, right? You first of all need to create the table in order to delete it. That makes sense. Right? So if I run this, it says yeah, doesn't exist, right? So let me just get rid of this. So this is the first task, right? When you want to delete table simply right. Drop table. Let's g o and create the table. so the second task says create tables, employees with attributes I D name City department and celery. So as get into it when you want to create a new table, simply write, create table Dan parentheses, right. You also need to specify the name of the table, obviously. So the 1st 1 is employees like this, and then you write all the attributes. So the 1st 1 was I d. And this will be type integer and this will be also primary key. Right? Then the 2nd 1 Wolf's name and we will use a march are for it. And the maximum value of Archer is 2 55 So let's set it to 2 55 Yeah, you can obviously use a small number because probably nobody have a name that have 255 characters in it. But anyway, so then we want the city and it will be the same data type. So what's also right in here to 55 and then we have the department that's still the same. But let's give it, like only 50 characters, Okay, cool. And then lastly is the salary so dead rob you once again a integer and we can do a constrained right. It doesn't make sense that the celery is no get. It's It's like smaller than zero, right? So we want to check when we insert value. We want to check whether the celery is greater than zero right, because it doesn't make sense to have a salary that it's smaller. Dan zero cool. So right now it's Grady Second Table, so create stable the same. And in here it's department like this and once again, parentheses and inside them we ride the attributes. So first of all, it's I d. And these will be once again off type interior and it will be a primary key like this. And then we have the name of the department, so name it will be a var char and let's get it. Also 50 right? Cool. And yet it's about it. This is the second table, So let's create the last one. So create table company and this one have a name while I d. Name and revenue. Right. So I d will be same as before in teacher and primary key. And then the name of the company will be our car and 2 55 OK, and then we will have d revving you. Revenue like this and Sylvie also a integer and weekend once again. Check. What do you Revenue is greater, Dan. Zero. Right. Well, even though this the revenue can be negative, so without a check. So this is these second SAS Graddy. Second question. So if I click on this ran it. As you can see, there is no error message. It would appear somewhere in here, right? So everything works just fine. We have created free tables right now. Let's insert the let's uncertainty values into the tables. So first of all, we want to insert into employee table, so insert into employees and we want to insert their values. And I'll just copy paste the values that I have put it inside D questions. Right? But if I run this everything, where's this fight? So yeah, this is also all right, then we want to add Rose into the department table, so pretty much the same as before, simply right. Insert into department values like this and then simply copy based it to save some time. And lastly, we have the company table. Right? So we want to insert into that. So simply right, insert into company like this and values and simply copy basted once again, you can also play with the space inside here. But I don't have a time for that like this. So if I run this as you can see no error message. So Edwards is fine. So right now the fun begins. We have a data, and we can do stuff over it. So first of all, we want a query. All arose from department table and how to do that? Well, we just want to select everything from departments table. So if I around this right now, there should be a table. As you can see, there is the table, and it have the records that are inserted in here. Okay, so the 1st 1 has i d and so on. And so so then we would like to update the name of the department with I d one, and we would like to change it to management instead of i t. So we can do that in order to update something recently right? Update and dandy table. So department. And we would like to set de name to management like this and we only to the record where the idea is equal to one. So where I d equal Swan. So if I run this, there will be nothing in here about If I once against selectee data after the update, so I'll do just the same thing. There will be management, right? As you can see, there's the management, So this one changed into management. Cool. So that's the seventh task. Let's go to the eighth. We want to delete employees with salary greater than 100 1000. So Lazutina simply write the lead from employees because that's the table we want to delete from right. And we want to delete all the records where these celery, celery IHS greater den 100,000 like this. So if I may be once again, select everything from the employee table Okay, so we'll take a look how the employee table looks like. Then we will delete the employees with salaries over 100 grand and we will select everything from it again. So if I run this as you can see, the Benny and Chloe have both celery over 100. And as you can see, they are not in the stable. So cool. This also work. I'll just get rid of this stuff. Cool. So right now, let's go to the next task. And well, we want to query the names of companies, so simply write, select. But right now we are not selecting everything. We only want to select the name, right. So select name Bram company like this. And if I run this as you can see, there are the names off cos. And the last one for this lecture is the query de name and city off every employee. And we will do that pretty much similar. So select the name and the city Bram employee table like this. And if I run it, as you can see in here, is the each employee with the city he works in. Okay, So cool. And as you can see, d Chloe and Penny is not in there because we deleted them inside here. Okay, Cool. So debts The first thing queries. If you have any questions, feel free to ask, and I will see you next time. 2. 02 10 20: Well, hello, Gas and well, countered his lecture In this lecture, we will take a look at next 10 query. So let's get into it. So the 1st 1 says the 11th 1 says, Query. All companies with revenue greater den five million. So let's get into it. We want to select the name off all company or we can select everything right from company. Where do you have in you is greater den five million Legace. So if I run it ah, there should be Google and Apple. Yeah, they are so cool. Then we would like to select all companies with revenue smaller den five million. So pretty much the same. Select everything from company where the year revenue iss smaller dan of five millions like this. And if I run it, there should be IBM. Yeah, they are, they're So if you want to select only the name so you can simply do that by writing name in here also without the typos, so name. And if you run in, there should be only IBM Cool. Then the next one says. Query. All companies with revenue smaller den. Five million, But you cannot use smaller than operator right. So you cannot use this operator. So how do we do that? Right. Well, do you know that there's something called negation? Right? So you can ask where something is not met. Okay, so we can do that simply by writing select the company's from company. Where is not true that the revenue is greater than or equal to five millions. Right? So when it is not true that the revenue is greater than or equal to five million, it must be smaller. Right? So if I run this as you can see, there is the IBM and we didn't use this operation. Cool. So then we can query all employees with celery greater than 5 50,000 and smaller Dan 70,000 . And yeah, so let's do that simply right. Select. And we want to query all employees. So select everything from employees. Where the celery It's between between a 50 thousands and 70,000 like this. So if I run it, there should be all the employees with celery, right? 70 and 50 between them, Right? There is no other employees with celery between these two. And as you can notice, it's takes even the salaries that are equal, right? So that's important to note. The next task says exactly the same, but we cannot use between. So how do we Candice supplement the between? Well, we can select once again everything from employees, and we want to ask them where these celery that must be. It must be greater than 50,000 like this. And also it must be smaller than 70,000 right or small than equal to 70,000 like this and same thing in here. I'll just add the equal sign, and this basically is the same as this one. So if I run it, the two tables should be the same, and day are cool. So let's get ensued. The next one query all employees with salary equal to 80,000. So simply select everything from the employee table where these celery is equal to 80,000 like this. Simple is that, And as you can see, there's D David Cool. So right now we would like to query all employees with salary not equal to 80,000. And how do we do that? Well, I'll just simply copy paste this one, because all we need to change is this equal sign right. So right now we would like employees that are not equal to 80,000 with salary that is not equal 8 to 80,000. And we do that like this. So this is basically the same s when you use in programming the not equal operator. Right? So this one, you cannot use it an SQL. So use this one is instead Okay, So if I run it, as you can see, there are all the other employees. Cool. Then we would like to query all names off employees with celery greater than 70,000 together with employees who work on the I T department. So we want to select everything from a sir, not everything. The name from the employee table where De Celery, Right. You need to maybe read this again. So where d celery is greater than 70,000 so salary greater than 70,000 and also or write or together with the employees who work on the I T department? So that means already employees work on the I T department. So simple, right? Or the department is equal to I t like this. And if I run this using the F eight as again see, there are names off all the employees. Cool. So Al Burgess semi going at the end of the line and let's do another one. So burial employees that work in city that starts with l. So select everything from employees where the city, it's like hell and then something. Okay, the l and anything behind it. Okay, so it starts with l. And if I run this, there should be employees. You work. Add London. Right. That makes sense, because that's the only city that starts with L. There's room. There's Paris, Right, Cool. So that's another one. And let's do the last one. So select all employees that work in city that starts with L or ends with s. So I'll simply copy paste this one. Because that's pretty My similar, right? That's the all the employees that work in the city that starts on l or we also want to select the employees that work and city that ends with s so or city is like like and inside . Here. You want to write anything and ends with s. Okay, So if I run this as you can see, there's also Paris because that ends with s right, But the London is still there because it starts with l cool. So that's pretty much it for this video. If you have any questions, feel free to ask, and I'll see you next time. 3. 03 20 30: Well, hello, Gas and well countered. His lecture and his lecture will take a look at the last stand questions. So let's get into it. So the 1st 1 for these lectures says that query all employees that work in city with, oh, somewhere in the middle, right? So basically, city must have Oh, somewhere So we would like to select everything from employees where the city is like, oh, somewhere in middle. So there can be anything at the beginning there, Dearest Oh, and then there could be anything at the end. So if I run this as you can see, there's London because there's Oh in here and also in here and Rome because they're so in here. Cool, the owner one says. Query. All departments, each name Onley ones. So we want to select bone everything from well named sorry name from the department Table department. But as you can notice, when I run this, there are multiple management right departments, so we would like to only quer it once. And in order to do that, we will use the distinct right so select distinct this things like this. And if I run this as you can see there there's each department Onley ones so cool. Then we can do query names off all employees together with I d off department. They work in, right. So, as you can see, the employees have somewhere in here department, right? The department attributes, but we would like to get this idea. So this number off the department So we like to basically go kind of connected, right that we cannot use joint, so we would probably is joined to connect these, but we cannot use them. So what? What do we do while we will select the name of employees, Right. So let's do it like this E name and the the apartment I d. So deed. Ah, I d right from the employee table and Al Jazeera. Name the employee table S E. So I will refer to the employees as e and also from the department table. So depart meant table, and I'll refer to it as d. So as again see in here, right? And we have to do that because we cannot refer only to the name because both employees and the department have named right, So we'll do it like this. And then all we need to do is sick that the name of department must be equal to the department off the employee. Right. So we do it using the wear cross. I'll just put it on next line where the employees department, like this must be equal to the department name. And then we will get the implying name and the idea of the department. So if I run this as you can see, Jim works at one. So let me let me see. And it's men. Yeah, yeah, yeah. We changed it to management. Right? So sorry. This this might be confusing, but inside here we have the update. So we changed it to management. Okay, so it works that management. And as you can see, it's a swan and too cool. So this one works. Let's do another one. So right now we can use drawn, So I will do the same. Only this time I will use joy. So I'm once again select the name off the employees from the employee table and our once again refer to it as e. But I would like to join it. So join that with the department table and I would like to refer to it as the and I would like to join it on the employees department being equal to department dots name. Okay. And then I forget in here to select the department idea. Right? So, comma and department I d like this, and that's pretty much it. So if I run this, there should be two tables that are the same, and they are cool. So these are the different approaches, right? These two queries are exactly the same. Okay, then it says query name off every company together with every department, So name of every company, together with every department. As you can see, we cannot connected the right. We have simply company name and then the department name. Right? So what do we do? Well, we simply do basically, like, set multiplication right when you multiply Said By said it takes every element from one set and puts it together with all the elements in the other set. So we will do exactly the same using the select, and we will select the name of the company. So you can also do it like this. Come, honey, that name. And then we would like to connect it with the department. That name, right? And we would like to do it with each department, so we'll simply select it from the company table and the department table, and it will put all the companies together with all the departments. So if I run this as you can see, IBM gets together with all the department and same thing in here. Cool. Then what we want to do is square e d. Name off every company to get a with department without the support department. So, as you can see in here, there's a support department. So first of all, let me just ask you, how do you get rid off these sewer lines? Right, So they are duplicate, right? So how do you get rid of that? Simply right distinct in here. So this thing like this and if I run this as you can see, there's each line only once. Cool. But anyway, let me get back to the case. So we want to select only the bars mons. But this side we don't want thes support department in there. Okay, so, almonds again, Copy pays this one, and we would like to restrict the support department. So where the department name. So I'll just put it on next line where D department name is not equal to the support department. Right? So soon port like this. And if I run it, there should be second table. And as you can see, there is no support department. Right? Cool. So right now, let me just come on despite out so that we don't have to scroll to see the tables. Right? And let's go and do another one. So the next one says query employee name together with the department name that they are not working in. Okay, so we would like to select. Let me just get rid of the tables. Okay? So select the name of the employees together with the together with the name of the department that they are not working in. Okay, so this is a bit complicated one, right? And we know in which department? The employees working in right. It's right here in the stable. But we do can not going to figure out how to how to do this, right? How to select the department's. The employee is not working in. So in order to do that, we can simply select Well, we will select. We will do. Once again, pretty much same thing gets in here, right? We will put together each employee and each department. And then we will remove the departments where the employee is actually working. And that way we will get the department where the employee is not working. Okay, so let's do that. We will select the name of the employees and the name of the department. So the dot name and in here will be eat that's name. Okay. And we'd like to select it from the employees and s e and the department s de table. Cool. And then we would like to remove the employees that employees and the departments that are in the stable. Okay, so in here, we right, except Andi. Except these select. And then we would like to remove the employees and d the yes. So we, like dislike the name and the department from the employee table. Right? And if I run this as you can see, there are all the departments dead. These employees are not working in red, so that's pretty cool, right? Good. Let's move on to the next one. So in here. It's ask very company name together with other companies. Name. So I'm not sure why you want to do that, but it pretty much doesn't make sense, right? This is just for teaching purposes. So you want to put Google together with IBM and Pepper, but not with Google again, Right? And then you want to bake IBM and put it together with Google and Apple and same thing for Apple. So basically what you are doing, you are selecting the name off one company. So see that name and then you are selecting the name off other company, right? So see to that name and you can select from same tables, right? Multiple times so select from company at sea. So that's one table and then once again from company and s C two. So that's the other table. So imagine that this table, right, the company table is there two times. One is called C and the other one is called C two. OK, that's basically what we are doing in here. And then you would like t put together all the pears where the see that name is not equal to see to dot name right, Because you don't want to bear Google with Google and Apple with apple. You want only the different companies, right? So if I run this, as you can see, there are paired. Okay, Cool. Let's do another one. So this one says query employee names with celery, smaller dan 80,000 that we cannot use not or the small den. Right operation. So how do we do that? Well, we can select the employees and then we can remove the employees that have salary greater than 80,000 greater than or equal. Right? So was duda. Simply write Select the name from employees. So we'll select all the employees. But we will remove the employees. So select name from employees Where the celery as greater than or equal to So we are using . We are not using the smaller than operation right. So is greater than or equal to 80,000 like this. And if I run this as you can see, the already employees with salaries smaller Dan 80,000 right? So, for example, a vise there, Right? Good. So yeah, let's do the another one. So query names off every company and changed the name off the column to company off the Result column. So select the name. And right now we would like to change the name to be company. Okay, so s company, and that's about it. So from the company table, and when I run this, there should be okay. I made a typo in here instead of company. This company. Sorry. Come the knee. Like this. And if I run this Oh, my God. And under Al just simply couple bases. Oh, my God, Sir, about it. So as you can see in here, it says company cool. And let's do the last one. So that's s query. All employees dead work in same department as Peter. So this one is a bit complicated We would like to select Well, basically everything from one employee from the employee table. Right? So employee s e. Okay, bar. They must work in the same department s Peter. And let's say that we don't We cannot go inside here and check in which department Peter is working on. Right? So we cannot simply put it in there. We need to check, right? So we need this to work. Even though when you change the department where Peter works to, for example, management. We want this query to work. Okay, so we need to select another employee from the employee table, and this one will be Peter. Okay, so Right, well, selecting from two same tables. Okay, 1st 1 is the colleagues. Right. So this one is the e all the colleagues off the Peter, and the 2nd 1 will be the Peter. So first of all, we need to make sure, Dad, this is actually a Peter. So the beater dot name must be equal. Sudi Peter. Right? So, Peter and maybe we would like to No, the idea of Peter, right? That might work better because there can be employees with the same name, right? But that's for another, like Okay, so cool. We Right now we know that, Peter, it's in this Peter table, OK? And we would like to check whether the employee dot department is equal to the department where Peter is working on, right? So like this. And it also must be not equal to beat her. Right, because we don't want to select Peter as a colleague of Peter. Right? That doesn't make sense. So the Edo name must be different than the Peter name. Cool. And you also want to boot. And inside here. So end and end like this. And if I run this, there should be all the employees that are working on the I T department. Okay, so if I show you this stable all the employees that are working on the I T department, except for Peter. As you can see, the Coe is not there because we delayed it inside here. So yeah, that's about it for this lecture. If you have any questions, feel free to ask, and I'll see you next time.