Aim:

To perform insertion, deletion, modification of records based on some conditions and to execute the DCL and TCL commands

Insert command:

Insert command is used to insert single or multiple rows in the table.

Syntax:

Single row:

insert into <table-name> values (list of values);

Multiple rows:

insert into <table-name> values(&val1,’&val2’,….,&valn);

Example:

Single row:

insert into emp values(1000,’anitha’,’lecturer’,400,’12-aug-80’,10000,100);

Multiple rows:

Insert into emp values(&empno,’&empname’,’&job’,&mgr,’&hiredate’,&salary,&deptno);

Delete command:

The delete command removes tuples from a relation. It includes the where clause to select the tuples to be deleted.

Syntax:

delete from <table-name> where attribute-name = ‘value’ or value;

Example:

delete from emp where empname = ‘anitha’;

Update command:

The update command is used to modify attribute values of one or more selected tuples. It includes a where clause to select the tuples to be modified from a single relation.

Syntax:

update <table-name>

set col1=val1,…,coln = valn

where condition;

Example:

update emp

set salary = 12000

where empno =1000;

Select clause:

The select clause is used to select a particular or all attributes from  relations.

Syntax:

Select <attribute list> or (*) from <table list> where <condition>;

Example:

Select * from emp where empno =1000;

Select empno, empname from emp;

TRANSACTION CONTROL LANGUAGE

Commit:

A commit ends the current transaction and makes permanent any changes made during the transaction.

Syntax:

commit;

Rollback:

A rollback does exactly the opposite to commit. It ends the transaction but undoes any changes made during the transaction.

Syntax:

rollback to savepoint <savepointname>;

Savepoint:   

savepoint marks and saves the current point in the processing of a transaction. When a savepoint is used with a rollback statement, parts of a transaction can be undone.

DATA CONTROL LANGUAGE:

GRANT

Syntax:

GRANT permissions ON objects TO account

Example Query 1:

GRANT INSERT ON employee TO PUBLIC;

Example Query 2:

GRANT SELECT,UPDATE ON employee to username;

REVOKE

Syntax:

REVOKE permissions ON object FROM account

Example :

REVOKE SELECT ON student FROM username; 

BUILT IN FUNCTIONS

Character functions

Sql> select initcap(‘xyz’) from dual;

Sql> select lower(‘XYZ’) from dual;

Sql> select upper(‘xyz’) from dual;

Sql> select ascii(‘a’) from dual;

Sql> select rtrim(‘xyzabc’,’abc’) from dual;

Sql> select ltrim(‘xyzabc’,’xyz’) from dual;

Sql> select translate(‘rmk’,’k’,’d’) from dual;

Sql> select lpad(‘rmd’,8,’*’) from dual;

Sql> select rpad(‘rmd’,8,’*’) from dual;

Sql> select length(‘rmd’) from dual;

Numeric functions

Sql> select abs(34) from dual;

Sql> select abs(-28) from dual;

Sql> select ceil(44.56) from dual;

Sql> select floor(33.48) from dual;

Sql> select power(4,2) from dual;

Sql> Select mod(10,3) from dual;

Sql> select sqrt(25) from dual;

Sql> select trunc(100.43) from dual;

Sql> select round(100.256,1) from dual;

Sql> Select sign(-34) from dual;

Sql> Select sign(34) from dual;

Date functions

Sql> select to_char(sysdate,’dd-mm-yyyy’) from dual;

Sql> select add_months(sysdate,9) from dual;

Sql> select months_between(’07-aug-05’,sysdate) from dual;

Sql> select greatest(’17-jun-00’,sysdate) from dual;

Sql> select next_day(’03-jun-08’,’wed’) from dual;

Sql> Select last_day(sysdate) from dual;

Sql> select trunc(to_date(‘03-jun-08’),’year’) from dual;

Sql> select last_day(sysdate) from dual;

AGGREGATE FUNCTION:

1. Count
2.  avg
3. max/min
4. sum

SYNTAX

Count: TO COUNT NO OF RECORDS

Sql> select count(*) from tablename;

Sql> select count(distinct columnname) from table name

AVG: TO FIND AVERAGE VALUE FROM THAT COLUMN

Sql> select avg(columnname)from tablename;

MAX/MIN VALUE FROM THAT COLUMN:

Sql> select max(columnname)from tablename

Sql> select min(columnname)from tablename

SUM: SUM OF ALL VALUES FROM THAT COLUMN

Sql> select SUM(column name) from tablename

ORDER BY , GROUP BY AND LOGICAL FUNCTIONS

Group by Clause:

Sql> select columnname from tablename groupby columnname

Order by functions:

1.ASC (ASCENDING ORDER)

2.DESC (DESCENDING ORDER)

Sql>select colname from tablename order by colunmname desc;

Sql>select colname from tablename order by colunmname asc;

LOGICAL FUNCTIONS:

1. ALL RELATIONAL OPERATORS  (<,>,=,<>)
2. LIKE,NOT LIKE,IS NULL, IS NOT NULL,IN, NOT IN,BETWEEN,NOT BETWEEN

SYNTAX:

sql> select colname from tablename where colname like(‘mo%’)

sql> select colname from tablename where colname not like(‘mo%’)

sql>select *from tablename wherer columnname is null

sql>select *from tablename where columnname is not null

sql>select *from tablename where columnname in( value1,value2,..valuen.)

sql>select *from tablename where columnname not in( value1,value2,..valueN.)

sql>select *from tablename where columnname between(value1) and (value2)

sql>select *from tablename where columnname not between(value1) and (value2)

RESULT

Thus the DML, DCL and TCL commands have been executed successfully.