MySQL Database Development Mastery | Trevoir Williams | Skillshare

MySQL Database Development Mastery

Trevoir Williams, Jamaican Software Engineer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
25 Lessons (4h 19m)
    • 1. Introduction to MySQL Server and Databases

      6:33
    • 2. Download and Install MySQL Server and MySQL Workbench

      12:23
    • 3. Connect and Create a Database

      12:50
    • 4. Drop or Remove Database

      3:40
    • 5. Create an SQL Database Table

      22:59
    • 6. Insert Data into the Table with SQL Script

      8:49
    • 7. Insert Data into the Table with Workbench

      9:04
    • 8. Select Data from the Table with SQL Script

      8:09
    • 9. Select Data with Filters

      19:53
    • 10. Update Data in the Table

      8:33
    • 11. Delete Data from the Table

      10:20
    • 12. Reverse Engineer Database into Model

      6:54
    • 13. Forward Engineer Data Model into Database

      11:44
    • 14. What are Relationships, Foreign Keys and Normalization

      10:49
    • 15. Create Relationships with Data Modelling

      15:28
    • 16. Create Relationships with Workbench Table Design Tool

      20:24
    • 17. Insert Records in Related Tables

      8:55
    • 18. Run Queries on Related Tables (Inner Joins)

      15:21
    • 19. Left, Right and Cross Joins

      7:07
    • 20. Grouping Data using SQL GROUP BY Clause

      9:52
    • 21. SQL AVG Aggregate Function

      9:00
    • 22. SQL COUNT Aggregate Function

      7:44
    • 23. SQL MIN & MAX Aggregate Functions

      2:53
    • 24. SQL SUM Aggregate Function

      5:18
    • 25. Splitting Groups using HAVING Clause

      4:33

About This Class

This is a zero to hero course on MySQL Database Server and MySQL Workbench. There are no pre-requisites for this course. If you are looking to get acquainted with the concept of Databases and Queries then this is the right course for you.

MySQL is the world’s most popular open source database. With its proven performance, reliability, and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter and YouTube.

We will explore best practices in database development using MySQL Database Server and will be building a small school management system database.

All you need is a windows machine and we will walk through setting up an environment, to creating a database, creating your first table and writing queries.

At the end of this course, you should be comfortable writing queries for multiple situations and reports.

What you’ll learn

  • Install MySQL Server

  • Install MySQL Workbench

  • Connect to an Instance

  • Create a database

  • Reverse Engineer Database into Data Models

  • Forward Engineer Data Models into Databases

  • Create Tables

  • Run Queries against Tables

  • General use of the MySQL Workbench

  • Create Relationships

  • Use Aggregate functions to do quick mathematical operations

  • Export data to Excel using MySQL Workbench

  • Practical use of SQL

Are there any course requirements or prerequisites?

  • A Windows PC (or Windows running on a Virtual Machine if using Linux/Mac)

Who this course is for:

  • This course is for beginners with absolutely no experience is database.

  • This course is for you if you are already comfortable with basic database operations.

  • This course is for you if you feel a bit 'rusty' and need to refresh your knowledge.

  • This course is for you if you want to understand design principles in database development.

  • This course is for you if you are looking to learn a new Database Management System

Who this course is for:

  • Beginner Database Administrators
  • Database Developers who want to go to the next level
  • Those who work in organizations where the company typically uses MySQL databases.

Transcripts

1. Introduction to MySQL Server and Databases: you guys. Welcome to database development and design for everyone. This is trouble Williams talking to you on. I'll be here to guide you through each step as we go through the world off database developments. Now just spare me about five minutes as we get started. We'll be exploring some off the jargon. So the keywords used indeed to be his development on. We'll just have an underlying foundation on knowledge, but this course is going to be completely practical. So I'm not going to bore you guys with much theory. The first thing we want to take a look at is data on what it is now. Data is any fat related to an object, and object can be a person within the context. On the data related to the personal artifacts related to the person could be their name, their age, their dates of birth, stuff like that. All of those things are called Vita on DNA databases, essentially systematic collection off these data points are these facts related to the objects on databases within the context off my SQL or other similar engines will help us to store and manipulate this data on make management off it easy. No, my SQL is what you call a day to be a server or relational database management system on essentially a database management system. Mallows users toe access manipulate on represent the data in whatever form they wish on my school servers actually free for development. There is an enterprise edition. What's for our purposes and for the purposes of this passage is free. Now, whenever you talk about the database on most times a relational database, you see the three letters sq and L. It is in my SQL Microsoft SQL Post Rescue will ah, lot off them. Oracle SQL. They'll have this that thes three letters SQL or sequel, as some people call it. So sq a Liz really standard language across most relational databases for manipulating and retrieving data on it is short for a structured query language us on. If you look at the examples I have online here, you see in all caps those are the keywords on in lower caps. Those would be more user defined. So the customers, for instance, in the first line. So let's start from customers. Select means I want to bring about the records star or the Astors really means all from. And then we have the table name customers. So we have all data from the customer's table coming back. And the customer, Steve is really storing all of the data points relating toe. Anyone? Customer? No, I just alluded to ah, table called customers, so we'll just look at what, exactly a table is on. Basically, a database consists off one or more tables. Table is usually created for each entity or objects that you want to track. The fax or data points on. On that they are. Table is made up off rose and columns. Just think of an Excel spreadsheet. You have columns with headings, and then you ever rose and eat. Sell Conte in some value corresponding toe the column off ahead, and our role would represent one hold record that is pretty emotionally out off any database table in a relational database where the rules contains data, points on the columns basically specified, a data type on what type of leader should be stored. It is always this practice toe one named the table very uniquely according to what it is you're storing data on sold the I would just saw we had a table specifically for customers who don't expect to see anything but customer information or data in that table on also, each customer or row or record in this customer stable should be uniquely identifiable on this unique identifier is called a primary T. So for every table we create will create the records using a primary key. Now, very quickly we look at what a relationship is. Lets us go back to the example of customers on Let's See Amazon needs to track all its customers on also all of the orders that these customers make. No, the orders will be stored in a table called orders on the customers in a table called Customers. So our relationship exists between the customer on Day one or many orders on. Essentially, we need a mechanism to be ableto link on orderto a customer because our order can't exist without a customer. So that is essentially what our relationship I laws us to do where we have two distinctly different data sets on, we're linking them, showing that they are related to each other. No, Whenever we talk about relationships, you also talk about foreign keys and a foreign key is essentially that link that is used for two facility it our relationship. So the example this time is about teachers. All teachers and the teachers table are uniquely identifiable by a primary key on. Then we have a classes table to say that, Okay, a class exists because this subject is being taught at this time in this room by this teacher. So we need a way to track the teacher that is associated with the class. So instead of trying toe duplicate this teachers information for every class that this teacher might have, we import that unique identifier from the teacher table into the classes table for each class record on. Then once we see that this foreign t right, So you have the teach i d in the teacher stable, and we import that value of its other classes table. So it's called a foreign key. And once you see that this foreign key much is a primary key in the teacher stable, then we know that there is a relationship. So I'm not going to go into too much theory here with you guys. I just want you to have an appreciation off whole databases work in the grand scheme off Things on did throw the rest of this course, we will get into far more detail on. Like I said, it's far more practical than this. That's it for boring you with theory and pull point sides. Let's get right into the meat of the monster. The next video, we will be installing Micros, my SQL Server on the war bench. 2. Download and Install MySQL Server and MySQL Workbench: Hey, guys, in this lesson will be walking through all the steps required to download and install my SQL Server and park bench. So we start off this journey by going to www my school dot com on. Then you can do the reading. We see that they have an Enterprise Edition and some code services. That's all good. But then we have to pay for most of what is advertised here, so we want to go to downloads on. Then we want to look for the Community edition, which is free for developers. So we click that and then we continue with my school community server, and then we can do a bit of bigotry. Bit off reading on the community edition, which is really downloadable on it is kept active by on open source, developer and enthusiast community. So we see that one. It's open source. You can get it. You can modify it if you are there. Developer. If not, well, that's fine, but it's always free for the developers and into just like ourselves. We can stroll down and then we just go to the MSR installer, which is the bootstrap installer, and we was gonna download page, and then we can select the Web installer. So we see we have two options 1 20 megabytes and 1 373 so we can just go ahead with the Web installer. Since it's smaller and easier to don't load on, then the next page would be asking you if you want toe, create Annacone toe are signing to your phones. We can skip that. That's optional. We can just go straight to our don't load. Once that don't load is completed, we can launch that fire, launch our installer and then it will ask. You probably will ask you if you want to upgrade, he can just see yes and continue. The next screen you'll see is our license agreement. I always advise you to read it through on. Once you've really turned, you accept and he can just go ahead and say, except license in terms. Think next. You can leave this on developer default, and if you think you want more power than just the default, you can always go toe full. But I will leave this UN developer default as this will have the server, which is the whole database engine that will be driving my SQL functionality on it also includes my skill workbench on then. In addition to that, it's also as a bunch off plug ins that may or may not be necessary for your context. So for the purposes of this course, we can just click next. No note if you may have had it installer trapped inside before you may get this path conflicts. I had it installed before, so I'm getting a path country terrible. You may not get it, so if you don't get it, and that's fine. But I just show you just in case and other split next on die, just accept that warning and go ahead on. Then, in this screen, you'll be prompted to install certain plug ins so some of them are manual like visual studio. Excellent Pathan connectors. You can click on any claim can click check, but for the purpose of the scores, we don't need any off these in installations right now, so I can just go ahead and click next and skip over this on. Then it will warn me that the requirements may not have been fulfilled, that just continue on then. Here we see the most important things, which are server, war, binge and other imports on pro gains needed for our engine to run. So I can just go ahead and click Execute, which will trigger the download now, use you me and counter these exclamation signs and you don't panic. You can just go ahead and click draggin sometimes it feels, but then sometimes the dependence is don't load faster than the second is Go ahead and keep on clicking, dragging for as many times. Almost every time I install this, I get a different experience. So your experience may be a bit different from mine, but I'll just go ahead and keep on ticking. Try again until everything has been downloaded and installed successfully. In some instances, you mean encounter problems were no matter how much you press, try again. It just won't proceed, and I'm actually experiencing that. So if you're having this experience also, then I'm going to try and alternative method. If you're not, then you can go to the next step and just wait until I catch up. But then, if you are one method that are seen, work is don't loading the offline installer so remember, we don't know that the Web installer for 20 megabytes and we can try the community installer, which is a much bigger fire, which actually has everything pre downloaded on the don't. There's nothing to don't Lord and feel too Don't load. So already don't know that this and I'm going to run that installer and try to catch up to where we are. So I'm just going to cancer this quickly on open this file. So if you had already started the process than you would see that maybe one or two off you're things would have been Donald it already. Either way, if you get this screen, then you can go ahead and click add so that we can finish adding the software. So we want definitely that my school services will just drew right doughnuts. Let that green arrow we definitely want or my SQL were eventually grilled on an clicthat on . Then we can get our rotor, all right, and then you can look through. You can actually go ahead and install them all if you want, so I actually just go through and bring over all off them because I don't want to leave any stone unturned, so I just go ahead and do that. Bring over the connectors for C plus plus on dot net, and these are really just connection libraries. I leave the python on out of it on Die can bring over my SQL documentation if I wish. So I have a native reference on the computer on Bring Over samples and examples, which will bring for some sample data bases. When we finish our installation on After doing all of that, you can just double check and make sure that your list at the very least has the SQL Server , the workbench on the old BBC connectors. And then we can just go ahead and click next, and we pretty much just go through all of the steps that we just went through on this time , they're ready to install because there's nothing to download, so just click execute on, then it will trigger that installation process. All right, so whether you use the offline installer or the Web installer at the end off the process, you should be seeing all green ticks alongside each of the options that you selected on. Then, once you have that and you're sure that everything has been installed and you can go ahead and click next on. Then they will start asking us to configure what we have. So the first thing to be configured is our server. No, we can just leave this on the standalone. My SQL Server cluster is essentially when you have more than one databases that need to be aware off each other. In this course, we won't be doing anything that complex so we can just leave it a standalone server and click next on. Then we can change our settings. The dedicated computer server computer development computer in this situation, development computer is best because I'm sure this is your personal machine with other non database related applications. So we don't have to make it that dedicated my SQL database server so we can leave it on developer development computer on. Also, we don't have to change that poor. This is the default port for my SQL. So, in an enterprise sitting, there are times when people change the port numbers for security reasons. But for our own machine, we don't have to make that change on so we can leave everything as is on the screen. Click next on. Then, for the authentication method, I recommend using the legacy authentication method because then you retain some backwards compatibility in case you're going to be creating certain applications are installing applications on top of this. Not every application can use this authentication method that is with the new version. So I recommend using the legacy one so that you have that backwards compatibility and then we can go ahead and click next. And then I already had an instance installed. But at this point, they would ask you for a user password for your route. User, I'm going to use route. So the user name is routes and the posture it is root. Andi, I suggest to you something that you can remember on here. I'm chicken the pastor, and clearly this is not the car. It's password. Okay, false alarm. So we see here. The tiki came up when I did a check. So once again, your screen me look differently. You may be asked to enter the root password and confirm it on below that you would be prompted to possibly add other users. But for no weekend this continent with roots Andi I see again that I used a very easy password. It's my personal machine, so I don't have to be very secure off course. If you're doing this for an enterprise sitting than you want to be very secure and in keeping with the password policy off your organization. But then we can go ahead and continue. Once we have entered that password on, then we'll see here that is just confirming that it will configure a service on set it to start up automatically. So this service will essentially lost the comic to the instance. If you on take this, then you would have to manually start the service every time. So I just recommend you leave the stick and you don't have to change anything here. And he just next on then or final step for configuring our server is to execute so that he can just carry out these final things at the end of this execution. Then we should see Audrey antics on. Then we can click finish on, then we can just go ahead and configure the rotor. We don't have to change anything here because we're not using the road to the rotor. Would before or clusters are eventually that was optional on. Then we can just click next to go ahead and install over samples and examples. And once again you will be prompted to use that user root on that credential a pastor that we provided we can click check just to make sure that it connects successfully once again, use a password that you can remember. If you didn't on this was not successful, then he can drop me a line. Whether by message or que Andi Andi, I will help you troubleshoot that on. Then we go ahead and click next and execute once more. And once that is done, we can click finish now essentially the most important parts off this installation where the server and workbench. But then ultimately it's nice to have other things. So when we click next, we'll see that we have the option to start my SQL workbench after set up so we can just need a tick and like, finished on. Then you may see the install come up with all of what was installed. He can close this so those are all of the things that were installed. And from here we can reconfigure, as in change the configuration, setting the port etcetera if we want, we can also choose toe, modify or add more or remove any previously installed one. All right. So as new versions come out, we have the install of which helps us manage or were versions, and we can upgrade that will. Ultimately, our goal was to see that our work bench was installed on from here. The next video will be to connect on, start running some commands. 3. Connect and Create a Database: Hey guys, In this video, we'll be looking at connecting to a database instance for the my SQL Server on also creating or first databases now post installation. We would be greeted with the my SQL work. Been screen. If not, then you can always go to the start menu, go down to em on, then under my SQL, you should be able tow identify my skill were Bench CE. The version number may differ based on when you're doing this course. However, if you do not see, then you can always go back to the installer. And trying at that feature retroactively. If you need any assistance, is drop me a line, whether by message or Q A or comments on that would be sure to assist you in that regard. Now, prior toe the existence off a graphical user interface, wheat interact with my SQL. We would have had the command line client, which is actually what I learned on on. If we just briefly go over to the command line clients, then we'll see that were greeted with them. Inter Posser, you don't see this warning this This might be unique to me, but the fact is that you should see a problem to enter the pastors. He can enter that passer that you would have entered through the installation process, which will help you gain access toe the server. So this is a easiest way to connect to the server. You open up the command line plans you put in the pasture than bomb, you're connected. And then, if we wanted to see certain things about the server, light the databases that are there than we have, the key words show on the phrase theater. BC's. So this command on most other commands you end with a semicolon in my school and press enter shows us all of the databases that are in over my SQL server. Right now, you may have more or less, depending on a few install. The samples are not, but the fact is, there certain key ones that are more like system databases that I recommended on Tamper with. I'll see might need to reinstall the entire server, so we're just going to do some cursor commands. Firstly, before you can execute, execute any commands on a database, you have to let the and eternal, whether it's the common line or Not that you wish to use a particular database going forward, so we have to type in the keyword use. And once again, I'm using all caps for all the SQL keywords on. I'm going to use the table Secura. So then my issue lets us know that the database in use has changed Secura on. Then I want to see all the table someone to say show once again on the key word. This time it's tables on. Then I press enter and then it will list all off The tables currently contained in Secura aren't so. I want to create my own database. So to do that, I would just go ahead and say Create It's obvious on then specify the name. So I'm going to create a database called School on their score TV press semi colon on. Then we will see that the quarry okay, Wandera affected. But then there's still no way for me to actually verify that, at least not automatically. So I have to run another command and by using the open don't buttons on the keyboard, I can actually cycle through all the previously run commands. So I'm bringing back the show databases command, and I'm pressing. Enter on there. You see that my newly created databases no counted among the many. So that is so. The command line interface can be used. Interact with your my SQL engine. But then you'll find that when you're trying to read through data, it's not very user friendly. And if you write a certain script or command or set of commands that you wish to say for later, it's not really very easy to do that with this interface. And so they gave us my skill workbench, which allows us to interact with our database engine on data in a more user friendly manner . No, If we wanted to connect or data bees, we see that we have a card here that says local instances this on day Give us, ah, user on. They're letting us know that we're connecting to local host on port 3306 Local host is the universally acceptable term for this computer. Meaning whatever computer you're on, right? No, that is your local host. All right, on, if it was a case where the my school server that we wished to connect to was on another computer then we would have to create a new connection. So we do that by clicking that little plus sign right here was to bring up a dialog box on . Then we could just say connection. Name on. Let's say I wanted to punish a school server. All right, then, host name here by default is 1 to $7 0 does here not one, which is what local host results. So you can either use the word local host or you can use this I p address if you want to refer to the computer that you're currently on. But if it is that you want at another machine, then you could use that machines name. Are that machines I p address on the network in order to connect to it. Also, you want to make sure that the port is correct because sometimes they may change the port number during installation. If you recall, we had the opportunity to do so. But we retain the default port so it presents the default court. But just confirm, with your need to be said many streets or what port it is that you're connecting to, and then you would have a user name that was probably assigned it to your user. So you want to confirm that the using in that year getting to use the facility in this connection is part on then the past, or do you just think store involved? And then you would enter that Posser someone to test connection? Andi, I know this is going to fail, but I wanted to see exactly what it looks like when he fails. So I put in everything by there, and it's will let you know that it failed. Your access is denied. However, if you use the correct information, then you would see a successful connection being made. All right, so then after annoying that you have a successful connection and if successfully set up this connection, they can click OK, and you see that this card gets added to your my esc you a workbench greeting screen? No, this is important because in a setting, maybe enterprise sitting where you're a DB and you have many servers that you need to add many streets. Then you can just add as many connections as you need and you will form this kind of card here on the screen. that you can just click on, get access to that, set off databases on that particular server or instance almost immediately. So for moving on, I'm just going to reuse our default connection card that came with our installation on That's just going to jump me right and you may be prompted for a password. And if you are, then just enter that password and you can up to store it in the vault and you can get that dirt access like I just did. No. On this screen, you be greeted with a quarry editor text editor, and you may have a toolbar to your right about SQL additions so you can actually drag this old or in or you can just use the dock on under undock. It's stand up two bars to the right, to the left on below this editor, but the editor means pretty much solid. But you can close this editor using the little X as it stubbed. And if you do that back since I need a new file, then you can descript this. Ask your file toe, bring up a new and it's our screen to the left off this editor. You see that there's a list ofall the scheme as or databases and in database lingo, you see schema on database being used interchangeably. So to the left, you see a list off all of them currently available to you on. You see school DB, which we actually just created in our command line interface. Just know. And if we drill down into we see that it is empty. There's no nothing, is there. All right, so I'm actually just going to rewrite that command here, create database on going to say school underscore DB one this time, all right. And we ended with a semi colon. So we can actually anything that we just wrote in over SQL command line, We can actually be right here in our editor, and then I can just use this lightning bolt. Execute on. Once I do that, we'll see allowed down here, showing us what the status off our command is. And this is saying that it was successful with that green tick. However, to the left, we don't see it listed, so we can always just refresh on that will update our tea or database listing accordingly. So on one side, you have to run a command each time to say show databases on this side, we just leak, refresh on it will adjust accordingly. So here we just looked at two different interfaces that we could use toe execute a create database script. All right, so I'm actually going to proceed through this course using school underscore db so I'll actually remove DB one eventually. But we just see if this script as if we were creating school db. So like I said, an advantage to using this graphical user interfaces that we can actually Seymour commands for later use. So, in the command line, we couldn't do that with 80 cute and percenter and then say goodbye. But in this one, we can actually just proceed to see of the file, save descriptive fire by using this save icon here and then we give it a name. So I'm just going to name it creates school DB and click Save already have a fire by that names, understands, overwrite it with this. That's fine on. Then we see that the script name is actually represented here. No, I'm actually went to add a comment which is represented by two hyphens on that comment is essentially dead text, meaning whatever you type. After the two hyphens and space, make sure you put the space because if you don't put the space that's going to cause an era , right so two hyphens and a space on that I can say this command is creating a database. All right, just get my spinning. Correct. All right, so that's one liter. It comment another way is by using the slash and asters aster skin star. So essentially, I'm starting the comment with slash on masters on them, ending it with an asterisk and slash on essentially anything I type, no matter how many lines see that will be ignored by the When we press the lining ball to execute, these won't be executed, so you have to always look at it. Is it a comment or does double blue dot because for each common that your right, you will see that blue dot appear on, you know that this line is about to be executed once you press that lining boards. So I mean changes here. I deceive Andi. So you see that as you continue to develop on your script, you can actually save your changes and those will be reflected and it becomes reusable overtime. All right, so this is essentially hole you about creating a database on Do you can choose either approach. But then, since we'll be working with workbench going forward, I to circumvent to use the workbench approach on this is all you do. It's using a script I have included. The script in The resource is for this video so you can review it if needs be on. When we get back here, we look at another way that my skill, where bench allows us to create tables on databases. 4. Drop or Remove Database: Hey, guys. So in our last listen, we looked at how you go about creating a database, using there to be a script sort or SQL commands rather on. In this video, we will be looking at whole. We go about dropping or removing a database. So in the previous video, we would have looked at two ways to create databases using our command line interface on our the SQL script. And so we ended up with two versions off school on the score db school under the score, D B and D B one. So in this video, you want to remove one off them. So we look at the command used to remove our drop a database that we no longer want. So I'm just going to go ahead and create a new script file. I don't over at this end to do that once again, you just sleep that s girl file over to the left with the plus sign on. That gives us a fresh script file which is capable of taking first scripts, and we can see this as another file. So that is one of the key advantages to using workbench when we want to execute many queries. So with a new file, we can actually go ahead and start writing out over keywords someone to write in lower caps this time once again, it's still is not case sensitive, so I can see a drop data beasts. So those are the keywords dropped? Meaning remove on. Please note. There is no undue with this step, there is no recycle. Being on recovered from doing this command is rather tedious. So I advise you, please be very deliberate. When you're about to execute this command, drop Dita bees and then the database Name on. As I start typing, you see the masculine suggesting the databases that I'm you be interested in. Sai Wan school db one second is selected. Press enter annual auto fill on. Did you just ended with a semi colon? Before I execute this, I'm going to say once again be very, very, very, very deliberate. When executing this, there is no undo. It won't promise you and ask if your short all right. So I typed this and then I click execute and then you see that you will see that it was executed on defy refresh over in my scheme us. Then there is no school db one toe be used again. All right, so once again, be very deliberate before you run this command are. And if I tried to execute it again, then we get our first arrow on. You will see, can drop a database because it does not exist. All right, So off course, If you get that arrow and you're looking at the data base and you probably have a spelling arrow on either side, he can just double check that. But once again, that is how you remove a database using an issue, a command. So I can just add a comment, and I'm going to use my multiply and comments in this one on. Say, this is how you remove a eight of these. Be very, very deliberate. All right, so I'm going to include this sample script file and I'll just say it quickly. Andi, call it drop school db one. All right. And that is going to be included with the resource is for this video on this lesser 5. Create an SQL Database Table: Hey guys, In this video, we will be looking at quite a few concepts, namely, creating tables using SQL scripts on also the fact that we can have multiple mons in one file on how we can execute them all versus executing them in parts. Now the first thing that we want to do having a new script file on the desire toe start adding something toe database. In this case, the school db is toe set the context for hours file meaning, said the database that we know we want to be using on. We looked at that before on the key word is used on. Then we specify the database that we know we want to use. So I say use school db and it with a semi colon on. I'm just putting some space in between. Andi, I could just add comments up top, but leave the comments for what I'm seeing in the file on. Then, after I specify used DB than I can go ahead and start filling all the rest off the quarry file. So please notes that SQL commands run in a very linear fashion meaning order monitors. You have to see this before you say anything else on whatever order you put the things that happen in that is exactly the order it will be executed in. All right, So the first thing that we want to do is create a table, so to create a table. It's a similar bit off court, we say creates the object type, which is table, and then we give it a name. And in this case, I want to create a table called Students for my school date of this. Then I will just open and close brackets on in with a cynical and so I like to open and close brackets at the same time, because that way I don't open and forget to close. All right, so I prefer to do it that way. Also, you notice that ah, command can span multiple lines. Are command does not end until it sees a semi colon. So once a cynical I mean this present that night knows that that it is the end off the commands. Even if I take off 20 lines with this command, you see off that as one command. So I'm just spacing it out this week so that we can see exactly what we're about to type. You know, the very first thing that we including any table at least for me it's best practice to do it this way is the I d. Remember, in the beginning of this course, we discuss what you call the primary key, which alos each row to be uniquely identifiable. So in your country may have a tax I d. On that alos the authorities to know exactly who you are just by having the tax I d. Well, in our database we want to have something similar to that for each student's. So the simplest way to do this is one the i. D. The name I d story, which is the name of the column. So the first thing you specifies the column name on then we say data type on the data types can be integer. They can be, um, Vier char That can be DEET. So in other words, they can be new miracle. They can be often numeric or just letters. Our string, so to speak on day can be date. They have a number quite a few off data types. When were writing the script you have to actually remember the data types. But I will include all off the pot possible data types in this lecture handing another video to see ho My SQL workbench helps us to keep track off these data types and which one we want to use. But we go ahead and we see I d on. Then the data type we want is in because or ideas no one to be counting each time a record goes in. Andi, think about it. If each time a record was in, that number goes up by one, then no two records will ever have the same number because they will be numbered from one toe and number off records. So what we do on do you see as, ah, common pattern is just to specify a column called Idee Set it to be an integer on. Then you would say something like auto on the score increments. So this means that each time of value goes in, this column will automatically increments and it's going to start up once the first record will have the value one. The second record will have the value to on for as many records as you have this number will go up even if you delete, say, 15 records and elite rule number five If you add 1/16 record well, at that point, it would be the 15th record because you just removed one. But if you add another record after record 15 then the number will still go to 16. All right, so this number will always increment. No matter ho. Many records preceded. So that way it will never have the same number as a proceeding record. So we say auto increment on. Then what happens is that in specifying the columns that this table should have, we have the name, the data type, and then what we call attributes. All right, so this is the 1st 1 side is complete. This so we have I d integer value autor increments on. Then I also wanted to be the primary key said this right primary key. So that's another attribute that I'm giving it on. Then if I am finished with the attributes again, this press comma on go to the next line on, then the next thing I want for the student is the last name. So that's a column name on notice. Color coding is giving me what is a key word versus what is a column name. All right, then. Ah, last name would be more like a string. It's a word. So we wouldn't have indigenous, not numeric. So the next data type would be. And if you just start typing, you see that it's giving. They're giving you all off the potential values that could be used for data types here. So the most commonly used data ties for string beast buddies are var char onda invites are so at any point, if you know that in this column or any data points associated with this column, you should be storing a word or an alphanumeric sequence. And I suggest that you use on environ char or of our chars. I'll just use environ char on set this to 50. So by doing that, I'm sitting that this last name column, whatever valuables in should have no more than 50 characters are 50 spaces or whatever it is a mix up. The string that is about to go in here should be no more than 55. Go on and count the number of letters in this last name right on another attribute that I'm going to add is not know. So this will specify that this last name should never be empty. That's what not know sits. So any problem that you put not in all on will never be empty, so we could have put non know up here. But the fact that we put other increment means that it's always going to be counting, so it won't have time to ever think about being empty. All right, so it could be placed appear at no harm. But for me, it's just redundant on the other other just separate them seek. And I appreciate why auto increment we works the way it works on not know, specifies otherwise for any other column. All right, so another column. We want his first name, I notice no spaces should be ever put in the column name, so I just did that out of instinct for my typing. But it's a good thing I did that because then you see that this is giving me an arrow, and it's cording both of these as as keywords when they should never be keywords. So if it's a case where you need SPS than you want to use. The I think that's apostrophe or an accent on that is not much is not this. That's not a quotation mark. That's a quotation marks different from the apostrophe. All right, so take notes off the difference between the two on the keyboard. So the quotation marks. The single court is not what we want here. We want that apostrophe on. Then that will say anything that is in between. These two should be seen as a column name. No. For me, it's best practice never to use a space. So if it is that you want to not have it, as you know, last name being crunched together because reelection they are towards then I suggest to use an underscore on. Then that way you can have a cleaner column name on design going forward. So I just don't underscored the last name. First name on. I can just copy these attributes from last name, since they're going to be common columns on note. Each time I'm going to a new column definition I in the line with a comma, so I d d the type or attributes Harmer Last name data type all attributes on, then comma. And then, as always, there's our comma ending. Then you'll see that there's an error. Here's but we're not finished with TBA as yet as we're going to put indeed off birth, and I'll just use underscores so that we can see each word specifically so off course, going forward in your design, you probably have your pattern and your own style. I'm just making suggestions and this practices as I go along, but ultimately you'll develop your own style of doing things Onda. We would use a seat theater value for this, and we could see and not know. So no student should ever go into the database without date of birth, a last name or first thing. That's essentially what all those not knows me on. Then any student would have been enrolled at some point so I can see enrollment date once again, I'm going to use my underscore. Andi, This should also be a date, but I'm not going to meet this not know, because maybe you would have signed up, but you haven't enrolled in any courses as yet or whatever, So I'm just showing you that you can specify some columns as not no meaning. They are very much required for any role to be input on. Then you have those without the not know on notice. There is no trailing comma at the end off this statement, so that would have created our students table. Now, in addition to the students table, I just go ahead and create another table for courses. Onda pretty much is very similar procedure. We just open and those are burning to seize on There we go ahead and fill in all the columns. So once again I d on Ah, unique Identify Roar. Primary key for me is best practice. Always have one on your tables on in this one. I use the pattern where I just said I d Sometimes you see, people actually specify the table name on the word I d Andi, right? It also you see like that sometimes. But once again, it's all about off personal style and preferring someone to leave it like this. You can see the two ways on then each course should also have a title. So remember that a table is a collection of data points or end nowhere, actually defining all of those data points that would create factual information about whatever entity it is that the stable will be storing those spots on so courses. What is it that, of course, has of course, as an I d. Of course, as a title, which is an environ char off type. Maybe I can put this to 100. Maybe the horse name is long on a course can't exist without the title title. So I'm sitting the attribute to be not no meaning. No course can ever go into into the table without having this value. I want to set the number. Sorry, number off credits. Andi, I'm making this an integer value on just for just for fun on for demonstrative purposes. I won't meet that not know, because maybe we have thought of a course. But we haven't thought off how many credits were going to give it just yet. So I'm not sitting. That does not know. But then we have course horde, which is also in Hvar Char and I'm going to me this a small of articles. Generally speaking, of course, school. It is not more than five characters, but then, of course, according to your needs. You set the size that you want on them. Also going to see, not know, because it doesn't make sense to create. Of course, that doesn't have a poor school. So how now? Just have come into your seek and see what I means. Our core score could be like, Maybe this course in a university would because its CS 21 introduction to databases right, So that's an example. So, you see, a comment can be included here. Once again, a comment will be ignored by the execution sequence. The blue dots on each off these command lines or common blocks. Brother, specify that this is a command by itself. Off course you could ever in all of this in one line, so there's no harm in writing it in one line. But then I just broke it. Also that we didn't have to stroll across appears to see what was happening, right? So and then you see here it's harder to add this comment because in the comment is including some called in it and causing an arrow. So that is one of the reasons that I would have broken it line by line. So that we could see each line on. Scrutinize it individually. All right, so you see, I have added some files. Here are some comments here. Sorry, Andi. That's just for informational purposes. When you're reviewing this this court file, you can see exactly what is happening here on, but be guided accordingly. Now execute. Once again, we can just click, are lining both Churchill, then execute use school db and then this and then this in the order that it is set up in the documents. But then, if I only wanted to execute, are part of the correct you just highlighted on the click this lining bulls of the cursor. And if we just over over to see that it executes the statement that is under the keyboard cursor or has been selected so essentially, that's what that means. So those are two ways. So if you have multiple commands in one document, but you're only execute executing one or few off these commands, then you can just highlights that command or those commands. And you just click that on with the cursor. So I'm going to just execute all of these, which will then choose the database and then create everything else on. Then you. I'm getting a warning here. It's seeing that I have some character friends stuff. I think that might be caused by the version ing. You see if anything happened so my tables were created. So you see, we have ah success. We have arrow and we have warning. So sometimes you get warnings on most times. These warnings will be because you might be using a certain bit off court without specifying certain things. Most times warnings are harmless, but in this game cities, because we got our table with all the columns and industrial down and see everything about our columns accordingly. And if we take a look at our column. So we drill down into school DB tables, courses and click columns, then below the schema section, and we'll see definition. So we have course idea. You noticed that the kind of stands out on if you look at its attributes, they're turning in that it's an individual, it's auto incriminating and it is the primary key. So my skill uses those little things here and PK just to show you the attributes that it has been given all right, on then you see all those with their attributes. This is title is Var Char 100 Andi So on. So that is essentially how you gullible creating tables in my SQL using commands on day. I'm just going to see if this file Onda I'm just going to see create tables on receiving that does an SQL file on. I'm going to be sharing that with this video. No, another way we can actually create tables without the use off. The SQL script is by using the GUI tools afforded to us by my SQL workbench on. We can do that by right clicking on tables. So having gone down into the database and we have the tables parts exposed, you can right click that and then we see create table, which will then launch a new interferes, allowing us to specify the name. So another table that would be useful in a school BB would be a table to store the lecturers. Right? So we're tracking the courses were tracking the students on No, we need to track the letter. So I'm just going to call the state lecturers. All right. On the schema. Once again, this school db we can leave that all of those things can be left alone one, and we can go ahead and specify the column name. No, it's worthy to know that my SQL supports both relational database style. Andi know what we call no excuse style, which is, Ah, variation off databases that doesn't rely on relationships on to get that you use my I assembled. And if you click on this engine, drop down, you see that there quite a few storage or engine stars that my SQL is capable off. To be honest, I always use in a DB because in a relational lead to be sitting, that is the one that we want to use so we can leave that alone on. Then we just go ahead and specify our or column name. So we got a primary T column by default because my school is forcing us to be to practice what is best so I can always change this column name on. I can see electricity or this idea to see i d. The data type is already int on there already telling me that this is a primary key. If I antique that you notice that the emblem changes. So there's three tickets and end means not know Andi. Each off these columns means something different. So the check boxes mean something. He can always just hover over the letter or letters to see exactly what is being described . All right, on the one that I'm really interested in is auto increments. So remember that when you were creating our tables here, we specified primary key, which is PK over here on. We also specified auto increments, which is a so the stick A all right on. From this view, we can actually add more detail so we could add a default value. We don't need a default value for our i d. Because we should never have a default value for our i. D. We cannot comment so that we know in the future. Or if we have to share this database with anybody, we can always write comments so that they can see exactly what this columnist for Andi. If you take it here, then you'll see that it is stick down in this section also. So you can use either one off these to manage those attributes. Okay on, then we to create another column. We just click in the next line on, then we see first or let's use the same convention, last on the score name on then. This is using the data value type far Ciara supposed environ char which we use. But I just need that's far char on. Then I can just stick, not know on dime, using first on the score name and also not know on something else about a lecturer would be , Let's see title. So maybe the person's doctor or let's see degree. So is that they have a BSC, MSC or PhD, and then we can leave that one as no notable. So you see that the emblem changes based on certain attributes being selected. All right, you may get a different color or are visual cue to the left hand side of the columns on then. Once you've finished creating all off your columns that you intend toe have, then you can go ahead and click Apply, which would then give you a very similar looking script toe what we just penned. But then you see that there certain variations so that they're supposed to show you that there more than one ways. Now we could have actually written the script because here, ideas being specified. But then primary key is known here as opposed to where it was in the one line when we did it. So you see that they're they're different ways to write it. Also for the notable fuse. We didn't write the word in all. We just left it blank on it automatically assume that there should be No. So, you know, if it's generated, is going to look a bit different from what we have. It also specifies the engine here, So if we click apply, then it will let us know if it was executed successfully or not. And when we finish, we can look over to our table listing and we will see our newly created table there. And if we look at the columns, then we'll see that everything looks in order. So that is so you can go about creating. I need to be stable using the my SQL workbench interface. 6. Insert Data into the Table with SQL Script: you guys in this missile we will be looking at in searching records in tow our tables. So to begin this exercise, you will use a script and we will look at this in tax to insert records into our student table. Now, do recall that any table should be a collection. Off rules are red cards relating to the entity as described by the tables name. So the table students should only be storing data on students of each record needs to be representative off a student in building our table, we would have stated or columns on the minimum data needed to build airport on a student on by the minimum data required. I mean, what did we set to be knowledgeable and not available? So we did specify that only enrollment did was knowledgeable, kneeling, meaning we could always add a student without specifying on in a Roman bitch. So let's look at the syntax toe, insert a row off data so the keyword is literally insert into all right. But then, before we do all of this, we should always use the database. So we use school on the score DB and then we continue with our syntax. So we insert into the table, and in this case or table is students on. Then what we do is list the columns on the erupt inside off parentheses on. Then we specify the values, and we also wrap those inside parentheses, fired some just writing it like this. So we see exactly what. So this is the farm it for any insert statement, insert into the table, name on, then the columns listed on the key word values and then the corresponding values with each column. So the columns, as I have here, would be last name Cuomo on each one is common separated first name Hama the IDs off birth on, then comma enrollment. It's all right. So these are the columns in into which we want to insert some amount off so we can actually break the line so that we can see everything happening at once. So insert into the table the columns values, and then we specify the values. Now the order that we specified the values most correspond with the columns. So, in other words, whatever value I put here first and remember, that last name is a string value. So when whoever were went to be writing out string. But you are a word. Want to use our quotation marks? Are are single quotation marks. The double quotes can work also, but I prefer to use the single quotes first. All right on. Then we see. Let's say, for instance, I'm going to insert test as the last name on the first name would be student, and then the date off birth on date of birth or date data type could also be in a string on the most commonly accepted four months would be year as in. Why, Why, why, Why dash models as an em dash day d d. So it would be a four digits year dash a two digit months dash. A two digit day on that is almost acceptable in almost any database management engine that you would be using on then for the enrollment it let's say, for arguments if this student is not enrolled as yet, but we want them on their part. So I just put no. So whenever we have a knowledgeable field, we can actually just seeing no on. Even if we omitted this role. This column from the insertion, then it would just get another value of by default. Also, it's already to know that if you have four columns here, then you should have four corresponding values. So there should be no mismatch between the number of columns outlined on the number of values or find. If you ever miss much, then you get an error. All right, so that is so you would go about inserting one rule off data into the data set off first to end with a cynical on. And then if I execute, then you see that it was successful. According to this log down here in the old put on one row was affected. So if I were to look in the students did I should see at least one student record? No, this channel get tedious if you have fire. 67 students are 10 20 students that enter on. It would be get tedious trying to change all these values with each student records. So SQL actually gives us a mechanism by which we can actually insert more than once. I've just been to copy this scored and basted down here on. Then I'm going to highlight that You can specify the columns one time on. Then you can have multiple sets off values going into those columns. So using sorry is in control and the letter D as in dog, you can actually duplicate each line. See that I'm just duplicating Andi. I'm just showing you that you can actually have values, and you can have multiple sets off values being inserted at once. So this is how you do a multiple role insert. And then all you have to do is just make sure that each one is separated by a comma upon to the last one, which we know does not get a comma but would get more like a semi colon to signify the end off the file. So I'm just going to go ahead and refined these values a bit. All right, so I changed numbering on the names here, So I just have students want just one to eat on. I didn't modify the student numbers on their egos. That's better. So we have student test one through student, test it on die, changed up the dates off birth, and then you notice that for enrollment data put in some dates and left some notes So the fact is that as many rules as you need to insert, you can actually write one statement that will execute them all. All right. No, you also may have noticed that I didn't include an i. D. Column here. And that's because once again, when we were creating our database, we specify that ideas should be auto implementing. So we wouldn't be specifying a date of value for I D. Ideas taking care of itself. So once we insert a report, I d will automatically cone, pope toe have a value to unique identify that record that is being inserted in that command . So in this situation where we're inserting it, that's later it rose. When I execute this, then I d will automatically increment for each of those rules on toe. Run this statement by itself because I have this statement that I run before, but I don't want to run this one again. I just want to run this portion off the script. Then, like we discussed in the previous video, we genders highlight that text, and then we click the lightning bolts that has the cursor fixed to it. And once we do that, it will only execute portion off the court that is highlighted on then here we see that we have the intros affected, the its records being inserted with no duplicates and no warning. So that was very successful on that is essentially how you go about inserting data into your data table, someone to save this script. All right. And I did it some comments to it. So we still have that for friends that I appreciate exactly what's going on on. Once again, we could ever in all of this in one land, as depicted by this template here, or even for the multiple insert as on zero comma separate the section with the values upon to the semi colon at the end. But we can also break the line and the blue door to keep us in check as to which statement belongs to which part. So that's it for inserting records using the scripts. Next we look at how we insert records using the war bench interface 7. Insert Data into the Table with Workbench: Hey, guys, In this video, we will be looking at using my SQL workbench interferes to insert records in tow. Our data tables, though in our previous video, we would have looked at how to do it with a script where we specify the database to be used . And then we wrote our keywords and or statements on. We looked at how we can insert one at the time, versus many at a time. Andi, you may be looking for quicker on potentially easier way to do this without having to come in and write up a whole script. There are advantages to the script usage because then you can see the file on. You can use it at a later date on maybe even another machine, So that's the advantage to using the script file as its exportable. But then workbench also uses a wave toe. Insert records directly without having to write a line off SQL Court. Andi. To do that, we can just over on our table, Andi, In this case, we've been interacting with students table so we can just hope around the students table, and then you'll see that you have three icons appearing on the last one has like that lining bolt on TV looking icon so we can click that on. We see that it executes some command That's a select from the students. So you see that select wearing No, in a future lecture will break down that select statements. But for know that worry will give you this result, which is what work been shoes is toe help you insert more records. So here they're actually listing off all of the reports that we would have created on you See that? Like I said in the previous video, for each record that was inserted, we get a unique I d. All right, so it's commenting. So that means the next one will be 10 Onda. We see all of the data that we would have put in. So if we wanted to create another student at this point, then we could actually just click in this road that has all null values under start filling in data so I can see Williams, my neck and other record for myself. I'm not going to tell you when I was born all right, I this was a type of, but I'm going to leave that because I wanted to see how my SQL handles erroneous reports so I'll just leave. This one has no. And then I pressed. Enter. So what happens is that when you press enter, just goes on to the next one, so you can add as many as you need. All right, on then, at the end off that process, you have to click apply on, then apply is where we'll actually still generates cold, very similar to what we just wrote, which is just insert into on. Then you see that it kind of fully qualifies the database name where it's a school db dot students. Because the reality is that if you don't have the use statement, then you can actually specify the database dot The table on that kind off removes the need to have the used statements because then you know exactly which table in which databases being targeted. All right, on then, if we look at the rest of the court, it looks just like what we wrote our values on. Then it puts in the values, including that invalidate on. Then if I click, apply, then you see that it gets back with an error, saying that an incorrect date value was attempted to be inserted. All right, so so SQL my skills actually doing its own validations on the data Based on the data time that you specified, there is no 201st months. So that is just an error. So it knows that it should be year months, the so, of course it's going to validate before it attempts to put that in. And that's one of the principals off data integrity because that integrity speaks toe. Having data that much is the needs on much. Is the standards set up by the designer off the database? All right, so I can this cancer that on meet that correction, I'm just at a few more rose. Alright, so I've added a total of three year olds with fairly consistent data standards are in keeping with the data standards, not violating the integrity that we're going for on. Then I can just click apply on, then you see that instead of doing the multiple insert is actually generating three different insert into statements. So the thing with the multiple insert is that if one off them feels, then the whole batch feels as opposed to doing one by one so that it will run this. And then if this one feels, then it will probably just stop the execution off the script there, allow you to car this one, and then it will move on. So that's the difference between it. So that's probably why they did it in three different statements to give each insertion command of fear chance as opposed. Clumping them in one statement and feeling them all if one is but so we can just go ahead and click Apply on you see here that there is still, uh, okay, I know what's happening. So this is actually a four digit year on a two digit months on then to digit de So I have the 21st month years old, 21st month. I apologize. So let me try that again. Apply Andi. I see. My girl. I should not have put the value. No, because no, these kind of represented like this. So if I just leave it empty, then it will automatically know that this is a no. So let me try that again. All right? So instead of having a lower case know that I'm going to have an upper case where No, which is actually a keyword on. But my skin is very particular about who it will enter it. So I'm just going to that. All right, so this is actually a good lesson. So if you want to do something, no, then it's best we just leave. It s Oh, my spl represents it because by me trying to type in the word null, whether in lower case or upper case or leaving it blank, we see that we're just getting some error here on the operation is just going to feel so I'm just going toe, remove this. So to remove that, you can just click it the rule on, then right click and said literal. And then it will remove it from that selection Criterion on. Then I just recreated down here just today's howdy foot representation, all right? And I'm not going to change that value that is there on. Then I'm going to apply once more on There we go. It was successfully executed and want to click finish. Then we see that the number is incremental. So you notice that it jumped all the way to 20 on. But that is because off all the failed attempts to insert the I. D. Was actually trying to auto increments each time aan den, it just kind off associate id any failure with a bad idea that's already used. So it just went to the next set off ideas. So all the times I feel just know he would have gone up by 10 11 12 and 13 14 15 and 16 17 80 all of those field onda until now it's at 2021 22 because no, it was successful on the next available number at the time was 20. So that is why these ideas would have jumped. So that is some behavior that you can expect from auto implementing primary keys on Do remember that the next record will always get the next number that is available while as it comes off. So from this view, you actually have the option to inserts records like this in a great view week and change records data right here. As long as you click apply at the end, he can also change the view you can change the former editor. That's Alozie to see it in a nice form, view or fields on their types. Seek and see all of the DEETs on the fields, and you can also see query stutts. But then this is for later when we start getting into some of the fun worrying stuff in our select section. So essentially, that's how you would go about inserting records into your table using that my school workbench interviews. 8. Select Data from the Table with SQL Script: Hey, guys, in this Listen, we will be discussing hole. We go about selecting or viewing records in over date of these tables. In our previous video, we may have glimpsed the query that was used to select the records from our students table . But no, we will actually do a breakdown and evaluates how this statement can be used on the whole purpose off. Wanting to view the records could be maybe to the airport. You know, the boss wants to know all the students and currently in the database, and people have been registering for school online. Listen, all you need to pull all the records off students so you can just go straight to the database and select those reports Onda potentially exported to excel and give it to the higher ups for evaluation. The statement to do this is quite simple, and it is what I call a select statement. So we select on then we want star meaning all all records, all columns and then we say from on then we specify over tingling, which is students know, once again we have to use our use statements, so specify the database to be used in the context. All right, So once you do that and we click executes, then we see that we have a similar grid to what we would have seen when we wanted to add records. Using the workbench interferes. So it's the same view. Essentially, Andi, more or less. This is the seam. I worry that we would have C. No. The challenge might be that there are data elements here that are not necessarily, you know, conducive to the analysis off data. Meaning you probably don't want to see this auto incriminating. I devalue Andi. You probably don't need the enrollment bit on. So sq allows us a specified exact columns that we wish to see when we are selecting records . So star here essentially means give me all off the columns that are available to me. I'm just by clicking these columns known in this view, you see that it's actually starting accordingly. But anyway, that's aside from the point, we want to select specific columns for or quarry for our reports. So I can say Cilic, and then I can specify the columns by name. So I want the last name on. I want the first name columns on, Let's there one bit off birth also, Andi, I can say select on individually list. The columns are comma separated upon to the last one which then leads into my from statement on then the table that I want them from which would be students and then with a semi colon. So I'm just going to execute this section by itself, understand the highlight it and click this would the cursor and you see that this quarter results looks different from the previous one Islam only seeing those columns that I had specified If I just execute the lightning bolt, it will execute both select queries. Onda lost the view the results side by side and we can view the results side by side by just women down here. And we see students three students for so for each select where that might be in the file, the results will be tapped on separated like this are in. So this allows us. This is an SQL alos us to specify which columns are data points exactly. We want to see for each students. So for student number one, we're seeing last name first name, date of birth in room indeed and I d when we see select star. But then over here we don't know if it's student 123 or four outside off the name off course. But if the names weren't so specific are for testing, then you would have a greater challenge knowing which one the student wanted. Three or four granted student 123 or four The i. D. Numbers who will be irrelevant to whoever actually is asking for this reports to be provided to them. Now, in the event that this should be a report and you've written your query, um, you can always export toe on external file, which is usually a CSP. All right, so you can just see in the inter fees you see the word export and you see that icon, you click that and then you can go ahead and save that file. So you would see that our deceive the file as that CS we prior to this. But I'm not very comfortable with how this query looks, because even if I export this to see its be my column, names are not as much as I use the underscore to kind of separate the first and last name. They're still not as user friendly as I would like. So I would actually want to rename the columns so that my users, who will be consuming the so put off this query would have on easier time looking at the column headings and deciphering what is being represented. Some, actually just mental break these lines quickly so that we can have the each column in its own line on. Then we're going to go through a whole we rename or Kahlo's. So to rename or columns, you can actually use a keyword called as, and then we can give it a value. So last name. But then, ofcourse, we discussed that we don't want to species. The last is a key word and name is a key word on my skill just will not Teoh this kindly to this. So whenever we want to use towards who use our apostrophe, which on my US slash Western keyboard is the it shares a border with the till. There on that is to the left, off the number one button for the number one key to the top left hand corner on the escape , so that is the apostrophe, not the single port. What they apostrophe? All right, so we have last name as and then we use the apostrophe and whatever we type in, there were no be the column name. So if I execute this quickly, then we can see this being demonstrated Where last name no. Looks like standard English. Last space name, first name and date of birth also need to be fixed. So I'm actually going to do this. But I don't need the key word. As so you can actually just forgo the use off the keyword as and to see first name on deeds of birth in the same way. We just use the apostrophe it off birth and then understand to re execute this query. And then we see and understand the fight in the columns so that everything looks good on. Then we see that last name. First they engage off birth. So for any column that you are entering into your database, you don't want to use a space I don't recommend. I use a space since either you're goingto make it one word. Chamila cases or use an underscore. But when you're presenting the data in A in an Excel spreadsheet To make life easier, you can just really in the columns from the query side on. Then when you export, those renamed headings will go with the data. So those are the ways that you can go about selecting data on once again. Once you've run that quarry and extracted data that is required, you can always just exported to a CS Wi Fi, which can be later opened, and Microsoft itself or comparable tool and analyzed accordingly. So once again, I'll include this script file with the resource is for this video. 9. Select Data with Filters: Hey, guys. In our previous video, we looked at selecting records using SQL statements that we wrote in this video. We will be looking at how workbench generates so of these things for us and also will be looking at more advanced Berries in terms off custom columns. Onda filters. Now the first thing that we want to do when we want to see data from the table and expedite the process is just find the table over here in the scheme a listing on. Then we just click This screwed with the lightning bolts that is in the same role. Once the rules highlighted. And then that would actually generate for us a select star from statement, which is a good base to start from on. We'll get the queries that the results associated with that Quartey. You know, if you notice instead off using the use statement like we would have done in our previous script, this generated quarry actually sees he uses dot notation to see database dot table. And that's just the standard thing that you see in more state of the Spanish grand systems or just seeing database name dot table name. So, in essence, if we forgot to use school Devi on, We had to school Devi's or two sets off databases with table with a table called students. Then what we could do is just say we want the students table from this specific database and it will know that that is the context within which he should run that quarry. All right, so I'm just going to come in that safe house. Any errors on that is the way that this generated for us. If you want to write it this way, then that's fine. Andi, as we get into some more advanced for you see the value in writing it this way because then you may end up reading scores across databases. Andi, this is a good way to know exactly which database the table that is being referenced applies to No to explore. Some of the filtering exercises are requirements. I have included some activities here in the script file on def. We look at them, we see that we want to retrieve only records that meet a certain standard, and so we'll have to amend over select worry because I know the select worries air scaring back every single thing that is in the table also failed to mention that you can actually limit the rules. So you can, actually, till we're in jail, you don't want a limit, or you want to leave me to just 1000 or 2 5000 so that when you click this button here, cigarette with the lining bulls, it will actually limit according to what you want here. No, we were toe actually. Write that limit constraints on our quarry than we would upend it to the end off our table . Go. So it's let's start from And then we say database stock table, and then we would say, limit on the number. So I say to then it would just give me the top two rows in the table. All right. If I say limit 10 then it would just give me the top 10. Okay, so that is the power off the limit. Keyword. Right there. No, back to our task at hand. We want to retrieve only the students with the last name Williams. Understand. To remove the limit on, we take a look at our entire detested once again, and we see that we only have one students named Williams. But Williams is a pretty prominent names, so you probably have multiple students with the knee. Williams. And for whatever reason, the dean of discipline is asking you to give him all of the persons with the last name Williams who are currently in the school. So no, you need to modify your select statement toe, have a filter toe this request for data. So to do this, what you would write is select on. Then. Remember that star really means all the columns are all the data points. So if it is that you want everything about the student, they can say star or he can only select the colors that you want. So I'm just interested Last name, Understand? Comma. First on the score name? Andi. I only want the first and last name from the students table so I could just write students . But I didn't have the use statement of tops are just conform to the school that BB dot notation syntax and I'm going to say not students on. Then a new key word that we would look at is we're so we're seeing select these columns from this table in this database were on then, after where is a condition or condition based on your requirements, would be that the last name must be Williams, so I can see where the column last name has the Valley Williams. And the easiest way to see has value Williams would be equals on. Then I type whatever it should be equal to on it should be Puerto Williams. All right, so that is a new parts toe, our selection statement. So essentially we see were once again and then we specify our column on then what we expect So you can have different operator. Siri can have equals. He can as less than we can have less than or greater than or greater than and equals less than an equals. We can also have not equal to all right, so off words the less than greater than and so on would apply to numbers as opposed the words. But then, when we want to much a specific word, we could just use the column equals on then some static text inside of quotation marks, which will be used to evaluate every last name and only bring back the records that meet this pond Isha, so we can just go ahead and execute this are selected and executed by itself on. There we go. So we're only bringing about the first name on last name off all students with the last name Williams. And if we look at the entire data sets, then we can determine or deduce that that is actually hi periods. All right, so what if I wanted the students with the last name test? So we see that we have one with test, but we have many variations off test on day. I'm just going to ask you to ask yourself right now how many rows would come back if I specified that the last name should be equal to test. So if I execute this, I only get back one role. Now, you may have said more than one rules already. May have said as many roses. There is a word test in the last name on. Do you have been wrong? Because test one is not Samos test? This is saying Give me Were All of them are equal to test. So any variation off the word test will not be brought back. So that brings the store next activity where we're going to retrieve only students with word test in their last mail. Now, we just attempted something like that. I'm just going to copy this query and paste it down here and we just modified accordingly. No, the steak. I look at the entire data. Sit once again and we see that all of these from one through nine ideas one through nine have the word test in their names. So that means whatever worry we right for this requirements should bring back ideas one through nine and not bringing back 20 through 22. So then we need to modify or conditioned chapter exactly what our requirement is. And to do that, we will use our keyword called, like no, like aloes us to specify what we call a wild card on much a pattern as opposed to any having an exact much so in more simple terms. When we say equal, it will look for an exact much. So let me just put back Williams if the name if the column doesn't exactly match this value than this condition fields, so that is what the equal sign does. However, we want a partial much we want anything that has the word test in its and so we need to change from using any. Both sigh into using the keyword like And then when we have the key word like inside off the string, we can specify what we call a wild card, which is represented by a model IHS or percentage sign. All right, so this is how modelos works. It will see. Give me anything that is before this or anything that is after this, and I'm going to start small before we actually fulfill this requirement. So let's say I wanted any name with the letter e Any record with the letter e in it. That means I should bring back every test on also this role 20 with Reed, which also has an e. So I can see. Give me these columns from this table where this column, which is last name, has something like an E. And to do that, I'm going to see models, which means I don't care what comes before on E. And I'm going to say modelos again because I don't care what comes after the so that's what the model is really does it says I don't really care what comes before. This pattern on I don't really care what comes after this pattern. As long as this pattern is met. In this case, the pattern is just one letter, which is e. So if I execute this, then it will sift through and find all the last names with you in it. And that's why I read came back. So let's try that with the letter I If I said give me about all the last names of the letter I in it, then only Williams and re come back because there's an iron read and there's there's at least one eye in Williams. Let's move away from litters. What if I wanted anything that had the patter and l l in it? So any column that has two l's in it. I don't care what covers before. Els don't care or comes after the else Just give me anything with else in it and off course you should only get back all small. I forgot that small was there. So we're getting back small because small has something before the l l. But it has an l l on just the same for Williams. He doesn't care what's in front of the L. L. R. After it, As long as there's an L present in the same way that we can actually much patterns inside of the string. We can actually use this to specify if the word starts or ends with the letter. So let's see, I wanted any name, last name that ends with the letter. It's all right. So I would just say models, It's so that means I don't care what comes before. Just give me anything that ends with an it's hard on. Then if I execute that, I should only get back Williams on then If I say I want anything that begins with an s, I can see s on, then modelos Andi, I hope you notice, but I know that it is not clear sensitive because I'm searching using common letters. Onda I'm actually getting back values with capital letters that meet their criteria. So the evening executing a quarry on searching on with the static string. It is not very key sensitive in my SQL, But when we're selecting here, when we just want to see any name that starts with us, then we see that letter or that sequence if I wanted S m or SNP be whatever it is. What this is saying is that give me any sequence that much is this regardless off what comes afterwards. All right, so I want any pattern, Any last name that has an ISS regardless off what comes after this. All right, but we're not putting the wild card before because you wanted to start to thinness on. When I do that, we get back small. If I did that with D, then we would get back all off the test student records in the date of these. All right, So actually, this would have kind of fulfilled this requirement Retrieve on the students were were test , but then this would not still not work. Because then, if I had somebody with the last name Fear Door or something like that with T. Thomas for arguments E, then that person would come back, and that would avoid the results it. So once again, I can wrap the expression inside off wild cards, and I can just read all the whole world the whole word test to see I don't care what comes before The word test, and I don't care what after comes after the word test. I just want back all records with the word test units. So I do that and they execute on the results. That stays fairly the same. But if there was some other record coming back before, no, it would be sifted out. And that is essentially whole wild cards work Now our next requirement does is that we want about the full name on drome and date for all the students. Know, you'd be wondering why my specifying full name Because I'm already bringing back before name here, but then noticed that last name and first and those are two entirely different columns. We want back one column with just the fulling. Someone just start off with the beast. Expects star from Andi. Just copy this horde on when I get these queries. I like to do what they call a process off elimination where I start off with all off the data off the potential columns on Then one by one, I sift through and take about what I don't need. So I know I need the last underscoring new on the first on the score need to make full name Andi. I also want enrollment. It's all right. And so when I execute this, I'm getting about the three columns that I want. But then, once again, I'm not getting back a full name here on day. I really want to rename those columns Notre Accomplished printing or first name on last name In one column we have to employ what we call in off the string manipulation functions are native stream functions off my SQL on that comes in the form off Khan Cat Con cut is short for concocting needs, which is the process by which we join two words or blocks off text together in general programming terms. So in this situation, what I'm going to do is write the expression Khanna cats on their auto, complete it for me. But then, inside of the parentheses, I need to specify the strings to be concatenation did so I can see I want to contact innate first name right on. Then I can see last new. All right on then. As as we discussed before. Once I specify the column, I can always redeem. It's so I can just see full in here so that we know that this is a full name on. Then when I select this on, this is not one to come out. 13 Lee, so we'll just refine it as we go along. So when I execute this, then you see that full name is being printed. But then you look at it and you see that it's chopped up. All right, so we need a space in between the Beverly and the small, and the student is interested it. So with the Khan cut, you actually have to specify every single space in addition to the values. So because we have this column here, and this column here is just crunching them together so we can just scrunch another space in between so I can see space open politician Mark Space bar on, then comma. So what that's going to do is join the first name toe a space toe. The last name on then, or end result will be a full name that is far more user friendly user riel, students, base test travel or space Williams on. That is how our data would look. And once again our column is named appropriately so we can just go ahead and rename enrollment date to ensure that everything looks uniforms. The next two activities are based on the course is stable. So by no, you should have inserted some records into the courses table on, do you know will be tasked with selecting those records with some filters. All right, so you can always just put in some test data that would kind of help you toe fulfill these requirements set up here for you on I leave you two hints as toe watch you can use to fulfill these. So when you're right in the filter you'd want to see were pretty it's column on. I'm not. I'm being very big here because I want you to actually get that practice is greater than three andan the same way where credits column is less than or equal to three. I'm sorry this would be to based on the question being asked. Right, So it's where it's better than two as a line here on where it is less than or equal to three so you can go ahead and complete that I will upload this file with the solution, but I employees to practice it on your own 10. Update Data in the Table: Hey, guys, welcome back in this lesson. We will look at how we can go about updating data that is already in our database. Now, if we take a look at some of the data that is already in our database So far we have been populating only the students table. So I can just go ahead and run that select curry against a student stable and we can take I look at what is in there and we see that we have a lot of students called test on DSO. We want to probably start changing some of the values associated with these students. And so what we want to do is look at how we go about writing SQL statements that would affect these changes. No. Back in or empty script file, we will start looking at a new key word. And this keyword is updates to just see update on. Then the keyword update is followed by the table that we're about to effect some update command on. In this case, it would be students. So I already have my used school db here, so I don't have to see it. School db dot students again. So I don't say students on. Then we have another key word which is set. And then after we see updates the table name on the keyword set. What we will have is the values that we wish to change. So we'll have a column on value. Pierre. No, let's say that we wanted to update the enrollment date for tests students because they're finally up enrolled into the course, and that would have happened. 2019 0701 So we need to change the enrollment date for to a test student toe that date. So what we would do is, say, specify the column, enrollment on the score date and then we give it a value, which in this case would be 2019. So this is the value that we wish to change too. And then we could execute Burt's. Please do not execute just yet, because what happens is that if you run this statement, as is what it will do is literally go to the every student record in this table and made this update, which is not what we desire. We want to specifically update this records enrollment beat so once again be very very careful when running this update Command, Because you have to specify if needs be, which record it is that you wish to carry out this update on If you have multiple columns that you wish to change the values for, then you could always just comma separate them. And you continue with that same column and value appear. So if I wanted to change the last name off test students, maybe there was a typo on. We spelled it wrong. So we want to just update the name off the student on the enrollment did. Then we can specify as many columns as we wish. So last name would no be Mark Paramount's on a first name would be Wilma. So I'm just showing that one. You can specify as many columns as you wish to be updated, and I'm just breaking the land so we can see here on then just drive home the point. You need to specify which one off the records you wish to update before you run this command. So in this case, because we're targeting test students, we have a number of options here. When we're going to apply that specifications, we could either try to target based on the first name, but then or the last name. But then that would not be very clean. Because if we have more than one persons with the same name because it's a school, we could have more than one person's named John Brown for arguments. See? So if you were to try and target the students based on the first name or the last name, or even both, you could still end up targeting more than one records on erroneously. So So that is one of the key reasons that we always try to have a primary key value on each roll. Because if we target the rule based on this value than we can be certain that we're targeting this specific record on Soto, apply this specifications or filter on. I was holding out on using that word because we just wrapped up a lecture on using filters when we're selecting where the same mechanism that helps us the filter on a select actually helps us the filter or specify which record we wish to carrots and hope the Amman on so we can reuse Our were keyword on to specify the condition and the condition that I would like to meet toe. In order to fulfill this up, the command would be where the i d off? The student is the value one. Remember that we're targeting test students. So the easiest way to know that we're targeting test student is just a specify where I d is equal to one. So I will just say were i d is equal toe one on, then ended with a semi colon on. Then if I execute this and I just execute this entire script we see here that we had one roll affected on it even tells us that this kind off effect was a change. All right, So if I go back toe my quarry looking for my students and I re execute this query, then I will see that students with idee one no has better information. Dermot Wilma on the enrollment. It's all right. So once again, if I failed to specify which record it is, I wish the update. Then this up the command will actually go through the entire data set. So be very, very careful and deliberate when running this This would be similar to the airdrop commands Be very, very careful and deliberate when you're a boat to execute on opiates statement. So you want to make sure that you are targeting exactly who it is that you wish toe update ? No. For virtually everything that you can accomplish with a command, at least at this basic create read, update and delete our crowd level, you can actually replicate that using just the interface. So we just wrote a command that handles updates and I'll say this script on included with the resource is for this video. But we can also having selected our records and gotten or results here, apply changes right here in this grid. So if I wanted to update student with I d number seven's on in Rome mandates, then I could just double click in that space and change this value. So 2019-0 for Dash 26. Just putting in a random date there. All right, now we see that it is no applied on. Then if I off course need to submit this, then I need to click apply over here, which would we should be used to from or insert video. And then we just click apply, and then it will generate a very similar statement What we just wrote. So obviate school db dot students set on it will give you the column on the value on. Then we see where it is, even specifying where I d equal seven and you see that they use a parenthesis here on you can tell that that's not absolutely necessary because he wrote always without and it worked . So like I said, the generated court sometimes has some differences in it. Andi, it's good to see it both ways. All right, so if we're satisfied that that is exactly what we want, you could review it on. We just click, apply, and then we see that it has executed the statement successfully. So if I refresh like industry fresh by which will re execute this query, then we see the changes. Steve. All right, so that is how you go a boat updating records in my SQL server. Alright, So in the script file, I have included some activities here in the comments, so you can when you don't know the script file, try and complete these. I will actually be uploading the script fair with the solutions, really and truly. So while you're watching this video, you can go ahead and look at the requirements and try and complete them. But then the uploaded script will have the court that matches these requirements. All right, so happy hunting and see you next time. 11. Delete Data from the Table: you guys in this lesson, we will be looking at how we can go about removing records from or database. Now, this is basically the last letter in the word crowd. Andi, I think this is the first time I'm actually mentioning that word crude. But the lesson section would have Bean entitled crowd operations and credits, essentially an acronym for Create, read, update and delete. So we've looked at how we insert records, which is create We have looked at all we select these records, which is reading the data we've looked at, how we update army changes. So that's the you. And so no, we look at how we remove or delete, which is the deep No, Much like the other commands in SQL Delete Command starts off with the word that characterizes the command itself, which is delete. So you see at this point that SQL really just looks like standard English there, just certain keywords. And there is a specific pattern which, with each type of command, that if you just follow that pattern and I know those keywords, you have no problem actually writing these commands. Now the elite command takes a bit more ity it says delete from And then we specify a table . So in this case, we only have data and the students table are these I only update and the students table And then we say we're on dso you notice that the where is going to become far more prominent. So when we filter reading date are selecting data we use were when we want a bit specific records, we use where and when we want to delete It is even more important to use aware because without the where it will actually just remove everything from the database Meaning if you were to execute this right now everything from the table Sorry, if you were to execute this command right now as it is, this would just remove every single student from the students table, which is not what we want. We want to probably remove specific records from the stable so we have toe specify over we're on, then give a condition. Now let's quickly refresh ourselves with what is in the students table on. You would have slightly different data from me right now because you would have completed the update challenge from the previous lesson on dso you would have updated enrolling deeds and changed out some of the values for first name and last name. But I just that the principles would remain the same as long as you know how to get the I D . Which is the best way to target a role as opposed to using any other column. Now let's say I wanted to delete student test it student eight and test it. All right. So, student a test it which is student with the i d nine. I want to remove that students. So I just say the lead from students were on. Then I specify my column on the actual come condition which would be equals nine. So I want where I d equals nine. Delete That record ended with a semi colon. And then once I execute this what I can just execute this entire script. Then we see here that one rule was affected. So if I refresh my quarry to select all from the students able, then I see I no longer have a student with the I d night. Once again, if I leave this filter or this condition off, then it will remove every single thing from this table, which is not necessarily what you desire, at least not right. No off course. Everything is done on a case by case basis. Now, what if I wanted to remove more than one rules and I only want to direct one command. So in the activity and the answer sheet that I would have uploaded with the update activity you have seen where in the comments are included? A statement using a key word in. So even if you didn't pay much attention to that, that's fine. We can look at using the key word in right here. So if I wanted to remove the students from 3 to 6 with ideas three through six. So that means I want to remove 345 and six All of those students and I'm writing one command to do that. Then instead, off writing. Well, I could write delete from and then this, and then have a delete from statement for each off those ideas that will be fine. But then I can also use the key word in and then in brackets or parentheses actually list out all of the values that she could be met or could possibly meet this condition evaluates to true. So in other words, I want to delete from students were I d The value I d can be phoned somewhere in a list of values. And I did say, I want a 345 and six So this command will actually just say, Look for all the records with an i D. Value that much is something in this list. Essentially, that's what it's seeing, and so all of these will come back. All of these will evaluate the true, and the expectation is that if I execute this, all of those rules will be deleted and I'm going to execute it. I would test that theory, and we see here that four rules have been affected. If I refresh my original query that I see that I no longer have students with ID's three through six, no expanding on the capabilities off this condition, we can actually specify more than one columns in a condition on here. We see where we're seeing lead from students were on the conditions. His first name must be equal to this value on and last name be equal to that value and I left on my underscores here. But essentially what this is doing is evaluating two columns on only returning much in condition when both columns meet this criteria. So in other words, if one if I find Terje in a first in column about her last name is read, then that this condition would not hold true because that means both first name and last name are not equal to the values that have been specified so that on the key word actually says both both condition on value. Both column on value Piers most be true on that is when all of this evaluates the truth. All right, in the same breath. If I said I wanted, either are than I have the key word or on this filtering the these keywords on on or also applied to selecting. So any time you have a were close, you can actually specify as many column on value piers as you need toe on. You can always just join them as on meaning everything most much. Both sides are as many size as there are most much for it. The value to true on diffuse on. Or it means that anyone off, These could be true. So if I execute this and it fires finds a student with our first name Taji doesn't matter what the last name is. Then it will delete it. And if you find somebody with a last name Dermot, then it will delete it. All right, so that's what the orders. So you have that. So in this case, where I want a student named Taji Dermot, I have to make sure that both the first name on the last name much the specified conditions . Another example to look at is with using our wild card. So the same week, we can use the wild card when selecting. We can use the wild card once again when we're deleting, and even when we're updating, the fact is that or were clause can be used when we're selecting, updating or deleting Onda. Essentially, it helps us the narrowed on the exact conditions that need to be mitt whenever we are about to execute. So in this scenario, we're actually using the or clause because I'm seeing leads all students with names containing students. So that means the first name could probably continent students for the last name could probably Contin student. So literally I'm seeing leave from students were the first name, and we already established that the modelos means I don't care what comes before. The word student are after the word students, but it contains the word students or the last name can quantity in the word students. And that's essentially hold that works. No. Once again, workbench actually gives us an interface to help us to manipulate or it or even removed the data that we are looking at when we were in a query. So the final step to this is whole to remove a record with writing or needing to write a script. So if we look at this result grid, we can actually just highlight a column and then we can remove. And I think we did something like this before we can remove that robe. I just right clicking that ruined picking the neat rows. Or we can just like this I can hear that is the need selected row. So if you have to select multiple at once and then you just delete selected Rose that will works, understand? To remove student one test one on. I just right click and say lead roles and off course we have to apply and applying will actually generate the cord. That much is the operation that were carrying us, and you see here that they're very intends on putting in the condition to make sure that they're getting that right record. So I click, apply on it would have executed successfully and so that students would no longer be among the lot. So essentially, that's how we go about deleting records from or database. I will include this script file as parts of this resource is for this lesson. Andi, I encourage you to just practice, find some data inserted and try and delete it, and look at many different ways to use up your conditions. Andi. If you need any assistance or guidance, fear, Frito hit me up by a comment message or Q Andy 12. Reverse Engineer Database into Model: Hey, guys, In this lesson, we will start looking at whole weaken global reverse engineering or newly created database into a database model. Now, a data base model is a graphical representation off our tables on holy really to each other in databases developments. Terminologies really referred to as an entity relationship diagram or E r d for short or er diagram. All those are pretty much the same thing on what you're seeing on screen. Here is on E R diagram that was generated based on one of the sample these databases called World. All right, so this is essentially, ah, graphical representation. Like I said off the database, meaning you print this out on a piece of paper and you handed to somebody they should be able to clearly identify your table, which is which is represented by this box here on. It's also called on Entity. So table and entity are pretty much used interchangeably. So the entity is city Andi. Each line in this box represents the columns on. Then you see that there is this highlighting thing that's going on here on this line connecting city to country on also a line connecting country language the country. So later, in the lesser we'll get into that. Those are called relationships, which is why the are means the entity relationship diagram. So it shows the relationships between the tables. We don't have any relationships in our school db just yet, but we will be looking at how we can go about having already created our database using strips. Reverse engineer it into a model looking form. So we're back on over hold scream for my SQL workbench. Just opened it. We learned on this screen on there are two avenues to get to this data modelling interferes so one we can go ahead and connector instance like we would on a normal circumstances on Dhere have all of the script file. So if you have closed workbench and reopened it, you notice that it kind of keeps struck off all the scripts that you would have been working on. But that's besides the point. To get started with this reverse engineering, you could just go toe eight obese and then you see the option there. That's his reverse engineer. Alternatively, without even connecting to the instance, you could just go to this modeling this BRD looking icon to the left, which is underneath that dolphin, all right. And then you could go ahead and just click this arrow and then they will see create E R. Mollo from database. So either one of those ways can get you started. You also have that database option appear reverse engineer. So anyway, you take it. It will be the same results. So I just go ahead and click. Reverse engineer appear on both will launch this modern. This dialogue which will ask you to select an instance to connect to maybe years. Woods would have started off blank so you can go script that and you can just choose that default. Local instance that you would have been using to connect up to know on de everything would have been filled in you click next on. Then it will do some tricks. And then it will ask you which one of these schemers do you want to include? So you can do multiple schemers at the time? Oh, I know we're just focusing on the one that we're creating. So just like school Levy and click next. There's some checks next on. Then it will ask us what objects do we want to import? So the PRD actually will import all the tables, all the views, the phone shop and stored procedures that are in your database in total. So right now we only have some tables which are courses, lecturers and students on By default, the tables will be included. All right. So we can just go ahead and click. Execute. So they say that the three I selected if you want to de select our are not choose when you can just click it on But that arrow and it will bring it over. So it will know that three are in total And to have been selected for the modeling I exercise. But I want all three in the modelling exercise. I just leave that as it is on. Then I'll go ahead and click. Execute on. Then this will do something in the background which I really want to see. Cider stick. Next on. Get rid of this on the screen by clicking Finish. Andi, I see here that Okay, I have one table somewhere on. Then you can have a visual. Cue us to where your tables are on the sheet off paper so I just screwed on you See that? The next one is there. And as you move it, the visual cues off the things. I want to see all three of them beside each other. Senators drive all off them up to the top on. You would notice once again that we have no lines because we haven't established any relationships between or tables just yet. We'll get there. But I just really wanted to show you that my SQL workbench makes it easy that having created the database, if your supervisor wants to see it on paper, you know he doesn't want to go into the database and log in to see Oh, you did this and that. How? What are the rules? So this is a nice, easy way to just represent your database that your supervisor who might be I t savvy or your manager who might not be I t. Said we can have an appreciation for your database design and all the entities on the columns are data points that you're storing per entity. Also, you would notice that the icons are there, represents in the primary. Keys on day are color coded, according to when we were initially designing this database so we can have some visual cues as still what is knowable on what is not now. The cool thing about this is that you can also make changes here. You can actually add a new table on to this onto this. She'd right here so I can go see Table one on Die can start putting in all the columns just like we did when we were designing or tables using the workbench interferes. We can make all those modifications on, then effect those changes in the database. So you have some people who aren't so first on using the scripts to design their database. So they rely on the data data modelling tool provided by workbench, which allows them to this click on, put in the data and then have this visual representation on then the generally the database from the already generated model. In this case, we're already built our database on. No, we're just looking at the model for it. But later down in this listen, we will be looking at how we can go about generating the database from a model that is designed in this interface 13. Forward Engineer Data Model into Database: you guys in this video, we will be looking at how we can go about forward engineering a data model into a database . No, like we would have discussed. The data model is a visual representation off a database and all its objects, including tables, views, start procedures and functions on. So we would have created a database before no and then reverse engineered it into that bit a model representation off itself this time. Instead, off writing scripts are going into the user interface and creating the database upfront. We want to actually take it from the modeling perspective because everybody has their own style. Some people do better by modeling it before they generate the database, and some people do the database on. Then look at the model. Just verify what they have done. So in this video, we want to cater to those who are visuals first. So from the my esque, you'll were bench home screen. We can go ahead and this air de looking icon Andi. I have the sample model here, but we'll be creating our own, so we just think that plus sign decide the word models, which will bring us to an interface that is, well, pretty blank. But then, if you take the time to just appreciate what is there, then you can see that it is not very scary. So, firstly, we want to change the name off the schema or database Onda. We can just right. We can just double click on this top here, and then it will bring up this menu, allowing us to make a change. So let's make a simple Amazon clone some which actually call it Amazon Clone on in Amazon Flown. We'll be having some tables, toe track customers and their orders. Now, after we renamed the Schema, we can go ahead and start creating tables. So the first table I want to create is customer Seiken. Just double click on this table I can hear, and it will bring up table one by default. That's actually just default. Andi below. It will allow me to start editing, so I'm going to call it customers on the first day. I want an I D column, so it's already generating one for me I d customers. So my naming convention would have me that just say I d or customer I d. So just say I d leave it as an injured or a primary key and also added to be a also incremental, and I'm going to keep this one simple. So I'm just going to see email address on full name. So for our customers, we are storing their email addresses on their full name. So that is one way to create a table. But then that way is not the visual way that I would have sold to you within the first minute off this course. So that is one way we can use the workbench, interferes to create the table without writing any court. But then we did say we wanted to do it. Viz offs. Visuals first. So what we can do is actually at a diagram by double clicking this command. This is at Bagram on, then that will actually give us this blank template to allow us to start adding tables and such so to the left on the catalogue tree, you see that you actually have your schema almost on cruel on. Then you have the tables listed and we created customers. Just know if I wanted to see customers over on this canvas, I can just drag it and drop it on. Then there we go. It generates that entity representation off it here for us to see if I wanted to add another table to this blank canvas, I could just click this icon here. That's his place. New table. If he over over, that's just that square. With that grade Onda click anywhere on the canvas, and that's where it will drop that table. And then if I double click on this newly generated icon or are entity representation, then you see that the menu comes up, which is pretty familiar to us by No so I can just see orders and that's the name off my table on. I can start adding column so I D on. This is my primary key, which is also incremental ing see, or all orders of a chord. So maybe order cold or order number. Andi, maybe a beat time for the order aren't another Seems that data type on. We see a full complement and listing off data types here, so of course you want to choose appropriate one. So this time it's beat time. So it's the time, so I'm going to do this Oh, all right, So daytime does not take us size so far. Chart takes a size Daytime does not. For some reason we click bait time. My skill is giving us this These parentheses. I'm sure it can put values in there. But you really don't need values because you really just want at the time. So you can just remove those parentheses if you got that error that I just got and you should be fine. But I'm going to rename this to order bait so that we don't get confused between the data. Type on the column Name Onda. If I column comes up that you don't want, you can just right click it on. Did say lead selected, and it will remove it on you see that? Whatever changed me. Don't hear it obviates that visual representation pretty much in the real time. No one other column that we probably want in order stable is our customer. I d. And I'm going to just add it here. So that's a customer. I d. On the customer. I d would be off type in snow. I'll explain the purpose of customer. I'd even though I won't get into too much detail because this is not the lesson for it. But do recall that when we started off looking at relationships on our data is related to each other. We did say that we have a table. You can have two disparate sets off data, so we have a customer stable and we have an order stable with two entirely different sets off data. However, on order generally exists if a customer made it. So we need to be ableto associate this order with a customer now, instead off repeating the email address and full name off the customer every time. Because if I meets and orders, then that would be 10 records in the order stable each with my name on email address being repeated instead off repeating all that data. What we do is create what we call a foreign key inside the table that has that dependency, which would be in this case, orders depending on the customer's table. So customer I d. Would be the representation off a customer in the orders table. All right, so that's what customer I D is here for, so I'm just going to leave it there. We looked at the data model for the world database, and we saw lines. I'm not going to go ahead and draw lines here because this is not the listen for that. But I'm just letting you know that this is going to come so you can just prepare yourself for it. But essentially, what I wanted to drive home was the ability. Or is the ability to go ahead and create tables and columns using this graphical user interface method and this visual method, and you can just add as many tables as you need on then, if you have no use for it, you can just click it and press delete, and you see that over in the catalogue tree. It gets updated as you make changes here. All right, now, when you're finished with all of your changes and you're satisfied with your models off what is not our final work, but for no, let's just work with it. Then we can just go ahead and treat database and click forward Engineer so forward engineer will look similar toe reverse engineer. Where will ask us to connect to some instance In this case, I leave that local default instance that was given to me on die. Click next on. Then they will ask you for a few options. I typically don't check any of these because whatever I would have done in a model would would kind off what? These options we kind off be asking you, Do you want to do everything it did in the model? So I'm very deliberate in designing the database. Whatever I would have designed is what I want to keep. So I'm not going to take any off these. I'm going to leave this screen, as is, at least within the context, off this lesson, and then we click next on. Then it will ask us, What do we want to import? So right now, we only have two tables. But if it is that you had a view on a routine, a trigger on other objects, then you could just take all of them on. They would make sure that when they're generating the database, they would actually include all of these four creation. So right now I only have the tables. I can just antique these. It's no harm if you take them or and take them. Since there are zero and then we click next. And then what happens really is that it uses this model to generate cord that is similar to the court we would have written when we were actually creating a database on. We can just scan through this because at this point we should be comfortable with the court . Andi, Like I said when we were doing that lesson it, we can use the word database or schema interchangeably. So we said, Create database, they say creates schema, but the end result is the same on. Then they add on this part, if not exists so pretty much. They're saying that if there is no scheme on by this name, then created, if there is, then there will be no creation again, so it won't repeat itself. Or this is a nice way to skip on error so you can actually use this in your script. If you're creating a database or a table once you're seeing, created, can just see creates see table, if not exists on that we use keep any. This already exists. Errors on this is just using some default characters that I guess, for Western, according on. Like I said, when they generate the court, you see a number of things that you probably didn't write, and I probably didn't assure you. But the end result is pretty much the same. And it's good to have an appreciation for all sides of the fence so you can go ahead and just take times. I appreciate all of this, but the end result is that you should copy this the clipboard I usually copy to Clipboard. Just in case. You can also say that the file. So this would be good? No, if you're doing it on your development computer, but the real database will be deployed on another computer. Then you can to save this the file, which will just save an SQL script, which you can just reopen on the other computer and execute. All right, so it's allowing it to export this creation operation to another machine, if you so desire. So since I'm on my machine, I can just copy to clipboard, and then we could next. But it really go ahead, goes ahead and comics on makes those changes on. When we click clothes on, we go back on. We try to connect or default. Instance then we see here that are newly created. Database is listed here. All right. Sorry. A copy to clipboard. Just in case off anything. I usually do that once again. You could also see if the fire justly oven archive or for further use. But the reality is that once you forward engineer, having created the database using this, er the approach you can always forward engineer your database on it will deployed on whatever instance you have selected. So that's just how you go about generating idea Toby's from a data model. 14. What are Relationships, Foreign Keys and Normalization: Hey, guys. In this video, we will start looking at relational database design as relates to relationships. Foreign keys, Onda Overall normalization now on the screen you'll see at these of is that I developed sometime in the past and you would see that we have an entity relationship diagram as well as a bunch off lines that depict relationships. Now the database being depicted here is really an accruing database for an INSTAGRAM application on you See that I have here the mean table being users, because without users there would be pretty emotional instagram on. Then from the user stable. You notice that there are a bunch off lines highlighted when I hover over that entity. All right, so each of those lines depict relationships, and what you call dependence is that users are other tables have on the data phoned in the user stable. Now, before I start dissecting the databases design, I would like for us to discuss shortly what normalization is now. Simply put, normalization is a process by which you eliminate redundancies and repetitions in your database. No off course you'd probably asked, okay, what would be a redundancy or petition and let's just eat this database here that we have one screen. Andi, look at an example. Off photos. Know if anybody watching this video has ever used INSTAGRAM, then you would know that a photo has re posted by a user, all right, And then a user can have many photos. Now, we see here that you have, ah, database for the users, and the user would have signed up there putting the user name on their password and their email that percents on on this is a rather skilled own version off that, but we can work with it for no. So a user would have put in their user name, and we would have taken a time stamp off when this user created their cones on. Then, having registered, this user can go ahead and start posting one or many pictures on the pictures or photos will be story in this database where we store girl because instead of story, the actual image word restoring a path to the image that we just, you know, pull back when we're displaying it on the application on Do you notice that this role here or this inter here in this entity, user underscore. Idee has a kind of red thing beside it on. That's what is usually used to peaked dependency or a foreign key. Now the scenario would be that every time somebody posts a picture that you would say, OK, new picture with a new I. D. You post the image rural on. Then let's say my user name would be T. Williams than you would say T. Williams and then opposing another one. Then you have T. Williams, and then you have a bunch of T. Williams is on. Then if there's something wrong with the court, maybe it would be T. Williams up top on TNT. Williams one below on. Then something could bore on the whole text. T. Williams would become kind off tedious to maintain right on dso way. We want a clean way to associate a photo being associated with my user on. Then. That is where normalization comes in because when you realize that in associating records in one table with a recording, another table kind of gets missing, because maybe you're repeating unnecessary data or maybe after 34 times that the data seems unnecessary to repeat, then you would normalize this database by taking all this seemingly repeating data or potentially repeating data, putting it in its own table with its own primary key in its own table on. Then you just referenced that primary key were unnecessary. So in context, when we have a user posting a picture instead of repeating the users user name every time because something would go wrong and it's string, we'll just reference their i d. So when I Maybe I was the first person to ever signed up for Instagram, my idea would be user i d. One. When I post my first picture, it would be user photo I d one with user I d one All right then I maybe post a picture, you know, down the line and it's the 200 picture on Instagram. Then the photo I d would be photo I d one and then the user. I d is still one. So if I look in photos for all of the pictures posted by me, I can just see give me back all photos where user i d. I went through select statements with our conditions and so on, so I could just say, give me back select or select star from photos where user underscore idea is equal to one. And that will much back all of the users from the stable with user i. D. One and us, much them here on, bring back all of the pictures for user I. D. One. All right, so that's a nice, clean and easy way for us to just minty in this referential integrity. On I spoke about data integrity before in this course that that is basically just maintaining that your data is screen on your data makes it easy for you to associate and navigate across different tables. So in, in doing normalization, essentially, what you're doing is establishing foreign keys between tables because when you abstract the user information from your photo stable, or maybe the photos information from the user stable because maybe your design initially had you trying to associate the photo with the user in the same table. Then you realize that you have to be repeating the using him in the user's table when your user stable should really only have one instance off a user. So then what you do is extract all the repeating photo information on put it in its own table. So in the process of normalisation, you know, it's kind of a break excitable. You get it right, you may get it wrong initially on, then you correct it later on. Um but then for the remainder of this section, I'll just be showing you how you can go about doing it right the first time to reduce that break fix cycle. And that repetition prestige process. If you if you read upon normalization online, you see a number of scholarly documents talking about the first normal form and the second normal form on that's really useful for when you get up on initially, badly designed database. But then, as the designer, I put the onus on you with responsibility on you to just do it right the first time. So if we take a closer look at the other tables here, we see that we have lines that depict which table is related to which, and in the context off my sq wells E. R D representation. You knows that you have kind of doctored lions versus solid lines. So the solid line means that the I D or the foreign key is not, is not optional in the related table, whereas the dotted line means it may not be necessary. So as the designer, you can set that up so I can tell you right now that this was poorly designed because I'm here. Seeing a photo can exist without a user. And that's wrong because the foreign t from the user table to the photo stable is user i d . And then if you hover over the line, it will show you what the primary key is on one side on the foreign keys on the other side . And then the dotted line means that the foreign key might be empty, which is wrong because Instagram really should not have on image unless it has a user to associate with this image. So that's wrong over on the other side between users and lights. If you hover over that solid line, then you see that the user I D. On the likes side is the foreign key, which is associated with the I D in the user stable on its solid because you can't have somebody liking something without a user. Well, you can't have a like without a user on in the same way I like only apply so forth. Or so we have likes being associated with photos on then. This is what you call a linker table because it is storing to foreign keys. It has no primary key for itself. But it is saying that this user depicted by user I d here like this photo. So it is. It is storing the foreign key for the user table on the foreign key for the photo table. So, in a nutshell, that's what a foreign key does. The foreign key is pretty much just storing the value off a primary key from another table on then. That brings up another thing when he released the integrity. Because you can't or shouldn't under any circumstance have ah, foreign key in a table that does not much back to our primary key in another table. All right, so you can have used that you can't have 10 users in the user table, number one through 10 and then have user number 11 liking photo number 250 when they're only 200 photos and 10 users. All right, so that's another aspect off the foreign keys where the values of the foreign Keys most much back, So the values in whatever primary key column is being referenced in the other table on. Finally, once you establish that foreign key presence in another table, then you would have created what is called a relationship. So just by creating this foreign key for user I d. Here and user I D. Here on defy over over users, you see all of those highlighted lines. Those are all the tables that depend on the primary key values stored in the user's stable . Just by sitting off that foreign key user underscore idee in any other table. Then you would have established what we call a relationship. So for the remainder of this section, we will be going through modifying an existing table and are being foreign keys because for our school db, we would have had multiple tables on. I'm sure just by looking at it, you will be wondering, How do we associate the teachers with the students? Onda. Also, you will be looking at doing that, using the regular editor view versus the model design and creation view. So stick around exciting times are ahead 15. Create Relationships with Data Modelling: he guys in this video, we will start modifying over databases so that we can prepare them toe, have relationships and foreign keys. No, on the screen. I have the entity relationship diagram for our database that we created. I'm Amazon kun. I'm just going to use this database and modify it a bit just to depict the differences and the intricacies when considering what kind off relationships we need in which situations. Now let's see, we are here finishing up this database on. We realize that there is a missing table and that table would be products because we have customers on a customer. Makes an order. But what do they order? They have to order products so we can just add a new entity here. Onda. We label it as products. So I just took the time and I hope fitted this new entity with all the properties. It's a products table it as an i. D. It has some product called a name and a description. Now, in the same pain that are lotus to create a new entity, we see if we go just the little down a few Akron's lower than you'll see that we actually have some options for some lines to be drawn, and I'm going to walk you through what each option is. We have a 1 to 1 kind of relationship where essentially an entity is only a low to be associated with one interview at a time. So let's say, for instance, you run a private school where only one student is assigned to one teacher. So one teachers hired to deal with one student that would be a 1 to 1 really center. So if you had to create a database, the facility that kind off situation, then you need to use a want one. If it was a case where one customers only allowed to place one order, then that would be a 1 to 1 relationship. And so what you would do is just click on the line and then you would say, OK, what table do I need? Associate with another table. So if we need it to our associate one order with one customer, then having clicked this SaiPan here, I would click the entities on. Then you see that it would generate that line with that one toe one line. Andi. It would also just put in a new property toe facility that no, we already had something cause customer I D. We could delete it. I mean, it's just a design. We put it in the design, but it is not one to assume that that's what that's why this is there. So just by virtue awas establishing this relationship, he creates the line, and it creates a property to facilities. This type of relationship. Now the next type of relationship that is very common and probably the most common one would be the one to many that would be depicted by a one call on and on because that notation, so to speak, that's relationship notation where it tells you the level off or the title for the No. One. To many relationship is more like what we have here with customers, and orders are given a teacher and student relationship. In the typical school you have one teacher on or you have one customer on this one customer can have many orders so customers can have one order or they can have any number of orders . And right now, I'm sure any customer and Amazon has many orders, so Amazon would not do very well with a 1 to 1 type of relationship between the customers and order stable, so the same principle applies. If we were to at this kind of relationship, we would just get the icon on. Then we would click the table that we want to associate first on, then the received being associated table. No, let me just explain why the I d Oil foreign key gets generated in the way we got generated by seeing by cooking orders. First, we're seeing that this is the side that has a dependency on this side because if you recall from the previous video on order cannot exist without a customer. So the order stable for any record to exist in the orders table, there is a dependency on customer stable. And once again, instead of repeating the email address and a full name and all the customer detail each time there is an order place, we just get a foreign key reference, which is customers on the score idee on. Please note off this naming convention. You can name it anything you want, but there's really a naming convention that is there that helps you to know exactly what table this foreign key belongs to, so customers is usually the table name, and then we know that it's the ID column. So this naming convention is really good when when it comes to helping us identify foreign keys. But then what I want to their home, really is that by including the i. D in the order stable, the idea is a low to repeat as many times in the order stable as needs be. So if a customer has 10 orders for each off those 10 that customer I D number would be actually the same thing over and over and over and over for the 10 orders Associate ID with that customer so you can look at it as an association were associating the order on this side with the cost around this side by having the foreign key, Or you can look at this as a dependency on order needs to have a coastal er, so we put that dependency in. Now the next type of relationship that is very common is many to many, and that is depicted by this line here with en tow em. And if you just hover over them they will show you something good. It's not very informative, more other than what you're seeing as a notation. But I don't like using any direct relationship between two tables for the many to many. Instead, I introduce what you call a linker table. Now, a situation that would require a linker table or many to many relationship would be a situation where you have a product table on an order stable. No, your products would be a scale your entire catalog off. What's available. This is what Savile but Amazon. Every item on Amazon goes into this table OK on. Then every order police by a customer goes into this table. What we don't have, however, is details on what is being ordered by the customer because for one order, you can have many products on then. On the flip side, many products can end up on many orders. So if all of us go on, I was on right now and order piers off shoes and people on us on some shirts and some pants plays off pants or on the females with their slippers. A lot off. Those orders will have overlapping products, which means many products will appear in many orders on one order or many orders can have many products. So then the fact that many can be on either side makes this a many to many relationship. So in order to facilitate this money to many relationship, like I said, I don't like using this relationship indicated that they gave us. So what I do is introduce a new entity and then we'll call this one products underscore orders on DNO We will put in the properties for products on the score orders. No, with the linger tables, it is not absolutely necessary to have a primary. He you will see it sometimes. So I'm just going to live by what I've been preaching. Always have a primary key, some just adding in that I'd column on that altar implementing so that we have primary keep . But the main purpose off the linker table is the store, the foreign keys off to different tables that need to be related to each other. But we run the risk of having that repetition. So we put in another table that facility. It's repeating the foreign key values. No, let me explain that again. The scenario is that we have many orders that can have many products on. We have many products that can appear in many orders. No, if we were to try and relate these tables to each other than we would have maybe orders having repeating details off products every ever, so offer all right and that is what we want to avoid. We don't want orders to have unnecessary information on this, the same we we if we toward to put orders on the products, they would have the shirts or that particular product repeating itself for every order that it is associated with, and that is very dangerous. So what we do is introduce a new entity which is going to associates the products with the orders, and I'm calling it products on the score order so that I can easily identify that this is linking these two tables and so that's why I keep on stressing the naming conventions that you use on your database objects and know what I will do is at a one to many relationship for the products to this stable on a one to many relationship, from orders to this table so one product can appear many times on one order can appear many times on then, for each record that causing would have a product. I d andan order I d. So that we can see that this product is associated with this order on. All we are storing or need to be worried about is the foreign keys for either. So I'm going to go ahead and click my old one. Colon in Icon and I went to start off with the table that has the dependency so that products on the score orders has a dependency on the product stable. And so we get a products I d introduced, and that's our foreign key value from our products table on. I'm going to do the same here. I just think that icon again, I'm going to start off with the chamber with the dependency, which is products on the score orders. And I'm telling that's it also depends on border I D. Okay, so we got 1/3 column being generated here, mainly because it realized that the table that we're linking this to also has another foreign key in the form of customers on the sport. I d. So it is telling us that will give us a foreign key that would store that budget. No. While it has the best intentions in doing so, this is also as a result, what this will result in some unnecessary repetition. The reason for this is what this new road that we didn't ask for or this name column rather that we didn't ask for is going to be storing. Is the customer idea associating with each order. Now, remember, we're store in the product. I d I Western, the orders I d on. Then this one was given to us to help us to know that we're storing that this customer made this order with this product on. Then if you if maybe they made a few orders than our even one order with many products as many times as products I d repeats with same order I d. Than the same customer ideas been to repeat on. The reason I say that this would cause unnecessary petition is that we already have an association between the order stable andare customers here. So we know that any order with I D 10 for instance, is going to belong to this poster. So wherever 10 appears in this with whatever product I d. We already know by virtue of the fact that we have on association of here who that customer is are we can find out easily. So we really don't need to repeat that customer one made order number 10 with 10 products and then we have customer number one repeating each time for each of those 10 products here again when customer one is already appearing here. No, I know what I just said was a mouthful on. That is why normalization is usually a very are Dios task on it requires you to have a keen sense off deeds of bees integrity Onda, you know, reference of the integrity. So because I am a practitioner, I could see it from day one on day. I hope that my explaining why we don't need this column makes sense. So I'm just going toe remove this column because I don't need it. I really just need this The link my orders to my products on then this order stable has the details off the order which includes the customer I d. If we need to see who made this order. Likewise, the product stable has all the details on each product that is in our catalogue. Onda. Whatever product is being referenced by its i d. Here we can always go and fetch the details in the other table. No forwards a forward engineer this mock up, it would generate code which would then generally the database with all of these associations and foreign keys built in. I also need to mention that from this design view on door modeling view, this is actually starting off a project. So when we actually go to home on, say, we want a new model, we start up our project where we can have multiple databases. They get this one by default. But then as many as you you want, you can add for the screen and have these models on as many er diagrams as you need on. If it is a case where it's an ongoing process, maybe like we're not finished with this database right now, then we can always go on save model us on. Then this was a loss deceive on MWD file, which is workbench model style, which we can always just reopen on. Continue over on the database just will not get created until we forward engineered. All right. Okay. So thank you for watching on guy. Will include this model file with the resource is for this video on die Trust. You enjoy this. Listen, the next lesson we will look about modifying or school database that we already have on starting to add some relationships to its. 16. Create Relationships with Workbench Table Design Tool: Hey, guys, welcome back in this video we will be working with our school database once more on in this situation, we will be modifying or altering the table to support foreign keys. In some tables, you'll be adding more tables to this database. Onda. We just see exactly holy global using work bench the facility it off these modifications that we're about to me now. In our previous video, we saw that when we were using our designer view on, we went ahead and used the relationship tool to create a release such a even though we had already designed the table with a column that we had earmarked to be the foreign key column , the designer went ahead and created its own so we could actually just get rid off this column off the order stable on It wouldn't be too detriment of anything because the designer give us its own column for the primary, sir, for the for foreign key. However, when we're using this design view with an already existing data bits that we want to modify , the onus is on us to actually create these rules. These new columns sorry on actually established them as Foreign Keys. First order of business would be to run the scripts that are included with this video on Go ahead and inserts on lectures and some courses out of the spring. Of course, it's quickly, so I took the liberty of entering some courses. You can enter your own if you wish, but I gave it the script so you could expedite that process. And no, we need to take a stock off what we have and what needs to happen. No, we're doing a school database. We already have three very important entities for any school toe. We have students, we have bleachers and we have courses. So we have people to be thought, people to teach on what to be thought. However, what we doing tough is any way off associating these three entities with each other because a class has all three elements in a class, has students. A class also has a lecturer. At least one letter on a class is centered our own, of course. So that means we need some form off entity that will embody class, which is when you have the associations for the students, to the literature to the course being taught? No, the lack off Bagram or the visual representation might be kind of a turn. Tora discourage discouraging factor when dealing with us, modifying tables and putting in relationships. But it is something you have to sort of through and learn to be comfortable with. As long as you understand normalization or rather, whole. You go about associating on entity with a dependency. Then you should have no problem. That being said in this file, I am depicting something that just occurred to me in talking about how we're going to include additional tables. The facility of the relief son ships. No, I did see a class would be a merger off a literature student on course. But then it occurred to me that if I have one table where you have many students, who even many students I ds on, then you're going tohave one course that I mean, all of these students are taking on one lecturer. Then you're going tohave many ideas and repeating courses on lecturer ID's for many students, Andi so that that while it could work to me, that's just kind off unnecessary repetition. Andi, I must warn you that this kind off a diagnosis off. What is unnecessary Repetition can lead toe what we call over normalization, where you tried to breathe on one table into too many small jewels on, then end up with too many touchpoints and potential points for failure and Burkett and but data. So there is, Ah, middle groaned under their balance that one must have when breaking down what needs to be related to watch and holds a facility of these relationships. But in this situation, because I don't want the lecturer and course ideas to repeat so much, I'm going to have one entity called Class, which is just Mento have the lecturer on the course or courses, so many electoral realities and many course ID's. This is a linker table now between the lecturer Table and the courses table. Because one lecture can be teaching, many courses on this seem course can be taught by many lecturer, so we can have three lecturers. Teaching into the programming on does seem. Three lecturers are teaching other courses. All right, so that's why I'm creating that linker table. This for these two on you could pushes that step further if you wish, and you could include a room, which would then Because then, if the lecture is teaching a course, then they must be teaching it in our room. So then you would put in a room entity which would have an idea and maybe a name. All right, so you have a room on a room stable or entity on each room gets its own. I d on its own name on DSO class would be a lecturer teaching this course in this room on. But, I mean, the idea they're just coming to me at this time. All right, so that is essentially what the timetable looks like. Write a timetable, is going to show you who is teaching what we're and at what time. All right on then, on enrollment suggests that a student saw a class at the he or she is interested in. So the student, which will be referred to by idea and where I have ref means that it's just a foreign key reference. All right. And that's just my notation. This isn't necessarily standard. Understand this to depict something, Teoh. All right, so a student would see that Mr. Look at my lectures, Mr Harold Benedict Charles is our child benefits brother is teaching introduction to programming in Science Room one at 4 30 PM on DSO student One is interested in this class. So we just referenced the class by I d on associate with that student. And so instead of having two columns repeating which would have been lecture on course, as many students as well subscribe to the same class, you'd have many students ideas referencing one trust I d. And then we can always look at the details off the class by going back to your class table looking for that I d. And then we can further see the details off every other referenced entity by going to the original table and looking at the Aggie on as we move along, we will be looking at how we go about extracting details off related data. All right, so for no, we just want to set off this kind off relational system in our already existing database. So the first team I'm going to create is a class Steve, and I'm just going to use the editor to do this. I want to create a script fast. I challenge you to either follow along or transmute on your own on. I'm calling this one class or classes because I like giving my tables. The plural name right on the first colon is an I D column, which is primary key auto implementing on notes that you don't always need a primary key auto implementing when giving the linker table. So this is a linker table he really doesn't need. It's own I D. But I'm going to practice what I preach kind of good to put in already on. Then we have lecturer idea, so lecture on a square idea and this is a naming convention because we need to be ableto just look at the column header and no, that's what it is. So by just seeing lecture on the square idea, I know exactly that. If I see any value here, it is referencing some lecture in the literature stable. Also, the data most much so because or primary keys, are all integers in, at least in this situation, because people do use things that are not introduced for primary keys. But in this situation, whatever our every situation, whatever the original date, the type of the primary key is the foreign. He must be the same. So in this situation, we have int all right. And then we repeat that feat. I'm also going to set this than not know. Well, I'm not you into new does not, as I want to make it knowledgeable. All right, So that means we can look at the timetable from time to time and see that a class at courses scheduled to be taught in a certain room at a certain time, But there is nobody assigned to it just yet. So that's another design consideration went to me Something no level on in this situation. Lecture i d is knowledgeable. So courses on the spore i d. And once again, this most much the data set on, I'm going to stop with those two. I did include room on time, but then that would require me to create the room TV land on. Then we can put in the time just the same. All right, So put in time and I made it data type time. And remember, if you selected from the drop down, remove the parent to seize on, then I ended up with a random column that's understanding, right? click on Delete Hard, so that is essentially it. On day I'm going to me these tools not available so a class can't be created without at least a course on at least the time it's to be taught. But we can be still searching for the lecture even while we have scheduled the cast. No, in orderto associates, these two columns with their respective primary key tables. I'm going to go down to the bottom parts. The bottom set off tabs in this interview on day I see here that I have columns indexes on foreign. He's now. This brings me to a new screen where I am asked for a foreign key name on. Then he talks about a referenced table are so the foreign key name for me. I usually give it a name that helps me to know which two tables are involved in this relationship. So this would be classes on the score lecturer. There's no real. There's no real stipulation as toe watching most name. It's This is just a practice that I use once again. So if I look at it tomorrow, I can exactly know what this foreign he meant on what this this relationship is for so after putting in the foreign key name, I no need to say which tape is being referenced on the referenced table in this situation. Because I named it. Classes on the store lecturer were already modifying the classes table. So that means the referenced table must be the literature stable. All right, on then, over to the right hand side. While having this this foreign key entry still selected, we'll see the list off Followers of Europe tours because off what we're creating in our classes table. No, the call him to be the reference point from our classes table for the classes to lecture relationship would be my lecturer, I d. So I take that on. Then I go ahead and let them know that referenced column meaning which column in the lecturers table should this reference? Andi, That's easy, because it's the idea. So the lecturers that the idea sorry from the Lecturers table is supposed to reference the lecture on the score i d. From over classes table on that foreign key. That whole relationship will be called classes on the score lecturer and then to the right . We see that we are foreign options. We're on a bitter on the Leeds. It's is no action. Really right? No. But then you can change the cascade or strict or said no. Meaning that if something on either side off the relationship goes missing or rather if the source data gets augmented, meaning if the source data gets deleted, you delete the lecture. Then do you want us toe? Delete all of the associations of this lecture. So everybody who has some relationship with Elektra table those records would also be needed. Or do you want me to just sit that failure to know, or do you want me to restrict what you can do? So no option means that if if anything happens to sleep the data as it is no. We repeat that feat with the next column, which is courses. So I need a lot of foreign key name or relationship me and this is glasses on the score courses and this time or referenced table would be school on a score db dot courses on, then our column. That should reference that is courses I d. On the reference color will be course I d. So you see that the names don't really matter much is just a matter off you keeping up with whatever you're doing and whatever the design is. And if you have a standard stick to the standard. All right. So I used just simply I d for the literal civil and I used horse idea for the course is stable. But either way, the name is your it about the factors. Either one is acting as a primary key in its respective table. On day, I am making sure that I'm referencing them with the foreign keys in by resulting dependent table are. So when I'm satisfied with my design, I just set up the foreign keys. I have all my columns, and if I go back over, you see that you have that read prison to the side, showing that okay, there's a relationship here. One is filled where one is blank and blank, Wallace, because I'm going to be invulnerable, whereas this one most exist for a report exist. But once I'm satisfied with everything that I've done like in the strict apply and then that will generates this block off court, which actually recreates the table, so to speak. No We're seeing that we have a bit more court here because yes, way should be familiar with all of this. But then we're adding what we call an index, which is a high speed local point on the tables. Primerica is always an index on by default, any foreign key will also become a need. It's all right. And they were adding the Const Reince. There is a name that we said classes almost for a lecturer. So the foreign key will be referencing the ID column in electoral stable. And it just feels about all of that. So you can take this on, be unhappy and piece it in another editor and just assess the cool that's being generated. But ultimately weaken the squeak, apply and then it will go ahead and execute that script on ultra, or it it will create our table with those on. It will essentially establish that referential integrity that is implied by or foreign keys . Now the next table that we have on our reader is enrollment on enrollment as a dependency on students on a class, someone to go ahead on, do the same thing, just go to tables, create table no went ahead and killed old the table name on the column. Names on the data types and I'm eating everything not know are not knowledgeable because it doesn't make sense that open enrollment without a class being referenced on the students being referenced and it's that spending your there are on. Then we settle for four, and he's just the same way, All right, So using the same naming convention, I associate the home table with the primary key table or the foreign key table, the Primerica table. On day I referenced the table accordingly, and in the columns and SNC of the columns aren't on. Then one some satisfied with all of that. I just pick up play. I can review the court books a speak, apply once more, finish on. Then what happened just know was that there was an awe creation off foreign rule and stable , and it just appeared over here. No. The next thing that if you want a visual representation, you can always go to database and you can reverse engineer or school devi database speed to that er diagrams streak and see all of the lines on the actual relationships that we just created Andi. In our next video, we will look at how we go about inserting data because if you we're being eating off attention to what we were doing, all we have at this point in classes and enrollments is a bunch off columns that are just supposed to be taking numbers. So let's look at enrollments. Enrollments have student I D, which is an integral Class I D, which is an integer on daughter in printing into there. So you moved away or we are slowly moving away from having these fans off, you know, data rich on clear tables where we have last name first, me and Roberta toe tables where we're just looking at a bunch off in teachers, all right, And so if I trying to type something here, that's 20 gives on arrows. I tried to apply because clearly that gated type there's not much what is expected because they're in teachers are on just the same way that I cannot enter a class or student I d. That doesn't exist because I have classes that are bringing like that's a stable. I have no classes, actually, So if I try to enter a class I d here. And I know I have a student Have student with I D number one so I can dispute in student one. If I apply this, I will get an error because it's going to tell you that the show notes had, uh, Well, it seemed that the constraint field, because the class I d. Holum value most much some value in the classes primary key, the Primerica in the classes table. So as a result of the fact that we don't have any classes right now, we have an empty table for classes. There is nothing for a turf assuming bring a plastics quickly, and that's empty. So there no class sister reference. So there is no way I can enter an enrollment for class symbol one when there is no class number one on. That is what a foreign key and relationship bring Steer table on. That is what we mean by referential integrity. Where did that cannot exist? Hardy, that can't exist in the depending table without it existing in the parent table. So our next lecturer, we're going to look at whole Wiegel a boat, inserting reports in tables that are, you know, have a dependency on another table 17. Insert Records in Related Tables: Hey, guys. In our last video, we looked at adding relationship store existing tables, namely students, lecturers, Andi courses. And we also added three new tables well to new tables at a time, one for class on one for enrollments. Now, in this video, we will look at actually adding deter to the class on enrollment tables. If we take a look back at student, we already have data for students already of Dayton or electric stable on. We already have data in our courses table, and we can just look at this data and it looks rather sensible. Wherever 90 we have a title, which is in standard in this way of number of critics, rebel course cold. Everything here is actually human, readable and easy to understand. At first glance, the challenge with the relational database over is that once we start introducing relationships into the mix, Andi, let's state the class table. For instance, you notice that all of these columns, except time, are actually reference columns. I explained to the last video where you see ref, that really means foreign key. So we didn't do room so you can go ahead and do room. I leave that challenge to you. But we would notice that even enrollment really just has a reference to the student table under referenced of a class stable. Which means that we are trying to obstruct the fact that when one student in roast one class that we would have to see Wilma McDermott's birth date of birth in room indeed, in rule toe data mining with three credits with core school. So instead of repeating all of that because we already have table storing all of that in the table story, only the reference what we'll do is just reference the key on. We already established that we have foreign keys in there. Norman table, which most much back toe the primary keys in the respective reference tables, someone to start off, and I'm going to populate the classes table. So I just go to class. Andi, I can just do this landing bullets agreed, and they will bring up on integrate because I have nothing in there. But we can just use this editor view to start inserting some classes. If you want to refresh yourself one. The design off the TV can always just over over the table name and he just quit that information icon and you can just go to columns, and then it will show you the data type. And remember, all of these are inter just ideas. Bremer. He which is also implementing Andi. None off them. Well locked lecture ideas, actually notable. So that's just the in form whole. We're going to put in data in this table. So when we're going to be populating classes, that means I can leave the lecture ideas? No. So the the referential integrity on later idea lows order the referential rules around the integrity off the value that goes into this column alos no are. But then I can't put a value in this column that does not exist in the ID column in Lecturer stables. I can't put it, but I can't leave it. No. All right. So I'm going to go ahead and I went to feeling about eight rows here on Just reconvene once I'm done. Alright, So I've gone ahead, and I did some columns here. Some rose actually on you notice that I am leaving the top three as no for the letter I d. I just want to show you that you can actually leave this one has. No, since it's not level but any value that goes in. Most appear as an idea in the actual table part on, then once have added all those on for time, you are low to use the 24 digit notation so that 1700 really means $1700 which is 5 p.m. All right. So after adding some sample data on Deaconess, pause and go ahead and add these. If you wish Oregon idea on values, you know, feel free to your database at a slick apply, which will generate the insert cords for me and apply Onda. Once I'm done with that, we see that we have some classes that exist in our system. No, I mean clearly, these are just numbers. So if you were to just select star from the classes table year, we're going to see a bunch of numbers for each column, at least the way it's designed here. Unless you're going to add other details like you know, the wear of time. So you probably want to be off the week on time and so on to have a proper rust oring system going on. But either way, the main data, which is a lecturer on the course, this lecturer is going to teach That data is kind of abstracted upon first glance because we're using ideas to make reference to the actual details. Remembering of the enrollment stable we're following the same trend will know we're just obstructing and we're friends in the class. My i d and removed just entered all of those classes. So know where those referencing each class by its I d on. Then we're linking this instance off a class which is a lecturer teaching a particle course at a particle of time. We're linking that to one or more students, So I'm actually just been to let everybody here, all of these students that we have in the system being rolled toe cross with i d five. All right? So just enter those really quickly and I'm goingto go ahead and let's two other students do to other classes, someone to enroll them in class with 86. And I mean, I just looking at it, I can tell you who the letter is. Our which course this is a said. I'm just filling in this data as it appears. Some just putting in some, some pecol himself, ill in a row. Somebody in class with I d one also Andi. I just chose to random students or student number seven and student number 21. All right, remember, whatever valuables in here must be in the original primary key. Listening in the original referenced table aren't on. Then I'll just go ahead and apply, and it generates that. And you see, it's all it's doing is putting in the values. So I'm not repeating the details. You don't know these students are because, you know, we have a few students, but in its database off 1000 students, I'm sure you're not going to memorize the names off students one through 1000. So by using the i d. Were actually obstructing the need to know any details about the student. We just know it's a student on. Then you probably be wondering, OK, so isn't that going forward? This is whole. You would be entering dating a database. Well, the simple answer would be that you would have built an application on top off this vita bees on then what? You passed down to the database would actually be controlled from your application level so you wouldn't be. You know, somebody's loved in our students. Is love being you know which students is loving because when they log in, you'd have to authenticate them against their student data, whatever. And then you can track this students I d. On. Then, when you're bringing about the list off horses are classes that they can enroll in. You would have been bringing about, at least from this table on then. As we go along in the scores, I show you how exactly you can write queries because we've done select words before, so we'll see how we can write. Select. Where is That will actually bring about the details off what is being referenced when we put in these ID's. But notwithstanding that in a real world situation, you would have actually built an application on top off this, which would display the necessary details to the user so the user wouldn't be seeing five what they would be seeing. The details, which would be five, which involves Lecture one and course five. So then we can go and look at his lecture, one that is Harold Benedict on course, Number five deserted, still Internet authoring. So then we know that we would present to the user Internet authoring with three credits. That course school being taught by Benedict tiled. However, when they fill in that form and then submit that enrollment request. What we will be storing in our database will be Class five on students with the I. D. R. Whichever students has long been at the time. So in the next lecture, we will actually look at how you can go about extracting the details, the clearly necessary details on each of these tables and each reports as we delve deeper into release relational databases. 18. Run Queries on Related Tables (Inner Joins): Hey, guys. In this video, we will be looking at Hall we global, pouring from multiple tables that are related. Oh, as my way of quick recap. We just entered some records into our enrollments and our classes. Tables on these both east tables are related toe at least one or two off the base tables, which are students, lecturers and courses. So classes is related to lecturers. Onda forces depicted by the names that we gave or foreign key columns are on. Then Enrollments is related to classes as well. A student's all right, so no, we will start looking at whole. We can look at the details off the classes. So looking at the table here, just just like that, it's kind of difficult to decipher who lecture One who lecture three is on which courses they are teaching because we may not be in a position to memorize these ID's. So we want to look at how exactly we will go boats bringing about the details associated with each referenced record. Just a in this script file, which already has the fire the parts of acquired that these things like star from school on TV classes, a school levy the classes. Sorry on. So we'll just expand on this and we're going to start against a new keyword or a new concept in databases, which is called joints because essentially we need to join on the related records from the respective tables toe this stable. So the base off the quarries classes and we're going to be joining on the lecturer details on joining on the courses. Details are so we can just go to the next line on. Remember, you always need a cynical and I just remove it for no so it doesn't get in the way, But we go to the next line, and then we introduce a new key word, which is inner joint. Let me state that you have different types of joints and we'll get into the other types off joints later on. But you also have different ways to accomplish this kind off joints. I'm going to teach you a very standard with which you can actually apply to almost any other relational database engine, because the reality is that each one has its own quirks in its syntax. There certain things that my school does that Microsoft SQL is a different with a post grad school does another way. So But then this inner giant technique is fairly standard across all the platforms. Okay, so following our inner join, I'm them. I'm going to go very slowly here so that we can understand exactly what's going on. He states the table that we want to join. So we have one table which has the information we want. But then we need the details. So what table contains the details that we see? So the first day that we want details from would probably be our lecturer stable because we want to see who this lecture is. So I can just say inner join lecturers. I reference the table by name on off course. We're not using a using statement off top. So if I use the table just like that, I have to put on school the honest where DV doctor and you see the intelligence is actually helping us there. Any hole we say inner joint on. Then we state our table on. Then we see on. So that's another key word. So Pinar join table name on on Then we state the condition that the two tables must be joined on. I like to say, What do the two tables have in common? What two values do these two tables have in common? That there should be joined on. So simply put, it would be the primary key, sir. The primary key value off the joining table onto the foreign key value off the next table aren't so. In this case, the foreign key, which is lecture i d. Most have a matching value with the primary key column in our electorate stable. So these values are what they have in common on DSO. These columns are what they have in common. So what I'm going to do is say, inner, join this table on the condition that the I d. From classes and then I'm went after. Spell this out of bits and you can see ho tedious he can get when you're not using your using statements, especially when more tables are getting involved. Have to be seeing me. Toby starts table name and then dot again to get to the I d. All right, but then that's not it. It's classes, some inner joining on lecturer. I d being equal toe on then I went up to do the same again. School db dot lecturers dots on Then it would be the Eid. So once again, I'm joining these two tables on the condition that the lecture I d value that is present in the classes table marches is equal toe the primer t i d value in the lecturer stable. No, please, no. We have six classes listed here on when I execute this query, you're going to notice that we will only have three on. I want to do it and then explain why. So we're down to three and you would see because I'm selecting star. It's selecting all columns from our classes stable. It's selecting all columns from any table that is being going on on dso We have all columns that were there before on no, we have more columns coming back from or lecturer stable on. If you look at the values that are in the lecture, I'd ian from the classes set off columns versus the values in the I. D column. Coming back from the lecturer, Steve, you notice that they're the same on DSO that would kind off inform why we have three fewer rules coming. But then we had before. And that's because remember that we had no in those lecturer i d columns and I just quickly re execute that a Paul it is. Let me just retired that Quartey. Right There we go. So we have the results. It's like star from classes and notice where? At 63 off which had no a lecturer I d on dso because our condition for joining the two tables states that both values must be the same. They must be equal. There is no lecturer with an idea as no in our electoral stables. Of those three would have been omitted from our inner join operation. All right? And so when we go back to our results, we only have those three with the names on the details off each lecturer. So, no, I have the details on the ledgers, but I'm still missing the details on the courses. Some went, I have to repeat this line. All right, on going to have to inner joined once again. And if you just look to the land and press control will hold on control and press D, it actually duplicates that line for you. So that's what they just did on. Then we can just go ahead and modify. But then you see that as we start inner joining an inner joining and inner joining, the more tables that get into this query is the more I would say messy, it looks so SQL actually gives us the ability and the opportunity to use what we call aliases and would have gone through that. We're looking at simple, select queries, but we would have looked at them relative to the columns. You can actually use aliases on your table. Names also cycle car classes instead of writing school. That db that classes every time. I can just see. See. All right on then, instead of writing school, that BB that lecturers school DVD lecturers every time I can just say l or let me let me do it a bit better Second C class and I can see elect. All right, then instead, off writing on all of this once again, I can just replace all of this part that references the table and to see class darts lecture I d on d. I can just see elect that i d. So no, instead of having to write out, especially since I'm not once again using the using statement. I can replace all of this with just a reference to an alias on Then use this alias in the referencing elsewhere Someone to rewrite this last inner joint statement for the classes using this technique, some went to inner join were in adjoining classicism went to see Oh, sorry, I'm Inter joining courses. So courses on them would to use May? Yes, and I'm just going to use this. You can use off foursome or descriptive expression in your necessary could be very abstract , but off course it can't have. See, hear and see here that would cause a conflict. Right? So just be very just be careful with that. So we have horses core on. Then I'm seeing that I want to enter, join class docked courses idea and see the intelligence is helping me along because intelligence knows that class represents classes on dime inner Joining that on core dots and I believe this would be course. I see. All right, is it course idea. Then we just double check. Yes, course I terrible Alright on dso and once again, we're still selecting star. So when I execute this, it will bring back Oh, so when you have multiple quarters, it will actually give you this result being with more than one. So results seven is the 1st 1 and we see here that we're getting back all of four class details. We're getting back, all of for a lecturer. Details on We're getting back all of the course details. All right, so this can come in handy when you are charged with furnishing a report. Maybe your instruction was select all the schedule classes that have a lecturer on DSO. The inner join is a nice way to just eliminate any null values that me appear. So in this case, we know that lectures can be no. If I lecture is no, then there is no Electra sign, so we can just use the inner join which does does an inherent elimination off that that missing value. So, no, you want to start tweaking our results that a bit because we have the details that we need . So we really don't need to be seeing lecturer I d courses. I d. The primary key columns and so on. So I'm just going to tweak the select statement, and I'm going to start selecting the columns that I know are absolutely necessary. So I would want maybe the lecturers name on. Then we see we have last on the score name first on the score name for the lecturers. But we didn't look at it earlier. And we saw that we could use con cuts which would allow us to see elect dots last on the score name. And I'm going to contact in it that onto a space on Bring back under this Put a comma there since its last name. First name. So let dot first new, and then I'm going to give this the fearless name. And remember, we use our apostrophe lecturer. Full name. All right, so we bring about the lecturer Spoolname. We probably don't need the degree in this in this report, so we just want the lecture on you want the title of the course? I could see a core. That's title. So I hope you notice what's going on here because I gave these stables. Ellis is wherever they were referenced. I can no use those aliases to make sure that I'm getting the card column from the card table. Because if we had students being joined into this square also have last name and first name , then it would be ambiguous. Super only, said last name and first name. So by seeing elect dot last name or if I had students here and as to call that wants to buy seeing students are starts to that last name. We know exactly which table the values should be coming from. All right, so core the title. Andi, I'm going toe rename this one as course title, and we probably don't need the course school. But we could use a number of credits, so its core dot score scored on off course. If it's the only column off, it's kind like poor scored is only column with that name. So it wouldn't be any harm Knots referencing it by the way, less. But I like to do that because if I look back at the quarry, I can tell exactly where what is coming from. So I just think, too, that even that I know and I say course horde. All right, Sorry we were said Credits, who said number of credits, actually, So it's number off credits on that is as number off credits. All right on. So we can execute this query once again on. Then we see that the details. Sorry. The parties were on the details that were once there are no eliminated as well as the abstracts. Foreign key values there are eliminated on that. While they're not going back in the quarry, they're still playing a vital role in the background, pulling the strings, allowing the joints on. Then once all of the data is is present and available to us, we're simply selecting the columns that were absolutely interested in alright someone to share this on. I'm going to challenge you and I'm going to leave this year. I'll blow this with the court, of course, but I'm with the challenge you to write your own energy in quarries for enrollments to bring back all the details on the course. The time to remember class I D. Is referencing classes and classes, has references to these guys. So I would want you to bring back the student on the name off the course who is teaching this course and the time off the that this course our class rather will be. So we teach in this class. What classes? It What courses it on? What time is it on? Who is the students? So that's my challenge to you. So happy hunting. 19. Left, Right and Cross Joins: Hey, guys, In this video, we will take a look at left joins, right joints and cross joints. Now, I already took the liberty off writing the three quarries, one for the left joint, one for the record one and one for the cross joint on. We'll just stay the time to go through each one to see exactly what is happening now, having just looked at the energy and we can establish that the inner join will make sure that records on either side meet the condition that it stipulated. In the previous example, we looked at bringing back all of the enrollments on the classes and letters. Yes, the classes, the lecturers and the courses on all were inner joins. And as a result, if there was any class that failed to meet this condition where the lecturer I d should be present on either side. Then that record did not come back because we know that we have six courses or six classes . Rather Sorry. Yes, we have six classes on. We only brought about three records when we did our inner join query. And that was because there was no lecturer in the lecturer stable with a no ideas. So these three records feel that condition now. What if we were faced with a situation where we wanted to see all the classes on? If there were lecturers, then we wanted to see the details. If there were none, we still wanted to see all of the classes that were available. So I left. Joint allows us to bring back all the records in the left table on any much in records in the underwrite table. If there are any records, if not, then it will fill that role with null values on. When I say all the records on the left table do know that this is the same Syntax is an inner joy. We have the select statement. We have the left instead off dinner and then we have the keyword join. And then we have the table and the condition, just like the inner join. However, what this is doing, you're seeing that Give me all the records on the table to the left off the joint statement on Here's our joint statement. So that means classes would be to the left off for joint statement if I were to rest in one line. Then you would see better if we look left. We have classes. So bring back all the classes on. If there are any lecturers that meet this condition, then bring about the details also otherwise still bring about the classes. And if we take a look at the execution of that quarter than we would see what we mean. So here we see that ignore in our results set, we have all off our courses, all of our classes. Sorry with all of the details. And of course, he could go ahead and join more details onto the courses and so on. Where already looked at doing that? So you could just left join our inner joy. And since no clash, it ever exist without, of course I d So you know that no court should ever feel that condition. But then where there is no lecturer there, no lecturer details to display. However, we're getting back all six records that could possibly be brought back because we're bringing about all six classes that are available so that is essentially holding left joint works. It is whatever it is that me and table is or the table that is in the slick statement is or is to the left off my statement off my join statement Bring back everything that is possible on try and much as many records based on the condition. If there are no much is then just put no on. Then we can move on to the right join, which basically does the opposite. It says bring back everything from the right side off my select stinar off my joint statements. Andi, if there anything any records matching in the table to the left, then bring them back if possible. So if we take a quick look at the results that off this we're bringing back all the lecturers Andi, if there any classes that are assigned to electrode any classes in the records that these two tables have in common, based on this condition that the lecture ideas present on either side, then bring it back. But I want to see all the lecturers and then this square would come in handy if you want to see all the lecturers on if they have any classes assigned to them. And if we look at that results that we see that we're bringing back only three off the course off the classes that have lecturers on. Then we're seeing all of the other lecturers with no class details attached to them. But we're still seeing all the lectures so we can see here who is not assigned to our class at all. So left, join and right joined pretty much do the same thing that has changed the direction off. The court and court mean table on once again, that mean table is depicted by whichever table is to the left. In the case of a left, join whichever team is to the left off that joint statement and in the case so far, right, joined. I just put this in 19 Also, whichever tables to the right off this joint statements, no moving on. From that, we take a look at the cross, join on the cross join. Pretty much just see is whatever tables I'm joining give me back as many records as possible. So, in other words, you would get back the number off records in this table multiplied by the number off records in that table on that would pretty much be everything just joined. I've never really had a chance are a reason to use this, but we can just look at the results that then see here that compared to the other giant statements, these this record set is really just I've never really used across joined before. But it's worth knowing, and I'm sure you can probably find use for it. But for no, the most important ones, at least the master and have functional use for would be your left, your right and your inner. And in rare cases you mean you to join a table on itself. On that, self join is pretty much accomplished through an inner join. So there are situations where, let's, say, an employee table where all the employees of a company are stored. But then some are supervisor somewhere, employees and some are managers on. You wanted to see who reports to whom, so you would have an employee, i D. But then you also have the employee. I d off the manager for that employees. So you want a report that shows this employee reports to another employee on DSO? You would have the same idea on the primary key employee I D and sort off pseudo foreign key employee I D column, and then you could just inner join that same employee table on itself on. Then you could use an inner join on do that, but then the two tables would be the same table. Just give them to different illnesses. But basically, that's a self joined on that controls all the types of joints that are available to you in my SQL happy hunting and remember to practice. 20. Grouping Data using SQL GROUP BY Clause: Hey guys. In this video, we will start looking at the group by statements available to us in my SQL The group, by alos US toe, eliminate repeating data on desolate, distinct, similar looking records from a table. But it also affords us the opportunity to conduct certain mathematical operations like Colin Ting on, you know, taking a sum on average things like that. Now it is worthy to know that there is another way that SQL allows us to eliminate repeating data and that is using select distinct No. If we take a quick look at our classes table, we see here that we have a few repeating records. If we were toe just want to eliminate them, then we could use either dissident distinct or the group I. So when I say repeating, I mean that we have course I d wandered, beating Corsetti tour, beating, etcetera. No, what if we're in a situation where we only wanted to see which courses currently have classes? We don't need any additional data would only the time or the lecture? We just want to see which courses are currently rostered. We don't need to see course number one twice and of course, number two twice. Well, we really want to see his course one course toe course, five on course for so we could just say select distinct Andi star. But then what this would do is still bring about the same data. Is that because it is looking for a distinct role, not a distinct value in the rule. So because this rule has an ID e 11 and 1400 whereas this one has to one and 1700 they're not, they're not well, they are distinctly different records. So in order for the distinct to be very effective, you have to make sure that you're bringing about the exact column or columns that you know are repeating. And you really want to eliminate the repetition from. So if I was to say, courses one, the score I D. Here on day, I said, This thing two courses Underscore Idea is just going to look for the distinct values in this column and only bring back 124 and five now. Similarly, if I was to do a group by on this side to call the distinct fewer Onda Group, I would just come after our select statement where it's a group by on whatever column I'm selecting, I have to group by So they were column or columns I'm selecting have to be grouped by and then I execute. Then you see that we get about the same results It what is invisible to the naked eye, however, is the fact that the group by is actually keeping truck off what it is grouping Onda. We can actually perform operations that counting each record that was grouped or getting an average off each thing that was grouped stuff like that. So that is the major difference between the group by on the select distinct. Now let us say that our supervisor Asos, for report off all of the courses that have currently being rostered for classes. All right, so we have the courses Onda. The courses are in their own table and we have the classes which is an instance of course, being given a lecturer on the time on DSO we wanted to see off all the clock the courses that are available, which ones have been rostered or put into the class table of our recording the class table . No, we don't need to repeat course. One. We don't need to repeat course toe. So really and truly, always should be. Presenting in the report are the details for the courses 1 to 5 and four. And so we would probably want to refine our results set before we give that report to eliminate the double instance off one and the double instance off to now when I'm writing these reports, what I like to do is select as much data as I possibly can and then start my elimination. So I'm selecting star from classes and then I'll just inner join the lecturer stable. All right, so I modified aquarium bit. I just put in my use statement because it's kind of annoying when after type school db dot every time I want a table side. Just put in my use statement on the energy and Victor's. I'm using my innocents see for classes for a lecture. It's on. I'm also going to inner join my horse's stable, and then we can take a quick look at that results that All right, so we're here. We're inner joining all of these, but then we see where this is not clean because we're inner joining on the lecturer stable , which is eliminating some of the classes that are available. So in this lesson, we'll also see that we can make send much the type off joins that we're using. So I'm I want all the classes because I need the course where I need all the courses on do some of the courses or some of the classes associated with their course don't have a lecturer. So the inner joint is actually eliminating them from my results that but I want to see them because they have been rusted. So I can just seems this inner join because I know that this is the joint statement that is eliminating those on. I can change that to our left joints. I want to see all the classes if you have any lecturer details than you can bring them back on when I want to see courses and this always safe because you know that this is not knowledgeable. So this condition must be met on either side off this query so I can just execute this again. We see that we're getting back all the courses this time with any additional details. If available before lecturers. No, I did say that I wanted only the course name. We can bring about the number of credits on the course scored, but I want to see only the courses I really don't need to enter Joint on. Lecturers are second t that old Onda simply a query, the better it is. It runs faster on. If it brings about the day that the Internet you need, then it's fine. So no, I go through my process off elimination, where I changed my star to the exact columns that I want. No. Do remember two things about this group by statement. One. The more diverse the data is is the harder it is the group. So let's leave this OnStar on Dwell. It's actually difficult if it's on Star, because if I add a group by here, I would have to be grouped by every single column that this star would bring back. And that would be tedious at this moment. So what I'm going to do is I'm going to group buy only the course related Kahlo's. So First, let me bring by the course related column so that c O knots course I D are looking better, but we still have that repetition. We still have data Manning coming twice the introduction to escort. Coming twice. So what we can do know is group by on. Then whatever columns were selecting, we have to group by those columns also. So I just add them there and then I execute. And there you see that? No, we have eliminated repetitions on because the data was consistent going across. So we only had the i d being one being repeated Data mining three. And this course scored. It was repeating the It was the same data that was coming in more than one records. The group buy was able to crunch them altogether on, then process off elimination once again. If I'm presenting the support, I don't need to say the course I d. Second, that's outs off. Both the select and the group by Andi do know it. If you can choose not to select what still grew by, that wouldn't cause an arrow. But what it might lead to is if this record or this columns data is diverse enough. You think you're grouping by boat here? You're not seeing the columns being split up are being crunched together the way you expect . Then that means that your group buys locked clean. So I'm going to introduce a wildcard column here on day. I'm going to put in see that I d so sees a less for classes. I'm no adding the class I d toe this group by statement. When I execute, you see that everything is no doubling up again because this value is different for each off these records. So because I'm grouping by this different value, it turns. It's not want to reconcile and say, Oh, I'm only selecting these. It's only going to see what a my grouping by whole divers are. These records are whole similar. If they're similar enough, crunch them in tow. One record. If they're diverse, then I can't help you. So because of this diverse value, it's no no longer grouping by effectively, so I can no stay that out on bond. I can I can take this old because it's off. No consequence that once I execute again, we're getting all of these records grouped. Now, in a nutshell, once again, this on this select this thing look very similar to the naked eye because the select distinct would do something very similar to this. But as we go along, we'll see how grouping by helps us to do our aggregate my functions. 21. SQL AVG Aggregate Function: Hey, guys, In this video, we will start looking at our aggregate functions, starting with the average function. So to take an average off, the rules or off values in Rosewood need some numerical values that would not be I DS. So for this example and well, we would probably eventually have to add it. Anyway. We will be adding a column toe our enrollments table for grades so we would be adding greats the enrollment stable because this is where we see that a particulate. Students took a particular class, so the best place to put the grade for this students performance in said class would be in the same table where we know that they took that class. So I'm going to modify the enrollment stable, putting agree it's column on, but in some dummy data, and then we can proceed. Soto added a column to an existing table with just over over that tables name in the design viewer. Click that range in the middle, which will bring up, or designer, and then we can just add that new column. All right, So in adding this new column, I am calling it greed. I'm sitting it as a float on dime, sitting it as knowledgeable. So sometimes you, when you're adding a rule, a new problem. Sorry. After having a few reports, if you are not careful with sitting in for no versus not notable, you may run into some design flow on problem because if it's not knowable and you already have 100 records, then you're going to be attempting to insert a new column that shouldn't be no against 100 existing columns with no preset data. So you want to be very careful with that, Maybe want to start off as no filling the records retroactively and then make it not know going forward. So for no, I just said it to be knowledgeable on having me the change of the slick apply. And then he will run an altar table statement, which is the excuse demon that is used to modify a table like when you're adding a column or you're adding Akira attractive. They are doing some operation that is morphing the the structure off your table, so I just go ahead and click. Apply on That was successful, Andi, I'll just go back to my design viewer and execute once again. And then I just add a few grades here. All right, so we've added some grades. Andi, having done all of that other stick apply on alot to run that Opie command sitting, the breeds were necessary on that was successful on No. We have some data against which we can do a few computations Now we have our task ahead of us. We were asked to bring by the average grades her course. No, once again, process off elimination. We know that we need courses so I can select Well, actually, let's start with enrollments because it's average greed. Per Corso Enrollments is what really has agreed. So let's start with enrollment. Select on. I'll say star from enrollments. All right, Andi, let me just add my use school on the score. Devi So that is our for a statement, and we can execute that. Then we see that we're bringing back col enrollments. No, we wanted to know the average greed for course. Based on that objective, I really don't need the course. Well, I need the course name cause they're seeing have class I d. Here. I don't know what course is associated with the class of 85. So that means I'm goingto have toe inner join my classes table. So I just read that quick Klay, and I'll start using my in. This is so Ian for enrollments and seal for classes and unseal dot i dy mostly equal to e n that class on the score. I d on when I execute this. Then I bring about more details. More numbers, though, but I'm really interested in May to the course name, so I'm going to have to see I want whichever which ever course has the i d off five Lee, let me let me take a second to look at these ideas. Says this is no confusing that you see here where we have i d on we have i d are you? So we're about contracts. So our courses i d here. So our classes I d here is being inner joined on the class I d here. So that's why we have 5555555 on six on one. But then we're really interested in the course is because we want to see the name of the course as well as the average grade. So I'm just going to duplicate this line and Jeanne this Soto courses and I'm going to call this one c o and I'm going to see c o that I d. Or well, that would be course I d on d There is the intended since helping me along just quickly, so that's course I d being equal toe cl. That's courses idea, all right, And then once we do that, then we can bring about more details on that we see here. Internet authoring is a very popular course this term on we have website development and data mining. No, the objective, once again, is to bring about the average grade spur course, which means that I want to see one rule seeing Internet authoring on the average grade. For that course on, I want to see one rule with website development and wonder with data. Many granted, we only have one rule off those right now, but essentially we shouldn't be seeing this many instances off Internet authoring by the end off this query. So no, I just focus on all of the columns that I know I want back. So I want back the course title, which in this case would be see or dots title. Andi, I also want back, and I just space those votes so we can see exactly what's happening here on day. I also want back my grade, which would be E n dots agreed. All right, And then when I select once again, no, I'm seeing that these air degrees for Internet authoring on the other courses. But once again, this should all be one value, which would represent the average grade for that class on with the average. What This would be 87 on average for this would be 34. Now, if your A C plus plus develop or are any other type of developer, you would probably go in tow a four loop for a while more where you read a statement for this boat in SQL. They gave us a command because you can see where it's not quite like the other languages. So we actually have the opportunity to use a function every g interrupt that greed value inside TVG, and every G will see I want average for whatever comes in here. So when we take a look at the results set, we see that we're only getting back one course with some average valuable. We had three forces on. We should have at least three rose on The reason for this is that the function a V G is not functioning properly because we're not grouping by So I'm going to add a group by Onda once again we will know group buy whatever we're selecting. But in this case because we're also selecting have value that we're putting in the aggregate function a BG We won't include this one in our group by So when we're using one of these functions, we don't include that column in our group I. And then if I execute this once again, then we see better values coming back where we see Internet authoring with it's riel average Onda website development. With those values off course, if we had more values for website development and data mining, then would get different values than the one greed that is available to us but otherwise that this whole our a V g function works as always, We can beautify or columns by adding or aliases, and we can see course title Onda course average or average greed on then we re execute and there we have or report which we can then export and share with our superiors. Thanks for viewing on. Have fun with that. 22. SQL COUNT Aggregate Function: Hey guys, today will be looking at our SQL count function. No, I have here on screen two scenarios You want to bring by the number of students and ruled for class on the number of students enrolled for course Onda. We will use these examples to explore how the count function in SQL helps us to accomplish this. Now the first thing that we want to do is look at who is in the enrollments table. Second slicks star from enrollments Andi as always I'll use my school be And then once I select all from enrollments I can see the students by i d on what class they are in. So for the first scenario where we want to see how many students are enrolled per class, it means I want to see how many students are in class number five number six and Trust number one. So I want the cones. So what I can do here is C group by and then well, we have all the columns. What are the columns I really want? I want name the class I d on the student I because I want to know what class and the number of students in said class. So I can say class on the score I d Andi, I will take students on the score I d And then I will group buy whatever it is I'm selecting on then that will bring back more distinct values. Well, not really distinct values. You just eliminated some calls. What we want to do is, instead of bringing back this many, we want to bring back the class I d. On account which would be 123456 for customer 501 on one for class number six and class number one. So we have ah, function called cones where we can wrap any value are owned in the cones. And then what this will do is once it is grouped, it will call the number off records being route. So when I execute this Oh, sorry. Execute this without the selection. Then I see. Oh, I'm not getting about the car thing because I'm grouping by a student. I d remember that student I d changes each time. So it's still not distinct someone to take students. I d vote off the group, buy clothes, and remember that once we're up a column inside off one of these aggregate functions we don't have toe and probably shouldn't be including it in the group by. So if I do this again and we see a better corns where we have one in class one, we have six students in class five and we have one student enrolled in class number six. Then we can expand all of this to see more details so we can inner join or classes table on did. Instead of bringing about the i. D. I will bring back the name so that it would be si dot title. But then this is also going to cause a problem because the class the class really doesn't have any need. The classes only need about the letter idea and the courses. So what I would have to do is then inner join once again on the course is stable so that I can get the details on the course. Now that looks a little better. And let's just dissect what's happening in our inner joins. So, up until this point when you were joining, we were joining everything up toe the main table that is being selected from in this situation over. I'm doing what I call a busy chin where I'm selecting from one table and then I'm inner joining onto another table on in the third inner joint or the second inner join. Or which brings us the third table is actually being joined on to the previously joint table. So let's look at that again. We're selecting from enrollments on. Then we're joining enrollments onto classes on Well, I didn't include the alias here, so just so we can see exactly what's going on output on the areas. So enrollments, Plus I D. Most much a class I d in the class is stable, and so this inner join is bringing enrollments and classes together. However, there is no condition on which enrollments can be joined tore courses, So the only way to get courses in the mix is then join it onto the classes table or the classes records. And so, in my inner joined four courses, I'm joining the course I D. Found in the course is stable onto the course I d phone in the classes table. So forever enrollment we're bringing back our class on then forever class. We're bringing back, of course, the course details and as a result, we can then access the title off the class. What? The course associate ID with the classes. And so in our results that we see that we have the name off the course with six on, then the website development with one and the data mining with one. Now the next scenario that is presented would be bring by the number off students and ruled per course. Now the significance off course versus class would be that. Remember that on instance, off a class is based on the classes table, of course, being taught by Elektra that's across. But many courses could be taught because in the courses table we have many courses and then each of them could have different class classes settled. All right, so essentially for as many enrollments as there would be for classes one and two being which are the same course than this second scenario should capture that. So if there were three classes available for website development for arguments seek on, there were 20 students in session number 1 10 in session number two on 15 in session number three, then we should be able to write a query that shows that for website development as, of course, there are currently on my mouth is going to feel here, so just work with me. 45 students doing website development that will be different from going home in there, doing session one versus so many of the session two. So this choir is bringing about harmony would be doing it session so I could include the time here, so that would be see, that's time. And that would bring back for this class session at 50 clock for Internet authoring their six students for this class Ishan at that time there not many students over in the second scenario, we would want to see just for Internet authoring, regardless off the time so many students are there. So I'm actually going to leave you to put in some sample data and to write that query on to see what comes back. I'm going to do the same and upload the solution and economics and much on. That's essentially how the cone function works 23. SQL MIN & MAX Aggregate Functions: Hey guys, In this video, we will be looking at rescuers mean on Mark's aggregate functions. In this scenario, we want to bring back the highest greed on the lowest grade for each course on the way to do that would be toe select everything from enrollments as we know that's where the grades are being stored on then group and then bring back the mean Onda Marks so we can start off by seeing select on We can say class on the score I d on Then we want agreed from enrollments. All right, on defy Do that. To begin Onda, we need or use statements changing the scenario from course the class are so I'm selecting the class idea and agreed from enrollments on. Then we do that select and and we're seeing that each class and we have all of the greets You already looked at Toto average them. But in this situation we only want the highest grade and the lowest agree so far for tonight's number six. Soon they have one green and the scene for class number one, but we have our range of grades for class number five on. We should expect that the lowest return would be 39 the highest return would be 100. The part about the inner joining and getting the details. But I know that should be Muslim member to you. Some Justin to focus on what the functions iron hold they were. So to get any phone, son, any Iger get full of sun working. First we need to group by on. Then we use the column that won't be involved in the aggregation, which in this case is Class I d. However great is involved in aggregation on. So what I need to do is wrap this in the function mean, which were then, for every record that is grouped, find what is the lowest value on then. I have one for months, which does the opposite by fan, the highest value for each record that is grouped on. When I execute this, I see I'm getting back only one rule or record per class. Andi, I'm getting about the minimum grade for class number five, which is 39 the maximum grade up 100. Ofcourse, these two values are the same for the other classes because there is not a wide selection. There is only one great, so the 1,000,000 marks would be the same body in that situation. And that's essentially home. Min and Max work off course. You can always just expand this and idea nearly a system columns so that you have more presentable column hitters for your reports. 24. SQL SUM Aggregate Function: Hey guys, In this video, we will look at sq as some function. The scenario that we're presented with today is to bring about the total number of credits that each student is doing. No, to get to this information, we'll have to hop through a few tables, starting with our enrollments table, which is trucking each student on what class they're doing. And then the classes table contains what courses associated with the class on, then, by extension, over courses. Table has the details off that course on the number off credits, so we'll have to skip through a few tables to get this kind of information. But then, the ultimate result should be that for any one students, we should see the total number off credits that they have, considering what they have being rostered for for the semester. So based on this data that I'm working with, we can expect that students number seven on the 21 at least should have more than three credits. With our some function, we will actually see the end results off that. So I'm going to start off and use the school db on. Then I went to select I'll just start with star from enrollments, all right? And then, having done that, we see. Okay, we're getting back all the enrollments on. Then we probably want to know the names off our students, someone to inner join the students stable. Firstly, and there were bringing back all off the students that are currently taking classes on. Then we probably want the details for the class to get the details for the courses. All right, I know that I've inner joined the classes onto the enrollment stable, have even more details, but then I'm still lacking the details for courses. So I'm going to do one more inner joint where I'm going to join our horses table on. I call it seal onto our classes table on DNO. We're selecting all off the beat of possible across all four tables, having inner joined an energy. So these two are enter, join toe enrollments, and then courses is directly inner, joined onto classes. And so the end result should be that we see one record for students six test six on. That should be six credits because that's three plus three. Andi, there was another student. Are we work better but Essentially, we should only specie in the maximum number of credits per student, as opposed to seeing the drill off. 33333 So let us refine our results that by process off elimination, what is it that we expect to see? I want to see the student's name. So I'm going to do ah, Kong Cats and I'm going to take the S. That's last name on concatenation That's with a comma onto s that first on the score name and I'm going to call this one student name on. Then after that, I want to see the number off credit. So I'm going to say some on what? On my summing, I'm something the number off credits column value. So that would be in my courses stable So that see dots, number off credits. And so I'm going to execute this Andi, I haven't error here because I should have said, See, people, apologies, someone to execute that, and we see that we're only getting back one student with us some. So that's wrong. And that is wrong because we left off the fundamental part off for a query which is over group by On what we're going to group by it. It is. Or are the columns last name and first name? Because, remember, whatever appears in our aggregate function, that column does not get grouped. So we're going to just group by s. That last name Homo is thought. First name on. Then this will actually just aggregate everybody and show us who is doing which course how many credits based on the classes that they have been enrolled in. And so there times you may have to jump through a number of fields to get to the results. But that is just the point off whole SQL, please. Nicely. All of these key words and statements they play nicely together on day, help you to formulate a quarry that gives you the results that you need in the moment. So I hope you enjoyed this lecture. Please feel free to play our own with that aunt. How fun 25. Splitting Groups using HAVING Clause: Hey, guys. In this video, we will be looking at splitting groups using the having clause. No, Prior to this, you would have learned that if you wanted to see only records that meet a certain condition that you could use aware we're command right after or from are well after our inner joins. Then you could see were and then stipulate their condition. Know what? If you were faced with a scenario where you wanted to see only students that are carrying the minimum or what? Three credits. So only students with three crates. Then by you know, instinct. You would probably say we're on. Then you would the column on. Then you stay there. Condition where it is less than or equal to three. All right or well. He only wanted to see those taking 3 to 1 to say equal the three on. Then you would execute this. But then that would probably It's not work because then you see here where it's still bringing back everything. All right on the issue is that when you're dealing with a group by or ah, aggregated data, then the were clauses knothole, You're going to go about filtering are splitting off this groups data. So that is where we have a new key word called having So I would say having on then that would allow me to see the some because remember that this whole choir is based on a some on I want to see where after summing up, all off the credits being taken by students, I am left with three. That is my condition. So I can't see where some on I'm going to try it. And just so you what would happen? Someone to see where the sum is equal to three. And if I attempt this, then I just get an error. It is it's an invalid use off group function. That's because the some really doesn't get executed until the group by gets executed or it gets incriminated, as the group by does its work. So to just say where something is really not going to cut it, because this is in the middle off the clumping exercise, it wouldn't be queen, so we have a keyword called having that allows us to stipulate the same kind off condition , except we can do it on the aggregate column this time. So we can say having some equals three And then by doing that after summing up, it will say Okay, give me back only those that meet this condition. And so we see here we're only left with three. If I said six, then they would only bring back those with six credits on this. Having class can be applied anywhere you have an aggregate function. If I go back to one of the previous queries where we had me and mocks, I can see Give me back all of that data. But I only want where the minimum greed is greater than 50 are It's only one back. All of the all of this data where the minimum grade is 50 or more and then that would Oh, that is actually giving me a tight miss much error. I'm sorry. From the previous example I didn't feel old. The in s isn't so That was causing the problem. So if I attempt this again then there we go. So I'm only seeing the records that meet this condition where the minimum greater turned was over 50. And so you can apply this having close if you feel the need that having grouped. Having performed some month, you still need to filter out some off the the returns or the results that come back so you can just use that having keyword onda AB your aggregate column. Andi, whatever condition it must meet on. Did you see that if I attempt to put in a non aggregate column here, it does not work, So it will give you something about an unknown column on. That's really because it can only look at the aggregate value as opposed to the value being aggregated. And that is the end of this video. I trust you learn something valuable with the having clause on. I know you put it to good use.