SQL – Create table using syntax

Real job of programmer is to create table programmatically using SQL Syntax.

Why we need to create table using Syntax?

Previously, we have learned, how to create table through PHPMYADMIN. It is very easy. But it is not an efficient way to create table for following reason:

PHPMYADMIN is not only providing an interface to create table but also provides terminal using which we can create table. (Also commonly known as SQL Command section). We are going to write every command in this section.

Create table using SQL syntax

 

Launch XAMPP, then visit phpmyadmin page. Select your database. Then, Look at the menu panel, click on SQL. (Note: Always select your database first then click on SQL)

Create Table Using SQL COMMANDS

The above shown SQL Command box will appear. Here we are going to provide all the required SQL Commands.

How to create table using SQL Syntax or command?

In xampp, we are using MySQL. MySQL also follows SQL Syntax. There is a command in SQL, which allows us to create a table.

Create Table SQL Syntax (Command):

Create table TABLENAME(

COLUMN_NAME TYPE(LENGTH),

COLUMN_NAME TYPE(LENGTH),


COLUMN_NAME TYPE(LENGTH)

);

 

Example:

Lets create following pictorial table:

Roll_no Student_name Subject Marks
1 John Math 90
2 Rita Math 95
3 Sam Math 84
4 Jack Math 72
5 Pom Math 99

This table has five rows and four columns

We have already discussed column names, column types (datatypes) and their length. We have created similar table previously using PHPMYADMIN interface.

Lets create the same table using the SQL Create Table Syntax

This time we are going to use different table name. Lets say, our table name is “new_student_marks”.

NOTE: Table name can not contain spaces or special characters. If you are intended to use multiple words than separate them with underscore.

We have four columns: roll_no, student_name, subject, marks

We have already discussed in one of the previous chapter:

We have already discussed in one of the previous chapter:

To create the table, we are ready with table name, column names, their datatype and length.

Lets create the table. 

In the SQL Command box, write the following code:

Create table new_student_marks(

roll_no int(3),

student_name varchar(50),

subject varchar(4),

marks int(3)

);

 

Note: We could have written the syntax in a single line, but for better understanding I have written the code as shown above. But for ease purpose, we can also follow the above approach.

Note: Every length value is kept inside brackets

Note: After each length value, there is a comma except the last length value. We separate more than one columns with comma. If we need to create a table with one column only then no comma is required. 

Note: The column values are kept inside the brackets ‘()’ and ends with semi colon. (This ‘()’ brackets are also known as ‘Parenthesis’).

Create table using SQL syntax

Once you write the create table syntax (command), then click on go. Then the status message will appear as below.

Create table using SQL syntax commands

It says ‘MySQL returned an empty result set..’. It means, the table is created successfully but it is empty.