Transcripts
1. What We are Going to Cover In This Course: hi and Brian home and welcome to the course mastering sequel database. Crease, Get anything you want from your data. So I'll just walk you the true quickly on what we're gonna cover in this course. So here's the structure. For course, we're gonna be starting off with all about SQL or sequel and data basis. And then we're gonna be setting up your training ground, which is in your computer on how we can start coding right away in Firefox. Pretty cool, huh? And then after that, we'll jump straight to data manipulation. Just more of the basics covering on How do we create tables? How do we insert data update, delete data swell inside the tables, and then we jump right onto single table crease from zero to hero? Because what we're doing here, ISS starting from scratch on how we can start creating data in a single table up to the advanced concepts and a nexus. Joining this is the heart off database querying because this is where we start interacting with multiple tables and then get more value out of the data and the nexus sub Korea's. Because I wanted to show you that there's also another way on how to relate tables to each other. But first things off. Thank you so much for taking this course. It really means a lot to me and preparing this one. And I hope that it would bring a lot of value to us. Well, and then please recommend this course if you enjoy this one, and any honest review is greatly appreciated, so let's get started.
2. All About SQL: Now let's talk about sequel So sequel stands for structured query language. It's a programming language used to query or manipulate data in a relational database. So when we say create data, it's more off retrieving data from the database and then could be massaging or manipulating the data in such a way that it will be useful for your needs and manipulating data. It's more off inserting, updating or deleting data from your database and the database have different management systems to name a few. Microsoft Sequel Server. Microsoft Access Sequel Light. My sequel. Oracle. So some of this could be familiar to you and for the database. You could think of this as a collection of tables. So maybe something similar to Excel, wherein there's multiple worksheets inside Excel and then each work shit. You could think of it like one table for each one of them. And why would you even butter learning about sequel? And here the benefits that I could share to you tickle developers or database administrators or DB A's are paid very well and mowing. Sequel. It's one of the most sought after skills and one advantageous well ISS. It will allow you to get answers that you want really fast. So you have your raw data and you can massage it quickly in such a way that it will give you the information that you want. So here's a table of students that I want to show to you. It's actually one of the tables that we're gonna be using in our course. And you could see here that we have the student I d have the first name. We have the last name, nationality and date of birth. It could be peculiar to you. Over here is the student i. D. This is actually our primary key. So when we say Primary kids column or set of columns, that's unique identifier for each individual row in this table. So, for example, so we can say here that Mickey Mouse has the unique identifier one. And if we say number four student I D number four were pertaining to Daisy Duck in this table, so this is actually used a swell to relate to. Other tables in the database will be showing examples in a short while. So we have two more tables over here. You can see here the table of classrooms. So we have tree columns here. Classroom I d. We have the class name and then the weight. So the weight is just more off saying How heavy is the grade off this class toward your final score and table off student enrollments? So this is the connector between the students and the list of classroom. So you could see over here that we have student I D column. And then we have the classroom I D column and the grade off each class student combination . Okay, so you could see here a swell. There's a primary key of student enrollment i d. For this specific table. And then there's also the primary key of classroom I d. For the table off classrooms. So just to relate all of this tree tables to get her this is the same tables, actually, what we saw in the previous slides and over here, if we look at the middle student enrollment table, you could see here that for the first row were saying that student I d. One and classroom mighty one. There's a great off 91. So what this signifies is one student I deace Mickey Mouse, and in the classroom. I t one pertains to public interaction. So what this means is Mickey Mouse stood at 91 in the public interaction class, which is class idea number one has a score off 91. Okay, so we just why Mickey Mouse scored 91 in public interaction. And if you jump over to the turd row, it's gonna tell us that Mickey Mouse stood in 90. Number one in the running class, which is classroom number. Tree has to score off 89. So over here you could see that there's a collection of tables that we have, and the collection also defines, like this middle table, the student and rolling the table defines the relationship between the student table and the classroom table, and on top of that, it also keeps track off the grades off its student.
3. IMPORTANT UPDATE - Your SQL Training Ground: Hi. It's a video update on the application that we're going to be using for this course when it comes to performing our sequel exercises. So the original way for us to execute our sequel statements was through Mozilla Firefox and also using an add on to Firefox called Sequel Light Manager. But the problem with that one waas Firefox will has been upgrading their version, and as a result, the sequel Light Manager doesn't work anymore. Firefox and one of our students recommended this one, and when I have trying to sound, it worked great. And that's really a good recommendation that I would want to give as well to all a few. So I've been trying to South us well for DB browser for sequel night. So here's the website sequel Light Browser that Borg, and what you can do is simply download it, depending if your Mac or if you're using windows, right and then install it on your machine and then you'll be able to start performing the sequel exercises as well. The good thing with this one is also true, of course. We're using Mozilla Firefox and sickle life manager in our course in the videos, but the use age off thes application, the DB browser vertical. That is exactly the same. So I'll give you a quick demo on how to install DB browser and also to start using it. Okay, so in my case, I'm using windows. So I've downloaded the windows application or installer for this one, so I'll just double click on it, and then we can do a quick install off the DB browser. Right? So wants this set up has loaded, it's just click next. Right. This one's good degree. You can click on next. Okay, Just go install. And then once we finish the installation, we can go ahead and start using this one to have a look. And the good thing with this one is it's very similar to the rest of the tutorials, so there's not gonna be much of a difference. Okay, so now we have our devi browse your window open over here. So the first step for us to do is to create a new database, So I want to click new database. We just need to specify a file name on what we want to file. Like the database name to be OK, so it's just go saved this one quickly. Just take in. Test database over here. Right, Go safe. And now I want to We have it. We could just close this window and then for the purposes of the course will be focused mainly on the execute sequel tab over here. And what I have is the initial script. Create our initial data base so that we can start performing our exercises. I'll just pace the text in here. Okay, so I'll be showing this a swell in the next lesson on where you can get this foul. But I wanted to give you a quick demo on how we are using Devi browser. Right? And then once we're happy with the script, we'll just click this one. This is the run sequel or execute sequel bottom. It will execute the entire script in here if you go this one. And we were able to execute our sequel statements successfully in here. Okay, so this is the same pattern that you will be using True of the course when we're doing the other sequel scripts. Just put your script in here and then click the execute sequel bottom. So just to reiterate, we will not be using Mozilla Firefox and the sequel Light Manager. Add on to it because it doesn't work anymore. And this is what I'm recommending in the moment BB browser for sickle night, because it's a lot easier to use a swell and you're just using one application. So if you have any issues or any questions, just feel free to use our discussion section right, Just leave your question there and I'll get back to you as soon as possible.
4. Setting Up Your Training Ground: Okay, so now we're gonna be discussing your training ground. So this is where will be doing the sequel programming so that you can create the database from scratch, right? All your queries do. You're just high, isis. And on top of that, you just need Firefox to be installed on your computer, and then we'll be using a Firefox Adam called sickle light manager. So I've listed out the steps here, but to make it clear to you, what i'll do is a quick demo on how we can install this on your computer. Okay, so we just used this as a quick reference if you need to go back to Okay, so here's D Firefox page and I'll be posting the link a swell over here. So wanting to take note office I'm using actually an older version of fire file. So you could see here is the Firefox extended support release because the sequel light Manager doesn't work with the latest version of Firefox. So if, for example, if you have the latest version installed, just go to this page and just scroll down and download, for example For me, it's gonna be Windows 64 bit so I'll just go straight to the English and I'll just downloaded and installed Firefox, so it's pretty straightforward. Installing Firefox is just clicking next once you start d installer file. So we just go for the specific operating system that you're using and then just download the installer. And then once you have Firefox install it, started up and then go to this link. I'll also be posting this link. So just look for a sequel light manager, and you could see over here that there's a bottom cold add to fire file, so it's pretty straightforward. I'll just click. Add to Firefox So now it's ready, asking me if I'm gonna go with the installations are just click Install, and we're going to be having a quick restart on Firefox. So after clicking Restart could see over here in my Firefox is on appearance late right now , and I'll just go straight to So let me just go to tools and you're gonna be seeing sequel like manager Now it's now installed in your Fire Falls, and this is the sequel Light Manager. The interface and first things first is we need to create a new database so I'll just click new. Just give it a name that say, this is our math being sequel Database. Go OK and it's gonna ask you Onward, Do you want to save it? So it's up to you on where you want to save it for me. I'll just save it in a folder and a new folder, cold and say this our database. I'll just select it. And now you have your empty database. So this is more flecked, just a container for all of your tables inside. And the next thing is, I've already created initial script for you. So initial scripts this were all off the created to student tables. Enrollments, the classrooms and the initial data is also going to be populated using their script and to find the script. It's actually in the your projects tab off this class and skill share, and you could just go over there and download that text. But it's just a text file, so I'll just copy text. Let me just make this bigger, Okay? So let me just delete this one, and I'll just pace the text and you could see that this grip it pretty much craze. All of the contents that's needed for discourse. And the good thing with this one is, for example, if you did something with the students table that broke the table, you could just rear on the script. And what the script will do is it will remove everything all of your tables, hold the data and then re create it from scratch. So which means it will bring you back to the original state, and you could restroom the course using that reset data. Another thing to take note office. Make sure that you're in the Execute sequel tab, because this is where you can start coding and then run the sequel scripts. So once we had the script in here, I'll just click run sequel and you could see here. Last error is saying that it's not an error. So which means everything runs successfully. And if we scroll to the left, you could see here that there's already tree tables, classrooms, student enrollments, students and let me just delete this script over here and let me just type this just to see if everything is working fine from okay, students and go Run sequel and you could now see the contents of our student table
5. Data Manipulation: before jumping straight to the creed part. What I want to show you right now is the data manipulation of sequel. So the initials actually uses all of this so we could see here to create table and how we created the students table and then asserting data, and you could also update and delete data as well. So over here you could see the syntax on the left side on how it's created. And then on the right side, it's an actual example. So, for example, over here we have great table, and then you specify the table name, and then you specify the column name and then the data type and in whatever constraint that we need. So it just repeats itself on additional columns that you need to add and any close it out with the clothes parent taste over here so you could see on the student demo, so we'll be creating. There's a spell in a short while and you could see your create able student demo and then we have a couple of columns over here. We have student I d. You specify it as an integer, which is a number, and then our constraint ISS primary T So which means that this column over here would uniquely identify its row in our student demo table. Okay? And then followed by first name, which is a var carve. Our car is just a string off. You could think of it as a text that has lent off 200 characters. This constraint over here, not now. It means that it has to be with a value always so. Which means if you want to add a new row, for example, to student demo, then there should always be a first name it this mandatory and the same thing for last name . So string off 200 characters Texas 200 and then not know so, which means it's also mandatory nationality, same thing, 100 characters, and then not know. And then over here, date of Bert and it's a daytime type. You could specify a date here, and then it's now meaning that it can be left s black. Okay, so this is for create table, and next this we have insert. So once you have your table created, you can already go into inserting data to the table. So we have your insert into table name and then the column. So which means we're specifying the column names off the table and whatever order that you specify here in the columns, the values should match. For example, if in column one expect if I first name, then the values here, the first value that I'm going to be specifying here has to be the first name a swell and call them to. For example, if I place it nationality, then it has to be nationality for the second values over here. So I can see here there's multiple rows off insertion. So which means this one this one room or one record and the 2nd 1 is the second row and cheddar. So in the pants off. If you want to insert 20 record in one go, then you could do it in a single insert statement. Okay, So if I go here right now, into into insert into students demo, we have first name, last name, nationality and date of birth. Then the values were going to be inserting two rows. So we have Mickey Mouse American for the nationality and then we have the dates bird over here. Same goes for the second role. So you can see here it's a comma, then followed by the next line Donald up Japanese and then 1922 for the date effort and followed by a seven contact Sami Common pretty much signifies that you've already finished your statement, so that's for insert. So let's jump over to the last two for data manipulation. So we have update, so update it's more off you have ready existing data inside your table, and you want to make changes to the data. So update issue could see the same tax update specified the table again and then set a specific column to the new Value and then other columns and what values you want to change . So it really depends on how many columns you want to change for the value. You could specify 10 columns if there's a need to and over here is something new. We're conditioned so which means it's more of a matching criteria on which records do you want to update? So, for example, if I want to update over here updates students demo set the first name to John were last name equals smells, So if we have a record off Mickey Mouse inside that table. Then the last name of mouse would match that record. And then we would be changing the first name from Mickey to John using this update statement. So the elite, on the other hand, is you think of it. It's really similar to update so delete issue. Need to specify again the table name and then a work condition. So same thing there's a matching criteria that you need to specify on which records do you want to delete from your table. So over here we have delete from students. Demo were last name equals mouse. So we want to remove all the records that have a last name off my house. Okay, so let's jump over to sequel light manager. And let's see all of the statements and action. So what will be placing first is Thekla a table and I'll just run this first students demo . So no errors, Okay? And then what? I'll be typing. Is this actually an advance portion? We'll be discussing this in the next video Select star from students demo. So for now you could think of this s a quick statement to show the entire contents off a specific table. So if we run it still nothing inside. But you could see the call if that we created student I d first name, last name, nationality and date of birth. And now what we'll do next is we want to see some data inside our students demo table. So we have insert into students Demel and Mickey Mouse and Donald Duck. So I'll run the sequel. So not an error again. And I'll just go on to so that we can reuse de Select star just to double check the contents of our table. So I'll take Run sequel and you could see now we have two records inside Mickey Mouse and Donald up. Another thing is to take note office in our insert statement a while ago, let me just place this here. We didn't specify student I D. In this column in our insert statement, because one thing is we defined student I d. As primary key, and what it will do is it will auto increments, which means it will auto populate it. So if I add an under row here, it will be number Tree number four, a cheddar of 5678 and it will just continue, and it will ensure that its unique for the entire table. So it's something that you don't need to insert you don't need to handle because it will be automatically increment ID for you. So let's jump over to update just to see what happens next. So what we want to happen now is updates to its demo. So we want all of the records with the last name of mouse, which is this one. Change the first name to John. So let me run this. Okay, No errors. So that's type again. Select star from students demo. And now I could see a record. It's now John Mouse and for the last one for delete. So let's put this one here. You want to delete from students them over the last name. It's mouse. So we just this record over here. This run this, okay, just kick. And do we have our select statement? Let's run the select statement and you could see that there's only one record left for the students demo because we've already deleted all the records that has last name off my house
6. Simple Queries: Okay, so let's get started with single table creates. So single table quarries were gonna be focusing first on retrieving data from one table. So we want to get your foundations right first in terms of creating one table before we jump over to multiple tables. Okay, so let's get started. We have here, get the entire students table, and you could see here there's two dashes. So what this signifies is this line over here becomes comment, which means this will get ignored and not be treated as a sequel statements. So this is pretty useful if you have a long, fickle script and you want to place documentation explaining different parts off your script so that it won't get confusing. If, for example, months later, you come back to your script and then you wonder, What is this for again? You can use comments to do that for you. What we want to do here ISS right down the statement. So, like star from students. So the star signifies. Give me back all of the columns and from would specify the table that you want to get your data from. And since our table name is students, we want to get all the data from the student table. So that's run the sequel. And you could see over here that this is the eighth students that we have Donald up Goofy goof. They see a cheddar. So let's jump over to the next example. So get the student i d. First name last name from this to this table. So now we have specific columns. We don't want to get all of the column student i d. First name, last name, nationality and date of birth. We want specific column. So what will do? Yes, Celek. I won't be specifying first what were returning from students. Since it's from the students table, we'll be specifying student I d first name last name. It's drunk and you could see now we have three columns for our assault. So now first name, last name from the students table and we have a condition that has a last name off duck. So it's gonna be this records over here that we want to be returned. So how do we do that? So what I'll do is I'll just reuse our previous statement and what will be adding s a were we're for this one. If you're familiar with the update and delete, it's actually very similar where it's more flight of filtering criteria that you want to use so that your result will be filtered based on what you specify. So we want a last name stuck. So let's type it out. It's last name, equality dunk. So you could see over here that if we specify a string or text, we enclose it with the apostrophe. Okay, starting and closing over here. So let's run this one. And you could see now that we only have four ducks for records over here that match our were close. So let's jump over to our next example. Get the student i d. First name, last name from the students table that has a last name off duck or mouse. So now we have an additional condition. It's saying that last name off, duck or mouse. Okay, so what we'll do ISS. We can reuse again the previous career that we just executed. Last name ical stuck, but it's saying that not just dunk. They also want mouse to be returned, so either duck or mouse, so you could do this as last name equals stuck or last name equals mouse. So now we have a new key word, which is or so this used to specify multiple matching criterias. So which means as long as you satisfy one of them and you're good to go, So let's run this now. And you could see that both records off mouse, then duck are now returned. In your result, there's actually another alternative of writing this. So let me change this one right now. We're last name, so we're allowing two records, right? Duck or mouse? So we can use the key word in I'll just type in duck and mouse. So what this means is for the in keyword, you can specify a set of values that would match the last name. So which means if your last name has either duck or mouse or anything, any records that you place in here, that it will get returned in your result sent. So if I run this right now, actually it gave me the same assaults because both of the statements are the queries that we just created right now would be doing the same thing. Okay. To some, over to the next sample. Get all the columns from the student table that has a first name off Mickey. And last name off mouse. Okay, so it's saying that all columns okay from students, So all columns would mean that we don't care. We don't need to specify specific wants. So let's just go for star and our field there in cafeteria. Where? First name of Mickey. So it's Type it out 1st 1st name equals Mickey and last name off mouse. So we're gonna be adding a new key word we should end. Last name equals mass, but the and keyword does is both off. This criteria should be satisfied for it to be returned to your assault, which means it has to be both Mickey and Mouse. It's very different from the or keyword that we use of all ago. So let's run this right now. And now it returned just one result, which is Mickey Mouse? So next example, get the student i d. First name last name from the students table that does not have and last name off duck or mouse. So this is pretty different now. It's the opposite. You don't allow duck or mouse to be included. So it's type first. So we need the student i d. First name. Last name from students Table does not have. Okay, so we're last name. So now we're introducing you keyword, not in duck mouse. She could see a while ago that we used last name in. Now we're using last name. Not in. So not It's pretty useful if you want to negate a specific condition. So which means now it's doing the opposite. So now we're telling the query, Do not return the values that have duck or mouse asked her last name. So if you run this one, you could see that it returned goofy and Scrooge because it doesn't have duck or mouse as the last name. Get all columns from the students table where the first name starts with the letter M. This one's pretty cool activity. I'll show you new keyword. So get all columns. So that's gonna be select star. We don't care which columns from the students table. Okay, where? First name starts with the letter M. So we're gonna be using a new key word called like and I'll be typing this m percent and included with the apostrophe So what this means is like it's actually used to match a specific pattern. And the patter were specifying Over here is M and the person signs of what this signifies ISS am followed by any number off characters. So which means you don't care what's after em. What I only want to ensure is it has to start with the letter M which matches our tracked here over here. So first name starts with the letter M followed by I don't care how many characters. So if we run district now, it's gonna give us back Mickey and Minnie since both of them have the letter m in front. So get all columns from the students table where the first name has s asked the second to the last character. So this is pretty tricky. So let me just typing again. Get old column. So ecstatic star from students. Where? First name. So we're gonna be using, like, again. But the question is, what pattern are we using here? So I'll just tied this as percent. Yes. Underscore. Okay, so, person, same thing. What it means is regarded off any number of characters, which means in front I don't care how many characters underscore, on the other hand, would mean any character, but one specific character only So which means I don't care on what the last character would be. What I only after ISS s would be the second to the last character which would be signified by this one. So if you're on this right now, it's gonna be day, see, because they see has s as the second to the last character. So what I want to stress here is the person is pretty important. If I remove this one, nothing will get match because what you're saying over here, ISS, the first name should start with the letter s and followed by any character, which means it's only two characters long, which is why it won't get match. So if I put back 2% than that would work for Daisy, so get the number of records and the students table. So this is kind of different right now. So what we're after is the number off records in this tennis table. So let me just strike this first from students and to get the number, we have a new keyword cold count. What count will do is an aggregate function off counting the number off results that you have from your create. So if we run this right now, it's gonna give back eight over here. But another thing, though, is the column header off count and then asterisks doesn't seem particularly useful. So what we can do is there's another key word called s. And you can specify a new name Number of students. So which means ask keyword. It's more flak, Al, using your column header and giving it a different name so that it's more presentable if you run this one. So if I run this right now, it now gets changed to a new name off number of students. And according with this one is you can change it, actually. So, for example, if we have student I D. So if you run this right now, you can actually use to ask. He works well to take hello and it gets changed right away. Okay, so you could use the count keyword to return on a specific number, and we have a lot more functions or aggregate functions to share to you later. Okay, so let's do a quick recap of what we've covered for are simple career. So we have the double dash as a comment to signify or create documentation in your sickle scripts, and we have the select to specify which columns do you want to return star issues with Select, which pertains. Just give me back all the columns from specifies which table to get your data from. And then where it's your filled in cafeteria and an or an end combining multiple car here together so you could see that we used it for our work across In specifies a specific set off data that our Carteris should match, not issues for negating so, which means the filtering care here becomes the exact opposite. Like you specify a specific tax pattern that you want to feel their your records on. And then we have the percent, which issues in, like so this is the world car character. So which means you don't care how many characters, any number of characters, but for the underscore, its exactly one character an account is an aggregate function. To return the number of rows and an ass is you're gonna be giving on alternative name to the column that you specify. So for the simple crease, the mandatory keywords are select and from, and then the others issue would just use it as you need to. And here's the fun part. Now it's for your practice. So I would really suggest that Go over this questions and try to write your own Korea's because that's where you get your most learning from. And in the next video, we'll be going through this examples one by one, and I'll be explained to you on how I'll be writing this crease.
7. Simple Queries (Solution): in case of hopefully you've already gun true the exercises for this one. And let's compare our answers. Okay, So for our first question, how many students have a last name off? Duck or mouse? Okay, so let's type this first from so definitely coming from our students table. So have a last name off, duck or mouse so we can write this s last name in. So we have two matching right here. Duck or a mouse. You can also use the or keyword if you prefer to write it that way, and we're after to count, which means how many students So we'll be using to count keyword over here. So there should be six students. And you could also change the name if you want to number off. And now you have six over here. Let's go over to the next question. What? It's Mickey Mouse student I d. So we're just after the specific student I d. So now we're pretty sure if you're unsure on what's the name of the column? You can actually expand this over here on the left side, students and we have a student, i d. So it's just type it over here student i d from students and what's our field and criteria ? We have Mickey Mouse as the information. So which means the first name should be Mickey and last name equals Mouse this run and we have the student I d off one. So next give me the names and birthdates off all off the students. So which means we're pretty sure that we're getting it again from the students table and the information that we need this first name, the last name and the date off. But it's trying its up. And you could see we have three columns, first name, last name and the date of Bert off all of the students. This one's pretty tricky gave me the names and birthdates off students born in the year 1980 tree. Okay, so we're pretty sure we want the name. You want the same things a while ago and the effort and this is coming from the students table again. But a question right now is how do we specify the condition born in the year 1980 treat. So let's think a bit here in the date of Bert to be able to check if It's from the year 1980 tree. It seems that it's the 1st 4 characters would match 1980 tree. So this is a good application for the keyword off. Like like and then we want to match 1980 tree at the beginning, which means we don't care what ghosts after 1980 tree. So that's close, this one. And now we have two records. Scrooge and Mini. Both of them have 1980. Tree asked the year off Bert and for last exercise, how many students have C and the last name? So how many students? So let's leave this black first. So we're pretty sure from a student's table and our filtering criteria, ISS last name should have the letter C. So let's use last name and we're gonna be matching again a specific pattern and see in the last name. Which means so we don't care if C is in the beginning in the end or somewhere in the middle . But we're pretty sure that has the letter C in there somewhere. So which means we can actually do this percent percent. So which means we really don't care what's in the beginning. Anna Carter of no characters and we don't care what's Indian. Any characters or no character to swell. Dusty could be practically anywhere, and we're after it account. So that's just add this year. And there's five. And out of curiosity, let's have a quick look. This just changed this with the star and see which one. So we have this one. Duck, duck, duck and dunk. Okay, So for example, if it just changed this to D, then it should. It's the same set of records, duck, and then say that's changed us to em. So we don't care where am is located. And you could see that mouse, McDuck and mouse get much over here.
8. More Simple Queries: Okay, so let's jump over two more examples. Ah, simple crease. So get the average score off the five exempts off student number one. Okay, so this is kind of different right now, but what? I want to show you first. So let's select star from student enrollments It It's the other tables we have. And you could see over here that it's actually a list off student i d. The classroom mighty and the great off that specific student. So which means what we're after is actually this five rows over here, because it is for student number one, and they sort of five scores that we want to get the average from. So this one already know that we want to filter it by student i d. One. But the question is, how do we get the average score? So there's actually a new function that I want to introduce, which is some. And then you could specify the column that you want to add the values on, which means the grade this five records, and then to get the average, you add it all up and then divide it by five, which is the number of records and that's our average and a free run this right now. You could see over here that it's 74. So which means that's the average and I'll just give you the more meaningful name I'll just wrap with entities and then used the key word ass and they'll type average score. So now we have the column header s average score over here. So another thing to take note off is there's actually a arithmetic operators that we could use in sequel, so there's actually addition, subtraction, multiplication and division that we could use. So in our example, we use division to divide it by five. And another function that we could use actually is just to make it simpler. Is theatric aged function so we could actually use average great over here. Give it the different name again. The average score if you run this one is actually 74.6. Exactly more precise. It gives it up to the decimal value, So next get the 1st 5 students. So we're pretty sure that's copasetic star from students. How do we get the 1st 5? Only there's actually keyword called limit limit five hotel to just return the five records in your assaults over. You run the sequel right now. It's gonna give us just the fight records over here. I want to treat for five, but it doesn't specify exactly that. It's gonna be field ringing, but I want to find okay, it just returns. The 1st 5 records over here get the last five students. According to student I. D. So this is pretty tricky. If we do select star from students, there's actually eight records. So what? We want to return ISS this five records over here Because this our last five students, according to student I D. So the question right now is, if we use limit, it won't give us what we need. Because if we go limit five, it's just gonna give us want to five. So we have a new keyword, his cold order by student i d. And what this will do is it will sort the records based on the student i d. And the cool thing with this oneness, we can use this keyword called D s seed or four descending, which means sort the records by student I d. By the reverse order. And then we're going to be getting the 1st 5 records only. So what I'll do is let me just comment this out first just to see what happens. So we're just running the order by this statement over here, and you could see that the ordering is no different. It's now arranged from eight to the last i D. And the less ideas, actually one. And the good thing right now is if we use the limit. Five. It's going to return to 1st 5 records, which is this one. So if we remove this one now, let's go run sequel And we already have the last five students. As a result, just a share. ISS. It's actually not very strict when it comes to capital letters or small letters. Spaghetti Over here, I typed in as capital i D. And over here is just the name off. I and Small D could actually type the other keywords as small letters, and it will still work, but it's just good practice to use the keywords asked capital letters, type it out as capital cups just to quickly show. It's easy readable that this Arctic keywords used in your career that wants with the capital letters, okay and order by. Actually, you could also specify a C as ascending order. But for example, if you don't specify a specific ascending or descending, it will sort it by ascending by default. Okay, so if I just run sequel, it's gonna sort it by onto the tree for 5 to 78 could actually changes, swelled first name, and you could see that it gets sort of according to the first name alphabetically. So if you use descending right now, it's gonna start so you could see it's in the reverse order. Now, get the lists off. Nationalities of students. Okay, so let's check again our student list and you could see here the nationality is American, Japanese, French and Canadian. So which means we need to create the less right now that has only the unique nationalities . So there's a new key word again, distinct. And what this will do if it will just return all off the unique values that you specify inside the distinct. So if I run, this right now is just going to turn this four values over here. So for our last example, get the number off each nationality in the student list and return nationalities that have at least two people. So this is actually pretty tricky. Okay, so we're pretty sure it's from the students table. And what do you want right now is the nationality and the count off each nationality. There's actually knew keyword right now called Group by what this will do. Is it true Group or bundle together records off the same nationality because we specify the Nationality column as our group by condition or are grouped by criteria. And what this will do right now is so let's just type us right now on the nationality, and you can now use to count nationality to count the number off records for each pool or for each group. If we run this, it's gonna tell you America and there's two. There's one Canadian. There's tree French people. And then there's two Japanese so he could see here that it was able to count each group. But we're not done yet. We have the condition return nationalities that have at least two people. The first inclination is it's just use where, but it doesn't work that way to be able to, to filter you would need the aggregate function count. And to do that, based on your groupings, you would use the having keyword, and you could now specify count nationality and at least two people. So that would be greater than or equal to two, which means to end above. So if we run this again, so we would expect the Canadian to be removed from here because this only has one count. So if we run this right now, you only have three records which he is the American and French and Japanese. So we're gonna be having more exercises because sometimes it gets confusing on group by and having so that we can reinforce you're learning off. So far and again, we can use acid smell to change the name to make it more readable Number of people around the sequel and we have number people over here. Let's recap the new keywords that you've learned so far. So we have some which is the aggregate function to get the total off the column values that specify average kind of similar with some. The only difference is it averages the values off the column and then we have our critic operators so we have addition, subtraction, multiplication and division. So the four of them over here that you could use on your single crease and then we have limit returns the first X rose. So you just specify a specific number from your limit and an order by its source. The results, according to to specify columns and ascending descending used in conjunction with order by so that you tell it the assorted by ascending or descending order this think it returns the unique values off the specified column little place in and then grouped by its the agreed function to group your results by one armored columns and having is you could think of it as the were condition for goodbye. It's the filtering tradition for your aggregate function, so you could see a while ago we use count for D, having so, which means we're filtering it based on the count. So now that you have all of the key word knowledge, I want to share the logical ordering off this keyword. So we have we start off with from so you specify which tables that you want to get your data from, and then we have the footwear which gets the filtering, which filters out all the records followed by gripped by. If you have a group by groups, the records to get her base in your condition and then having wishes filters out based on the groupings and then select, which gets the columns from the result and then it sort it at thievery, Aunt by the order by and now it's time for practice. So really suggest that you take the time to go over. This exercise is trying to create the Koreas on your own, based on the concepts that you learned so far and will discuss all of them in the next video.
9. More Simple Queries (Solution): Okay, so you've taken time to work on this exercises. And some of them are pretty challenging, actually, and really satisfying once you go over them. So that's jump straight to secret. Like manager. So far. First exercise. What are the last names and count off each last name in the student list? Okay, So pretty sure again from students table, We're after the count off each last name. So which means we need to group the last names to get her and then what we're after. It's the last name and the count off each last name. So we have four ducks. We have one goof, one locked up and two mouses. This one's pretty fun. It's actually the same career as a while ago. What are the last names and count of each last name in the student list? But there's a condition and sure that the list it's ordered from the highest count to the lowest camp. So which means from Amman ago it has to be something like for 211 So how do we do that? So let's reuse the same statement from a long ago, and we just need to add this. We just need to add order by could see over here that, I've added asked. A number of people can actually use the new name. And since we want it from the highest count, let's let's just run this first and see what happens. It's actually one onto four. So it's ascending order from Low. West's the lowest count of the highest count. So we want the opposite. That's just add the keyword descending. We run this again and now we have for 211 get all info after five youngest students. Okay, so this one's pretty, pretty fun Get all involved, which means we don't care. FedEx star from students. Now the question is, what do we mean by youngest? So let's just run this first. So when we say youngest, so we're gonna be basing it on day of Bert. So which means definitely there's some ordering that we need based on the date off, Bert. So if you're on this right now, you could see the records on top actually are the oldest ones. So which means this is not what we need. We need the youngest, so we need the opposite so you could see over here, the youngest. Now it's on top and we want the five youngest. So which means we just want the 1st 5 records. If we run this right now, There you have it. The five younger students next one is what is the average score off All of the exempt taken . So let's check first. It gave us hint select from student enrollments table. So let's have a quick look. Okay, so we have all of the great over here, so which means we just need to get the average off the grades in one go. And now we get 77. So get a list of nationalities that have a letter C and have at least two students. This one's pretty tricky. So let's check it out first from definitely from student again. So get a less off nationalists that have a letter C. So which means nationality. Okay, so right now, I'll just placed our first so that we can review, So we're gonna be using, like, because we want to get the letter C and the same pattern. So which means we don't care if it's in front in the middle, the very end. Okay, so let's just run this first and you can see or hear that American? Yep. Has it? Has a letter, c, French Canadian. Same thing. And you have all of the records here with nationality with the letter C. But the condition right now is and have at least two students. Okay, so which means we need the grouping to group this together by nationality. But a question right now is we have another condition having and the count off. Each nationality has to be at least two students. So which means we'll be using count nationality greater than or equal to two. So which means it's too and above and the list of nationalities over here, what is the result? So if we check here at least two people, so it's gonna be American and French, so which means we want the results to have two records over here. So let's run this now, and there's only two results over here. And just to verify, that's just type in a swell count nationality. If we want to really make sure And there we have it. There's two Americans and three French people over here
10. Joins: Now this is the main part of Learning sequel, which is all about joining different tables together. And this is where it starts taking shape off all the concepts that you learn to get her. And before we start learning about joining us, I just want to show you first the relationship between the student table and the student enrollments table. So on the first part over here, you could see this to this table. And on the second part, that's the student enrolling its table. And you already know the concept off primary key. So this is the unique identifier off each specific girl in the students table, and the student enrollments table actually also has a primary key. Its own primary key. We should student enrollment I d. Over here, but the thing to take note office. It also has a student here, and this is what we call as a foreign key. So the foreign key pretty much references the primary key off another table. So which means the student enrollments table it's related to the students table. True, it's foreign key, which is the student I D. So it gives you over here that it has tree rose Right now that has stood an idea foreign. Which means all of this records is related to student I d one, which is Mickey Mouse in the student table and one constraint ISS for the student. I d. Over here. All of the records over here should exist. The i d should exist in the student I d called him so which means if we try to place a student i d of five over here, it will give us an error because you're saying that you can't do that. It doesn't even exist in the students table. And one of the main questions is, Why do we even butter doing this? It's because it's better designed to do so over here. If you have a look. If, for example, if we try to place everything in here Micky Mouse, Micky Mouse, Micky Mouse over here you would have to pace the same exact information of Mickey Mouse American and the date of birth 1991 to all of the records and the student enrollments table , and you would have repetitive data, which is a significant waste of space. And the cool thing with this oneness. Since we have this relationship, you only need to store the lady over here, and you only have Mickey Mouse information stored just once in the students table. So it's a much cleaner approach and at the same time it's not more flexible once you start right in your curious. So that is the concept off foreign keys in our backup sequel, Night Manager. And there's actually different types of joints. Their self joined cross join, outer join, inner join, but for the purpose of this class will be actually focusing on inner joints, as this is the mostly used join in my personal experience as well. And that's gonna be really helpful to you. Okay, so let's start off with our for tick sample. So the better. Understand the joints. I'll jump straight to an example. So we have get the last name first name Classroom I D and grades of the students. So let's check first what we have in student enrollments that so what we're after right now is definitely we know this to information. It comes from the student table for the names, but the classroom I d and the grades. It will come from the student enrollments table. And the way to relate the student enrollments table to the Sooners table is true. The student i d column over here. Okay, so what we'll do right now? It's students. So you could see here that we're actually giving the students and name off s because once we start getting involved, more tables set up easier to reference or refer to a specific table with a shorter name, he could actually do this. And here's the fun part. Now, now we're gonna be using a new key word called Inner Join and its student enrollments because we want to relate the students table now with the student enrollments table And let's give it a name off S E. And a question right now is what are we using to connect the two tables together? And it's gonna be true. The student i d column? Because it access in both sides. And that's where the foreign key references the primary key off student table. So no, and just typing s dot student i d. Which is referencing the student table. He is equal to S e that student i d what we're saying here iss much all of the records from the student table to the student enrollment table that has the same student I D. So just to see what happens all the strongest first, and you could see right now there's actually five. Mickey's over here. The 1st 12345 1st 5 columns over here from the students table and the next four called him from the student enrollments table. And you could see her that stood a 91 over here. All of them have the Mickey Mouse records now because they have matched the student i d from the first, call them over here and to this Caughtem over here from the student I d. S. And same goes for Donald Duck. So you have all of the greys off Donald Duck now fortify records that cheddar and all the way to the bottom. We have Louis Ducks. Well, it's now joined with the grades for Louis over here. OK, but we're not done yet because our question right now requires the last name first name Classroom I d and the great. So what I'll do right now is we can actually start referencing the table so you could use s stuff. Last name? Yes, that first name. So we want the two columns from the student table classroom. I d would now come from the student enrollment stable. So s e dot class room I D and the grade s e dot craved Let's run this now and now we only have four columns that we need. Let's jump over to the next example. This one gets a bit more interesting because it's exactly the same. The only difference is we don't want the classroom. I d What we want now. Iss the classroom name. The question now is how do we do this? So that's double check First, a specific table that we haven't touched yet. Sadek star from our turntable classrooms. Let's have a quick look. So our classroom, on the other hand, has a classroom i d. And then the class name. This is what we need. So which means the last name in the first name would come from the students table. The classroom name would come from the classrooms table and the grades would come from student enrollments table. So now it's not just one table. It's now treat tables that we need to involve Let's start first with students. So let's just give it a name off s now worse. The connecting point. We want to connect it with student enrollments, so we want to join it first. Let's give it a name. Essie again. So it's up to you on how you want to name it. So it's gonna be related to student I d looking. So let's just run this quickly, okay? Still looking good. And we want to join it now with our classrooms, tables on issues, inner joint again, then classrooms. Let's give it a name off. See, on the question is, how do you want to join the skater? We have the classroom I d in the student enrollments table. So we're going to be getting it from S E debt class room I D. And on the classrooms table. It's gonna be class room. I d read this right now and see what happens. And now you can see the class names are now displaying according to the classroom I d. Specified over here so you can see over here to classroom I d. And the classroom ideas. Well, they're now masters. Well, we're pretty close. And what we need is the last name. So it's gonna be from students. Last name, first name from the students classroom name. So that's gonna be See that class name and the greats. The greats comes from student enrollments and great. It's wrong. This now and there you have it. You have public interaction. Well, Mickey's pretty bad at acting with a score for 60 and all the grades right now of the students are listed here with the specific class name. Okay, now let's start with your exercises. So we have this examples for joints and wanting to take note office. There's a weighted great right now, which you'll be getting the weight from the classrooms table, so let's get on it in the next video.
11. Joins (Solution): Okay, so you've already done the exercises for Joined. So let's jump straight to sequel, Night Manager and sold them one by one. So get the first name last name average of old grades off each student that Simms pretty easy at sea first. So we have first name stink first. What tables are re after? First name, last name. Definitely from the students table. So we need students table. Okay, Average of all the grades off each student. So if it's gonna be average of all of the greed, the great would come from thestreet enrollment stable. So that's join it right now on here. And we're gonna be using the student I d join it with here. Okay, so now we have all of them joined together. But the problem is, how do we get the average off all of the grades off each student? So which means we need to group the great off a student, and then once it gets grouped together, we're going to be averaging it out. So to do that, this is what we will need. You will need a group by to group it by student. There's two ways. Is eatery group it my first name and last name, which means group the grades together of Mickey Mouse. And then we'll averaged it out. Or it could also group it by the student i. D. So a lot will do right now is get the first name last name, and let's use the average to get all of the grades s total. Great. Okay, So which means once we're now grouping it together, get the average off each group for the grades and said we're gonna be setting it asked the Well, it's not actually the total grades. So that changed at that's actually average. Great. So now we have the average grades off each student. The next question is, what is the average score off Donald Duck? So it's actually pretty close to this one because we already getting the average of its student. So what we'll do is just add actually a field drinker here because we just want a specific person so we can actually add here. Where asked that first name, you called Donald and asked that last name equals duck. So which means it's the same exact areas what we did a while ago, but we're just filtering it for Donald Duck Records. So we would expect Donald Duck to show up with the average core of 80.2. Let's so let's to run the sequel. And there you have it. Which class off Mickey Mouse has the highest grade. This one's pretty fun. Give the class name. So Mickey Mouse definitely were getting it from the students table. Highest greed were getting it from the student and role in this table. And we need the class name that's gonna be from the classrooms table. So it's for yourself students. So that's gonna be joining it again with student enrollments. Okay, so same thing we're gonna be using the student i d to put this together and definitely were joining it with the classrooms. Okay, that's for my tea. And one thing you could take, not office. There's the order. It's not exactly important. So even if I place this first, for example, that would work perfectly fine, as long as you have the correct value specified over here. Okay, so the question right now is we're specifying a specific record, so we need the filtering criteria. Where s that first name? It's Mickey and asked that last name equalised Smouse. So we want the highest grade. So what we can do first is let's just list everything out first to have a better idea. So let's go for class, name and the great ISS in the student enrollments table. So let's just run this first. So these are the records of Mickey Mouse and you could see over here that this hardly five records and we want the highest grade. So if it's the highest grade, we need to sort it by e that great if we sort it out and see what happens. Okay, That's the opposite of what we need. We want the highest on top, so we're gonna be sorting it by descending order. So we have the highest great on top right now and the same question it's a while ago. How do we get the highest? Which is the 1st 1? So we're gonna be using the limit keyword and name it one. And now we have public interaction asked High score off Mickey Mouse. But we just need the class name. So let's remove the great And there you have it. And out of curiosity, wonder what Donald Ducks highest class, miss actually acting. Okay, The next question is get the average score for each class show the class and average score . So which means we need the scores, the grades and the class name. So the great would come from student enrollments. Okay. And the class name would come from the classrooms table, and we're gonna be joining it based on classroom I d and classroom I D c. Okay, the average score off each class. So which means we need to group saying classes together. So which means group by we can grip it by seed at cast name, because we want to show the class name. And once we have them group together, So just to have a better look first, that's just common this out. It's common this out. This will, I'll just run this first. So now it just sorted by clatter mighty. So we want to group all off this together. So we're grouping it by a class name, and then we're gonna be averaging out the greats for this one. Okay, so we're grouping at my classroom now and then let's average it by the great and student enrollments. Just give it a better name. Rich, Great. Let's run it. And now you could see the average scores for each class and the highest score. Actually, ISS everybody's pretty good at acting. Okay, So for our last exercise, get the last name first name, class, name and weighted grade off each student when you say waited. Great. So let's just have a quick look at classrooms so you could see over here that each classroom has a weight, which means that acting and making jokes have a bigger weight. It's like 30% of your final score and running pranks is only 15% and the public interaction is 10%. So if you add all this up together, it's actually 100% and we want to get the weighted grade off each student. And to do that, you just multiply the weight and the great of its student. Okay, so last name first name would come from students class name from classrooms. The grade would come from student enrollments and the weight would come from classrooms. Okay, so which means we have all the information we need. So that started up. So definitely students again. Let's join it with student enrollments. Okay, So same thing we're gonna be using the student i d on stood enrollments and joining it began with classrooms. See that, Castro my d with the catcher mighty, uh, student enrollment. The question right now is this is old stuff for us, okay? And we want the class name, and we want the weighted grade. So which means it's a combination off the weight in classroom. Okay, so let's just typing wait multiplied by the great, which is an student enrollments table. And let's give it a more meaningful name. So I'll just wrap this in parentheses and give it a different name off and say, Wait a great So that's run this up. And you could see now this is the output off multiplying the weight and the great for each grade off the student. So we have Mickey Mouse. We have Donald Duck Cheddar
12. Subqueries: Okay, so now we're going to be discussing about sub query is so Sub Korea's is actually another way on alternative way for us to write queries involving multiple tables. And I'll just quickly show you this example over here just to better Reese allies. So we have this example. What is the average score off Donald Duck? Okay, so let's just leave this black first. Definitely. That's coming from student enrollments so I could see that we didn't start first with students. I'm gonna be showing you on how to do a sub quarry. And what we'll do is we're student I d in, and I want to strap this over and I'll be writing a new Cree over here. Select student I d. From students were first name. It was Donald, and last name equals dunk. And this is actually the sub query that we have right now. So just form it. It's a bit better just to make it look better. And there you go. So now we have this inner Cree over here, and it's saying that just returned to student I d off Donald Duck. And then over here in this outer Cree, it's gonna be matching it based on whatever's returned over here. So which means just get the list. Cisco Select Star. If we do that, it's going to return us well, Donald has a student I d have to. So which means all the records return in student enrollments, matches Student 82 which is a Donald duck over here. So it's pretty cool because it's an alternative way for us because we can write this differently using inner joints, which all showing in a short while and the average course. That's just completely question average Create s rage right off Donald Duck. And if we run this there you have it 80.2. So it's actually very similar if I'll just paste it here. This is the statement. If we write it an inner join and you would accomplish the same purpose. So I'll just run this right now and you get 80 point to us well, so we could see here that inner join some crease. You can use both them as you see fit and really depends on your style on which one you're more comfortable with and just go for that one. And there's actually another way off writing sub Praecis. Well, a while ago, What we saw is the sub query that we did just basis again here, most inside the work loss you can actually make a sub Aquarius will behave as, like, another table. So let me just show that to you quickly. So let me just get it again from student enrollments. So same problem. Every scarf dolled up again. Okay, So what we'll do right now is I won't be writing the work costs. I will be using the Inter join, and I'll be wrapping this in parent issues and egistec sack. Same query that we just wrote a long ago. Said ex student I D. Okay, were first name and last name equals to wear matching Donald Duck again. And this one, we can actually give this result a temporary name. So let's just make it a temple. Okay, so this is just my custom name. So which means this table off her salts, it's now named as temp. And now same thing with inner join. We need the on How do we now relate this table temp with student enrollments? So we're gonna be using tempt the student I d in quotes S e That's do then I d. So now, just to show you you can actually refer the temp table or the table contents from your sub Cree over here. Since we're returning student I D. We can access debt and let's run this right now. And you could see here that it's the same result 80.2 and it's pretty cool. It's pretty flexible. So now he could use sub queries to create your own tables of swell on the fly. And you can use that to inter join, and you can also retrieve the results in your select statement. Okay, so let's go again to the exercises. So we're submarines. So it really depends up to you on what method you want to use, whether it be joins, where to be sub creates, it's all up to you.
13. Subqueries (Solution): Okay, so let's now work on and compare our crease for dis exercises. So the 1st 1 is what is the final grade off Donald Duck. So to computer final grade, some all off the wait. Times grade off each class. So, for example, you get the first class of don't the grade multiplied with the weight, get the second cast weight with flight with agreed and then add them up together and so on . Okay, so I'll just paste here. The useful joints that were using at the moment because we were definitely involved. Student enrollments. That's where the greatest past rooms, that's where the weight ISS. And we want only Donald Duck. So let's place a filter First name called Donald and this last name, they call it stuck. Okay, so the question right now is how do we get the final? Great. So definitely the formula for doing that IHS getting the weight and was applying it with the great. But that's not enough, because once we have multiplied them to get her, if we run this up right now, we have the weights off each individual class. So there's five classes of Donald Duck and the missing piece for this one. Yes. We need to add them to get her, which is the sum. And we have this, then the final grade off Malta, and it's just give it a better name. Final grade is 81.55 which is not so bad. Okay, so the next question right now is fairly similar. But the question is, what is the final grade off? All off the students. So we want the first name. We want the last name and the final grade. So this is a but a bit more challenging than the previous question. So right now, we're pretty sure we're not feel during anymore With Donald up, we can re use the previous statement. Okay, so let me just remove this first. So the question first ISS, how do we get the final grade off each student's, Which means we need to group together, degrades according to each student. So this sounds funny there. Let's go for student I d. Because once we group them together, we're pretty sure now that for Mickey Mouse, we have all of the five Tacis group together, and now that we have it grouped, we can actually use the first name of the student and the last name, and then we're gonna be summing it up and the same for minutes a while ago. Wait. And the great. So it means where students some the weight of grades if we run it now. So let's give it a better name again. Final. Great. And there you have it. You have the final grades off each student over here. So our top performer is actually Dacey So far. Last question. Get the average grade off its student for the running and acting classes show the first name last name and average greed off the two classes. Okay, so this is more off just to specific classes that we need. So let's use a sub cree just to show you how it's done. And first of all, we need the students table and then we're gonna be either joining it with the student enrollment stable, okay? And the energon right now it's Let's now use our sub query now for the classrooms. What we're after is only two classes, so we can actually do it this way. Let's leave the select black first. Okay, So definitely were getting it from classrooms, and we're just after two gases. So we're class name. It's in. There's two running and acting. So what we're after over here is the only information that's important to us right now is the classroom I d. So let's turn that. Let's give this table and name so which we call it the STI temp, and we're joining it right now. See Time classroom Mighty because that's what's being returned to our student enrollments table less room I D. And according right now is it's ready Field over here in the sub Cree and we're only returning, running and acting. And the question right now is we want the average grade, which means to be able to do that, we need to pull them together to group them together again. My student, I d. And it's jump straight here. What we need is the first name. Okay, we want the last name and we want the average score off each student. So if you run district now, we now have Mickey Mouse Dhondup and the scores off the two classes, and if we want to double check, it's this really giving us the correct result. I'll just comment out. Let's just removed the group by. Let's just change this two asterisks. And if we run this right now, he could see over here that classroom I d. It's being a turned tree and four tree and for three and four in K for each student. And if we're still skeptical history and for really running and acting. So what I'll do is I'll also return to class name in our sub Khoury. And if I click run since its select star in the outer Cree, it will just return. All the columns have run. This now could see here that the class name is also being shown and all of them are running and acting.
14. More SQL Examples and Exercises!: Thanks a lot for your feedback forward, of course. And one common feedback that I'm getting. IHS Mawr examples for sequel Crease. Okay, so I've heard your feedback and we have more examples over here and more exercises for you to try out. So we have this four questions over here. So we're using the same data set, and we have this questions how? It's the duck family when it comes to running. So I phrased these questions right now in the English sentence so that it's up to you on how to decide on how can you create the tables. Write in the sequel format. OK, then. Next question. ISS wandered averages off the dock. Family ardor, any missing students in any off the classes list of students born in the month off November work on this exercise. I highly suggest for you to pause this video just after this, we're going to be showing you step by step on how we're going to get answers for this four questions. So we're now inside the DB browser and we're gonna be going first with the first question. How is D duck family when it comes to running? So what we're going to do here is we're going to create a report where in we look for the students with the last name off duck. Right, Because it's the duck family. And then we're going to get their scores in the running class. Okay? So that we can assess like our good rd when it comes to running. So it started off with Select King will fill in the columns later and from students. Okay. And then we're going to be doing joints with our student enrollments table, OK, It's name It s s e and then joined this with the student i d. Right. SST connecting columns between the two tables. Let's do another joint, okay? Because we also need the classroom information. Okay, so inner drink test rooms, See? And then we're gonna be linking this or joining this. True, The classroom. Mighty okay with the student enrollments classroom. I D card. Okay, now it's looking good here. We're finished with the joining. So the condition for where? Iss We want the last name right to be duck because it's the duck family and class name right would be running because we want to get their scores from the running tests. Now let's fill up the select. So we have s let's say we want to get the first name and deed last name right from the students table. And we want just to show you that it is indeed the running calf. So seed up class name and in a student enrollments, Of course, we need the great. Okay, so it's looking good. That's have a run and see how it goes. So now we have all the ducks, the running classes over here and then their respective grades. Okay. For the next one, what are the averages? Uh, the duck family. Okay, so it's pretty seeming are in a way, right? The duck family. So we know the drill for this one, but the question is, how do we get the average? Right? So which means we want to get the report. Okay, we're in The last name is Duck, and then we want to get each off their average. So let's work on select right? We have from we're gonna start with students. Okay, Let's do a joint on student enrollments because we just need the great right aan s student i d. Again. That's the common column between the two of them, right With the student enrollments student. I d called him, so Okay, now we have the field during cross, Okay? We're close. Yes. That last name he called Stop. Because we want to get the doc family from here. But the question is, how do we get the average? So we're gonna be grouping this by first name, okay? And last name. Okay. So once we have that grouping together, let's go for to select. So we want to get the first name, right? And then we have the last name, and we're gonna be using average right to get the average off each duck in the dark family . Okay, so SC, that grade. And let's just give it a better name off. Total create. Okay, now it's looking good. Let's run this. And now you have the averages right off each duck in the family. Next question is are dare any missing students in any after classes? Now, when we think about this one, any missing students. So the easiest way for us to do is just get a report wherein it shows the number of students that are enrolled in each class because currently, if we do a quick select right, if we do a quick look on our students, we have eight students, okay? From our students able so you could see over here. And if we check for the number of students off each class, then we would immediately see right. If it's not eight, then we know there are missing students from that test, so we're gonna work on that career. So let's go for select, Okay? And let's go for the enrollees, or the number of students in a class is stored inside the student enrollments table. Okay, so let's have that in a joint, okay? With the classrooms, See? Okay, on. See that clatter? Mighty right. The goal for this one is we want that report to also show the class name. Okay. And for us to have the cast name, we need the classrooms table next. This we want to gripped by. I see Classroom 90. Okay, so we're gonna group this by the classroom now. What is the information that we want to show? Okay, so we have definitely the class name so that we can quickly see which class has amazing students and then we're gonna use count next because we want to count How many students are inside the That's a running class, for example. Thinking. So we have s e that student idea. Let's count that. And let's give this a better name, okay? It's a number of students. Okay, let's run this. And now we have actually perfect. Okay, enrollment? Because all off the classes that we have here have eight students each. The last one is less of students born in the month off November. So we're gonna be a bit creative with this one. Because if we have a look, right, if we have a look from the students table, let's just do another quick quarry over here. You could see you could see the date of birth in this format, right? Your four digits for the year and then dash Month Month. Okay, Dash day day. Okay. For the number of digits. So we're gonna be looking for November so we can use the, like, okay, to be able to create a format that would match the dates in November. So let's go for selek. Okay, so we're gonna get it straight from students Where Okay, date off, Burke. Okay, let me just add the s over here just to keep things consistent and a date of birth. Like, Okay, so how are we gonna do this is we can go for the underscored to match the 1st 4 digits, so I'm typing gets four times. Okay, so which means we don't care what the years okay for underscores followed by a dash came because we know it's Father Bangash always. And then since we're after Damonte of November, we type in 11. Okay? Afterwards, we can just place in a person sign because we don't care what goes after that, but there's many ways that you could do this. So, for example, if you wanted to be something like this a spell, for example, that's perfectly fine, because you just want dash afterwards and then followed by two digits. Okay, so let's go on to start running this. So we just add in the s first name, right? We want to get the last name. And then just to prove that we're getting off the date of births in the month of November, that's just time and date of birth as well. Now let's run this and we get the ones with November. Okay, so that's looking good. Let's try the other format just to show you. Okay, we can do this differently. Just go 4%. So we go run right, we get the same result. Okay, so you could just change this. Let's say for December. Okay? There's not If we try, let's say January. Just go run as well. Okay, there's not. It's trying march. Okay, lets go. May there have it. OK, so you could just try different formats. This is just a couple off the possibilities that you can do with sq.
15. Thank you and Congratulations!: Thank you and congratulations
on completing this course. So I hope that this has
helped you in learning SQL database queries
and sequel basics. So you're also able to leave
a review on this course. So to be able to do that, just click on reviews
over here and you can simply click on the
leaf Review button. So please, please leave an honest review as I would
love to hear your feedback. You can also check out my
profile by clicking on my name, this link over here for more
Microsoft related courses. And once you click on the link, you will be able to see my
profile and you can continue your learning journey
by clicking on any of my top microsoft courses.
Bash (BizTech Matters), Online Teaching Excel Expert
