EX.NO:2 DATA MANIPULATION AND DATA CONTROL LANGUAGE

(Insert, select, update and delete commands)

AIM:

To execute the Data Manipulation Language (DML) and Data Control Language (DCL) commands in RDBMS.

Data Manipulation Language-Syntax :

INSERT:

INSERT INTO <table name> values

(

fieldvalue-1,fieldvalue-2,…,fieldvalue-n

);

INSERT INTO <table name> VALUES

(

&fieldname-1,&fieldname-2,…&fieldname-n

);

INSERT INTO <table name>

(

SELECT(att_list) FROM <existing table name>

);

SELECT

SELECT(att_list) FROM <table name> [WHERE <condition/expression>];

UPDATE

UPDATE <table name> SET

(

fieldname-1 = value, fieldname-2 = value,…,fieldname-n = value)

[WHERE <condition/expression>]; );

DELETE

DELETE FROM <table name>

[WHERE <condition/expression>];

Data Control Language:

Data control language provides users with privilege commands.

GRANT

Used to give privilege to user on object

Grant privilege on <object_name> to <user name>;

REVOKE

Used to withdraw the privilege that has been granted to the user.

Revoke privilege on <object_name> from <user name>;

Sample Output

INSERT, SELECT, UPDATE AND DELETE COMMANDS

SQL> create table person(pid int, lastname varchar2(10),firstname varchar(10),

address varchar2(20),age number);

Table created.

INSERTING A SINGLE ROW INTO A TABLE

SQL> insert into person values(1,'Prettina','Anne','Bangalore',14);

1 row created.

SQL> insert into person values(2,'Benitto','Anish','Trichy',24);

1 row created.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

INSERTING MORE THAN ONE ROW USING A SINGLE INSERT COMMAND

SQL> insert into person values(&pid,'&lastname','&firstname','&address',&age);

Enter value for pid: 3

Enter value for lastname: Raj

Enter value for firstname: Anita

Enter value for address: Chennai

Enter value for age: 27

old 1: insert into person values(&pid,'&lastname','&firstname','&address',&age)

new 1: insert into person values(3,'Raj','Anita','Chennai',27)

1 row created.

SQL> /

Enter value for pid: 4

Enter value for lastname: kumar

Enter value for firstname: Ashok

Enter value for address: Coimbatore

Enter value for age: 30

old 1: insert into person values(&pid,'&lastname','&firstname','&address',&age)

new 1: insert into person values(4,'kumar','Ashok','Coimbatore',30)

1 row created.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 kumar Ashok Coimbatore 30

SKIPPING THE FIELDS WHILE INSERTING

SQL> insert into person(pid,lastname,firstname) values(5,Hinn,Benny);

insert into person(pid,lastname,firstname) values(5,'Hinn','Benny')

1 row created.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 kumar Ashok Coimbatore 30

5 Hinn Benny

INSERT VALUES USING MEANINGFUL FIELD NAMES

SQL> insert into person values(&personid,'&lastname','&firstname','&personaddress',&age);

Enter value for personid: 6

Enter value for lastname: Prakash

Enter value for firstname: Bhaskar

Enter value for personaddress: Andhra

Enter value for age: 40

old 1: insert into person values(&personid,'&lastname','&firstname','&personaddress',&age)

new 1: insert into person values(6,'Prakash','Bhaskar','Andhra',40)

1 row created.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 kumar Ashok Coimbatore 30

5 Hinn Benny

6 Prakash Bhaskar Andhra 40

6 rows selected.

UPDATE VALUES USING CONDITION

SQL> update person set address='United States'where pid=5;

1 row updated.

UPDATE VALUES USING &

SQL> update person set address ='&address',age=&age where pid=&pid;

Enter value for address: Assam

Enter value for age: 40

Enter value for pid: 6

old 1: update person set address ='&address',age=&age where pid=&pid

new 1: update person set address ='Assam',age=40 where pid=6

1 row updated.

SQL> /

Enter value for address: Britain

Enter value for age: 55

Enter value for pid: 5

old 1: update person set address ='&address',age=&age where pid=&pid

new 1: update person set address ='Britain',age=55 where pid=5

1 row updated.

SELECT COMMAND TO RETRIEVE THE ENTIRE INFORMATION FROM THE TABLE

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

6 rows selected.

SELECT COMMAND USING 'WHERE' CLAUSE

SQL>select * from person where lastname= 'Kumar' and address='Coimbatore';

PID LASTNAME FIRSTNAME ADDRESS AGE

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

4 Kumar Ashok Coimbatore 30

7 Kumar Chander Coimbatore 45

SELECT COMMAND TO RETRIEVE THE TOP VALUES

SQL> select * from person where rownum<=3;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

SELECT COMMAND WITH LIKE OPERATOR

SQL> select * from person where address like 'C%';

PID LASTNAME FIRSTNAME ADDRESS AGE

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

3 Raj Anita Chennai 27

4 kumar Ashok Coimbatore 30

SQL> select * from person where address like '%i%';

PID LASTNAME FIRSTNAME ADDRESS AGE

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

2 Benitto Anish richy 24

3 Raj Anita Chennai 27

4 Kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

SELECT COMMAND USING IN OPERATOR

SQL> select * from person where lastname in('Prettina');

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

SELECT COMMAND USING BETWEEN OPERATOR

SQL> insert into person values(7,'Kumar','Chander','Coimbatore',45);

1 row created.

SQL> select * from person where lastname between 'Kumar' and 'Kumar';

PID LASTNAME FIRSTNAME ADDRESS AGE

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

6 Kumar Ashok Coimbatore 30

7 Kumar Chander Coimbatore 45

SELECT COMMAND TO ELIMINATE DUPLICATES

SQL> select DISTINCT lastname from person;

LASTNAME

----------

Benitto

Hinn

Kumar

Prakash

Prettina

Raj

6 rows selected.

SELECT COMMAND WITH ORDER BY CLAUSE

SQL> select pid, firstname,age from person order by age;

PID FIRSTNAME AGE

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

1 Anne 14

2 Anish 24

3 Anita 27

4 Ashok 30

6 Bhaskar 40

7 Chander 45

5 Benny 55

7 rows selected.

SELECT COMMAND TO CREATE A TABLE

SQL> create table individual as select * from person;

Table created.

SQL> select * from individual;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 Kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

7 Kumar Chander Coimbatore 45

7 rows selected.

SELECT COMMAND TO INSERT RECORDS

SQL> insert into individual(select * from person);

7 rows created.

SELECT COMMAND WITH FUNCTIONS

SQL> select count(*) as pid from person;

PID

----------

SQL> select count(distinct lastname) as pid from person;

PID

----------

SQL> select max(age) from person;

MAX(AGE)

----------

SQL> select min(age) from person;

MIN(AGE)

----------

SQL> select sum(age) from person;

SUM(AGE)

----------

235

DATA CONTROL LANGUAGE (DCL) COMMANDS

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne BAngalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 Kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

7 Kumar Chander Coimbatore 45

7 rows selected.

SQL> commit;

Commit complete.

DELETE COMMAND

SQL> delete from person where lastname='Kumar';

2 rows deleted.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne BAngalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

SQL> rollback;

Rollback complete.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne BAngalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 Kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

7 Kumar Chander Coimbatore 45

7 rows selected.

SQL> savepoint s1;

Savepoint created.

SQL> delete from person;

7 rows deleted.

SQL> select * from person;

no rows selected

SQL> rollback to savepoint s1;

Rollback complete.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne BAngalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 Kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

7 Kumar Chander Coimbatore 45

7 rows selected.

RESULT:

Thus the Data Manipulation Language (DML) and Data Control Language (DCL) commands in RDBMS were executed and verified.

Information technology

Monday, July 15, 2013

DBMS-DML

SELECT

UPDATE

DELETE

No comments:

Post a Comment