Inserting and Retriving Data from Database

Hello, and welcome to the blog. In this blog we are going to talk about "Inserting and retrieving data from database" in database.

We have created a simple Employee table in our previous blog ( https://learntocodewithmustafapinjari.blogspot.com/2020/07/basics-of-sql.html  ). Now its time to add some data in that table and then we will see how we can retrieve that data.

In this blog we will talk about :

1. Inserting Data
2. Retrieving Data
3. Operators in SQL

1) Inserting Data :

There are 2 syntax available in SQL for inserting data. Both are simple. Let's see them one by one

Syntax 1: insert into table_name values (value_1, value_2,........,value_n)

Example: insert into Employee values(1, 'John', 'Doe', 'Sales',50000,'Sales Manager')

1. In this syntax you have to make sure that you maintain the sequence of  values as per the sequence of column in the table.
2. If you  miss a value or you add more values than number of column then you will get an error.
3. If you miss-match column values then you will get error only if there is a conflict in data types i.e you can't enter a string value (word, sentence,etc.) in a column with integer data type.
4. If you miss-match column values you may not get error if both the column have same data type but you will definitely end up having wrong data in your tables.

Now let's see another syntax

Syntax 2: insert into table_name (column 1, column 2, ... , column n) values (value 1, value 2,... , value n)

Example: insert into Employee (Id, FirstName, LastName, Department, Salary, Designation) values(1, 'John', 'Doe', 'Sales', 50000,'Sales Manager') 

1. In this 2nd syntax you can mention only those column which are required and follow the sequence as you have mentioned in the query.
2. For example, as we have specify "Id" first in list of column so we are adding value for Id first.

Both syntax works fine  You can use any syntax of your choice.

Note: When you specify value for varchar data type ensure the value is enclosed in single quotes

Now I am going to insert some random records in this table so that we can retrieve those records and learn how to retrieve data. 

2) Retrieving Data :

We use "SELECT" command for retrieving data. Select comes under DQL query type i.e. Data Query Language. Let's see syntax and then we will discuss further.

Syntax : select column 1, column 2, ... , column n from table_name

Example : select FirstName, LastName from Employee

1. Data retrieving is the process which happens 90% of time in a database that is why "SELECT" operation is performed for n number of time.
2. Let me explain the syntax so first you write "SELECT" which is keyword then you specify columns which you want to see or display then you specify "FROM" which is again a keyword then you specify table name.
3. If you want to see all the columns then you can use '*' symbol instead of specifying all column names. This will show you all data from all columns
4. For example: select * from Employee
5. There are multiple combinations in which "SELECT" is used.

Now, let's assume you don't want to view all data, you want to only view data in which the department is sales. In this scenario we have a additional condition. So, in this scenario we have something called as "WHERE" clause. It allows you to add additional conditions to get more specific data.

Now, let's see syntax and example:

Syntax: select column 1, column 2, ... , column 3 from table_name where condition

Example : select FirstName, LastName from Employee where Department = 'Sales'

3) Different Operators that can be used with "where" clause :

We are going to talk about 3 operators 'AND', 'OR' and 'NOT'. These are mainly used for enhancing effectiveness of where clause. With help of these operators you can add more conditions to a where clause, add either-or condition and you can also choose to not view a particular type of record.

Now, let's first see their use and a small description about them then we will see an example.

1)AND operator:

1. AND operator is used to add more and more conditions in'where' clause.
2. When AND operator is used all conditions specified in where clause should be true i.e. a record must satisfy all the conditions.

2) OR operator:

1. OR operator is used when you want to add either-or conditions.
2. OR operator ensures that out of all conditions specified in where clause at least one condition is satisfied or not.

3) NOT operator:

1. NOT operator is used when you want to show records where certain condition is not true.

Now, lets see examples:

1) AND operator: select * from Employee where Department='Sales' AND salary='50000'

2) OR operator: select * from Employee where Department='Sales' OR Department='Marketing'

3) NOT operator: select * from Employee where NOT Department='Sales'

Congratulations on inserting and retrieving data. See you  in some another blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day".