RSS

Tag Archives: Simple SQL learning

Simple SQL learning.

17 Apr

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;

 
Leave a comment

Posted by on April 17, 2013 in HOME, MANUAL TESTING

 

Tags: , , , , , , , , , , , , , ,

Sample ORACLE queries.

17 Apr

ORACLE QUERIES

1) Display empno and total sal fro each employee

    Select empno, sal+comm from emp;

 2) Display ename and annual sal for all employees

    Select ename,12*sal+nvl(comm.,0) annualsal from emp;

 3) Display the names of all employees working as clerks and drawing a salary more than 3000

     Select ename from emp where job=’CLERK’ and sal>3000;

 4) Display empno, names for employees who earn commission

     Select empno,ename from emp where  comm is not null and comm>=0;

 5) Display the names of employees who are working as clerk, salesman or analyst and drawing a salary more than 3000

    select ename from emp where job in (‘CLERK’,’SALESMAN’,ANALYST’) and sal>3000

 6) Display the names of employees who are working in the company for past 5 years

    select ename from emp where sysdate – hiredate > 5*365;

 7) Display the list of employees who joined the company before 30th June 90 or after 31st Dec 90

     select * from emp where hiredate < ’30-jun-1990’ and hiredate>’31-dec-90’;

 8) Display current date

    select  sysdate from dual;

 9) Display the list of users in your database ( using log table)

    select * from dba_users;

10) Display the names of all tables from the current user

    select * from tab;

 11) Display the name of the current user

     show user;

 12) Display the names of employees working in department number 10 or 20 or 40 or employees working as clerks, salesman or analyst

  select ename from emp where deptno in( 10,20,40) or job in ( ‘CLERK’,SALESMAN’,ANALYST’)

13) Display the names of employees whose name starts with alphabet S  or ends with alphabet S or whose names have second alphabet S in their names

      select ename from emp where ename like ‘S%’ or ename like ‘%S’ or ename like ‘_S%’;

 14) Display the name of employees whose name is exactly five characters length.

      Select ename from emp where length(ename)=5;

15) Display the names of employees who are not working as manager

      select ename from emp where empno not in(select mgr from emp));

16) Display the employees who are not working as salesman or clerk

       select * from emp where job not in (‘SALESMAN’,’CLERK’);

17) Display all rows from emp table. The system should wait after every screen full of information

       set pause on;

 18) Display the total number of employees working in the company

      select count(*) from emp;

 19) Display the total salary being paid to all employees

     select sum(sal) + sum( nvl(comm, o)) from emp;

 20) Display the max,min,avg salary from emp table

      select max(sal), min(sal), avg(sal) from emp;

 21) Display the max,min,avg salary being paid to department number 20 or job is manager

      select max(sal),min(sal),avg(sal) from emp where deptno=20 or job=’MANAGER’;

 22) Display the names of employess in order of salary (lowest to highest)

     select enames from emp order by sal;

22) Display the names of employees in  descending order  of salary (highest to lowest)

     select enames from emp order by sal desc;

 23) Display the names of the employees along with their annual salary (sal*12). The name of the employees earning highest annual salary should appear first.

     Select ename,12*(sal+nvl(comm,0)) annualsal from emp order by 12*(sal+nvl(comm,0)) desc;

 24) Display deptno and total number of employees working with each group;

      select deptno, count(empno ) from emp group by deptno;

 25) Display job and total number of employees working with each group;

      select job, count(empno ) from emp group by job;

 26) Display deptno and total salary for each group

      select deptno, sum(sal)   from emp group by deptno;

 27) Display deptno, and max,min,avg sal for each deptno

     select deptno, max(sal), min(sal), avg(sal) from emp group by deptno;

 28) Display the department numbers with more than three employees in each dept.

     select deptno, count(*) from emp group by deptno  having count(*)>3;

29) Display the name of employees who earns the highest salary

     select name, sal from emp where sal=(select max(sal) from emp);

30) Display the name of salesman who earns a salary more than the highest salary of any clerk

     select name,sal from emp where job=’SALESMAN’  and sal>(select max(sal) from emp where job=’CLERK’);

31) Display the names of clerks who earn salary more than that of james of that of sal lesser than that of scott

    select names,sal from emp where job=’CLERKS’ and  sal>(select sal from emp where ename=’JAMES’) and sal<(select sal from emp where ename=’SCOTT’);

 32) Display the employees who earn highest salary in their respective departments.

      Select * from emp e where sal=(select max(sal) from emp where deptno=e.deptno);

 33) Display the employees who earn highest salary in their respective job groups

   select * from emp e where sal in(select max(sal) from emp group by job having e.job=job);

 34) Display the names who are working in accounting department.

   Select ename from emp where deptno=(select deptno from dept where dname=’ACCOUNTING’;

35) Display the employee names who are working in Chicago

   select ename from emp where deptno=(select deptno from dept where loc=’CHICAGO’);

36) Display the job grops having total salary greater than the maximum salary for managers

   select job, sum(sal) from emp group by job having sum(sal)> (select max(sal) from emp where job=’MANAGER’);

 37) Display the names of employees from department number 10 with salary greater than that of any employee working in other departments.

   Select ename,sal,deptno from emp e where deptno=10 and sal> any(select sal from emp where e.deptno!=deptno);

 38) Display the names inn upper case, lower case, proper case, length of name

  select upper(ename),lower(ename),initcap(ename), length(ename) from emp;

 39) Display the length of all the employee names

    select sum(length(ename)) from emp;

 40) Display the name of employee concatenated with empno

    select ename || empno from emp;

 41) use appropriate function and extract 3 characters starting from 2 char from the following string “ORACLE”

   select substr(‘ORACLE’,2,3) from dual; è rac

 42) Find the first occurance of character  ‘a’ from the following string ‘computer maintenance corporation’

  select instr(‘computer maintenance corporation’,’a’,1,1) from dual;

 43) Replace every occurance of alphabet A with B in the string ALLEN’s

   select replace(‘ALLENS’, ‘A’,’B’) from dual;

 44) Display the information from emp table wherever job ‘manager’ is found it should be displayed as BOSS

   select empno,ename, replace(job,’MANAGER’,BOSS’) job from emp;

 45) Display your age in days

  select  round(sysdate – to_date(’15-aug—1947’)) from dual;

46) Display your age in months

    select floor( months_between(sysdate,’15-aug-1947’)) from dual;

 47) Display the current date as 15th august Friday nineteen forty seven

    select to_char(sysdate, ‘ddth month day year ‘) from dual;

 48) Display the following output for each row from emp table as ‘scott has joined the company on Wednesday 13 the august nineteen ninety’

   select ename || ‘has joined the company on ‘ || to_char(hiredate, ‘day ddth month year’) from dual;

49) Find the date of nearest Saturday after current day

    select next_day(sysdate, ‘Saturday’) from dual;

50) Display the current time

     select to_char(sysdate,’hh:mm:ss’) from dual;

51) Display the date three months before the current date

     select add_months(sysdate,-3)  from dual;

52) Display the common jobs from department number 10 and 20

     select job from emp where deptno=20 intersect select job from emp where deptno=10

53) Display the job which are unique to deptno 10

    select job from emp where deptno=10 minus select job from emp where deptno!=10

54) Display the details of those who do not have any persons working under them

     select empno from emp where empno not in (select mgr from where mgr is not null)

 55) Display the employees whose manager names in JONES

  select * from emp where mgr=(select empno from emp where ename=’JONES’);

 56) Display those employees whose salary is more than 3000 after giving 20% increment

   select * from emp where sal+sal*20/100> 3000;

 57) Display the all the employee name with their department names

   select ename,dname from emp e, dept d where e.deptno=d.deptno;

 58) Display employee name, deptno,sal and comm. For those sal in between 2000 and 5000 while location is Chicago

    select ename,deptno ,sal,comm. From emp deptno=(select deptno from dept where loc=’CHICAGO’) and sal between 2000 and 5000;

 59) Display those employees whose salary greater than his manager salary.

   Select * from emp e where sal>(select sal from emp where empno=e.mgr);

60) Display those employees who are not working under any manager

  select * from emp where mgr is null or empno=mgr;

61) Update the salary of each employee by 10% increments that are not tligible for commission

    update emp set sal=(sal+10/100) where comm. Is null

 62) Delete those employees who joined the company before 31-dec-82 while there dept location is NEWYORK or CHICAGO

    delete from emp where hiredate<’31-dec-1982’ and deptno in (select deptno from dept where loc in (‘NEW YORK’,’CHICAGO’));

 63) Display those employees whose manager names is Jones, and also display there manager name

   select e.empno,e.ename,m.ename MANAGER from emp e ,emp m where e.mgr=m.empno and e.ename=’JONES’;

 64) Find out top 5 earners of company

   select * from emp e where 5>(select count(*) from emp where sal>e.sal) order by sal desc;

 65) Display those employees whose salary is equal to average of maximum and minimum

     select * from emp where sal=(select  (max(sal)+min(sal))/2 from emp);

 66) Display dname where at least 3 are working and display only dname

   select dname from dept where deptno in (select deptno from emp group by deptno having count(*)>3);

 67) Delete those dept where no employees working

      delete from emp where deptno not in(select deptno from dept);

 68) Display those employees whose salary is ODD value

     select * from emp where mod(sal,2)=1;

 69) Display those employees who joined in the company in the month of DEC?

    select * from emp where upper(to_char(hiredate,’mon’))=’DEC’;

70) Display those employees whose name contains ‘A’

   select * from emp where instr(ename,’A’,1,1)>0;

71) Find out the employees who earned the highest sal in each job typed sort in descending sal order

   select * from emp e where sal=(select max(sal) from emp where job=e.job)

72) Display the dept where there are no employees

     select * from dept where deptno not in (select distinct(deptno) from emp);

73)Delete the rows of employees who are working in the company for more than 2 years

    delete from emp where floor(sysdate-hiredate)>2*365;

 74) 3rd maximum salary of employee

            select distinct sal from emp a where 3=(select count(distinct sal) from emp b where a.sal<b.sal);

 75) Deleting duplicate rows from a table

     delete emp where rowid not in (select max(rowid) from emp group by empno);

 76) display first 5 max salaries

     select sal from (select abs(sal) sal from emp group by sal) where rownum<6;

 
Leave a comment

Posted by on April 17, 2013 in HOME, MANUAL TESTING

 

Tags: , , , , , , , , ,