SQL-TABLE

What is table?

·         The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

·         Remember, a table is the most common and simplest form of data storage in a relational database. Following is the example of a CUSTOMERS table:

ID	NAME	AGE	ADDRERSS	SALARY
1	ANIL	34	Bangalore	34000.00
2	MANJU	45	Mysore	22000.00
3	NAVEEN	23	Bangalore	32000.00

What is field?

·         Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

·         A field is a column in a table that is designed to maintain specific information about every record in the table.

What is record or row?

A record, also called a row of data, is each individual entry that exists in a table. For example there are 3 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table:

3

NAVEEN

23

Bangalore

32000.00

A record is a horizontal entity in a table.

What is column?

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

For example, a column in the CUSTOMERS table is NAME, which represents location description and would consist of the following:

NAME

ANIL

MANJU

NAVEEN

What is NULL value?

A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

SQL CREATE TABLE:

SQL CREATE TABLE statement is used to create table in a database.

If you want to create a table, you should name the table and define its column and each column's data type.

Let's see the simple syntax to create the table.

create table "tablename"  

("column1" "data type",  

"column2" "data type",  

"column3" "data type",  

...  

"columnN" "data type");  

Let us take an example to create a STUDENTS table with ID as primary key and NOT NULL are the constraint showing that these fields cannot be NULL while creating records in the table.

 CREATE TABLE STUDENTS (  

ID INT                           NOT NULL,  

NAME VARCHAR (20) NOT NULL,  

AGE INT                         NOT NULL,  

ADDRESS CHAR (25),  

PRIMARY KEY (ID)  

);  

SQL DROP TABLE:

A SQL DROP TABLE statement is used to delete a table definition and all data from a table.

This is very important to know that once a table is deleted all the information available in the table is lost forever, so we have to be very careful when using this command.

syntax :

DROP TABLE "table_name";  

SQL DELETE TABLE:

The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.

DELETE FROM table_name [WHERE condition];  

But if you do not specify the WHERE condition it will remove all the rows from the table.

DELETE FROM table_name;  

There are some more terms similar to DELETE statement like as DROP statement and TRUNCATE statement but they are not exactly same there are some differences between them.

Difference between DELETE and TRUNCATE statements:

There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified.

But it does not free the space containing by the table.

The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space.

Difference b/w DROP and TRUNCATE statements:

When you use the drop statement it deletes the table's row together with the table's definition so all the relationships of that table with other tables will no longer be valid.

When you drop a table:

• Table structure will be dropped
• Relationship will be dropped
• Integrity constraints will be dropped
• Access privileges will also be dropped

On the other hand when we TRUNCATE a table, the table structure remains the same, so you will not face any of the above problems.

SQL RENAME TABLE:

SQL RENAME TABLE syntax is used to change the name of a table. Sometimes, we choose non-meaningful name for the table. So it is required to be changed.

Let's see the syntax to rename a table from the database.

ALTER TABLE table_name   

RENAME TO new_table_name;  

Optionally, you can write following command to rename the table.

RENAME old_table _name To new_table_name; 

SQL TRUNCATE TABLE:

A truncate SQL statement is used to remove all rows (complete data) from a table. It is similar to the DELETE statement with no WHERE clause.

TRUNCATE TABLE Vs DELETE TABLE:

Truncate table is faster and uses lesser resources than DELETE TABLE command.

TRUNCATE TABLE Vs DROP TABLE:

Drop table command can also be used to delete complete table but it deletes table structure too. TRUNCATE TABLE doesn't delete the structure of the table.

Let's see the syntax to truncate the table from the database.

TRUNCATE TABLE table_name;  

For example, you can write following command to truncate the data of employee table

TRUNCATE TABLE Employee; 

SQL COPY TABLE:

If you want to copy a SQL table into another table in the same SQL server database, it is possible by using the select statement.

The syntax of copying table from one to another is given below:

SELECT * INTO <destination_table> FROM <source_table>  

For example, you can write following command to copy the records of hr_employee table into employee table.

SELECT * INTO admin_employee FROM hr_employee;

  

SQL TEMP TABLE:

The concept of temporary table is introduced by SQL server. It helps developers in many ways:

Temporary tables can be created at run-time and can do all kinds of operations that a normal table can do. These temporary tables are created inside tempdb database.

There are two types of temp tables based on the behavior and scope.

1. Local Temp Variable
2. Global Temp Variable

Local Temp Variable:

Local temp tables are only available at current connection time. It is automatically deleted when user disconnects from instances. It is started with hash (#) sign.

CREATE TABLE #local temp table (  

User id int,  

Username varchar (50),  

User address varchar (150)  

)  

Global Temp Variable:

Global temp tables name starts with double hash (##). Once this table is created, it is like a permanent table. It is always ready for all users and not deleted until the total connection is withdrawn.

CREATE TABLE ##new global temp table (  

User id int,  

User name varchar (50),  

User address varchar (150)  

)  

SQL ALTER TABLE:

The ALTER TABLE statement is used to add, modify or delete columns in an existing table. It is also used to rename a table.

You can also use SQL ALTER TABLE command to add and drop various constraints on an existing table.

SQL ALTER TABLE Add Column:

If you want to add columns in SQL table, the SQL alter table syntax is given below:

ALTER TABLE table_name ADD column_name column-definition;  

If you want to add multiple columns in table, the SQL table will be

ALTER TABLE table_name   

ADD (column_1 column-definition,  

           column_2 column-definition,  

           .....  

           column_n column-definition);  

SQL ALTER TABLE Modify Column:

If you want to modify an existing column in SQL table, syntax is given below:

ALTER TABLE table_name MODIFY column_name column_type;  

If you want to modify multiple columns in table, the SQL table will be

ALTER TABLE table_name   

MODIFY (column_1 column_type,  

             column_2 column_type,  

              .....  

             column_n column_type);  

SQL ALTER TABLE DROP Column:

The syntax of alter table drop column is given below:

ALTER TABLE table_name DROP COLUMN column_name;  

SQL ALTER TABLE RENAME Column:

The syntax of alter table rename column is given below:

ALTER TABLE table_name  

RENAME COLUMN old_name to new_name;