SQL & PostgreSQL: From beginner to pro | Lukas Vyhnalek | Skillshare

SQL & PostgreSQL: From beginner to pro

Lukas Vyhnalek, Microsoft Employee, Programming Teacher

SQL & PostgreSQL: From beginner to pro

Lukas Vyhnalek, Microsoft Employee, Programming Teacher

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
24 Lessons (1h 51m)
    • 1. Introduction

      1:40
    • 2. 01 Create Table

      4:23
    • 3. 02 Create Table (Primary Key)

      3:16
    • 4. 03 INSERT

      4:03
    • 5. 04 SELECT

      2:15
    • 6. 05 WHERE

      4:32
    • 7. 06LIKE

      6:22
    • 8. 07 LOGICAL CONNECTIVES

      5:30
    • 9. 08 UPDATE

      5:21
    • 10. 09 DELETE and DROP

      3:27
    • 11. 10 SELECT DISTINCT

      3:48
    • 12. 11 Multiple Tables

      4:29
    • 13. 12 Rename Attributes

      3:12
    • 14. 13 Rename Tables

      5:16
    • 15. 14 Order By

      5:42
    • 16. 15 Naming the Result

      5:33
    • 17. 16 UNION INTERSECT

      5:07
    • 18. 17 INNER JOIN

      10:09
    • 19. 18 OUTER JOIN

      5:35
    • 20. 19 GROUP BY, COUNT

      4:09
    • 21. 20 MIN,MAX,AVG

      3:40
    • 22. 21 SOLUTION 1

      4:08
    • 23. 22 SOLUTION 2

      5:51
    • 24. 23 SOLUTION 3

      3:44
  • --
  • 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.

133

Students

3

Projects

About This Class

If you want to learn how to gain insights from data but are too intimidated by databases to know where to start, then this course is for you. This course is designed to help you master SQL & PostgreSQL, one of the most highly in-demand skills in the computer science sector today.  

It doesn't matter who you are, whether you are a business owner, marketer or programmer. This course will teach you everything even if you don't have any previous experience. I try to make this course as engaging as possible. Yes, that’s right, it’s possible to make an engaging course on databases.  

What I will learn?

  • How to create tables

  • How to delete tables

  • How to select data

  • How to restrict data (How to use WHERE)

  • How to use logical connectives (AND, OR)

  • How to order your result

  • How to select data from multiple tables at the same time

  • How to join tables

  • How to use GROUP BY

  • and so much more

Why Learn SQL

SQL is consistently ranked as the most in-demand skill in recent employer surveys, SQL is a fantastic way to increase your income and boost your professional development.

So let’s do this! Enroll today and start learning SQL!

Who is the target audience?

  • Anyone who wants to learn SQL and PostgreSQL

  • Anyone who wants to learn how to create database and how to SELECT, INSERT & DELETE data

  • Developers who are looking to build applications using SQL

What tool (application) do I need?

No tools at all, all you need is a web browser, internet connection and willingness to learn.

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. Introduction: Hello. My name is Luke and I will be your instructor for our discourse. I am a programmer With over five years off experience, I worked for companies like S A P, and I decided to share my knowledge. In this course, you will learn how to query a database using SQL and Basri. SQL. I explained everything you need to know in order to put SQL and possibly SQL on your resume . John, worry if he I have never created any query or if you don't have any database course is beginning friendly. You don't need any database or any program except for your vet browser. I start off by explaining how to create Savo. Then we will insert, select, update and delete data. Then I explain a bit more complicated tools like how to order your result, how to select data from multiple tables, how to join two and more tables together, how to group rose by an attribute and so much more. After Dad, you will practice. I prepared multiple query exercises on which you can test your knowledge. Another thing I want to mention is there. My job does not end by making these video lectures If you have any problems or questions, you can always reach out to me. Mostly I respond within a day, and Dad is pretty much it. Thanks for a time, and I'll see you in the course. 2. 01 Create Table: Well, hello, gas. And welcome to this lecture and this lecture. We will create a new table. So let's get started. Please follow up. Dueling that I added as a resource to this lecture. It should take you up to this page where you make sure that you have in here pose, Agree SQL. And here you can choose whatever you want and in here also. But I used the horizontal layout so that I have the result in here and he killed in here. Cool. So first of all, let's get rid off all of these stuff because we don't need it. And let's creating your table. So in here, when you want to create a new table, simply write, create table and then you want to specify the name of your table so it can be whatever you want. But it should describe waters in the day or so. For example, if I want to create a table, Dad will contain all of my students. I can do that so simple. I can ride like my students and it will create a new table that will be called my students that what table also have well, it have attributes, right? So the the columns in the table and we want to define them in here. So in order to define them simply right parentheses and inside dem You want to ride your attributes or your columns? So, for example, every student will have something called idee. Okay, so I d. And when we define a attributes, we also want to define the the data type off the attribute. Okay, So in order to do that, simply write the name of the attribute and behind it right space and in there, the data type. So in this case, end as a in teacher. So then you can define like, other attributes by writing comma, and only you can write it behind it. But I usually write it on the line under and you can define other attributes. So, for example, ever student have a name, so name like this, and usually a name is represented by a string, right? So in order to write that we simply right of our car, so that basically means a string. And we also need to define the number off characters in these strings so you can write 255 so that is the biggest number. You can also write something like 100. I don't think there's any person with name greater longer than 100 characters, right? But maybe there ist. So you maybe want to write him or in here that in this case, we don't need it. So we also can to find, for example, a h of my student, so h and also h can be a in T shirt, right? But also, H can be only positive, right? It can be only unsigned if you are familiar with the programming. So in order to check for that so that we don't insert a record with negative age, we can do that. So in here we can write check and what does? Well, what What did us? Well, check and parentheses. And inside the sprint theses, we want to define what we want to check. And we want the H to be greater than zero. Right? So whenever we are inserting a record into the stable into does my students able, we will check whether the age of the record is greater than zero. If it is greater, we inserted, everything's fine. But if it is not. We don't insert the record. Okay. So cool. Right now, when you're happy with all of these, you can run it using the F eight key on your keyboard or using this button. And since it has like no records in the table, it just tells that it ran. It was pretty fast, but you can see any result in here, But trust me, the table is created. 3. 02 Create Table (Primary Key): Well, hello, guy. Send Welcome to this lecture and this lecture. We will create another table. So same as before, As when we created the my students stable. We will create a new table, and we will call this one my courses. Okay. Like this and in parentheses. We done right. A attributes. So, for example, the course will have an i d. And it will be once again an interior and bad. I don't want to courses to have the same I d right, because this should be a unique identify air oldie course. So I want all the courses to have, like, unique I d. So in order to make sure that this will happen, we can, right behind this primary key. So that means dis record is unique throughout days. Whole table. Right. So there will be no to courses with same I d. Okay, you Should you Brownlee her determine primary key? It's It's very common. And yes, So also, every course can have a name. So I also right in here, worker and yeah, 100 characters. Then it can have, like, maybe some category of the car. So this should be like the devil man and I t and software or whatever. So yeah, in order to represent something like that I brought in. We want to use work are once again so in here was right where our car and yeah, that's it. Let's say that all of my courses will have Onley these free attributes. Maybe I can put in there something like number of students, number off students. Yeah, Another thing I want to mention is that when I write the name of the attributes, I use a camel case. It's up to use. Some people use, like even the first character big. It's up to you. Some people do like this thing, so number off and then students like this. So it's up to you or the use. But I used the camel case like this, so number of students should be a anti seizure, and we also can check so that the individual is greater than zero right, because it can be also equal to zero. So number off students must be greater or equal to zero because I cannot have, like negative value, negative number of students so cool. Let's great this table. So let's simply pick on the running? Yeah, in here. I'll just commend this thing out and run it And number of students does not exist. Okay, In here s I made a typo so running again and it works so cool. 4. 03 INSERT: Okay. Guess we have created the tables. So right now, let's insert some records into it. So, Al, just un commend this thing and put a semi corn. Okay, so this will kinda mean the line ending the comment ending, right? You probably know some according from programming. Okay, so right now I want to insert some records into, for example, the in my students stable. So, Lazutina, when you want to insert something simply right, insert into, then specify the table you want to insert into. So for example, my students like this, and then you want to specify vote well use. You want to insert so values. And in here we will just insert the rose. Okay, So every row will have will be in parentheses, and we'll have all the attributes that are stated in here. So it will have an i. D. For example, one. Then it will have a name. So when you want to re present a string in SQL, we write it like this. So, for example, Peter, and then it will have an age. So for example, 20. Okay, then, when we want to insert multiple multiple rows at once, we can do it simply by writing comma. And underneath we write another or not a row. So, for example, to in here will be Ava and she will be 20 free. Okay? And once you're happy with dese, you can run it so like this. And you cannot see anything in here because it just inserted bad. Yet it inserted the values about it. It's not shown because we didn't select any values yet. So we will do that later on. So let's also insert something to the my curses stables. So insert into and in your weekend, right d my courses. And in here we can specify the attributes that we are inserting. So, for example, we want to insert name. Then we want to insert number off students. Then we want to insert the I. D and the category. Okay. And then we specified the values that we are inserting, so this skin came out handy. In some cases when he, for example, half some value automatically add it. So usually the I D can be out of incremental did or something like dates. This may be automatic, so you want to add only some attributes. OK, so you specify the attributes he wants add. Or if you want to add the attributes in different order, it's up to and in here you ride the values. So, for example, the name will be my curse. So, for example, sq, our like this, then I want to write numbers off students. For example, 5000. I want to ride the idea of the girls. So one and I want to ride the category. So let's write development like this. Then when I want to add multiple records, I do it quack comma. And in here I write, for example, a Java course where I write like 10,000 students. Then I write to as an I D. And then I write a programming as a category like this. Once I am happy with these, I can simply run it and it drowns correctly. Everything worse is fine, so we have inserted several records into these suits. Ebel's and in the following lectures we will query 5. 04 SELECT: Okay, guys. Hello and welcome to this lecture. In this lecture, we will select data firm the table. So we get to the born where we have a tables and we have some records and in them. Okay, but we still want to see how d tables looks like. Okay, So in order to do that, we can select all well, everything, for example, from the my curses table. So let's do that in order to do that, simply right, Select. Then you want to write everything. So in here will be a star instead off everything then you want to write from, so the you need to tell computer from which table you want to select everything, right? So from my curse is stable and yeah, so let's do that. Let's hit the Iran. And as you can see, there are several records. Well, actually two. And these are these two. So, as you can see it have an I D, which is one ensue. It have a name which is SQL and Java. It have a category which is development and programming and it have a number of students. Great. So when you want to select Onley no name and category, for example. From this, my curses stable. You can do it at simply by writing in here name, then comma and category. Okay, so this basically says select name and category from the records off my curses stable. Okay, so if I run it, as you can see, there's only SQL Java development and programming. So these two attributes. So the number of students and the i d were kind of skipped, and we're not shown as a result of this query. Okay, so that's basically how you can select. And now we can select everything from table how we can select some combs. And yet I can also show you how you can update values. So, for example, 6. 05 WHERE: Well, l gas. And welcome to this lecture and this lecture We will select only summary Kurtz. So basically, we will talk about the restriction. Okay, So usually you don't want to select everything from the table, right? You want to select only some records that met some condition. Okay, so I will show you a how to do that. Basically. So when you want to select a records, Dad met some condition you can right in here. Select Walt attributes you want from what table you want. And in here, you can specify what what record to on. So where the record So where you can specify all you want. So where do you roll? So some attributes in the row can meet some condition. Okay. So, for example, if I want to select from D, my curse is stable. I can select only the courses that have number of students. Greater Dan, I don't know. 7000. Okay, so we have in here. We're on course with 5000 and another with 10. So if I select the girls with number off students, greater den 7000 it should be only the Java course. Okay, so if I run it. Okay? I made a typo number offs too thence It's like this. And if I run it, as you can see, there's only one course the gel occurs and why is dead? Well, when computer evaluates this query, it goes through all the records in this, my cursor stable. Okay? And it picks only the records where the number of students is greater than seven thousands . So it skips this record and takes only this one and then it's elects only the name so Java , Andy category. So programming. And that is basically how you can put the restriction inside. So what it can also do. It's not compared the number of students and it doesn't necessarily have to be greater or equals or smaller inside. You can also use the equal and not equal sad. So, for example, if I want to select a curse where d I d is equal to one, I can do that so I d equals one. So that will be the SQL curse. OK, so if I run in, as you can see in here, is the SQL and it's a development. Cool. So right now, what you also can do is right in here, not equal. So if I want to select an course where the idea is not not equal to one. Okay, so basically, I can write it like this. So it's not the usual and programming you write something like this as a not equal. So in SQL, you write this as a not equal. Okay, so if I run it as you can see, it selected the Java course, which is which have the i d equal to two. If I insert in here another record. So, for example, a C course which will have, like, I know 8000 students and it will have an idea of free. I and I ran it. There should be like two courses. As you can see, there are so Java and C. Why's that? Well, because both of these courses have I d different than one. Okay, in here is to and in here is free. So computer picks these two records, and Dan picks the name and category. And as you can see, this is the result. So cool. Basically, what to take from this lecture is that we can define some condition when we are asking for the data from our table and the condition can be something like equal or is not equal or a smaller than or greater than and something like that. Okay, so, yeah, that's pretty much it for this lecture. If you have any questions, feel free to ask in Al, see next time. 7. 06LIKE: Well, hello, gas. And welcome to this lecture. In the previous lecture, we've talked about the where a restriction. So And as you may notice, I only restrict the columns where devalue us in. Teacher, I didn't talk about the war car right of the about the string attributes. So because when you want to restrict restrict a string, it's a bit different. Okay, So for example, in year when you want to restrict a string, you can ride where name is equal to SQL. So for example, like this name equals and in here SQL So when I run this, it should sell like the only course, right? You shouldn't be surprised by that. I can also use these not equal. So this will select these two courses, but yes. So these two operations are the same as as in the in teacher compared right? Like pretty much every day. The type is equal or not equal right? Every day to die path equal and not e cooperates So another all brand Atis when we want to compare a strings IHS like so in here we write name like and in here we write some string. Ok, so for example, it can be a name like SQL. Okay, well s lq net SQL. And when I run it, it's like d SQL. So that's okay. That's basically a equal to side. But we also can do something like this. So s and then percentage side. So this will select all the courses that name starts with s. This percentage sign basically means that behind the name can be anything, right? Any other sub string and there could be, like, any number off characters. But as long as this is the record, as the row starts with s, we will select it. Okay, So if I run this as you can see, it's the as a duo, right? If I change it to J A developing, the job occurs. And if I select the sea, it'll pick the Sikh or so even a Aziz you can knows Notice discourse, Discourse Name is only see, and there's nothing behind it. But this percentage sign means anything, right? Even a even Nothing. Even nothing. Is anything OK? So make sure to keep this in mind, But how does is useful. Well, for example, we want to select a course with category that ends with a G for some reason. Okay, so in here I want to write where category is like that. There's some string in front and the last character is G. Okay, So, Lazutina, when we want to define a something were I d percent it's side. So there's something and it ends with a G so like this. And if I run it, you should see these two courses, right, Programming, Because these two categories ends with G, this one ends with T. So if I read in here T there should be only the SQL course as against the so cool right now , we also can check for the character in middle, right? So sometimes we want to do that. So in order to do that be right, that there can be something. Then there is the character we want and then dares de something. So this can be handy winning, for example, dealing with some short guts. So maybe you have, like a no buildings and you have some shortcuts for them, and you won't like all the buildings that have like development in the name. So you want the D leather to be in the short gut, maybe something like that. So when you want to do that, he right that there is something. Then there is the Maybe something that's some leather dentists in both of these. So maybe Oh, like this whole. And then there's also something right behind it. So if I run it as you can see all of the categories have a low end it there's 00 and O. So right now, I can also check for a more character. So maybe, like Yogi. Okay, so in the category, it's sent Ogi. Right. Then there's something and something. So if I run this, as you can see, there's only programming. Ogea, Okay? And if I, for example, warmed a m so o m. As you can see, there will be nothing. Okay, Nothing in here. Because even though there's O and M in every like category right in here is so in here is m who he When we specified this, we said that there's so and then and behind it, right? And there's no o and m behind writing here is O p o g. So when we want a oh to be in the in somewhere in the string. And we also want an M to be somewhere in the string we can write in here. D something right? So if I run this, there should be all of them because all of them have bull and then ends. Okay, So om om om. So, yeah, This is basically the like character. You can also add in here d the question mark. So that basically means that there will be a one character. Not not like anything. So in here, there's 8. 07 LOGICAL CONNECTIVES: Well, l guess And welcome to this lecture and this lecture. We will take a look at the end and or condition. Okay, so let's do that simply in here. Where's the wear clothes? We can define multiple conditions. So, for example, we want to select the Rose. Dad have, like, I d greater than one. And then the category that starts with I know the Okay. So we can do that simply out. I will delete this and in your I will change it. So you want to select the I. D. That is greater than zero. So we do it simply I d greater that Oh, I said, wanna write idee greater than one and then we won't also d category starting with AP. Okay. So in here, I'll set and the category start with B. So, like and in here, I all right, be and then percent. It's side like this. Cool. And I'll just put it on eight new line so that you can see it better. And I also need to close this thing. So like this. And when I run it, it's a select. These two courses, right? It does, because the category is the same if I put in here something different. So for example, I D is greater than zero and the number of students is smart and none 9000. So number off students this smaller then 9000. So without the space 9000 and run it, it's selected only D C course. Now why is that? Well, we need both of these conditions to be true. So the record that is in here must have the idee greater than zero. And it also must have the number of students smaller than 9000. So if you look at this, you can see that the Jave occurs have 10,000 off students, so that's greater than 9000. So this is false. So even though the idea is to so this is true, the true and false will result into false. So this where cross will be force and that means this record will not be in the results. OK, I hope does make sense. So But if I changed its end into or so either the I D is greater than one or the number of students is smaller than 9000. So if I run this, they're all of these courses. Why? Well, even though the SQL course have I d equal wanna So this is false. It have the number of students smaller than 9000. So as you can see in here's 5000 So this whole condition, it's true. Okay, so that means we will select the record. And in here, both of these have I d greater than one. So that means this is true. So that means these two will also be in the result, so cool. But, for example, didn't change this so Daddy can see that it actually works. So if the SQL curs have 50,000 students, the it will not be as a result of this query because the number of students is 50,000 and the I d is one. So do I. Hope that makes sense. You can use them in. You can use end and or to define, like much, much more complicated query. So, for example, if I d iss smaller den uh is bigger than one or number of students is smaller than 9000 and then you can define another condition. So maybe end and something something So for example, I know Hey, What do we have in here? We have the Java and D C course. Right. So we can also do a restriction over the name. So, for example, name will be you like a G and something. OK, J in something. So if I run this, it will select only the Java course. Why? Well, both of these Both of these met this condition, right? So it has i d greater than one bad in here is the end. So this and it also must have the name like Jay. So the name must starred with Jay. And that's only dys record. Okay, so you can put all of these into, like, extremely complicated conditions, but yeah, so you can play with that, See if you can figure something out. And if you have any questions, feel free to ask, and they'll soon Next time. 9. 08 UPDATE: Well, l gas and welcome to this lecture. In this lecture, we will update some values in the tables. Okay, so we will take a row that met some condition and we'll update some attributes of the road . So let's do that. Simply, I will just delete this thing the select query, and in here I will write update. So you want to update what table we want to update. So in here, I will just choose the my course is stable and what I want to update. So first of all, I want to define what do I want to set? So I want to set some property in here. Maybe. Let's ride the number of students. So number off students and I want to set it to maybe 11 thousands. So, like this? Okay. And right now, I also Well, let me just would some corn in here around this and actually, I also want to display the result. Okay, So Algiers, right? Select everything from my courses. Okay. So likeness. And what this squared us. Well, this query will update my course is stable and it will set number off students to 11,000. But to all of the records. Okay, so if I run this, it said all of the records have 11,000 students. Okay? So even though in here I write like 50,000 and 1000 when I but this update statement, it sets all of the number of students off all of the records to 11 thousands. Okay, so right now what I can do? Well, usually I don't want to change like blindly some attributes to all of the records, right? I want to select only some of them. So how do I do that? Well, basically, we do it same way as in these select statement. So the select restriction, if you remember, was the wear clothes. So where and in here, we want to define the Rose where that we want to update. OK, so in here, I can ride where? Let's say I d equals two. Okay, So what? This will do? Well, it will update a records in my curses, and it will said the number of students to 11,000. But owner, it was like only the courses where the idea is equal to two. So only d Java course. So this will have a 11,000 in here will be 50,000 and 1000. So let's Rhonda's. And as you can see, the SQL have 50,000. See have 8000 Andy Java have 11,000 Cool. So noticed that the i d doesn't change only the number off students changes If I want to change something else, I can write like a maybe name and I want to set it to know to whatever best. Okay, And if I run this the name is now test so I can set in here. Ours is put an enter so that he can see all of the code so I can set multiple attributes. Right? So I can said the number of students as you can see in here and then where comma I can define another attributed I want to send I want to set so the name and as you can see it's detest And I can even change the i d. So in here I write I d and I can set it to maybe eight. And what will happen? Well, the I d will change. How is that? Because in your you check 40 I d being too. And as you can see there is no record with I d equal to two. Well, that depends on how the computer evaluates this thing. Red when he looks at the fair condition he because he first checks this and then he do he does descending. Okay. So even though this set is before this, where the where kind of in the background you wear happens first. So first is the restriction and then computer sets, the number of students and the I d. Ok, so computer just takes a my curse a stable, and then it checks the i d. And it selects on Lee the records. That's that This true for this kind of condition? Okay, so it selects only this record, and then it says the number of students to 11,000 and then it says the I d to a. So as you can see in the results, it said so cool. That's basically how we can update your values. So your rose in your table and yeah, if you have any questions, feel free to ask, and I will see you next time 10. 09 DELETE and DROP: Well, hell, guys, send welcome to this lecture. In this lecture, we will talk about how to delete something from your tables. So let's do that. Let's just get rid off this stuff. And in here, let's do the leading. So when I want to delete something, I simply write the lead. And then I want to define from what table I want to delete. So delete from my customers. Sorry, my courses. And in here I can specify the condition because when I just write the lead from my curses, it will delete all the all the values, all the roast in my courses. So if I run this as you can see, there's nothing s o that means the select everything from micros, iss, iss, nothing. Okay, so right now, if I define a condition in here so I can say something like where? Okay, where? And I can said the I d to do, for example. So I want to delete on Lee the rose I want to delete from my curses, but only the roads that have idee equal to two. So that means destro will be deleted. But these two well stayed the same. So if I run this as you can see in here, I select everything from the table. And as you can see, there's only I'd want and I'd be free rights of these two courses. Cool. So I can also put in here like ants and stuff like that. So maybe where I d is equal to two and I don't know, number of students. ISS is greater than 10,000. OK, so that means that none off the courses will meet the condition because the Jaffa cars have i d two. But it has a number number of soon. It's equal to 10,000 so we won't delete anything. So if I run it as you can see, all of the records stays the same. So if I put this equal in here, so if it is greater or equal, we delete a dear oh, so cool. That's basically how you can delete from your courses. And nothing I want I want to mention is that you can also delete the whole table. So, as you can see in here, we wrote, create table my courses, you can delete the whole table. So if I, for example, right in here drop and then table. And in here I want to specify the table. So my courses and what these lined us? Well, it just deletes the table. So if I run this, I will get an Aurore, right? And it says relation. My courses does not exist. And that basically means in here. I refer to a table, my courses. But I deleted the table in here. Okay? So it cannot find the table. So this is how you can delete a table? I will just get rid of it. And yeah, So that was pretty much it for days. Lecture. If you have any questions, feel free to ask, and I will sue next time. 11. 10 SELECT DISTINCT: Well, hello and welcome to this lecture and this lecture. I will talk about the select distinct from something. Okay, so let's get into it. Sometimes when you are kind of selecting data, you don't want a multiple multiple rows, right? You don't want the same that data to appear multiple times. So in order to prevent that is simply right, select and in here, distant like this. So select this thing and then you want to write the names of your column. So let's pick a column that have the same that is so in Here is the category. So select listing category like this. And if I run this okay category and if I run this as you can see, there's only development and programming, even though there is no restriction. So nothing like where, but the programming is there just multiple times. And this distinct kind of takes on Lee. Everything only wants. So if I removed is this thing. As you can expect, there will be development, programming and programming, and that makes sense because we select category from my curses. So we go through all of the rows and simply pick d category. OK, but in you. When I read the distinct it takes only the categories. Dad are not equal. Okay, so it takes only the development and programming cool you can distinct over a number. So in here, If I write 10,000 and in here also then 1000. And if I want to select the number off students and get agree, if I run this, it will select only the 10,000 and programming because both of these have 10,000 in here. And both of these have programming. But if I changed a suit like, let's say 5000 in here and will select both of these why? Well, because the distinct Onley, the results so the result that are the same. But in this case, even though they are the same in the category, so they are both programming, the number of students is different, one have 5000 and other have 10,000 so the distinct will kind of will not remove them. OK, so as you can see in here is the 5000. That's the programming, the scores. Then there is the 50,000 developments. So discourse, And then there's the 10,000 programming again. So that's discourse. And why is that? Well, the number of students is different, as I said before, so it can remove Onley. The the records that have that are the same in number of students and in category. Okay, so if I change this back to the 1000 there will be only one record, right? 10,000 and programming. So that's basically how we can use the select distinct. It comes out handy. It's It's like usually used when you want to select. Maybe just some job titles, but it's it's really it's really used. You may want to remember this, so, yeah, if you have any questions, feel free to ask, and I will see you next time. 12. 11 Multiple Tables: Well, hello, guys. And welcome to this lecture. In this lecture, we'll select from two tables at once. So let's get into it. I'll just get well, I'll get rid off the whole line. And in here I will just write Select something. So what we want to select? Well, let's say that we want to select D. We also like for my students and my curses at the same time. So I will select names of both Valdez. Right? So I in here, outside name and in here I would type name again. But how would computer No, dad. I'm referring to name off my student and then the name of my course so he can no debt. So in here, I'll just type my student. So am I. Student, that name. And there's s so my students. And that means that I'm referring to the name attribute off my students stable. Okay. And in here, D name will be my courses. So my course iss done name. So right now, when I select it from the my students and my courses, it will display something. Ok, so this is basically how we can select for my students and my cars, my courses. So if I run this thing as you can see Derris be there and it has matched with SQL Java sea . Then there is Eva and and she is once again matched with SQL Java and C So what? This what this select dust. Well, first of all, it picks a one student. Okay, so from this first table, it picks a student, but only only one row and add matches it with all the rose inside the second table. Ok, so with this row, this row and Destro and it does the select. Okay, so it selects the name off the one row from students. So that means feeder. And then it select the name off my from my courses, but for each course. Okay, so its SQL Java and C Right? So basically what do you see in here? Then it grabs another record for my students. So that means Eva and once again does all of it once again. OK, so yeah, that's basically what does. Thus you can select whatever columns you want. So for example, in here I can select the h then name. So my students studio Din's that name and rice tunes that age and my courses done name right. And if I run this as you can see, there's also the H. But it's not like there should be more records. Nothing like that. It's only matches this with the name off my course, Okay, but there won't be any additional records. So cool right now. What do you might think is that in here we have my students age, but in my courses, there is no age right, so we can just get rid of distinct. So, yeah, you might think that and you might be right, because we only need to specify the same attributes. So if the my students stable, have the attribute, name and my courses have the attribute name, we cannot just refer to a name because the computer wouldn't know which which attributes to pick, right. It wouldn't know whether we are talking about the course or ready. We are talking about the student, so we need to specify it using the dot OK, but if we are referring to attribute that as like only in one table, we don't necessarily have to writing name off the table in front of it because the computer is smart enough to figure it out. So yeah, that's pretty much it for this lecture 13. 12 Rename Attributes: Well, hello, gas. And welcome to this lecture. In this lecture, I will talk about how you can rename the name of your combs. So let's do that. Simply as you can see, we got a select going to commend. And in here is likely name off my student and the name off my course. Bad as the result, we can see name and then once again, name. Okay. So in this particular example, I'm quite sure that this is the name of my student bar. If I would, for example, have a name of a employees and name of a supervisor, I wouldn't I wouldn't be sure which one is which. Right? So we would like to somehow kinda rename the name of an attributes so that we don't have to Same names. Okay, So in order to do that, we can simply do that. Where? Simply so in order to do that and we simply write the name of our attribute, and behind it we write s and then we writing you name. So for example, a student like this, And so that means involved Grab still the name of my student, but after d execution off the select is done, it will rename the attributes and it will call it student. Okay, so then I can also rename the name of my course, and I can say it as course, And if Iran it as you can see in here, it's a student. And in here it's discourse. So pretty cool, right? That's how you can rename this thing. And this will came out handy when we do like joints and stuff like that. So that will be later in the scores that yeah, the skin be definitely useful. So what you can also do is do this thing. So rename the line, but without the S, because the S is actually optional, right? Is just You don't necessarily have to write. He s in there. It just the same thing without d s, Right? So if you only write the actual name of your column than space and the new name of your column, you can do that without es. But I've pretty much always right in here the s because it can. It makes more sense to me if I when I read E when I read E select statement, I can see like my student name will be s student, right? If I have it this way, I can see only my students name. And I think I forget on comma in here. Okay, so I recommend writing the S in here. It's like two characters. It won't kill you. But if you want If you feel like you, you want to You can definitely forget about the s and simply right d new name behind the company. OK, so as you can see, it works just fine. So yeah, that is pretty much it for this lecture. If you have any questions, feel free to ask End. I will sue next time. 14. 13 Rename Tables: Well, hello, guys. And welcome to this lecture. In this lecture, we will talk about how touring name the tables that we are selecting from. And yes, so let's get started. Imagine a case where I want to bear up my courses. And I want to pair the name of my course with another course from another off my courses from the same category. Okay, so how how to do that, right? I want to select Su names from one table. Okay, So in order to do that, we can definitely do that. But we can select from two different tables, right? We know how to do that right now, but I would like to do something like this. My courses, my courses and then my courses. But I would computer know what? What, what that means. Like he cannot know. So remember, dead how we renamed the attributes we can rename the my course is exactly the same way. So in here I can write s see. And in here I can write s c two. So right now, when I want to write some conditions maybe Like where? Something I can refer to the record from first my course and then to the second like record in my course. So remember that as I explained the the selecting from multiple tables, Dad, for each record from the first table, it grabs all the records from the second table. OK, so this works exactly the same. Even Dodi tables are the same. Okay, so it grabs one course, and then it bears up with all of these, including the course that it that it grabs. Okay, so right now, what do you want to do? We want to select the name off de courses and and the name of the second courses. And I want the first and second course to have the same category. Okay, So imagine that D. C represents the first course, and the C two represents the second course. So right now, I can refer to it in the select statement. So instead, off this thing in here, I can refer to it as a see something. See that name? Right? So I'm selecting from the first course, right for from the first table, and I can rename it to let's say first and then I can like the C two. So from the second stable, and I can once again select name and sit and said it as a sicko. Legace. And in here I want the two course to the two courses to be in the same category. Okay, so I want the sea, that category to be equal to see to that category. And you should know how to do that. So simply right, see, that category is equal to see to that category. So what did Square does? Well, it grabs all pairs off courses that are in the same category. So in this case, Java and C and then see and java. So if I run it, it takes a while bar yet and it doesn't Well, it also obviously pairs up the SQL with SQL and then Java with Java. Okay, so if you want to get rid off this stuff if this off this duplication kinda we can set. We can also say that the because this is happening because the i d off off, see, it's equal to i d off see too, right, Because we are matching two of the same courses and we know more that so in here I can simply right and see that I d is not equal to see to that I d Okay, So dead will match Onley Java and C and C and job. So that is pretty cool. In my opinion, it might be a bit complicated on the 1st 1st try. So maybe watch this video again. What we are doing, basically is we are grabbing from same table, right? But we are grabbing one record and then second right, And we are matching all of these. So we grab this row and match it with this role, this role and this row. Then we grab this row and match it with this road, this road and this road. Okay. And then we are kind of doing some reduction off the rose. So where something is true? And then we picked the name off the first throw and the name of the second row. Okay, so I hope this makes sense. And yeah, if you have any questions, feel free to ask End. I will soon. Next time 15. 14 Order By: Well. Hello, guys. Send welcome to this lecture. In this lecture we will talk about how to order the result. So let me just get rid of distinct and yes, So sometimes we want to, for example, select all of my courses. But I want them to be ordered by a number of students, for example. Okay, So if I right now, right, select everything from my courses, coarsest cannot write and I run it. As you can see, there is the number of students is like, not sorted, right? There's 50,000 and 5 10,000 And I wanted to be sorted. I want 5000. 10,000. 50,000. So, in order to do that, what I can do is right in here. Order by. So that means it will be ordered by some attribute that is, in my courses stable. So in this case, you on the number of students, right? So, Al, just copy and paste it. And if I run it now, it is order. Right. So in here, as you can see, 5000 than thin. 10,000 and 50,000. So that's pretty cool, right? But so that means it automatically swords by number or students and increasing order. Right, So from the smallest to the biggest. But if I wanted in descending order, I can simply right behind it this and then run it. And as you can see, it's from the biggest to the smallest. So you it's like 50 50. Sometimes you want in descending order. Sometimes you want in decreasing in increasing order. So from the smallest to biggest bad it's important to know how to do both. Okay, so this will soar them from biggest to smallest and without d descending in here, it will be sorted from smallest to biggest coup. So right now what we can do? Well, we can sorted by multiple rows. So, for example, if I write in the number of students and then I d it'll soared by multiple rows. But in this case, as you can see, there are, ah, how all of these have a different number of students, right? So this basically is redundant. It doesn't take place. In any case, This the idea is important only when the number of students off to rose is the same. Right? So let me change it right now as you can see the SQL course this last with 50,000 students . So if I writing here 10,000 it will have same number of students as see. So let's see what happens. Well, the SQL course iss in front of see Why is that? Because the number off students this same So as you can see, it's same in here, and the computer looks at the I d. And as you can see, the idee off SQL course is one and the i d off C course is free. So that means it will grab the it will grab the SQL course and put it in front off C course . Okay. And as you can see it thus, so in here is the I d one and I d free. If I change this record suit and 1000 again, Ed will be sorted by i d So want to free 12 free? Okay, but if I change it to five, it sorts first by number off soon's and den by I D. Okay, so what you can do is also right in here in the descending order. So, like this. And if I run it as you can see, the Java IHS 5000 right? The java is first. And why is that? Because the number of students assorted an increasing order. So that means from smallest to biggest. Okay, but the i d assorted from biggest to smallest. Okay, so this might be a bit complicated that hopefully can keep up it first. Swords by number of students When the number of students is same, it looks at i d and picks the bigger run. OK, so as you can see in here the number of students this same So D. C and SQL course have same number of students. But the idea off the sea course is greater than the i d off SQL course. So since this is sorted in the sin in descending order, the C course, it's in front of the SQL course. Okay, you can right in here. Descending to so dead means the If I ran it, the java will be last. Okay, but this will still be the same because it is just the same as before about yes so deads how you can sort how you can order by something by Sun column You can order even by like string comb So maybe by name, right? It will look at the characters and see which one is the It's in front of the 2nd 1 in in, like, alphabet. Okay, so, yeah, that s pretty cool, I think. And if you have any questions, feel free to ask, and I will see you next time. 16. 15 Naming the Result: Well, Hogue, I send Welcome to this lecture and this lecture. I will show you how you can basically create a new table, Almost like creating your table. You're not actually creating a new table. You're just naming a result off some query and then you can refer to it as if it was a table. OK, so let me just delete this thing and show you how it is done. So if I want to do that, I write with then the name off the result off the query, right? So the name that I want to refer to So maybe like, let's say that I want to select Onley best selling courses. Okay, so in here I ride best selling s and then I want to ride the name. So the sorry, the query. Right. So in here I write some query that will select only the best selling courses from my courses. So let's say that I want to select everything from my courses like this where the number of students IHS greater Dan 7000. Okay, so let's say that this will select the best selling courses. Okay, so right now what I can do is refer to the best selling as a new table because the result off this query will be, we'll be Bind it to this best selling. Okay, so right now I can, for example, select a Let's also come combined the students and the best selling courses. Okay, So for example, as ride a best selling dot name and then I want to refer to the student names. So it's my students. I think that name and I can refer to it as a student. Okay. And in here I write from, and as you can see, I use the best selling in the select. Okay, so in here I can write best selling, and this will work. And I can also right in here. My students, okay. And, yeah, So I close it by using the semi corn. Remember, there is no semi coin after the sprint theses. Right? And if I run this, it selects de SQL and matches it with Peter and Eva, and then it's like d C course and matches with with Peter and Eva. So let me just talk once again about what is what What happened? Okay, so first of all, we itself computer debt. We want the this name to be as a new table, right? It's not a actual table, but as a new table that will be will have the records from this is a from the result of this query. Okay, so this query select everything from my courses from this stable where the number of students is greater than 7000 and noticed that I don't have a semi coin in here. I only have it in parentheses. Okay, so it's elects all of the courses with number of students greater than 7000. So that means this SQL course and this C course, Okay. And it binds these two record as a new table to the best selling. Okay. And right now, I can refer to the result off the square e using the best selling keyword. Okay, so in here I can select the name of the best selling course and a name off my student, and then I can write from best selling. So not my courses. And from my students. Obviously, this thing could have been done differently. Where I would just put this where cross behind it behind here and using here. My courses but I want to demonstrate the usage of a naming a result. Okay, so I hope you understand. And yes, so let me just show you, Dad, I cannot refer to this best selling after disc amends. So if I select everything from best selling that's selling like this at the computer would would not execute this because it doesn't know what best selling. It's right. It's a relation. Bestselling does not exist, and that makes sense because there's bestselling only exist within this with and the semi corn. Okay, I can refer to its own in this statement. It it won't create a new table right or anything like that. It's just like basically a We are just naming a local variable. Okay, so, yeah, this might take some time to proceed, but yeah, if you have any questions, feel feel free to ask and I will see you next time 17. 16 UNION INTERSECT: Well, Hello, Gash. Send Well, countered his lecture in this lecture, we will talk about the union and intersect. So let's get started. I will simply just get rid of this stuff in here. And I will also create a new table. So it will be Al just coffee basted because it will be pretty much the same as my courses because it will be a best selling courses. Okay, So like this and it will have the same attributes. Everything stays the same. Only this time the we will use the table too. Going to do some intersection and union. Okay, so in here, where's the insert? Into my courses. I will changing my courses to that selling courses. And in here I will get rid off this job, of course, and only keep desu. Okay, so if I run this, everything works just fine. Cool. So right now what I want to do is union unite my courses and the best sending courses. Okay, so out. It works well, I just write, select everything from my courses and then I d union. So basically united with the best selling courses. So in here I write select everything from best selling courses. Best A. He's selling courses. Skin It's Bill. So then I write Semeka in and around this thing. So what It does? Well, we just selected courses, But in here they are the same courses, right? So if I, for example, changed this to C plus plus and run this again in here, you can see there's a CPAs bus record. So by default, you can see that it basically it's like union. Distinct, right it get rid off the it gets rid off the like, multiple, multiple entries. Okay, so, yeah, you don't write to you distinct behind union, right? It's automatically there. But you can also do union all Dad will pick even the same entries. So as you can see in here, it picked the same entries in here and in here. Right? One SQL development 10,001 SQL Development and 1000. So that's what this old us it kind of picks everything, even the duplicates. Okay, But without this four, there won't be this record. Okay, there's no second SQL, so yeah, let's get back to my point what this union does. Well, it picks all the records from this first stable And then it picks all the records from the second query basically from the second table and puts them together, right? It's kind of adds them together. So there's not nothing too complicated about this one. It just grabs everything and puts it together is a one big result. Okay, so what then Intersection does If I write in here, intersect and run this thing? As you can see, there's only the SQL. So why is that? Well, what the Intersect does, it's it 60 all of the all the things from the my courses. And then it picks all of the all of the records from best selling courses. And it fixed only the records that are the same. Okay, so only the duplicated. So if the result off this query must be in this table and also in this table, OK, so as you can see, there's only one record that match it right. You cannot see any java in here. You cannot see any see in here. So if I removed a c bus bus, there will be also the second record, right? As you can see, there s so remember it must be same on every attribute. So if there is, like 10,000 and one, there won't be any C course. Okay, so yeah, that's what Intersect does. So union brings everything together. You can select also union. All that will also include the duplication, right? D duplication queer, the rose that are the same. And you can also do intersect That will basically pick the rose that are the same from the to results. Okay, so from this result and dis result Okay, I hope that makes sense. So if you have any questions, feel free to ask in. I will see you next time. 18. 17 INNER JOIN: Well, hello, guests, And welcome to this lecture. And there's like, Sure, we'll talk about D join. So let's get started. Simply go to your editor, your online editor, and get rid off this stuff in here. We will create a new table. So right now we have a my students in my courses stable. But I would like to know which student enrolled in which course Okay, so I can definitely do that in here. I will simply write create table So I will create a new table called Enrolled and indistinct able. I will have a student which will be just an I d off this student. So it will be just an in teacher and it will be same number as this I d off my student. Okay? Because it would be like an efficient to have all of these informations multiple times in multiple tables. Right? So we will kind of refer from this table to the my students table where the I d. Ok, and another attribute it will have is the course, right, Because we want to know. And which course the student enrolled and which attributes will be the primary key. Well, student can enrolled in multiple courses. Right? So the student won't be a primary key and course Well, we have. We can have multiple courses being well. We can have the same courses being enrolled two and multiple times. Right? Because to students can enroll in the same course. Right? That makes sense. So the course, it's also not a primary key. But can we have a student enrolled in the same course? Multiple time? Well, we cannot write. So this is the other way. We can define primary key simply right. Primary key. And then in parentheses we write all the attributes. OK, So for example, in here it would be student and course like this and what it means. Well, there can be there can There can be same students and there can be same courses, but there can be zeros with same students and same courses. Okay, so for example student, one can enroll in course to and student Guan can enroll in course free. Okay, That's okay. That student one cannot enroll in course to and then once again student wanna can cannot enroll in a course to write so it cannot enroll in same course two times. I hope that makes sense. So, yeah, let's lose up right now. Let's also insert some values into the stable. So insert into the in the role table and we want to insert some values. So in here, the student will be just a ni de. So in here we have one and two, right. So we will refer to D. Peter as one and sooty Eva is too. And the course is once again only the idea of the course. Okay, so we have 12 free and let's bury it up. So imagine that Peter enrolled in the SQL course, then beat there. Also enrolled in the job. Of course, I think it's too. And then, for example, Eva enrolled in the sea course, and they've also enrolled in the SQL Okay, like this and seven coins in here. So this will create a new tables. Well, new table called enrolled right now what we want to do. Well, D task. Well, I will kind of talk of Audi. Join will be to select a name off my student and match it with the name off the course he enrolled in. Okay, so this is a bit complicated task because we are not selecting only from my students stable and we are not selecting only from my course is stable. But we are selecting from mind students because we said select the name of the student then from my course is because Because we need just like the name of the course. And then we also select from the enrolled table because we need to match these together, right? We don't want suggest puts a random student and random course together. We want the where the student enrolled. Okay, so we need to kind of join the stables together and how join works. Well, basically, in year, As I said, we have the idea of student Okay, so that means distinct. And as join works the way dead, if I can kind of connect these same attributes, OK, so if the student in here we'll have the same value as the I d. In here, I can connect the roast. Okay. The rows of desu tables will be connected to get a I hope that makes sense. I will show you an example. You think I think you will understand it better there. So as I said, before we want to match name of my student with name off the course he enrolled in. So it will be just select. And right now, let's refer to a student s and to the course. Sn. Okay, so the s name that means students name and the course name. That means see name. And I can also rename it a scores. And in here I can rename it a student. Okay. Like this. And I want to select it from the enrolled table. Okay, so right now you might say Well, wait. In the enrolled there is no name. There's nothing like that. Well, yeah, there's no So you you are right, bar. We will join the enroll table with the my students stable and d my course is stable. And how do we do that? Well, in here we can simply right in their joint. And right now we want to specify the table that we are joining with. Okay, so in this case, my students and right now we can also rename. It s right. So we are referring to the my students table as s in here. And right now, Computer also doesn't know which road to kind of connect, right? So as I as I said before, it puts the rows together. Right? So if there is one in here, it will match it with one in here. Okay, so we want the student attribute from enrolled, and then we want the idea attributes from my students to be equal. Right? So we just write it in here so the S i d will be equal to enrolled. Dad. Student. Okay, so this kind of thing will connect the students. So this whole, you can imagine it this way. So imagine that this whole thing well, the kinder will replace this one in here. Okay, so the 2nd 1 will still be there. The course attributes will still be there. But this whole thing will can replace this one in here. Right? Sing with two. So this Eva, this Eva record will replace replays. The two in here. Okay, so let's now connect the courses. Okay. So, Britain, that same is as before. We simply right in there join So that will join the two tables together. And we want to join the My course is stable, and I want to also change. It s C and in here I made a mistake. There should be like my students s s. And right now I also need to define on so on which attribute this will be joined. Okay, so sorry about it. And in here I to find the two attributes that should be equal. Okay, so like this and same thing in here. So my courses as C So I will refer to the my courses stable SC. And right now I want to join the C i. D. So the idea of the course that we have in here with the enrolled that course so with the course Dad, the student enrolled in OK, so as you can see the second attribute And if I run this as you can see, it's s Pizer enrolled in SQL Peter also enrolled in Java. Eva enrolled in C and Eva enrolled in SQL. So, as you can see, it matches these rights. So the one its SQL and as you can see in the my courses, its SQL So you can imagine it like that. We are connecting tables together on some attribute that is being equal. Okay, So if something is equal to something. We will connect the rows together. OK, so the different roles from different tables. But we were going to create a new table that will connect these rows. So yeah, this is basically how d join works Howdy in their joint works. So if you have any questions, feel free to ask and I will see you next time. 19. 18 OUTER JOIN: Well, hello, gas. Send Well countered his lecture and his lecture. I will talk about the outer joy. So we've talked about the inner join in previous lecture bar. Imagine Dad in here. We have an I D. Off students. Right. So in here we have the student. And imagine that in here we have an idea of student that is not in my students table. So, for example, free and we match it with one. Okay. And also you can imagine that in here we have end idea of student Derris in the table. But in here we have the i d off course that is not in the stable. So with I d for example four. So right now what will happen? As I said before, we are matching this I d with the ideas in here. So when there is free, it will be no match. Right, because in here there's no free. So what computer do? Well, it just gets rid of the Gesserit off this record. Okay, so get rid of this row and counting. Use on right. And dad's happens when we used the inner joy. Okay, So when we use in their joint it will get rid of this record. And it will also get rid off this record. So if I run this as you can see, the result didn't change at all. Okay, bad. What if What if we want this? We want even the records that are not matched it. In some cases, we want that. Well, in that case, we will use the outer joy. So instead of the inner joy in here we write full outer join. And what did us? Well, as I said before, we are joining the enroll table with the my students stable. Okay, so that means on the enrolled students. So that means the first 1st 1st attribute. Okay, so that means in here we have the free and it will not be matched with any other with any other student, right? With any other i d off student. So since we are using full outer join, it will include even this even this row. Right? So what happens? Well, the computer cannot match this, so it will say that it is a now, okay, and it will just move on. But it will include it in the results. So right now, since we are matching the since the A student can be now, right, So this will also be in the table. But in here we have the idee off course and the courses in their joints. So that means the last record won't be in the result. So if if I ran this, you should see five records, as you can see. And the 5th 1 is this one. And as you can see in here is now And data is because this free didn't match any record in my students. Bad It is still in the table because in here we used the full outer join because the full outer join will contain even the records that were not joined. Right? So if if this is not true well, if it cannot find this record, it will just say OK, I didn't find it bar, Since it is out of joint, I if I want even this record So it said it as now because it didn't know what name, right? It didn't. It cannot know a name of a unknown record, right? So it will set it as in now, but it will still continue with the other line So right now, as you can see, it matches the SQL. So even though this was now, it still continues with execution and matches this one as a SQL. Okay, bad. This record is not in the result, because even though it matches the student right, one s Peter, It didn't match de course, So the course I d iss, There's no course with I'd equal to four, right? So it cannot match any record with it and it will get rid of it because it's a inner joy. So that's the difference between full outer joint and energy. If I change this two full outer join and if I run and there will be even the six record Okay, as you can see in here so that says speeder, that means this one okay, as you can see and this four is now because there is no course that matches the I d off four. So this is basically how the outer joint works. You can Basically, the outer joint is just a inner joy. But it also includes the the roast that we're not joined. Okay, So D rose where d where this was not met. So that means we cannot find the enrolled students or d s. I d off the of the student. Right? So we cannot match the euro about Yeah, that's pretty much full outer Join. If you have any questions, feel free to ask and I will soon Ex im. 20. 19 GROUP BY, COUNT: Well, hello, guys. Send well counted his lecture and this lecture. We will talk about the count. So let's get started. Simply delete this thing and let's talk about he counts. So, for example, imagine that sometimes I don't want a name of a student match with the name of a course, but I want. And how many courses that students enrolled it. So imagine that I am here. Have a Peter and I would like to know in in how many courses he enrolled. So in this case, it would be free courses, right? And then Eva in world in two courses and so on, so on. So sometimes you want to know d Count off something, right? The count off the courses and stuff like that. So you can do that. You can. You can query a count off a something in our case courses, and you can do it. Britain. It's in simple way so simple. Right? Select. And yeah, we we can do the thing that I explain so and how many courses that student enrolled it. Okay, so, first of all, I want to select the student and then I want to select d count off de course, right? So, student and the count off the course, right? And I'll close it. And I want to select it from De in their old bod right now. It won't work. So if I run this, it won't work. As you can see, there's arrow that why is that? It tells it right here. You need to have the group by Klaus. Okay, So Well, what is that? Well, will group the record by some attributes. So imagine that all the attributes with same values. So imagine that if the student is well, all the records, all the rows with one as a student will be kind of grouped together, Okay. And then we can refer to the group as a as a one record with the student equal to what? And then we can check for the account of the course is OK, so group by does exactly in the name it groups D rose the in in the by some attribute that is the same. So if I write in here group by and right now, I want to define the the the road that I want to group in by So in this case, student. Okay, So grew by student. You can even select multiple rows. But we will get to it in the later in this course. The if ice, like the student, the computer will group all the records where the student attribute is the same. And then it can query the count off the courses for each student, right for each group. Okay, so if I run this, as you can see, it says that student one have free courses. So in your one to and then free. Okay, So it doesn't matter that this four isn't a real course, because we are not matching it yet. So it's It's OK. So as you can see, it counts the number of records for every student. Okay, so, Dennis, pretty cool. So grew by, basically puts the same records together, and we can then refer to the group. Okay, so, yeah, that is pretty much it for this lecture. If you have any questions, feel free to ask and I will see you next time. 21. 20 MIN,MAX,AVG: Well, hello, gas. And welcome to this lecture. In this lecture, we will talk about the men Max and Everett. So let's get started. Simply delete this thing. And for the purpose of the men Maxim average, we will use the my courses table. So sometimes we want the For example. In here we have a categories, right? So we want the course with the maximum number of students for each category. Right? So we want the maximum number of students in which category. So let's do that simply right. Select. Then we want to select the category. Right? So we don't just like the maximum off the number of students bird category. Okay. And in here we write Mex and we passing their the number off the authority. The name off attribute that we want to beg the max value. Right? So in here, we right number off students. So we want to pick the maximum off number of students for each category. So we also need to specify the name of the day. Bo's so for my courses. And we also need to specify the group by right. So we want to groovy categories together. Okay, So in here we write groove by and get agree. You don't necessarily have to put the parentheses. And there you can just put it like this. And if you run this as you can see the max value off off both of these is 10,000. And why is that? Well, in here we have 10,000 bird development. And as you can see its own, it's the only record. So it it will be the maximum value. And in here there are two rows for programming category one have 5000 and the 2nd 1 have 10,000. And as you can see, the 10,000 is bigger. So it s picked as a maximum value. Same thing ISS the minimum. So if we were right in here, the minimum well, big the 5040 programming grow. So if I run this, it says 5000. Why is that? While we grouped this both this to records as a one, then we refer to the programming category, and then we picked the minimum off these two values. Okay, And that is just 5000 and another. The last thing that we can do is the ever it so sometimes you're going to know, like the average salary, the average bryce of something, the average, whatever. Average number of students may be so in here. We can simply write the A V G as a average, and it will pick for us the average. So if I run this, it should say 7.5 1000. And in Taos, right vehicles, 10,000 plus 5000 is 15,000 divided by two and add a 7.5 1000. So these free kind of things the max men an average as quite often used. It's It's quite often because you're simple, like there. There are a lot of cases where you need, like, the average value off something right, So you better remember that. And that is pretty much it for days. Like if you have any questions, feel free to ask end. I will see you next time 22. 21 SOLUTION 1: Well, hold gas and welcome to this lecture and this lecture. We will take a look at the first problem. So let's get started. I created the table course detail which will have the i d. Which is deep primer key. Because we don't want to details for the same course. Right. And there will also be a price 40 course. Okay. And I inserted there some values and the desk is supik, every course sorted by number of students and then also big the price of the course and also order advi ity. Of course, Bryce. Okay, so let's do it. In order to solved this problem, we first need to figure out what we are picking. Well, we are picking the name of the course, right. So in here I will refer to the course a C and in here we want to pick the name off the course. Then we want to pick the number of students or see Dad. Number of students and den. We're going to big from D detail, so I'll just refer to it as D every once a big, deep price. Okay. Like this. So how to do that? Well, first of all what we are picking while we are picking the course. So in your I'll just right from my courses. Okay. Like this. But we also want to join the stable with this table to get to these prices. Right? So we'll join. And where the i d and we will join it with this table. Why are the I d? Okay, so in here were I d in there, Jordan, because we want some matches. Right? So we ride the inner Joran Dandy course detail so coarse detail as the and in here it will be as see. Okay, so I forgot about it. So we will rename the my courses table A C and the course detail as the so we will refer to the C and D then okay. And we want to join these two tables on the D I D. Well, the c i d. Being equal to D I d. Okay, because the course detail idee is this one and the my courses, which is the sea ideas. This one. So we want to match these ideas with these prices, okay. And then we can pick the price. So that is pretty cool, right? Now we have all the records, but we still want to order it. So in here I write order by and I won't order it by see that number of students. So I want to order these courses by number off students. And then if two courses have same number of students, I want to order it by price. So the price that I want the price and the number of students to be in descending order ride. So in here I write descending orders because I want the best selling courses to be first. But that's pretty much it. So if I run this as you can see, 1st 1 is the SQL course, which is 10,000 students. 2nd 1 is the C course, which is 10,000 students. So that's fine, right? We first compared the number of students and then if the number of students is equal, we pick the higher price. So in this case, the SQL courses first see course second and jave a casus last. So yeah, this is basically how you could sell the first problem. If you have any questions, feel free to ask and I will see you next time 23. 22 SOLUTION 2: Well, hello, guys. Send welcome to this lecture. This lecture will be a faster one, because the desk iss pretty simple, but yeah, let's do it anyway. So in here, we want to select the programming courses, right? So for my courses, we want to select the courses that d where the category is a go to programming. So let's do that simply in here type. Select the course name from the my courses like this. So we are selecting the name off the course, and we want to put a restriction in there. So that means where the category is equal to programming. So let's do it where the category is equal to programming well in string. And it should be like this program Ming like this. So if I run this, there should be two courses and they are the Java and D C course. So as again, see Java and C. So that is correct. Right now. Let's Judy second task. So that should be select all the courses. Names where the where the number of students is greater than 7.5 1000. Okay, so let's do that. Select the name of the course from my courses where the number of students IHS greater than 7.5 1000. So if I run this as you can see, there are these two courses which have 10,000 number of students. Cool. So right now let's do the 3rd 1 This will become a bit more interesting. Select D name and the number of students from my courses where the number of students is greater than 7000 and d category starts with B. So let's do it. We want to select the name and the number of students. So we will do it like this, right? Remember the come up between the attributes that we want to select number of students and we want to select it from the my course is stable like this. And right now we need to do the restriction. So, as I said, where the number of students is greater than that, then 7000. So number off students and I will just put it on second line is greater than 7000 and the the category started to be so in here. We all right? And so that means both of these must be true in order for the road to be in the result. So nd category is like B and then anything. OK, so if I run this as you can see, there's only the C course. Why is that? Well, even though the SQL cores have the number of students greater than 10,000 the category is development, right? So the seekers have 10,000 and programming. So that means this is correct. Cool. Now you want to select the students where the age is greater than 20 and the idea is greater than one. So, for example, we can do it like this. So select the name or students from my student, where D I d is greater than one stand the age I believe it's called. Where is that age? Right? So the H is greater than 20 and H is greater than 20 like this, and you can put it in parentheses. It's up to you, but it will work. So as you can see, it's only anybody. So Eva is H 20 free, and the idea is greater than two. Right now. Let's do the 2nd 1 So select the number. The name of the students where the H is greater than or equal to 20 or the I d is greater than wanna. So let's do it. Select the name from students where the I D is graded and one more the age is greater than or equal to 20 like this. And I forgot about semi coins. And if I run this as you can see, they're both of these students. Why's that? Well, even though Peter have I d equal to one, there is the or and the age off. Peter is equal to 20 so d or is true. And that means this whole restriction is true. And that means speeder is in the result. So cool. This is pretty much it for this lecture. If you have any questions, feel free to ask and I will see you next time. 24. 23 SOLUTION 3: Well, hello, guys. Send welcome to this lecture in this lecture idol. Salty problem from the article. Okay, so let's get into it. We want to select the courses from my courses where d category is equal to the category of the Java course. Okay, so to the programming that we cannot write the programming directly in there. We need to know. We don't imagine that we don't know the category off. Jave Accor's we only know dead. We want to select the category of Jabba course. Okay, so let's do it simple in here. We need to select the name from Well, let's put it just like this from my courses. But right now, we first need to compute the category of the Java course because, as I said before, we don't know that it is programming and then we need to find d courses. Dad have the same category. So what would be nice in this case? Well, we would like to have, like, two tables, right? And in the first table, finding Jave a course in the category and then in the second table, find all the courses where d category is the same s in the first table, right? So we would like to select from two tables and we can do that. Even Dodi tables are the same. Right? So in here Well said the my courses as the Java course, And he also we want to select from my courses as the other courses. So let's just right course in here. And you want to select the name of the course, right? Of course. That name. And right now what we need to do Well, we need to boot the restriction in here. Right? So we need to figure out the Java course category, right? We don't know that it is programming, because if we know that we just put select from my courses where the category is equal to programming, right? So that would be really simple, but we don't know that. So we need to pick the my course is where the name is equal to Java. And then we need to compare the categories off these off the second course. Right? So the Javan course category must be equal to the category of the course. So let's do that De Java, Dad, Name must be equal to Java. Okay, That way We are sure that in the Java we have the Java course and right now we can compare the Java category to the course category and if they are equal, we just put them into yourself. OK, so in here you want to write and the Java category must be equal to the course category. Okay, so that should work. Right now we are picking the courses that have the same category as the Java course. So if I put a semi going in here and I run this as you can see in here, we have the Java and the sea course. Okay, so and a year where already courses in here we have the Java, which is programming and see which is programming. So pretty cool. And then it's pretty much it. So that's how you solved the problem. If you have any questions, feel free to ask end. I will soon, Next time