Lesson : 9 - TABLES

How to create Tables in Oracle database ?

Before tables can be used,  they have to be created in the database.  Here is the simple syntax for creating a table,

CREATE TABLE  <tablename> (column_name1 datatype(column size),  column_name2  datatpe(size), column_name3  datatpe(size), ….. );

Example :

SQL>CREATE TABLE emp ( employee_id    NUMBER(6), first_name     VARCHAR2(20), last_name VARCHAR2(25), email VARCHAR2(25), phone_number   VARCHAR2(20),          hire_date      DATE , salary         NUMBER(8,2)  ) ;

Table created

How to view the structure of a table ?

DESCRIBE  (or)  DESC is the command to see the structure of any  object.

Example :

SQL>desc emp

 Name                          Null?    Type

 ----------------------------- -------- --------------------

 EMPLOYEE_ID                            NUMBER(6)

 FIRST_NAME                             VARCHAR2(20)

 LAST_NAME                              VARCHAR2(25)

 EMAIL                                          VARCHAR2(25)

 PHONE_NUMBER                      VARCHAR2(20)

 HIRE_DATE                                 DATE

 SALARY                                      NUMBER(8,2)

 

How to insert data/records in tables ?

Type 1 - if you want to insert data for all columns,

INSERT  INTO<tablename> VALUES (data1,data2,….);

Example :

SQL>insert into emp values(46,'Arun','Kumar','arun.kumar@gmail.com',67898345,'01-Dec-2008',25000);

1 row created.

SQL>commit;

Commit complete.

Note : COMMIT  is to store the data permanently in database.

Type-2 - - if you want to insert data for particular columns,

INSERT INTO <tablename> (column list) VALUES (data list).

Example :

SQL>insert into emp(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER) VALUES (100,'Sri','Varshan',89765478);

1 row created.

SQL>commit;

Commit complete.

 

How to retrieve/view data from table ?

SELECT is the SQL statement for retrieving data from tables.

1) Select all columns

Syntax : SELECT * FROM <tablename>;

Example :

 

2) Select only required colums,

Syntax : SELECT col1,col2… FROM <tablename> ;

Example : to view name and phone numbers.