Hey guys, in this video, we will be looking at quite a few concepts, namely creating tables using SQL scripts. And also the fact that we can have multiple commands in one file, and how we can execute them all versus executing them in parts. Now, the first thing that we want to do will having a new script file and the desire to start adding something to a database, in this case, the school DB is to set the context for our file, meaning set the database that we know we want to be using, and we looked at that before and the keyword is us. And then we specify the database that we know we want to use. So I say use school dB, and it with a semi colon. And I'm just putting some space in between.

And I could just add comments up top, but I'll leave the comments for when I'm saving the file. And then after I specify use dB, then I can go ahead and And start filling all the rest of the query files. So please note that SQL commands run in a very linear fashion meaning order matters. You have to see this before you see anything else. And whatever order you put the things to happen in, that is exactly the order it will be executed in. Alright, so the first thing that we want to do is create a table.

So to create a table, it's a similar bit of code, we say creates the object type, which is table, and then we give it a name. And in this case, I want to create a table called students for my school database. Then I will just open and close brackets and end with a semicolon. So I like to open and close brackets at the same time because that way I don't open and forget to close. All right, so I prefer to do it that way. Also, you'll notice that a command can span multiple lines.

Our command does not end until it sees a semicolon, so wants us semicolon it is present, then it knows that that is the end of the commands. Even if I take up 20 lines with this command, it will see all that way as one command. So I'm just spacing it out this way so that we can see exactly what we're about to type. Now the very first thing that we include in any table, at least, for me, it's best practice to do it this way is the ID. Remember, in the beginning of this course, we discuss what you call the primary key, which allows each rule to be uniquely identifiable. So in your country, you may have a tax ID, and that allows the authorities to know exactly who you are just by having that tax ID.

Well in our database, we want to have something similar to that for each students. So the simplest way to do this is one the ID, the name ID sorry, which is the name of the column. So the first thing you specify is the column name. And then we say data type and the data types. can be integer they can be var char they can be deed. So in other words, they can be numerical.

They can be alphanumeric, or just letters, or string, so to speak, and they can be date, they have a number quite a few of data types. When we're writing the script, you have to actually remember the data types, but I will include all of the possible data types in this lecture. And in another video, we'll see how my SQL workbench helps us to keep track of these data types and which one we want to use. But we go ahead and we see ID and then the data type we want is int because our IDs know when to be commenting. Each time I record goes in and think about it. If each time a record was in that number goes up by one, then no two records will ever have the same number because they will be numbered from one to n. number of records.

So what we do and and you'll see as a common pattern is just to specify a column called ID set it to be an integer. And then you would say something like auto underscore increment. So this means that each time of valuables in this column will automatically increment and it's going to start at one so the first record will have the value one, the second record will have the value two. And for as many records as you have, this number will go up. Even if you delete say 15 Records and delete rule number five, if you add a 16th record, well, at that point, it would be the 15th record because you just removed one but if you add another record after record 15, then the number will still go to 16. Alright, so this number will always increment, no matter how many Records preceded.

So that way it will never have the same number as a preceding record. So we see auto increment. And then what happens is that in specifying the columns that this table should have, we have the name, the data type, and then what we call attributes. Alright, so this is the first one. So I just completed this. So we have ID integer value, auto increment, and then I also want it to be the primary key, so I just write primary key.

So that's another attribute that I'm giving it. And then if I'm finished with the attributes, again, just press comma and go to the next line. And then the next thing I want for the students is the last name. So that's a column name. And notice color coding is giving me what is a keyword versus what is a column name. Alright, and then a last name would be more like a string, it's a word so we wouldn't have integer.

It's not numeric. So the next data type would be. And if you just start typing, you'll see that it's giving, they're giving you all of the potential values that could be used for data types here. So the most commonly used data types for string based values are var char and in var char. So at any point, if you know that in this column, or any data points associated with this column, you should be storing a word or an alphanumeric sequence, then I suggest that you use an invar char or a VAR char. So I'll just use n var char and set this to 50.

So by doing that, I'm setting that this last name, column whatever valuables in should have no more than 50 characters or 50. spaces or whatever it is, that makes up the string that is about to go in here should be no more than 55 sit down and count the number of letters in this law. Alright, and another attribute that I'm going to add is not No. So this will specify that this last name should never be empty. That's what not gnosis. So any column that you put not null on will never be empty. So we could have put non null up here.

But the fact that we put auto increment means that it's always going to be counting, so it won't have time to ever think about being empty. All right, so it could be placed up here at no harm, but for me, it's just redundant. And now there are other just separate them so you can appreciate why auto increment weave works the way it works, and not know specifies otherwise for any other column. All right, so another column we want is first name. And notice no spaces should be ever put in the column name. So I just did that out of instinct for my typing, but it's a good thing.

I did that because then you see that this is giving me an arrow and it's called Both of these as keywords when they should never be keywords. So if it's a case where you need a space, then you want to use the, I think that's apostrophe or an accent. And that is not not is not this, that's not a quotation mark, that's a quotation mark different from the apostrophe. Alright, so take note of the difference between the two on the keyboard. So the quotation marks, the single quote is not what we want here, we want the apostrophe. And then that will say, anything that is in between these two should be seen as a column name.

Now for me, it's best practice never to use a space. So if it is that you want to not have it as you know, last name being, you know, Cronus together because really, that's what they are towards, then I suggest to use an underscore. And then that way, you can have a cleaner, column name and design going forward. So I'll just add an underscore to last First name, first name. And I can just copy these attributes from last name since they are going to be common columns. And note, each time I'm going to add a new column definition, I end the line with a comma.

So Id, data type or attributes, comma, last name, the data type, or attributes, and then comma, and then as long as there's a comma ending, then you'll see that there's an error here, but we're not finished with the table as its, as we're going to put in date of birth, and I'll just use underscores so that we can see each word specifically. So of course going forward in your design you you probably have your own pattern and your own style. I'm just making suggestions and this practices as I go along, but ultimately, you will develop your own style of doing things. And we would use a date data value for this And we could see and not know. So no student should ever go into the database without a date of birth, last name or first name, that's essentially what all those not knows me. And then any students would have been enrolled at some point.

So I can see enrollment date. Once again, I'm going to use my underscore. And this should also be a date, but I'm not going to make this not long, because maybe you would have signed up but you haven't enrolled in any courses as yet or whatever. So I'm just showing you that you can specify some columns as not long meaning they are very much required for any role to be input, and then you have those without the not know. And notice there is no trailing comma at the end of this statement. So that would have created our students table.

Now in addition to the students table, I just go ahead and create Another table for courses. And pretty much is very similar procedure, we just open and close our parentheses. And then we go ahead and fill in all the columns. So once again, Id n, unique identifier or primary key for me is best practice, always have one on your tables. And in this one, I use the pattern where I just said Id, sometimes you'll see people actually specify the table name and the word ID, and write it out. So you'll see like that sometimes.

But once again, it's all a matter of personal style and preference someone to leave it like this so you can see the tool with and then each course should also have a title. So remember that a table is a collection of data points. So right now we're actually defining all of those data points that would create facts To all information about whatever entity it is that this table will be storing those facts on. So courses, what is it that the course has a course has an ID, of course has a title, which is an N var char of type, maybe I can put this to 100, maybe the course name is long, and of course can't exist without the table title. So I'm setting the attribute to be not known, meaning no course can ever go into into the table without having this value. I'm going to set the number sorry, number of credits.

And I'm making this an integer value. And just for just for fun, and for demonstrative purposes, I will meet that not know because maybe we have thought of Of course, but we haven't thought of how many credits we're going to give it just yet. So I'm not sitting that does not know. But then we have course pause. Which is also in var char and I'm going to make this a smaller value because generally speaking, of course, school, it is not more than five characters. But then of course, according to your needs, you set the size that you want.

And I'm also going to say not low, because it doesn't make sense, it creates a course that doesn't have a poor score. So and I'll just add a comment here so you can see what I mean. So of course score could be like, maybe this course in a university would be coded CS two, one introduction to databases, right. So that's an example. So you see a comment can be included here. Once again, any comments will be ignored by the execution sequence, the blue dots on each of these command lines or command blocks, rather, specify that this is a command by itself.

Of course, you could have written all of this in one line, so There's no harm in writing anything online but then I just broke it out so that we didn't have to scroll across the page to see what was happening right. So and then you see here, it's harder to add this comment because then the comment is including some code in it and causing an arrow. So that is one of the reasons that I would have broken it line by line so that we could see each line and scrutinize it individually. Alright, so you see I have added some files here. Some comments here, sorry. And that's just for informational purposes.

When you're reviewing this, this chord file, you can see exactly what is happening here and be guided accordingly. Now to execute once again, we can just click our lightning bolts which will then execute us school DB and then this and then this in the order that it is set up in the documents. But then if I only wanted To execute parts of the query, I could just highlight it and click this lightning bolt with a cursor. And if we just hover over to see that it's executes the statements, that is under the keyboard cursor R has been selected. So essentially, that's what that means. So those are two ways.

If you have multiple commands in one document, but you're only execute executing one or few of these commands, then you can just highlight that command or those commands, and you just click that one with the cursor. So I'm going to just execute all of these, which will then choose the database and then creates everything else. And then you getting a warning here. It's seeing that I have some character friends, stuff, I think that might be caused by the versioning. Let me see if anything happened. So my tables were created.

So you see, we have Success, we have arrow, we have warnings. So sometimes you get warnings. And most times these warnings will be because you might be using certain bits of code without specifying certain things. Most times warnings are harmless. But in this case, it is because we got our table with all the columns, and you can drill down and see everything about our columns accordingly. And if we'll take a look at our columns, so we drill down into school DB tables, courses on click columns, then below the schema section, and we'll see definition.

So we have course ID a notice that it kind of stands out. And if you look at its attributes, they're telling you that it's an integer, it's auto incrementing, and it is the primary key. So my skill uses those little things AI and PK just to show you the attributes that it has been given. All right, and then you'll see all the Are those with their attributes, this is title is var char 100 and so on. So that is essentially how you go about creating tables in my SQL using commands. And I'm just going to save this file.

And I'm just want to see create tables. And we're saving that as an SQL file. And I'm going to be sharing that with this video. Now another way we can actually create tables without the use of the SQL script is by using the GUI tools afforded to us by my SQL workbench. And we can do that by right clicking on tables. So having gone down into the database, and we have the tables, parts exposed, we can right click that and then we say create table, which will then launch a new interface allowing us to specify the name so another tip That would be useful in SQL DB would be a table to store the lectures, right.

So we are tracking the courses, we're tracking the students. And now we need to track the lecture. So I'm just going to call this table lecturers. Alright, and the schema, once again, is school dB, we can leave that all of those things can be left alone, and then we can go ahead and specify the column name. Now it's worthy to note that my SQL supports both relational database style and know what we call no SQL style, which is a variation of databases that doesn't rely on relationships. And to get that you would use my sample then if you click on this engine drop down, you'll see that there are quite a few storage or engine styles that my SQL is capable of.

To be honest, I always use in odb because in a relational database setting, that is the one that we want to use. So We can leave that alone. And then we just go ahead and specify our or column name. So we got a primary key column by default, because my school is forcing us to be to practice what is best. So I can always change this column name. And I can see lecture ID or this idea to see ID, the data type is already int.

And they're already telling me that this is a primary key. If I ontic that you'll notice that the emblem changes, so the three tickets and means not null. And each of these columns mean something different. So the checkboxes means something, you can always just hover over the letter or letters to see exactly what is being described. All right, and the one that I'm really interested in is auto increments. So remember that when we're creating our tables here, we specify a primary key, which is DK over here.

We also specified auto increment, which is a, so the stick AI. All right, and from this view, we can actually add more data. So we could add a default value we don't need a default value for or ID because we should never have a default value for our ID. We can add comments so that we know in the future or if we have to share this database with anybody, we can always write comments so that they can see exactly what this column is for. And if you tick it here, then you will see that it is ticked down in this section also. So you can use either one of these to manage those attributes.

Okay, and then we to create another column, we just click in the next line, and then we say, first, or let's use the same convention last underscore name. And then this is using the data value type, far char as opposed to N var char which we use, but I'll just leave it Does var char and then I can just take no. And I'm using first on the score name by and also not know. And something else about a lecture would be, that's the title. So maybe the person is doctor or let's see degree. So is it that they have a BSc MSC or PhD, alright, and then we can leave that one as no loadable.

So you see that the emblem changes based on certain attributes being selected. All right, you may get a different color or or visual cue to the left hand side of the columns. And then once you've finished creating all of your columns that you intend to have, then you can go ahead and click Apply, which will then give you a very similar looking script to what we just penned. But then you see that there are certain various So that just goes to show you that there are more than one ways. Now we could have actually written this script, because here it is being specified. But then primary key is down here as opposed to where it was in the one line when we did it.

So you see that there, there are different ways to write. It's also for the nullable fields, we didn't write the word normal, we just left it blank. And it automatically assumed that there should be no so you know, if it's generated is going to look a bit different from what we have. It also specifies the engine here. So if we click Apply, then it will let us know if it was executed successfully or not. And when we finish, we can look over to our table listing and we will see our newly created table there.

And if we look at the columns, then we will see that everything looks in order. So that's it so we can go about creating a database table using the my SQL workbench interface.