Data Modifications in SQL/Oracle

Data Modification Languages(SQL/ORACLE)

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The DML commands are:

1. SELECT - retrieves data from a database
2. UPDATE - updates data in a database
3. DELETE - deletes data from a database
4. INSERT INTO - inserts new data into a database

The DDL statements are:

1. CREATE DATABASE - creates a new database
2. ALTER DATABASE - modifies a database
3. CREATE TABLE - creates a new table
4. ALTER TABLE - modifies a table
5. DROP TABLE - deletes a table
6. CREATE INDEX - creates an index (search key)
7. DROP INDEX - deletes an index

SELECT Statement

· is used to select data from a database.

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

Syntax

1. Select column name(s) from table name

2. Select * from table name

3. Select distinct column name(s) from table name

4. Select column name(s) from table name where column name operator value

An SQL SELECT Example

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1
10254	Meera	1	09/04/2006	3
13654	Thilak	2	09/05/2009	4

The "Employee_dtl" table:

To select all employees name from this table we can write

· select emp_name from employee_dtl;

The resultant table is as shown below.

Emp_name

Manu

Meera

Thilak

· Select * from employee_dtl - it select all columns in the table

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1
10254	Meera	1	09/04/2006	3
13654	Thilak	2	09/05/2009	4

· Select distinct department_id from employee_dtl –it select distinct field values from the table

Department_id

1

2

· Select emp_name from employee_dtl where emp_code=10001

Emp_name

Manu

· Select * from employee_dtl where department_id=1

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1
10254	Meera	1	09/04/2006	3

· Select emp_code from employee_dtl where emp_name =’Meera’

Emp_code

10254

Data Modifications in SQL/Oracle

Data Modifications in SQL

We can create a table using ‘create’ command. Now we can see how rows are inserted int the table, deleted from the table and modified within the table.

Insertions

Insert statement look like this ,

important.if no column name is speciafied the values must be given in the same order as the columns created in the table.

Eg: insert into emp(emp_Code,emp_name) values(10001,’JAYA’);

We can also insert data to a table from another table with querry.

Updates

We can use ‘update’ statement for modifying the attribute values of a tuple.

The new value to assign to column i must a matching data type. If we exclude the the where condition then the update statement updates all records of the table.

Eg: update emp set department-id=10 where emp_Code=70754;

Like in the case of insert statement other tables can be used to retrieve data that are used as new values. In this case we can use querry instead of expression.

Deletions

Using the delete command all or selected tuples can be deleted from a table.

If the where clause is omitted, all tuples are deleted from the table. The alternative command

for deleting all tuples from a table is the truncate table[ table] command. However, in this

case, the deletions cannot be undone.

Eg : delete from emp where emp_code=23564

Commit and Rollback

Transaction : A sequence of database modifications, i.e., a sequence of insert, update, and delete statements. Modifications of tuples are temporarily stored in the database system. They become permanent only after the statement commit; has been issued. So as long as the user has not issued the commit statement, it is possible to undo all modifications made since the last commit. To undo modifications, one has to issue the statement rollback.so to complete each modification of the database with a commit..

Any data definition command such as create table results in an internal commit. Also commit is implicitly executed when the user terminates an Oracle session.