Data Modifications in SQL/Oracle

SQL AND & OR Operators

· The AND operator uses to satisfy two conditions. That is it returns true only if two conditions are satisfied.

· The OR operator return true only if one of the conditions are satisfied.

Select * from employee_dtl where department_id=1 and designation_id=3

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10254	Meera	1	09/04/2006	3

Select * from employee_dtl where department_id=1 or designation_id=3

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

Select * from employee_dtl where department_id =1 and (designation_id=1 or designation_id=4)

Emp_Id	Emp_Name	Department_id	Join_dt	Designation
10001	Manu	1	06/06/2008	1

The ORDER BY Keyword

· Used to sort the result set by a specified column

· By default it sort in ascending order

· DESC is the keyword used to sort in descending order

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC.

Select * from employee_dtl order by join_dt

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

Select * from employee_dtl order by emp_code desc

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

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