Microsoft SQL Server Development for Everyone!

1. Introduction skillshare: Hey guys, my name is true for Williams and I want to take this time to welcome you to my brand new course, Microsoft SQL Server development for everyone. This is a Zero to Hero course in Microsoft SQL database development and querying. And all of the prerequisites for this course is that you have a computer that is capable of learning the technology that we're going to be using and a willingness to learn. Now in this course, we're going to develop a small database, practical enough database in the form of a school management system. And what you're going to learn along this journey is how to install and set up the SQL Server management environment hold to create databases, query them, create tables, sets of different objects. We're going to look at things that would prepare you to be at least a database developer and, or a database administrator. Now this course is going to be perfect for you if you are a beginner with absolutely no experience, you have basic database operations knowledge, or you just want to refresh your knowledge. Or maybe you're kind of using older technology and you want to kind of advance in technology. This course is for every and any one. By the time you finish this course, you would have moved around so much in this environment that you're going to become an expert with database concepts, database jargons, and how to execute. With this new knowledge, you should be able to increase their markets ability and your income. Of course, impress everyone around you with all that said and done. Welcome to the course, and I'll see you soon. 2. Introduction to Databases : that is welcome to this exciting journey. Introduction to SQL Server 2017. I'm travel or Williams, and it is my pleasure to be sharing this knowledge with you for this video. We'll just be taking a very, very lightweight on high level overview off. What SQL server is Andi databases. What data is and how we can go about appreciating why this knowledge is going to be necessary. Moving forward. Firstly, we need toe Assess. What is data on what is a database that in simple words, neither can be facts related to one object that in more practical terms, data can be anything that relates toe someone or something. So my name, my first name, Proforma. Last name Williams. Those are bits off data about me, which would be the object in this scenario, or you have a full what is its color? Its black. Those are bits off data relating to the object, which is a phone. So data collectively describes an object on then by extension, a database is a systematic collection off this data on a database alos us toe store. This data on manipulated, you know, if you need to change something about some some object than the databases where you go toe make these modifications on. As a result, a database is supposed to make the management off this data stuff easy. No, that brings us to what SQL server is and how it helps us with databases. Now, SQL Server is a relational database management system or for short R D B M s database management system are D D. M s is a program which enables off the users access manipulate. Andi represents vita however we want. So in other words, the D. B. M s is our quarter into this database, allowing us the users to manipulate on access data at will That no SQL server comes in a few additions on their listed there. We have expressed, which is free. It's really lightweight. It's for, you know, quick development, quick projects. You just install it on your off the grown quickly and that's one will be using for the rest of the scores. We also have the Developer edition, which is a fully featured enterprise edition boats. The license is really only for testing and development, so you shouldn't be going into production with a developer license. All right, and then standard and enterprise. A really more suited for large corporations with huge on copious amounts off data. And they really need a structured way toe store it so that the different parts off their business can benefits. No, we explore. What is SQL No sq Eliza standard language that allows us toe talk to the databases. The abbreviation is really short for structured query language on below. I have some examples off what this language may look like. Whatever is in all caps is actually syntax relating to the language on whatever is not in our caps is relating to the database itself. So in other words, we have, well, data bees or tables parts off the database, so customers is a table in your database on. Then we can see where we're seeing select star or asterisk from customers, meaning we want all records from customers. Know this is just a primer. Do not panic or anything. We're going to get into detail, and we're have a lot of activities coming up where we will actually delve into the language into databases on we will get a clean on unclear understanding off whole SQL Server 2017 will help us to manage our databases even better. So stick around the next video, we will go about installing and setting up our server. 3. Download and Install SQL Server Express 2017 : as welcome to the first video in discourse. Introduction to SQL Server 2017 on the Today will be looking at downloading and installing this bit off software. Now a database is pretty much a logical collection off related data on That's it. In a nutshell. If you have prior experience with databases, it probably came with Microsoft Access or my SQL. And you just want to extend your knowledge beyond that. And even if you have no prior knowledge off databases, this is an excellent course to get you up to speed with what databases are capable of. What preparation usable in place on how you can go about starting your journey towards becoming a database administrator or just manipulating data in databases altogether. As I said before, we'll be exploring all of these concepts using Microsoft SQL Server 2017. Andi. I already have it up on my screen. The page for Don't lose. So I have included the U. R L, as outlined here in the class notes, and this would get you to downloading the Microsoft SQL Server 2017 express database engine on the short description is that it's a powerful and reliable database management system that allows you to build lightweight websites and their stop application. So this is pretty good. Pretty decent is a decent starting place. If you don't have that high spec high end machine, he does have a regular machine. You just want to get started. This is an excellent option. There are other additions that you would probably see in your research, but if you follow this link, it will cut through all of that noise and all the other editions. And we can get started with express so you can go ahead and that download on Dinitia. Initiate that Donald procedure on then. Another bit of software that we're interested in is the SQL Server Management Studio. Now the management studio will be your gateway into actually interacting with the database , interrogating the data on writing what we call queries in a language. SQL. We'll get into all of those things in due time, but for no, you can follow this girl once again. It's in the class, notes on. We can just take this download button to initialize this download, which may take a while. It's a pretty big file, so as soon as that don't load is done, we reconvene. And then we walked through that installation process. Hey, guys, welcome back. Know that our don't lose our finished. We'll start by installing our SQL Express engine. So we just click on that insulation file, click run on. Then we will be greeted with this installation screen we can click. Basic custom would allow you to select certain features at this stage. We don't need to be doing that. We can just go with basic. We can go ahead and agree to the terms and conditions you can read it through if you wish. If you have other ideas than you may want to change its director, otherwise you means will use as a default. And you can see here that off six gigabytes, we only really need 300 megabytes. So I'm just going to go ahead and click Install on this may take a while, so you will reconvene once this is finished. Okay, so at the end off that installation procedure, you see that you were, you know, successful in doing this installation, and then they give you some information to give you a comics and string. So if you wanted to develop an application on top off this database, then you probably want to pay attention to this part. But that's not really necessary right now. And they just give you the paths for the love fires and other essential bits off the application. Another essential part off this installation process, as mentioned before, is to install the sequel Management Server studio at the end off this experts addition installation, they will actually give you an option to see installed SMS. And this will really just bring up your browser to navigates toe. Well, I figured this speech so the link to this page is already included in the class notes. Andi here. From here, you can get a direct don't Lord toe the management studio. So if you didn't do it before, you may just follow this link and get that file already have it in Don't loaded here. So I'll just initiate this installation. All of this will launch the installer and they really just give you one button to click, which is installed. Of course, you can change this location if you have other ideas for the path. Otherwise you may leave it as as the default on just click install. So this may also take a while So we'll reconvene at the end of this process. Okay, so our installation is finished on Doll we have to do is restarts to continue. So we can just do that quickly. Onda we in the next video we'll start looking at how we start piecing everything together. What a database is on. Why learning this skill is so important. Stay tuned. 4. Setup SQL Server For Mac and Linux : Now as it stands, Microsoft SQL Server is almost exclusively only usable on Windows machines. So the previous installation steps may not have gone down well, if you are not using a Windows machine, if you're using Mac or Linux, don't worry, there's still a solution for you. Solution number one would be that you can use a virtual machine, so you can use VMware or some other tool that supports virtualization. And you can spin up a virtual machine that has a Windows OS and then use that. Know that can be resource intensive. And I'm not going to put you through all of that just to use the one software. The alternative to a virtualized environment for Windows would be to use Docker, which is what I have up on my screen. Darker, you can easily get here by going to Docker.com. Darker is a very fast application that allows us to use what we call containers instead of a whole virtualized environment. So what we can do is use Docker to simulate the environment needed and only the environment needed for a particular application to run without needing the entire operating system. So what we're going to do is use Docker to simulate an environment for SQL Server. And then we'll be able to connect to the SQL Server just to see, even if you're not using a Windows machine. Now, the first thing you'll notice is that it is available for every operating system, pretty much right? So if you're using Windows, you can still use Docker. If you have an Apple device or Linux-based device or Intel chip based device, you can just the same. We install Docker. So go ahead, download and install it. I already have it installed on my Windows machine, but once you have installed it, what you will get is access to the Docker command line commands. Once you have it installed, hit pause, let it install, and then you can continue. I would encourage you to launch your terminal. So once again, I'm using a Windows machine, but your terminal on Linux or Mac OSX would look very similar to this. And you can simply run the command docker just to make sure that it's installed. And if you see something looking like this, they didn't have access to the Docker CLI commands. Alright, so what we want to do at this point is run a command that's called docker. Pull is zoom in a bit so it's easier to read. So we're going to do docker, pull, docker pull. We're going to pull this what we'll call image. So Docker has predefined files that line the environment that is needed for a particular application. And these are called images. The image that we want is the Microsoft MS SQL Server image. So we're going to do a docker, pull against that image so you can hit pause, make sure you type it in just the way I have it. And when you press Enter, it is going to go ahead and say, Okay, I'm getting latest and then you're going to see downloading. I already pulled that image, so I already have it on my machine. But you're going to see it's pulling and then it's going to start showing metrics of it done loading. And it would actually look something more like this. So this is a screenshot I took earlier from when I was downloading it. And you're going to see that it's going to spawn up these bunch of lines looking similar to this. And you're going to have these downloading tags. So once that is completed, the next step is to actually run it. To run it, you need this command. So we're going to say Docker, run, then hyphen e, and it will do the eula accept. So what happens is that SQL Server usually it has one of those documents that you need to accept the terms and conditions. So we're just putting it in a parameter that yes, we accept the terms and conditions. And then another one that says SA password. So if you looked at the installation process when we're using Windows, we can use Windows authentication to connect to the database, right? So all we need is a machine name. We can use a Windows user, the current Windows user, Windows authentication and just connect. Now because this is darker and it's a container, There's no windows or doors or Mark or there's no linux Authentication. So it's not really that you can just connect using the default user on your computer. So this step is applicable whether you're using Windows, mac, Linux, etc. So what we need to do is specify an SA password. So AC is the default user, which means system admin or system administrator. Every time you install a database engine for Microsoft SQL Server, you get this essay user. We're going to set up this SA, password. And you can put in any password you wish. I'm just putting in a strong password here. And this is really possible that you might see in other demos that you might watch anywhere on the Internet. So this password is not necessarily unique to me or to this exercise. You can put in any password value that you feel comfortable with. And you remember, I'm just splitting the word strong password of course with Characters, numerals, and a couple of capital letters. Then we specify the port. The port here at the front is important that we want to go through. The port. On the other end of the colon is the port that it will map to. What this means is SQL server by default broadcasts from port 1433, that's the default port. So without doing anything, specifying any ports or anything, will always go through 1433 once or connecting. However, Docker is running in its own specialized environment, so we need to map. This is the default port, and then this is the port machine that will want to tunnel through to get to this sport. So you could actually just leave that as 14331433. If you don't want to have SQL Server installed already and you're running Mac and Linux, then 141433 is fine. You don't have to do anything extra. You can just connect. However, because I'm using a Windows machine and add to change my port because 1433 is already occupied by my native SQL Server installation. So I'm just showing you that you can do 141433 by default, or you can put in your own specific port if you so desire. Then the next and final thing is we say hyphen D, and then we specify the image that we want to run. So basically we're saying docker run this image and being sure that all of these parameters in-between are configured. That's essentially what we just did. So when you press Enter and you alone, this one to run, what it will do is launch that image inside of the darker UI. In the darker UI, you're going to see something looking like this. It's going to appear under containers. And you can see here that's how several containers, right? I even have another SQL container that's adding salt from earlier. And here's the new one from my just running that command. So he created a brand new container for Microsoft SQL Server. And it is currently running, and it's been running for 30 seconds. And the port it uses is 1400, which if you didn't change that and use the default 1433, would be 1433. You can always stop and start the containers. You can also delete and you can also watch what is happening. So you can open it into terminal and you can interact with it here. You can also inspect to see the health of it and look at some of the configurations. So these are some of the environment variables that we had set up. What are the parameters that we pass? Then you can also look at the logs. See here that it seems to have started up successfully. So after I've, I've confirmed all of these things now, I would want to connect. Now for Windows, we can use the Microsoft SQL Server Management Studio. However, Management Studio is not necessarily available on Linux and Mac. So the alternative here would be to get the Azure Data Studio. You can simply do a Google search and you can go ahead and follow the Microsoft documentation to the Azure Data Studio. Data Studio is a cross-platform database tool for professionals. And it can work on Windows, macOS, Linux. So you can go ahead and install the appropriate version for yourself. And of course, you can look at any warnings just to make sure that you are in a compatible state. Alright? So the thing is that the Data Studio can do most things that the Management Studio can do, especially within the confines of this course where we're learning about database development and queries and such. The experience over will be slightly different. So certain things won't be at the same place and might not be called the same thing. Over to connect. Let me just show you how we connect to. So you can see I've connected quite a few ups over time or it needs a base is rather, but here I'm going to connect Microsoft SQL Server and then the server here is going to be local host comma, the port that you specified. In my case, I specified 1400 on my porch. You might have specify 1400 as well or whatever it is that you specified. When we set the container should run and if you don't remember, you can just go back over and look. You specify that port. And then not Windows authentication but SQL login. And our username is SE, and our password is the password that we typed. And once again, if you don't remember what that password is, you can always go to the container and then click Inspect. And I can just copy this value and then paste it. And then for future, you just remember password. Then we can connect. Then this is a sign of a successful connection. I, you see here that OS version, so I am still on my Windows machine. But the OS version that it has detected is Ubuntu. So that is what the container is doing. It is running in a Linux environment, but it's simulating the environment, exact environment needed for SQL Server to be able to run. If I expand databases, you'll see it's empty. And then basically everything that we can do from the Management Studio we can do from here. One thing that you may notice though, is that some of the tooling is limited. So in the Management Studio, I can easily, or more easily to right-click and say, do certain things like create a new database and so on. In the management in the Data Studio, however, it will probably have to be scripted. It's something that you'd have to control and creates a new script file. And then to create a new database, you'd have to say create database. And then state the name, let's say test db, then F5, and then it gets thrown. So then you can refresh the list of databases and then you'll see TSDB, right? But in this course we're going to be looking at how to do both scripts and use the graphical user interface in Management Studio. So you should be able to follow along quite fine. And at any rate, if you run into difficulties, feel free to reach out. 5. Connect to Database Instance and Create a Database: it's no that you've completed the installation. It is time to launch over management studio and get connected to our engine so we can just go to the start menu. Onda. We can just look for the Microsoft SQL Server tools, and it may have a number the number me very based on the version. If Europe with 2017 and the latest estimates, then you shouldn't have a problem. Uh, previous versions off system it's was still work. So I'm just saying that in case you would have had a previous installation, then you may see a different number or it may not be in this former. Either way, if you have an abnormal experience, just drop me a line. Leave me a message on. I'll be sure to follow up with you, and we can work through it together. Otherwise, you may just expand this and you can look for Microsoft SQL Server Management's off course . If you have windows, then you can always use that search so we'll just go ahead and click this on. We can spin it, toe our task bar into the start, so it's accessible more easily so we can just go ahead and launch that. Okay, so upon launch, you will be given this tiny dialogue box and it will stay to the server name. Now, upon launch will be given this dialog box which asks you to connect to the server. It should be pre filled with the server name, which would be a combination off your machine name on your instance name. So at the end of the installation, you would have seen where they indicated. On instance On the default name is SQL Express. Andan authentication by default will be tired to your windows Authentication accounts on DSO If all of this is there and it looks OK, you can just go ahead and click. Connect on to know that you're successfully connected. You will see over two year object Explore to your left side that you are connected and you can expand and looking databases and you see a bunch of options here. The author, our very first worry. What we need to do is just click the button. You query the keyboard shark. What control end will also open up a new worry window and then we can see that all we're given is a blank text file. And there certain things here that you may want to pay attention to. Our object Explorer is still here. Off Corsican. Set it to auto. Hide if you want more. Really State. I like leaving it, Doc. Second issues that Pushkin, the father that setting on we see here that when we click in the empty text area, we see some life offering up here. This drop down or this word monster really triggers a drop down box which shows us all off the databases that are currently in the system. These are all default data bases, and I would advise that you do not delete them. So even if you go exploring and you go into the system databases and you see them listed there, I advise you to not manipulate these, at least not right. No, there may come a time when you become a SQL guru, but right now you can just leave those alone. So, as I said, we want to write or first query to create a database, and we're going to call this database school. So the first thing we want is the word creates. So we write, create Andi just the point out. SQL is not case sensitive, So I will write the SQL syntax in all caps so that you can see what it's syntax on, what isn't. What ultimately or editor will highlight it As blue as we go along on what? Whatever is not syntax. It will usually leave in black. So we want to create a database and we're going to call it school. I'll just leave school in lower caps. Some people end with a semicolon, but it is not absolutely necessary. So we have our query here, and then what we will do is press execute. Or we can just use our keyboard shark cut f five. They do the same thing, and then once we do that, we'll see it says Commands executed successfully. All right, so I zoomed in, say conceit commands completed successfully. Now, how do I validate? That's a new database has been created. We have a drop down over here labeled databases. We already dropped it down, and even if we collapse and expand its we still don't see the database. So what we'll do is refresh on. Then we see it appear so ultimately as many databases as you will create will get listed directly under the databases part off this file Explorer tree. So once again, you see other databases on their system databases. You can leave those alone on as many databases as you will create. You see them listed here, so I will go ahead and to create school. That's test just the highlight and Dimon Street. And then you can see that there's a red line here seeing you cannot use dash in the name off a database. So there are some strict rules. People tend to camel case or use underscores at there are restrictions. And this editor is quick to point out what you can and cannot do before it even allows you to execute. So we'll go ahead and create school. Underscore, test Andi. We'll see. Command competed successfully, and once again we just leaked databases. Refresh and then we will see that we have our second database listed there 6. Drop or Remove Database: All right. So picking up where we left off, we have to Databases successfully created. But really and truly at least right now I only need one. So I want to remove one of thes databases. So there is a command in SQL. So I just remove this nine or I just commented out so you can just use a double dash, right? And then what this does is rendered this line useless. So if you're used to other languages, like maybe see sharp javascript, you know you have that double slash or you have that slash and each language has its own way. So in SQL, we actually just use a double dash. All right, I want to see that lion turning green. If you are using the defaults, color and scheme, then you know that this line is not going to have any bearing on anything. Once you click execute so we can go to the next line Onda, we will want to delete or in this case, drop in SQL nomenclature drop or database? No, I want to warn you Be very, very careful on deliberate when you are going to run this on, execute this command because dropping a databases and irreversible there's no recycle bin. There is no recover method or there are a few boats at this stage. We're not ready for those. So be very, very careful and very, very deliberate in writing on further executing this command. So we're going to see a drop database on. Then as we go along, you start seeing that there is a pattern, and I notice I'm not writing it in our caps. Once again, it is not case sensitive, so drop database on. Then we have to specify the database name, so we want to drop school on the score test. So I just school on their score test and we see here that the editor is actually kind of picking up on what you mean want to do. So it's kind off completing what it is that it thinks you want to do. Being stunned, that information it has available Teoh, so it makes it pretty easy to write complex statements in a very short while. So we want to drop the database called school on the score test, and then I'm going to execute that once again, be very deliberate and careful when doing this Because this skill will not ask you if you're sure you want to delete this item. Alright? SQL is just going to see the sorry s Your management studio is just going to see this is the command I got. I'm going to execute it. So when I click execute, they're ago. But we still see the database listed here. So as usual, we just click here and refresh on. Then we see that there is no longer any trace off school on the score test on If I attempt to execute this again, then I will get an arrow saying that I cannot drop it because it does not exist or you don't have permission. We know we have permission. We know we just did it. So we know that it's because it does not exist in the same way on a quick way to comments in SQL Management Studio is the personal control key. And see, as a matter of fact, in the class notes, I have a bunch off keyboard shark oats that can help you get along quickly. Andi, move along in the editor with with minimal effort. All right. I love keyboard shortcuts. so we can say create database on. I will try to create school. Well, we know where they have school, so I just want to show you the type off era that you would see on then It says database school already exists. Please choose a different name, so we can't have two databases with the same name. If you need toe identically named ones, you know you can use your underscore or you can add a number. Numbers are acceptable. Ofcourse, the Carter sits there. Strict rules are owned it, but we have infinitely many Carter's at our disposal, so we can always make send much and be unique. All right, so that was how we go about removing a database. Next, we look at how we go about creating what we call tables in our database. 7. What is a Database Table?: No. Let us discuss briefly of what our table is Now. A database consists off one or more tables. A table comprises rows and columns. Essentially, people looked at an Excel spreadsheet. You would see that you have rose depicted by numbers, and you have problems depicted by letters. So in the cell a one you would have some amount of data, and usually you create some form of tabular structure. Let's say, for instance, for, uh, least off students, and you have an Excel spreadsheet would have a sheet for students on. Then you would have columns first name, last name, date off birth, etcetera on. Then in each cell underneath the respective columns, you would have the appropriate bit off data to support that value. Together, this record or rule would create a description off on objects or a student. So in the same way that we can visualize data in an Excel spreadsheet, it is is the same way that it is represented in an SQL table. So we have rows and we have columns on rules, create records and their meet up off data points on each column in the table stores. The data point, which can be in the form of text or number or a date of many data types that are supported on all of these combined to tell us details off on object. So that's essentially what the table looks like in a database on. Then in the same vein, each row or record should be uniquely identifiable using what we call a primary key. So in the next video, we'll go about creating a table. I remember databases called School, and we will go about creating or first table to store all of our students stick around. 8. Create an SQL Database Table: Hey, guys, Welcome back in this video, we will be looking at creating a table. Now, once again, a table is inside off a database. A database can have more than one teams. So the first thing to note is that our database engine, which is SQL Express, can have more than one gator basis on then. Secondly, each database can have more than one tables. The first thing we need to do before we start creating anything once we get to the table stage is to specify which database where a boats to use to carry out or operations. Now, we did show that we could have more than one databases as we created school and we had school underscore test. So let us assume that we had more than one data basis here on then. We need to specify which one it is that we're about to use. You recall we have these databases as well a school. So we need to specify that we're about to use school. So we say use and then we use our database name on there. Our intelligence is completing that for us, and I just put the semicolon there at this time. The next task is to create a table, so using very similar sin tops, I'm going to rant my create statement, but instead off database. Last time we said created Toby's instead of database. This time I want a table, so I'm going to say, Create table on, then I'm going to specify and name for this table. In this case, we want students. So essentially students. It's explore allies because the table is going to be storing records for students. So you'd want to prove pure allies the names off your tables just thought of principle because each record, you know, it makes it plural. So we say, create table students and then we open and close brackets and off course you get that little red line because you can't have empty brackets. So we're going to fill in the columns that we want to create. Now you don't have to break through and I'm breaking line because I like to see where my code starts and stops and have it tabulated. But everything could actually be not in one line. SQL is really not going to through any arrow with its either way, so the first thing that we want to specify is a unique identify or what we call a primary key. Now, the simplest name that we can give to this unique identify air is idee someone to say idea , which is our column name on then after or column name. We have to specify at beta type. Now, if you have done other programming languages than a lot of this may seem familiar a lot off them. Me, very. But otherwise let's just go through this slowly on appreciate the different data types that are available to us for this simple example. So our I D data type is going to be a number type, which in SQL is called in short for integer. All right on, then I wanted to be what we call the primary key, so I need to specify that this column is our primary key. So this is called an attribute. So let's just recap that we have the column name followed by its theatre type on. Then anything else would be an attributes. So in this situation or first attribute is primary key, other attributes can include not know which is specifying that this column should never be empty that were no means empty. So we're seeing that this should never be empty. No student record should ever go into the database without this value. Alright on then. We have one more thing in Microsoft SQL that we had done that I lows it toe cones automatically because when you have the 1st 5 students, it's easy enough to say, OK, student one on, then follow their self Today it's a student, too. But there, when you reach toe a 1,000,000 records, it's hard to keep track. It's hard to know which number was previous. Andi will become tedious trying to keep up so Microsoft SQL as well as other database engines. They give us the ability to ConEd automatically. So each time I record goes in, this column would just increment by one. Just go up by one or you can specify whatever body you want. Andi. It will take care off that. So if it's always counting, then that record is always going to be uniquely identifiable by its i D. So in Microsoft SQL that syntax is identity. Sorry I didn t on we can to specify that we want to start off with one and we want to increment by one each time. So once again, this specifies that it should automatically increment each time a record goes in on. We want to start counting at one, meaning the first record should start up one. So if you wanted it to start at 10 then we could see 10 right? But we'll just leave that that one on the second apartment in this function says that we should increment by one. So if we wanted to start off to Andi, go up 2468 with these record and would start counting your toe and then say we want to start every every card should be incriminating by two. All right, So by leaving it at 11 on, sometimes you just see the word identity. All right, So I'm just giving you the full understanding so that if you see the syntax certain, either way, you can appreciate what is there, so I'll just leave that right there. So once again, column new theatre type and then everything else would be an attribute on. These attributes are by fire and large optional. It's up to you if you want to specify attributes and constraints on your columns. So our second column would be last name on the students. Last name on then. Obviously, we don't store names as numbers, so we need another data type to store last name. So the providers of a few data types that take text I'm going to use this one called env are char all right. You have in varnish our and you also have arch are you also have char and you have quite a few off them. I also included in the class notes this script as well as details on the most popular data types and which one you would use when so you can always review that on your own time. So I'm going to say Environ char And then I need to specify Khomeni characters. Should this environ Shahriar column or data point be able to accept? So this is also another constraint. I want to constrain it to maybe 50 characters, which means no student records should ever come into my database with more than 50 characters are at, so nobody's last name should be. I can think off a last name that's obscuring off the violet this, but at least where restricting it. So if you know you're building a system for a country that tens are very exotic names, at least by our English speaking standards. Then you may want to play our own with this Carter limits, so I'll just leave this at 50 on. Did you may have noticed? If not, then that's fine, that I am using a comma after each line. So after you specify a column user comma and then you move on to the next column, if you leave the calmer there, then it will give an era. So if it was the last one, there is no comma. But then we have more to put in, so use a comma. Next, we need first name on environ Shar. We also sent this to 50 on Let's just say I want to put on a front street. I want that no student records should ever go in missing the first and last name. So I want to make sure that this column is never know. Okay, I know just at that constraints toe our last name problem. So last name should not be No on first name should not be no. Another another. Another important bit off data toe have on any student attending my school is the date off birth. Once again, SQL is not case sensitive. Um, but then for human readability, you can appreciate that Maybe using camel casing would help with readability because whereas last name and first name are east read date of birth in lower caps might not be so easy to pick up, so we can always camel case it. But I will just say be consistent. If you're going to Camel case, then you should come. Camel case all off the columns. If you're going to be lower caps, you stay lower caps. If you're going to use underscores use underscores, but be consistent. All right, so I'm just going to be consistent with my lower case. But then you may have different ideas from me, and that's fine. Where all individuals we're all learning here so you can use your own flair and you can break it of using your underscores our camel casing, if you wish. All right. So date of birth on date of birth would Well, obviously we're storing the date here, but then we wouldn't want that states because maybe want to. So do some date manipulations later on. So SQL actually gives us a data type that is suited for storing bits. We actually in modern s you'il engines, like 2017 have a date data type of. Then for older versions, we have big time. That's that works in virtually every versions. I just stick the date time, just in case I build this thing in 2017 but up to deployed in a previous version. So I just used the more universally acceptable the attacks. But off course, you feel free to play our own with them all. So we have bit off birth on it takes the time on this should also not be no. All right. And then I'm which are one more column on this one would be enrollments date. All right, so you can see here we're having a lower case as the column names gets kind off more, you know, bigger and more descriptive. It's more difficult to keep up with watches. What? So once again, you can always bring these old using underscore to increase readability or camel. Kiss them by capitalizing the first letter off each word. All right, so enrollment date one just the same as date of birth will get the time. Andi, I won't put on any constraints to specify that this one wouldn't be. No, because maybe the students came under register, but they they haven't enrolled. They just came and filled out a form, but they aren't really taken any classes, so we don't consider them enrolled. So that means a students can be entered into our database. It will be. He will be. He or she will be given on idee automatically. They can't be entered without a last name without a first name or without the date off birth. But then the enrollment did. It is alone to be empty. All right, so as a quick recap, we specify which database were about to use so literally we were at use and then the database, his name on. Then we go about creating a table, we give it a name on. Then we start specifying each column by writing the name that we would like the theater type. It should be on certain attributes. Are const Reince that should surround the data going into this column. So we're going to execute this and then we'll see what happens. All right, so it was completed successfully, so we can actually expand our database here, which is school Onda. We'll see that we can see tables, views and a bunch of other stuff. We're really interested in tables right now, so we expand tables on lo and behold. There is our table called students. We will go about creating courses table in this database. Off course, we have a school. We have students where, of course is and I mean we have teacher. So next up is courses. Andi, just like our tables. Sorry. Our databases, just like over databases. We are not ableto have to off the same tables with the same name. So if I tried to execute this script again, it would given error. All right, but then I'm going to leave it on the screen because there's something I want to highlight to you guys. And we're going to write old another create statement for our horses table. So you see it create table Andi courses open for aspirant assists and pills. I just like to open and close at the same time so that I don't forget afterwards. No we have our first column? No. The order that you create the columns in really does not matter when the table comes out, your seat in the design in the in the order that the columns appear. But it's not a matter to see that all you have to create the i D before you do anything else. I just think it's good practice so that you don't end up creating the table without it. Because when you have ah, poorly constructed table to begin with than the richer, active steps to correct, it might, you know, be difficult. So you want to make sure you have the proper thing from the beginning. So firstly, you say I d on, you'll see other people cannot specify if they're in the course is still. Then there's a course I d. So that, you know, is very specific. Some people are generic and just fall everything i d Somare more specific NC whatever the table name is on just up in the word I d. So I'm going to do it both ways just to show you that there are many ways to do this. At the end of the day, you pick your old pattern on, Develop your own sense off individuality. All right, So course I d on the data type. As we discussed this int, I wanted to be an identity and we'll see 11 primary key and not know on you As we go along , we'll see that not all of these attributes are absolutely necessary because the fact that this constraint is hero this attributes is here means that this one is implied. If it's a primary key, that means value must be supplied. So we really don't need to specify and not know. But I'm showing you all of these things until you get comfortable. So we have our course I d which is our primary key. We say comma, go on and we see a title. You notice I'm taking the camel casing approach for these columns. So I'm just showing you what it looks like and once again, you just develop your whole butter. We're using the teeter tipped type of our char on, specifying that it should be 50 Central course title should or will be ableto exceed 50 characters on then over. Last one is number off credits on this would be an integer because in school we have three credit courses, five pretty courses, etcetera. So we want to specify that for any course, it gets this unique identify rare. We have a title. We have the number of credits. Andi in a college setting, we usually have course scored, so we'll just go ahead. And at that course cord on, then this would be maybe an environ char on course. Cordes aren't usually long, maybe five. Carter's so we can see an invite. Our off five. Carter's on If it is a case where you have an alphanumeric value, meaning you have numbers on the letters. So in this case, of course, sport could be something like, for argument's sake. See, as for Computer Science Tau 01 so ever second year course in computer science, and that's a poor scored, so that would be an alphanumeric value. If you have an alphanumeric value need to store, you can simply store it as an environ char or as a regular text value. All right, so a course called like this would fit perfectly into this scenario on, I'll just leave that there and come into Total and give that as an example. All right, So you see, using our double dash like we saw in our previous video weaken this comment off a line on it would be ignored. When that worry is wrong on, then we need to add some attributes and no course should ever get added without a title. Maybe we can add it until we figure out the number of credits that it will have. Andi, it should not go without a course school so minimally it should have a title and, of course, pulled. But it's a load to be there without any number of credits, all right. And once again, this is all, for example, purposes. So I know that we have specified everything for our courses stable. We could just go ahead and hit execute. What will happen, though, is that it will. We're on this land successfully on then failed to execute on this land and then stop the query. Sometimes it will stop the quarry. Sometimes it wants. But in this situation, we don't want to risk having that error. So what we will do is select the portion that we would like to execute. All right, so we have 1/4 file. I can just bring this down so we can see the total picture. So we have 1/4 file with three meter commands and you can always tell where command starts , because it will give you this little minor sign. All right, so all of this is being seen as one command. Remember, we could ever in it in 19 And then this is another command. So as far as its is concerned, it has three directives are common is that they used to carry out. So if we want to run one off these three or any portion off all of the commands in Wana Corey File weekend just highlights that set off commands on click Execute or press F five, and it will then only execute what was highlighted. Proof off this. When we go here, we can refresh as we don't see any changes. So we click on our tables folder on Just Refresh. And then we see we have bbo students and bbo courses are newly created. Table also don't Here is a good indicator as to when something feels if I buy some miracle forgets and just execute, then we'll see it stopped at the table. The creates students table directive under city we already have on object. And then it didn't go any further in the quarter. All right, so we can always just highlight and execute the query, or we can run, execute, and it will go through everything that is in the file. So when you have more than one commands, you can actually just chain them alone and click Execute one time, and it will do them all. In this video, we will be looking at using the tool toe actually carry out all of the operations that we just so skillfully wrote. SQL Statements toe Accomplish Now Microsoft, give us this beautiful tool Microsoft SQL Server Management studio. And we already established that we can go ahead and write SQL statements in this tool to carry out a lot of the operations. Now I'm about to depict to you how you could go about creating a database on tables with all writing one line off SQL cold. All right, so it all begins with right clicking over databases folder so we would have already been connected and we'll see that we have databases here. We can always expand on contract that tree we just rightly databases. And then we will see the option New database. This will bring up a dialog box which then asks us What name do you want for the database So I can see school. And I just named this on V two. As in version two on. Do you have some options here you can look at So everyday two B s comes with what you call an M V F. As in fan file, which is the actual file. Storing all of the data on it has a lot of fire, which keeps track off all the changes being made against the data. So you can actually specify another path outside of this default path where you would like it to go. Most database administrators, they would be They would have configured the server to have to different hard drives on one where the operating system is installed on another one, specifically for storing the M V F on log files. All right, so you can change those. But I won't change those today. I leave those as is, and you can look at the other options. What really and truly, you don't need to me any changes here. All right, on then. We can just go ahead and click, OK? And that will execute on Douala There. We have a new database, not one line off court on. Then to create the tables, we can just drill down into the database on then right, click on the tables, Folder on we see new on. We just want a new table and you can see that you have a bunch of options here. I won't get into those today, but we can just go ahead and see table, which will then give us some form off designer. So it's asking us, What do we want as a column name? Specify the data. Type on if you want alone. No. So I'm going to go ahead and recreate the same two tables in school veto that we just wrote SQL scripts to create. So the 1st 1 the column name. I want his I d. And they can just first stop to move across or a type here and impressed Albany. Just lose in a lateral movement to the right on. Then we can drop down and see the plethora off data types available to us in Microsoft. SQL. We have big into a bit. We have Charlie have beats the time floats. We have quite a few off them, so many that you may get overwhelmed and want to use all of them. But you can. You can actually just boy and create a table, maybe one with a column for data type, and you just see the different behaviors between an environ tire. 50 on in Hvar Charm ox or whole Environ chart. Here's differ from inch Are Home Alone is different from real small daytime, etcetera. Sicher There quite a few. So you can actually just go in and play our own with them. I encourage that. But today we'll just stick to the task off recreating those columns those tables with the respective columns. So I d was off. Type in on. We did not alone. No on. Then you see, Instead of following me, Tal specify any attributes to the right. It actually gives me like a properties window, and I'll just bring this up a bit so we can see more clearly, which then allows me to specify certain things. No, in order to sit if those primary key I first they have to right, click on I can see here set primary key. So know we know that it is the primary key. However, we still need to tell you that it needs the auto increment. So we find identity specifications. So you'll notice that certain terms me differ between the sq ALS statements on the designer view. But ultimately, whatever it is that you can do in the designer view, you can write a statement accomplish on vice versa. So I want to specify that it is identity, and I can just don't think this to change the value or you can just dropped on this dropbox and you just see yes on. Then you see that it still gives you that increment value and that seed value, just like we saw when we were writing or skill statement. So we leave those at one on one, all right, and then we can move on to the next column. So our next column in our student database was last new on this one was environ. Try and he can just type on. It will try to auto complete it for you with what it knows Onda. We just typed on per stop and then it also competed it and it moved on to the next option on then we did not alone knows on last name. So that's a no And then the next loan was first name Also environ tired Just let it auto complete impressed Stub on DWI did not alone knows on we had date off birth which was the time we did not alone No on Then we had enrollments deeds which is also did time on this one We are load to be No, no. Once you've completed all of this, what you need to do is save. So once you save, I used control s That's the universal Windows shortcut for receiving. So I said control s and then I got this dialog box, which then said internee for the table. So I want the stables name to be students, people K. And then what it actually did was executes that vory query or very similar quarry toe what we wrote it did it in the background. So all of this just generated that worry and executed it on. If we drop down into our tables, you see that it was successfully created. All right. So we can go ahead and create the second table in a very similar manner. New table, and then we get a fresh window. I noticed by default, it is db o table on the score one so it doesn't know what its identity is, where the ones telling it which is in life. So course I d on the data type was int alone knows is currently ticked. I if you recall, I said that once you have a primary key, you don't necessarily have to put on the not know attribute. So if Iraqi can say set us primary, he noticed that it's automatically on ticks that box because it can't bear primary key. Andi know. And if I tried to re ticket is going to see cannot be set toe alone knows once That column is a primary key. All right, so it automatically enforces those attributes Once you click and you click. Correct. All right. So we had the course i d. And then we had the title off the course, which he said was environ tire type off 50 Do not alone knows. Oh, on We skipped the step we needed to set course I d to be auto incremental, So we needed to say I didn't. The specification is identity, and I can just double click on everything is saying yes so we can move ahead. Andi, I'll show you how to go back and retroactively set that in case you miss it. So we had the title. We had the number off credits and that was integer on. That was a lot to be No. So we can leave that one ticked on. Then we had horse Hold on. That was environ Char and I can let it all to complete. But I can always go back and edit it, take off zero and make it five on that was not know And then once we're finished and we're satisfied with what we have done, we can just once again seems so. We just control and s and then it will ask for name. This was courses we could go okay on Douala so we can just go ahead and refresh this and we see that our tables have bean created successfully. So we have to databases with two identical sets, you know, structures. But then a database does not interfere with another database. So too identical databases can co exist as long as they have different names. SQL Server is not going to be able to know all these two are the same. They shouldn't be the same that are really curable the teams because the tables are within the context off this database. 9. Create an SQL Database Table Using Management Studio: our last video. We did what we usually do. We wrote SQL Script Accomplished Tasks. Now in this video, we'll be looking at how we can use the power off our sequel server management, studio toe, do the same functionality without us leading to write a line off court or s Cuba. So we are back to our similar screen. We have no script called here, but then we do have in our object explorer our table so we can just practica are table on. Then we can see at its top 200 rules. No, when we trick that, we see a tabula former like what we were describing from the earlier parts off these videos showing us all off the data on the respective columns that these data points are in. So we see one. We didn't put any ideas into the system upon to know on. We see that we have ID's going on counting. We skipped too, because remember, we try to insert a second required an Italian errors. So the auto incremental er said, oh, on attempted second record, was there It fails, but who is no longer a valid number to use in uniquely identifying anything, so it skips toe. But then each other successful one got its own idea value. So that is the point off using that identity attributes. Secondly, you can see that each last name and first name went in. So it really didn't matter the order that we put the call, we put the columns and then put the data in. The fact is, the data should have lined up with each column on the date of births on I just expand this and you see that because they're using date time even though we didn't specify already will specify the year months, and D it still appends a time value on it. So you can actually use this time big time value if you need toe, keep track off time. That exact point in time something happened on being time is very idea for that. On then we have the enrollment, which is similar. But then we see that we have some with values on, some with knows so once again because we mean it. Unknowable column. Then we see that we were able toe insert some records with no value on insert some with values. Now you may have noticed that I was actually able to click in the column on it became irritable. Andi, if you took note of that, then that's excellent When you are in this morning, it literally said it topped 200. So it ran a small query on it, got the top 200 rules, and he gave us a form where we can actually change or add new ones if we want. So first we're going toe. Add new rules. So this new students, I'm goingto using my last name. Andi. I have a friend old artiness on. Then That person's date off birth is 2000 and one. The ninth month, the 50 on. I'm going to actually put in an invalid value here for the date of birth. So that's the 50th day. We all know, at least in our Western calendar. There's no 50th day and I went to press enter, which would trigger the save on. Then we see here that we get an error message. The value in this cell was not valued, so it's telling us, Go back to the drawing board. There's something around, so I just edit it on now. Just take off that zero on others. First enter and you'd see that it just falls in line. Those red icons disappear. Onda, we have 1/12 required Williams rt so you can actually use this view toe? Just put in your records. If it is that you don't want to write the script off course, the script is more flexible because you can actually save the script and share it and execute it on another machine at will. Whereas this one it you have to be on that particulate machine in that particulate database , the fill it'll. So this is more manual for me, the script is more automated because I can always tweak that and share. So that's all you were both inserting data in your data tables. Using the editor, you can go ahead and insert more records on. You can experiment with inserting more reports in the forces to 10. Select Records With SQL Scripts: that is in this video, we will start looking at how we can go boat extracting data from over tables. In the previous video, we were inserting records and, we hopes, inserted up to probable 10 or so reports on No, we want to actually start pulling them back on manipulating the data. So we have our skirts peach open on. We know that the first thing that we do is tow specify which database is that. We're a bolter on the script against. Upon to know out, I'll show you that you have the use keyword where you say use, and then you give it the database name. Um, since we're using the editor, I'm going to show you that this drop down up here that says Master on it will say, Master, because Master, which is a system database, is the default database that is going to be selected for any script. We can actually use this drop down box on. We'll see all the databases listed there. So if we click school, then it changes the context off our quarter with us having to write use school so he can use either one personally, I prefer toe right use school because it goes on another machine on by default equals the master. Then it knows when the script executes, it uses school, as opposed to leaving it up to memory. To change this when you're about to execute the script and then you may end up executing a possibly destructive script against around database. So be mindful off that off course for this course. I wanted to be very versatile, so you can use either methods. I'm going to stick to the evil I know and use school on to get the intelligence you can press control and space. What? You probably know that already since you've gone through all the keyboard shark it's on, you will see that it actually gives you all the traditions on helps you to complete certain statements faster. No, today will be looking at the select statements, so we're going to create. We've gone through insert into on No, we're looking at select and select is doing exactly what the name suggests. It's selecting theatre from a table, and that is exactly what the quarry lion looks like. Select. And then what data do you want? Do you want the first name? Do you want the last name or do you want everything in the tables? If you want specific columns, you can write the mote in this situation. We want all columns so they give us a shortcut for all in the form of an asterisk which allows us to see select all so the Astors gives synonymous with all And that means all columns from on. Then we specify the table and we want students that this all we're going to get all off the student records in our students table. After executing the query, we see here that our results have come back on. Everything is as we saw in our previous video. However, in this view, we're not able to click in on it. It any off these records, So the select is really just to read the beta. So when you were on a select command, you're just reading the data for presentation me before reports. It's just giving you in a great former and you can actually change that. The options air here, toe team, the results, the file, or to change the results of fixed liken to speak that and then re execute the partner and then destroyed the difference in the results. So you actually get it in takes form instead of a table. Former on then file would actually export it toe RPT file so we can just leave it on great as the most. That's the most common. And that's the default one on we execute and we see it in great form. Once again, it is not creditable. Let's start looking at some variations off this select statements. So I'm just going to remove this one on. Then what if I wanted all the students I only wanted first names on last names. I didn't want to see the i d. That's also generated. I didn't want to see their date of birth or the enrollment it I just wanted to see first name and last name, so I would still use select on this occasion. I don't want to see all the columns. I just want to hold him. So I want first name on. Then I come a separates all of the columns that I'm specifying. So first name Homo last name on. Then we say from students on Then all of those errors go away because now it knows that first name and last in home from students who we execute once again. And we see that our results that no only reflects those two columns, first name and last me know if we're actually pulling these data points for reports on understand to add enrolment needs to this. So enrollment dates on. We want to see all of the students and their enrollment. It's but then we want to export this to excel on. We want sensible looking. Colony's first name is not, you know, reader friendly. It has a common F on common, and and it's one word. First name is not one word. Last name is not one word, but because were discouraged from using spaces in creating our column names. You didn't write for a space name, but when we printed told in our group we actually want the column names to look more presentable. So what we can do is say, as on. Then we can write the string that we would like to be printed for the column name. You actually can write it without the US on using the square bracket. Just see last names. I'm doing it both ways. so you can see all of your often so the as keyword is actually optional. You can put it here all right, and then enrollment day. It's like we discussed would look find off convoluted, Onda confusing. So understand to bring that to the next line on, I'll say in role men's date. And then we execute this. And then if we look at the headings, you see first space name, last space name Onda enrollment dates all looking more presentable and human readable. Once again, we can always break our lines because SQL know where the statement starts and and so, if you need to do this just toe, see everything more clearly, you have your keyword column on its alias. This is what we call in serious. HAMA COLUMN Alias Call column. Areas on, then That's one of the advantages off that square bracket. Because if it is a case where you inadvertently created our column with a space in the name , then you could actually use the square bracket to still operate. But then, that's not good practice. I don't like doing it, and I'm not recommending it. So the street here your column names like this either use one word like this in all lower case use. You're on the score toe separate the names that the words or camel case them. But then ultimately, for presentation, you want to use the aliases so that your columns from all looking So let's say you have to run. This reports your your administrators saying, Hey, I need this report you printed. You put on all the dresses on, then you need it in excel. You can actually just right click here in the corner. So by kicking in the corner, it actually selects all the data. And then you can distract Lee and you can Poppy, and it will copy it in tabular format and you can paste it in itself or you can actually see the results us on. The most common thing is see SV, which can be opened in excel anyway. So that's how we would go about exporting on extraction from the database toe a file for reporting purposes. So let's try another thing and you can actually run multiple selects in one script. What it will do is separate the results, so I want to say select star from forces. I didn't enter any courses. I'm not sure if you did, but when I execute this, I'm expecting to see something saying there is no record in the database. So you see here when you you and you have more multiple selects, you see the different sections for each grid results. So we see appear that's our first select on. Then don't. Here you see the results from or Second select, which we have nothing. And also take note of the fact that all of the column names are coming, but as they were created. So by using the star here, you're not afforded the opportunity to set some aliases. So you probably want to spell out your columns on set penises for when you're going to be exporting the data for another human being tree. So when we get back with star looking at more advanced extraction to eat on hole, we can go about filtering and selecting only some records on excluding others 11. Select Records With Management Studio: Hey guys, In this video, we will be looking at more advanced select statements and we will start looking at how our editor helps us with creating these statements. So, firstly, I am going to instead off creating a script to extract the records and read reports from or school database and students table. I'm going to use the editors function by drilling down into the database into the tables and finding the table that I would like to set rules from on. Then I'm going to right click and go to select top 1000 rules. Once that is done, it will actually generate a select statement. Onda, bring back all off the results accordingly. Now let us take some time and dissect the select statement that is generated for us. We see here we know our keyword select on from and we know that in between the select on the front we really have a list off columns. Then we have an additional key word being introduced here in the form off top on what this really does is to see give me the first X number off record. So the default option gives us 1000 ofcourse, the only have 11 records in already Tau Bay. So one toes and is more than sufficient to get back all the reports in our database over. If we had a database off 10,000 reports, then this would really just give us the 1st 1000 reports. So if I wanted only five records and I would change its value to five and say, Give me the top five And there we go. Another thing I want to point out is the fully qualified name for our table. Now, if we didn't dissect this line, you see that we have the Beetle B s name dots, the context or schema name, which, if it's in the fall sittings, would be DDO on another dot on then the table object. So this is actually the fully qualified table name, which means that if this database script is not within the context off specific database, you can actually just right out this entire lying here on it will no to contextualize this table to this database. So I'm going to change school up top here to school v two on. Then if I execute this again to record that we have no data in our school veto students tables understands quickly bring up that table and show you that that has no records on. We can see here that we would have school V two dark students that d v o dot students. So if I run this worry, even though I have contextualized scripts to school on a score veto, if I execute this square right now, it will know that I want the students table that is found in the school database, and that is the advantage of using that fully qualified database table name. So this would actually allow us to be able to query different tables from different databases all in one script. Five. No, let's expand our scope a bit on. Actually start looking at filtering no there times when you want only certain records. I'm just going to remove these constraints on execute for all columns once again and do recall that if you wanted off columns without having to type, the motor could replace off that with star over the ordinary that Ford give us every column . So if we wanted to filter and find, let's see only the students whose last name is Williams or we wanted to see only the Williams is in our database. That is a form off filtering because no, we're narrowing. Don't the results that a specific what we call condition? So SQL gives us a key word in the form off where on then it allows us to specify a condition afterwards, a condition could be like a column being equal toe a certain value or being greater than less than or looking like it. Those are all conditions in this situation. Once again, the scenario is that we want only the students whose last names are Williams. We see we only have one, so we should only expect one report to come back. So I'm just going to read a new Korea Don't here and with the comment this one out so I can just highlight all of this on Do use this bottom appear or once again, hold on key control and press key and see so once again, our objective history tree. Only the students with the last name Williams. So I'm going to add a new keyword where and then I go about adding the condition. The condition is hinged on the column last name. So I say, last name on my condition stipulates that the last name columns should be equal Teoh a specific value. And I'm just going to go ahead and produce this value here. So because we're comparing a string column or an environ shark column, it's a word we have to make sure that we're comparing it with also a word or string. So if I go ahead and execute this, then we will see that we are filtered out all off the data toe. Only retrieve students with the last name Williams. In this case, we only have one. But then let's try a more popular one. So we had a bullet off test students. Andi, I want any student with the last name student, someone to execute, and we only got back one. Now that's kind off strange. But it is very accurate because if we once again and I'm just going to uncommitted this on by highlighting this and picking execute, I can specify that I only want to run this work on die. I see that I had quite a few students with the last name student, except only one had student. Everybody else had a number attached to the word students, which then brings us to another type off filtering where we can filter on what we call Ah, wild card. The wild card is essentially was looking for a portion off text that meets our requirements and bringing about anything that meets that pattern. So I'm just going to, well, don't here and creates another query. So in this scenario, we want all the students with word student in their last name, which means we need to say where last name once again, the column that's in contention for the condition. But then, instead of seeing equal, we're going to see like No, like is Greedo. But it's also a keyword after or like keyword, we're going to employ the use off. What we call a wild card now are wild card symbol is in the form of a models are presented . Sign on that. This allows us to actually specify uh, character sequence that we are looking for in any block off text. Which means if I want every last name that has the letter I in it, I can see I So that's open. Politician Mark presented sign or modelers I and then model ISS on, then this is really going to say I don't care what comes before. I don't care what comes after as long as an I is somewhere in a block of text that is found in the last name column, then I should bring it back. And we can identify this by looking at the results that that at least three records should be returned. So let us test that theory and there we go. We got Macintosh Williamson on Williams, so it just looked to see it. Didn't care if EMC came before the eye on You can notice that it is not case sensitive, as this is a common I that I search for and this is a capital in the block of text, and it really didn't care what came after the eye as long as the block off text contained and I on the flip side, you can also say you want anything that begins with an eye, meaning it's going to see I I'm looking for and I on I don't care what comes afterwards. No, based on the results that I don't think we'll get back any cause you don't have any names that begin with I on in the same being we can actually see. We want anything ending in I by putting the wildcard character before the letter so I can see wild card. But the last character should be on eye on then. We also should not get any results here as we don't have anything ending in. I know this is not limited to a letter. We can actually once again look for any character sequence. So our objective, according to this comment above here, is to look for any name that contains the word students. So if we write while card, we don't care what me come before this character sequence, which spells the word students, and we dont hear what comes after it. So if I execute this, then we should get back every students with the last name containing the word students. If I look for the car to sequence that spells the word Williams, then I should get back at least two results because we have one student with Williams on one with Williams, and that is how you go about using that wildcard character now. Our next task involves us retreating the full names off the students in our database on their respective and Roman, it's Andi. I already drafted up the quarry where we said Select specified the clothes that we wanted from on the table that we want them from. We discussed in a previous video hole. We can go about making our column names a bit more presentable. What we're going to be discussing no is actually creating our own columns to represent our own data. No, we did say full names. Yes, the first name on the last name Comprise a full name, but then they're still into entirely different problems. I want one column that stores the full name. So I'm going to go boats creating a column on by doing so. I can Actually no SQL actually allows us to input any value that we want to be coming back with beach record. So, for instance, if I wanted to, money will print the number one or I wanted to print the words Hello class or rather, those students, as long as I comma separated off one of the last column, then I can actually do this and then you see that we get the first name the last name Random Carter that I put in just ones with no column name on this text. That's his fellow students know column name on over enrollment deeds. We discussed that if we wanted to give the column a specific name, we could just say as or just right on with square brackets, the name So friend, um number. And then I can execute this again and we see that No, we actually get that column with the colony random number, so this can actually be useful in certain situations. But based on our objective for this activity, we want a column that is storing full name off the students. So I'm going to what we call concatenation string or join the strings together or join the values in these two columns together to give us a full name so I can actually say something like First name on. Then, in SQL, we use the plus sign to say this columns value, plus that columns value Andi because it's in between two Commerce. SQL is actually going to see whatever comes by here as one value or one column for the results of, So I'm just going to execute that. And we can see we get first name, last name and over custom column that we just put in that we did not give a name. So no column name, But then if we look at the values that are coming but we see that we don't have any space, it's still it looks more camel cased than them human readable. So we actually need to put in manually the space that we would expect so if I say first name and then concatenation it on empty string. So I just opened a quotation, marks first the space bar and close the quotation mark on. Then I'm con fascinating once again the last name. Then we will see something that looks more reader friendly. So we have the first name to the left on the last name to the rights on Once again, we still have no column name, so we can just had the column name full name. And then because we have the full name, we can actually do away with the first name column in the last name column. So, no, you will have the full name on, and I will give enrollment dates a pretty name. So enrollment, dates and full name and then we execute. And here we have full name on enrollment is and you can always become as descriptive as you want against a student Full name. So that any reader looking at this report can know exactly who is listed to the left and what the dates are relating them to the right here. So I'm just doing your holy can Global using certain select statements toe customize how you present your data. OK, so open to know I have been focusing on the students. Now let us run some quarries with our courses table. I didn't create any video. Creating content of the course is stable. I did, however, give you a script that populates some records in your courses stable. And I hope that in your experimentation you would have populated here, of course, is stable with some data. So you have enough to play our own with for these few exercises. Now, up until now we have been focusing on our students table. I hope you would have populated your courses table. Well, you are experimenting Either way. A script files a touch of this video, which gives you some data to populate your courses table with so that at least these activities you can go through with some test data. So we've been focusing on manipulating strings and environs our values on No. I want to just look at how we can go about filtering on number values with number values. We actually can carry outs, numerical comparisons greater than less than we can look for equality. And these two examples will bring us through some of those. So we want all the courses in our courses. Still, with the number off credits being greater than two on, if you take a peek at the design off, our course is stable. You're call that we have tired of number of credits, which is an integer or numerical value on course score. So everything accepts number off credits on the I. D. Our inverters, so we'll just quickly say, Select on we want star from and we have to fully qualify our table. And then we see where number off credits, which is our column, is very ter than to. So I want all off the courses where the number off credits is greater than two. So if I execute this, I see that I get back four courses on. They all have values that are greater than two. I could usually have said equal to to to find out if there is even a to predict course on. If there were multiple, then would get back multiple. So it I know we have multiple three credit courses, so I understand that the three and execute that we see we have all of the three credit courses. In contrast, I'm going to select on. I just copied this query down here on going to just modify to say, select courses with the number off credits. Three. And let's so we want anything. With the number of credits, Maximum three board anything less than three is acceptable. So what we did here was to just say less than or equal to three. The number of credits should be less than are equal to three, and then if we execute this, we should see our three credit courses as well as over two credit course, and if we had a one credit course, it would also show. But nothing above three is appearing 12. Insert Data into the Table with SQL Scripts: Hey, guys. In this video, we will start inserting some reports into our database tables on today. Our database table off choice is students, so we're going to create a few student reports on we look at using on SQL script to do so as well as how we can use the editor to accomplish this task. Now there are a few things you have to do before you can just start writing your script, and one of them is to select the database that we're about to use or targets for our script . So once again, to select that database, we have to use the use keyword on. We specify the database by name, so in this case, we want school on. Then we go a boat creating our script. Now the keywords involved in entering our record or inserting a record are literally they're standing. This really and truly so inserts. And then we say into and then we're expected to see what database table on. Then what columns were inserting into so do were called. When we created our table. We created it with a few columns on. We can always just drilled on on the in the object Explorer on expand columns and see and refresh yourself on. You can see insert into on the table. Name is students on. Sometimes you'll actually see deeper right off the entire name. Bbo dot students. So BBO is just a keyword that is within the context off Microsoft SQL Server On it. It sits in front, off more stable. So even if you didn't put it there, chances are you will see it appearing there at the default settings. All right, so you can always just see insert into. But I leave off the db o for no on the table, name on. Then you open a parenthesis. You don't need to space it there and clothes on. Then, in these parentheses, you actually specify what columns you are going to be entering data into now. This is important because remember when we were configuring or tables, we set up one off the columns, which we called idee to grab primary key on auto incriminating. That means when we're inserting a record, we don't have toe put anything in here. We have to specify other columns that last name, first name date off birth on enrollment, it on what we cannot put or shouldn't be attempting to put anything here. So we need to specify. Also do recall that one off these was a load to be no, which was enrollment. So I can just over over and you would see York and this expand object explorer. And you see here last name not know. First name, not no date of birth. Not know, but enrollment be. It could be No. Which means I could specify up to three columns. I don't have to specify all four of these. So I'm just showing you that this is why we specify the columns. Because not ever record me get every bit off data off course. If the data point is not gullible, then it will not execute the script because I can't be putting in a student record. I can't be inserting something in just the students table. We though it's one of thes mandatory of data points. All right, so let's work through this. So the 1st 1 is last name, and I know some people don't like the type, so you can actually just drag and drop on. Then you would see that the school editors actually put in the square brackets. So that's also a feature that you notice When s UAL management studio generates some of the SQL for you? They usually put these in square brackets as as we go on, I'll explain what those square brackets are for. So we can just meets in much and we just drag and drop across on. I'll fill this entire record with all the data points. So we're inserting and used to wear inserting into the students table into the columns. Last name, first name, date of birth, enrollment date. And then you see that the red line is they're so that means the script is still not quite finished because we need to specify what values will go into each hollow. So we have to say values and I just break the line and bring that don't on. Then we open and close parent disease again in these fire disease. We have to make sure that the values that we insert here line up with the columns that were ordered here. So the first value has to be the last name and sorry in SQL. I find it easier to just use the single courts for a string. All right. I use double courts, and that was an accident. Use single quotation marks when using SQL Microsoft SQL that these so last name Andi, I will insert renal IDs. First name. So Reynolds lines up with last name. The next value should line up with first name, uh, e date of birth. The date time. Four months. That is default in SQL Server is year. Month D On that comes in the y y y y dash. Mm Dash e d. If you're familiar with Excel, then you're familiar with these date four months? If not, that's fine. When entering a date in s dual into a column that is at the time or even beats once it's a date type the expected four months should be the year. So we're in The year of dispersal was born in 1980. It's the first month on the 15th day. So once again, that's year Mourns be that does they expect to your shot and for enrollment beats, we will enter 2019 dash 05-0 watts. So that is the first off me. 2000 and 19 and then we can review this and ensure that all the values are correct on correctly lined off. Once again, these values have to correspond with the order that the columns appear in, and then we can go ahead and execute. And once we execute, we know it is successful. When it does this one row affected, that means whatever it is we did, our role was somehow affected whether it was created in other examples, you see where we can manipulate the data. Otherwise so, this is an indicator that the script was successful in manipulating that rule. So little stray and insert are another record. So let's write the script again from scratch. So once again we say inserts into, and then we choose a table name students. And then he opened and closed parentheses and and least all the columns somewhere to mix up the order off the columns a bit. I'm going to say first name first, then last name. Then it's off birth. That's a much of a fact. I'm going to do enrollment date, so I want you to see the arrows that you get when you don't follow the rules that you have set for the columns. So values on, then in the values listing, we have to make sure that first day we have a first name we can say Jodi Macintosh on These are purely fictional news on enrollment dates. I'll just say no because we can pass in a no value on. Then I'm going to execute this. The expectation is that we should get an arrow because we did say that date of birth is mandatory. Yes, we're here trying to insert a new students without a date of birth value. So if I say execute, then we see cannot insert value, no in tow. That column. So we do get on arrow Message feels so we know we need to rectify our script toe, reflect our rules. So I have to say it off birth. Andi, I have to values so 1994 the third month, the B year months D, and then we execute and then we'll see. Voila. One row affected. Let us make one more adjustments toe this stack of scripts. I'm just going to Instead of taking everything out, I'm just into curious the values on I'm also not going to insert the enrollment deeds. So first name last name, date of birth. You recall that enrollment did. It is no little. So it is not a mandatory field. So this time we want for So Willie Anderson Onda her deeds off birth is the year 1995 8th month, seventh day and then we click execute and there you have it. So this required went into the database without any enrollment. So to this point, we would have at least three reports in our database. Now, you're probably thinking this is tedious. Every time I have somebody to insert, I'm going to have to erase or, you know, rearrange or something. But then there is a we to actually do a multiple insert using one insert into statements, and that's what we're about to do. So when we're going to be inserting multiple records in one statements, what we do is we maintain this first line, the insert until table name, and then we spell out all of the columns. Let's just say all of these next two records haven't enrollment. So what we do is write the 1st 1 test on I'm just going to get very short handed. No students on dispersal was born. 2000 and one month. If they Andi kind devoted ashes that will use slashes It may be forgiving sometimes when you mix up your dear four months, but ultimately that's the date format it expects taken to stick to that Andi enrollment be it is No, no. When I want multiple records in one statement, what I'll do is just threat Hama and then go to the next line. Andi, Right, Another one off these understand city this line on poppy in peace and this person is tests just one student, one on leave the same date of birth. Or let me just change it so we can see the difference of the data on then, as many records as you have. You just write a comma and you can just duplicate that line on what I just did to duplicate is another keep word shark. But I held on on control on DSI envy, which is stopping and peace. But then, without highlighting the line, it just duplicates what are aligned the cursors on. So that's another needs trip that you can trail second dishpan troll CV and put in as many as possible, right? Each one comma separated up until the very last one in the series and off course editor with me. No mistake. Importante, you told you that this is invited. So I'm just going to go ahead and fix up these records a bit on day. We will then see how this works. Alright, So I've made some adjustments to this script. We are inserting into the table and I just put some differences in the data so that we can move ahead on Do not be confused. Us toe Any repeating data so that you can see where each record is unique in its own right on Guy left some off the enrollment dates as no put in some on under showing you that as many records as you have. You can use this. Just use that technique and string them along on. Then we will execute one time and then we can see that we move from the usual Wataru affected toe. Seven rules affected because we inserted 1234567 years students. So as many records as you put in once it does not fail, you will see a message like this indicating that you have done something right? No. I know that we've been inserting, inserting, inserting, and you're probably wondering, you know, is this icon I'm not seeing the daytime. They're seeing a sentence. That's his Rose affected. I want to see the data. That's fine. We do have another video coming off where we start extracting data. But then until then, I'm going to show you a very quickly to view the data on also hole. We use the editor toe, replicate this kind of functionality. So I did some comments to this script on I'm going to show you how we can see a script file . So you've written a script like this. Maybe you were tasked with writing the script to actually put in all the student records, and then you have to submit it to a database administrator or whoever is going to actually pull the trigger on. You need to have this file exportable. You can actually just save the file control s or goto file and click. Save on, then that will prompt you as to where you want to put the files. I'm just going to put this one on the desktop on. I'll see insert students on the default file extension for any SQL Finally start SQL so I can just see that. And then you will be able to come back or open this and execute it on another computer. Or in another sitting where you have a school database, I need to be his name. School. Andi, All of the data needs to go into those tables. So that is how you go about exporting your scripts and saving them for future reference. Maybe you're working on it. I need it for later. This is how it's done. 13. Insert Data into the Table with Management Studio: our last video. We did what we usually do. We wrote SQL Script Accomplished Tasks. Now in this video, we'll be looking at how we can use the power off our sequel server management, studio toe, do the same functionality without us leading to write a line off court or s Cuba. So we are back to our similar screen. We have no script called here, but then we do have in our object explorer our table so we can just practica are table on. Then we can see at its top 200 rules. No, when we trick that, we see a tabula former like what we were describing from the earlier parts off these videos showing us all off the data on the respective columns that these data points are in. So we see one. We didn't put any ideas into the system upon to know on. We see that we have ID's going on counting. We skipped too, because remember, we try to insert a second required an Italian errors. So the auto incremental er said, oh, on attempted second record, was there It fails, but who is no longer a valid number to use in uniquely identifying anything, so it skips toe. But then each other successful one got its own idea value. So that is the point off using that identity attributes. Secondly, you can see that each last name and first name went in. So it really didn't matter the order that we put the call, we put the columns and then put the data in. The fact is, the data should have lined up with each column on the date of births on I just expand this and you see that because they're using date time even though we didn't specify already will specify the year months, and D it still appends a time value on it. So you can actually use this time big time value if you need toe, keep track off time. That exact point in time something happened on being time is very idea for that. On then we have the enrollment, which is similar. But then we see that we have some with values on, some with knows so once again because we mean it. Unknowable column. Then we see that we were able toe insert some records with no value on insert some with values. Now you may have noticed that I was actually able to click in the column on it became irritable. Andi, if you took note of that, then that's excellent When you are in this morning, it literally said it topped 200. So it ran a small query on it, got the top 200 rules, and he gave us a form where we can actually change or add new ones if we want. So first we're going toe. Add new rules. So this new students, I'm goingto using my last name. Andi. I have a friend old artiness on. Then That person's date off birth is 2000 and one. The ninth month, the 50 on. I'm going to actually put in an invalid value here for the date of birth. So that's the 50th day. We all know, at least in our Western calendar. There's no 50th day and I went to press enter, which would trigger the save on. Then we see here that we get an error message. The value in this cell was not valued, so it's telling us, Go back to the drawing board. There's something around, so I just edit it on now. Just take off that zero on others. First enter and you'd see that it just falls in line. Those red icons disappear. Onda, we have 1/12 required Williams rt so you can actually use this view toe? Just put in your records. If it is that you don't want to write the script off course, the script is more flexible because you can actually save the script and share it and execute it on another machine at will. Whereas this one it you have to be on that particulate machine in that particulate database , the fill it'll. So this is more manual for me, the script is more automated because I can always tweak that and share. So that's all you were both inserting data in your data tables. Using the editor, you can go ahead and insert more records on. You can experiment with inserting more reports in the forces to 14. Update Data in the Table with SQL Scripts: Hey, guys, in this video will be looking at how we go about changing data that already exists now. The first conferences that we would like toa update all enrollment dates that are currently and this is a perfect test bed for a still right or first off, it's daily. So our keyword for updating our record or changing a report is literally the word updates on before anything else, we have to ensure that we're in the CART database table. So we'll just change that from master to school to ensure that when we run this opiate command, it actually looks at the correct table in the cart beta bees. So once again or key word here is opiates, and then we specify the table. In this case, we want to change the enrollment dates, and we know that we have enrollment dates in our students table, so we have to specify students on. Then the next keyword is set after set. We specify the column or columns on the value that we would like to put in each column. So we have updates the table students set on the column that we would like to update his enrollment it's, we specify that, and then we specify the value that we would like it to be. So we would like to specify that all of the enrollment dates that Arnold get updated toe 2019-5 bash 01 Now, as a quick recap, we can just take a quick look at our table and we see that we have quite a few no reports. So at the end off this, only the records that are no should be updated, meaning we should still see these values on changed. No, we need to have a condition. That's what specify which rules will get affected. So or condition is where in the enrollment DEET is empty. Our know. So we see over here that we have seven records that should get affected by this worry. So we need to add a condition using or were statements. Just think for more. Select, and then we will see the column enrollment deitz on because we're dealing with No, you probably would want to write equals no on that would not throw in arable. What it would do is bring back zeros affected, because when we're checking for knows, we need to say is no on On the flip side, you would say, is not no meaning. You want any column that is not currently empty? So that's one of those little watches that might pop up during your experimentation on if you haven't run into it before. No. You know, if you have before No. Here you have solution. So we say where this column is No. We want to update the same column with that value, and then we can go about executing this, and then we will see the seven rules have been affected. So we go back to our select statement and we just execute it once more to see orbits. We see that there is no more any null record or no enrolling date in our students table. And that is our first update statements. All right, so task one is completed on the next task would ask us toe update all enrollment dates. And then I left a little warning here that we would be off the 18 multiple records and you should be careful. And I'm going to explain why. I know we can replicate this court and I'll just re type It's off dates, students set enrollment dates, which is the column that we said We are going to be abating on. We're going to set all enrollment. It's toe one specific value. So 2019 on, Let's try 06-0 orchestrated 10 So we're all beating all of the student records to see that everybody in this table, as at the time the square was run, was enrolled the 10th off June 2090. The reason I said, Be careful is that we don't have any condition on this statement, which means that this statement will be executed against every single record on I can tell you that if you mistyped or you make a mistake and we're all human, but you may make a mistake and write this on you wanted for specific rose and you leave off the condition. Then this will be executed against every single record, which may not necessarily be desirable. So it's a database administrator or developer. You want to be very careful and deliberate when you're running Europe the statements and ensure that the statement, if it doesn't have a condition, it is perfectly fine on if you only one specific rose you ensure that you put on your condition. So for this example, we want Toby it. Everybody's enrollment it to the 10th off June, so we'll just do that execute and we see that we have 11 rose affected. So if we go back and look at the updates, then we will see that everybody know has the 10th off June as their enrollment beat on. That was our desired results. Once again, if you want a off the specific rose, you make sure that you include your condition on DNO. Onto our final task for today's video will be updating the students with ID's seven on 11 now, At the start of this course, when we were creating our tables, I would have said quite a few times that each record should be uniquely identifiable on what makes each record uniquely identifiable is the I see that is given. So we implemented an auto implementing I D column so that every time a record goes in, even if all the other information is the same, this I d number or our value would be different on DSO. This will alot us the specifically target. Any record in our database regardless off watches in the other Hollows. So in this situation, we want to make an update. Toe the names for students. Seven. Andi 11. So if you don't want to identify them by name, because maybe they have the same name. It's hard when you have to Students with the same name you're at a loss for which one should specifically get off bitten. So by using over idee column, we can know that even though we have students and we have students, one is seven on one is 11 so I can target seven and make a change, and I can target 11 and make a change. So let us execute those on. Another thing that we will be doing is changing their first and last name. So we'll be exploring how you go about make it a bit to multiple columns using one opiates statements. So we would say opiate or table students, and we want to set first name be equal to the G Andi last name to be equal to mark their marks. So here we have two column on the Value Piers. We have the first column on the value that we're sitting and second column on the folly that were sitting so as many columns as you need to make an obituary can discover separate them and change them along on. Do you do all of that up until you get to your condition, which is where on in this situation we want to update the students where the I D is equal to seven on once again. Remember, that idea is a number problem, so the value would not boy in quotation marks because it's just a number. So if you're dealing with a virus, are environ, tire or char anything with words are alphanumeric characters. Then you would use their single quotation marks. But then, when you're dealing with a column that is not a text based data type, then you don't need your quotation marks. So when we execute this, the expectation is that the student with the I devalue being seven is going to get changed is going to get the first name on the last name changed. So we're just execute and we see we had Juan dro affected so we can go back and just re execute or select on Take a look at student I D seven from Know that student to test to when we execute, we see that students seven is no T. J. McDermott as expected. So we can just go ahead and do the same thing for the next students who would like to off dates, which is students 11. And we're changing this students. So, Roddy Sean So when we execute this, then we should see students with I d number 11 get updated in the system. One were affected and we look again. That was student six Best six. When we execute again, we see that we know have dawn on, Roddy. So that is how you gullible making an update via script. You can scroll to the top of this file which is included in the notes for this lesson on I gave you some other names that you could use the update your student records just to give you some practice on. You can also player on with updating other data values and setting off other conditions that would specify which records get updated 15. Update Data in the Table with Management Studio: Hey, guys, In this video, we will be looking at how you go boat abating records or making changes to existing records using the tools that the studio the management studio has given to you. So we had looked at it in a previous video, and we looked at inserting reports on we had gone to the table that we would want and right click it. And then we said, Edit top 200 rose on that actually literally shows us the edit or update this data. So by doing so, we got this screwed form off the data on From this view, we can actually modify the quarry that is giving us this data here by clicking that SQL so s European. It may be in a different position based on your screen and your resolution, but you can just look for it right there on it would show you the actual Corey that is being executed. So if you want more, Rose would have seen from or select statements that you can see top 200 or top 1000 or you can change it to what you want by default. It's giving us the top 200 on this greed. Actually, I lows us to go in and make a change as we please, so we could insert a neuro or we can modify an existing rules so I can change this. Students 11 toe. Last name will burn on this one for Troy on. Then we I pressed. Enter on That change is made on that so you would go about off dating. Obviously, if you put in an invalid value, then that orbits would not go through and the editor would give you an error message. And here you see that this air message came up saying that the value one is not valid value or the type of column that we're using, which is the daytime. It would not commit that change until it gets a legitimate values, understand, to re enter date of birth and press enter. And there you go. It has been updated. So that's what you go a boat updating using the sequel Management server studio 16. Delete Records with SQL Scripts: big guys in this video, we will be looking at how we go about removing data from our database table. So we have three fasts. Old line. We want to believe a student with the I d. Value one on this will be our first activity for this video. So the key word used for a delete statement is literally that worked really on. We say from our table students on then we have to, just like with our update and just like with our select in some cases specify the conditions upon which something should be deleted in this case are condition. Is that the students? The i d value one should be deleted. So use our wear clothes on. We specify the column. I d. On the value that we're looking for. So we want to believe from our students table where any i d value is equal to one and then considering and I'll just go ahead and select the top 1000 again, considering that our I d column is our unique identify. For any students, it means that only the record with one in the space for the I. D column will be the leader no other record will be touched when we look for the record with the I d. Value being one. So we go ahead and execute this demand and we will see that one road gets affected. And if we refresh our so that statements, we see that we no longer have a students with the I D Value want on in the same way. If I try to execute this again, then we should see zeros affected because there is no record with idee value being one next up. We want toe removed the students by the name off the J. McDermott. So we will say, deletes from or table students where and in this situation we're going to have to use a more advanced wear clothes here, So we want to make sure that we're getting the student with the full name. This is still risky because you could have had too touchy my dear amounts in your system. And if you use this method than everywhere that the first name is stodgy on the last name is my Dermot, you would delete all of those reports. It would not be able to distinguish exactly which one you would want. Which is why using the i d. Value on the I. D. Column is the safest way to know which record you're targeting specifically. However, for example, sake we will go ahead and say first name is equal to and our value for G Andi literally Andi because no, we're seeing the condition hinges on this being true, Andi, last name equals McDermott. So here we see where we can have more than one parts toe a condition, a condition that is not limited to one column on one valuable we can have on. And you can also have four or would say, if the first name is equal to touch it and I want it or if you don't find that one and you find something with the last name equal, my Fairmont and I will also take it. You can experiment with. There were clauses and the motive conditions. I would suggest that you start experimenting with those on your select statements as opposed to need statements, but for no, we want any students that has the first name, the energy on the last name being McDermott. So if we have a student with TJ and read then Taji really would not get leader on because her name is Doctor G. Andi, my Dermot. So we can execute this and we'll see that we have one are affected because it went into the data set and look for first name to J on last name Dermot, which was Heidi Number seven. If we execute again, we see that she is no longer. There also notes that there is no way toe recover deleted records. It will be very careful and deliberate when deleting records. There is no recycle bin that you can just go back to and recover. And lastly, we wanted to eat all students with names containing the word students. So we'll just write and mother the command delete from students where on this time or condition will have to employ the use of a wild card. Because we said with names containing the word students, which means student student, one student, six test students, all of those should be taken into consideration. So say delete from students where and we can say first name like, which we know is our wild card expression and then we save models. Students on model ISS. The once again. This is going to see. Look for any students where the first name has the word students in it. I don't care what comes before the word students and I don't care what comes after the word student. But then we said name. We didn't specify whether it's a first name or the last name. So in this case, through this, follow the instructions on we said name. So we have to say first name or last name like students. So what this will do is say, delete any students that my tubal first name containing the word students or my tubal last name containing the word student. So when we execute this, the expectation is that every record that we have that according that has the word students in it will be erased. We can go ahead and do this, and we see that four rules were affected. So when we execute, you should see 1234 being removed from or results it on. There we go. We're don't 05 students. And take note of the fact that the idea column it does not re assess itself and recalls and say, Oh, this is no one, and this is too. And this history, whatever volley was assigned at the time of creation, will be there going forward. So that's the end of this video. Once again, we went through some statements to delete some data on you can go ahead and experiment with different conditions on Be very careful, because if you see the leads from students with the outer condition, this will actually remove every single record from your table, so be very deliberate and very careful when executing these commands. 17. Delete Records with Management Studio: you guys continuing from our last video where we did a few still commands to leave some records, we will look at how we can go about the leading records using the editor itself. So if we right click our table on once again, go to edit stop 200 rules From this view, you can actually select the raw that you would like to remove on the go ahead on right click and press the orders press delete on your people so we can go about moving. You don't number 11 on. We just prefer delete on. At least here it will ask. Are you sure you want to delete these rules so you can destroy guess and then it will do the script in the background. Andrea, just parting a script is a nice automated way toe effect multiple changes multiple times. But by writing one time, However, if you use the editor and you had a 1,000,000 rose, it would be a bit more difficult to comb through these rules to find the ones that you want to delete. Or you could actually this modify the SQL statement to find the exact rules that you want to delete, and then you could just slip them all and press delete. It's a matter of which one is more efficient to you. You remember that scripts are actually insurable, so if you do it on your machine, you can share with somebody else who may have the same problem or need as you do with their days of this. 18. What are Relationships, Foreign Keys and Normalization: Hey, guys, congratulations for making it this far. No, we move on to a new part off databases on this aspect of Italy's design and development, usually people EXP express a bit off Langston anxiety towards understanding and completely airplanes aunt to be fear. It takes a bit off understanding and practice to actually gather the concepts that are required for it to be proficient. So this part off database design has to do with relationships and foreign keys. And the bigger elephants that's always in the room when this topic is being discussed is normalization. Now let me start off with relationships. Relationships are essentially what what the word means. They speak about. Whole beater is related now. We were started off the course by seeing that we're using SQL Server, which is a relational database management engine or relational databases. Polski Rescue of my school. There are a few others on the markets that are also relational on. There are some that are not so relational databases. They thrive on their ability to have released son ships. Andi, I keep on seeing that relationships, so let me explain. Relationships are essentially talking about hold. The Dita relies on each other. For instance, if you look on my screen, you see that I have what we call a database diagram or entity relation diagram, which is showing entities in the tables in the form's off entities. So a table once again is the collection off different rose off different entities so that in database terms, the whole table is called an entity fire. And so this is a little niece I was working on outside off this course, but I'm just bringing this up. So we have a bigger picture of completed and fully relational, fully normalized database on. So you see that each entity or table with all of its colors right on. Then you see that there is something here connecting it to another table so you can see leaderboards is connected somewhere somehow, to the user stable predictions is connected to the user. Still, and so this visual representation off relationships can help us Still, you know, conceptualize what purpose a relationship serves eso, you know, not show. It's literally seeing that entities are connected to each other. All right. They shared the it that they share some dependency. You can't have one with all the other arts. It's very difficult to have one without the other. That's what this connection means so we can have a user on. If you notice the key is always going in one direction. Right? So it is going from this leaderboards on the keys pointing to users, which means you can't have a leaderboard entity without some representation off the user. You can't have a prediction in the system without some representation off a user on the same for much. So this database was being developed toe be the storage mechanism for ah, work off prediction application. All right, Yes. I built one of those in the past, Andi, so people could log in longer prediction. So off course, when they longer prediction they needed to state which much it is that they were going to be predict making a prediction on. And so they had to choose the much All right on. Then we would have to know who is making said prediction. So off course, I had to keep a record off the user on dso. That brings me to the second part off relationships which is called the Foreign Keys. Andi on. This is all very high level, so off course will be getting into more details as we go along. But I'm just giving you a high level representation off relationships and for NT's. So a foreign key is essentially just the bit off data that you need toe help. You identify the related entity on That's all Spanish that made a show you in in practical terms, and then we can revisit that that explanation. So let's stay on our prediction Steel. So we have a prediction. We all know that once we create a table and we're creating an entity, we need a primary key. And so that is depicted right here by this is the key m them on. We would have gone to creating tables and sitting primary keys and all those things so that shouldn't before in on. Then we have a bunch off properties that we know we put on, and this doesn't display the data type, which is really the material in this setting in this world. So we have an i d. For the prediction on, we know if it's an active prediction, are you know if it should be Continentals? The many. Any comment that was that joker, Just another property that was there. And if they spend entire system for you to understand that but that is not really important on. Then you see something much? I d okay, Andi, if you notice Well, it's not always like this, so I don't want you to necessarily t This is a rule of thumb. But sometimes you actually see this connection originating directly from the the primary key. Our foreign key. Rather. So in this situation, it doesn't. So that's not really something to go by. But you see that we have much idee and much I d is the representation off the much from our other table. So in other words, it would be difficult for us to have a prediction on. We actually spell out the much because we're actually creating the much is here. We actually created the timetable for all the much is that are going to occur. I was store that in a table. It would be kind off foolish and redundant toe. Keep on repeating all of the's details. So you know which much the prediction is being created on. All right, So what we did was have a table just four matches on. Then what we do is each much an I d on. Then each time a prediction is made, we just referenced the I d off the much that is being predict IQ. All right, so let me say that again. We have a table solely for one entity. It has no dependencies that much can exist without having anything else. Well, in this case are much needs our group. So you see that much still needs to be associated with their group on. Then the much can exist without a prediction. So the much table has all the marches on, then a prediction can only exist if how much is associated with it. So that is that kind of association. You always have toe map out what beat off leader can exist without any other data on. Then figure out what else what needs this pre existing data in order to actually survive. So let me use the user stable since this one has absolutely no dependence is so I user is I user user exists, right? I can create a user tomorrow. I don't need anything else. In this case, I don't need gender or anything, so I just have a user who has either his active. The deity was created here. She was created on certain contact information, but that's all I need on a user. However, to make a prediction, I have to see which users making this prediction, so I can. It would be foolish off me to create a prediction and write about the person's first name, last name on. Use the name every time or just the using it, because then I metal a spelling error over here on the user name is something over here on , then trying too much them back. That's going to cause problems. And when we get into more advanced borders with energize and so on, you understand what I'm saying. But what we do is we know that the I d is always absolute ideas, always unique. So we want to reference this unique data points to make sure that we always know who is making a prediction. So we create a prediction stable on. We give the prediction all of its properties that it needs, but then we say okay for who is making the prediction. We just want to reference that person by I d. So that user I d hear much is the primary key value here. So if the user is one here than the expectations that when user one makes a prediction, all of this gets filled in on the one would be the value here when user 10 minutes a prediction, everything else gets filled in on Day 10 is the value here. So if you look at who made the prediction are we see User i d 10 I can go back to my user steel and I see 10 and I see oh, travel war Williams created that prediction. So that is the relevance off a foreign key. The foreign key courts don't all off the repetition offbeat across the tables, Onda lows you to have a unique reference points between two tables on whatever data needs to be shared amongst them. All right, so that is the example with predictions and users. So in the same way with the much is the prediction can only exist on a much so instead of seeing oh, where where were make a prediction on team one and team two and then somebody comes along and makes the same prediction on team one and team toe on. You were Pete that again for that entry? Then we say this prediction exists on much I e five. If we go with our much is stable, we can see much I d five and get all the details here so we don't really have to repeat the details across the tables on that principle is also called Normalization, where you're looking at repeating data on reducing it. So that is why this video is called relationship normalization on foreign keys, because they all go hand in hand with each other in reducing redundancy and repetition again across tables. So pretty much the only repetition you should have would be our foreign key values. Because if user 10 minutes, 10 predictions and you see 10 predictions in the database having that repeated i d over the details off user 10 only appear once, and that's in one place that we can go toe all right, Onda. That is essentially, in a nutshell, that is whole foreign keys normalization on relationships work for the rest off this section will actually be looking at creating tables and creating relationships on DWI will build on the database that we have already started on. We'll start looking into more advanced beat these concepts to so 19. Create a Relationship in SQL Server: you guys, welcome back in this video, we will start fleshing out are putting in more tables into our school management database. No coming off the last topic off relationships, foreign keys on normalization. People generally see that all. They don't know where to start or hole to start. They know that stuff needs to be related, but then sometimes they get it in the wrong direction. You know, when I'm designing a database, what I like to do is start with the tables that I know are comic. When I say I tell me, I mean that they have no dependence is and they can exist entirely on their own. So we already started that process here with our school management database, where we said courses and students know a student can exist entirely on their own day. It's student is the student, right? Um, the student can be there technically without, of course, because it will pay the school fee for the general fees. There are low to come on the campus, but they may not be taking a course, so the student can exist with no association toe, of course, just the same way I can create a school, Andi, I'm offering courses and I don't have any students just yet. So of course can exist on its all. And that is that. That is something that you can actually employ when trying to decide what is atomic or not . Think about it in a real life scenario, if in real life, this element or this entity can exist without dependency on another one. All right, so that is the first step. So when designing a database, I always start with the atomic elements those that can exist by themselves. So we're actually responded. Creates about two more tables for this section. To begin this concept, I'm going to go ahead and use the editor on. I'm going to create another table on this one will be for teachers. So we all know that we always start off with an I d primary key can say I d or just teacher i d whichever one it depending on your naming convention on, we'll give I the type off its on course you go down here. There are professionals that this by No. So you know that we just will make its the identity on. We also flagged US primer key, which we can distract Lee and say set primary key. So I'm just going to go ahead and follow the details off this. Give me a few. All right, So after that, some columns here I give the teachers record Our first thing in my last name on the deed joined. And I used the wrong data type there. Start to see the time on a four secret just say dates the reason city on. Then what we would be tempted to do on. I could completely understand if you attempted to put course I d or associate this lecture or teacher with a course right here on off course or tolerable. Foreign keys and primary key. So you're thinking that OK, so clearly a teacher has to have a course teaching, which is which is fear. But then a teacher can also teach many courses. So that is something you need to take into consideration. Because then if we put an i d here for it. Of course I did. Then we're We're only associating this teacher record with one course at a time on dso. If this teacher is teaching another course hold, we accommodate that we would have to repeat all of these details. Andi, sit another course I d which is not the optimal way to store our data in a relational database setting. All right, so that is one of those covey. It's that people run into where they would try to put the foreign key at the wrong place. Because real, eventually the teacher, like I said in the beginning, can exist in my school and not be teaching because maybe as a freeze, Mr Maybe is a specialist. I don't know, but I can have a teacher. I can hire a teacher on because I have no students. I'm offering courses, but I have no students than this teacher is not actively teaching a course. So I'm going to leave the teacher table at this Andi save here. So no, we have three teams like inter school and refresh on. I have three tables at my courses of my students and have my teachers once again, they're all atomic. So you probably say Okay, so hold y Really It's the teacher toe. Of course. No, Let us explore once again in real life, I have a school, right? No, I have a course. When I created this database force, the course was there. Um, yes, I was a teacher on, but I had no students. What you did was enroll into this course on then the platform that you're learning this course on then said that me travel Williams, the teacher? Yeah. It will be associated with this course because I have many courses on this platform. Andi, you the student will be associated with this course with this teacher on. I'm sure you're doing work. Multiple courses on this platform on DSO. That kind off relational relationship needs to exist between the course. The student on the teacher so setting a foreign, Kian anyone off these tables would not fully accommodates that kind off three part relationship. Because at some point, something will have to repeat in these three tables, with details being spread all over the place on what we want to reduce is a spreading off details in multiple places. So when we see this situation, we're trying toe associate two tables with each other will lead toe some duplication off details. In the long run, what we do is have another table that is going to facilitate these relationships. All right, so what I'm going to do is create a new table on called it Enrollments. I'm just going to say I d you get the primary key on. We make it at entity. All right, so it's also implementing Andi. Then I'm going toe have once again let me just say this table from No. So, you know, we're working with this is enrollments so on Enrollment can only exist if we have a teacher with the course with at least one students on. Then we have an enrollment of the student is enrolled in a class being taught by this teacher, right? So then we create teacher I d. So this is the person teaching, and it's teacher i d. So it has to be an integer aren't at least based on our design. So far, we have students, I d because the teacher is teaching this students and I'll just keep may using consistence . And then we have the course that these that this teacher and the students are both going to be involved in. All right, on that is the beginning. And I received this quickly so it can get stopped. Hated that is the beginning off the creation off a foreign key. Now there is another step that needs to occur, so no, we need to actually have the relationship. So, yes, we created the foreign key properties, but they're not foreign keys yet because we need to actually establish the relationship between this on the teacher table on then. That is what will give us what we call. It'll be integrity when you know that the relationship on the constants are there. Once of constant is there then teacher I d can never, ever have a value that is not present in the teacher table. If I have five teachers on their number ideas one through five, then teacher I d can never, ever get a value off 10. So as it stands, that can happen because there no constraints. There's no actual relationship. It's just required saying, Oh, this is the teacher i D. Teacher. I should go here, But once I create this relationship, we set up the constraint. We discussed constraints in our design that that's a limitation to what can actually occur in this problem. On what we will do know is actually just right. Click on the 1st 1 which is teach i d. And then you see that they have an option relationships that brings up a dialog box, which allows us the ad. All right, then that creates some generic thing and you see enrollments in romance. That's not really what we want. So the name off the foreign key here usually indicates the tables that are related to each other. So we want his enrollments to be related toe teacher. So we'll click on this one on. Then we go over to this side and drop down the tables on common specifications. We can speak the ellipsis button here on then That brings up another the yellow box, which is reiterating our name on the one is here for me because I had started it before. So this is what years would look like, and that's fine on then. What we want to say is, What is the primary key table? No. Do we're call or primary key for a teacher? I D is stored in our teacher table, so our primary key is coming from or teacher table on the column that has that primary key is our I D column see that? So when I switched it, the teacher gave me the drop down on di Select I D. Andi. I want the I d. From teacher from the teacher table to be directly associate ID with which column in the enrollment. So it's already it's green notes the foreign key tip because it knows it's a foreign key table. I'm initiating this relationship creation from the foreign key table, so it's going to meet that assumption, but it doesn't know what columns. So I'm going to go ahead, and I have the responsibility off naming my columns such that I can know exactly what the relationship is. All right, so there is no direct stipulation that teacher i d has to be named that way. I could have said puppies, I D. But that would be very bad practice on my part because later on the line or somebody inheritance database, they would know why puppies is related to teachers, right? So I kept called a teacher I d. So that if I did this work fell asleep and looked at it the next day, I could remember that teacher I d was earmarked to be the foreign key column for my teacher relationship. So teacher I d. Here in our enrollments table will be directly related to the i D column from or teacher, too, and then actually go OK, And then you probably just want to be the name here just to see enrollments teachers just so that if you look at it tomorrow, you know that this foreign key is between the enrollments and the teachers on. Then you can trick those, all right? And then you see, it made a change and is asking us to see on then bys when we got when it asks us to save, it's telling us that we're about to me some changes toe the teacher and enrollment. So that means it's making some structural change in the buck in tow. Associate the teacher on the enrollments so I can just click. Yes, on that will happened that that will be accepted. So I just go ahead and do that same activity for students idea, and I'll do them slowly. So we have the 1st 1 and then we can just go ahead and add, and now we're going to do the same steps or we just pick on our lips Spartan in the tables and column specification space on Then this time we're doing student I d. So I say I want a primary key table to be students on. Then when I drop this down, you see all of the student columns. But I know I'm only interested in the i d andi on the foreign key side. I am only interested in student I. D. S. I click OK on, then This one was automatically knew because I didn't edit it. So that is what should happen. That is what probably happened for you and teachers on that is what really should happen. So once you set off, that relationship would actually rename it accordingly. All right, on. Then we noticed that it's the same step, regardless of which one we click. So even if we close this end into it on the other one, we're still going to get by this style of box. So what I'm going to do is stay here on just go ahead and add another person to have 1/3 1 toe. So I just click. Add on. That gives me 1/3 foreign key relationship on. Then I'll just think that And then this time we're dealing with course. So courses on drop it donor we seek course I d. So you see that sometimes I have I d sometimes outpour society so and of course, is stable. I could have said I D but I think I explained earlier that you know, it's confusing to change it. It's up to you and your pattern on your standards. Eso horses, horse I d on in the enrollment stable The foreign key. How long is course I d. All right, so we see a direct much there. So sometimes I don't like doing this because having course I D in the home team landed in the foreign key table can lead to confusion at times, but so I usually just use idee as the ID column so we can just go ahead and continue click OK, and so we have Or three foreign keys and romance, the courses, enrollments of students and enrollments, the teachers. So that's it, saying that enrollment stable is no directly related to courses, students and teachers. So once again, the whole purpose off are being. Those relationships is to set the constraints between what values are load here relative to what value is available here. The value is not available here, meaning there is no course with i d 10. Then the enrollment still cannot have 10 in this column, because this column is directly bound to the I. D column in or in our appearance table. All right, so I'll just go ahead and say this and you see that it is trying to be changes to all the tables that were involved in that really, since its creation process. He could guess on vawa that we can refresh the tables and see our newly created table there on defy, drill down and click on their keys. Then you see that this enroll unstable actually has four keys, all right, But then, if you take key and be keen attention to the direction off this key, you see that this one is pointed to the left, and that's the PK. So that's showing that's primary key, and then all the others are pointing to the right to show you foreign keep in Sequel Express. Destro, they do not have that capability to show the database diagram. You may have to don't load that separately, or use developer to get that false society. But at the end, off the B, you can always just ensure that those keys are there. And you know that those are created properly. Now I'm going to add one more column to this table on. I went to parties agreed on. The reason behind this is when we have a student. Do you know? Of course he needs a group, right? But then they're all a tummy, so I can put greed in the course because there many students during the course on do that that wouldn't make sense. Trying to trap agreed in the horse's stable on the student is doing many courses, so he's been thought many grades. So it doesn't make sense me trying to put the greed on the students record. So what I would do is just put it in the enrollments section, where I know that the teacher who is teaching the students through this force is going to give them agreed for this course. Right. So that is, that is the kind of thinking of the employer when designing a database. So once again, this enrollment stable is going to have the teacher who is teaching the students? Andi. Many teachers can be teaching many students at any time on they can be teaching them multiple courses right on. So for each instance where you have this specific teacher teaching this specific students in this specific course, we give them this specific greed. Even if they repeat, we know that each record is going to have a unique greed toe these three values so I can just give that inch. I'll just knew that in char on degree with a little greats of the C one. Let me see it, too, because it could be a minus someone to give them two characters to put in that grade. Andare receive changes that table on so we have that relationship table. This table is a bridge toe alot thestreet e was to be related to each other. All right, on, there are situations where you have, you know, you have a table and and you're going to put, you know, foreign key in that table. So this was not one of those scenarios, so it's not a one size fits all. You don't just look at one relational database and then say, Oh, I'm going to use those exact techniques to build my own database In this school management system, the different rules apply to the previous system. The previous database that other Sony in the last video, where I was doing predictions for work with matches. All right, so you just have to understand the core concepts. Onda apply them as you go along. So once again, is this entity going to be atomic or is he going tohave? Dependence is in this situation. A course doesn't have a dependency. A student doesn't have a dependency on a teacher doesn't have a dependency. All can exist by themselves, however, for an enrollment or occur, I can only have our record off a teacher teaching our students on this course through on enrollment. And so my enrollment stable has the dependence is on the teacher table, the student table and the course table. Andi. Well, it has its old columns afterwards, so you could always fresh assault on add on beat off enrollment or date off, start, date and lock on. Indeed, so this would actually create an instance off when somebody being a student was taking a class being taught by this teacher the start of this Mr the end of this Mr those kind of things you can always put into this. But it does have three. Dependence is on three different tables. All right, Thanks for watching. You remember to leave any feedback or ask any questions if you are still unclear as to what was going on here of the quick to reach. 20. Insert Records in Related Tables: you guys, but this video will be looking at inserting reports in tow tables that have relationships. So in previous videos, we would have already inserted some reports into summer for tables. And I discussed the atomic tables versus the ones that time dependence is or appearance tables versus child tables. That's another expression you here in detail based, um, and Fletcher Rd to be his terminology. So we have courses and we created someone. I'm distracted in tables and goingto it topped 200 because no, we're supposed to be comfortable with our editor on. We should be proficient enough in this school to be doing our civic statements if we want to see what's there. But for expediency, for speed, I'm just going to use the editor for most of these operations. So we didn't put in any teachers, understand, to go ahead and insert about three teachers, all right, so I did three teachers and notes that because I used the date as opposed to be, it's time we don't see a time stamp on the joint column. So that's what dates looks like. I think it was the beat off birth at the time Stump Red So I'm just showing you the different date that that's what once again be. It may not be available in previous versions of fiscal and in your corporation. You may not be using the latest one, so I'm just showing you all of the different scenarios. They can act a parting to your context. So we have three teachers. We have a few students on. We have a few courses. Know what? We don't have our enrollments. We don't have any teacher actively teaching any students through any course. All right, so that is why we created the enrollment stable so we could actually find them together through relationships. So I understand to edit the top 200 on, then you notice, based on our previous design and based on what's on the screen. No, all that is a load in these columns are integer values. They're just ideas right on. Great. Well, we had a string value alone for this, but these are only willing to accept into your values. So what this means is when we have a table with relationships on, we set up the relationship to be on the primary. Key columns on the ideas have too much on. There are integers your primary key. Your foreign key column is always just don't have the referencing I d toe. The detailed record on that was discussed in the first video off the Siri's. So once again, we're not repeating the teachers details every time there's an enrollment. Because if John is teaching multiple courses and we don't need to see John, John, John, John, John, John, John, every time you see Jane, of course receiving John through the database right, that would lead to missing data. And that's a part of normalization where we're cutting out all of that repetition. So if you realize you're in a situation where the details off something is going to be repeated more than once, then you probably need to look at creating another table toe. Have that detailed information once, and then what you do is only repeat that referencing I e. To that detail information. So that is that this whole normalization works. You see things about first normal form. Second normal form third normal form. To me, those normal forms are important when you're looking at already miss idiot and trying to fix it. But for me, I skip past the normal forms, and I just see designed the data bees in a normalized form from the one. If you see repeating details, put them in a table for themselves so they only appear once on, then repeat the idea associated with for whatever it is. So that is what we're doing here. So instead of repeating the teachers details because John is clearly going to be teaching the top three courses Isham, Sq and PHP, we want to just repeat his I d. So, well, associates. The i d to with the course is 12 and three are on going to give him one students for each. So Jodi McIntosh is currently in all three off those courses being taught by John. All right, on the reason I'm doing that is the fact that we have ideas, returns, inserts any Oh, I did. I did alone? No, on my designs. So I just popped on over to the design quickly on Di did alone? No. Which means that we don't have to enter all of the violence. The only thing that is really, absolutely necessary is the i d. So we can do that. So As it stands, we have given John with I d toe the course schools under these the courses html sq and PHP . And let's just say he has no students at the moment. So we're going to associate teacher i d to Who is John with course I d one I can press, enter and see. It seems it's a no students. He's also teaching course I d too. And he's also teaching course I d three. No, this kind off looks stupid. I can appreciate that because then you're moving away from clearly seeing who teach i d to John Andi here. No, just looking at tools and threes and numbers, All right? No, from a human perspective, it may look foolish because we prefer to see letters than numbers. But the computers Dr Maura number. So it's actually more efficient to get this number on. Find all the details in somewhere else than toe Have those details repeating every time on that will actually lead to a slower database on a slower application overall, because I figure you're building your database here to use in an application on that's point number one point number two Is that because you're using your database in an application feeling What's these? ID's would be much easier from an application standpoint. If you're not on up developer just yet, that's fine. But I'll just give a high level overview. What do you remember that when you're when you be filling over 10 creating these associations, you would kind of be dynamically associating the controls in your up with the values from the I. D columns. And so you can pass that into your database quite easily to court on. You wouldn't have to necessarily manually come in here and actually be writing twos and threes and ones and twos, right? Because we're learning database in the context, off database and sign. I'm the showing you exactly how this data goes into the database. So back to what we're doing. We actually have associative John with courses 12 and three. So if I look at this enrollment record, I see John teaching course one, which is html asi done also teaching course to which is SQL Andi. He's also teaching force tree, which is PHP Andi. She has given no grades because, well, quite frankly, he has no students, So I did say that Jodi is actually taking all three off those courses. So know that we know Jodi has enrolled because she looked on the timetable and saw that John is teaching these or these and three courses being offered by John on. Once again, you could have put on the semester details and whatever, whenever and even the campus it's your database. You can do the design no majority is interested on. Jodi's going to click enroll. So she goes on to the up the school management up school management system and she and rules in these three courses, all being taught by John. So know her. I d, which is three. Gets associate ID with John on each course. So this is what I was saying about her petition. Imagine if we had John, John, John, Jodi, Jodi, Jodi and html PHP on desk. You well on. It would be very tedious when balloons into more than one. And then you have to track all off the johns and all of the Jodi's. We can just see which students is doing this with John. We just look for a student i d three and we see everything on the agility in one place. All right, so you could get messed up if it's in more multiplication. So we try to keep that repeating data One, please. No. Here's another scenario. Patrol Oy has decided to also do Esti well, Onda hb he's not doing HTML. He's only doing SQL and Ph. B. Now, here's a beauty off using our relationships, you can actually repeat this. So we know John is teaching that course so we can just put in John twice and understand this. So we know Johnny speech in that. And we did see the student in question would be patrol his ideas. Six. So we just put in six on six. I'm sorry. Something's going on here. Let me over. All right, so we're associating Pay Troy with done once more so. And he's doing courses one on the three only. All right, so here we see teacher I d to So we see John is teaching all these courses and he has to students so far in these courses I know we can track directly. If we were to pull all off the each demo students that John is teaching, we could just look at all of the course I d. One on teacher I 82 then we'll see all of the student ideas coming back in that quarry. Alright on We'll get into that eventually. We're just looking at inserting Onda. We look at whole we go boats pulling reports does I mean, obviously we're only using numbers, so it will take a bit more harassing off the data toe, actually accomplish viewing certain things. So I'm just going to use this time and I'm just went to fill out some more data I wanted put in about 20 records off enrollments on way Reconvene. All right, so I said to internal it'd 11. I'm sorry. It's kind of tedious and annoying, and I'm sure you're you're experiencing the same thing, but I don't think you've done this as yet. What I understand to this display something to you. So do remember that by setting off the relationship would have enforced certain constraints way shouldn't be able to put in a teacher i d. That does not exist in our teacher table. So if in enrollments I try to put in the value 10 on, then it's a still of the rest and then move on to the next line. Then it will tell me nothing happened. It was not committed and the error messages that it conflicted with the foreign key constraints, the conflict that so it's not really telling you all. There is no value 10 in the parent table, but that is essentially what it is. What is happening. The constraint is that no teacher i d value can exist in our relationship in our foreign key column that does not exist in the primary key column off the primary key table. All right, so that is what that that brings a boat. So if we were repeating details and seeing John all the time, then somebody could come in on DSI. Oh, spell his name Kwan, because they're trying to be cute or funny or something on then that would lead to some data integrity, because then we'll do wonder. Who is this one word? It one come from on. Then we really wouldn't know who this teacher was. So by using foreign keys and relationship where enforcing certain data integrity across the board to ensure that no value goes in without it being vetted by a second source sold to speak so I can see this little one and press enter on when impressing enter. It's telling me another parable than this time. It's with the students because I don't have a student with idee, too. Aren't they deleted that one? So I'm just going to change that idea to 12 on Douala. So I have a rich mix off students and teachers, and it's all looks confusing by this point. If I went all the way off, it would have been even more confusing and rightfully so. So I mean, inserting isn't the Q test Um, process and procedure and even looking at it, it's not very intuitive with a D. C is not as intuitive as looking at the teacher and the students and the courses. So what we'll do next is look at whole wheat and global pulling meaningful data from this table. When it looks like this 21. Run Queries on Related Tables (Inner Joins): Hey, guys, In this video, we will be looking at some advanced quarrying techniques. So, yes, we went through some select in previous videos, and we went through some filtering and other more advanced topics. But then, when it comes to having multiple tables and sheer desire across the tables, we need to employ certain other esque UAL Commons Andi functions not are in order to facility that kind off court. So I'm just going to go ahead and create a new query window on. We can select our database to be used, which is school on off course. I always say use school above here in this group so that we can be absolutely sure, even if we move in, turn off the computer that were is in the right database. But so let's start off barreling a few select. So we select star from teacher on. We have three t 14 with some dismantle on a responsible C and V on that will actually duplicate this line on I'm just into run these Sorry, that school courses on and students All right, So if if I have these three commands and I see executed will actually execute all three and give those different results for each different result. Peons on. Remember that you can always just highlights the one that you want execute. And it will give you just that. No, I want to run a select star from my enrollment stable on we saw home messy and confusing that looked Do that alot Sorry a little from that's embarrassing. So execute on then we see that we have all of these numbers and literally they mean nothing to us sitting down and looking at them because all we have to do know is go back and wept The trend finalise teacher to on. Then we go back and final to a student. Three on that bucking and four thing is really just tedious and absolutely not necessary because in SQL we have ah construct called joints so we can do joints in our quarries. All right, so this script file will be included in the resource is for this video on. I'm going to show you how you can go about being a joint. I joined pretty much sees. I want toe literally join the data from two different tables based on the one thing that they have in common on based on the father. We just went through relationships and foreign keys and primary keys and those constraints . We can always assume that what two tables have in in common would be the value in the primary key on the value in the foreign key parts of the value in the foreign key column for enrollments is what is something that it has in common with the teacher table, all right, or in the student I d. With the student table. All right, so the syntax for a joint is you see, if select so the select part stays the same star from on. You can choose your primary table and romance. Since that's the rial table. We want to see the details off all of these enrollments. Want to see which teacher is teaching this course? So his teacher toe. We want to see the details off teacher toe on all of the courses that Teacher number two is teaching, so we select star from enrollments on. Then we say inner join and you have multiple joints. You see inter use the left, you see right, but for now, let's just do dinner and you'll see which table is is it that you want to join the data onto? Which table is it that you want to look for the details in? So we want the details off the teacher. I'd be too. So obviously the details off anything from teacher I d would be in our teacher table. So they say, Teacher on. Then we want to see on. So giant on teacher table on the condition that which column from Teacher Column is I d on . Remember that what they have in common is the value in the I. D. Card off the teacher table on the teacher i d column off the enrollment stable. So inner. Join this new table teacher on the condition that the i d from teacher table and you can always say something like teacher dots just to make sure you're dealing with the right one in case both of them have the same name. All right, so against a teacher dot and any intelligence is asking, which column is it? We'd already wrote I d is equal to on. I can just see enrollments dots on. I will see teacher I d on then what? This will do is say give me everything from enrollments. Onda also joined on the details from the teacher table where the I d much is that so it will bring back the details. So teacher toe, where teacher toe much is the idea and teacher table will bring back all of the details for Teacher three, where those two values much etcetera. So that is why if we had 10 in the other room and stable as a teacher, I d. It would cause a problem because we don't have that those would never much are. So when I run this query no, we see and I said, Select star no, we see that the details are coming back. So for teacher I D to once it's tool in the enrollments table. Then it's bringing back the corresponding required for two where it's three and then romances bring about the corresponding reports for three on the same was on and on. So teacher one teacher, one So we can know, see which teacher is actually teaching these courses. But we have more details that we need to see because they don't know the student ideas. We don't know the courses so I can enter join as many tables design into so I can know. Go ahead an inner join on Let's say we want the student details alongside the teacher details on. Then I can say the student i d. So student i d He's going from the students table on day. I'm going to energize that on the condition that students so that that's another thing. So you have teacher and it asked you dance and of course, is so it's good to just keep those things uniforms. So that's That's my design floor at their and then students dot idee is equal to in roll mints. Dark ill. In this case, I want students student I d on. Then I can execute this again on what will happen is that you see No, that the teachers details are still there. And then to the right, it's a pending columns for the students. So where their student i d. Three. We're seeing the details for the students. All right, I'm going to do it one more time on At this point, it's probably less arm or confusing because at least we're seeing the details. No, but then we're seeing all of these irrelevant information because we don't need to see the ideas on for institution. That is, You probably don't want to see the joint on the date of birth, of the students on on what stuck on the course, and we seem or possibly a real even better. So we'll go ahead and refined that as we go along. So I'm just going to join courses. Fun on what the course is, What is the courses stable? Having come on with over enrollments Force I d remember. I did say that he could get confusing when you have the same name across two tables and this situation would be extremely good to see courses dot course I d. So we know exactly which table this course ideas going from versus which table the other one is going from enrollments dots on. Then we can go ahead and do this on. We'll see. We're getting back all of the courses and the number of credits on the course school. So we're getting back all the details off everything. So that is what I'm saying. That we don't need toe repeat the details in more than one tables, right? Because if we have all the details, or at least our petitions. One place we can always cross reference it with another table on in a setting where you're selecting then repetition is quite fine. What you don't want is a repetition when your story All right. So you don't want tohave John McDonald, Five different places in five different tables. You does have his details. One place on you. Make reference to him by his I. D. On dso when you need to find more details on him, you just inner join that table with his details based on that foreign key primary key relationship that you would have set up so we can start refining this obits. Um, I'm going to leave this square, and I'm going to write another one where we're going to select the specific columns that we actually want for or report. No, I did start off by saying we only wanted the courses being taught by John Gibson. So I want to bring back John McDonnell. Sorry. So we only want to bring back where the teacher ideas to. We want to bring about the first and last name off the lecturer as well as the students first and last name on the tackle off the course being talked. All right, so that's all we want. So we can actually go ahead on that. Just copy these inner joins since already have them here. But it would be good if you just retyped them for practice purposes. So I'm going to actually just go ahead and select the specific columns on then. This is where this query and kind of becomes more tedious because then you see that first name is present in two different tables, so we need to be very specific. Which first name it is that we want? We're all right. So I want a first name, last name and in first name, last name again. Whose first name is tools? Last thing is that the students are the teachers. Those standard things. So what is a common practice in database? Design is, in essence, where when you have a table, you can actually just press space. So, in this energy in line here, I have teacher understand press space, and I'm going to say something like tea. All right. On, students somewhere to call in s on courses. I'm going to call it. See on what this those is. Wherever we would have said, teacher dot some column, we can actually just see alias dot That column alias that that column alias. But that column. So this me extracting the quarry much easier. When? When it comes to this tuition. So we don't have to write a teacher dot First name on then. Right? Student, That first name. We can just see t dot First new on sometimes in inter since me not necessarily chipping for this part. So I'm just going to run the first. Where with this. So selected that first name. From now on, I'm just going to say enrollments on even this can get an alias right here. I can just see e All right, So where I had the word enrollments, I can replace it with you. And so this quarry just strong inside. Look at that. Look at the difference there. All right, so once you have a table, he can just press space and put on that alias on. Then I'm just going to run this query quickly. So when I bring this back, I should only be seeing John or whatever is in the first thing column off lectures coming back. And there we go. So first name is what is coming back on? Only the teacher first names Because we did specify this alias and we're reusing its here. I did see a limited Sriram this query. I did say that we would be bringing about the first and last name of the teacher on then that no brings up another problem. Because if I'm only seeing first name last name, first name, last name, I still don't know who is so so I have to put an alias on the column itself so that it prints box a teacher First name on. I'm just going to do this. I'm going to break the line and just do it cascading form. So teacher first name comma movies in the next line. I like a lot at my core is being very readable. So it's good when you have readable Corey's and I can just duplicate this since ah, lot of this is going to repeat last name S O. C. The intelligence is chipping in now because it does realize that OK, t is the teacher table set as t right. Remember that as keyword. Who could have used as so last name on the last name here on the reason for the square brackets? Is that because you have spaces in between? What we want is a column hitters, sq aware, turning sq of the stake it literally as this. With all that, he would try to create certain keywords ultimate. So we're seeing will use any keywords. Just eat this literally. As you have it typed. So have the teacher's first name and last name, and we also want the students first name on last name. So, as I said, we can just duplicate these lions because it would repeat and we just refined. So students, students, this would be last name on. Then finally and remember, we used Thomas for every column separation upon to the last column on. Finally, we want the course title so horse would have the area sees. I can just see see that title, and then we can give it a custom header course title on. But this is this is essentially how we go about earning reports. You know you're working on organization. They ask you to put a report directly from the data bees. This is how you would global writing the squares when you have multiple tables with multiple data across the place, you know, you just find out what they have in common right up your inner joins on. Then select the theater. That is absolutely necessary to the report that the person asked for, because if your superiors us for this report, you really don't need to show them the I ds and the you know the core school. Maybe want the course sports's include course, Bullseye. Just duplicate this at that comma and I'll say, see dot core scored on this is force for all right. And then also, when you're when you're going to be rendered reports you don't want missing names, you want something human readable. I think we went through this on win through our select worries. You want to make sure that the person can read. It's you know, it looks neat. So we just tried to keep some uniformity and some quality some standard because your supervisor or whoever is reading report won't see a database, not all the time, so they won't know that enrollment be. It is here as one word and lower chaps. But at least when they get it in a dump in, you know, in a presentable form you wanted to book on. So we run this query and we see teacher, first name, teacher, last name, CNN so we can clearly distinguish who is who. We see that Johnny's teaching Judy. Oh, I haven't error here because of Jodi appearing in last name and first name. Second, despite back on didn't change that. All right, so we can just re execute this. That's why it's good to check your work. So we have John MacDonald's teaching Jodi Macintosh on all of those sports titles on then. If we only wanted to see John students, which is that the way I think we started off, then we can. I had a were close, so we went through the were close. When we'll tell filtering on our were queries on the condition would be It could be that the lecturers first name is maybe John. But I wouldn't do that way. I would prefer to filter on the i d. So I would say I d is too all right. So I mean, maybe a supervisor came to you and said, Hey, find all the students that Johnny's teaching You know, if you said John, maybe I have more than one John's in the database, so she may be in the conversation. You know, it's John McDonnell that is being spoken off, but if you just try to do it on that, then give me. Have more than one. John McDonnell's in your database So the I D is the most unique thing to separate the two John McDonnell's that my do little bits. So I would just find What is the i d off the specific person being asked for on dit is to in this sick case, So I'll just say we're t dots I d using on my s is equal to two on DSO. This will actually go ahead on filter everything. Oh, it's just to show me John McDonalds and all of his students. So you see that Jodi is taking issue Mill SQL PHP with them. Patrol is doing HTML and PHP on Tricia. He's doing SQL and patrons also doing ask your on Those are the core sports as you go along . So, of course, there are different ways to run this kind of report and cut out all of these repetitions and refine it. But at the end of the day, you want to ensure that you have a good understanding off whole. You global doing your inner joins when you have relationships or you bring about data from different sources on Finally, how you go about filtering the filter in the where clause should most often then more often than not, be last. All right, so we could have ordered by. So you do an order by after all of this. And let's say we want to order by course title So we say See dots on we can see title. So in order by the title on that would come after you were on then here we have Okay, Johnny's Teaching Patrol and Judy in each day. Families teaching those. Those are speech we students and these areas SQL students. So based on the way you are the detail, you can get different things from it because you know it's done all the time. Um, but then the students seem are seemingly random on all over the place. But at least we can know. See, where is HTML students yes. To who are speech? Be students he has to on he has three s dual students on DSO. Once again, based on whole you write, you query, he can get different data from it And you want to be nice and clean on ready for export. So if you want to export this, we can always just right click and we can see see results as And then we choose the data type that we're more comfortable with So we can docs because see if we can open in excel on Then we just say students enrolment reports And then we saved that and then we can send that off to our supervisors. Off course. The title didn't give the full story because it's really students on John. Inhuman John. Students and enrollments read eventually being stunned. The data we have here, so this skirt file will be included in the resource is for this video stick our own CNN 22. Left, Right and Full Joins: Hey guys, in this video, we will be picking up where we left off last time about inner joins, and we'll be looking at other types of joints that we can do between tables, namely left joints on right joints. No. Before I proceed, I just want us to review some off the decide decisions that we need on over and room unstable on those would be surrounding the necessity off values for the foreign key values . You recall that we allowed knows on each of four foreign keys and off course. Those design decisions could be changed because on enrollment really shouldn't occur if a student and, of course, don't go together. However, in practical terms there can be an enrollment off a students in a course, but no teacher being assigned to that enrollment just yet. And so, in keeping with that design decision, I actually added a few more rose. A few more rolls off data toe our courses table teacher table on enrollments table. So here you see that I selected all from enrollments and I have a few columns. Don't to the end where the teacher ideas actually no on, but we have students being assigned to course with I D seven. Just know Lecturer being assigned to that course just yet. I have included scripts that will insert additional courses on additional teachers into the database so you can go ahead. Also made a design change. I changed teacher two teachers That's animal table teacher to teachers as lift off the S. So I made that part. Since you can feel free to change that design or just modify the script according to what you have, no, let's take a look at the concept off left Onda, right joints? No. Once again, let's think to look at our select statement on our enrollment, Steve, which is bring back every single enrollment that is currently in the system. Well, at least the top 1000. Since we are fewer than 1000 then we're getting about all of them on. We see here that the between ideas sending through 21 there would be no lecturer or teacher assigned to those enrollments. Now here, Everton and energize statement, which is going to bring back all the columns between the two tables and Romans and teachers . That's what the star means. If you remember on the expectation is that the inner join is actually just going to bring back on either side where the condition is met, meaning the teacher I d in the enrollment stable and I'm using that Ellis is TNT E for in Roman city for teachers. So E, that's teacher i d. Most exactly much the i d in the teacher table on. If that condition is not meant, then that rule will not be returned. And so the expectation is that when we execute this query, the rose 17 through 21 or ideas something through 21 in enrollments will not be returned because there is no teacher in the teacher stable with an i d off note. So no will not matched any off those values. So I pressed their five on here. We see that both where results are here stuffed on in the top results, we see that we're bringing about 17. A total of 17 rose for the enrollments. However, in the lower stuff, we're seeing that we're only bringing back 12. Which turning me is that there were omissions as we went along. So hollow 17 through 21 are enrollments 17 through 21 were not returned because they did not meet the minimum condition. Now, in a scenario where you wanted to bring back every single enrollment, regardless off the presence off the data too much this exact condition. That's where your left joint comes in. So the left join is really easy. It's just a modification off a typical inner join where, instead of seeing inner, you literally just a left on. What happens here is that it will bring back every single thing on the left side, off the quarry on I'm seeing left side of the choir because if you read from left to right and I'll just put this all in one line, then you see that the enrollments table is to the left off the joint on the teacher Sabres to the right off the joy. So I left join you seeing bring back every single roll, every single rough data from the table. It's on the left on. If there's a much, then you can bring back any matching data on the table on condition on the rights. But even if there is no matching date are still bring back that column on the left. So when I execute this quarter. Expectation is that this will bring but every single enrollments just that there should be a few more knows in our results it So let's execute and see what happens. All right, So I wrote an entirely different statement for our left joints. You know, we have three quarries being running this script alone, each one being displayed here in its own stock. So up top, we have select star from enrollment, so we know that enrollments would have about five. Noel rules. Andi below we have the inner giant data set which should only have about 12. So this one is only bringing about the exact matches. But then the last one, which would be the results that from or left joint, would actually have every single enrollments. All right, so we do have enrollments and see where bring about ideas 17 through 21 with the enrollment details. But because there is a no value in the teacher i d. There's unknown much because, well, it didn't bring back the corresponding data from our teacher table. So that is the pull off a left join. You may be faced with a situation where you know we need back everything from one side, but then you need additional data if it's available. But the table the main table is absolutely important on that would be this situation. Enrollments are absolutely important. Maybe the administration wants to see ho. Many students have enrolled in a class, regardless off if they have a lecture or not. So this worry would actually bring back every single enrollment that exists regardless off the presence off a teacher. The reverse may also be true, so we're nowhere catering to the table on the left, and we could always stop. The soldier could put teachers on the left side and the words and unti on enrollments on the right side on that would actually bring about every single teacher, regardless off if they are currently assigned to and enrollment our two a class. But instead of doing all of that and that switching, we could actually just use the right joint, which would basically do the reverse off the left joint, where would bring back everything in the table to the right off the joint Andi, regardless off if it has much in leader in the table to the left, so here. I've written select statement with our right joining us That so this time the biases on the teacher stable where you want to see every single teacher I included. Some teachers inserted some more teachers, not all off them have been assigned one enrollment. So the expectation is that we're going to see every single teacher andan enrollment if it exists and if it doesn't exist them the enrollments would be blank. So once again, I have all of my parish. You're stuck toe have my select star from enrollments off top. I have my inner join here. I have my left joint here on. Then when I go down, I'll see that I have my rights here. And if I scroll through, see that picture details do repeat for each enrolment for which they are associate ID friend. So everywhere there's I D to in an enrollments. That's where John Matalon is. However, we can see here that these three lectures that were just added we're not the same as any class. So we bring back all off the lecturers on the right. Right? So remember, we're using right joint. So the teacher stay abreast of the rights or bring about every single lecturer. Andi, if there's an enrollment and we see the details, if not, then everything is no. And you may note also that the that the enrollments that were not given a teacher i D and other school about quickly these enrollments with IEDs 17 through 21. They're not being returned here because once again, the right joint is giving the bias to the teacher to the teacher stable, which is on the right. So the left joint gives by a store chipper tables on the left bring back every single record, regardless off. If there's a much are nuts on the right, join basically does the opposites, so it brings back everything to the rights, regardless off. If there's a much for the data that's on the left, the final statement I'll show you is the full join, which basically says I want everything on either side. If there's a much, then bring me back one rule with all the details. If there is no much, then bring me back from either side, regardless on that is what the full joined us. So we have the left. We have the right and we have the full on that's not for yet or inner so inner cities through. And make sure that there's an exact much left says If there's an exact much, then that's fine. But bring me everything to the left side off my query right? Joined us opposite Off left. Join What was right isn't what was left is no right on the full joint says I don't care which direction you are, just bring me back And if there's a much then fine on DSO the last results that we're looking at below. Actually, he picks what happens during the full joy. So we see here that all of the rules that would have been missing in the inner joined or left joint, right, joined. They're all being brought back right here so, or lecturers without on abiding city on over enrollments without a lecturer on all enrollments that have lecturers assigned or teachers assigned. Everything is going back on that support off the full join so you can go ahead and play around with this. The three script files have been included in the resource is for this video. Have fun 23. Grouping Data using SQL GROUP BY Clause: Hey, guys, In this video, we will be looking at sq ALS group by statements. Now the Escalade grew by statement is an excellent way toe literally group the data to see more distinct data. Onda. We will look at how it is written, the difference, variations and constitutions that one must make in using this statement, Andi, In the rest of this section, we will actually be looking at aggregate functions that rely on this statement. No, I did start off by saying that the SQL Group by statement can help us to identify a distinct data on. I just want to inject a bit off this ambiguous Asian there, with the select distinct statement so select, distinct, actually says that whatever date I'm about to select, I will look for only unique Rose. That's what the select distinct does. So if I did a select distinct on enrollments than what it would do is if I had a rule that C wrote 12 with the same data across the columns of them selecting, then it would only bring back one rule. So let's take, for instance, and here's a practical example, at least in this enrollment stable in real world. There should be no two rules that have the same I d teacher I the student i d enforced idea . At least taking idea till the picture. No teacher student on course should be repeating in your table. So there's that. But then in this situation I do have a lecturer or teacher teaching the course, and I have two instances off that appearing here is a one for one and four. So I quickly wrote up to statements wanted to select. That's the teacher idea and the course I d from enrollments. And if you want to get fancy off course it Candice, inner join everything. So you get about more details. But just for the expediency, I'm just going to use this quick example. So select these two problems from the enrollment stable. And here is all of enrollments. When I run this query, it should bring back 17 rose only with the teacher idea and the course I d known. We look at them together. We see that we have a few repetitions here. We have to end to hear toe into here We have one and 41 and four etcetera, etcetera. So the select Distinct is actually going to say, OK, since I'm seeing these two the two and two twice, then I only need to bring it back one time. And that's the purpose off. Submit this thing. So I'm just going to run that line and then you can compare the data. All put. So here we see that we're now eliminating on were Don't from or it's 17. So we're don't from 17 rules to just nine. So this let distinct actually eliminated all off those repeating roles. So if you're in a situation where you have repeating rules up debatable, you really only need one roll represented in your results and then the distinct keyword right before the columns on, right after the words select that is your keyword to eliminate those repetitions. That is, however, not why we're here. Why we're here is the group now grouping. Actually, there's something a bit differently than the select distinct grouping actually will bring back only one record. That much is all off the requirements, and it brings about this one record by actually doing like a bat grown mathematics kind of thing to actually keep track off the number off rules that were there, allowing our still then layer certain mathematical functions on top. So if you need muff like, for instance, comb toward average or the maximum or the minimum those kind of things, then you need to use our group by to actually bring back that one plump off the same data as opposed to the distinct the distinct will. Just, um, it grew by actually Trump's, so we'll just do some examples off this on. As we go through this section with a difference, I forget functions. Then you will see exactly what I mean by the group. I actually helps with the mushroom article function. Now we'll just modify this square that's already here selecting everything from our enrollments and at a group by on the thing with our group eyes that it is the last statement. So, in other words, if you have an inner join, it comes before the group by if you have a where it comes before the group by. So we're going to use that were in this situation, someone to see where on let us run some course against teacher I D to So we want to do some things where teacher ID's equals toe another's execute and sift out. So this is all the data relating to teacher I D to no, The first thing to note with the group by is that what? The second thing to know? What the group by is that every column that is being referenced needs to be included in the group by Andi. This is a blessed curse because the more columns that you put in on, the more variations off data is the less grouping that can actually occur. So let's take, for instance, I d. I. D is different in every rule in this results that so if I included I d in my group by, I literally would see no difference. All right, so the same way you will write the select int Andi, specify the columns of the same way you would group by column Hama column, etcetera, etcetera. And if I execute this query, then you see that there is literally no variation in the data set that comes back. That's because because each role is unique, then there is nothing to group because it can't group distinctly different data or rose. So when you're running a group by. You have to start with a process off elimination. What data is not absolutely necessary to my grouping. So let's say, for instance, I d I really don't need to group I d. All right on. So my group by starts with teacher idea of this food. So no, that's I've taken idea out of the picture. It depends on what kind of beetle we want to get back once again. So let's see our scenarios that we want to see home any or all of the courses being taught by this teacher. Which means that I just want to see the teacher I d on the course, the teacher already in the course of society and the course. And you can see that these start repeating because he's already here for course to. He's already here for course. One, etcetera. So obviously the variation off the values with student I d across the forces would skew that results so I can just eliminate student idea just committed off quickly because I'm going to use it at a later date on. Then I execute on. There you go. So the group I is no eliminating all off the additional rows under springing back the group's teacher i d with course one forced to your story. And remember that we use group by because you want to use some my friend, You get into the mouth and the aggregate functions a bit later. But for now, just appreciate whole the group by works. So we see here. Obviously, if the grades were different than there would be variations there also, So it would be distinctly different dates again. So right now we don't need grade. Actually, I didn't remember to remove that on. They can execute on. There we go. So the group buys just clumping these two together. What if we wanted to see all off the students that this teacher is teaching regardless off the course? So then students idea would become our source off contention. In addition, toe horse I d. So we can just comment off course idea on. Then we run it. And so you see teacher to coming back with only one record off the student. And of course, we can go ahead and join it on over table if we wish. Oh, all right. So I wrote off my inner joint statements and I'm just showing you this error here on. This is why I would have alluded to use off in dresses before because if two tables have the same hollow name, then there's going to be some amount of ambiguity as to which one is which. So that's why we put on our Ellis is on. We use those two distinctly identify which table it is that we're referring to. No. Having added both tables, inner joins to this query. We need to add the columns. No, no time getting this this era, seeing that certain things are involved because they're not a part off the group by cloths . Remember that any column that I put in another space. So what I did was include the student first name on the student last name and call it student name. And I included the teacher's first name attitude on last name called Teacher's Name. On the both off. These are any column that I'm referencing in. My select has to be referenced in my group. I So I have to also add s thought first new this thought last name and repeat the same for the teacher columns and after I have completed adding my additional columns to my group by So it really doesn't much of the fact that them concatenation them, wondering about the full name. It just matters that I am making reference of them in my select. And if I'm selecting them, then they must be apart off the group by. So when, when I write off this entire quarry and this entire condition, then I must ensure that my group by is a part, contains all the columns that are part off my quarry and so below. You see that the teacher idea and a student i d are coming back on. The student name is going back as well as the teacher name and then read in. Truly, I don't need the teacher idea on the student i d. So process off elimination, cleaning off my report on Dhere. My old put is just a student named on the teacher names on the back end. I'm grouping by, and I could probably even take them out of the group by Klaus. And there you go. So it's just a process off elimination, really and truly, so sometimes you're building from the ground up. You have a requirement for a report. You don't know where the dates I spoken from your feel, it'll to group by. And then when you see a sensible result, then he can refine it so that you get the desired open. All right, So as we go along, we'll see whole group by helps us with our aggregates functions. 24. SQL AVG Aggregate Function: Hey, guys, in this video, we will be in looking at our A V G or average. Either get function on this is building on top of our newly acquired SQL Group by statement use on skill and in this video because we need average on average is numerical on, which means a database structure change. So our enrollments tables originally designed toe have a letter greed. That's why we started. That's inch are, too. I'm just going to modify the design so that we have a few number grades, and then I'm going to enter some numbers in that table. So to change the design just as a refresher, we can destroy clip the table design on. The good thing is that we had no data in or grid column, so you can bear this in mind when make and design change to your column. If data is in there than the data may get corrupted based on the type of change, a terrible to make. So moving from a string toe, let's say afloat is actually a destructive kind of change to any string that was there, and you'd probably get an error at least moving from floats to a string or two. An entire environ tire might not be as disruptive as the other way our own. So be very careful when making those design changes. And if you already have grades in there, then you can probably create another table just so you can follow along with the examples for this video. So I mean, this design changed. I changed, agreed to float, and I can just click save and you make it a warning that sees that you are not a load to see changes. And that's good because these air errors that you mean ankle inter when me candy Toby exchange. So we're going to go through this era together. So when you encounter that area, you need to go to tools options, and then you're going to look for the designers so category on table and database designers , and then you're going toe auntie. This option that is, prevent saving changes that require table recreation. So, like I said, certain changes can be disruptive. So I just wanted that People K and then click save and then in the background it will actually execute update scripts because just in case I made any change. I would affect the related tables than it needs toe group of those changes across. So I just think yes, on their legal next, I'm just going to quickly directly go to edit Top 200 enter some great values. Alright. So I quickly entered some values and I can assure you that there was no bias with these values. Andi so know that we have some New America values that are not ID's, because averaging I ds wouldn't really be off any used to us. We can actually delve into our aggregate functions. So I'll just read it before here to select star from enrollments. And then we can build on that. So we have all the enrollments and we have a bunch of greats now lets us see. We wanted to see the average grade for a course I d one. All right. So whatever course I d one is, we just want to see what was the average grade for that class. So there are a bunch of things that we need to do. So we have all the records from enrollments, and next we need to filter on the particle class. So we're just going to say we're on, get the course I d equals to one and then we can execute that. And we see we're only seeing that there were three students for horse I D one and these are the greats. Once again, we want average for these grates. No, If you're a programmer, then you know, programmatically to find an average would use Luke off some sort. And if you get fancy than maybe a recursive call. But the fact is that you would need some repetition structure in place to actually go through, find all the grades some of them on, then divide them by the number off grades that there were. That's a mathematical way, and you can find that algorithm Italy. No, SQL doesn't necessarily. I mean, it does support arbitration structure in the in the in the form of a loop or cursor. But in this situation, we don't have to go that fire. We can use our aggregate function combined with our group by accomplish this task. Now, in selecting this data, we really don't need to know the teacher i d or the student i d. Because we're only interested in the course that is I d one on the average grade so we can just narrow down our selection. Remember that process of elimination on We take on Lee the course I d on the grades. And so we filtered it down to just the course idea and just a great But then what we really expect toe have is one course on just this one record that has the course I d on the great value. And in this case, the great value should be the average. So the next thing we need to do is building off off. Our last video is group by. So we say group by and we only have two problems. So we can just stay those two columns and group them by that. And then we know that if we execute nothing much changes may be just the order. So our group I didn't change much, but then this isn't helping because we're still not getting the average. So because we're grouping by and remember that group by actually keep struck off everything that it is grouping so that I, on aggregate function, can be employed to do some form off mathematics toe some value. So in this case, the math we want to do is to find average off this column on DSO our function to find the average is a V g. Andi, like any function in most programming languages, we're up. Then we're up that column in parentheses. And so what this is going to do is when it executes its going to group the course I d. So we know we've seen that the group buys only going to return. It's only trend is trying to return as few rows as possible. So I spent to try and group all of the ones into one on because we're running this either get function. The A V G is actually just going to say home any rules should I group on perform this much on. So when I execute this, we get back one rule with one value and no, we did not because I misled you. Just know. Actually, what should happen is that once you're doing and performing that aggregate function on a certain column, you don't group buy it. So the group, by still going to take into account the fact that their distinct values here and still bring them back. So if I'm going to be aggregating on the column or running this average or marks or whatever it is kind of function, then I really should not be grouping by that very column. So I will just remove that on DSO. I'm grouping by Force I d, which I know is repeating on. I'm averaging everything that needs that group. That grouping were course idea is equal to one. And so when I execute, you see that we're getting about one rule with horse idea one on average. And if you're not trusting the math, you can always politic calculator and test it yourself. And so if I remove this were Clause and I execute, then it will try to group all the courses by their respective ideas on bring but the average greed for every enrollment into that articular course. So if I want to, the average agreed across all courses than this. Where would suffice? And so you see that average grade across Horse one is 70 point through 70.3 on four force, to which 80.3 etcetera, etcetera on because there are no grades putting for seven than the average grade is No on. If we wanted to order this by then, we could just add in our order by on the knees here on what would be want order by would want to order by a V a g grade. And there we go. So it's from the smallest, very value to the highest. And if we wanted to eliminate that, no, then we could just nowhere close to see were a V a g grade is not know Andi There you're seeing that it cannot have a where clause with over agreed with are either get phone, Sean. All right, so that brings me to my second point on aggregate functions where if you want to have a were claws on aggregate value, then you can't use were, but you have a keyword that's is having on Then you can see a V g grid on then and there you go. So whenever you want to filter on a regular column, you know you have your were clubs. If you want to filter on aggregated column, meaning you're employing one of these aggregate functions and remember that you have a few . You have moxie of media of so me of count on, we'll get into all of them as we go along. But once you're filtering on one off these aggregate columns, then you have to use having on having follows the group by class on. Then you had your condition. So this could be a V. G agreed is greater than 70 and that would bring back only 70 end up he could be is equivalent to 70. He could be is like, you know, whatever it is. Off course, we have nothing that is equal to 70 but experts is 70.3 to bring about exactly what that wants. So any time you want to flutter, you have to use their happening. And of course, we can beautify the old puts here by just inner joining all of our columns and remember the rules. Once you're grouping by, you have to include every column that is not being aggregated. All right, so you can play around with that. Chandler inquiries transport with scenarios. I think you'd scenarios at the end of this section that you can use the practice on overall . Just have fun 25. SQL COUNT Aggregate Function: Hey guys. So we're continuing over exploration off aggregate functions in SQL and today we'll be looking at comment Now. Our scenario is to bring about the number off students and rolled per course so we can just start up our quarry, the usually where we So let's start from or enrollments table and we look at that data set on what we want. What we're interested in is the number off students purports, so we know we need the course to be filtered oat based on its i d. Andi. Then we need to call into the number off students that much each course. So that means Force one should have one, too. Three students on the same logic would apply for everyone, of course. So firstly, process off elimination. What do I really need for this scenario? I need the course I d Andi, I need the students idea on when I do that, we only bring back those two values on by No, we should all be experts on giants and energize someone to not went to focus on inner joining and making this beautiful. I just wanted to focus on the function that is the aggregate functions. So the next thing is that we want to use an aggregate function toe cone to the number of students. Which means that or course one I should only see one on well, three on, even though you see that here when we finish the party, I shouldn't be seen one and 31 and 61 and 12 but I should just be seeing one one rule with course I d. One on account off. Three on the same would apply for every other course I D. That is present with the number off students present, so I can just use my get focused on. And then I will get an arrow and I get this arrow because it's is telling us that we have to use our by. So that's just a warning. You can't use an aggregate function unless you're grouping by. So the next step would be to group by on. Remember that we're not grouping by whatever column is being aggregated. So we use grew by course idee only. And then when I execute this, I get back. Course I d one with the cones off three course I d to with a point off four. So that means that course for each course I'm seeing home. Any students are enrolled. So this course All right, so I just took a few minutes and modify this square so it looks a bit more beautiful on the printout. So I just included the course Course what? I included courses stable through an inner giant. Appear on denying through the columns. Of course, Could give it a nice, beautiful heading the title on give or Cones portion. And I spending also on off course because the cardinal rule is once you're grouping by, you have to include their distinct columns. I had to buy the title on the course score, so because those a repeats the group by trump them together but also included an cones for the number off from her cards on. So that's what we're able to use this aggregate function to get a count on dso don't Here. You see that this is the old put on. This looks like something you could actually turn into your administrator when they send that request for such a report. So you can go ahead and play with this. The script files included for further reference Andi, have fun 26. SQL MAX and MIN Aggregate Functions: Hey, guys, In this video, we will be looking at our marks on mean aggregate functions. For this exercise, we start off with the activity to bring back the highest and lowest greed or grades for each course. And so, as usual, we start off with our basic select star, and then we can assess and see what it is that we're interested in on that will be the course on the grade. So process off elimination. We just say horse I d. Onda. I agree. So, no, we're bringing about each course on the grades that have been awarded for these forces. And I can include a were close to say we're agreed is not know so that I can eliminate those rules that have no grade. So essentially, this exercise should see us bringing back one rule per course on In this one rule, we should have the highest grade and the lowest buried for any particle, of course. So that steak, for instance, course with I d one the highest grade awarded so far is 100 the lowest one is 35. And so we want one principles that is the course highest period on DLewis Greek as I'm sure we have grasped. But I know any time that we're going to be doing an aggregate are some mathematical function that this we need over grew by close. So we say, Course I d Andi, because we're going to be doing an aggregate function on grade, then we don't need to include grade here and the group by. So if I see mocks, then that is the aggregate function that brings back the most are the highest value wants that grouping has occurred. So if I run this, then we will see that course i d one is bringing about 100 course I 80 to 90 95 59. But of course that's not all. We've satisfied the requirement to bring about the highest. No, we need to bring about the lowest. And to bring about the lowest is simply using the function mill so mean, agreed. And so when it groups it by horse, I d is going to say, OK, what are all the grades? Give me back the highest value given, but the lowest value. Also, you me realize a trend that most of these aggregate functions only work with numbers because you know you can't find the mark's name and the mean name, but then off course, like cones, actually is not biased towards any data type, because count is literally just keeping track on presenting a number off the number off occurrences. All right, so when we have marks and mean, please remember to only use number values there. And so when we run this where you we see that we have 135 96 before. And so this is a nice, clean way to bring back a report on each course on this highest scoring or the highest score on the lowest score. So you can go ahead and play around with mean on max and, as always, see, have fun. 27. SQL SUM Aggregate Function: Hey, guys, in this video we're looking at the some aggregate function or scenario today is to bring back the total number off credits being done by each student on DSO. We'll have to get a bit creative here because in order to get this data will need toe see the students information. We'll see the course information because our horses table has the number of credits on off course. All of that is combined within the enrollment stable. So we'll need data from three different touchpoints in orderto form over aggregates, old put. And so for me, grown zero is our enrollments table. So I select star from enrollments and then I start adding on what I need. So I know I want students details. So I'm just going to energize the students table. All right, on having done that inter drama on the students. David, I know I'm going to also have to enter join The course is stable. All right, now I have a full party on my hands. So we have data coming in from all three tables and room and students on courses and no, we can start filtering on what is most important for us to have a sense of the old put. So we want to see the total number of critics that eat student is doing so off course. We don't know we need the credit cones. So see that number of greats and we'll also probably want a student named since already energized artist. Go ahead and silly in our column for the full name are. And so I just amended my court. I want to bring about the number of credits on and the students full name and off worse. You can just add pretty titles to those now. The next thing I want to do off course is the group, so I will just group by on what we want to buy is any column that we have included in our select. So I have to group by first name, have to group by last name, and I just stop in peace. Then I'm and it's putting a comma separation and I have to group by the number of grades. Now you see that this list has reduced because maybe our to Mrs taking more than 13 credit courses, and so it's from two them together and proper and true group by fashion. But the fact is that our work is not complete, because what we want to do is find a total number off credits for students. And so we have to employ our aggregate function called some. Which, actually see is that for all of those that your grouping give me the total so I can just ramp the number of credits inside. Some on if you work began screaming when I included the number of credits. Don't hear the group by then. You were right. So because we're about to do the aggregate on a number of credits, I don't need to include number off credits in the group by actually, I shouldn't included in gigabytes. And so this query will actually give me how many credits duties doing home any credit speck Troy's doing. Andi, etcetera, etcetera on All of this is based on the enrollments table, because the enrollment state is keeping track off which classes each one is doing. So the group buys. They're saying that each student I'm grouping them. Andi, I am summing up the additional data, which in this case is the number of creates on bringing back that daughter, and that is essentially hold the some aggregate function works. I hope you have fun with it and experiment with it. Bring up another scenario. The script file is already included with this video on. Based on the data that you have or how you design your database, feel free to experiment and use it. 28. SQL Views: Hey, guys, welcome back. In this video, we will start looking at database views. No of you in a database structure is basically a virtual table whose contents are defined by some form of quarry. There are many ways of explaining what the view is. Some states of virtual table some say it's just on abstract expression off a query or representation of a query. Rather, I mean, you'll get different definitions, and that's based on perspective. But ultimately, basically, any definition is trying to see the same thing. It's a representation off data from one or more tables without actually querying the table . So some off the uses of of you would be like toe focus and simplify, maybe for customized perception off a user off the data in the database and in standard in this simply means if we are, you know, including 34 tables in a quarry that needs to represent data. Instead, off having to heat those tables directly, we write that quarry one time save it is a view. And then, from that point on, anybody who needs that kind of leader can just access the view. Another important functionality off of you is It's used as a security mechanism because we don't necessarily always want users to be able to access the table or tables directly. So we just create a view on below them to access the view to see the data. It's also read only so they can't manipulate the data by manipulating the view you can only with modified. The query on the view is just a representation or manifestation off that query. So that was quite a mouthful. Andi, on my screen here, you see that we have the school databases open. Andi. Once it is opened or expanded, we see that there is a folder here called View. So we're just going toe practical, not folder on Go ahead and create a new view. So the view off words is relative to the database that is being selected, so the view is being created inside off a database. But that being said of you can comprise tables from other databases. So even though it may be local to school database, if there was another, it was a table in another database that we wanted to include in a query. We can actually accomplish all of that on publish one view that represents all those data points from all of those databases. But for now, we'll just keep it simple on with Iraq. Click Views on Click New View, which will then open up Microsoft SQL Server Management Studios Query editor. So the first thing they ask this is what tables do we want to include in over query. So ultimately, once again, the view is just a manifestation off the results off a query. So first we have toe right or build. According. So it's the first time we're seeing the square editor And I can tell you, right? No, I'm not very verse that this someone to show you the basics. I won't get to advance, but open to know we've been writing more and more advanced squares someone to show you how to build a query. A basic quarry from this view to get well, sorry from this interferes to get a view built on that quarry on. Then I'm going to use one off the course that would have written in previous times on build a view based on that. All right, so that's Clinton. You so personally, we at the table that we know we want for this quarry, so lets us probably take courses on students. I'm just going to click courses, holding on the control button and click students off course if you want. To them, Ali could just select them. Also taken, select as many or as few as you need. So once again, I'm just selecting students on courses and I'm going to click. Add on, then in the background. You see, it kind of creates this representation of the tables. Once I'm finished with that, I can click close on. You'd also notice that you can actually create views based on other views, because if we had views, you could have selected them to be included in the quarry building. And they also have functions and civilians. So for no, I'm just going to at the courses students on inflict clothes, which will then have these two. But that's not what I need. I need enrollments because these don't really have a relationship, so I'm going to add another one that would probably some medicine directly can empty space click add table, and I'm going to include enrollments because that would actually tie those two tables together. There we go. So now we're seeing those relationships and I can rearrange them for my easier viewing. No, the view that I want is going toe. Have the students someone to have the first name on the last name off any students on. I'm also going to include the course code Onda name off the course that the student is currently enrolled in. All right, so this will be made possible because as you notice as you click or on click these columns , you'll see that the well this section is being modified according to what you're selecting or de selecting on. The query is being modified and they're generating all of the inner joins and all of those things to make sure that all the data that we need is being represented properly. Now we can preview all of this work by right clicking in the empty space and we can click execute SQL on DNA results Being will show up at the bottom of our screen showing us you know what this query would yield. So it will be the same thing as us writing that acquiring a nuclear window, Andi executing it, and then we would see exactly what data would come back. So this data is basically what I want to see, Andi. Once again, we could ever in this query by ourselves, some distant it highlight this right click, copy it on, then open up a new query somewhere to go to new quarry on investment, to paste it so that we can seat in a bigger display. What exactly was being generated on then? I'm just going to former to tell little so it falls better in the screen. All right, so I just formatted it a bit and we see here where it's selecting the columns and you notice it saying, db o dot students. So it's misguided. BBL represents the name of the database, which is school on. Then students docked first name students thought courses that so and through all of this and this is basically an inner joint query that we would have looked at. It's just joining two tables so that we can get all of the relevant columns. All right, so we're selecting from courses, but we're inner joining enrollments and students so that we can get students in four and courses in for and of course, enrollments was that linker table between the two. The point of this, however, is that this square is a simple it. Well, it's a just a select query on it may not be the simplest query in the world, but it's not the most complicated. But the point is, we probably don't want to have toe rewrite this or execute all this every time we want to see that data. So I went back to our view design. We can actually just design that quarry, get it upto standard. And like, for instance, we have first name and last name would went through aliases, so I can actually just use the square builder and put on an L S o first name with that come with a capital F last name with a capital L on a course called with a speedy Since course scored on Dwell, we can leave title, but notice what happened in the court as I put in the palaces in this grid form or the stubble, a form that court actually got modified, the facilities, all of that. And so once again, I can execute quarrying, distract leak in the empty space, execute coriander, and I see the results look more palatable for human consumption. So I know that I'm satisfied with this view. I can click, save Well, I can click, Save here, or just use Control s on my keyboard on. Then I'm going to be asked to give a name to the view so I can give this one students courses. So name this one student underscore courses on. I would suggest that you said you follow the same kind off naming convention with no spaces and so on. Like Corden with the tables. That's that's my suggestion. So student underscore courses and we click OK on, then that would have saved or view. No, If we go back to our views folder Andrew alone in, then we will see that construct or this object called Student on the score courses. But you're probably wondering, OK, yeah, it's okay. No, I created a view. So what? Know the relevance off the view is that once again, I don't have toe execute this huge quarry each time I want to see this kind of So I'm just going to executed this one time and then we can look at I'm sorry. We can look at the results sets on, then, alternatively, know that I have a view that actually runs this quarry anyway. I can know, just select from the view so I can say select star from And then my view is called Student on the Score courses. Remember, that is a virtual table. So the fact that it exists no mean that means that I can query it on by saying so. Let's start from that view. What it really does is execute this huge quarry in the background. But it's all encapsulated in the expression student on the score courses. So if I execute that, then you see I'm getting about the same results set. So that's a nice way toe kind of simplify the representation off your quarries. You have huge quarries, and you probably need to generate reports on instead off, you know, right in the huge query, and then trying to see if the quarter filing and trying to find it each time you need to run it. You can actually just write it, create a view on, then execute select statements on that view. Going forward on the fun thing is that I can do all off the filtering and all of those fancy things. I can join the data interview toe another view, even to another table if I need toe. But the point is, this is kind off a nice way to simplify the representation off the data. Now let's get a bit more complicated. What if my school administrators asked me to furnish a report showing all of the students with subjects that they're in enrolled in on the teacher so I would have to build on this query on the all come off? That would be, well, more inner giants. And since I just wrote, the quarry on the square is something that we've done prior to this. So it's not. It shouldn't be too far into you, but as you can see, it's it's a bit more to this query than the last one. So the fact is, we sat down. We wrote the quarry on. We finally got to a point where we're satisfied with the results offset query. No, I don't want out to execute this quarry every time, every week or every semester. I don't want toe have toe run. I don't want to run the risk off saving this file and potentially losing this file. So what I'm going to do is make this query into have you have two options that can take the square, having written it and then going over to the views on then right clicking, create any creating a new view, go into the view designer and then pasting it here on just seeing all of that. I'm kind of exhausted already. So I'm going to use a script and create the view instead. So I have everything here but then above the select. I can actually run a create command right here in the scripts that will just create the view on defined the view as this quarry that I have just spent so much time working on. So what I will say is something I create view. And of course, you have to use use statements if you're not. If you haven't already selected the database, you'd have to use the use statement some. Are you going to show you what's probably might run into on hold to solve it? And it's not really a problem. It's just a much off awareness. If I use the use statement, then I need to say, Go on, then run my create statement. All right, so with all the goal, I'll end up with a syntax error that's seemingly your 11. So off course he's go and then you see creates view. You give it a name. So this would be students subjects like tourists off. What you want to give your views names that when I look at it, I can kind off get an idea of what beater to expect. Should I query this view? All right, so I say create view on give it the name and then see as and then what follows is the quarry that will define the views. Remember, once again of use, really just are a manifestation off a query. So I wrote this quarry and instead of saving the file so I can execute it and run the risk of losing it are deleting it inadvertently. What I will do is just create it as a view and then having done that, it will be attached to my database. So the next time I need to see this kind of date, I just worry the view. So when I execute this script as it is, all right. So we're getting an error here. And it seemed that order by clause is invited in views on certain other objects on the reason for that is that Well, for some reason, the view doesn't guarantee that the data is going to come back in that order. So we ordered by title me just refresh ordered by course, title on that is an illegal operation inside of creating the view. So I'm just Linda. Remember that for no, some just actually just been to cook this, not want to delete it. Just cut it on. I'm going to go ahead and create the views. I know my view is successfully created. I don't see a difference here. I can just click views, refresh the folder on. No, I see that my view, which is student subject lecturers, is a bill. So, using this well, I'm going to open a new query file on. Then what I can no see is select star from Yes, you guessed it. Students on the score subject on the score, lecturers. All right, so that's the view that I just created. And once I do with this and executes, it will bring it will execute this huge quarry that I just wrote in the background and bring up all off the results. No notice. Remember that we initially had an order by and it was ordering by the course title, and I took it off because it's illegal. In a view. However, I do want my data sets, which the view is returning to be ordered by. So here's what I can do. I can say select star from my view on then order by and then I can say See that title serving? But I cut it So the space, that box I need to do a little refractory I can't order by seed outside. So there is no object in the square called C. But there is an object where there is no object, also called title. All right, so that's another important point based on our original quarry, where we were actually looking at the columns in the table on giving them aliases. All right, remember, we had seed up Title See is an alias for courses for the courses stable, right, so I would see that title. But then we give it the alias course title. So for the lifetime off this query. We could have, you know, doing other operations on the column names I could have ordered by C Doc title because it knows about the column name. Now the view, which is a virtual representation of that doesn't know about the original column needs. It doesn't care some. You just get this back out here. It doesn't care about the original column. Means it does knows that when I select star the columns, I know our teacher first name, teacher, last name on whatever you see coming up here in the column titles, a k a. The aliases. So that means if I needed to order by one of these columns that would have gotten any less . I need to order by the actual name off the column that I see appearing once I covered this view. So in other words, I need to order by and I have to use my square breeze because there's a space in scores title and then I execute on. Then you see that it is ordering by. So the red line listed up here. You don't need to worry about that, but this is what the user sees. This is what they know. Course title is the name of the column so we can order by the name off the column we see coming back on once again, we can always add on or were close. I can say we're on defy only wanted CS 100 for instance. I can see a were course cord once again, Square bracket course scored because that's name off the column that the View knows Course court is equal toe on the string CS 100 and then I execute that on the field during work. So everything that were used toe up until this point with writing quarries against database tables, it is very possible. Sorry. Everything that were used toe with selecting data from tables is possible with a view. However, when we have multiple tables and more complex squares, we don't have toe continuous there, spend time and write them. But once we get them perfectly, we can say them as a view on going forward. Just write a simple select against that view to see the data. Now, with all that being said and done, all of that was just about creating the view so of you can be managed. Otherwise we can also say Alter View. So maybe we needed to change the view itself. We needed to change the query so we can say alter view on We have that view name. So it looks really the same way as a creative you. But we just change creates toe altar on. Then what would do just modify that quarry and then execute on whatever change meek will be reflected in the next time we execute against that view art in a similar manner. We can actually right click the view on Go to Design, which will bring up that query editor. So remember that students on this for subject on the score lectures. That's the one we created by a script, but it can be opened up and modified inside off the View editor. So maybe we didn't want one of these titles. That maybe course scored was no longer necessary. So if I actually go to the course is stable and Manticore scored, then it would remove it. And then if I save, then it would have altered the view Either way. Right? So there those are two ways you can alter your view. Another thing you can do to manage of you is probably rename it so Students on the score lectures on the score subjects. Sorry, I read it in the wrong order, but the fact is, it's a long mean. What if we just wanted it to be called student enrolment? So we can Archer just right click the view here in the editor and just click rename and that makes it creditable. We can rename it here Or if I wanted to do it via script or cold would look something like this so we would say exact, which is a key word that we're going to learn when we started. Can start procedures. Andi, it's short for execute on. Then we have SP on the score. Rename SP is kind of like a tell for stored procedure s stored p procedure. So we have a stored procedure that comes with SQL called Rename. So it's S p on a screen iam and then we see which object it is that we want to rename, which is a view by that by the name students subjects lecturers on. We want the new name to be student enrollments on. Then when we execute this on, then go back to our views folder and refresh. Then we see the student on the sport. Enrollments is no the name off or V, which means if we execute the previous quarry Select star from student on the score subject on this word, let jurors. Then it would give us an error that there is no object by that name because the new name for this view is student enrollments. So if I made that change and execute again, then we're right back where we started. No, I hope that you know the use of use makes sense theory. It's very flexible. You can always change the query that generates the view on. Ultimately, you don't always want users with the database uses or even application users toe have toe interact directly with their tables all the time, so you probably want to build your application. Our own views on each of you is Brondby, a carefully crafted quarry for that particular interferes, or even just for, you know, general database access and use. You probably don't want users to have to sit down and figure out where to find what field across 23 tables, so you as the database developer, You can actually just do the script, create the view on each time they want that data. You just didn't. There's a view. That's a view. You go for that data. Hold that. You injured this video sticker on for more. 29. Scalar Valued SQL Functions : Hey, guys in this video will start looking at functions in SQL on design or own skill are valued function. In a nutshell. A function gives us ah, construct through which we can execute potentially complex code using a simple line off court examples off functions that we built up before would be like our aggregate functions . Marks mean some cones, etcetera. But there are dozens off functions in SQL on in this video, we're going to be looking at some of them. Let's start off with one that we're familiar with. So in a nuclear window, I went to do my usual use school statement on. Then in the next line, I'm going to do a select statements now. What if I wanted to select the highest greed that has been awarded to any student in the enrollment stable? Recall that in our school DB When we look, we have an enrollment Steve, which has a few columns, one off, which is the greed. So we want the highest greed that is currently inside a. So what we would do is actually use on a four aggregate functions in the form off marks, passing the value greed on, then say from enrollments. This would then when executed, he was the highest greed that is found in the entire team. Notice wouldn't use a group by. We're not aggregating over rose our records in order to find anything. We're just looking in on finding them the marks in the same way we can look in and find the minimal. These are examples off functions, so marks mean they're all functions. We have the function name on, then inside of the breaches, we have the value that it requires in order to fill its operation. Another example off a function would be get. Did so if I wanted the exact time stump off my running accord than I can say select, get date open and close breeze. And when I execute that, I will see that I get the date on time off when I executed According to the millisecond, The difference between get date on the Max and Mina and other aggregate functions is that the while one, they return different types of values. So this is a date time value. Where is Max and me and return a numeric value. But also get be It doesn't require me to pass in a parameter. If you recall when I typed in marks and open breeze, I needed to provide some value that it would use to do. It's computations. No, in the context, off the dates on the time we can use another function in the form of year, which takes one parameter. So I if I wanted the year having gotten the dates, I just wanted the year that the year value for the date. Then I can use another function called year and passing that value as the permitted. So I typed in year open breeze, and then you see that the managements to you is letting me know that it requires some day time to be passed in. Andi returns an integer value. So when I enclose the get beat, which is returning at the time value inside off the year parameter list, then the year is going to look at the dates that is passed in on just return, an integer value that represents the year value in that daytime. So and execute, I only get back 2019. Now moving on from these examples, we can always see the list or a good list off what SQL has to offer us in terms of functions by going into the database on going into the program ability folder than expanding or functions holder and then once more, expanding our system functions folder inside system functions. You see all the different categories off functions that are built into SQL for us on I'm going to take a look at the string functions. So inside of string functions you notice that you have one for difference. You have one for lane on if you hover over each one. It kind of tells you what this function is used four itinerary to get enough information. If you continue to expand, you see the parameter that are required in order to use dysfunction. You see that it requires at least have arch our expression integer expression and it returns. A virus are environ tire. All right, so you can always get some information on the types of functions that you need to help manipulate the presentation off your data. You know, they may come a time when you need your own function to return something maybe more table specific, unless generically the system functions on, then that is where we have to start defining our own scale are valued function. Let us see that at any given time we wanted to call a function that would actually return the highest agreed in the class. We did it earlier we wrote a quarry on What we want to do is turn that query statement into a function so that when we call that function, it executes the quarry unknown to us, but returns the appropriate value. So what we need to do is under functions we go to scale, are valued functions we right click on. Then we go ahead on click scaler. Valued functions are in some interfaces. You may see new, but anyone who CRE contract like functions and then go to new and then you select skill are valued function. They will both lead to the same. Please, no. Having done that, you're given a query window on. It looks complicated, more far more complicated and what we've been looking at. But if you take a good look, you notice that it's really not that it's really it's really simple. Anything that is green or has to Dutch is at the start of the line. That's a comment on there really just guides to tell you what to do, where so we can look at it and we see that it's really just generating the template for the create statement that is used to create a function. So we just need to go through on be scared to replace anything inside off the angle brackets with our own data. So for author name, it's always good practice toe come into court to see who wrote it when it was written on what exactly this function is about to do now. When we get to the actual syntax, you see that we have the syntax create the keyword function on then angle brackets on in the angle brackets there, suggesting that we should specify a function name so I can replace everything inside this function. Name on. I'm going to see get highest. Agreed. So this is the name of our function. So once again, we have functions that have nears like year on. Get dates will were defining our own that is going to see, get highest, agreed. Next we want to start adding parameters. No, the guideline for parameter is that we use an at sign give it a name on then a data type. Once again, we're simply replacing anything that has uncle brackets around it. Someone to go ahead and replace this open and close angle bracket block of text with the name of a parameter on. Well, if I'm getting the highest grade from a table, I really don't need a parameter because I'm getting the highest greed in the table. So just like we saw here with get bit get, they didn't require a parameter in order to carry tow, carry out its duty. So given our context, we don't need a parameter to carry out or duty. So since we don't need a perimeter, we actually have to remove that block off takes to see create the phone shown with that name with no parameters required. And then we move on to tell the return type. So the return type is the type off data it's expected to return, so once again, get the it returns at the time. However, year returns on integer if we're getting the highest greed than our return type would be, I believe Destin Mile, based on the fact that the data type Ford agreed, Is there is this man and I can always just go back on. Validate that by expanding the tables and it is actually floats. Not this months. I'll use float so that value that's our function gets high screens a boat to return is afloat data type. And then the court continues where it says as then begin, and then, in between the begin on the end, keywords will actually do some work. So the first thing we want to do is the clear variable for the return time. Now variables like a temporary storage, we create a variable, we give it value, and then we can use that variable just to track this value, are manipulated this value into whatever we want on, actually store it for the period that we need it. So in this case, we need to define our return value that will be prone to change later on. But at the end of the day, it will be returned on. That is the value that we will get back from, or function so or the clear statement requires that we give or verbal a name on then a data type. So once again, the naming convention for any variable or parameter is that it starts with an at sign on. We give it to me, and so I'm going to give this one that sign. Agreed, and then the return variable has too much the return type off the phone shown. So that means that this variable needs to be afloat because then it's going to store flowed . Value on it is going to be returned, so it has to be a float value too much back what the function, data type or return date attack is. Then we go on toe assign value toe this variable based on the old, put off some query. So the next statement goes on to say, Add the T's SQL statements to compute the return value here. So I'm just going to remove once again everything inside the angle bracket on. Then I'm going to start off by writing the SQL statement, and then I'll show you how you actually go about assigning the value now, since I only want the greed from enrollment, then I only need to select greed as a column on. Then it's coming from enrollments, but then I need to get a bit more creative because I did see the highest grade. So like we just saw, we actually would see mocks greed from enrollments. So selecting marks greed from enrollments will actually looking then Roman civil once again and return the highest. Agreed. So since this column or this function will give us the highest greed we need to be ableto assign whatever value comes back as this return value from mocks creed here in tow, our at greed variable on the way to do that is to say, select on. Then we see the variable is equal toe the column that is going to store the value that we need to return. So once again, we say select variable is equal toe column or function return type. Whatever it is that is bringing about the value from our select statement, we we do this assignment inside off the sedate statement, and then the six demon goes on to say from elements and if we're filtering, would put on the wear and the group and all these things. But the fact is, whatever statement we're writing for our scale, our function should only return one value that can be assigned toe our return variable that was declared inside off our function. Once you've done that, bakery son ones who've put that SQL statement together and done this this assignment, then the last step is to return that variable. So the variables that sign greed So we're returning at sign greed. So once again, we create a function. Give it a name. We define the parameters. If they're known, we meet this area blank notice. This is a comment, so it has no bearing. Whether it's there or not, we define the return type off the function. And then within the beginning and the end, we do whatever computations are. Statements are necessary to get over value to be returned, so we need to declare I return variable, give it a data type, executes some SQL query that is a going to go and calculate or in whatever way, fetch the value that were desires off returning. Then we're going to assign it toe are variable, so it's a select variable is equal to whatever column or construct is going to be returning that value on. Then we go ahead and return that returns variable. Having done all of that, we know executes. Remember that all of this was really just one big create function statement. So nothing has been done until we execute on once we do that and we see that all commands were unsuccessfully, Then we can go back to our functions folder Gerald own into our skill are valued functions folder, and then we will see that our function is no accessible to us now. If we wanted to make an adjustment, this function then would have to change the out the creates toe altar on, then make whatever changes and then execute, and it would update the function. According toa Whatever changes we've made over, I'm satisfied with the function as is on. Then I need to test it out. So I want to see if my function will return 100 which, based on the Guerrero last time was the highest greed in our database tables. I go back to my quarter window on Dai said the context off school on Then what I'm going to do is select on then specify my function highest get highest greed on because the function I need my braces. I notice I only open and close because we didn't define it to require any parameters. If I execute this, I'll get an error because it will say it's not a recognized built in Foshan name on. For whatever reason, SQL requires or recommends that we put on that d b o dot when we're calling our own functions. So DB or docked on then or function name will solve that. And when I execute, we see that it is returning. One value on that is 100. If I wanted to define another one to get the lowest agreed, then I will do the same thing out. Right Click on scaler Value functions go ahead into the edit view on Make all the changes required to get the lowest agreed. So that is my challenge to you. Rewrite that quarry to get the lowest grade on Get the number off enrollments on just, you know, go crazy. Get some practice in with the functions. Now I'm thinking of a scenario where I probably would want to get something else a bit more in unique on Let's say I wanted to get the course name by I D. So if you go into the courses table and take a look at the data we see that we have about 11 courses and they all have titles, the number of credits and the course school. But I want a function that when I pass in a value I get but just the title off course. You know that if I wanted to see that, I would have to see where on then say I D is equal to on then maybe say five on then execute this query on dime Sorry, Put its course idea, not I d on Then when I execute this query, I get spark dotnet So all I want is the title when the function is is provided the i. D value. So I'm going to go ahead and create a new scale are valued function, and it's going to bring back the same templates that we just went through on. Then the changes we make are going to be pretty similar. So the function is going to be called get course by I d. Or maybe I should be more explicit. Get course name or title by I d get course stifled by I eat. So you always want to be as descriptive as possible in your function name so that anybody who comes along can look at the function and say, Oh, yes, I know what that does. So we're getting the course title by I D. And if we're getting it by I D. That means that we need to provide a way for somebody to give us an I d. So that we can know exactly which title we need to return. And so, in this scenario, we need a parameter, most definitely someone to modify this. The angle brackets inside on. I'm going to start off with one that's is defined the program name. So I'm going to say Param name would be course I d. So I see at Sign on, give it a name and that's the name off the parameter on. Then I need to specify the data time, and if I'm getting it by i d. And ideas are usually integer than that means the data type off. The parameter needs to be integer because I need to specify that you need to pass in an integer that will act as my look up I d. When I'm executing my query. No, no, that we've gone through the parameters the next thing is a return type. So if I'm returning the title off the course, that's a word. Then my return type can be in this time it has to be environ charge. So I'm going to say environ char on. I don't know the length off, of course, understand? To say 50. Maybe we can actually just see environ char without any specific size. And that would kind of be more elastic. Taught over length string is returned. So after defining the function, the name, the parameters and the return type, we go on to the begin on. Then, in this section, we declare our return. Variable someone toe, remove this angle bracket, block off court and call this title on. I'm going to give it the data type environ char Next. I need to specify the select statement, so I'm actually listening to write the quarry first on. Then we do the assignment. So I'm going to select title from courses and then off course, selecting title from courses. And if I run this that worry over in another choir window. If I don't say select title from the courses table, then that's his Autodata. So it looks like we get 11 titles, so that's not adequate, because we really only need one based on the idea that's provided on. The only way to filter would be to say where the course I D is equal to some value. In this case. If I say one, we get back HTML. But then we won't always know this value once a low the user to provide this value, which is why we give them the parameter. So that means if I'm about to make sure that we're finding the course, that is corresponding to the idea that's provided and we need to see we're or column course ideas equal to the value that the Param Eter has. So in my function, I want to say, select title from courses and then go on and see where the course I d. On the course I d notice is not the one with that sign. Where course I D that belong Star table courses is equal to that sign on. Then we call in the parameters, So when we do course I d from the table is equal toe course exercising course idea. We're seeing where the course I D in the table is equal toe whatever value this parameter has so know that we can feel satisfied that this square would bring back what we want. We can no do the assignment where we're going to assign the title from the table toe our return variable that we declared earlier. So I'm just going to say select at sign on my return variable on Not we don't have to call them title. We don't have to give them the exact names. As a matter of fact, I tend to prefer not using the same names because to me, is less confusing. So I could have actually just called this name. So I'm going to call this Neil. So my return variable is name someone toe assign the title value coming from my courses table in the database to my name variable someone to say select at sign name is equal toe title. And then we see the red lines go away because, no, it feels satisfied that we're assigning the value toe or return variable. And then, at the end of it all, we want to return whatever value came back inside off this assignment. No, that we've Don't this query and we feel satisfied. Or this yet this square to create the function we can execute to actually create the function. And then we can validate that he was created by one second going into our database going to program, ability, functions killer valued functions. And if you're already here, you can always just refresh and then you'll see your new objects appear. Get course titled by I d. If you expand it, you will show you department of listing on let you know what is expected off you. So if I go back to my experiment, acquire a window where? Right, So this is where we are And then I say Select on then remember, we need the DB or on. Then I'm selecting. Let me just make sure I get the knee writes. I can actually just drag and drop from the object explorer over to the choir window. And it will just generate that court for me so I can avoid spelling errors. And then I try and execute this statement. It won't work because you will tell me that he could not be born. In other words, it seemed that I need It's a function I need to put on my braces on. And if I try to run it again, he will not work. Because then it's going to see I don't have enough arguments. Are parameters supplied for the phone, son Toe work. So I need to supply a parameter on. Remember that in the definition, we said that we need a parameter of type int someone to pass in a value on. I'm going to see one on. Then we execute. All right. And there it worked. I'm not sure what that era was about. Could not find sir procedure s, but they're worked on. Okay, so we see that we have a little arrow because we made a miscalculation. With the size off, the inverter will only said invite or not, invite our 50. So it's literally just returning one value because it defaulted to one. So we need to modify our function So we go back to or Foshan. Let's say maybe we closed it and we went on with our ways. We can always just right click the function in the object explorer and click modify and it will automatically launch that alter function cord that I was at that I mentioned earlier. So we're no back in the same quarry, how we need to alter the function. So what I'm going to do is I'm going to see that it returns. Environ tire 50 on that are variable. To be returned is also invite our 50. Because even if I said the return type to invert our 50 it would still be truncating if if I didn't set the return value the turn variable. Sorry to also be environ shar 50. So you want to make sure that the return variable type much is their return type as much as possible. All right, on so we can execute this query because it was really just acquired to alter the function. We made our changes and we're satisfied we execute on then that successful. So when we go back to our query window on disclose some of these windows here we go back to our choir window and we execute this function once again. Then I see the full name or title off the course going back called HTML. If I change this value, we had 11 courses. So course number 11 on then I execute again. It goes in it looks up on the value that was passed in, and we get back artificial intelligence. If I looked up on 110 on, then I don't expect anything to come back. All I get is a no, because there is no course in the database with the i. D 110 on That's it for scaler. Valued functions pretty simple concept. We can just always create these functions that help us, you know, write queries more quickly or execute choirs on the fly in more complex squares without having toe complicate that actual quarter. We can just create a function right, the the query that we I wish to do in the background and then call the function any time we need that particular value so the next topic would be on table valued functions on that gets a bit more exciting, but I advise you, or I'm encouraging you to actually do some practice on this one, create some functions within the context off this or any of the databases and may have working with on just explore all the possibilities 30. Inline Table Valued SQL Functions: Hey, guys, welcome back in this video will be looking at table valued functions and more specifically in line table valued functions. Now I have here a choir window that already has a template for creating a function on I got this fire by going toe are functions folder and then going to table valid functions, right clicking and then saying I wanted a new in line table value function that brought up or function create, um, quarry Onda. As you can see, it is pretty much a seem template. If you're used to creating a skill, are valued function there few differences, so we'll just go ahead and create a function as we explore. The difference is now the first thing that we want to start doing is replacing the blocks off text in between angle brackets with the appropriate center. So the first thing we want to do is remove this angle bracket for the function name on, put in the name off our function, so I'm going to see greed range. So this function on are actually just in tow. Put your description so that we know what we're working with. So our function today is to return grades between two values. So the name off the function is greater range. And if we want to return between two values, then we need to parameters defined. So they're already letting us know that we probably need to parameters. In this example, they have one suggestion off internal sedition of child. But off course it's or function so we can do what we want. Someone to define the first parameter as the first greed on the first. Greed would have a data type off floats and then because I have another permit, I have a comma and then I go on defined the second perimeter, which would be second greed on. It also has a data type off float. So what this function will be doing is accepting agreed off some value on then another greed off another value on then the expectations that we're going to return a table so noticed that the return type is already defined for us in the scale. Our function. We have to tell you what returns up we wanted in a table valued function. It is going to be a table for obvious reasons. All right, so it's our turn type of a team of meanings returning our results sick. So our results it that we intend to return is a results that for all grades between the first grade that was passed in on the 2nd 1 that was passed in. Next they asked us to define the select quarry that will return the data sets that is being requested. So I'm going to write a query that is going to return all grades from or enrollment stable , that fall in between or in the range off. What our values provided here is in first grade and daughter of value is provided in second grade. Someone to say, select agreed from enrollments and then there two ways to write the filter. So obviously I need to filter all the grids on. I need a were close to do that on there. Two ways to write this work, someone to do the one that you probably are thinking off where I went to see where agreed is greater than or equal to, and then we can see first agreed Andi. Greed is less than and or equal toe second agreed. So right there we would have facilitated, facilitated arrange. We're selecting all the grades from enrollments, but and we're filtering all the grids where the values that are being returning, um, must be greater than or equal to water Volley came in as first grade on. There must also be less than or equal toe whatever came in a second grade. No, that's the first Twitter at a Korean. There is nothing wrong with that. It's perfect. But then SQL gives us a keyword called between that also our houses to accomplish this kindof logic with less court so I can actually write right under this line. We're agreed and then see between so we see where grade between body one on Val ITU's of value one would be our first grade, and then we have the keyword Andi and then value to would be or second grade. So these two statements were pretty much the same way. It's up to you. Whichever one you find easier and more intuitive. There's no problem. You pick whichever one is better for you and you use it. So for this video, I'm going to press a long press ahead with the between statements. So I'm seeing select grade from an grow moments where agreed between the first value that was provided as a parameter on the second volatiles provided as a perimeter. And that's literally that's all there is. It's really just writing a query. Andi. Some people think off table valued functions as para motorized views because they talked about views where view is just executing inquiry on returning what we want to return. So in this situation, we actually get toe passing parameters, which can be done, and you don't know what the user may want to see at the time. But we're facilitating their desire by giving them the parameter on. Then we just make sure that we craft the Corina we that it returns data relative to the parameters that they want. All right, so I'm going to just execute this on. It's going to go ahead and create the function, which I can validate by going to table violet functions, refreshing on expanding that fuller if needs be. So I'm going to go ahead and create a new career folder, Onda, use my use school statements, and then I'm going to select and remember that we have to see a DVD well dot on then the function name someone disagreed. Range and then I need to pass in two parameters because this function was defined to take two parameters. So one great and cigarettes. So let's say, for instance, we wanted to find all feeling students on the pass mark for our school is 50. So that means anybody between one and 49 these feelings. So then, if I wanted to see the Greater INGE, I can see one comma 49 on their When I execute that it does a snake statement and then, it seemed, cannot find great range. All right, so I rolled that wrong and I'm sorry about that. What we should do since it's a table valued functions, I was attempted to call it like a scale are valued function. I need a table valued functions I actually need to see select star from on, then the function. And then once we do that, we get back or desired results. So once again, if it's a table value function, we need to call this function as if we were dealing with a table. But then we get toe passing parameters that, implicitly based on definition off or function, will handle whatever complex square in the background. So if I wanted to see all of the students who had are all of the grades that fell within the passing category So all passing students in the past markets 50. That means I want anybody with 50 on the 100 right? So if I execute this, then I'm going to see that I do have quite a few students have certain students with breeds that are passing on. Then I have five that are feeling now all those them only returning greed. And there is no limitation on the columns that can be returned. So if I wanted to upgrade this query a bit, I can actually go back, right? Click it, goto, modify, and then that would generate the altar function command for me. And then I can modify this query to bring back other columns. Do inner joins Andi, bring back as much data on the students and their grades as I need to. So you can go ahead and write this function on, then maybe modified or create another function that has a big, more details based on what you think would be necessary for somebody to view. Based on the data set that we have 31. Multi Statement Table Valued Functions: Hey guys, welcome back in this video will be looking at creating a multi statement valued function in msde SQL. Now in the inland table statement function we run. One query returned results it as a table. However, in the multi statement table, valued function will actually have the leverage off defining or over table construct on the flexibility to execute more than one quarries to fill the stable construct before we finally return it. Now to create one off these functions, we just need to go back to our table valued functions folder, right click on. Then we'll see new multi statement table valued function that will bring up this template which will be using to guide our development. So in the template, we start up with our create function statement and then we give the function and name someone to see all persons on all persons, meaning we're going to be writing some statements that will return all the persons currently in our school db on persons would be students and teachers know in order to differentiate between the two, I would have to put some form of flag and so given that I I don't need any parliament is to know all of the person. So I'm actually just went to not use parameters. Andi, if you want to see a perimeters work, you can look in the scale, are valued functions, video or the table valued functions video. And then you get a clear sense off why we need parameters and why we may not need parameters over pressing forward in finding all persons inside the school. Db we don't need any parameter, so we'll go on and then we see that the returns type is off type table. But then remember, we're replacing everything inside off the angle brackets so unsettled on the brackets here , we would actually say that sign on, give a name for the table that is going to be returned. So our table or variable off type table that will be returned will be persons. No, the data that I want back on the persons will be their first name. Their last name, the date they joined the school as well as a flag to see if they are stuff or teacher. So, in defining my table, I can actually define all the problems, just like I would in creating a table it to go into the SQL a structure except in this situation. My table is a viral over the once again, the rules apply nonetheless. So if I go by the angle brackets, I went to define column one and then give it a data type on. Then, since I'll have multiple columns, I use a comma until I get to the last column. So the first column I'll have here is first name on the detect for first name. That I'll be entertaining will be environ Shar 50 and then I have my comma on. Then the next one would be last name on this one will also be in Hvar Char 50. Now I did see that I wanted to find the DEETs made joined as well as if they are students are stuff, so the next one would be be it joined on the joined I would probably give the time and then the last one would be ah, personal type. All right, so the fact is, once again I'm defining my own problems in my own table called that sign persons. So this is just one big variable off type table that will look like a table operate like a table on be returned from this statement as a table. So I'm just putting in personal type as a final one on. I'll leave this as environ char 50 because we're going to use this one to print the word stuff or students based on the person's status. Now the next instruction in this template is to fill the table variable with the rose for your results. It So what I'm going to do is insert into this table twice. I went to insert into the stable all of the students, and I'm going to hard code the value students so that personal type will always be student for all of the students. And then I'm going to do the same thing for the teacher. Stable but hard, putting the word teacher as the personal type flag so I can write, insert into. We all know the insert into we read insert into him at the table were inserting into. In this case, our table is in the form off of variable of time. Team was our table or 10 T Blore variable table is at signed persons on. Then what we want to do is a select statements, so I can actually just chain an insert into statement onto a select statement so that whatever this select statement returns, just go straight into the team. So I don't have to define all the columns and then save values and in turn, find the values that much. Buck, if I have the results that that I can just hard cold Andi well, dynamically feel the table based on what is coming back. All right, So in this case, I'm just going to see select on then from the students table. And I'm just going to explain the students they will just to refresh myself. I have first names. I can actually just drag and drop from the object Explorer First name, comma, last name on note. If you're doing it this way where you're not specifying the columns, you have to make sure that whatever you're selecting in this statement, much is toe the column structure off the table. You're inserting into using this method. So we define it as first name. Last name did joined and personal type. So I'm inserting into persons on. Then I'm selecting the first name so much to the first name, the last name too much of the last name on then joined would be contextual. It'll seem as enrollment did it on. Then after that, I'm going toe hardcore the value. Ah, student. All right, so once again, I'm selecting from something they haven't defended as yet. But that's select statements. It's coming from a table, right? So I'm selecting this valley from the table that vary from the table, that body from the table. But I'm also allowed to select a hard coded value if I wanted to. On this heart, bullet value will correspond with personal time. And then, after defining all off the values or columns that I want to select off course, you know we have to finish up by seeing from on then students on. Then all the red lines go away because though it's it's okay. No, I know what this guy starting about. Some selecting first name. Last name enrollment did all from students, and I'm also just hard core tea. So I'm kind of cheating by this hard, quoting the words student into this data set coming from the students table Onda inserting that the person So let me just run this section just to show you what it would look like. So the select first name, last name in a moment dates. I'm just going to execute that. And then you see here that it's bringing back all of the students in the database with their enrollment dates. But everybody has the word students hired. Cool it. No matter watch. All right. So when he goes into the persons table, all of the students in the database will have that as their personal type value? No, simply enough. I'm just going to repeat these this this entire statement for the teacher, Steve, because I did say I wanted to know all the persons in the school and all the persons in the school would be the teach our students. So instead of trying to write an accomplice query and join and trying to figure out which is which I can just see if I know I'm selecting from the students table, then that's going to be a student so and want to select again from the teacher steel on. I'm changing the flag this time to teacher Andi. I'm selecting the same data from the teacher stable on I can just expand The teacher stabilised. Make sure I'm getting my column names partly on you. See here I'm referencing enrollment dates, which is wrong. The deep its debt joined according to the teacher stable. So I need to rectify that. Andi, I'm just going to drag it over since I already have it. Andi, fix like Kama on. There we go. So first name, last name and they joined Coming from the teacher stable on were hard boarding personal type. So that means any time this function is run, it will define this table called persons with these four columns on, then proceed to execute these queries which are filling the person's table with data on then at the individuality just returns. So when it returns, it would return what their values went into persons. No, to complete this, this is the creation Acquire once again, we need to execute, so it will actually create the table. And I'm getting an error here where it seemed that I have some mismatches with number off columns supplied somewhere I see the error. So I didn't put the comma after the joint. So then we tried that again on executing their wiggle Hermann's completed successfully on then once against validate, I can just go to table valid functions and refresh on When I expand I will see great range listed there. So we're going to try on and run a query toe, See what comes back from this function on rumble with table valued functions you have to select from the tea from the function light. It was a table. So I'm just going to select star from Ondas Adeeb, you greater inch notice. We didn't put in any piranha Mater's Remember we didn't put any parameters when we're creating a function so there is no need to pass in a value integrator INGE. And then when I execute that I see Oh, I'm sorry. I still need to make representation of the fact that it's a function right. So even though there nobody's going in as if need open and close braces to show that some function on when I execute, it will take a while. I'm sorry I was executing the wrong function. I should be looking at all persons not great range A politics. So we're looking at all persons so I'm selecting start from all persons and we still need to make representation for the breezes, even though we are not passing any parameters and win execute. Then we see here all off the person's coming back in the database. So at this point, we don't know which table anything is coming from the way we know which table or can deduce which table what came from his father. We put that personal type flag on or data sit. So without that, we could have joined that deter seamlessly, and it would all be kind of hidden from naked as the whole. We got the students and teachers to come back as one big data set. So that's how multi line or motive statements, table body functions work. You just create them. Define your table, define inquiry, fill the table with the results. It's on return on. That is it. I hope you enjoy this one 32. Stored Procedures: Hey, guys. In this video, we will start looking at stored procedures in Microsoft SQL Server. Those third procedure is pretty much prepared SQL code that you can save and re use at will . Stored procedures are other versatile. You can use them to put too much. Carry out any crowd operation on the data. Once you call it, you can pass in parameters, or you can just call it the defense. It depends on how you define it's really on in this video. We're going to build a few just to see exactly what they're capable off know. To get started with store procedures, you would have already logged into your server management studio, and you can just expand your server, going to the database and kowtow program ability on right there, you see store procedures you can right click that and click stored procedure. Now, before we even get into this, it is noteworthy that there are quite a few systems stored procedures, and if you expand that, you see a nice long distrusted procedures that are available to you by defaults just by having a secure server. However, none of these are really designed to interact with the database in question, which is our school database that we've been building on DSO. We would probably want to define some stir procedures that can interact with the database. Once you're creating a stored procedure, you will be given a template on def. You've done functions. Then you do realize that this template looks very much like functions. It's almost like you've done one you've done off. The only major difference here is that in self seeing create function, it releases create procedure. All right, but then everything inside the angle bracket needs to be replaced, just like we would have done in the other template files. So for this procedure, I'm going to call it, select all students, someone to replace all this text from the from one angle bracket to another on I'm just going to give you the name. Select all students. All right, so select all students and then four Sillitoe students really don't need any parameters. No, in this section with other parameters, Andi ah, parameter can be an outside value that we're passing into the stored procedure on that value could help inform the results. So we could have select all students with no parameters versus Select a student where we're expecting apartment off, maybe an idee or something that that that would help us to run our quarry for that specific reason In this situation, select all students. We don't need any parameter. So for this example, I'm going to erase that entire section one that is suggesting that we have a parameter Andi so have create procedure. No parameters as and then begin. So after begin covers the body, we don't have toe change this, at least not for this example. But then what we need to do is create statements for or procedure in this section. So what I am about to do is select star from students. So we're starting off with a nice, simple example. So ever student step and we're selecting star from students on. That's pretty much it. So that's it to creating this first store procedure on once, I'm sure I have no more red lines. I can always like execute because once again, this is a script and it's running and create procedure command. So I need to actually execute the script at the end. So I click execute, and then you will tell you commends commands completed successfully. If I refresh my store procedures list, then I will see meister procedure. Select all students now to use the store procedure. And I'll just open up a new query window on. Make sure that I'm using the school database that we're working with to execute this stored procedure. I simply need to type the word exact or which is short for execute. Really? But its exact on Ben. I call the name off the stored procedure. So it is select all students. Andi, I may need toe qualify this with the db o objects on my DVR object or more support bbo It kind off opens you up to more options. If you might get error without the errors without the db o say, can we just abduct? Um so the statement To make sure you get no errors, I click execute. It does exactly that. It selects all the students. Now the wonderful thing about this is that it is reusable. I can exact select star select our students at any point, especially from building an application. Most use cases for start procedures would be that you use them. So how's the business logic, Meaning you put all the intelligence inside the sore procedures. And you really just call the stored procedure in your application when you need that kind of operation to be carried out. So that's one off the use cases for stored procedures. Um, for this video, I'm going to go through other examples off store procedures. Andi. So this one was one without parameters. My next example is going to have parameters. I want to do one where I'm going to say select student by Eddie someone to go back to my store Procedures folder, right click, click stored procedure on. Then the name of this store procedure would be select student by I D aan den. This procedure would have one parameter, so I'm actually just going to systematically replace all the errors in the angle brackets. So the first era asks for a parameter name, someone to call it at sign, because in SQL we define our parameters using the at sign on. Then the data type which is being asked for in the next section would be int because we're accepting the I D that we want to use to return the student or find the students in question. So I only need one parameter someone to remove that comma on. I'm going to remove that second line because I don't need it on. So I'm creating procedure a select student by i d using this I d parameter of type into as on. Then we begin the court. No, to get the student by i d. It's a simple, select statement. Really. I'm sure if we were to do this in a regular sq estimable could do it, so I'm just going to repeat it. But in this situation, I'm going to use the I D as a variable because I don't know what idea is going to get past so clearly we have toe the selecting from the student of a relative to the value that is being passed in through the perimeter, someone to see select star. I'm replacing the angle brackets section from andare table students. We're so we're adding our filter on. Then I can see at I d. And then I would see aware the i d. Because this still double check, We have the column called I D. So we want to find a student where the i d value is equal to whatever valuer passing into Mr Procedure. So were the I. D column is equal to or via being passed in, which is going to be stored inside off the Parmenter that we define, which is at i d. So this square read Select star from students where I d is equal to whatever value the parameter has at that point in time on. Then we can once again executes because this is that create procedure script. So to create the procedure we have to execute on, Then I'll go back to my script file here where I'm winter from another test so I can see exact than db o on then the name of the sir procedure, which is select student by I on then to pass in the parameter that is required. We need to reference apartment or by its name, which, based on the definition off, our procedure is at sign. I decided at sign I d on. Then I give it a value so outside an idea is equal to three. So I want our that. I expect that when I execute this statement and I run my sir procedure passing in the value three into the I D. It will execute this select statements where it's selecting from students table where the I D value in the column is equal to three because that's what I'm passing in as the perimeter value. And so when I execute this or I just execute the entire script so the top value, the top results. That is my select our students. But then, if you look below it, you see that it is bringing about the students with the I. D. Three. If I said 10 and executed, then nothing would come back because there's no students with the i d. Value off 10. So the point is, as many values as I may need to pass in tow the stored procedure to carry out the operation . I can define them as parameters. So all any is a comma. And then I defined as many permit is, and the respective data types, as I need on off, was the last one has no comma on. Then we can carry out some operation using SQL syntax that makes use off the permit is that we're passing it, so we've done two examples where we're selecting. Let's do one where maybe we're inserting. So I I'm getting a new student on. This student is coming into the school. The name is Francis Read Onda. We can make up some other votes. So the point is, I want to create a stored procedure that allows me to insert students and I don't want after right the insert statement every time I want to insert a new student. So I'm going to create a store procedure that does the insert, and it just asks for the values to be inserted. So once again, directly the store procedures folder, click store procedure on Go ahead and modify this template. So I'm going to call this stored procedure insert student on. Then I'm going to list of all the parameters and I'm going to make a parameter. Her column that will be inserted is if we look back at our students there and I'll just close all off the unneeded tops here. So if we look back, we know that ideas are implementing so I don't need to passing on. I devalue when inserting. However, I do need a last name, my first name, a date of birth and enrollment. It So that doesn't mean I need to create a procedure that is prepared to take four values or four parameters for four columns. So I'm going to go ahead on defined these four parameters. So I did say four, but I defined three. So I'm doing last name, first name and date of birth, and I'm going to show you something so the next one would have been enrollment date. But I am going to design the stored procedure such that the time or the data in time when you enrolled the student or would have inserted the student is going to be the enrollment. Someone to use a function to fill this column. I won't expect you toe pass in that value as a parameter. All right, so let's finish the procedure that we look at using the procedure. So I'm accepting these three colors, but I need to insert three. So I'm inserting. So this time I'm going to remove all of that example the select example. And instead I'm going to insert in two my students table. And then we know that we list out our columns, which are last name, first name, date of birth and enrollment it on. I just breathe the line and then we have the keyword values. So we know that we don't abundant our basic crowd. SQL statements Because there will come back to haunt us. We have to reuse them at any point. However, using the stored procedure. We don't always have to write this statement every time there's a student so we can write it one time, save it is a procedure and then leverage the procedure. So the values will be the last name value communion from the parameter. So that would be acts last name, sort of. The last name is passed in when this, when this procedure is called, we passed in the first name. So remember that whatever values were were defining here have to line up with the columns being defined. So last name the last name first name the first name on date off birth to date of birth on . Then I did see that at a special way to deal with enrollment it. So instead off passing in something as the enrollment dates from the perimeter, I am going to just use my function, get it which will return the date and time off execution off the procedure. All right, so once I do that on, close it off. My insert statement is complete on I'm inserting into students Last name, first name, date of birth, enrollment it. Andi, I can just create this procedure so I click execute on then. This is a new procedure that I have access Tyler's Refresh my folders so we can see all of them. So we have insert students on. We can always expand and see all of the parameters that are required on it Says it returns an interview. And this integer is usually like that. One are zero to see if it was successful or not. So let's test the execution off this. So I'm going to go back to my start procedure query page on. Then I went to once against the exact on DB. All on. Then this stored procedure is insert student Andi. Once again, you can always drag and drop it someone to drag and drop it. Just to show you that you can do that. We're not trying on then to call this one. We have a few Proceed up few parameters. Sorry that we needed. So we need the last name. Let me see if I can drag and drop that. I can't so actually have to type that one. So last name on I did say the last name would be read. Remember that we defined last name in our store procedure to be an environ char. It's always a good idea to use the same data types as what the columns have. All right, so you wouldn't want to be passing in an intern entrance, insert into in tow, invite our or vice versa. So it's always a good idea to minty in that data type when defining the parameters, and then we'll come and separate all of the parameters that need to be there. So that's read on then first name, which will have the value off Francis Aan den, the last one. I'll just break the line so that we can keep everything within view. The last one would be the date off birth, and it's not exactly K sensitive, either. On the beat off birth could be maybe 90 95-0 16 bash 18. All right, so I'm just going to highlight this section on execute, and when we do that we see commands completed successfully. No, we don't get anything else but that the only way I can verify if this student was actually inserted is to go to my select all students to procedure and execute. On there, we see student number 13. Read Francis for 1995. And then if you take a good look at the enrollment that value, you see that it is an exact time stamp for the time that I executed this worry. So that's what you can mix and much and use your functions, enquiries and in the insert statements and use them to your advantage. So this would be the enrollment dates. The student would have been enrolled exactly on this date if I wanted to get back details on only that student that I could just execute. So that student by I d on, I could just say 13 on, then execute on there. You would get back just the values for that students, um on. Then let's try and put in an illegal value and see what happens. So if I puts kina ones so remember, well, that wouldn't be a good example, because then you would probably still try to convert it to an inverter. So let me put in a bad value for the date off. So I put in a two for the date off purse so we know that date of birth Mr Look like a year , a year or a time at the time kind of value. So I'm putting in just the number two. And then I went to execute. Let's see what happens. And it says Commands completed successfully, and I think it's actually accepted it and try to make sense off just the tool. So you see, here I keep on inserting read because I keep on executing the entire file and it keeps on executing the stored procedure. So it's reusable. I didn't have to retyped the insert statement every time I need it, so I can always just change all these violence and for as many students as I want to insert that exchange values, run the execute command with the stored procedure name, and I get that students in certain so once again start procedures can basically house any kind off logic. Any kind off SQL commands inside off them. You can use them to create records you can use them to read records like we did. We did that create and what did I read? So I'm challenging you to do on update on a delete. Just create store procedures, one that says Update Student, Where you passing the I D value, as well as whatever values that you expect when students is to be updated and in Iran statement for that on our challenge. You also to be one for delete where you put in Leeds students passing the idea off and then you delete that's particulate students. So that's essentially holster procedures work now. Very common practice after inserting an euro is to return the i d off the road that was just inserted. Andi, even if that's not necessarily what you're returning, sometimes you want to give our return Verticals. Store procedures are used for all sorts of things that can be used for a month. So maybe a passing two of ideas. You do some math or you wanted to run some form off complex operation on something using a query, and then you just return a value. So the point is you can return values and to do that what you need to do is specify on old put parameters. I'm actually going to just modify this procedure that we've already don't called insert students on to modify our procedure. Um, let's see, we came about the day after some closing this. Andi, I have the stored procedure here. I can always right click it and go to modify, which will launch the same template, maybe with one toe extra lines. But the point is that instead of having a create procedure, which of on altar procedure? All right, so remember that the altar key word is used instead of the create when the object exists, already aware, modifying its so in this situation because they're modifying it to have the altar on. Then what I'm going to do is at a new perimeter, and I'm going to call it students I d. On this will be an int. But then the important part that will differentiate it from all the other parameters is that it will have the key word out put. So then I know I'm defining this parameter as on old put parameter, meaning it. You have to provide values for these right on. Then it does its operation, but then were expected to give something to the output parameter for return. So, student ideas, my old put parameter I'm inserting a new students. And the expectation is that I'm going to return the I d off this newly inserted student because, I mean, when we have a big school, we don't know how many students we have. We just want on all put value. So I'm going to then see select on then se student i d on then inside student idea, um went assign what would cause school on the score identity. So that is just a little function that sq gives us toe alot us to return the pretty much the I d or the last scope values Remember that identity is what helps it account. So when we say school pie identity were just returned in the last cones based on this operation all right, so then, having made those changes on altering, I have to know execute because once again, this is a script. So to execute this other command so that it will save the changes on Then we can try to execute this query again. So I'm going to change the name are The names are all. So I'm using a new student called Abby Thompson on She was born the fifth of the second in the year 2000. So that's her date of birth. Someone to go ahead on, execute on that will then insert. So then it seemed that, uh, parameter was not supply, which is the student i d, which is one that I just put in as the old put on DSO We can go back to our definition and see what I left up on. I do believe I left out something in the execute statement. So there are a few things that we need to modify in our executions. So, firstly, that all put variable needs to exist in the context off our calling scripts. So this is our new script and just a just the eliminate confusions. I'm going toe open up a brand new script file where we're going to use school, and then we start over. Now that we have you school, the next thing we want to do is declare some variable. That kind off looks like our 12 the old put verbal. So my output variable is student. I D So I'm going to just declare another variable in this new script. Window course students idea with a data type off. Also next I'm going to execute meister procedure and I'm just going to write exe executive . And you cannot extremely right. All the word execute. So you know which only a few more comfortable with writing. And then I'm just going to drug over the stored procedure. No problem. That's an easy on. Then here's a clincher. You can actually just give values instead off writing odes or this is variable name equals that value. So if I remember in my permit early score clad, last name, first name, date of birth so I can actually just put in the values. So my first values last name. So I put in Thompson Then my next one is RB on then which is first name on date of birth is my third ones are putting a date of birth. So if I remember, her date of birth was 2000 0502 on, then, to end it all, I can just append students, i e. And then after writing about the variable name, I need to actually put the keyword or put once again. So when I execute this entire query file because I can just execute this line because the variable student ID's declared here. So I need the entire file where go sequentially, declares student idea and then executes the command on. Then I'll just put at the end of this select student, Heidi. So what we will do is execute the stored procedure passing in Thompson, Abby and date of birth doing what it needs to do on, then all putting the scope identity or the newly created I devalue to student I d on. Then I'm just going toe store it in this section here with all put keyword on. Then afterwards, I'm with selected, so it should be displayed in the results that someone to execute on. There we get that the newly created student I. D. Is 17. And just to show you how reusable everything is, I'm going to put in another student. This time. The student's name is dark white. Tunney was born 1985. Andi Instead of just selecting the student idea because I don't really want to see the student idea, I want to see the entire student record. So what I'm going to do is execute my store procedure. Bbo dot Select student by i d on. Then I will pass in the student i d value as the parameter So I'm just showing your whole everything works together So I'm executing insert student on I'm passing in the values toe you know, execute against on Then I'm returning or all putting the value student i d or the newly created I d toe a variable cause student I d on Then in this next line, I'm going toe use that student i d Value toe Select that students using the other store procedure that I have called select student by i d someone to take student I d on. I'm going to replace that 13. So what is going to do is when that variable stores the return value from the insert students. That procedure, I'm going to execute this next stored procedure, and I'm going to pass the value that is in that variable into the parameter for that phone shown or for that store procedure. Sorry. So when I click execute, then I will actually see the students details coming back there we go. So is student number 18 door quite on all off the details that were just inserted. So this is so you can actually put things together toe autumn it complex or potentially complex operations Once again, when you're building your actual application on top of this database, you can put a lot off intelligence into your stored procedures. Andi, it's just up to you to call them in a manner that is, you know, beef eating off a good user experience. At the end of this exercise, you would have learned hole to start creating store procedures in your database. You once again can go ahead and create a stir procedures for any kind of operation, any crowd operation, any operation in between. On you can reuse them, using the exact keyword, calling the stored procedure on passing, whatever parameters you well values for whatever part mentors you mean have defined. If you didn't find defined parameters Sorry, Then you can just call the stored procedure like we did here with select all students 33. Table Triggers: Hey, guys in this video will be discussing triggers in SQL Server 2017. No, a trigger. Indeed, Toby's development is pretty much a procedure that automatically runs when an event occurs , and the event is usually like an insert on update or delete action. On a table off course, you can define which one off them you want toe actually carry out an action on, and that's what we'll be doing in this video. Oh, to create a trigger, we would go into our database, go into the tables listing and then find the table that we would like the trigger on. So for this video, I'm going to create a trigger on the students table. So let's build a scenario. Each time a student is added, lawn automatically add on enrollment for that students on def, we check out the enrollments. What? Let's trickle the course. Remember that we have a few courses, and let's say every students who gets enrolled in the school will be automatically enrolled into the HTML course heart. So then, in the enrollment stable, what we have is the teacher i d. With the course I d. So we know teacher to a teacher's course. One on that. We can find the details off teacher to easily by just looking at who teacher to is. And that's John McDonalds. So John McDonald teaches HTML Onda. Well, we he has more than one students or more than one. Students are doing HTML with John McDonald. So every time a student gets added by default, we expect that there will be some enrollment into the HTML class with John McDonald. Andi, even if their greed is no. But that enrollment must take place. No, they're two options. You could try to create some complex insert statement where every time you insert a student , then you try to put it in there. Or what we can do is create a trigger on the students table, such that on insert off a student. We will just create the enrollment. So that's where Trigger comes into play. So what I'm going to do is go to my students table, expand it on. Then you see the folders that states triggers. And if you expanded their no triggers attached so I can distract, we can click new trigger, which brings up a template file for the creation off a trigger? No, If you've been doing functions and sir procedures, you would be kind of familiar with this temp it And you know that you firstly replaced everything in the angle bracket with whatever is suggested. So at first, this is the schema name. What is the ski? My name schema would be by default. TV force. I just replace key ski money with d viel on. Then the trigger name would be create and roll mint And then we want to see on so on. And then we see a ski money once again. So we would say db o on then the table name, which is courses or less are students. So we want to create the trigger, create enrollment on the table students on. Then we want to see we wanted to happen after and then they're suggesting insert off the delete. So based on the scenario, working with he would be on insert No, to continue this trigger, we insert the statements for the trigger and remember that what we're doing is creating a new enrollment based on the I d off or newly created student on dwell other predetermined values so I can go ahead and create started Dubai insert into statements and that will be inserted into another say db o dot enrollments on. Then we specify the columns based on the operation that you're defining the trigger for you have access to three specialized table so we have insert in this scenario. So we have access to a table called Inserted. If we did at the elite operation and we would have access to deleted, I'm just in the same way. If we did, an opiate operation would have access to the table called Created. So because they're doing an inserted what happens that inserted table, No tracks, all off the values that would have just been literally inserted into our DDO students. Since the value in need from the inserted role is the i d. Then I need a way to get the i d to capture it so that I can go ahead and create the enrollment. Know what I'm going to do is firstly be clear some variable that I'm calling it student I d on time period of an inch on Then I can select the state the value into student i d from the inserted table. So I can always do something like select star from inserted So that would actually give me just the rule All the rules for what was just inserted, including the I devalue. Since I don't really need star What I need is just student i d Then I can assign the value from the inserted table for the i d for the newly inserted value into my scale are variable called student I d someone to say select that science students i d is equal to. And then the i D column coming from the inserted table. So just to put that into context, anything I need from remember that this trigger is being called as soon as an insert occurs . So once something is inserted and I just modified object, you're so we can see other columns would have inserted last name, first name, date of birth and enrollment it on. Then the idea gets created upon insertion. So, in this trigger, once I called my inserted table, Then I have access to the I d. On. All the values that were just inserted into the equation aren't into the students stable. So then what I'm doing here is I just really need the idea because that creates on enrollment or anything that students they were really has for me to use foreign enrollment is a student i d. Anyway saw that I need is a student i d. So I'm declaring a variable cause student i d And then I'm selecting the i d value from the inserted table on dime pleasing that inside off my newly defined scale are terrible. I know that I have that variable or that value. Then I can do my insert into and I'll just say db o dot enrollments on Then the columns to be inserted in which our last name first Sorry enrollment. So that would be course idea which we know is going to be too greed, which we know is going to be no on Remember that it doesn't really matter what order we put them in. So I know all the values. The only variable value here is really the student i d. Because I don't know what the idea is when a student is inserted, but I don't know which. Of course they're going to do. And I don't know who they're going to do it with, right so insert into bbo that enrollments the course by I d greed, which is going to be no student at you, which you want to be. Whatever i d is associated with the students who has just inserted when the teacher i d. And then I see values and then the values once again would be to our sorry one for html, The great is no, because what? They're just enrolled so they can't have agreed on then at science student idea. So whatever, whatever value is taken from the inserted table for that newly inserted students, that's the students I devalue on. Then the teacher idea would be to because you know that it's done but don't want to be teaching that course. And then once you've done all of that, we would have completed all of the steps required to create this trigger. No, I can click execute, which will actually create the trigger. So if I go back to my students stable on refresh the trigger solder than I will see my trigger listed on there s create enrollment. Now, once again, this will be fired upon an event. So what I'm going to do is do something like, I'm going to create a new query window to use school. Andi, I actually have a steward procedure somewhere here that allows me to insert a new students . I'm just going toe call Master procedure using the eggs IQ key word. And you can always check on my video on, sir procedures if you're not entirely sure what I'm about to do. So the stored procedure insert students will insert a new students with the last name Willis the first name brand on the deeds off birth of 2000 and 1-0 dash 12. All right, so one on, then once I execute this, it is seen that I have in car Cintas because I need my commerce cerebral that on not execute again. Oh, I left off of arable, so I need an old put verbal called at student ideas. So once again, if any of this looks far into you, you can always check on my video on stir procedures where I actually go through all of this , but for no, I'm just going to be clear. Some very because students idee into So you really didn't need to use a store procedure? Could have used a regular insert statement to insert into the table. That's no problem. But our that Mr Proceed. So I'm just doing the shark Opie on. Then I click execute again and you know, we see that the command is successfully completed. Now there some shine is that once something is inserted into our students stable, then automatically based on our trigger that we just defined, something should get inserted into our enrollment stable someone to go over to my room and stable on. I'm going to do another execute on there. We see that we had 17 rules before. No, we have 18. We have a new enrollment. The new students with student I D 19 Onda, this course idea one on the teacher I d to. And if we just take a look at students with the I d 19 on that, I just go over. Let me just select top 1000 from students. Then we see that brand Willis was indeed entered. He does have the student i d 90 andare trigger acted upon the insertion action because I said after insert So after a new student is inserted, then I want you toe get the i d for that newly inserted student on, then do an enrollment. No, this is one use case. A more practical use case would be made before auditing. So, you know, you want to keep a log off what students are, what data may have been removed from the database additive additive is updated and especially by whom. So good use case off triggers is that once, um, active has bean, you know, obviated our records in a table have been updated, removed or added. Then we had a trigger to that table to see. I want to insert into some form of log table that this happened at this time. All right, so that's a more practical use case. Very man over that. Triggers are kind of expensive, meaning that they can be resource intensive. They can slow down the operation off the database, especially in a setting where large amounts off beat are being pastor. A large number of operations are being conducted someone to do another trigger, and this trigger no, is going to actually see that if a student is deleted that I want to remove all the enrollments that that student would have had in the system. So I'm once again I'm doing this, for example, sick. It's not necessarily best use cases, but just so that we can understand what triggers are capable of. So I'm just interact click again and see new trigger on. Then I'll go ahead on Put in BB or for my ski my name. And I'll do that to stop the MPs that on very quickly for both spaces that require ski ski My name The trigger name will be delete students record is a on it will be based on the students table once again on then it will be after a delete sermon. We can return, insert, update or delete. So this time it's a delete. So after a delete off, a student and I want to carry out this operation and operation and tend to carry out will need me to get the student the i d off the student that was just deleted so we can do something similar. I can be clear. Some scale are variable on. I'm going to call it still I d this time just so you can see that it doesn't necessarily have to be what you know, the same name. So studio I d. And then I'm going to select the value forced to I d to be equal to the I d value coming from on this time. The table we're looking to is deleted because this is a delete action. So I'm getting the data from some temporary table called deleted. Know that I have the value I'm going to say delete from enrollments we're on. Then I'm going to say where the student i d is equal to at science to I d. So I delete a student any subject that the student would have been enrolled in. The expectation is that it's going to be deleted now. There's one kind of adjustment I will need to me to my current design, and that is our owned the foreign key constraints. So I will have to go back to my students stable, go to design, go to relationships, and then I'm going to have to modify the relationship that the students and the enrollments they have on this modification is going to be well, the name off it is car cascading. So I'm going toe, drop down the insert and delete in certain opiates mystification and then I would just defined rules for deletes and updated so well, really, for delete. So you see here that it's is no action. And what this means is that if I try to remove a student, then I can't remove the students as long as they have enrollments right because and that value is needed for for reference in the enrollment stable. So what this is really going to do is see that wins when a student is deleted or the primary key in a relationship is being deleted. But I want to cascade, meaning all the related values for this primary key and all the other tables should get deleted also, or do I want to just set no meaning the enrollment will stable than the value. If it was one, it would know become no Or do I want to sit some form of default value? So in this case, I'm going to just say cascade because I did say, I want to remove the students records so that I'm just doing this. They can see that, you know, one thing affects the other, So by seeing Cascade on, then kicking clothes on, then saving what I'm seeing is that if I delete a student and if it has a relationship with enrollments, ever expect that all related records should also get deleted with no worries? All right, so having done that, well, I didn't see it. So I need to execute so that we actually create this trigger. Andi, I'm just going to refresh my triggers for the just make sure it's there. So have the lead student records on. Then I'm going to keep this one simple, so I'm just going to go to the design. Onda. I'm sorry. Not designed. I'm went toe and it top to wondered My bad on. I'm going to cross reference this with whatever enrollments I know. I have someone dispose some of these windows. All right, so in my enrollments, I see here that student three is in three courses, which means that if I remove student, three are in four horses rather. Um, the expectation is that if I remove state in three than all four off these and roll mentor card should also be deleted. No. Once again, it doesn't have to be a stored procedure can be through any method that I'm going to remove a student. So I'm just going to remove Jodi Macintosh using the edit Stop 200 interface. I just select the entire rule by clicking the left most pale on. Then I'm just went to president to leave putting on my keyboard on then as a C s. I want to permanently remove Jodi Macintosh notice. I got no errors on. Then if I go back to my enrollments and I execute a sex there again, you see that there are no more records for Jodi Macintosh. So that's a pull off a trigger, you know, on on a certain event, I want to carry out certain action on DSO. My trigger is really in the background, just executing. I'm just doing the light work, and the trigger can be in the back of doing the heavy lifting after the fact. So I'm going to encourage you. Just try out a few triggers, tried to modify the design accordingly to support the trigger. Once again, if you're doing anything to modify beat especial with relationships than you need to go back to you. The design off that foreign key relationship on change whole. It behaves on the lead on orbit 34. Database Scripting, Backup and Restore: Hey, guys, in this video, I'll be demonstrating how you can go about creating a script for your database or Anita means buckle. So the scenario is that you don't only need to do with designing your database on your dev machine on DNO. You need to actually promote your data bees toe the production or to another environment, or whether it is that, or you want to just share your database with a friend or with a family member for a reason . Or you just need to move it between machines or maybe just need a copy. The point is that Excuse server, my SQL Server 2017 actually gives you some tools that allows you toe kind of create either a back up off the database or just script on the database so that you can actually run the script on another instance or computer and re create the database. So we're going to go through that right. No. Now the first step off course just have created a database, got into a level of satisfaction with its design on. Then we're going to know script it, so if you're using a skill server, management studio can right click your database on, then go to tasks on. Then in tasks you see an option that's his generate script. So let's start with that one. As you can see, quite a few tasks he can do. You can touch it, you can take it off line. You can do a number of things. But in this video, we're just looking at how we can export it to a script. So I'm just going toe to generate scripts on. Then this brings up a little wizard so we can click next on. Then it asks us, Do we want to ext script the entire database and all files, Sorry and all objects Or do we want to be very selective in the types off objects? So, you know, we have functions or stored procedures. We have user tables. I want everything so I can just leave it at the first option to just, you know, blindly tick everything anyway, on. Then we go to next on. Then they asked us, Do you want to cve the script toe a file which would then create an SQL file at a place off our preference? Do you want oh, copied to the clipboard, which means that after it does what it's supposed to do, we have to find somewhere to paste it right so it will just automatically be copied to the clipboard. Or do we want to see if the new choir window? Because we we just want to see the script instead of having it for future reference. So I actually going to choose a save to nuclear window option so that afterward finish it will open up and we can review it on. Then we couldn't next. And then we see the source and so on, so we can just next once again on. Then the operation begins. Once that operation is completed, we see that the script fire kind of loads behind the wizard window. Then we can click finish, and then we're going to see our beautiful script that looks, you know, nothing like what we've written. It's very strict, sq, a standard with a bunch off parameters that we wouldn't even have looked at. So you see auto shrink you see on sea so it does a bunch off pre operations prepping the database for generic sittings, I'm guessing, or based on the conflagrations off your machine on. Then we create the phone shuns on. We create all of the objects as we go along. So here the create table statement is there so would know how to script all of these words off towards the scripting is going to be a bit more complicated and what we've done, because then we have some relationships like on the enrollment stable. It test three relationships. They're going to see a few constraint lines off cord on. Do you know it's It's quite complex is quite nice, however, what's missing from this? And if you look, you see that this one, this section is creating some properties and the diagram so that that means if I execute this script on another machine, it will create a database called School with all of these objects in there. So that's the database school say, if I wanted a different name, I could see on the score V one. Alright, but off course, if I change created two bays underscore view one. They never wear the databases referenced, which is school. I would have to me try modified script accordingly, but the point is, whatever would appear after this script is generated will be illegible to be role on another machine, and it will give us but the database as it stands, this users and empty database because, well, it creates all the tables and all the functions and sir procedures. But it does not bring over the heater itself. So this is Method number one. This is when you want to stop blank, fresh NATO based. The design is intact. The name is intact, but there is no data institution. Where you want a script file that gives you the data on the data, obviously is relative to what's in the database, as at the time the script is run, which means if something gets added thereafter, then the script file will not reflect it. But we can right click go to task school to the same generate scripts up son on there we go to Next on. We can leave that up again, but in this screen, what we need to do, I'm going to do it a nuclear window just the same. But what we need to do differently is goto advance now in advance. There is a category where it say's Do you want that? What types off data do you want to script on, then the options available Tours are scheme only, which is the default. We can drop the door and say we want only the data, Which to me doesn't mean what since unless you're creating the data bees or you have the data Bizerte and just want to detain? No. But then you also have schemer and data on By selecting schema and data, it would give us what we already have, which is the it'll be structure. It's generally son script Onda Also the script that inserts data into the respective tables . Some which is true. Scheme and deter Think. Okay, I leave it to go to a nuclear winter so we can analyze it. Also click next twice on, then wait for the operation to be completed. Click, finish. And now if we look through way can scroll a bit and we'll see that were no in the insert section. So any data that was in the data bees as at the time that task was done, we are no seeing the insert scripts that will insert that very data into the respective tables All off the lecturers or teachers would have been in the system there being inserted ? No, all of the students. Andi, all of those wonderful things. So that's how he could go about exporting a database. Are exporting a script creating a script, brother that exports both the structure onda the data that was in the data base at that time? No. Another technique that is used and this is probably the more commonly used technique by database administrators who want to keep back ups off their database is to actually back up the database. Know the difference between a script file and a backup is that the backup is usually taken as, ah, file that can be restored at any point on did. You will just have a database. So let me demonstrate that one. So if I wrecked like the school database I go to tasks on, then instead of going down to generate scripts, I go toe back up on dyke like that. That brings up this menu. Where are this dialog box where I choose the database? I can confirm that I want to back up the school database. I want the backup tight to be full. Andi. The component is database and then I choose a path for the backup file. I noticed that a file extension is that B a k. So this is, Ah, part that will be very hard defense. I'm just going to remove that quickly and as a slick ad on day, I'll use the ellipsis button to Boro's to, ah, folder somewhere, and I just put it on the C drive so we can find the five easily on. I'm just going to call this one school dot Be a case of that B A k once again is that backup file extension? So we click OK on, then we have different options there. Media options where you want toe add onto an existing backup. Um, sometimes that's not a good option because of the existing one is corrupt state. I want to, you know, be used. The carpet one. So, um, the old want to do that on? Then you have this one that's his back upset where the backup will expire after Ekstrom off days so we can change us. Leave all of those at the default settings. For this example, you have fulling of differentials. The differential is more like that on incremental us full sees. Give me everything anyway. And then we click. OK, And then it seemed that the accesses the night and that's because I'm going directly to my C drive on this server. So let me let me do that again. So I'm going to remove on when the click add on day instead of going to the C drive directly, I'm going to go toe. All right. So I don't have a suitable for some archer just going to go into my C drive on going to create a folder. So just create a folder, and I'm going to call it to be a k on. Then let's try that again. So in this screen on which, actually after, cancel on and do it again so that I can actually see be a key as an option on Then, once again, this is school dot b a k on a quick Okay. And then okay. And then access is not denied this time. That backup is completely completed successfully on I can click. Ok, so if I going to be a K folder, then I will see that I have ah backup file for my database. So I can actually just, you know, share this with somebody and they can restore. And they get that database copy, as at that point in time. Or I can you know, what if maybe my machine was going down or something? I needed a backup of the data base or today's database Copy. Got corrupted. Andi, I have the BK far from yesterday off course, there would be some loss off data, but the point is that I would actually have it abyss copy. As at that point in time when that backup was taken. So let's pretend that we're restoring or school database onto our server. Something happened. We have the backup file, but we need to restore it to our server. Someone to go to databases. Right. Click on. Then you see that your options are attach. Meaning you got maybe an oil call. An M D f f as in fire file on. Then that would allow you to just, you know, attached the database because I already have the database. So as you see them here, they're really what you call him. I am a zinman B as in dire on de F as in fire mdf files, right? That's what the database files are stored us. But once they're in bucko form, there not be a K. So if I had an MDF file, I could restore that MDF and it would appear in the listing. You don't be added to the server. I don't have on India file. I have Ah, Buick. If us. I need to restore the database to the server second, to select restore data beasts. And then this dialog box asks me what I would like to do. So first, I'm going to select device, which means that I don't have a database itself, but I need to go on the device to find the backup file. So I think the ellipsis I click add on. Then I navigate to where I know my backup is, which is my BK folder in my C drive. So I click OK on, then I think OK again. And then you see, there's some kind of operation detects that this was a school database on its a full backup on everything. You know, it shows you the last transaction tugs on the start date, and the finish did as at the time, So once again. If you're doing a daily backup as a database administrator, this is the more recommended method. Andi, if anything goes wrong along the way, obviously you some data. But this is a nice, clean way to restore your databases. So one thing, though, is that I already have a school database. I don't want another school database Are not the database with the names school that's illegal. So I'm going to call this one school underscore Wiki. Just assure you that this was a backup file that was being restored. So I can do that. Where? Renamed the database file on, then click. OK, on, then it does a restoration. And if you look over to the object, explore, You know, See, that's our three databases. One with the name school underscore B A K. So I click. OK on, then, if I, you know, just go in there. I see all off the objects that were in the original database, All the views, all the stored procedures, everything that would have been there originally if Iraq click on teachers and set up 100 all of the data that was there as at the time off the backup is once again available to me . So the reality is that as a database developer, you need to know how to build a clean data busy. Need to know to manipulate data, Sure, but then, as a database administrator goes a bit deeper than just performing the basic crowd operations where you actually are responsible for the health off the beat a beast on, furthermore, for the integrity off the data on a daily basis. So these are Tipnis that you know as a database administrator. Toby, you need to be cognizant off, and you need to be comfortable with doing on a daily basis to make sure that everything flows for your organization.