>> CREATE Table , ADD data into the table AND CREATE table from existing table

CREATE  NEW Table:

Syntax:

CREATE TABLE { table name }

(

    column1 datatype,

    column2 datatype,

    column3 datatype,

   ....

) ;

For example , let's create a table  TEST:

CREATE TABLE TEST  

(

Name  varchar(20),

Dept  varchar(20),

Salary  int  

) ;

When run, it will show : Table  TEST  created.

Let's see if the table has any data.

SELECT  *  FROM  TEST  ;

Since we have not added data into the table, it will not show any data , it will show the column headers only as below :

NAME

DEPT

SALARY

Now, Let's add data in the table.

ADD DATA INTO THE TABLE

Syntax :
INSERT INTO { Table Name } (column1, column2, column3,...)
 VALUES (value1, value2, value3,...) ;

For example:
INSERT INTO  TEST ( Name , Dept , Salary )
VALUES  ( 'David' , 'Marketing' , 2000);

After running above query , it will display : 1 row inserted.

Let's see the content of the table:

SELECT  *  FROM  TEST  ; -- This will return below table :

NAME	DEPT	SALARY
David	Marketing	2000

Let's add one more row (called record) of data.
INSERT INTO  TEST ( Name , Dept , Salary )
VALUES  ( 'Smith' , 'HR' , 3000);

After running this query , it will display :  1 row inserted.

Let's see the content of the table now:

SELECT  *  FROM  TEST ; -- This will return below table :

NAME	DEPT	SALARY
David	Marketing	2000
Smith	HR	3000

This way , you can add any number of records/data.

If you do not provide data for any column while adding data, then it stores  "null" as the cell value. Note: "null" is not zero.

For example :

INSERT INTO  TEST (  Name , Dept  )
VALUES  ( 'Mina' , 'Staffing'  );

After running this query , it will display :  1 row inserted.

Let's see the content of the table now:

SELECT  *  FROM  TEST ; -- This will return below table :

NAME	DEPT	SALARY
David	Marketing	2000
Smith	HR	3000
Mina	Staffing	( null )

CREATE A NEW TABLE FROM AN EXISTING TABLE

Suppose we have an existing table and we want to create another table with exactly same data from the existing table. ( create duplicate table):

Let's take an existing table DEPTARTMENT :

DEPT_NUM	DEPT_NAME	LOCATION
20	TECHNICAL	NEW YORK
10	RESEARCH	BALTIMORE
30	MARKETING	CHICAGO
40	OPERATIONS	DALLAS

Let's create a Table NEW_DEPTARTMENT with all the data from the table DEPTARTMENT.

Syntax :

CREATE TABLE  { new table name }  AS  SELECT  *  FROM  {  old table name  };

Query :

CREATE TABLE  NEW_DEPTARTMENT AS  SELECT  *  FROM  DEPTARTMENT;

After running this query, it will display : Table  NEW_DEPTARTMENT created.

Let's find out the data in the new table NEW_DEPTARTMENT :

SELECT  *  FROM  NEW_DEPTARTMENT ;

DEPT_NUM	DEPT_NAME	LOCATION
10	RESEARCH	BALTIMORE
20	TECHNICAL	NEW YORK
30	MARKETING	CHICAGO
40	OPERATIONS	DALLAS

CREATE TABLE FROM AN EXISTING TABLE TAKING THE  NECESSARY  DATA ONLY FROM THE EXISTING TABLE.

Suppose we need to create a new Table NEW_DEPTARTMENT1 from existing table DEPTARTMENT, the new table will have  LOCATION  information for  DEPTNO 10 and DEPTNO 20 only .

Syntax :
CREATE TABLE { NEW TABLE NAME } AS SELECT { required columns } FROM { OLD TABLE NAME }
where { condition } ;

Query :

CREATE TABLE  NEW_DEPTARTMENT1  AS  SELECT  DEPT_NUM, LOCATION  FROM  DEPTARTMENT

where DEPTARTMENT.DEPT_NUM IN (10,20) ; (Note: if you are filtering with more than one values than you use "IN", if there were one value only, you could use "=")

After execution , it will show : Table NEW_DEPTARTMENT1  created.

Let's check the data in the Table NEW_DEPTARTMENT1  .

SELECT  *  FROM  NEW_DEPTARTMENT1  ; -- This query will return below data :

DEPT_NUM	LOCATION
10	BALTIMORE
20	NEW YORK

Using Constraint while creating Table:
If you are very fresh in SQL, ignore below section, do not get confused.

I am using a constraint NOT NULL while creating a table:
 

CREATE TABLE TEST  

(

Name  varchar(20), 
EMP_No int NOT NULL,

Dept  varchar(20),

Salary  int  

) ;In above example, the column EMP_No can not have null value.

NEXT

Go To Main SQL Page



.