What is SQL Table
Table is a group of data, arranged in terms of rows and columns. In DBMS , table is named relation and row as tuple.
Note: A table has a specified number of columns, but can have any number of rows.
Table is the simple form of data storage. A table is also considered as a convenient representation of relations.
Let’s see an example of an employee table:
| STUDENT | ||
| EMP_NAME | ADDRESS | SALARY |
| Ankit | pune | 50000 |
| Ram | Mumbai | 19000 |
| Minakshi | Nashik | 30000 |
In the above table, “STUDENT” is the table name, “EMP_NAME”, “ADDRESS” and “SALARY” are the column names. The combination of data of multiple columns forms a row e.g. “Ankit”, “Lucknow” and 15000 are the data of one row.
SQL TABLE Variable
To create, modify, rename, copy and delete tables the SQL Table variable is used. Microsoft was introduced Table variable.
It is a variable where we temporary store records and results. This is same like temp table but in the case of temp table we need to explicitly drop it.
To store a set of records table variables are used. declaration syntax generally looks like CREATE TABLE syntax.
create table "tablename" ("column1" "data type", "column2" "data type",.……"columnN" "data type"); SQL CREATE TABLE
To create table in a database we used SQL CREATE TABLE statement.
If you want to create a table, you should name the table and define its column and each column’s data type.
syntax to create the table.
create table “tablename” (“column1” “data type”, “column2” “data type”,”column3″ “data type”,.……………………….
“columnN” “data type”); The data type of the columns may change from one database to another. For example, INT is supported in MySQL database for integer value whereas NUMBER is supported in Oracle.
Let us take an example to create a STUDENT 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.
SQL> CREATE TABLE STUDENT (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
PRIMARY KEY (ID)
); You can verify it, if you have created the table successfully by looking at the message displayed by the SQL Server, else you can use DESC command as follows:
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
| ID | Int(11) | NO | PRI | ||
| NAME | Varchar(20) | NO | |||
| AGE | Int(11) | NO | |||
| ADDRESS | Varchar(25) | YES | NULL |
DESC STUDENTS;Now STUDENTS table available in your database and you can use to store required information related to students.
SQL Primary Key :
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key
SQL Primary Key with CREATE TABLE Statement
The following SQL creates a PRIMARY KEY on the “ID” column when the “Employee” table is created:
CREATE TABLE Employee(
EmployeeID NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
City varchar(255),
PRIMARY KEY (EmployeeID)
); Or
CREATE TABLE Employee(
EmployeeID NOT NULL PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
City varchar(255)
); SQL DROP TABLE
A SQL DROP TABLE statement is used to delete all data from a table.
This is very important to know the DROP statement cannot be rollback.
Syntax for the DROP table Statement is:
DROP TABLE "table_name"; Let us take an example:
First we verify STUDENTS table and then we would delete it from the database.
DESC STUDENTS;| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
| ID | Int(11) | NO | PRI | ||
| NAME | Varchar(20) | NO | |||
| AGE | Int(11) | NO | |||
| ADDRESS | Varchar(25) | YES | NULL |
This shows that STUDENTS table is available in the database, so we can drop it as follows:
4 rows in set (0.00 sec)
The following SQL statement drops the existing table “STUDENTS”:
DROP TABLE STUDENTS; Now, use the following command to check whether table exists or not.
DESC STUDENTS; Query OK, 0 rows affected (0.01 sec)
As you can see, table is dropped so it doesn’t display it.
SQL DELETE TABLE
The DELETE statement is used to delete rows from a table.
DELETE FROM table_name; 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.
SQL RENAME TABLE
Sometimes, we choose non-meaningful name for the table. So it is required to be changed.Than SQL RENAME TABLE syntax is used to change the name of a table.
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; Let us take an example of a table named “ARTISTS”, now due to some reason we want to change it into table name “STUDENTS”.
You should use any one of the following syntax to RENAME the table name:
ALTER TABLE ARTISTS
RENAME TO STUDENTS; Or
RENAME ARTISTS TO STUDENTS ; After that the table “ARTISTS” will be changed into table name “STUDENTS”
SQL TRUNCATE TABLE
A truncate SQL statement is used to remove all rows from a table.
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; Note: Once you truncate a table you cannot use a flashback table statement to retrieve the content of the table.
SQL COPY TABLE
SQL copy table statment used to copy a SQL table into another table in the same SQL server database.
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; Note: SELECT INTO is totally different from INSERT INTO statement.
SQL ALTER TABLE
The ALTER TABLE statement is used to add, modify or delete columns in an existing table.
SQL ALTER TABLE command used to add and drop various constraints on an existing table.
It is also used to rename a 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; 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; 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;