Well, hello, everyone. Welcome to another video lesson on the Structured Query Language. In this lesson, we're going to be taking a look at an entire set of different SQL commands that are grouped together under the category of DDL and DML. Let's dive into this. This particular video, we're going to talk about the create statement and there's a bunch of other ones that come as we're talking about DDL and DML. DDL stands for data definition language. DDL is really like a grouping of SQL commands that we use to create, remove, and modify database structures. Well, what's a structure? What does that mean? Well, inside of my database, I can create things like tables and indexes and then I fill those things with data. There's a distinction between the structure itself, which is like an empty shell, and the data that I put into that structure. The DDL statements, and specifically we're going to look at create, alter and drop. These statements allow me to manipulate database structures and the DML allow me to manipulate the data that goes inside the structures. We'll take a look at the insert, update, delete, and truncate. Let's begin and talk about the database create statement. I will use the create statement when I want to create a new table or view. We do have another lecture coming up on of view, so if you're not sure what that means, don't worry about it, we'll cover it in a little bit. When I run a create statement in my database, the create statement will define a new table by giving it a name and it will define all the columns within that table. For each column, I need to define the datatype and the length for that column and possibly any constraints that I'm going to put on that column. Now, we will have a separate video lecture to talk about constraints, so don't worry too much about constraints yet. If I'm going to create a table, usually I drop the table before I create it. You'll see an example of a drop statement here shortly. The drop statement will only drop the table if it exists. If I try to drop a table and it's not there, the drop statement won't do anything. You'll see when we do the drop that it has an IF exist clause in the drop statement. We'll see that in just a second. This is what the CREATE TABLE statement looks like. I say CREATE TABLE and I give that table a name, then I put a left parenthesis here whose partner is down here at the very end of the create statement. You got to make sure that all the stuff that you put inside the create statement is wrapped inside those parentheses. Then I can give it a column name and a datatype and a length for that datatype. I can give it a column name, it's followed by a comma. Then the next column, it's data type and length and I can add a constraint like NOT NULL. I will talk about the constraints in just a moment. In this case, the datatype doesn't have a length because some of the datatypes we use in SQL, it doesn't matter how much data we're going to put in there. It always creates that column with the same length. I'll show you some examples of that. Now, I can put a constraint on here saying NOT NULL and give it a default value. I can also create another constraint with the constraint keyword, and then give that constraint a name and then set up some more information about that constraint. These constraints apply to the column that they follow in the CREATE TABLE statement. You'll see that sometimes SQL developers like to put the parentheses that wrap the create statement on a separate line by themselves and that makes it easier for you to keep track of them and not lose them and make sure that everything that needs to be within the create statement is wrapped within those parentheses. That's a common thing you'll see when people do table creates. Here's our DROP TABLE statement, and let's go and run this. Our database that we've been using for the lab work in this class contains a little table called shippers. I'm going to go out here and drop, if it exists, the shippers table and it does exist. It shows up here on my bit.io screen. As I run this query, it drops the table and there it's gone. See that? So that's how a table drop works. It doesn't ask you, are you sure? It just drops it when you tell it to drop it. Then we will run a table create. The table create, in this case, I'm creating a column called ShipperID, which is an integer column, and I'm putting a NOT NULL constraint on that. A CompanyName column, which is a variable length character with a maximum of 40 and a NOT NULL constraint, and a phone number, which is a variable length character field up to 20 max and it's got a NOT NULL constraint with a default of zero. What that means is, and we'll see an example of this. When I insert a row into this table, if I don't provide a phone number, it'll put a zero in there. Let's go ahead and run this CREATE TABLE statement since I just dropped the table. I think we better put it back. Here we are running the create table and it put my shippers table back here. Right now there is no data in the shippers table. We're going to take care of that shortly by running some inserts. The not-null constraint just means that the database software will not allow me to insert a row into the table if that column is missing a value. Not-null means don't do the insert if the value is missing. However, if I set the default option, then it will insert a row even if the value is missing, but it will plug in the default value. Now, this is just one constraint. There's a lot of constraints that we can use in our database, but we'll talk about them later in a later video. Here are the insert statements, then I'm going to put the rows back into that shippers table that we just dropped and I just recreated. Notice the third row that I'm inserting is missing it's phone number. We will see the database provide a default value for that. Let's run these inserts and put these three rows back into that table. Now, I can take a look at that shippers table, and here's the three rows that I just inserted. You'll notice that even though this is a not-null column, the insert didn't provide a value, so it uses the default of zero. There are a lot of different datatypes that you can use, and those datatypes really depend on which database engine you are using. We are using bit.io for our lab work in this class. Bit.io users and underlying PostgreSQL database engine. There's a lot of different data types you can use in PostgreSQL. I've provided a link here on this slide number 9, that you can go out to the web and look at the PostgreSQL tutorial website, and it'll tell you about all the different data types that you can use within PostgreSQL. But here are some of the more common ones. For example, there's an integer datatype, a big integer datatype, a small integer datatype, and here's a screenshot from the PostgreSQL documentation that helps you understand what integer you need if you want to create a column that's defined as an integer type. I can create a small integer which only takes up two bytes of storage on disk, and it can have this range of values. I can create an integer which uses four bytes, and it can have this range of values, or a big integer, which uses eight bytes of storage, and has this range of values. I can create a Boolean data type which is just a one or zero, yes or no datatype, I can create a character datatype. The n indicates the maximum length of that particular column. I can have a character column or a variable length character. Folks sometimes refer to this as varchar with the maximum length of that variable length character column. I can create a text column. The text column has no length and you can put into it an unlimited amount of text. Now, realistically there is a limit, but I don't know what it is and I challenge you to run into it if you create a column with a text datatype. As we looked at in an earlier video, I can create a column with a datatype or a timestamp. Date is just the date. Timestamp is a date plus the time. I can even do a timestamp z, which keeps track of a time zone. If I'm dealing with numeric data, I can create a decimal data, the x here represents how many total digits in my decimal number, and y refers to how many of those digits are to the right of the decimal point. Or I can create a floating point or a real numeric item that allows me to have unlimited precision. Or I can create a JSON, that is, JavaScript Object Notation, that is a special data type that I can use to store semi-formatted documents inside of my relational database. You have a lot of variety that you can choose from when you're creating tables in PostgreSQL. Now, at this time, since we're using bit.io for our lab work, these are the data types that are supported in bit.io. The stuff I'm doing in lab or to show you a little demonstrations, I'm limited to using what bit.io supports. I can create columns that are integer or numeric columns that are real with a precision, with a decimal point and a certain number of digits to the left and the right of the decimal point. I can create character data, which is text or varchar, I can create binary data where it just stores bit strings for me. These are pretty much the same. I can create dates and timestamps, I can create a Boolean column, and I can create JSON columns. This is what bit.io supports if I'm going to be creating tables in bit.io. I think that wraps it up for our create DDL statement. From here, we're going to go on to look at some other DDL and DML statements. Thank you.
