Hey, welcome back, everyone. In this video, part two of topic three in our database class, I'm going to provide an introduction to the SQL Create Table Statement, including a live demonstration in SQL Server Management Studio. Let's see our first statement, and this is the Create Table Statement. The Structural Query Language is designed to be simple. It uses simple English language keywords, and it's also designed to be simple and easy to learn. So we can look at this statement here, and this is a very simple SQL statement that will create a table, an employee table in this case. And even if we've never seen this before, if we're reasonably savvy, we can look at this and probably figure out what's happening here. But before I get into the details, what I want to do is encourage you to not memorize these specific things, but rather focus on learning the pattern. When you're learning the Structural Query Language, it's all about learning the patterns. And if you can memorize the patterns, then you can easily write SQL statements for any specific scenario. So the idea is to learn the general pattern, and once you have that, you can apply it to any specific scenario, like any database, whatever the tables are, the attribute names or the data types, whatever it may be, you will know how to write in SQL statements because you learned the pattern, rather than trying to memorize the details for specific examples, like the ones that you see here. With that idea of patterns in mind, let's take a look at our first pattern. And again, the idea here is that we want to write a SQL statement that will create a new table for our database. It begins always with the keywords create table. So we use create, and then we specify the type of database object we would like to create, which in this case is a table. And then we specify the name of the new table. And so you can see in this case, we're proposing to create a new table named employee. And then we'll have an open parenthesis. And ultimately, a closed parenthesis, followed by a semicolon. So everything that happens between those parentheses is a part of the create table statement. Now again, this one here is very simple because we always want to start with a simple version. And in this case, all we're doing is just creating a table that has two attributes. Create table, the name of the table, open parenthesis. And then the first thing that we do when we're creating a new table inside these parentheses is we specify the columns that we want to be included as part of the table structure. So in this case, we're saying, all right, create a column named employee ID. So this will be the name of the column. And then we specify the data type. In this case, an integer, which is a four byte integer, meaning it can hold whole number of values ranging from approximately minus 2.1 billion to positive 2.1 billion. So that's the range of values for the integer data type. And we specify it's null status. We learned in our last topic about null values. So we're saying here that null values are not allowed for this employee ID attribute. That is, whenever we create a new row in our employee table, we must provide an employee ID. Null values are not allowed. Then you can see we have a comma followed by the details for our next attribute, which in this case is an attribute or a column named employee name. And then we can see we're using a very care data type here. This is a variable length character string. And we're specifying a maximum size of 25 characters. So character data types like very care can hold any sort of character data. It's like text. All right, so any sort of character data you wanted to hold could go in there. And in this case, what I'm doing in this illustrative example is I've created a storage space, so to speak. That is a maximum of six characters long. So if we wanted to do something like this would be like very care six. Means the maximum length of what you can store in that field is six characters or 25 in the example shown here. So you can just fill this in. As long as it has fewer than six characters, you'll be fine. So if I put Dan in here, that only takes up three characters now. Here's what's really nice about Vercare is it is space efficient. So if I use, if I have a maximum length of six, but I only use three, and I'm not going to use these extra spaces at the end on disk. It's not going to waste that space. So I'll only use the actual three characters that I need for this field plus one more. And that's usually represented by having a little box at the front. We have one more, and that's one that tells us how long the value is, like how many characters are in there. So in this case, we might see something like this. So this would actually take up like four units of storage on our disk, one, the first one, to hold the length of the string, which is three characters. And then the next three spaces, hold the three characters that actually make up the value. So in this case, I'm only using four storage locations. If you want to think of it as bytes, like four bytes of data, that's fine. And that's a lot better than if I had done something that where I would use six storage spaces, six bytes of data on disk, regardless of how long the actual data value is. So this sort of design here is the character data type. So if I had something like character six, then that's what we would get. So when you use a character data type, and you specify its length, it will always use this number of storage spaces. So if I specify a character data type of length six, I'm always going to use six bytes of storage, even if the value that I'm trying to store is shorter than that, like in this scenario, I have three characters here. But if I define this as a character data type rather than a very care, so if I just define this as like care, that was a six instead of a 25, then no matter what, we're always going to use six bytes of a storage space on disk, even if we're storing something shorter. What this means then is that you should only use the character data type if you have some kind of fixed length string. So something that is always the same length. I don't know in the United States where we have social security numbers. Right, and they have a fixed pattern of three digits, a dash, two digits, a dash, and four digits. And in every single one of them follows that pattern, it doesn't matter what it is. So that would be a good candidate for the character data type. We would just set it to whatever length we need, like 11 characters or something like that for social security number and store the values. But if we have something like a name, like here we have employee name, right? And we know that can vary from person to person, then we should use a very care data type because it will be able to store the results more efficiently. We're not wasting as much disk space. Right, in this case, if I specify a maximum length of 25, it means my employee names can be anything from length one to 25. And we will be able to store anything in there without wasting a bunch of extra disk space. So there's a little quick lesson there. Other data types that are useful to us, so here's an integer. There are several integer data types. So in addition to integer, we have other ones, including a byte. And let me do the smallest one first. Smallest one is a bit. And that can store values zero or one. So that is an integer data type, but it'll only store two possible integers is zero or one. This is also useful for storing Boolean data. So if you want to store something like yes or no, true or false, you can store that using a bit. And it will literally use just one bit of storage space. If you're interested, bit is short for binary digit. So that's where that comes from. Binary being the mathematical base, I can tell only two possible values of zero or one, binary system. So one bit holds one unit of information, right? A single unit of information. All right, so that's our simplest integer data type. Another one is a byte. And this holds values in the range of zero to 255. So a byte consists of, no, it's not a computer science class. We won't get too deep into this stuff. But a byte consists of eight bits of information by arranging those zeros and ones. You can think of it as a string of eight zeros and ones. We have a total of 256 possible unique values. So they range from zero to 255. So if you have an integer value that you need to store and it's going to be relatively small, I don't know if say that you needed to store, I don't know, like the day of the month, you just need to store like the day of the month or something like that. You can use a byte data type for that because the range of values for a day of the month would be the byte provides to you, which as you can see here is of zero to 255. So another integer data type that we have available to us is a small int, right? This holds values if it's signed in the range of approximately minus 32,000 to plus 32,000. So a small int uses two bytes of data. So twice as much storage space as a byte, but you can see you get a much wider range and you can store about 64,000 different numbers in there, ranging from minus 32,000 to positive 32,000. And after that we have int or integer. And this one goes from about minus 2.1 billion, plus 2.1 billion. I don't remember these are integers, so it's always whole number values. All right, so I can't store any decimal data in these, that would be lost, but these are just integer data types. And int, a regular int takes up four bytes of data, so it's twice the size of a small int, which is twice the size of a byte, so you kind of get the idea. And then there's something called a big int as well, which is an eight byte integer. And that can store values that go up to something huge, like 320 billion or trillion or something like that. So it's really big, it's minus 320 trillion to, I don't know, plus 320 trillion, something like that. I don't remember the exact number for the big int, until the idea. Basically, if you need to store a very large integer value, that's more than a couple of billion than you can use a big int to do that. So these are all integer data types. And you just learn them, it's just something that you learn, okay, based on the nature of the data I need to store, I can choose one, say that you need to store, I don't know, like the year, the best choice for storing just the year out of these options would be a small int. And because our years are, these days are going to be in the range of 2020, 2022. And that falls within this range. It's too big to fit in here. And it's small enough that using something like this would be excessive and would be wasting space. So we can use a small int to store something like a year. But if we need to store something larger, I don't know, let's say that we're storing our employees, annual salaries. So if we have like reasonably skilled employees, we would expect that they're earning more than, say $32,000 per year. So in that case, we might choose something like an int data type to store the employee's salary. Because it will almost certainly not exceed 2.1 billion dollars per year. So we just have to be judicious with our choices of data types, right? And all of this comes in SQL, drawing it back to our SQL discussion. When we are defining these columns, right? So remember, it's all about patterns. The pattern is name of the column, data type, whether or not null values are allowed. If I want to allow null values instead of saying not null, I just use null. So this would say that null values are allowed for our employee IDs and that our employee IDs will be integers. So we now know what that means in terms of the range of acceptable values that can be stored there. And if you try to store a value that is outside of the range of the data type, the database will generate an error for you. So if I was using integer, like I am here for employee ID, and I tried to put something in there, I don't know, five billion, that would be outside of the range of an integer. And the database would throw an error. So through a judicious and intentional data type selection process, we can actually use the chosen data types to help guarantee and protect the quality of the data in the database, just by knowing what their range of values are. Now, if we're gonna have a simple design like this, where it's just one field or one column to store the employee's entire name, 25 characters is probably not enough, right? If you, some people have three names, some people have four names. And if you just think in your minds about, what is the total sum of all of those characters, you can probably think of some scenarios where it would easily exceed 25. But so if you're just trying to store like a first name or a last name, 25, 30, 50, might be a reasonable upper limit for you. All right, so the overall pattern then for create table, statements is keywords create table, the name of the table that we're going to create, open parenthesis, and minimally we need to have a column. So column name, data type, whether or not null values are allowed. If there's more than one, then we put a comma, and then we do the next one, right? Employee name, data type, whether or not null values are allowed. Now you'll note that this one is the last one, so there is no comma, right? It's the last line here before we get to this closing parenthesis. So there's no comma after that last line. All right, a few other things that I'll point out here before we proceed, is you may have noticed some things. This is all upper case. This is all upper case. This is all upper case, right? This is capitalized, whereas these are not. So let me explain what's going on out here. First, I want to state that the way that you're seeing things written right now is very intentional, but it is only for the benefit of we humans so that we can look at this and get a better understanding of it. Now, what that means is that the computer will understand this regardless of your capitalization. So if I wrote this in lower case, something like, create table, employee, blah, blah, blah, that's fine. The database will understand what it is you are trying to convey by writing this. However, I do it in the way that's illustrated here for some very specific reasons. First, words that are entirely capitalized, it's all upper case like you see here. These are keywords in the structured query language. So whenever I see something that's all capitalized, I know immediately that is actually a keyword in the language. That's a part of SQL. These are reserved words. If something is capitalized as we see here, I use that as a way of indicating that this is a database object like a table, when you're dealing with a simple SQL statement like this, it doesn't matter much, but as you'll see later on, when we write some more complicated statements, it's really useful to be able to know what this is. So if you follow this sort of approach and you capitalize the names of your tables, whenever you see that, you'll know that by virtue of the fact that it's capitalized, that is a table. Whereas things like column names are not capitalized, for exactly that same reason. I know by the fact that this is not capitalized, that it is the name of a column and not the name of a table. So this is my preference, but I do want you to understand that it's not required. Other things you'll see is like there's indentation here. I think this has been indented over one. And again, that's for readability. It's such an indicate belongingness. In the sense that these lines here belong to this create table statement that starts here and ends here. But again, this is just a preference. It's for the benefits of human, for humans like you and me. Like we could just write this all out all in one line and it would work perfectly well. So we could do something like create table, boy, whatever employee ID, and not null. We could do this all in one line. We wanted to, we could even do another one. I don't know, name, pair, care, 30, not null. So the database will understand this. Even if it's all just written out in one big line and exactly the same way that it would understand this. But aside from very simple statements, like this is a relatively simple statement here, these SQL statements can grow to be quite long. So learning to format your statements using an approach like the one shown here is very useful. It helps you visually as a human being to really see and understand what's happening in this equal statement itself. And so I recommend this approach. It's something that I've developed over my many years of working with databases and it tends to work really well. You just have these sort of visual clues. Like these are all upper case. I know that's a key word in this equal language. This is capitalized. I know that's the name of a table. These are not capitalized. I know that these are attribute names or column names. All right, so that's easy enough. Let's see, maybe a little live demonstration of this. I'll just switch over here to SQL Server Management Studio. And if you remember, I've used this, I use this database a lot for examples, but I just little employees database over here that looks a little something like this. Got our four tables in there. All right, departments have employees and we have employees have skills and we keep track of those in this employee skill table. So let's say that we wanted to expand our database a little bit and maybe add, I don't know, like a projects table to it. Now, what you could do is use the structure query language for that purpose. I'll just click on new query. And this will open up a query window where I can write MySQL statements. One thing that I'd like to point out before I write MySQL statements is this lovely and very important dropdown box right here on your toolbar. Okay, so this dropdown box tells us the target or destination for the SQL statement that we will be writing. And this is my employees database, right? And what that means is that when I write a SQL statement in here, that statement is going to be directed to this database. You can see I have lots of other pieces out here for various projects and things that I work on with my research, but it's always a very good idea for you if when you want to write some queries, you click on that new query button and just glance over there and check to ensure that your statement is going to go to the database that you expect it to. And if you need to change it, you could switch it up here. And you can get one of this to go to my reading list database. But for now, we want this to go to employees. So I'm going to select employees. And then I know whatever SQL statements that I write here in my little query editor window, this area are going to be sent to that employee's database. All right, so let's write ourselves a little SQL statement here where we're gonna create a new table. In this case, I'll create a project table for us. And again, it's all just about learning the patterns. So create what sort of thing do we want to create a table? What do we want that table to be named? How about project? And the open parenthesis, right? I like to fill in some of this stuff early. So I've got my closed parenthesis and semicolon down there and anything that happens between this open parenthesis and this closed parenthesis, we know and will be a part of this create table statement. Okay, so I'll tab that over just for the sake. And let's say that we have a project ID, project ID, and we'll make that an integer. And just to follow what we saw on the slide, we'll make that not null. And let's give it another one, how about a description. Let me call it something else, how about project name, just so that we're very clear. And now that we know what Vercare is all about, we'll use that one. And let's give it some reasonable space. We'll say up to 50 characters. Now for the sake of us as humans, I'm just lining up some of the stuff here. But again, this is optional. It just makes it easier to read, see what's going on, right? So if I have something like that, we now know that this is going to create a new table in our employees database. It's gonna have two columns and that's it, very simple. First one will be an integer. Second one will be a link character string. Note by the way, that you could also do this by spelling out the word and its entirety integer, but it works as well. And you'll note that the keywords, the SQL keywords create table into Vercare, these are all shown in blue in this editor. So it's nice, it again gives us a little visual indication of something that distinguishes the keywords and the language versus things that we're adding, like project ID, project name, et cetera. Now I'll go ahead and run this by clicking on the execute button here on the toolbar or by pressing F5 on my keyboard, but I'll just click on this. And we'll see commands completed successfully. Now if I expand my set of tables over here, you will see that we have a project table that now exists in our database. Before I move on, I want to illustrate something here for you. Let me go ahead and delete this. I'm gonna delete this or drop this table that I just created. So now you can see we no longer have our project table over here. I'm gonna run this SQL statement again. So I run this command completed successfully. Now if you look over here on the tables list, you'll see that our project table does not appear among the tables in this employee's database. So if this happens to you, all you need to do is right click on tables and select refresh. And then you'll see that your new table will appear in the list. So it just doesn't automatically update the tree structure over here in the object explorer for some reason. So you can always just refresh it, like right click refresh and you'll see it there. Now if we were to expand this out, take a look at the columns. We would see that we've got these two columns here, project ID, project name. And they have the data types and null statuses that we've provided up here. Easy peasy. Pretty easy to create tables using structured query language. No big deal.