SQL may be primarily used to find action data requests, but another major benefit it brings to the table is, well… tables.
Yep, you can use SQL to create and alter tables in your database easily and efficiently, so no matter how much data you’re dealing with, keeping on top of it all is an absolute doddle.
Today, we’re going to be running through a beginner-friendly, step-by-step SQL table making tutorial. Stick with me, and you’ll have it mastered in no time at all!
DDL Is The Key
To create a table in an SQL server, you’ll actually be using DDL (Data Definition Language), a subset of SQL, rather than SQL itself.
Don’t let that spook you, though, as DDL uses the exact same syntactic principles as SQL, so if you know your way around a database query, creating a table using DDL should come naturally.
DDL Keywords And Syntax: Creating A Table
Rather fittingly, the DDL keyword for creating database objects is “Create”, so that will always be your very first word in the statement. To create a certain object, you simply have to specify which, directly after Create in the syntax chain, i.e. Create Table.
Next up, you have to supply an identifier, or, in other words, a name for your new table, and this name goes right next to “Table” in the syntax. For the sake of example, let’s say that you want to name your table tbl_Clientele. That would leave the statement looking a little something like this…
Create Table tbl_Clientele
Now you can add your column titles, but beforehand, insert some brackets like so:
Create Table tbl_Clientele
(
)
In the space between your brackets, you’ll detail your first column. Let’s say we want it to list the first names of your clientele; we’ll type that out within hard brackets which, for the uninitiated, are these guys here: [ ]
So, now our statement looks like this…
Create Table tbl_Clientele
(
[first name]
)
Now’s the time to add a string and character limit to our first column. A string is essential just a data type, and you’ve got two to choose from here:
- Char
- Varchar
“Char” means character, and should be used if all the data values in the column will be equal in length. Char is commonly employed for numerical columns. Varchar, on the other hand, stands for variable characters, meaning it’s used for columns that will have irregular data values.
As our example column will be listing names of differing lengths, we have to use Varchar as our string, and all that’s left to do then is to give a character limit as a number in brackets, followed by a comma.
It’s unlikely that anybody’s first name will be longer than 20 characters, so let’s pick that for our character limit. Now our syntax should resemble…
Create Table tbl_Clientele
(
[first name] Varchar (20) ,
)
Now you can rinse and repeat the same method for your remaining columns. For instance, if we added middle name, last name, phone number, city, and age columns, we’d end up with…
Create Table tbl_Clientele
(
[first name] Varchar (20) ,
[middle name] Varchar (20) ,
[last name] Varchar (20) ,
[phone number] Char (11) ,
[city] Varchar (20) ,
[age] INT
)
Don’t be alarmed by the “INT” section in our age column. That stands for integer, and all that means is that the data values in that column must be whole numbers.
Adding Values To Your Table
With our columns established, next on the agenda is to fill them up with values. To kick this process off, you simply need to add the keyword “Insert” beneath your closing brackets. Then you have to explain to your database where you want the values to go, which is, of course, our new table, tbl_Clientele, so you would type…
Insert INTO tbl_Clientele
But this isn’t enough information. In brackets, you also need to explain which columns you wish to add values to:
Insert INTO tbl_Clientele (first name, middle name, last name, phone number, city, age)
With the value locations set, we can enter our values, first by typing the keyword “Values”, followed by the data in brackets. Each value should be separated by a comma, and they’ll be assigned in sequence to the columns labeled in the above example. It should look like this…
Insert INTO tbl_Clientele (first name, middle name, last name, phone number, city, age)
Values (Joseph, Jedediah, Bloggs, XXXXXXXXXXX, Austin, 38)
That’s the first row of data entered — Woo! But how do we go about adding our second, third, fourth, and so on? Well, it’s easy, you just put a comma after your first bracketed row, and type in another, for example…
Insert INTO tbl_Clientele (first name, middle name, last name, phone number, city, age)
Values
(Joseph, Jedediah, Bloggs, XXXXXXXXXXX, Austin, 38) ,
(John, Jeremy, Doe, XXXXXXXXXXX, Dallas, 29)
And you can keep on doing that, ad infinitum. So, our full syntax should now look like…
Create Table tbl_Clientele
(
[first name] Varchar (20) ,
[middle name] Varchar (20) ,
[last name] Varchar (20) ,
[phone number] Char (11) ,
[city] Varchar (20) ,
[age] INT
)
Insert INTO tbl_Clientele (first name, middle name, last name, phone number, city, age)
Values
(Joseph, Jedediah, Bloggs, XXXXXXXXXXX, Austin, 38) ,
(John, Jeremy, Doe, XXXXXXXXXXX, Dallas, 29)
ID Columns
If you’re worried about identical information in a simple table, an ID column will help you differentiate between the two interchangeable entries.
The ID column should always be first and look a little something like this…
[Id] INT IDENTITY (1, 1)
First, you have the column name (Id) set as an integer, which, if you remember, means the value has to be a whole number. Then, instead of Char or Varchar, we have “IDENTITY”, which is a data type that will automatically increase in value with each row.
The first number in brackets next to IDENTITY establishes the value the column will start at, which in our example, is 1, and the second number in the brackets determines the increment of value increase, which, again, for us, is 1.
All that’s left to do is to attach “PRIMARY KEY” after the bracketed numbers, which will ensure there can only ever be single values in our identity column.
So, our final table including an ID column would look like this…
Create Table tbl_Clientele
(
[Id] INT IDENTITY (1, 1) PRIMARY KEY ,
[first name] Varchar (20) ,
[middle name] Varchar (20) ,
[last name] Varchar (20) ,
[phone number] Char (11) ,
[city] Varchar (20) ,
[age] INT
)
Insert INTO tbl_Clientele (first name, middle name, last name, phone number, city, age)
Values
(Joseph, Jedediah, Bloggs, XXXXXXXXXXX, Austin, 38) ,
(John, Jeremy, Doe, XXXXXXXXXXX, Dallas, 29)
Final Thoughts
And there you have it — you can now create your own detailed tables in a database using SQL/DDL languages. Once you’ve got the syntax memorized, it’s a total breeze!
Here at Open Query I want to make it as easy as possible for you to learn about topics that might not be easily accessible elsewhere online. I don’t know about you, but I haven’t seen many other websites talking about the MariaDB Foundation and its updates.
That’s what makes Open Query truly unique. While others might assume everyone knows this information already automatically, I am filling my online resource to help.
- What Is Percentile Rank? - February 15, 2022
- How To Use Distinct In SQL - February 14, 2022
- How To Drop A Column In SQL - February 14, 2022