Oracle database -12c - 3

Creating a table

Datatypes

Inserting rows into a table

Selecting rows from a table

CREATING A TABLE A Table is a collection of rows and columns. Data in relational model is stored in tables. Let us create a table first. Then we will understand how to store data into table and retrieve data from the table.

Before a table is created the following factors of a table are to be finalized.

1. What data the table is supposed to store
2. The name of the table. It should depict the content of the
3. table
4. What are the columns that table should contain
5. The name, data type and maximum length of each column of the table
6. What are the rules to be implemented to maintain data
7. integrity of the table

...............................

CREATE TABLE tablename

(column datatype [DEFAULT expr] [constraints][, ...] [,table_constraints] ... )

.............................

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

The following CREATE TABLE command is used to create Persons table.

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

The above command creates a table called Persons. This table contains 5 columns.

If command is successful, Oracle responds by displaying the message Table Created.

Rules to be followed for names The following are the rules to be followed while naming an Oracle Object. These rules are applicable for name of the table and column.

1. The name must begin with a letter - A-Z or a-z.
2. Letters, digits and special characters – underscore (_), $
3. and # are allowed.
4. Maximum length of the name is 30 characters.
5. It must not be an SQL reserved word.
6. There should not be any other object with the same name in your account.

Note: A table can contain up to 1000 columns in Oracle8 or above, whereas in Oracle7 a table can contain only 254 columns.

Datatypes:

DATATYPES Each column of the table contains the datatype and maximum length, if length is applicable to column. Datatype of the column specifies what type of data can be stored in the column.

The datatype VARCHAR2 is to store strings that may have different number of characters, NUMBER is used to store numbers. The maximum length, which is given in parentheses after the datatype, specifies how many characters (or digits) the column can store at the most. For example, column VARCHAR2 (20) would mean it can store up to 20 characters.

Following table lists out data types available in Oracle along with type of data that can be stored and maximum length allowed.

Datatype Description VARCHAR2(len) Can store up to len number of characters. Each character would occupy one byte. Maximum width is 32767 characters. VARCHAR(len) Same as VARCHAR2. But use VARCHAR2 as Oracle might change the usage of VARCHAR in future releases. CHAR(len) Fixed length character data. If len is given then it can store up to len number of characters. Default width is 1. String is padded on the right with spaces until string is of len size. Maximum width is 2000. NUMBER Can store numbers up to 40 digits plus decimal point and sign. NUMBER(p,s) P represents the maximum significant digits allowed. S is the number of digits on the right of the decimal point. DATE Can store dates in the range 1-1-4712 B.C. to 31-12-4712 A.D. LONG Variable length character values up to 2 gigabytes. Only one LONG column is allowed per table. You cannot use LONG datatype in functions, WHERE clause of SELECT, in indexing and in subqueries. RAW and LONG RAW Equivalent to VARCHAR2 and LONG respectively, but used for storing byte- oriented or binary data such as digital sound or graphics images. CLOB,BLOB,NCLOB Used to store large character and binary objects. Each can accommodate up to 4 gigabytes. We will discuss more about it later in this book.

BFILE Stores a pointer to an external file. The content of the file resides in the file system of the operating system. Only the name of the file is stored in the column. ROWID Stores aunique number that is used by Oracle to uniquely identify each row of the table. NCHAR (size) Same as CHAR, but supports national language. NVARCHAR2 (size) Same as VARCHAR2, but supports national language.

Inserting rows into a table:

Inserting a row with selected columns It is possible to insert a new row by giving values only for a few columns instead of giving values for all the available columns.

INSERT INTO table_name
VALUES (value1,value2,value3,...);

It is also possible to only insert data in specific columns.

The following SQL statement will insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns (and the CustomerID field will of course also be updated automatically):

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Syamal','Venkat','KPHB 21','HYD','520084','INDIA');

Selecting rows from a table:

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SELECT column_name,column_name
FROM table_name;

or

SELECT * FROM table_name;



SELECT * FROM DBA_USERS;