Oracle Datatypes and Creating Tables

 A table is composed of rows and columns.

A table in Oracle Ver. 7.3 can have maximum 255 Columns and in Oracle Ver. 8 and above a table can have maximum 1000 columns. Number of rows in a table is unlimited in all the versions.

A table can represent a single entity that you want to track within your system. This type of a table could represent a list of the employees within your organization, or the orders placed for your company's products.

A table can also represent a relationship between two entities. This type of a table could portray the association between employees and their job skills, or the relationship of products to orders. Within the tables, foreign keys are used to represent relationships.

Although some well designed tables could represent both an entity and describe the relationship between that entity and another entity, most tables should represent either an entity or a relationship.

The following sessions explain how to create, alter, and drop tables. Some simple guidelines to follow when managing tables in your database are included.

Designing Tables

Consider the following guidelines when designing your tables:

• Use descriptive names for tables, columns, indexes, and clusters.
• Table Names, Columns Names can contain maximum of 30 characters and they should start with an alphabet.
• Be consistent in abbreviations and in the use of singular and plural forms of table names and columns.
• Select the appropriate datatype for each column.
• Arrange columns that can contain NULL Values in the last, to save storage space.

Before creating a table, you should also determine whether to use integrity constraints. Integrity constraints can be defined on the columns of a table to enforce the business rules of your database automatically.

Before creating a Table you also have to decide what type of data each column can contain. This is known as datatype.  Lets Discuss what datatypes are available in Oracle.

Datatypes

A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle to treat values of one datatype differently from values of another datatype. For example, Oracle can add values of NUMBER datatype, but not values of RAW datatype.

Oracle supplies the following built-in datatypes:

Character datatypes

• CHAR
• NCHAR
• VARCHAR2 and VARCHAR
• NVARCHAR2
• CLOB
• NCLOB
• LONG

Numeric datatypes

• NUMBER

Oracle CREATE TABLE

In Oracle, CREATE TABLE statement is used to create a new table in the database.

To create a table, you have to name that table and define its columns and datatype for each column.

Syntax:

1. CREATE TABLE table_name  
2. (   
3.   column1 datatype [ NULL | NOT NULL ],  
4.   column2 datatype [ NULL | NOT NULL ],  
5.   ...  
6.   column_n datatype [ NULL | NOT NULL ]  
7. );  

Parameters used in syntax

• table_name: It specifies the name of the table which you want to create.
• column1, column2, ... column n: It specifies the columns which you want to add in the table. Every column must have a datatype. Every column should either be defined as "NULL" or "NOT NULL". In the case, the value is left blank; it is treated as "NULL" as default.

Oracle CREATE TABLE Example

Here we are creating a table named customers. This table doesn't have any primary key.

1. CREATE TABLE customers  
2. ( customer_id number(10) NOT NULL,  
3.   customer_name varchar2(50) NOT NULL,  
4.   city varchar2(50)  
5. );  

This table contains three columns

• customer_id: It is the first column created as a number datatype (maximum 10 digits in length) and cannot contain null values.
• customer_name: it is the second column created as a varchar2 datatype (50 maximum characters in length) and cannot contain null values.
• city: This is the third column created as a varchar2 datatype. It can contain null values.

Oracle CREATE TABLE Example with primary key

1. CREATE TABLE customers  
2. ( customer_id number(10) NOT NULL,  
3.   customer_name varchar2(50) NOT NULL,  
4.   city varchar2(50),  
5.   CONSTRAINT customers_pk PRIMARY KEY (customer_id)  
6. );  

What is Primary key

A primary key is a single field or combination of fields that contains a unique record. It must be filled. None of the field of primary key can contain a null value. A table can have only one primary key.

Oracle Insert Statement

In Oracle, INSERT statement is used to add a single record or multiple records into the table.

Syntax: (Inserting a single record using the Values keyword):

1.  INSERT INTO table  
2. (column1, column2, ... column_n )  
3. VALUES  
4. (expression1, expression2, ... expression_n );   

Syntax: (Inserting multiple records using a SELECT statement):

1. INSERT INTO table  
2. (column1, column2, ... column_n )  
3. SELECT expression1, expression2, ... expression_n  
4. FROM source_table  
5. WHERE conditions;   

Parameters:

1) table: The table to insert the records into.

2) column1, column2, ... column_n:

The columns in the table to insert values.

3) expression1, expression2, ... expression_n:

The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

4) source_table:

The source table when inserting data from another table.

5) conditions:

The conditions that must be met for the records to be inserted.

Oracle Insert Example: By VALUE keyword

It is the simplest way to insert elements to a database by using VALUE keyword.

See this example:

Consider here the already created suppliers table. Add a new row where the value of supplier_id is 23 and supplier_name is Flipkart.

See this example:

1. INSERT INTO suppliers  
2. (supplier_id, supplier_name)  
3. VALUES  
4. (50, 'Flipkart');  

Output:

1 row(s) inserted.
0.02 seconds

Oracle Insert Example: By SELECT statement

This method is used for more complicated cases of insertion. In this method insertion is done by SELECT statement. This method is used to insert multiple elements.

See this example:

In this method, we insert values to the "suppliers" table from "customers" table. Both tables are already created with their respective columns.

Execute this query:

1. INSERT INTO suppliers  
2. (supplier_id, supplier_name)  
3. SELECT age, address  
4. FROM customers  
5. WHERE age > 20;  

Output:

4 row(s) inserted.
0.00 seconds

You can even check the number of rows that you want to insert by following statement:

1. SELECT count(*)  
2. FROM customers  
3. WHERE age > 20;  

Output:

     Count(*)
     4

Oracle UPDATE Statement

In Oracle, UPDATE statement is used to update the existing records in a table. You can update a table in 2 ways.

Traditional Update table method

Syntax:







UPDATE table  
SET column1 = expression1,  
    column2 = expression2,  
    ...  
    column_n = expression_n  
WHERE conditions;  




Update Table by selecting rocords from another table

Syntax:







UPDATE table1  
SET column1 = (SELECT expression1  
               FROM table2  
               WHERE conditions)  
WHERE conditions;   




Parameters:

1) column1, column2, ... column_n:

It specifies the columns that you want to update.

2) expression1, expression2, ...expression_n:

This specifies the values to assign to the column1, column2, ?. column_n.

3) conditions:It specifies the conditions that must be fulfilled for execution of UPDATE stateme.

Oracle Update Example: (Update single column)







UPDATE suppliers  
SET supplier_name = 'Kingfisher'  
WHERE supplier_id = 2;  




This example will update the supplier_name as "Kingfisher" where "supplier_id" is 2.

Oracle Update Example: (Update multiple columns)

The following example specifies how to update multiple columns in a table. In this example, two columns supplier_name and supplier_address is updated by a single statement.







UPDATE suppliers  
SET supplier_address = 'Agra',  
    supplier_name = 'Bata shoes'  
WHERE supplier_id = 1;  




Output:

1 row(s) updated.
0.06 seconds



Oracle Update Example: (By selecting records from another table)







UPDATE customers  
SET name = (SELECT supplier_name  
                 FROM suppliers  
                 WHERE suppliers.supplier_name = customers.name)  
WHERE age < 25;  




Output:

2 row(s) updated.
0.02 seconds



Oracle DELETE Statement

In Oracle, DELETE statement is used to remove or delete a single record or multiple records from a table.

Syntax







DELETE FROM table_name  
WHERE conditions;   




Parameters

1) table_name: It specifies the table which you want to delete.

2) conditions: It specifies the conditions that must met for the records to be deleted.

Oracle Delete Example: On one condition







DELETE FROM customers  
WHERE name = 'Sohan';     




This statement will delete all records from the customer table where name is "Sohan".

Oracle Delete Example: On multiple conditions







DELETE FROM customers  
WHERE last_name = 'Maurya'  
AND customer_id > 2;  




This statement will delete all records from the customers table where the last_name is "Maurya" and the customer_id is greater than 2.





Oracle TRUNCATE TABLE

In Oracle, TRUNCATE TABLE statement is used to remove all records from a table. It works same as DELETE statement but without specifying a WHERE clause. It is generally used when you don?t have to worry about rolling back

Once a table is truncated, it can?t be rolled back. The TRUNCATE TABLE statement does not affect any of the table?s indexes, triggers or dependencies.

Syntax







TRUNCATE TABLE [schema_name.]table_name   




Parameters

1) schema_name: This parameter specifies the name of the schema that the table belongs to. It is optional.

2) table_name: It specifies the table that you want to truncate.

Oracle TRUNCATE Table Example

Consider a table named "customers" and execute the following query to truncate this







TRUNCATE TABLE customers;  




Output

Table truncated.
1.11 seconds



Now check the customers table, you will find that there is no data available in that table. It is equally similar to DELETE TABLE statement in Oracle.

Oracle DELETE Table Example







DELETE TABLE customers;  




TRUNCATE TABLE vs DELETE TABLE

Both the statements will remove the data from the "customers" table but the main difference is that you can roll back the DELETE statement whereas you can't roll back the TRUNCATE TABLE statement.







Oracle ALTER TABLE Statement



In Oracle, ALTER TABLE statement specifies how to add, modify, drop or delete columns in a table. It is also used to rename atable.

How to add column in a table

Syntax:







ALTER TABLE table_name  
  ADD column_name column-definition;   




Example:

Consider that already existing table customers. Now, add a new column customer_age into the table customers.







ALTER TABLE customers  
  ADD customer_age varchar2(50);  




Now, a new column "customer_age" will be added in customers table.

How to add multiple columns in the existing table

Syntax:







ALTER TABLE table_name  
  ADD (column_1 column-definition,  
       column_2 column-definition,  
       ...  
       column_n column_definition);  




Example







ALTER TABLE customers  
  ADD (customer_type varchar2(50),  
       customer_address varchar2(50));  




Now, two columns customer_type and customer_address will be added in the table customers.



How to modify column of a table

Syntax:







ALTER TABLE table_name  
  MODIFY column_name column_type;   




Example:







ALTER TABLE customers  
  MODIFY customer_name varchar2(100) not null;  




Now the column column_name in the customers table is modified
to varchar2 (100) and forced the column to not allow null values. 



How to modify multiple columns of a table

Syntax:







ALTER TABLE table_name  
  MODIFY (column_1 column_type,  
          column_2 column_type,  
          ...  
          column_n column_type);  




Example:







ALTER TABLE customers  
  MODIFY (customer_name varchar2(100) not null,  
          city varchar2(100));  




This will modify both the customer_name and city columns in the table. 



How to drop column of a table

Syntax:







ALTER TABLE table_name  
  DROP COLUMN column_name;  




Example:







ALTER TABLE customers  
  DROP COLUMN customer_name;  




This will drop the customer_name column from the table.



How to rename column of a table

Syntax:







ALTER TABLE table_name  
  RENAME COLUMN old_name to new_name;  




Example:







ALTER TABLE customers  
 RENAME COLUMN customer_name to cname;  




This will rename the column customer_name into cname.



How to rename table

Syntax:







ALTER TABLE table_name  
  RENAME TO new_table_name;   




Example:







ALTER TABLE customers  
RENAME TO retailers;  




This will rename the customer table into "retailers" table. 




next →← prev

Oracle DROP TABLE Statement
Oracle DROP TABLE statement is used to remove or delete a table from the Oracle database.

Syntax








DROP [schema_name].TABLE table_name  
[ CASCADE CONSTRAINTS ]  
[ PURGE ];   




Parameters
schema_name: It specifies the name of the schema that owns the table.

table_name: It specifies the name of the table which you want to remove from the Oracle database.

CASCADE CONSTRAINTS: It is optional. If specified, it will drop all referential integrity constraints as well.

PURGE: It is also optional. If specified, the table and its dependent objects are placed in the recycle bin and can?t be recovered.


If there are referential integrity constraints on table_name and you do not specify the CASCADE CONSTRAINTS option, the DROP TABLE statement will return an error and Oracle will not drop the table.

DROP TABLE Example







DROP TABLE customers;  




This will drop the table named customers. 



DROP TABLE Example with PURGE parameter







DROP TABLE customers PURGE  



This statement will drop the table called customers and issue a PURGE so that the space associated with the customers table is released and the customers table is not placed in recycle bin. So, it is not possible to recover that table if required.


Oracle DISTINCT Clause
Oracle DISTINCT clause is used to remove the duplicate records from the result set. It is only used with SELECT statement.

Syntax:








SELECT DISTINCT expressions  
FROM tables  
WHERE conditions;  




Parameters:
expressions:It specifies the columns that you want to retrieve.

tables: It specifies the table from where you want to retrieve records.

conditions: It specifies the conditions that must be fulfilled.


Oracle DISTINCT Example: (with single expression)
Let's take a table "customers"

Customer table:








CREATE TABLE  "CUSTOMERS"   
   (    "NAME" VARCHAR2(4000),   
    "AGE" NUMBER,   
    "SALARY" NUMBER,   
    "STATE" VARCHAR2(4000)  
   )  
/  





Execute this query:








SELECT DISTINCT state  
FROM customers  
WHERE name = 'charu';  



Output:




Oracle DISTINCT Example: (with multiple expressions)
Execute this query:








SELECT DISTINCT name, age, salary  
FROM customers  
WHERE age >= '60';  



Output:



This example specifies distinct name, age and salary of the customer where age is greater than or equal to 65.


Oracle FROM Clause
FROM clause is a mandatory clause in SELECT expression. It specifies the tables from which data is to be retrieved.

Syntax:








FROM table_name...  
Expressions...  




Oracle FROM Clause Example: (with one table)
Let's take an example to explain how to use FROM clause to retrieve data from one table. Consider a table "customers".

Customer table:








CREATE TABLE  "CUSTOMERS"   
   (    "NAME" VARCHAR2(4000),   
    "AGE" NUMBER,   
    "SALARY" NUMBER,   
    "STATE" VARCHAR2(4000)  
   )  
/  





Execute this query:








SELECT *  
FROM customers  
WHERE salary >= 20000  
ORDER BY salary ASC;  



Output:




Oracle FROM Clause Example: (with two tables)
Inner Join example:

Let's take two tables "suppliers" and "order1".

Suppliers:




Order1:




Execute the following query:








SELECT suppliers.supplier_id, suppliers.supplier_name, order1.order_number  
FROM suppliers  
INNER JOIN order1  
ON suppliers.supplier_id = order1.supplier_id;  



Output:




Oracle ORDER BY Clause


In Oracle, ORDER BY Clause is used to sort or re-arrange the records in the result set. The ORDER BY clause is only used with SELECT statement.

Syntax:








SELECT expressions  
FROM tables  
WHERE conditions  
ORDER BY expression [ ASC | DESC ];   




Parameters:
expressions: It specifies columns that you want to retrieve.

tables: It specifies the table name from where you want to retrieve records.

conditions: It specifies the conditions that must be fulfilled for the records to be selected.

ASC: It is an optional parameter that is used to sort records in ascending order.

DESC: It is also an optional parameter that is used to sort records in descending order.


Oracle ORDER BY Example: (without ASC/DESC attribute)
Let's take a table "supplier"

Supplier table:








CREATE TABLE  "SUPPLIER"   
   (    "SUPPLIER_ID" NUMBER,   
    "FIRST_NAME" VARCHAR2(4000),   
    "LAST_NAME" VARCHAR2(4000)  
   )  
/  





Execute this Query:








SELECT *  
FROM supplier  
ORDER BY last_name;  



Output:



The above example returns the first_name ordered by last_name in ascending order.


Oracle ORDER BY Example: (sorting in descending order)
If you want to sort your result in descending order, you should use the DESC attribute in your ORDER BY clause:

Execute this Query:








SELECT *  
FROM supplier  
ORDER BY last_name DESC;  



Output



The above example returns the first_name ordered by last_name in descending order.








Oracle GROUP BY Clause
In Oracle GROUP BY clause is used with SELECT statement to collect data from multiple records and group the results by one or more columns.

Syntax:








SELECT expression1, expression2, ... expression_n,   
aggregate_function (aggregate_expression)  
FROM tables  
WHERE conditions  
GROUP BY expression1, expression2, ... expression_n;   




Parameters:
expression1, expression2, ... expression_n: It specifies the expressions that are not encapsulated within aggregate function. These expressions must be included in GROUP BY clause.

aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN, MAX or AVG functions.

aggregate_expression: It specifies the column or expression on that the aggregate function is based on.

tables: It specifies the table from where you want to retrieve records.

conditions: It specifies the conditions that must be fulfilled for the record to be selected.


Oracle GROUP BY Example: (with SUM function)
Let's take a table "salesdepartment"

Salesdepartment table:








CREATE TABLE  "SALESDEPARTMENT"   
   (    "ITEM" VARCHAR2(4000),   
    "SALE" NUMBER,   
    "BILLING_ADDRESS" VARCHAR2(4000)  
   )  
/  





Execute this query:








SELECT item, SUM(sale) AS "Total sales"  
FROM salesdepartment  
GROUP BY item;  



Output



The above example will show the total sales of every individual item.


Oracle GROUP BY Example: (with COUNT function)
Let's take a table "customers"

Here we are creating a table named customers. This table doesn't have any primary key.

Customer table:








CREATE TABLE  "CUSTOMERS"   
   (    "NAME" VARCHAR2(4000),   
    "AGE" NUMBER,   
    "SALARY" NUMBER,   
    "STATE" VARCHAR2(4000)  
   )  
/  





Execute this query:








SELECT state, COUNT(*) AS "Number of customers"  
FROM customers  
WHERE salary > 10000  
GROUP BY state;  



Output:




Oracle GROUP BY Example: (with MIN function)
Let?s take a table "employees"

Employees table:








CREATE TABLE  "EMPLOYEES"   
   (    "EMP_ID" NUMBER,   
    "NAME" VARCHAR2(4000),   
    "AGE" NUMBER,   
    "DEPARTMENT" VARCHAR2(4000),   
    "SALARY" NUMBER  
   )  
/  





Execute this query:








SELECT department,   
MIN(salary) AS "Lowest salary"  
FROM employees  
GROUP BY department;  



Output:




Oracle GROUP BY Example: (with MAX function)
In this example, we are using "employees" table that is given above.

Execute this query:








SELECT department,  
MAX(salary) AS "Highest salary"  
FROM employees  
GROUP BY department;  



Output:













next →← prev

Oracle HAVING Clause
In Oracle, HAVING Clause is used with GROUP BY Clause to restrict the groups of returned rows where condition is TRUE.

Syntax:








SELECT expression1, expression2, ... expression_n,   
 aggregate_function (aggregate_expression)  
FROM tables  
WHERE conditions  
GROUP BY expression1, expression2, ... expression_n  
HAVING having_condition;   




Parameters:
expression1, expression2, ... expression_n: It specifies the expressions that are not encapsulated within aggregate function. These expressions must be included in GROUP BY clause.

aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN, MAX or AVG functions.

aggregate_expression: It specifies the column or expression on that the aggregate function is based on.

tables: It specifies the table from where you want to retrieve records.

conditions: It specifies the conditions that must be fulfilled for the record to be selected.

having_conditions: It specifies the conditions that are applied only to the aggregated results to restrict the groups of returned rows.


Oracle HAVING Example: (with GROUP BY SUM function)
Let's take a table "salesdepartment"

Salesdepartment table:








CREATE TABLE  "SALESDEPARTMENT"   
   (    "ITEM" VARCHAR2(4000),   
    "SALE" NUMBER,   
    "BILLING_ADDRESS" VARCHAR2(4000)  
   )  
/  



Execute this query:








   
SELECT item, SUM(sale) AS "Total sales"  
FROM salesdepartment  
GROUP BY item  
HAVING SUM(sale) < 1000;  



Output:


Oracle HAVING Example: (with GROUP BY COUNT function)
Let's take a table "customers"

Customer table:








CREATE TABLE  "CUSTOMERS"   
   (    "NAME" VARCHAR2(4000),   
    "AGE" NUMBER,   
    "SALARY" NUMBER,   
    "STATE" VARCHAR2(4000)  
   )  
/  



Execute this query:








SELECT state, COUNT(*) AS "Number of customers"  
FROM customers  
WHERE salary > 10000  
GROUP BY state  
HAVING COUNT(*) >= 2;  



Output:


Oracle HAVING Example: (with GROUP BY MIN function)
Let's take a table "employees"

Employees table:








CREATE TABLE  "EMPLOYEES"   
   (    "EMP_ID" NUMBER,   
    "NAME" VARCHAR2(4000),   
    "AGE" NUMBER,   
    "DEPARTMENT" VARCHAR2(4000),   
    "SALARY" NUMBER  
   )  
/  



Execute this query:








SELECT department,   
MIN(salary) AS "Lowest salary"  
FROM employees  
GROUP BY department  
HAVING MIN(salary) < 15000;  



Output


Oracle HAVING Example: (with GROUP BY MAX function)
Execute this query:








SELECT department,  
MAX(salary) AS "Highest salary"  
FROM employees  
GROUP BY department  
HAVING MAX(salary) > 30000;  



Output:






Oracle Joins
Join is a query that is used to combine rows from two or more tables, views, or materialized views. It retrieves data from multiple tables and creates a new table.


Join Conditions
There may be at least one join condition either in the FROM clause or in the WHERE clause for joining two tables. It compares two columns from different tables and combines pair of rows, each containing one row from each table, for which join condition is true.


Types of Joins

Inner Joins (Simple Join)
Outer Joins

Left Outer Join (Left Join)
Right Outer Join (Right Join)
Full Outer Join (Full Join)

Equijoins
Self Joins
Cross Joins (Cartesian Products)
Antijoins
Semijoins





Oracle INNER JOIN
Inner Join is the simplest and most common type of join. It is also known as simple join. It returns all rows from multiple tables where the join condition is met.

Syntax








SELECT columns  
FROM table1   
INNER JOIN table2  
ON table1.column = table2.column;   



Image representation of Inner Join


Oracle INNER JOIN Example
Let's take an example to perform Inner Join on two tables "Suppliers" and "Order1".

Suppliers


Order1


This example will return all rows from "suppliers" and "order1" table where there is a matching supplier_id value in both the suppliers and order1 tables.


Execute the following query







SELECT suppliers.supplier_id, suppliers.supplier_name, order1.order_number  
FROM suppliers   
INNER JOIN order1  
ON suppliers.supplier_id = order1.supplier_id;  



Output














Oracle OUTER JOIN
An outer join is similar to equijoin but it gets also the non-matched rows from the table. It is categorized in Left Outer Join, Right Outer Join and Full Outer Join by Oracle 9i ANSI/ISO 1999 standard.


Left Outer Join
Left Outer Join returns all rows from the left (first) table specified in the ON condition and only those rows from the right (second) table where the join condition is met.

Syntax








SELECT columns  
FROM table1  
LEFT [OUTER] JOIN table2  
ON table1.column = table2.column;   



Image representation of left outer join

Example

In this example, we are performing left outer join on the already created tables ?suppliers? and ?order1?.

The following example would return all records from table ?suppliers? and only those records from table ?order1? where the joinfields are equal.

Execute this query








SELECT suppliers.supplier_id, suppliers.supplier_name, order1.order_number  
FROM suppliers  
LEFT OUTER JOIN order1  
ON suppliers.supplier_id = order1.supplier_id;  



Output


Right Outer Join
The Right Outer Join returns all rows from the right-hand table specified in the ON condition and only those rows from the othertable where the join condition is met.

Syntax








SELECT columns  
FROM table1  
RIGHT [OUTER] JOIN table2  
ON table1.column = table2.column;   



Image representation of Right Outer Join

Example

In this example, we are performing right outer join on the already created tables ?suppliers? and ?order1?.

The following example would return all rows from the order1 table and only those rows from the suppliers table where the joincondition is met.

Execute this query








SELECT order1.order_number, order1.city, suppliers.supplier_name  
FROM suppliers  
RIGHT OUTER JOIN order1  
ON suppliers.supplier_id = order1.supplier_id;  



Output


Full Outer Join
The Full Outer Join returns all rows from the left hand table and right hand table. It places NULL where the join condition is not met.

Syntax








SELECT columns  
FROM table1  
FULL [OUTER] JOIN table2  
ON table1.column = table2.column;   



Image representation of Full Outer Join

Example

In this example, we are performing full outer join on the already created tables ?suppliers? and ?order1?.

The following example will return all rows from the ?suppliers? table and all rows from the ?order1? table and whenever the joincondition is not met, it places the NULL value.

Execute this query








SELECT suppliers.supplier_id, suppliers.supplier_name, order1.order_number  
FROM suppliers  
FULL OUTER JOIN order1  
ON suppliers.supplier_id = order1.supplier_id;  



Output