Ex:no:2 Data Manipulation Language
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:
- Count
- avg
- max/min
- 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:
- ALL RELATIONAL OPERATORS (<,>,=,<>)
- 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.