Practical SQL Guide : The Art of Table Creation (Part - II)

 In the previous blog, I spoke about how to create a table in SQL.  We also created one. But what will you do with empty table? You need to insert data into it too! So in this blog I intend to share on how to insert data into table, how to update them or how to delete them.

INSERT

All of the operation that I mentioned above are easy operation, you just have be cautious a little if you don't want any error. Insert operation is easiest of all. All you have to take care is of the datatypes and the values. The command looks like the following : 

INSERT INTO <table_name>(col1, col2, col3...coln)

VALUES (v1, v2, v2....vn)

All you have to mention is the table name along with the column name that you want to insert into and their respective values. Remember, the datatypes must match. For example, if v1 is the value to be inserted in col1, then v1's datatype must match the data type of col1. Now notice here that I have said to mention the column names you want to insert data into. So are column name skippable? Well yes, somewhat. If column name is nullable then you can skip the column. Let's do some coding so that we can understand it well. Write the following code in your playground : 

There are two parts of the total query, one is to insert data (red rectangle) and another one is to see the data. Let's focus on the first part. You can see that I have mentioned the table name as Employee(table that we created in the last blog) and its column in which i want to insert the data. Then I have mentioned the VALUES to be inserted in a tuple. Now its important to note that, the order you have mentioned the columns is crucial. Because the same order will be followed while SQL maps the data you have provided as value with the column name. So '1' will be mapped to EmpId, 'Rajat' will be mapped to Name, 23 will be mapped to Age and finally 40000 will be mapped to Salary. And it will be inserted to the table. Easy right? Now let's do another example, where will leave out Age(remember that age is a nullable column as we created the table) : 

You will be able to insert the data into the table. Easy! But notice the NULL in the place of Age for Rahul, you didn't provide anything for it so it is null. But do you always have to place the data one by one, can't we just insert data into the table in a bulk? Well yes we can. Here's you can do it, just repeat the tuple after VALUES with different values : 

Pretty neat right? I must mention that there are many edge cases where you will fall down, and I won't cover them right now. It's not like that I won't cover them, I will but when its time comes. What's the hurry?

UPDATE 

Now what if Rahul's salary has increased? Let's say 45000, in that case you need to update the record, isn't it? You will. And it's very easy, trust me. All there is to do is to use UPDATE command with SET command and it give it a condition on which the update will happen. Okay let's break this down, I want to increase Rahul's salary, so first I need to find Rahul's record in the database. You can do it by Rahul's name, that will work, but what if we had another Rahul in the database? In that case, both Rahul's salary will increase. We need to find something unique about Rahul's record.....what is that uniqueness? If you said EmpId, then you are absolutely correct! We will filter out EmpId to find Rahul's record and UPDATE the record by SETting its salary column to 45000. Let's do it. Enter the following code : 

Perfect? Well if you are confused about WHERE clause then don't worry, it's just a filtering command. And see Rahul's salary has increased! Congratulations to him. BUT remember one thing that while Updating database, be very sure about the filtering condition you are giving, if its wrong then other data data will get updated unnecessarily. That's the basic of UPDATE Command, and I don't know much of its use anyway :) At the very best you will use it in Store Procedures (SQL stored Functions) to manipulate data inside the function.

DELETE

What if a employee leaves the organization, you must delete it's data. And it is simple, just keep in mind that as simple it sounds, it is very risky as well, only run this command when you are very sure what you are doing. DELETE command also runs on WHERE clause, so be very sure. Suppose Rohit's record need to be deleted, so here how it will be written : 

See how Rohit's record has been deleted from the database. Pretty easy. Except for INSERT, other two doesn't have much of a direct use. 

This is it for this blog. Try experimenting with the table, with whatever you have learnt till now. As we gradually progress into the realms of SQL, I will share more and more about the concepts. Till then...