Microsoft SQL Server For Absolute Beginners

1. Introduction: Hello and welcome to this course, Microsoft SQL Server boot camp. I am your instructor for war Williams and I have been teaching database and programming courses for the better part of the last decade. In this course, we will be focusing on installing, configuring, and developing a database using Microsoft SQL server. This course is primarily designed for individuals who are brand new to the topic of database development. And anyone who may want to refresh their knowledge is also welcome to join. By the end of this course, you will have enough knowledge to create a database and supports and application run basic queries to interact with the data and ensure that you follow industry standard best practices. With all that said and done. Welcome once again, and I will see you in the next lesson. 2. Types Of Storage: Data can come in various forms and sizes. And before we start configuring a database to store anything, it is best if we take a step back and get to understand the various forms that it may come in and the recommended storage mechanisms that can be employed. So the first type of data that we want to look at these unstructured data. They'll usually unstructured data refers to data that is stored in documents and various files, videos, images, etc. Whether they are the physical or digital copies. And then for physical storage, we would use file storage mechanisms like filing cabinets for the hard copies and for the soft copies would tend to put those on our hard drives. Now, it is a bit more difficult to kind of keep tabs on what is in each document. And that is why we get the word on structured. Because while it may be in a structure, there is no real standard governing how each file looks or what is in each file. So that is why we would call it unstructured data. The next type of data we have is semi-structured data. Now this is also file-based and this might get confusing because we just discussed file-based storage. But when we talk about files here, we're actually talking about like a document that will be stored on the computer or in some form of database management system. So here we are talking about files that are created using JavaScript Object Notation or JSON for short, or Extensible Markup Language or XML for short. There are other slightly less popular formats that you may see this kind of data being stored as. Now, the best engines to deal with or semi-structured data would be no sequel databases like MongoDB or Azure, Cosmos DB. And then we can also store the actual file on our computer. Then we have an example of a JSON file. We have what we call an entity. An entity would be the thing that the data is being stored on. And then the entity has data points. And you can see here that it's really just a bunch of brackets or curly braces and text in quotation marks. And this is what we call a key value pair. The key would be to the left, and then we have a colon, and then we have the value to the right. For this employees details, we have the employee's name, Schwab email, and then their job profile. And then you'll see that we have a curly brace starting and ending, and then a comma separating it from another structure that is very similar to the preceding one. Now semi-structured data is very useful for situations where we're not entirely sure hole or data needs to be stored. So we can start off small and as we learn more, we can actually extend this document to have more data points or remove data points that we don't need as you go along. So that is why it's semi-structured. While there is a standard, it is still kind of flexible. The next example is one of an XML file which is very similar in nature, where we have an entity and then we have different data points about the entity. You can see that the structure is slightly different, but it affords us the same kind of flexibility that we saw with the JSON file. Next up we have structured relational data, where our data is formatted in tables, rows and columns. Now, in this kind of data layout, it is very strict. Tables have x number of columns which represent each data point. Then each row represents those data points relative to an entity. So a table is a collection of data about an entity. And usually for these kinds of data, we use relational database management systems like Microsoft SQL Server, azure, SQL, MySQL, postgres SQL, Oracle DB, and there are others. Now in this course, once again, we will be using Microsoft SQL Server. And Microsoft SQL Server is a verb pole, full relational database management system given to us by Microsoft. It's used in many companies for simple and complex database operations. This course, once again, is perfect for beginners because Microsoft SQL Server is very beginner friendly. And through this course you will get employable skills, learned best practices, and you will get to experience industry standard database development and management practices and use that kind of software. So in the next lesson, we're going to dive right in. We're going to see how we can start configuring our Microsoft SQL server on our machine. 3. Download and Install SQL Server: All right guys, In this lesson we're going to be looking at setting up SQL Server on our machine. Now, finding SQL Server is installed. Files is as easy as a Google search. I literally just googled SQL Server and the very first search result is good enough for me. You'll see that you have different tools or different years. So based on your machine, you may want to choose a version that is in keeping with the specifications of your machine. But at this point I'm going to install the latest version, which is 2019. So just by clicking that link, it will bring me to the download for SQL Server 2019's. And then I have a few options. I can one installed on Windows and Linux Docker containers. There you go. You have the different installation instructions and you have other things that you can look at. But we really wanted to focus on the free versions, which are the developer version and the expressed version. Express is a free edition that is ideal for development purposes and for very small applications. On servers are desktops, whereas developer is full featured. So you actually have different Edison's like professional and enterprise. And you'll see here the Azure and On-premises, and it varies based on its butt then based on the version that you select when it comes to on-premises, you realize that you have an evaluation for the Enterprise edition or the professional Edison, et cetera, right? So you can look through them. But at the end of the day, we want to go with the one that is free for development and educational purposes. In this course, I will be using Express. That being said, everything I'm about to do in Express is also available in developer, however, has a lot more that is available but for learning purposes. And it just because I don't know where everybody's machine capabilities are, we can start off with Express. So when you click Download know, you will get an installer file which you can launch immediately. Make it prompted by your machine like I just did. But this is what that installation looks like. So you have basic costume and download. Now I'm using experts because the setup is easy and it's very easy to maneuver with. So you can go ahead, hit basic. Accept, choose where you want it to go. If not the default location, make sure you have enough space relative to the download size, and then you can hit Install. Now when that installation is done, you're going to see some pertinent information being presented to you. The first thing that stands out is the instance name. You're going to see SQL Express. I have SQL Express or one. The only reason for this difference is that I already have SQL Express installed on my machine. So in other words, I have one version of SQL Express installed and I'm installing another one. And for every time I would install, I would get a different instance name. Instances like a container that has all of the databases in it. So based on the instance that you connect to, right, then you would be storing your data bases on that instance. You don't need to install multiple am just explaining why I have a different name from you. If you have gotten to this beard successfully, then you have successfully installed and SQL Express instance on your machine. No need to worry. They also let you know that the administrators would be your machine name, slash your username, and the engine, and everything and connection strings and some other bits of data that you don't necessarily have to worry about, right? No. The next major step would be to install the SSMS, which is the sequel server management system. So this is the user interface that will allow you to administrate your instance, set up your database is your tables and everything so you can go ahead and hit that, which will launch a new window in whatever browser is your default browser. And then you can just download that version that is available to you. So when you finish and you install that, and the installation of that is fairly simple and straightforward. You'll get this prompt and you just click Install and next, next, next to it. So I'm not going to walk you through step-by-step. It's fairly straightforward and you should not encounter any major issues. When all of that is done, you'd have completely and successfully installed SQL Server on your machine, whether this is your personal laptop or a server laptop, it is no capable of hosting an administrative databases. So stick around because in the next lesson we're going to look at how to connect to our database and start interacting with data. 4. Create Database: Hey guys, welcome back. In this lesson, we will be looking at whole weekend, connect to SQL Server and create our first database. Now the first thing that we want to do is launch or SQL Server Management Studio. Now once that has launched, you're going to see a window looking very similar to what I have on my screen. Now the first thing that we have to do is clear this dialogue box, which is asking us to connect to a server. It is asking us for our server name. And for that we're going to put in the name of the SQL Express server that has been installed. Now, to do that, we have to precede it with either the word local host. And then we use a backslash. And that is usually the slash that is situated directly above the Enter key. And then we will see SQL Express. They have a number of options here. And I'm going to walk you through each option, but they all really amounts to the same thing. No local host here refers to the machine that you are on that is local. So local host refers to the machine that you are currently on. An alias for local host would be a full stop. You can also say full stop, then the slash and then SQL Express. Another way that could be written would be the name of your machine, which in my case is MSI. The letters S, M, S, and a represent the name of my machine. So whatever your machine name is, you could also use that if you are so inclined, then the other option would be to use the IP address, which will be one to seven dot 0, dot dot one. Notice once again, all of them are just aliases, are other ways of seeing local host. What comes after local host or the machine name or whichever option you chose must be slash and then SQL Express. Once you put that into the server name slot, you can leave the authentication as Windows authentication. So it will use the user that you are currently logged in as and then you can hit Connect. After hitting connect, your object explorer should be populated won with the name of the server that you are now connected to. And then you'll see the other folders that allow you to access various parts of the server. If you've got an error, then please revisit the server name. If you are sure that you have the card server name, then you may need to go and check if the service is running so you can hit your Start button on your keyboard and then start typing in the word Services and then launch that window called services. Though in services you can scroll until you see the service for SQL Server, SQL Express, and makes sure that it is in a running mode. If it is not seeing running, just like all the line that I have highlighted is seeing running. If it is not doing that, then you can hit the start button that is usually located right here. Or you can right-click and say, start if anything, and it is running and you're still getting an error, you can always restart and try again. If all of that is feeling, please revisit the lesson on installing and configuring SQL Server. Now in our Management Studio, after we have successfully connected to our SQL Express server, we can expand the list of databases. Not doing that. This is a fresh installation for you, would lead to nothing. You would just click the plus button and you'd get nothing. However, you can see that I have been quite busy with my SQL Express instance. And so don't worry, I won't leave you out. That's what we're about to do together. Lets us look at creating our first database. Now a database is the collection of tables. And remember that each table represents an entity. So a table represents the thing that we want to store data or bolt. And then in that table, we're going to have columns, and each column represents the data points. So let us start off by seeing right-click on the Databases folder and then we will see the option for new database. We click that and then we're going to be led to another dialog box that asks us for the database name. So here I'm going to get a bit creative, go out of scope a little, and we're going to do a sports database. We want to create a database where we want to store all of the sports, all of the teams that are involved in those sports. And maybe it can be expanded to store the players and locations and potentially the leaks. However, for this course, we're going to keep it simple enough that we can get through the exercises and appreciate the basics of creating a database and tables. I'm going to call this sports underscore database. Know when naming your database, you don't want to use any special characters or spaces. It may work. But my recommendation is that you avoid them because later on it can lead to unnecessary complications when they're trying to write queries and trying to make a reference to the database objects. So the only special character that you would usually see people use is an underscore. And you can get done onto square by holding down shift, bringing the hyphen button that is in the same rule as your numbers up top right underneath the F buttons on your keyboard. So sports underscore database usually want to say database, or you can see sports DB or DB. There are many variations, but the general rules are you want to use what we'll call posco case, where you capitalize each individual word and no spaces, no special characters outside of that underscore. I think I'm going to go with sports Db. Db is short for database, right? So we can hit that and hit Okay. Once we have done that, we will see sports DB appearing under our list of databases. So after that, if we expand that, we will see that we can look at database diagrams. So you can look at tables, views, external resources, synonyms, programmability, service broker, storage, and security. We're not going to get into every single one of these in this course. But it is good to understand or have an appreciation of what you need exactly to get started with that we have created our first database. When we come back, we will pick up where we left off and then we will create our first table. 5. Create Table: So we're back in this lesson, we're going to be looking at what we need to create a table. Now, you'll see that I have cleaned up my object explorer for you. So we're know on parity. Now, everything that you have in your object explorer looks just like what I have in my object explorer. To create our first table, what we're going to do is expand the database. And then we'll look for the folder that says tables. Now if we expand it, we will just see some basic folders that we don't necessarily need to focus on right now. But to create our own table, what we want to do is right-click, go to New and then say table. Then we're going to get this user interface that allows us to define the name of a column, the data type of the column. And if we should allow null or not. No rule of thumb when designing relational databases and certain concepts are transferable. So whether it's a MySQL database or Postgre SQL or Oracle or Microsoft SQL database. There are certain foundational concepts that are not negotiable. Rule number one, always give your tables what we call a primary key. Primary key is a unique identifier for any role in our table. Think of it like this. Remember that the table is a bunch of a collection of entities, entity records. Each entity needs to be identified uniquely. So think of an entity like a person in your country. I'm sure that your government has a way to uniquely identify when this could be in the form of an ID number that only you have. So we make sure that we assign a primary key to each role and that will act something like that. Tucks ID or that id. So only that entity will ever have that ID. If we look for something with that ID or an entity record with that ID, where assure that we only get one. So all of that to say, or first column, must always be called id. Or generally speaking, you call it ID or the name of the entity ID. So in our sports dB, our first step is going to be one to store the different sports. Alright? So I could call this ID simply, or I could call it sport ID. Now there are a number of reasons you'd want to name it like that. One is for consistency, and two is just best practice. You could call it puppies if you want it, but that wouldn't necessarily mean anything to somebody else looking at the database. So the standard would be that you call it either after the table or the entity name and ID are just ID. Personally, I prefer to just use ID because if I'm already in the sports stable than obviously the ID is relative to the sports ID. And then the datatype here refers to the kind of data that are restoring. If you drop this down, you'll see that you have a bunch of them. You may not necessarily understand each one. But I'm going to go with an integer because that represents a number or numeric value. So I'm going to choose int. Then. Do I want to allow nulls? So alone, no means am I allowed to not have date? No. Nobody in your country I'm sure, is legally allowed to not have a unique identifier, that particular Ib. So I'm not going to allow any record in my table to have to exist without an ID record to make sure that this is the primary key. First of all, I'm going to right-click it. And then I went to say Set primary key, which automatically removes that alone, no tick. So it's not allowed to be empty. And then down below you see that you have column properties. Now the next thing I'm going to do is enable the identity specification. So I'm going to expand this and I'm going to double-click here where it says Nor four is identity and make it say yes, no. The identity specification basically says, Please won't automatically, for each record that goes in. Here, that is how you can establish a primary key. That is always the first step that you should take. Now I'm going to go ahead and fill out the other columns and then we can discuss, I've added two other columns. One that says name, which will represent the name of the sports. When this is created, we need to provide the name. We don't need to provide an ID because it will automatically cone that for us will always be unique. However, we do need to provide a name for the datatype I specified in var char. So you can go ahead and type in name as a column name, specify in var char 50 as the data type. That means it can support text, just regular text. You just type in the name, whether it has a number or special characters in varchar can handle that kind of data, then we choose not to allow null, which means that if you're entering data, you have to provide a name. You cannot have an entity existing in this table with alter name, just like how it will need an ID, but of course, we took care of that with the city. The next one is individual. Now, notice it is asking a question, is individual is a sport and individual sports, because we have football, we also have tennis. Well, so I've been bombed. So those could be considered individual sports. So this is really just what we call a bit or a Boolean or a yes or no. Yes, it is individual. No, it's not individual. Right. So that is just another naming convention is individual. So that's how you tend to name your big columns. Now, these are just best practices. They're not necessarily prescriptive, just descriptive. I've also chosen to allow null on that column to see if our table, we're going to hit Control S or we can use a floppy disk in the toolbar. And then here we're going to give it a name, so we call it Sports. Know the entity is sport too, because as sports, each row represents a sport. However, the table is a collection of records, so it will be called Sports. And with that, we have created our first table. If we click on Tables and refresh, then we will see our first table. 6. Write SQL Statements: So picking up where we left off in this lesson, we're going to be looking at creating statements that can interact with our data. Namely, we want to carry out crud operations, which is an acronym for create, read, update, and delete. To get a new query window, we can easily click new window here, or we can hold on Control and press N. Now I'm going to make this a bit bigger. So I'm going to hold on, Control and zoom in if you need to. You can also use the percentage gauge down here and choose a percentage Zoom that is appropriate for you. Now, when we are going to write an SQL statement, the first thing that we want to make sure that we do is target the cart database. You want to always start off with a use statement. And what I'm going to do is write the keywords in all caps. So we wanted to see use and specify the database. And then it knows that everything that comes after that land is relative to or sports DB. No, let us look at how we can create data now to create data or create an entry in our database table. Remember that we created the table cause sports, right? So to do that, we need to have insert statements so we can write insert and then into, then we specify the table name. The table here is sport, and then I can say values and then open and close parentheses. Now inside of these parentheses, I'm going to provide values that need to exactly aligned with the columns. So one, I don't insert an id value because remember that's an auto-incrementing primary key. Auto-incrementing means that it will automatically cones and it will provide its own value. It doesn't need me, but I do need to specify the name, and I do need to specify the bit for these individuals. So let us start off with the first one and notice that it's invar char. So I have to use quotation marks so that it knows that the values between the quotation mark is whatever value I want in that name column. So I'm going to say football. Then I can say comma, and then I can specify a true or false. It doesn't accept true, and it doesn't accept falls. Instead, a bit is one or 0. So false would be 0. And I wrote my table name wrong. That should be sports. So you see that it will kind of give you that red squiggly line just like Microsoft Word or any other word processor woods. So when I press F5 or I hit the green button up here that says execute, then it will tell me one row has been affected. Now if I want to see the data in that row, then I want to read. So this was our create. To make a comment, we do double dash. And then that was created, right? So hyphen, hyphen makes this not executable. It ignores this. This is just for me to read and understand. This was our create statements. So when I want to read, I run a select statement. So I say select asterisk from and then the table name. So that gives me read query. And then if I only want to execute this because I don't want to insert the same sports again. This time I want to see, and I only want to execute this one line. I highlight it, and then I press F5 or execute. And then it runs that select query and shows me the data. So it shows me the ID1, which was auto-increment and automatically provided for me. That is its unique identifier. The name is football and it is not an individual sport. Then if I wanted to update, lets us see that I needed to change that value from football, basketball. So I went to write an update statement. So the update statement would have me see updates and then the table, and then I would specify set the column. So I'm changing the name value, right? I'm updating the value that is inside the name column. So I have to specify set name to have the value of whatever the new value is. The new value would be basket ball. So I want to execute just this line. I can highlight that statement, press F5, and then it will show me one row affected. Now, this is a very dangerous statement around like this. Usually you want to have a weird statement and I'm going to show you why in a moment. So if I select star from sports, I'm going to see that this has been updated to basketball. Now let me reinsert football and then we inserted one role. If I run this select again, I'm going to see I know have basketball and football. Now look at what happens if I change this to tenants. If I say update sports, set, the name to tennis, unfortunately, tomorrow's got affected even though I really just wanted to update one. Why is that? Because I didn't specify which one of them I wanted to update. Which is where we have what we call a where clause. And then that is where our id value comes in handy because each sport is uniquely identifiable. So if I had two records of tennis and then I realized my mistake and I wanted to change this one back to football. Then I can say update sports setName to be football, where the ID has the value of one. When I do, the update statement will know exactly which record to target. If I put in an ID that doesn't exist, it will search. But to no avail, nothing got affected. When I run that select query, again, I will see that I know have that record. The final thing that we're going to look at is the Delete. To delete a record, we have delete from that we specify our table. And then we would want to put on the same where clause because this statement, as it stands with lead, everything from the table C, two rows affected, even though I only wanted to delete one. Now there's nothing in the table. So I'm going to reinsert football and then select just to make sure. And then I can do an update to change it to what it's already football, so skip that update. But no, if I have maybe two records of football and I only wanted to remove one of them. I can know use the same where statement or a where clause specify the ID value that I want to target. And then I see where the id is equal to four. Please delete. And then you'll see one row is affected. And then we can select and we have one rule. Now, in a nutshell, this is how all of our crowd statements for any database that you will ever write in SQL Server will always look, study these statements, studied the structure of these statements. Nowhere to put the table name and holds a structure it. And that will form the basis of all crud operations across your database. 7. Table Relationships: All right guys, So we're back and what we'll be doing in this lesson is creating table relationships. Relationships are exactly what they sound like. A relationship between two entities means that something is in common between the two, or one depends on the other for some bit of information, that's essentially what a relationship is. In databases are relational databases. We have what we call foreign keys, which facilitates that association between one entity and another. So you started off with our sports table. And we have, okay, we have the ID, we have the name and we have the individual. Now, every sport has Teams, or potentially has teams, which means that a team can not exist by itself. A team relies on a sport. So when you hear me say Real Madrid, you're going to associate that team with the sport of football, just like LA Lakers, you would associate with basketball. That would have us create a foreign key or a relationship between our new table, which is going to be teams, and our existing table, which is sports. So let's get into it. We know how to create a table, you just right-click tables, go to New and table and we get a similar interface where we fill out the ID, we give it the datatype int, and then we right-click it so that it is the primary key because every table should have a primary key. And we made that primary key and identity column so it can auto increment. Next, we have name because every team has a name and we know that we use n var char for that data type. Now, you can increase the size of the invar char because not every team might have a size name of 50, right? You might be putting in some complicated name like Baron Munchausen, glad buck, or one of those German names. So you can always increase that 5200 or if you're not entirely sure, You can just put the max. I would recommend however, that you do some analysis and use the best maximum size possible because you don't want the database to grow too much for smaller values. So for now we'll just leave it at 50. Next stop, we're going to have to associate this team with a sport. So I'm going to enter sport ID. Now this sport id column that I've entered is going to represent that foreign key or that association between this table and the sports table, which is why I called it sports ID. So anybody who comes and looks at this database should be able to surmise that this is a foreign key column. So that is a good naming convention to follow sports ID, the datatype would be whatever the matching datatype in the other table is. No, the foreign key in one table should directly match the primary key of the table that it is. Therefore, let me say that again. In this case, sport ID is there as a foreign key relationship to the primary key of the sports table. If I look at the ID column in sports, it's an integer. So that means sport ID as a foreign key and a foreign representation of it must also be integer. Then, well, we could choose to allow null R-naught, but lets us save this table first. So now that I've created all the columns that I know nor the team needs. I can Control S and save and then I'm going to call IT teams. But our work here is not done. We still need to set up that foreign key. That relationship between the tables simply creates a new column suddenly is not enough. So what I do is right-click the column that I know should be the foreign key. And then I go to relationships. Then we get this dialog box where we have to add the relationship. So we click Add, and then we get some options here. And we see tables and columns specification. If I click that once, I will see this ellipsis appear to the right. So I click that ellipsis and it launches another dialogue. In this dialogue it asks two questions. One, where is the primary key located? Well, the primary key for this relationship is located in the sports table, and it is the primary key column of that table. The primary key table would be sports. And then the column would be id. Name is individual, the primary key. Then it says, what is the foreign key table? Well, this one isn't really a question. It knows where the foreign key table is because I started the process from on the foreign key. So it already knows where the foreign key table is, but I do need to tell it exactly which column should be the foreign key. The matching foreign key would be sport ID. And just because I followed good naming conventions, it is easy for me to know that the sport ID is a foreign key column to the sports table and the id column therein, which is the primary key. So following good naming conventions can save you a lot of headache in the long run. So let us go ahead and select that click. Okay, click Close, and then we can save. And then what we have right now, is there a relationship between the teams and the sports tables? Now it's going to say the following tables need to be saved. You want to continue, they're both going to be affected. You can click Yes, if you do get an error, which you probably will, what do you need to do is go to Tools, go to Options. And then from here you're going to search for the designers section. So we look for the designers sexual and expand that. Then we go to tables and database designers. And then you want to make sure that you untick this option that says Prevent saving changes that required table re-creation. So you can untick that click Okey tried to save once again, and you should have the same experience that I just demonstrate it. So right there we created our first relationship between two tables. But if we want to see that relationship in a visual manner, we can create a new database diagrams. So by clicking that, it is going to ask you if you wish to create the diagram, you can click yes, if you do get another error at this point which looks something like this, then my quick tip to get by it is to just click OK and restart your SSMS so you can do that. No. And after relaunching, reconnecting, and going to our sports dB, we can right-click new database diagram. And then that will launch this editor that allows us to add our table so we can select both tables, go ahead and click Add. And then it will take some time to add and generate what we call an entity relationship diagram. So when I click Close, it literally shows or entities or tables. Alright, so remember that tables represent the collection of entities. So this would be a representation of an entity. Then it shows the relationship between the two. So you see here that Teams has a relationship to sports. So once again, in a nutshell, we create relationships when we have certain entities that rely on other entities for additional details are additional data. It doesn't make sense if I have 20 teams and I keep on repeating the word football for every football team. Instead, I have football once and then I have relationships from the 20th football teams. So the one instance of the sport that is football. 8. Complex Select Queries: Welcome back. In this lesson, we're going to take things up a notch and we're going to be looking at more complex select queries. Now, I've already written the query. You can hit pause. You can reproduce these on your own machine, which I'm going to walk you through what I'm doing here. First of all, I'm using the cart database and then I am stating that I want to insert these teams. Now notice that the ID here is three teams, as we know, just by expanding here and looking at columns, we have the ID which we don't need to provide a value. Four, we have the name, which in this case would be Real Madrid. Then we have this sport ID that is required. So the sport ID here is going to be three. Why is it three? Because if I run a quick select from my sports table, and I can always do that and just highlight the one line, press F5. Then I'm going to see that the ones boards I have here is three and it is indeed football. These are all football teams. So I can highlight all of those. And then I can press F5. And then you'll see here that it says one row affected four times. So if I reproduce this query, and I'm going to just say select star from Teams this time, press F5. Then I will see here that I have four teams, each with their names and each one with a card sports ID. So I've populated the Teams table. Now what if I had other sports and other teams for those sports? In this case, our basketball. And I'm going to have that Team, LA Lakers, associated with basketball. So what I would want to do first is insert the sport. And after an insert, It's always good to just do a quick select so that you can make sure that this data was actually inserted. And you can also look at the value. So this new sport got the value of five, the id value of five, which means that if I am to insert into teams and use the sport id value of four, I will get an error and it will see that it conflicted with the foreign key constraint. This is a relational database is we have seen that I cannot establish a relationship between what's your trend to enter and any record that exists because no record exists with this identifier. So I have to make sure that I use an identifier that exists. And that would be five. And no, everybody is happy. So once again, if I look in the Teams table, I will be able to see, and I can just select both. So we can see both. And we'll see all the sports up top and all the teams below. So here we have LA Lakers with the ID of five, which means that it is associated the basketball. Now, this is where relationships come in handy. Once again, because I have four football teams. I don't have footballer beating. I could've had a typo and then it would be four rows with the wrong spelling of the word football. So we keep it one place and just create that relationship. Now what if I wanted one query that shows me all the details because right now I have two queries and what I'm going to do is just select all of this and hold on Control, press K and C, and that will make all of that commented. Alternatively, you could go line by line and it preceded with the double dash. So I want one select query that shows me in one rule all the details about LA Lakers because this sports IID means nothing to me. If I only look at the teams, I'm only seeing sports idea. I don't know what three is. I don't know what five is. I don't know what 15 is. Instead, I would prefer to see the name. So we have to write more complex query. So we already have the select star from teams don't. I would love to see the details for the sport ID. So what we have to do is extend this and say inner join. And there are different kinds of joints. Some persons leave off the type of join and just say join. You also have left join and you have right join. But the simplest one to get started with his inner join, inner join. And then we state the table that we want to join onto, which usually represents a table with which we have our relationship. We know that Teams has a relationship with the sports table. And the sports table is the table that we want to see the details off. So we say select star from Teams, inner join sports. And just so you know, all of this could have been written in one line, but I'm breaking it up so you can see each segment. So the inner join sports, then we have the keyword on. Then we need to see what the primary key and foreign key pair is. So I know that the primary key of the sports table, and I'm going to see a sports dot ID. So I'm seeing on the ID column in the sports table being equal to the teams dot matching foreign key value, which is sport ID. So let us dive a bit. First of all, we're selecting everything that is in the Teams table. But I also want to join the details from the sports table on the condition that the ID in the sports table is the same as the ID in the sport ID table. So what this should yield is where the three matches a sport value or a sports star ID value. I want to see the details of what three is. I want to see the details of what five is. If there was 50, we'd want to see the details. Once this value that is in this column matches a value that is in the id column of our sports table. That is basically it for handling getting the details once there's a relationship. So once I do this big select, not want to see everything where I cross. Now I can see that Real Madrid has a sports ID of three. Here's that matching value of three and the name is football. La Lakers has a value of five, matching five basketball. Know we can get more granular because star or asterisk here means select everything from all the tables that are all aligned. Obviously, some of these data points I can live without. So I don't necessarily need the ID columns and I don't necessarily need the sports id column. So I can be a bit more specific and I'll just duplicate this. And instead of seeing asterisk, I can specify the column from this specific table that I want to see. So I can say sports dot name, give me the sports that name column gives me the teams dot name column. And give me the sports dot is individual column. Of course, the order that I have them in his order they'll appear. So I'm going to run F5 and show you both results sets and then you can determine which one you find more readable. Now name, name can be a bit confusing. We can do what we call aliasing on these columns. And I'm just going to use my square bracket here. And I can see sport name inside of those square brackets. And then I can see team name. And notice that I'm putting them right beside the actual column that I want to alias before that comma. So once again, when I try that, I can see sport name is football team name and is individual. Well, we could alias that one, but I'll leave that as a challenge for you. You can check the resource script for the details on that implementation. 9. Productivity Tips: Welcome back guys. In this lesson we're going to be exploring some efficiency tips and tricks so that we can move around in the Management Studio as quickly as possible when we have a lot of things doing. So we already know that when we want to create a new query, we can easily hold on Control and press N, and that will launch a new query window. We also know that if we have some texts that we wanted to comment out, we can hold on Control, press K than c, that will create a comment on that light. Now there are other things that we can do, like if we wanted to run a quick select query on a table instead of sitting down and writing or they use statement and then writing or the statement for the select, we can easily right-click on the table and go to Select TOP 1 thousand rolls. Once we do that, it will actually generate that select statement and execute it for us. Now you'll also notice that the select statement, it looks slightly different from what we have been doing because they don't use this star. They actually spell out each column and they use the square brackets because if you use the space or some illegal character in the name, the square bracket nullifies the adverse effects that it could have in your code. So while I did discourage it, it, SQL actually actively mitigate against those scenarios. But once again, best practice would be you leave old spaces and special characters when naming your tables and columns. So that is a quick select query. It also will limit it to the top 1 thousand, so it doesn't try to get every single record in a huge table out all at once. Now another productivity tip would be that if you want to carry out crowd, you can right-click on that table and do edit top 200. Know what this does is it brings up an editable grid where you can actually insert data on the fly. You can edit the data that might be there and you can remove data. So let's say for instance, I re-enter tennis and is individual, I accidentally wrote true or I put the numeral one. No true works here. We saw that true does not work when we're writing the query here. But true works in this view. However, if we were to query, it would actually show us 0 R1. That's what gets stored as a bit. So you don't have to be mindful of those little nuances. So if I decided that I wanted to change this because Tennessee is not an individual sport, I can easily come here and just type in false. And once I press up or don't, it will commit that change, right? So if I try to create a new record and remember that we specified that name cannot be null. If I had tried to create a new record by only stating true, and then I tried to change. I will get an error where it tells me it cannot insert the value null into the column name. So we'll enforce all of the rules that we put on our database from the jump. So here I have to make sure that I provide a proper value. And another sports here would be baseball. So when I changed that value, see that the incrementing works. I cannot enter anything into that increments. I'm typing right now and nothing is changing. And it is showing us the id. It is showing us the name and the individual flowers and any other column that you have. So this is a quick way to insert one or fewer rules if you really need to, however, maintain that script would be the fastest way to do like a bulk insert or a bulk edit or bulk select. So if I wanted to remove baseball, I can highlight the entire column, press, Delete. It will actually ask if I'm sure notice the script did not do that the last time. But it will ask you for sure. I see. Yes. And then it will remove know because there are relationships that exist on some of these records. If I tried to remove a sport that has dependent values, meaning, remember that we have some football clubs that are all there. They're relying on the presence of a sports with an ID of three. If I had tried to remove that sports with the idea of three, I will get an error because it will tell me that I cannot remove the parents required when there are children depending on it, Isn't that nice. So I cannot accidentally remove football or else I would have to set up policies. That means that if I remove football, remove everything associated with football, which you may not necessarily want because you want to maintain the integrity of your data. Now we saw how you can generate a quickselect. We've also seen how you can bypass the inserts, read, Update, and Delete queries. But what if you actually wanted to generate queries for those situations? So I can right-click on the table and then I can go to script table as. And then that allows me to generate, create statements which we did not look at in this particular course because we use the UI for it. But I can also generate a select query and insert query, update query and a delete query. So if I do update and then choose New Query Editor window, you will see that it will actually generate that update statement for me. And all I have to do is fill in the blanks accordingly, right? And I can just put in that where statement and then by pressing F5, it will go, it automatically chooses the correct database form. So you can challenge yourself and fill this out. It's going to look the same way, except when we did it, we only set name, but this time you'll see that you can set the name. You can set the sport ID. If you don't want to set one, you just remove it. But then you also have to make sure that you provide that where clause where you specify the column and the values. So the unique identifier and a value it needs to have so you know what you're targeting. So you can play around with those script generation tasks and to see how they can make you more efficient in sequel management studio. 10. Conclusion: This was Microsoft SQL Server development bootcamp. And I hope you've gained a lot from this course and you are ready to take on the challenge of developing and designing your first database. The time you need to practice and reinforce these concepts. And don't shy away from challenges that lay ahead. Once again, my name is terroir Williams and it was my pleasure to be in this course with you. See you next time.