Sample ORACLE queries.

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 30^th June 90 or after 31^st 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 15^th 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) 3^rd 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;

 

Basic reasons to automate testing.

Why to Automate?

Reusing the test scripts:
When you want to execute the regression test scripts after every build it makes more sense to automate them. In case of testing web based application there is a more need to automate as the test suite has to be run on various browsers like Internet Explorer, Firefox and other browsers.

Saves time:

Running unattended automated test scripts saves human time as well as machine time than executing scripts manually.
Better use of resource:

While automated scripts are running unattended on machines, testers can do more useful tasks.
Cost Saving:

On test engagements requiring a lot of regression testing, usage of automated testing reduces the people count and time requirement to complete the engagement and helps reduce the costs.

To Automate or Not to Automate?

It is not always advantageous to automate test cases. There are times when manual testing may be more appropriate.

For instance, if the application’s user interface will change considerably in the near future, then any automation would need to be rewritten. Also, sometimes there simply is not enough time to build test automation. For the short term, manual testing may be more effective. If an application has a very tight deadline, there is currently no test automation available, and it’s imperative that the testing get done within that time frame, then manual testing is the best solution.

Decide What Test Cases to Automate

• Repetitive tests that run for multiple builds.
• Tests that tend to cause human error.
• Tests that require multiple data sets.
• Frequently used functionality that introduces high risk conditions.
• Tests those are impossible to perform manually.
• Tests that run on several different hardware or software platforms and configurations.
• Tests that take a lot of effort and time when manual testing.

Create Automated Tests that are Resistant to Changes in the UI

• Automated tests created with scripts or keyword tests are dependent on the application under test.
• The user interface of the application may change between builds, especially in the early stages. These changes may affect the test results, or your automated tests may no longer work with future versions of the application.
• The problem is automated testing tools use a series of properties to identify and locate an object.
• Sometimes a testing tool relies on location coordinates to find the object. For instance, if the location has changed, the automated test will no longer be able to find the object when it runs and will fail.
• To run the automated test successfully, you may need to replace old names with new ones in the entire project, before running the test against the new version of the application.
• However, if you provide unique names for your controls, it makes your automated tests resistant to these UI changes and ensures that your automated tests work without having to make changes to the test itself.
• This also eliminates the automated testing tool from relying on location coordinates to find the control, which is less stable and breaks easily.
• However, automation has specific advantages for improving the long-term efficiency of a software team’s testing processes.

Test automation supports:

• Frequent regression testing
• Rapid feedback to developers during the development process
• Virtually unlimited iterations of test case execution
• Customized reporting of application defects
• Disciplined documentation of test cases
• Finding defects missed by manual testing

Automated tests should be:

 

Concise: Test should be as simple as possible and no simpler.

Self Checking: Test should report its results such that no human interpretation is necessary.

Repeatable: Test can be run repeatedly without human intervention.

Robust: Test produces same result now and forever. Tests are not affected by changes in the external environment.

Sufficient: Tests verify all the requirements of the software being tested.

Necessary: Everything in each test contributes to the specification of desired behavior.

Clear: Every statement is easy to understand.

Efficient: Tests run in a reasonable amount of time.

Specific: Each test failure points to a specific piece of broken functionality (e.g. each test case tests one possible point of failure).

Independent: Each test can be run by itself or in a suite with an arbitrary set of other tests in any order.

Maintainable: Tests should be easy to modify and extend.

Traceable: Tests should be traceable to the requirements; requirements should be traceable to the tests.

 

Test validation in Datawarehouse testing.

VALIDATION Testing Levels: There are several levels of testing that can be performed during datawarehouse testing. Some examples: Constraint testing, Source to target counts, Source to target data validation, Error processing. The level of testing to be performed should be defined as part of the testing strategy.

Constraints Testing: During constraint testing, the objective is to validate unique constraints, primary keys, foreign keys, indexes, and relationships. The test script should include these validation points. Some ETL processes can be developed to validate constraints during the loading of the warehouse. If the decision is made to add constraint validation to the ETL process, the ETL code must validate all business rules and relational data requirements. Depending solely on the automation of constraint testing is risky. When the setup is not done correctly or maintained throughout the ever changing requirements process, the validation could become incorrect and will nullify the tests.

Source to Target Counts (Reconciliation): The objective of the count test scripts is to determine if the record counts in the source match the record counts in the target. Some ETL processes are capable of capturing record count information such as records read, records written, records in error, etc. If the ETL process is being used can capture that level of detail and create a list of the counts, allow it to do so. This will save time during the validation process.

Source to Target Data Validation: No ETL process is smart enough to perform source to target field-to field validation. This piece of the testing cycle is the most labour intensive and requires the most thorough analysis of the data. There are a variety of tests that can be performed during source to target validation.

Below is a list of tests that are best practices:

Threshold testing – expose any truncation that may be occurring during the transformation or loading of data

For example:

Source: table1.field1 (VARCHAR40):

Stage: table2.field5 (VARCHAR25):

Target: table3.field2 (VARCHAR40):

In this example the source field has a threshold of 40, the stage field has a threshold of 25 and the target mapping has a threshold of 40. The last 15 characters will be truncated during the ETL process of the stage table. Any data that was stored in position 26-30 will be lost during the move from source to staging.

Field-to-field testing – is a constant value being populated during the ETL process? It should not be unless it is documented in the Requirements and subsequently documented in the test scripts. Do the values in the source fields match the values in the respective target fields? Below are two additional field-to-field tests that should occur.

Initialization – During the ETL process if the code does not re-initialize the cursor (or working storage) after each record, there is a chance that fields with null values may contain data from a previous record.

For example:

Record 125: Source field1 = Red Target field1 = Red

Record 126: Source field1 = null Target field 1 = Red

Validating relationships across data sets – Validate parent/child relationship(s)

For example:

Source parent: Purple.

Source child: Red and Blue.

Target parent: Purple

Target child: Red and Yellow.

Error Processing: Understanding a script might fail during data validation, may confirm the ETL process is working through process validation. During process validation the testing team will work to identify additional data cleansing needs, as well as identify consistent error patterns that could possibly be diverted by modifying the ETL code. Taking the time to modify the ETL process will need to be determined by the project Manager, development lead, and the business integrator. It is the responsibility of the validation team to identify any and all records that seem suspect. Once a record has been both data and process validated and the script has.