14. SQL Commands Part 6

SQL Commands Part 6- DDL, DML & TCL.

Create a table.

code: Create table sha_emp ( empno number(3), ename varchar(10), sal number(8,2) );

[Note: In table creation we have to specify what kind of data we are inserting in the table. It means we have to specify which column holds which type of data.]

All Datatypes in SQL

Ans: There are different types of data in SQL:-

1. Number Type- Where we can store numeric value like 0-9 and decimal.
2. Character Type- Like A-z, a-z and special character like #, @,!, etc. and numbers as well 0-9.
3. Date Type- In this data type we can insert date, like in "dd-mm-yyyy" format.
4. Raw Type- Where we can store binary data like 0 and 1.

What does "empno number(3)" mean?

Ans: Number is the data type of the column named empno, while '(3)' represents the number of digits we can store in empno. Here (3) means that we can store any number from 0 to 999 can be stored in empno.

What does 'ename char(10)' mean?

Ans: It means the column ename accepts character type value. It accepts value that is of 10bytes. Using this datatype provides each character type value 10 bytes of memory space.

For example the name 'RAJ' takes 3 bytes the rest 7 bytes remain unused.

Why use varchar(10) instead of char(10)?

Ans: Varchar(10) gives us a maximum of 10 bytes space. For example RAJ name has 3 characters so it takes 3 bytes of memory space, the rest 7 bytes of unused space is freed up. So only 3 bytes space is used.

Why use varchar2(10) instead of varchar(10)?

Ans: Varchar has only 2000 bytes of total memory space but Varchar2 can store up to 4000 bytes of recored, value o characters for every individual record. Now a days we use Varchar2.

What does number(8,2) mean?

Ans: It means the number will have 8 digits in which 2 digits will be decimal place.

How to rename a column in table in SQL?

code: alter table sha_emp rename col sal to salary;

How to add another column in table.

code: alter table sha_emp add deptno number(3);

How to modify a data type of a column