Practical SQL Guide : The Art Of Table Creation (Part - I)

 Before I start, I must confess that this is a very unusual sequence of speaking/teaching about SQL. In general case SQL teaching starts with SELECT statement and then goes on to other DQL statements and then it comes to Table creation. Yes, but what I intend to do in future is based on learning about how to create tables and manipulate them first, hence the unusual sequence. No worries, this ain't that difficult either. May be some topics will seem peculiar at first glance but don't worry that much, I will make them as easy as I could. And for the ease of understanding I will break the whole idea into 2-3 blogs so you don't have to read an epic in one go. Nevertheless, table creation is an art and a very important concept in DBMS/SQL so must focus well on the same.

A Recap...

Remember, I spoke about tables in blog number 1 of this series, let me recap it for you.

Table is made up of rows and columns. A column represents a specific part or attribute of the entity. And a row represents a entity. For example look at this image of a table below : 

Now this specific table captures the three aspects(or attributes) of a student which are translated into as columns, namely Student's roll number , its Name and its CGPA. There could be many other things that a entity 'student' can hold, but for this table it is just this three aspect. And the rows in themselves are students or entities in the table. 

Primary Key

In the image above you can see there's something written as Primary Key. Its time that we talk about it. One of the essential component of table creation. Primary key is a single column or a group of columns that uniquely defines a record. To complex to understand? Let's understand it with a hypothetical example. Suppose that you're an accountant who works with God. You have a lot of data in a table regarding humans, and the table comprises of different attributes, like their name, place of birth, etc., ,and also their DNA and fingerprints. Now if you want retrieve data for a specific human how will you do it? You need something unique that defines that human isn't? So what's unique? Name? DOB? Well as much I know, it would be DNA as every human's DNA is different and unique. Also fingerprints. They are unique to each human beings. You understand the context now? Primary key is like DNA of the table. Unique to each record. It could be a single attribute or a collection of attribute. 

In the table above, Roll No acts as a primary key and it uniquely defines the entity. Now can you find a human without a DNA? No right? In the similar way, Primary Key(PK) cannot be empty for a record. It cannot be null. There has to be a unique PK for a record in the DB.

Table Creation

I think this much information is enough to get started with table creation. 

Remember how we declared a variable in SQL? We will do something similar in table creation. The code would look like

CREATE TABLE <table_name>(

Column_name data_type constraints

.

.

.

.

.

)

Let me break it down for you, CREATE TABLE is a SQL command that is used to create table (bruh). After giving a space you give the table name you want to create. Remember that Table name must be unique, otherwise SQL will throw a error at your face. Then you start parenthesis '(', and inside it you write a column name along with its datatype just like u did when u created variables. The only extra thing that you will do here is that you will give some constraints to the column if you want to. Constraints are specifications or restrictions for that specific column. PRIMARY KEY constraint says that column is unique and not null and act as primary key for the whole table. Constraints can be either declared directly at the column as I have mentioned in the format or it can be mentioned at the end of the statement. The second way of declaring constraints is usually done when the constraints are complex in nature.

Okay enough now, let's create a table. I will create a table where 'Employee'. It will have a Employee Id which will act as primary key, its name, age and salary. You have to type in SQL as follow : 

Let me break the whole query for you. I will just explain the columns defined here. The first column, 'EmpId' is of INTEGER type and it's constraint is that it is a primary key column. When you define a column as PK it will by default take it as NOT NULL column, i.e., it will never be null / empty. 'Name' will be a string, of the maximum length of 50 characters and it cannot be null because of the constraint NOT NULL. Age column is of type INTEGER(obviously) and it can be null. Salary can be decimal value hence its data type and it cannot be null.

Once you run this query in the playground, you will find a new table generated.

Look how the table is created. And you can also see its column description and in the Index section, you can see that we have a Primary Key for Employee table.

In the next blog (Part 2) I will tell how to alter the table you have created, how to insert values into it and also delete a table!

SEE YA!!!