Hey guys welcome back. In this video we will be looking at creating a table. Now once again, a table is inside of a database, a database can have more than one tables. So the first thing to note is that our database engine which is SQL Express can have more than one databases. And 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 we're about 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 have more than one databases here. And then we need to specify which one it is that we're about to use recall, we have These databases as well as school, so we need to specify that we are about to use school. So we say use. And then we use our database name, and there are IntelliSense is completing that for us. And I'll just put the semicolon there this time.

The next task is to create a table. So using very similar syntax, I'm going to write my create statement. But instead of database Last time, we said CREATE DATABASE instead of database. This time I want a table. So I'm going to create table and then I'm going to specify a name for this table. In this case, we want students so essentially, students, it is pluralized because the table is going to be storing records for students.

So you'd want to prove pluralize the name Have your tables just thought of principle because each record, you know, makes it plural. So we say CREATE TABLE students. And then we open and close brackets. And of course, we get that little red line because we 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 line I'm breaking line because I like to see where my code starts and stops and have it tabulated.

But everything could actually be written out in one line SQL is really not going to throw any arrow with it either way. So the first thing that we want to specify is a unique identifier, or what we call a primary key. So the simplest name that we can give to this unique identifier is ID. So I'm going to say ID Which is our column name. And then after our column name, we have to specify a data type. Now, if you have done other programming languages, then a lot of this may seem familiar, a lot of them may vary.

But otherwise, let's just go through this slowly and appreciate the different data types that are available to us for this simple example. So our ID data type is going to be a number type, which in SQL is called in short for integer. All right, and 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 data type, and then anything else would be an attribute. 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, the word no means empty. So we're saying that this should never be empty, no student record should ever go into the database without this value. All right. And then we have one more thing in Microsoft SQL that we add on, that allows it to count automatically. Because when you have the first five students, it's easy enough to say, okay, student one, and then follow the rest of the data student two, but then when you reach to a million records, it's hard to keep track, it's hard to know which number was previous and it will become tedious trying to keep up So Microsoft SQL as well as other database engines. They give us the ability to count automatically.

So each time a record goes in, this column would just increment by one, just go up by one Or you can specify whatever value you want. And it will take care of that. So if it's always counting, then that record is always going to be uniquely identifiable by its ID. So in Microsoft SQL, that syntax is identity. Sorry, right, and TT, and we can just 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.

And we want to start counting at one meaning the first record should start at one. So if we wanted to start at 10, then we could see 10. Right? Well, we'll just leave that one. And the second parameter in this function says that we should increment by one so if we wanted to start off at two and go up to 468 with the truck Then we'll start counting at two. And then say we want to start, every record should be incrementing by two.

All right, so by leaving it at one, one, and sometimes you just see the word identity. Alright, 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 name, data type, and then everything else would be an attributes. And these attributes are by far 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. And then obviously, we don't store names as numbers. So we need another data type to store last name. So the provide us with a few data types that take text when to use This one called n var char. All right, you have n var char and you also have var char, you also have char, and you have quite a few of 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 n var char. And then I need to specify how many characters should this n var char column or data point be able to accept. So this is also another constraints I want to construct and it to me with 50 characters, which means no student record should ever come into my database with more than 50 characters. All right, so nobody's last name should be I can think of a last name that's obscure enough to violate this, but at least we're restricting So if you know you're building a system for a coding shoot that turns out very exotic names, at least by our English speaking standards, then you may want to play around with this character limits. So I'll just leave this at 50. And 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 comma there, then it will give an error. So if it was the last one, there is no comma but then we have more to put in so we use a comma. Next we need first name, and and var char. And we also set this to 50. And let's just say I want to put on a constraint I want that no student record should ever go in missing the first and last name. So I want to make sure that this column is never know.

Okay, and I'll just add that constraints to our last name column. So last name should not be no and first name should not be not another another. Another important bit of data to have on any student attending my school is the date of birth. Once again, SQL is not case sensitive. 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 easy to 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 of the columns if you're going to be lower caps, you stay lower caps if you're going to use underscores you use underscores but be cautious systems.

Alright, so I'm just going to be consistent with my lowercase. But then you may have different ideas from me. And that's fine. We're all individuals. We're all learning here. So you can use your own flair.

And you can break it down using your underscores or camel casing if you wish. All right, so dates of birth and date of birth would, well, obviously, we're storing the dates here. But then we will go on to data states because maybe we want to do some date manipulations later on. So SQL actually gives us a data type that is suited for storing bits. We actually in modern SQL engines, like 2017 have a date data type, but then for older versions, we have date time that's that works in virtually every version, so I just stick to date time, just in case I build this thing in 2017, but up to deploy it in a pretty Previous versions, I just use the more universally acceptable data types, but it's of course you feel free to play around with them all. So we have date of birth, and it takes the time.

And this should also not be no. All right? And then I'm going to add one more column, and this one would be enrollment date. All right, so you can see here where having a lowercase as the as the column names get, kind of more, you know, bigger and more descriptive. It's more difficult to keep up with what is what So once again, you can always bring these old using your underscore to increase readability or camel case them by capitalizing the first letter of each word. Alright, right.

So enrollment date, one day The same as date of birth, we'll get the time. And I won't put on any constraints to specify that this one wouldn't be not because maybe the students came on registered, but they, they haven't enrolled, they just came and filled out the form, but they aren't really taking 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 an ID automatically. They can't be entered without a last name without the first name or without the date of birth. But then the enrollment date is allowed to be empty.

All right. So as a quick recap, we specify which database we're about to use. So literally, we were at us and then the database is named. And then we go about creating a table. We give it a name, and then we start specifying each column. column by writing the name that we would like the data type it should be.

And certain attributes are constraints that should surround the data going into this column. So we're going to execute this and then we will see what happens. Alright, so it was completed successfully. So we can actually expand our database here, which is school. And we will 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, and lo and behold, there is our table called students. We will go about creating courses table in this database. So of course we have a school we have students who have courses, and I mean, we have teachers. So next up is courses. And just like our tables, sorry, our database is just Like our databases, we are not able to have two of the same tables with the same name. So if I tried to execute this script again, it would give an 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 out another create statement for our courses table. So we say create, table and courses. Open, first parenthesis, and close. 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. You will see it in the design in the in the order that the columns appear, but it's not a matter to see that Oh, you have to create The idea 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 a poorly constructed table to begin with, then the retroactive steps to correct it might, you know, be difficult. So you want to make sure that you have the proper thing from the beginning. So firstly, we'll say ID, and you'll see other people kind of specify if they're in the course is stable, then there's a course ID.

So that you know is very specific. Some people are generic and just call everything ID, some are more specific NC, whatever the table name is, and just append the word ID. 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 own pattern, and develop your own sense of individuality. All right, so of course ID and the data type as we discussed this event, I want it to be an identity And we'll see one of one primary key and not know.

And you as we go along, we'll see that not all of these attributes are absolutely necessary. Because the fact that this constraint is here or this attribute is here means that this one is implied. If it's a primary key, that means a value must be supplied. So we really don't need to specify not not. But I'm showing you all of these things until we get comfortable. So we have our course ID, which is our primary key, we say comma, go on, 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 own butter. We're using the data to type var char and specifying that it should be 50. So no course title should or will be able to exceed 50 characters. And then our last one is number of credits.

And this would be an integer because in school we have three credit courses, five credit courses, etc. So we want to specify that for any course it gets this unique identifier. We have a title, we have the number of credits, and in a college setting, we usually have course code, so we'll just go ahead and add that course code. And then this would be maybe an N var char. And of course codes aren't usually long, maybe five characters so we can see an N var char or five characters. And if it is a case where you have an alphanumeric value, meaning you have numbers and letters, so in this case, our course code could be something like for argument's sake si s for computer science.

201. So we have a secondary course in computer science. And that's of course scored. So that would be an alphanumeric value. If you have an alphanumeric value you need to store, you can simply store it as an invite char or as a regular text value. All right?

So, of course code like this would fit perfectly into this scenario. And I'll just leave that there and comment it out and give that as an example. All right, so you see, using our double dash like we saw in our previous video, we can just comment out a line. And it will be ignored when that query is run. And then we need to add some attributes. So no, of 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 and it should not go away. Though it's of course code. So minimally, it should have a title and of course food, what it's allowed to be there without any number of credits. Alright, and once again, this is all for example purposes. So now that we have specified everything for our courses table, we could just go ahead and hit execute. What will happen though is that it will run this line successfully, and then fail to execute on this line and then stop the query.

Sometimes it will stop the query, sometimes it won't, 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. Alright, so we have one query file. I can just bring this down so we can see the total picture. So we have one query file with three major commands and you can always tell where command starts because it will give you this little minus sign. All right.

So all of this is being seen as one command. Remember, we put everything into one line. And then this is another command. So as far as its is concerned, it has three directives or commands that it needs to carry out. So if we want to run one of these three, or any portion of all of the commands in one query file, we can just highlight that set of commands and click Execute or press f5. And it will then only execute what was highlighted.

Proof of this when we go here. We can refresh as we don't see any changes. So we click on our tables folder, and just refresh. And then we see we have dbo. Students and dbo courses our newly created table. Also don't here is a good indicator as to when something fails.

If I buy some miracle forgets and just execute then we'll see It stopped at the table, the Create students table directive and just said, Hey, we already have an object. And then it didn't go any further in the query. 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 to actually carry out all of the operations that we just so skillfully wrote SQL statements to accomplish.

Now, Microsoft gave 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 printing Creating a database and tables without writing one line of SQL code. Alright, so it's all begins with right clicking or databases folder. So we would have already been connected. And we'll see that we have databases here, we can always expand and contract that tree.

We just right click 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 say, school. And I'll just name this one v two, as in version two. And you have some options here you can look at. So every database comes with what you call an MDF as in fine file, which is the actual file storing all of the data.

And it has a log file which keeps track of all the changes being made against the data. So you can actually specify modifiers 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 two different hard drives, and one where the operating system is installed. And another one specifically for storing the MDF and log files. Alright, so you can change those, but I won't change those today, I'll leave those as is. And you can look at all the options, but really untruly You don't need to make any changes here. All right, and then we can just go ahead and click OK. And that will execute and voila, there we have a new database, not one line of code.

And then to create the tables, we can just drill down into the database, and then right click on the tables folder, and we say new and 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 of designer. So it's asking us what do we want as a column name, specify the data type, and if you want to allow or not. So I'm going to go ahead and recreate the same two tables in school v2 that we just wrote SQL scripts to create. So the first one, the column name I want is ID.

And I can just press tab to move across. So I type here and then press tab and it just goes in a lateral movement to the right, and then we can drop down and see the plethora of data types available to us. In Microsoft SQL. We are big into a bit we have char, we have dates, the time floats, we have quite a few of them. So many that you may get overwhelmed and want to use all of them but you can you can actually just go in and create a table. Maybe one With a column per data type and you just see the different behaviors between an inverter char 50 and in var char max or whole entire chart here is different from in char harmonise different from real small daytime, etc, etc, there are quite a few.

So you can actually just go in and play around with them. I encourage that. But today we'll just stick to the task of recreating those columns, those tables with the respective columns. So Id was of type int, and we did not allow No, and then you see, instead of allowing me to specify an 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 it more clearly. which then allows me to specify certain things. No, in order to set it as primary key.

I firstly have to right click, and I can see here set primary key So now we know that this is the primary key. However, we still need to tell it that it needs to auto increment. So we find identity specification. So you'll notice that certain terms may differ between the SQL statements and the designer view. But ultimately, whatever it is that you can do in the designer view, you can write a statement to accomplish and vice versa. So I want to specify that it is identity.

And I can just double click this to change the value or you can just drop down this drop box, and you just say yes. And then you'll see that it still gives you that increment value and that seed value, just like we saw when we were writing our SQL statements, so we leave those at one and one. All right, and then we can move on to the next column. So our next column in our student database was last Name and this one was n bar char and you can just type and it will try to autocomplete it for you with what it knows and we just typed and press tab and then it auto completed it and it moved on to the next option and then we did not alone knows on last name so that's a no and then the next one was first name also and var char does that it autocomplete and press tab and we did not allow those and we had Date of Birth which was the time we did not allow no and then we had enrollment date, which is also the time and this one we are loaded.

To be No, no, once you've completed all of this, what you need to do is save. So once you save, I use Ctrl S, that's the universal Windows shortcut for saving. So I said Ctrl S, and then I got this dialog box, which then said enter a name for the table. So I want this tables name to be students, click OK. And then what it actually did was execute that very query or a very similar query to what we wrote it did, it's in the background. So all of this just generated that query and executed it. And if we drop down into our tables, you'll see that it was successfully created.

Alright, so we can go ahead and create the second table in a very similar manner, new table, and then we get a fresh window. And notice by default, it is dbo table underscore one so it doesn't know what its identity is where the ones telling it who it is in life so of course ID and the data type was int Alo 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 I right click and say set as primary key notice that it automatically on ticks that box because it can't be a primary key and not identify a treasury ticket is going to see cannot be set to a low knows once that column is a primary key. All right, so it automatically enforces those attributes once you click and you click correct.

Alright, so we had the course ID. And then we had the title of the course. Which you said was n var char type of 50 do not allow knows Oh, and we skip this step we needed to set course at To be auto incrementing. So we needed to say identity specification is identity. And I can just double click, and everything is saying yes, so we can move ahead. And 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 of credits. And that was integer. And that was a lot to be know. So we can leave that on ticked. And then we had course code. And that was n var char and I can let it auto complete but I can always go back and edit it, take off the zero and make it five and that was not No.

And then once we're finished and we're satisfied with what we have done, we can just once again see so we just Ctrl A and S and then it will ask for a name This was courses, we click OK. And voila. So we can just go ahead and refresh this. And we see that our tables have been created successfully. So we have two databases with two identical sets. So you know structures. But then a database does not interfere with another database.

So two identical databases can coexist, as long as they have different names. SQL Server is not going to be able to know or these two are the same, they shouldn't be the same. They don't really care about the tables because the tables are within the context of this database.