SQL Basic Concepts

Basic Principles of Table in SQL

 A table is dtabase which holds the user data. Let’s take anexample of spreadsheet, cells of spreadsheet equate to the column of table having specific datatyapeassociated with them. The same logic applies to the table’s column. Each column of the table will have a specific datatype bound to it. Oracle ensures the user thet only data, which is identical to the datatype of in column, will stored within those column. 

Data types in Oracle:

 Datatype specifies the valuse which will stored in the variable. One should be wise to choose the datatype it will influence the performance of database.

Following are the commonly used datatypes : 

Datatype	Description
CHAR(size)	This datatype is used to store character strings values of fixed length. Size in bracket shows how much value can it store/ hold. Maximum size is 255 character.
VARCHAR(size)/  VARCHAR2(size)	This datatype is used to store variable length alphanumeric data. It is flexible form of CHAR datatype . The maximum valuse it can store is upto 4000 characters.
DATE	This datatype is used to represent date and time. The standard format is DD-MON-YY as 11-DEC-1999. For time it is 07:16:38 am.
NUMBER(P,S)	The number datatype is used to store number(Fixed or floating point). It can store upto 38 digits of any magnitude ranges from 1.0E-130 to 9.9...E125.
LONG	This datatype is used to store variable length character strings containing upto 2GB. LONG datatype can be used to store arrays of binary data is ASCII format.
RAW/  LONG RAW	The RAW/ LONG RAW datatype are used to store binary data, such as digitalized picture or image.

There are many other datatypes such as dec(p,s), integer, real, rowid, boolean, blob, etc.

CREATE Table Command :

 Create table command define each column of table uniquely. Each column has three attributes i.e. a name, datatype, size. 

Rules of creating table :

1. A name can have maximum of 30 characters. 

2. A name should begin with an alphabet.

3. SQL reserved words are not allowed. Ex. Create, etc.

4. Use of special character (_) is allowed/ recommended.

Syntax:

  SQL> CREATE TABLE <TABLE_NAME>(

         2 <COLUMN_1> DATATYPE (SIZE) [CONSTRAINTS],

         3 <COLUMN_2> DATATYPE (SIZE) [CONSTRAINT],

         4 <COLUMN_n> DATATYPE (SIZE) )

         5 ;

Example:

 SQL> CREATE TABLE EMPLOYEE(

        2 EMP_NO NUMBER PRIMARY KEY,

        3 EMP_NAME VARCHAR(20) NOT NULL,

        4 LOCATION VARCHAR(20))

        5 ;

 Table Created.