1. Informatica and types of transformation available.

2. Difference b/w priority and severity.

Priority is the IMPORTANCE of the bug identified. Severity pertains to how CRITICAL the bug is Priority is Business and Severity is Technical severity: This is assigned by the tester.severity of a defect is set based on the issue's seriousness..it can be stated as mentioned show stopper:4,Major defect:3,Minor defect:2,Cosmetic:1 setting values for these four categories can be again defined by the organisation based on their views. showstopper: this have a higher severity as u cannot proceed further testing with the application testing. Major: If there are any main defect based on the functionality . Minor: If there is any error in the functionality of one object under one functionality Cosmetic: any error based on the look and feel of the system,or improper location of the object(something based on the design of the web page) Priority: this will be set by the team lead or the project lead. based on the severity and the time constraint that the module has the priority will be set

3. Software testing life cycle.

Software Testing Life Cycle (STLC):Specifies the various stages of testing. 1.Requirements stage a.Requirement Specification documents b.Functional Specification documents c.Use case Documents d.Test Trace-ability Matrix for identifying Test Coverage 2.Test Plan a.Test Scope, Test Environment b.Different Test phase and Test Methodologies c.Manual and Automation Testing d.Defect Mgmt, Configuration Mgmt, Risk Mgmt. Etc 3.Test Design a.Test Case preparation. b.Test Traceability Matrix for identifying Test Cases c.Test case reviews and Approval

4.Test Execution a.Executing Test cases b.Capture, review and analyze Test Results 5.Defect Tracking a.Find the defect & tracking for its closure. 6.Bug Reporting a.Report the defect on tool/Excels 7.Regression/retesting

4. Defect Life cycle.

Bug Cycle

Bug

Close Not Resolved (rejected) Resolve d new Reported (assign) Developer/ engineer

Verify

Resolve the bug (resolved)

5. Some questions on SQL

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT b.sal) FROM EMP B WHERE a.sal<=b.Sal); Enter value for n: 2 2nd highest salary

to find highest salary

select max(salary ) from emp table where sal<(select max (salary)from emp table)
Difference between group by clause and having clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns Aggregate fns are like SUM,COUNT,MIN,MAX and AVG The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns The syntax for the HAVING clause is:

Several types of Oracle constraints can be applied to Oracle tables to enforce data integrity, including: Oracle "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to the table definition to ensure the validity of the region column.

Not Null Constraint: This Oracle constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time. Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key. References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times. At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table. Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.

6. How to delete duplicate records in a table in Oracle.

DELETE FROM our_table WHERE rowid not in (SELECT MIN(rowid) FROM our_table GROUP BY column1, column2, column3... ;

7. How get the name from an email id. substr('[email protected]',instr('[email protected]','@')+1) 8. Concepts of stored procedures ( diff. Between stored procedure and function)

Function : 1. Should return atleast one output parameter.Can return more than one parameter using OUT argument. 2. Parsed and compiled at runtime. 3.Cannot affect the state of database. 4.Can be invoked from SQL statement e.g. SELECT. 5. Functions are mainly used to compute values. Procedure: 1. Doesn't need to return values but can return value. 2.Stored as a pseudo-code in database i.e. compiled form. 3.Can affect the state of database using commit etc. 4.Cannnot be invoked from SQL statements e.g. SELECT. 5.Procedures are mainly used to process the tasks. A stored procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth. User-defined function A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

9. Concepts of Data warehousing.

10.

Type of joins with examples.

INNER JOIN - only rows satisfying selection criteria from both joined tables are selected.

SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID;

Or

SELECT * FROM employee, department

WHERE employee.DepartmentID = department.DepartmentID;

LEFT OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from left joined table are being kept along with Nulls instead of actual right joined table values.

SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

RIGHT OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from right joined table are being kept along with Nulls instead of actual left joined table values.

SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

FULL OUTER JOIN - rows satisfying selection criteria from both joined tables are selected as well as all remaining rows both from left joined table and right joined table are being kept along with Nulls instead of values from other table.

SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

Self-join
A self-join is joining a table to itself.[2] This is best illustrated by the following example.

11. Some testing scenario based questions. 12. Few questions on HPQC. 13. Questions on UNIX Unix Basics

14. About grep

What is grep command?

Finds text within a file.

15. How to grep ^M characters. 16. How to remove ^M characters in a huge file in UNIX.

tr -d '\r' < infile.txt > outfile.txt tr - d will accept the source file .. make a second file and remove all ^M characters in that
17. What is the process to find and kill a process in UNIX. i) Find the process id # ps aux | grep lighttpdOutput:

Or

ii)

kill process using PID (process id)

# kill 3486

# pidof lighttpdOutput:

18. About shell script basics.

Dataware Concepts
OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multiaccess environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). - OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).

Differences
Data warehouse database OLTP database

Designed for analysis of business measures by categories and attributes Optimized for bulk loads and large, complex, unpredictable queries that access many rows per table.

Designed for real time business operations. Optimized for a common set of transactions, usually adding or retrieving a single row at a time per table.

Optimized for validation of Loaded with consistent, valid incoming data during data; requires no real time transactions; uses validation validation data tables. Supports few concurrent users relative to OLTP Supports thousands of concurrent users.

There are two leading approaches to storing data in a data warehouse

In a dimensional approach, transaction data are partitioned into either "facts", which are generally numeric transaction data, or "dimensions", which are the reference information that gives context to the facts. For example, a sales transaction can be broken up into facts such as the number of products ordered and the price paid for the products, and into dimensions such as order date, customer name, product number, order ship-to and bill-to locations, and salesperson responsible for receiving the order. A key advantage of a dimensional approach is that the data warehouse is easier for the user to understand and to use. Also, the retrieval of data from the data warehouse tends to operate very quickly. The main disadvantages of the dimensional approach are: 1. In order to maintain the integrity of facts and dimensions, loading the data warehouse with data from different operational systems is complicated, and 2. It is difficult to modify the data warehouse structure if the organization adopting the dimensional approach changes the way in which it does business. In the normalized approach, the data in the data warehouse are stored following, to a degree, database normalization rules. Tables are grouped together by subject areas that reflect general data categories (e.g., data on customers, products, finance, etc.). The main advantage of this approach is that it is straightforward to add information into the database. A disadvantage of this approach is that, because of the number of tables involved, it can be difficult for users both to: 1. join data from different sources into meaningful information and then 2. access the information without a precise understanding of the sources of data and of the data structure of the data warehouse.

In the bottom-up approach data marts are first created to provide reporting and analytical capabilities for specific business processes. Though it is important to note that in Kimball methodology, the bottom-up process is the result of an initial business oriented Top-down analysis of the relevant business processes to be modelled. The top-down design methodology generates highly consistent dimensional views of data across data marts since all data marts are loaded from the centralized repository. Top-down design has also proven to be robust against business changes. Generating new dimensional data marts against the data stored in the data warehouse is a relatively simple task Fact Table In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema or a snowflake schema, surrounded by dimension tables In data warehousing, a dimension table is one of the set of companion tables to a fact table. The fact table contains business facts or measures and foreign keys which refer to candidate keys (normally primary keys) in the dimension tables.

Snowflake schema

the snowflake schema is represented by centralized fact tables which are connected to multiple dimensions. In the snowflake schema, however, dimensions are normalized into multiple related tables whereas the star schema's dimensions are denormalized with each dimension being represented by a single table. When the dimensions of a snowflake schema are elaborate, having multiple levels of relationships, and where child tables have multiple parent tables ("forks in the road"), a complex snowflake shape starts to emerge. The "snowflaking" effect only affects the dimension tables and not the fact tables.

NVL Command
In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

Example

select supplier_id, NVL(supplier_desc, supplier_name) from suppliers; This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.

Decode Function In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement. The syntax for the decode function is: decode( expression , search , result [, search , result]... [, default] ) expression is the value to compare. search is the value that is compared against expression. result is the value returned, if expression is equal to search. default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

example: You could use the decode function in an SQL statement as follows: SELECT supplier_name, decode(supplier_id,10000, 'IBM', 10001, 'Microsoft', 10002, 'Hewlett Packard', 'Gateway') result FROM suppliers; The above decode statement is equivalent to the following IF-THEN-ELSE statement: IF supplier_id = 10000 THEN result := 'IBM';

ELSIF supplier_id = 10001 THEN result := 'Microsoft'; ELSIF supplier_id = 10002 THEN result := 'Hewlett Packard'; ELSE result := 'Gateway'; END IF;

The decode function will compare each supplier_id value, one by o

To Update character UPDATE RTLGRPCDPRDCDGLDEPTASSN_OS SET ORACLE_GL_ACCT_NBR = REPLACE (ORACLE_GL_ACCT_NBR,'0','*') To view 0 as * SELECT REPLACE(ORACLE_GL_ACCT_NBR,'0','*')FROM RTLGRPCDPRDCDGLDEPTASSN_OS Substr function in Oracle

The substr function is a function that returns a substring from a string.

syntax
substr([input],[start],[length]) or substr([input],[start]) or With input the String to take a substring from, start is the starting position where 1 is the first character. (if you pass 0, this will be substituted by 1) and the optional length parameter is the number of characters in the substring. If length is left out, then substr will return the substring from position start till the end of the input-string.

Sample code:
select substr('1234567890',3,2) from dual; will return: '34'. select substr('1234567890',7) from dual; will return: '789

For example: substr('This is a test', 6, 2) would return 'is'

substr('This is a test', 6) substr('TechOnTheNet', 1, 4) substr('TechOnTheNet', -3, 3) substr('TechOnTheNet', -6, 3) substr('TechOnTheNet', -8, 2)

would return 'is a test' would return 'Tech' would return 'Net' would return 'The' would return 'On'

Oracle/PLSQL: Instr Function

In Oracle/PLSQL, the instr function returns the location of a substring in a string. The syntax for the instr Oracle function is: instr( string1, string2 [, start_position [, nth_appearance ] ] ) string1 is the string to search. string2 is the substring to search for in string1. start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1. nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.

Note: If string2 is not found in string1, then the instr Oracle function will return 0.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example: instr('Tech on the net', 'e') instr('Tech on the net', 'e', 1, 1) would return 2; the first occurrence of 'e' would return 2; the first occurrence of 'e'

instr('Tech on the net', 'e', 1, 2) instr('Tech on the net', 'e', 1, 3) instr('Tech on the net', 'e', -3, 2)

would return 11; the second occurrence of 'e' would return 14; the third occurrence of 'e' would return 2.

In Oracle
SELECT SUBSTR('[email protected]',1,INSTR('[email protected]','@') -1 ) FROM dual Return Vikas1410 Explanation start search from 1 position of string, Insrt will return posotion of @ and subsrt will return all the value from posotion 1 to @-1 SELECT SUBSTR('[email protected]',INSTR('[email protected]','@') +1 )FROM dual Return gmail.com Explanation Instr will return position of @ and will all the value after @+1 In teradata SELECT SUBSTRING('[email protected]' FROM 1,INDEX('[email protected]','@')-1 ) Return : biswajit.pal

Substring function in Teradata

The SUBSTRING function is intermediate-level ANSI compliant. Teradata continues to support the alternate SUBSTR syntax as well. Examples SELECT SUBSTRING ('catalog' FROM 5 for 3); Result 'log' SELECT SUBSTR ('catalog',5,3);

Result 'log' Let's look at several more examples showing the results of a SELECT using either SUBSTRING or SUBSTR . Equivalent results are returned in all cases.

SUBSTRING Result

SUBSTR Result

SUBSTRING(catalog FROM 5 FOR 4) SUBSTRING(catalog FROM 0 FOR 3) SUBSTRING(catalog FROM -1 FOR 3) SUBSTRING(catalog FROM 8 FOR 3) SUBSTRING(catalog FROM 1 FOR 0) SUBSTRING(catalog FROM 5 FOR -2)

log ca c 0 length string 0 length string error

log ca c 0 length string 0 length string error

(without a FOR clause)

SUBSTRING(catalog FROM 0) SUBSTRING(catalog FROM 10) SUBSTRING(catalog FROM -1) SUBSTRING(catalog FROM 3)

catalog 0 length string 0 length string talog

catalog 0 length string 0 length string talog

Since the Teradata extension provides equivalent functionality to the ANSI-compliant feature, we recommend that all existing applications replace any use of the SUBSTR function with the SUBSTRING function.

Note - SEL SUBSTRing('[email protected]', 1) will not work have to

pass 2nd parameter in this case Index Function in teradata Similar as insrt function of teradata