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