SQL Table

• SQL Table is a collection of data which is organized in terms of rows and columns. In DBMS, the table is known as relation and row as a tuple.
• Table is a simple form of data storage. A table is also considered as a convenient representation of relations.

Let's see an example of the EMPLOYEE table:

EMP_ID	EMP_NAME	CITY	PHONE_NO
1	Kristen	Washington	7289201223
2	Anna	Franklin	9378282882
3	Jackson	Bristol	9264783838
4	Kellan	California	7254728346
5	Ashley	Hawaii	9638482678

In the above table, "EMPLOYEE" is the table name, "EMP_ID", "EMP_NAME", "CITY", "PHONE_NO" are the column names. The combination of data of multiple columns forms a row, e.g., 1, "Kristen", "Washington" and 7289201223 are the data of one row.

Operation on Table

1. Create table
2. Drop table
3. Delete table
4. Rename table

SQL Create Table

SQL create table is used to create a table in the database. To define the table, you should define the name of the table and also define its columns and column's data type.

Syntax

create table "table_name"    

1. ("column1" "data type",    
2. "column2" "data type",    
3. "column3" "data type",    
4. ...    
5. "columnN" "data type");   

Example

1. SQL> CREATE TABLE EMPLOYEE (    
2. EMP_ID INT                           NOT NULL,    
3. EMP_NAME VARCHAR (25) NOT NULL,    
4. PHONE_NO INT                         NOT NULL,    
5. ADDRESS CHAR (30),    
6. PRIMARY KEY (ID)    
7. );    

If you create the table successfully, you can verify the table by looking at the message by the SQL server. Else you can use DESC command as follows:

SQL> DESC EMPLOYEE;

Field	Type	Null	Key	Default	Extra
EMP_ID	int(11)	NO	PRI	NULL
EMP_NAME	varchar(25)	NO		NULL
PHONE_NO	NO	int(11)		NULL
ADDRESS	YES			NULL	char(30)

• 4 rows in set (0.35 sec)

Now you have an EMPLOYEE table in the database, and you can use the stored information related to the employees.

---

Drop table

A SQL drop table is used to delete a table definition and all the data from a table. When this command is executed, all the information available in the table is lost forever, so you have to very careful while using this command.

Syntax

1. DROP TABLE "table_name";    

Firstly, you need to verify the EMPLOYEE table using the following command:

1. SQL> DESC EMPLOYEE;    

Field	Type	Null	Key	Default	Extra
EMP_ID	int(11)	NO	PRI	NULL
EMP_NAME	varchar(25)	NO		NULL
PHONE_NO	NO	int(11)		NULL
ADDRESS	YES			NULL	char(30)

• 4 rows in set (0.35 sec)

This table shows that EMPLOYEE table is available in the database, so we can drop it as follows:

1. SQL>DROP TABLE EMPLOYEE;    

Now, we can check whether the table exists or not using the following command:

1. Query OK, 0 rows affected (0.01 sec)    

As this shows that the table is dropped, so it doesn't display it.

---

SQL DELETE table

In SQL, DELETE statement is used to delete rows from a table. We can use WHERE condition to delete a specific row from a table. If you want to delete all the records from the table, then you don't need to use the WHERE clause.

Syntax

1. DELETE FROM table_name WHERE condition;    

Example

Suppose, the EMPLOYEE table having the following records:

EMP_ID	EMP_NAME	CITY	PHONE_NO	SALARY
1	Kristen	Chicago	9737287378	150000
2	Russell	Austin	9262738271	200000
3	Denzel	Boston	7353662627	100000
4	Angelina	Denver	9232673822	600000
5	Robert	Washington	9367238263	350000
6	Christian	Los angels	7253847382	260000

The following query will DELETE an employee whose ID is 2.

1. SQL> DELETE FROM EMPLOYEE  
2. WHERE EMP_ID = 3;  

Now, the EMPLOYEE table would have the following records.

EMP_ID	EMP_NAME	CITY	PHONE_NO	SALARY
1	Kristen	Chicago	9737287378	150000
2	Russell	Austin	9262738271	200000
4	Angelina	Denver	9232673822	600000
5	Robert	Washington	9367238263	350000
6	Christian	Los angels	7253847382	260000

If you don't specify the WHERE condition, it will remove all the rows from the table.

1. DELETE FROM EMPLOYEE;    

Now, the EMPLOYEE table would not have any records.