INTRODUCTION TO ORACLE

1. DDL: Data Definition Language (DDL) statements are used to define the database structure or

schema.

DDL Commands: Create, Alter, Drop, Rename, Truncate

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

RENAME - rename an object

2. DML: Data Manipulation Language (DML) statements are used for managing data within schema

objects and to manipulate data of a database objects.

DML Commands: Insert, Update, Delete, Select

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

SELECT - retrieve data from the a database

3. DCL: Data Control Language (DCL) statements are used to create roles, permissions, and referential

integrity as well it is used to control access to database by securing it. To control the data of a database.

DCL Commands: Grant, Revoke

GRANT - gives user's access privileges to database

REVOKE -withdraw access privileges given with the GRANT command

4. TCL: Transaction Control (TCL) statements are used to manage the changes made by DML

statements. It allows statements to be grouped together into logical transactions.

TCL Commands: Commit, Rollback, Save point

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last COMMIT

Ex.No : 01 CREATION OF TABLES

1) Create a table called Employee with the following structure.

a. Add a column commission with domain to the Employee table.

b. Insert any five records into the table.

c. Update the column details of job

d. Rename the column of Employ table using alter command.

e. Delete the employee whose Empno is 105.

SOLUTION:

SQL> create table employee(empno number,ename varchar2(10),job varchar2(10),mgr

number,sal number);

Table created.

SQL> desc employee;

Name Null? Type

--------------------------- -------- ----------------------------

EMPNO NUMBER

ENAME VARCHAR2(10)

JOB VARCHAR2(10)

MGR NUMBER

SAL NUMBER

a. Add a column commission with domain to the Employee table.

SQL> alter table employee add(commission number);

Table altered.

SQL> desc employee;

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPNO NUMBER

ENAME VARCHAR2(10)

JOB VARCHAR2(10)

MGR NUMBER

SAL NUMBER

COMMISSION NUMBER

b. Insert any five records into the table.

SQL> insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission');

Enter value for empno: 101

Enter value for ename: abhi

Enter value for job: manager

Enter value for mgr: 1234

Enter value for sal: 10000

Enter value for commission: 70

old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')

new 1: insert into employee values(101,'abhi','manager',1234,10000,'70')

1 row created.

SQL> /

Enter value for empno: 102

Enter value for ename: rohith

Enter value for job: analyst

Enter value for mgr: 2345

Enter value for sal: 9000

Enter value for commission: 65

old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')

new 1: insert into employee values(102,'rohith','analyst',2345,9000,'65')

old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')

new 1: insert into employee values(105,'pramod','salesman',5678,5000,'50')

1 row created.

SQL> select * from employee;

EMPNO ENAME JOB MGR SAL COMMISSION

---------- ---------- ---------- ---------- ---------- ----------

101 abhi manager 1234 10000 70

102 rohith analyst 2345 9000 65

103 david analyst 3456 9000 65

104 rahul clerk 4567 7000 55

105 pramod salesman 5678 5000 50

c. Update the column details of job

SQL> update employee set job='trainee' where empno=103;

1 row updated.

SQL> select * from employee;

EMPNO ENAME JOB MGR SAL COMMISSION

---------- ---------- ---------- ---------- ---------- ----------

101 abhi manager 1234 10000 70

102 rohith analyst 2345 9000 65

103 david trainee 3456 9000 65

104 rahul clerk 4567 7000 55

105 pramod salesman 5678 5000 50

d. Rename the column of Employ table using alter command.

SQL> alter table employee rename column mgr to manager_no;

Table altered.

SQL> desc employee;

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPNO NUMBER

ENAME VARCHAR2(10)
JOB VARCHAR2(10)

MANAGER_NO NUMBER

SAL NUMBER

COMMISSION NUMBER

e. Delete the employee whose Empno is 105.

SQL> delete employee where empno=105;

1 row deleted.

SQL> select * from employee;

EMPNO ENAME JOB MANAGER_NO SAL COMMISSION

---------- ---------- ---------- ---------- ---------- ----------

101 abhi manager 1234 10000 70

102 rohith analyst 2345 9000 65

103 david trainee 3456 9000 65

104 rahul clerk 4567 7000 55

Ex.No : 02 CREATION OF TABLES USING DDL AND DML COMMANDS

Create department table with the following structure.

a. Add column designation to the department table.

b. Insert values into the table.

c. List the records of dept table grouped by deptno.

d. Update the record where deptno is 9.

e. Delete any column data from the table.

SOLUTION:

SQL> create table department(deptno number,deptname varchar2(10),location varchar2(10));

Table created.

SQL> desc department;

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NUMBER

DEPTNAME VARCHAR2(10)

LOCATION VARCHAR2(10)

a. Add column designation to the department table.

SQL> alter table department add(designation varchar2(10));

Table altered.

SQL> desc department;

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NUMBER

DEPTNAME VARCHAR2(10)

LOCATION VARCHAR2(10)

DESIGNATION VARCHAR2(10)
b. Insert values into the table.

SQL> insert into department values(&deptno,'&deptname','&location','&designation');

Enter value for deptno: 9

Enter value for deptname: accounting

Enter value for location: hyderabad

Enter value for designation: manager

old 1: insert into department values(&deptno,'&deptname','&location','&designation')

new 1: insert into department values(9,'accounting','hyderabad','manager')

1 row created.

SQL> /

Enter value for deptno: 10

Enter value for deptname: research

Enter value for location: chennai

Enter value for designation: professor

old 1: insert into department values(&deptno,'&deptname','&location','&designation')

new 1: insert into department values(10,'research','chennai','professor')

1 row created.

old 1: insert into department values(&deptno,'&deptname','&location','&designation')

new 1: insert into department values(9,'accounting','chennai','manager')

1 row created.

SQL> select * from department ;

DEPTNO DEPTNAME LOCATION DESIGNATION

---------------- ------------------- ----------------- -------------------

9 accounting hyderabad manager

10 research chennai professor

11 sales banglore salesman

12 operations mumbai operator

9 accounting chennai manager

c. List the records of dept table grouped by deptno.

SQL> select deptno,deptname from department group by deptno,deptname;

DEPTNO DEPTNAME

---------- ----------

9 accounting

12 operations

10 research

11 sales

d. Update the record where deptno is 9.

SQL> update department set designation='accountant' where deptno=9;

2 rows updated.

SQL> select * from department;

DEPTNO DEPTNAME LOCATION DESIGNATION

---------------- ------------------- ----------------- -------------------

9 accounting hyderabad accountant

10 research chennai professor

11 sales banglore salesman

12 operations mumbai operator

9 accounting chennai accountant

e. Delete any column data from the table.

SQL> alter table department drop(designation);

Table altered.

SQL> select * from department;

DEPTNO DEPTNAME LOCATION

---------- --------------- ----------

9 accounting hyderabad

10 research chennai

11 sales banglore

12 operations mumbai

9 accounting Chennai