Understanding different data types in SQL and Creating table

-
Hello, and welcome to the blog. In this blog we are going to talk about different data types available in the SQL and we are going to create our first table in which we are going to store data.

In our previous blog( https://learntocodewithmustafapinjari.blogspot.com/2020/07/understanding-queries-and-creating.html ) we learn how to create database and we created a database named as "EmployeeManagementSystem". Now its time to create some tables in our database so that we can store our data. So in this blog we are going talk about:
  1. Understanding data types
  2. Different data types in SQL
  3. Creating a database table
  4. Adding Column to existing table
Lets' start with :

1) Understanding Data Type:

  1. It is very important to understand "Data types" before creating a database table as your table is going to represent a data for a specific Entity.
  2. "Entity is thing with distinct and independent existence" like a Person, Car, Shop, Institute, Animal etc.
  3. Every entity do have some properties. For example: A person has is First Name, Last Name, Age, Date Of Birth, Gender, City, Country, Phone Number, etc. Now each of theses property has value. Some values are represented as words , some are represented as numbers, some are represented as date, some are represented as decimal value.
  4. So "Data Types" defines what type of value a particular column will hold in a database table. There is a particular data type for storing words, a particular data type for storing numbers, a particular data types for storing date, etc.

2) Different data types in SQL

We are not going to discuss all data types and get bored. We are going to discuss some basic data types which are widely used and other data types we will see as we progress through this tutorial.

Note: There are some data type which are specific to DBMS software you are using.

1. varchar

  1. This data type is used to store words, sentences, paragraph or any collection of letters.
  2. Programmers say it like this "varchar is used to store String value".
  3. In varchar data type you can specify number of characters allowed in a column. 
  4. For example: varchar(50) this will allow you to store words or sentences with maximum 50 characters. 

2. int

  1. This data type is used to store numeric data.
  2. Numeric data which is stored in this data type don't have decimal value.

3. decimal

  1. This data type is used to store numeric data with decimal values.
  2. You can specify number of decimal precision in this data type.
  3. For example: decimal(16,2) here we can have value with up to 16 digit number and 2 decimal precision. Let's see another example decimal(14,4) we can have value with  14 digit number and 4 decimal precision.

4. datetime

  1. This data type is used to store date and time value.
  2. When you want to store date and time, for example: date of purchase and time of purchase, etc. this data type is used.

5. date

  1. This data type is used to store only date.
  2. When you use datetime data type by default a time is also stored in the database if you don't want time to be saved you use this data type.
Now, we know some of the basic data types so let's go and create a database table

3) Creating Database Table

Creating a database table is super easy thing. You have do just open your DBMS software and press "ctrl+n" or click on "New Query". While running queries you have to make sure that correct database is selected.

You can check selected database in "SQL Server Management Studio (SSMS)" where I have marked red. You have to make sure there "EmployeeManagementSystem" is selected otherwise you will end up running query in "master" database. Now, just run the query written in example. I have marked predefined keywords in blue.

Syntax :

create table table_name
(
    column_name datatype,
    column_name datatype,
    .
    .
    column_name datatype
)

Example :

create table Employee
(
    Id int,
    FirstName varchar(50),
    LastName varchar(50),
    Department varchar(50),
    Salary decimal(18,2)
)

4) Adding column in Database Table

As a human we have tendency to forget things. What if you forget to add a column to your database table? Don't worry we have a SQL command to do that.

Syntax :

alter table table_name
add column_name datatype 

Note: This syntax is for "Microsoft SQL Server"

Example:

alter table Employee
add Designation varchar(50)

Great we have learned to add a database table and a column to existing table. Upcoming we will see adding and retrieving data from database.
See you  in some another blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day".

Comments

Post a Comment

Popular posts from this blog

SQL Joins | Inner Join | Left Join | Right Join | Full Join | Self Join

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about " Joins ". In the  previous blog  we have seen most important topic of Primary Key, Foreign Key and now its time to join some of the tables. So, In this blog we are going to talk about What is join and its purpose Type Of Joins Inner Join Left Join Right Join Full Join Self Join Let's  start with 1) What is join and its purpose There is a clause in SQL called Join. And its purpose is to combine rows from two or more tables which are connected with each other by a common column. When I say connected with each other by a common column I am talking about Foreign Key which we have covered in previous blog. Although if you haven't enforced foreign key for this blog but we do have a common column then also you can perform jo...
Read more

Update, Delete and Truncate Commands

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "Updating and deleting data". We learn about "Inserting and Retrieving Data" in  this blog . Now its time to learn updating and deleting our mistakes. Yes with the help of the UPDATE and DELETE  statements  you can actually update and delete wrong entries in your database respectively. So in this blog we are going too talk about  UPDATE statement DELETE  statement TRUNCATE command Let's start with 1) UPDATE statement : UPDATE statement is used to update a specific record or a bunch of records which exists in your database table. If you made a mistake while inserting data in your database then UPDATE statement is the loophole through which you can correct it. Let's see a example: If you can see in our table I have made a mistake that SALARY  of "Sales Per...
Read more

More clauses in SQL

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "More clauses in SQL". We learn about "WHERE" clause in this blog . Now its time to learn more clause which are going to help us to be more specific about retrieving data from Database and eventually its gonna make us more and more efficient in handling data in Database. So, In this blog we are going to talk about GROUP BY clause HAVING clause ORDER BY clause Now, let's talk about 1) GROUP BY clause This clause is often used with Aggregate Functions (which we have learnt in previous blog ) so that we can group our result with a particular column. I know it sound little confusing but we will solve this confusion with the help of example. Let's consider a scenario where I told you to count number of people working in each department with our existing Employee table. So then you might write a query like this: select count(*) from Employee where Department='IT' Then you will keep rep...
Read more