Data manipulation in oracle

Unit 2
Data Manipulation Language (DML)
• Data Manipulation Language (DML)
statements access and manipulate data stored
in the Oracle database. You can use them to
insert, update, delete, and read data. Control
statements are related, since they are used to
control how Oracle operates when accessing
data in the database
 Create a table: it is not DML, it is DDL

• 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:
• CREATE TABLE table_name  
• (   
•   column1 datatype [ NULL | NOT NULL ],  
•   column2 datatype [ NULL | NOT NULL ],  
•   ...  
•   column_n datatype [ NULL | NOT NULL ]  
• );  
 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.
• CREATE TABLE customers  
• ( customer_id number(10) NOT NULL,  
•   customer_name varchar2(50) NOT NULL,  
•   city varchar2(50)  
• );  
• 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

• CREATE TABLE customers  
• ( customer_id number(10) NOT NULL,  
•   customer_name varchar2(50) NOT NULL,  
•   city varchar2(50),  
•   CONSTRAINT customers_pk PRIMARY KEY (c
ustomer_id)  
• );  
 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.
 CREATE TABLE AS Statement

• The CREATE TABLE AS statement is used to

create a table from an existing table by copying
the columns of existing table.
• CREATE TABLE new_table  
• AS (SELECT * FROM old_table);   
• Ex(copying all columns of another table )
• CREATE TABLE new_customers  
• AS (SELECT *   FROM customers  WHERE custom
er_id < 5000);  
 copying selected columns of another table

• CREATE TABLE new_table  
•   AS (SELECT column_1, column2, ... column_n  
•       FROM old_table);  

• CREATE TABLE newcustomers2  
• AS (SELECT customer_id, customer_name  
•     FROM customers  
•     WHERE customer_id < 5000);  
• The above example will create a new table called
"newcustomers2". This table includes the specified columns
customer_id and customer_name from the customers table.
 Create Table Example: copying selected columns from multiple tables

• CREATE TABLE new_table  
• AS (SELECT column_1, column2, ... column_n  
•     FROM old_table_1, old_table_2, ... old_table_n);   
• Consider that you have already created two tables
"regularcustomers" and "irregularcustomers".
• The table "regularcustomers" has three columns
rcustomer_id, rcustomer_name and rc_city.
• CREATE TABLE  "regularcustomers"   
•    (    "RCUSTOMER_ID" NUMBER(10,0) NOT N
ULL ENABLE,   
•     "RCUSTOMER_NAME" VARCHAR2(50) NOT 
NULL ENABLE,   
•     "RC_CITY" VARCHAR2(50)  
•    )  
• The second table "irregularcustomers" has also three
columns ircustomer_id, ircustomer_name and irc_city.
• CREATE TABLE  "irregularcustomers"   
•    (    "IRCUSTOMER_ID" NUMBER(10,0) NOT NULL ENA
BLE,   
•     "IRCUSTOMER_NAME" VARCHAR2(50) NOT NULL EN
ABLE,   
•     "IRC_CITY" VARCHAR2(50)  
•    )  
• In the following example, we will create a table name
"newcustomers3" form copying columns from both
tables.
• CREATE TABLE newcustomers3  
•   AS (SELECT regularcustomers.rcustomer_id, regularcust
omers.rc_city, irregularcustomers.ircustomer_name  
•       FROM regularcustomers, irregularcustomers  
•       WHERE regularcustomers.rcustomer_id = irregularcus
tomers.ircustomer_id  
•       AND regularcustomers.rcustomer_id < 5000);   
 Modify/Alter a Table
• In Oracle, ALTER TABLE statement specifies
how to add, modify, drop or delete columns in
a table. It is also used to rename a table.
• 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);  
 How to modify column of a table

• Syntax:
• ALTER TABLE table_name  
•   MODIFY column_name column_type;   
 Example:
• ALTER TABLE customers  
•   ADD (customer_type varchar2(50),  
•        customer_address varchar2(50));

• two columns customer_type and

customer_address will be added in the table
customer   
 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.
 Oracle DROP TABLE 
• 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.
 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 SELECT Statement

• The Oracle SELECT statement is used to retrieve data from one or more
than one tables, object tables, views, object views etc.
• Syntax
• SELECT expressions  
• FROM tables  
• WHERE conditions;   
• Parameters
• 1) expressions: It specifies the columns or calculations that you want to
retrieve.
• 2) tables:This parameter specifies the tables that you want to retrieve
records from. There must be at least one table within the FROM clause.
• 3) conditions: It specifies the conditions that must be followed for
selection.
 Select Example: select all fields

• Let's take an example to select all fields from an already created table
named customers
• SELECT *  
• FROM customers;   
 Select Example: select specific fields
• Example
• SELECT age, address, salary  
• FROM customers  
• WHERE  age < 25  
• AND salary > '20000'  
• ORDER BY age ASC, salary DESC;  
 Select Example: select fields from multiple
tables (JOIN)
• SELECT customers.name, courses.trainer  
• FROM courses  
• INNER JOIN customers  
• ON courses.course_id = course_id  
• ORDER BY name;   
 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):
•  INSERT INTO table  
• (column1, column2, ... column_n )  
• VALUES  
• (expression1, expression2, ... expression_n );   
 Syntax: (Inserting multiple records using a SELECT
statement)

• INSERT INTO table  
• (column1, column2, ... column_n )  
• SELECT expression1, expression2, ... expressio
n_n  
• FROM source_table  
• WHERE conditions;   
• Parameters:
• 1) table: The table to insert the records into.
• ) 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

• Consider here the already created suppliers

table. Add a new row where the value of
supplier_id is 23 and supplier_name is Flipkart.
• INSERT INTO suppliers  
• (supplier_id, supplier_name)  
• VALUES  
• (50, 'Flipkart');  
• Output: 1 row(s) inserted.
 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.
• In this method, we insert values to the
"suppliers" table from "customers" table. Both
tables are already created with their
respective columns.
• INSERT INTO suppliers  
• (supplier_id, supplier_name)  
• SELECT age, address  
• FROM customers  
• WHERE age > 20;  
• Output: 4 row(s) inserted.
 Oracle INSERT ALL Example
• This example specifies how to insert multiple
records in one table. Here we insert three
rows into the "suppliers" table.
 Oracle INSERT ALL statement
• The Oracle INSERT ALL statement is used to
insert multiple rows with a single INSERT
statement. You can insert the rows into one
table or multiple tables by using only one SQL
command.
 Syntax

• INSERT ALL  
•   INTO table_name (column1, column2, colum
n_n) VALUES (expr1, expr2, expr_n)  
•   INTO table_name(column1, column2, column
_n) VALUES (expr1, expr2, expr_n)  
•   INTO table_name (column1, column2, colum
n_n) VALUES (expr1, expr2, expr_n)  
• SELECT * FROM dual;  
 Parameters

• 1) table_name: it specifies the table in which

you want to insert your records.
• 2) column1, column2, column_n: this
specifies the columns in the table to insert
values.
• 3) expr1, expr2, expr_n: this specifies the
values to assign to the columns in the table.
 Oracle INSERT ALL Example
• Here we insert three rows into the "suppliers" table.
• INSERT ALL  
•   INTO suppliers (supplier_id, supplier_name) VALUES (20, 'Goo
gle')  
•   INTO suppliers (supplier_id, supplier_name) VALUES (21, 'Micr
osoft')  
•   INTO suppliers (supplier_id, supplier_name) VALUES (22, 'Appl
e')  
• SELECT * FROM dual;  
• Output
• 3 row(s) inserted.
• This is totally equivalent to the following three
INSERT statements.
• INSERT INTO suppliers (supplier_id, supplier_n
ame) VALUES (1000, 'Google');  
• INSERT INTO suppliers (supplier_id, supplier_n
ame) VALUES (2000, 'Microsoft');  
• INSERT INTO suppliers (supplier_id, supplier_n
ame) VALUES (3000, 'Apple');  
 Oracle INSERT ALL Example: (Insert into
multiple tables)
• The INSERT ALL statement can also be used to insert multiple rows into more than
one table by one command only.

• In the following example, we are going to insert records into the both "suppliers"
and "customers" tables.

• INSERT ALL
• INTO suppliers (supplier_id, supplier_name) VALUES (30, 'Google')
• INTO suppliers (supplier_id, supplier_name) VALUES (31, 'Microsoft')
• INTO customers (age, name, address) VALUES (29, 'Luca Warsi', 'New York')
• SELECT * FROM dual;
• Output

• 3 row(s) inserted.
 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 records from
another table
• 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)

• 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;  
Oracle Update Example: (By selecting records from another table)

• UPDATE customers  
• SET name = (SELECT supplier_name  
•                  FROM suppliers  
•                  WHERE suppliers.supplier_name = c
ustomers.name)  
• WHERE age < 25;  
 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
condition
• 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.