CREATION OF TABLE:
Syntax:
SQLl> create table <table name> (column name data type(size), column name datatype(size));
Example:
SQLl> create table emp1 (empno number(4),ename varchar2(10),job varchar2(9));
HOW TO INSERT VALUES:
Syntax:
SQL> insert into <table name> values(data1,data2);
Example:
SQL> insert into emp1 values(100,’arif’,’dba’);
Syntax:
SQL> insert into <table name> values(&data1,&data2);
Example:
SQL> insert into emp1 values(&empno,’&ename’,’&job’);
Syntax:
SQL> insert into <table name>(column list) values(data1,data2);
Example:
SQL> insert into emp1 (empno) values (300);
HOW TO DROP A TABLE:
Syntax:
SQL> drop table <table name>
Example:
SQL>drop table emp1;
HOW TO CREATE A TABLE BY COPYING FROM OTHER TABLE :
Syntax:
SQL>create table <new table name> (column list) as select */(column list) from <table name>[where (condition)]
Example:
SQL>create table emp2 as select * from emp;
HOW TO SEE TABLE FROM THE DATA BASE:
Syntax:
SQL> select * from tab;
HOW TO GET DATA FROM THE TABLE:
Syntax:
SQL>select */ (column list) from <table name>[where (condition)]
Example:
SQL>select * from emp;
SQL>select empno,ename from emp;
HOW TO INSERT RECORDS IN A ALREADY CREATED TABLE BY SELECTING FROM ANOTHER TABLE
Syntax:
SQL>insert into <table name> select */(column list) from <table name>[where (condition)]
Example:
SQL>insert into emp1 as select * from emp;
UPDATE ROWS IN A TABLE:
Syntax:
SQL>update <table name> set column name= value [where (condition)]
Example:
SQL>update emp set comm.=500 where comm.=null;
HOW SPECIFY INTIGRITY CONSTRAINT:
Unique constraint:
Syntax:
SQL> create table <table name> (column name data type(size)constraint
<constraint name> unique, column name datatype(size));
Example:
SQL> create table emp1 (empno number(4)constraint uni_emp unique ,ename varchar2(10),job varchar2(9));
Not null constraint:
Syntax:
SQL> create table <table name> (column name data type(size)constraint
<constraint name> not null, column name datatype(size));
Example:
SQL> create table emp1 (empno number(4)constraint nn_emp not null ,ename varchar2(10),job varchar2(9));
Primary key constraint
Syntax:
SQL> create table <table name> (column name data type(size)constraint
<constraint name> primary key, column name datatype(size));
Example:
SQL> create table emp1 (empno number(4)constraint pk-emp primary key ,ename varchar2(10),job varchar2(9));
Check integrity constraint:
Syntax:
SQL> create table <table name> (column name data type(size)constraint
<constraint name> check(value), column name datatype(size));
Example:
SQL> create table emp1 (empno number(4),ename varchar2(10),job varchar2(9) constraint ck-emp check (job in(‘salesman’,’clerk’,’analyst’));
foreign key constraint:
Syntax:
SQL> create table <table name> (column name data type(size)constraint
<constraint name> reference (parent table name(column name)) , column name datatype(size));
Example:
SQL> create table dept1 (deptno number(4)constraint fk-emp reference emp(deptno) ,ename varchar2(10),job varchar2(9));
Cascade deletion:
Syntax:
SQL> create table <table name> (column name data type(size)constraint
<constraint name> reference (parent table name(column name))on delete cascade , column name datatype(size));
Example:
SQL> create table dept1 (deptno number(4)constraint fk-emp reference emp(deptno)on delete cascade ,ename varchar2(10),job varchar2(9));
HOW TO ADD AND INTIGRITY CONSTRAINT TO A COLUMN IN A TABLE
Syntax:
SQL> alter table <table name>add constraint <constraint name>
<constraint type>(column name)
Example:
SQL> alter table emp1 add constraint pk primary key(empno);
HOW TO DROP A PARENT TABLE DIRECTLY:
Syntax:
SQL> Drop table <table name>cascade constraints
HOW TO DELETE ROWS FROM A TABLE:
Syntax:
SQL> delete from <table name> [where (condition)]
Example:
SQL> delete from emp where deptno=10;
COMPOSITE FOREIGN KEY:
Example:
SQL> create table emp (empno number(5),ename varchar2(10),job varchar2(10),deptno number(2),sal number(7,2) foreign key(empno,deptno),reference dept(eno,dno);
HOW TO MODIFY THE TABLE:
Syntax:
SQL> alter table <table name>add (column name datatype(size) default integrity constraint);
Example:
SQL> alter table dept add( loc varchar2(20));
HOW TO SET DEFAULT VALUE FOR A COLUMN
Syntax:
SQL> create table dept1 (deptno number(4),ename varchar2(10),loc varchar2(9)default ‘string’);
Example:
SQL> create table dept1 (deptno number(4),ename varchar2(10),loc varchar2(9)default ‘khammam’);
HOW TO MODIFY DATA TYPE OF COLUMN:
Syntax:
SQL> alter table <table name>modify (column name datatype(size) default integrity constraint);
Example:
SQL> alter table dept modify( loc varchar2(20));
HOW TO ENABLE & DESABLE OF INTIGRITY CONSTRAINTS:
Syntax:
SQL> alter table <table name>enable or disable (constraint
<constraint type>(col1,col2….);
Example:
SQL> alter table emp enable (primary key);
HOW TO DROP INTIGRITY CONSTRAINTS:
Syntax:
SQL> alter table <table name>drop constraint <constraint name> (col1,col2…)
Example:
SQL> alter table emp drop primary key;
SQL> alter table emp drop unique (empno);
SQL> alter table emp drop constraint nn not null(empno);
TRUNCATE COMMAND
SQL>truncate table dept;
AND, OR, IN, NOT IN OPERATORS:
SQL>select * from emp where sal>1000 AND job=’cleark’;
SQL>select * from emp where sal>1000 OR job=’cleark’;
SQL>select * from emp where job IN(‘salesman’,’clerk’);
SQL>select * from emp where job NOT IN(‘salesman’,’clerk’);
DISTINCT CLAUSE:
SQL> select distinct(job) from emp;
ORDER BY CLAUSE :
SQL>select * from emp order by sal;
SQL>select * from emp order by sal desc;
NUMBER FUNCTIONS:
1) select abs(-20) from dual; 20
2) select ceil(1.5) from dual; 2
3) select floor(1.5) from dual; 1
4) select round(100.856,2) from dual; 100.86
5) select round(100.856)from dual; 101
6) select trunc(100.856,2) from dual; 100.85
7) select trunc(100.856) from dual; 100
8) select sqrt(25) from dual; 5
9) select mod(25,5) from dual; 0
10) select log(10,2) from dual; 0.30103
11) select ln(2) from dual; 0.69314718
12) select sin(30) from dual; same as sin, cos, tan, sec ,cosec, sinh, cosh…………
13) select exp(2) from dual; 7.3890561
14) select power(5,2) from dual; 25
CHARECTOR FUNCTIONS
1) select initcap(‘arif’) from dual; Arif
2) select upper(‘arif’) from dual; ARIF
3) select lower(‘ARIF’) from dual; arif
4) select lpad(‘arif’,10,’*’) from dual; ******arif
5) select rpad(‘arif’,10,’*’) from dual; arif******
6) select ltrim(‘*****arif’,’*’) from dual; arif
7) select rtrim(‘arif******’,’*’) from dual; arif
8) select replace(‘arif ur’,’ur’,’ur rahman’) from dual; arif ur rahman
9) select translate(‘arif ur’,’ur’,’ur rahman’) from dual; arif ur
10) select * from emp where soundex(ename)=soundex(‘scott’); scott
11) select substr(‘arif rahman’,6,6) from dual; rahman
12) select ascii(‘A’) from dual; 65
13) select instr(‘arif’,’r’,1) from dual; 2
14) select length(‘arif’) from dual; 4
DATE FUNCTIONS
1) select to_char(sysdate,’dd-mm-yyyy’) from dual; 20-03-2005
2) select months_between(to_date(’20-03-2005′,’dd-mm-yyyy’),
to_date(’20-01-2005′,’dd-mm-yyyy’))from dual; 2
3) select months_between(sysdate,to_date(’05-07-1978′,’dd-mm-yyyy’))from dual;
4) select to_date(’20-03-2005′,’dd-mm-yyyy’),sysdate-165 from dual;
5) select add_months(sysdate,120)from dual;
6) select next_day(sysdate,’sunday’) from dual;
7) select last_day(sysdate) from dual;
8) select round(to_date(’05-07-1978′,’dd-mm-yyyy’),’month’) from dual; 01-JUL-78
9) select round(to_date(’05-07-1978′,’dd-mm-yyyy’),’year’) from dual; 01-JAN-79
10) select to_char(sysdate,’ddth-mm-yyyy’) from dual; 20th-03-2005
11) select to_char(sysdate,’ddsp-mmsp-yyyy’) from dual; twenty-three-2005
OTHER FUNCTIONS:
1) select job,greatest(sal,comm) from emp;
2) select vsize(ename),ename from emp;
3) select nvl(comm,0) from emp;
GROUP FUNCTIONS:
1) select count(distinct(job)) from emp;
2) select sum(sal) from emp where job=’manager’and deptno=10;
3) select max(sal) from emp;
4) select min(sal) from emp;
5) select avg(sal) from emp;
6) select stddev(sal) from emp;
7) select variance(sal) from emp;
GROUP BY CLAUSE:
1) select sum(sal),job from emp group by job;
2) select sum(sal),job from emp group by job having job=’MANAGER’;
3) select sum(sal),job,count(*) from emp group by job;
4) select sum(sal),job,count(*) from emp group by job having sum(sal)>=5000;
JOINS:
1) select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;
2) select e.empno,e.ename,d.dname,e.deptno from emp e,dept d where e.deptno=d.deptno;
3) select empno,ename,job from emp where job=’MANAGER’ and empno not in(select distinct(mgr) from emp;
SUB QUERY:
1) select * from emp where job=(select job from emp where ename=’BLAKE’);
2) select * from emp where sal=(select min(sal) from emp);
3) select * from emp where sal in(select min(sal) from emp group by deptno);
4) select * from emp where sal >some(select avg(sal) from emp group by deptno);
5) select * from emp e where exists(select empno from emp where emp.mgr=e.empno);
SET OPERATORS:
1) select * from table1
union
select * from table2;
2) select * from table1
union all
select * from table2;
3) select * from table1
intersect
select * from table2;
4) select * from table1
minus
select * from table2;
VIEW:
Syntax:
SQL> create [or replace][force] view < view name> (col1,col2……) as select columns from <table name>
Example:
SQL> create or replace view emp2 as select * from emp;
SEQUENCE:
Syntax:
SQL> create sequence[user schema]< sequence name>
[increment by n]
[start with I]
[max value n]
[min value n]
[cycle/no cycle]
[cache/no cache]
[order/ no order]
Example:
SQL> create sequence s1
increment by 1
start with 10
minvalue 1
maxvalue 1000
nocycle
nocache;
USER AND SECURITY:
Syntax:
SQL> create user <user name>
[identified by password]
[default table space]
[temporary table space]
[quota =on x system]
[quota=any]
[profile (name)]
Example:
SQL> create user arif indentifed by maga;
System privileges: create session, create table, create view, create index
Privileges: select, insert, update, delete, alter, index, references, all
Change user password: alter user <user name> identified by <new password>
Grant: grant <privilege>on <object name> to <user name>
SYNONYM: create[public] synonym <synonym name> for <table name>
Ex: create synonym emp1 for emp;
Drop synonym: drop synonym <synonym name>
Drop synonym emp2;
Software Testing 