HEADSTRONG INTERVIEW QUESTIONS

    1.What is rowid and rownum?Is it possible to select a row using a rowid or rownum? 

        If Yes, give me the query.

  

  Ans) Rowid is used to identify the first duplicate record that has appeared in the database.

Always it is a Hexadecimal digit.

For Ex:

Name	Age	Address	Empno
A	25	ABC	100
A	25	ABC	100
A	25	ABC	100

The First appeared row is identified by using row id .The scenario is like to keep the first record and delete all the duplicate records then the SQL goes like :

QUERY : 

Delete from tab_name where row id not in (select min(rowid) from tab_name )

ROWNUM :

In Oracle , to retrieve some set of records :

SELECT * FROM TAB_NAME WHERE ROWNUM<=10

Rowid --àit is the address that is physically stored in the disk.

Rownum-àIt is the Memory address stored in RAM.

    2.       what is Normalization?

Ans)3 NF Normal Forms.

 It follows 2^nd normal form

 The related columns in a table are only dependent on the Primary key only.

 The Referential integrity is maintained.

    3.       Explain about Outer joins and Left outer joins?

Ans)LEFT OUTER JOIN AND RIGHT OUTER JOIN 

Records from /the left table will appear with the matching records from the right table and null values.

     4.       select employees from emp table whose dept is 10 and salary is higher than 2 lakhs.

Ans)SELECT EMP_NAMES FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO AND sal >200000

     5.       what is OLTP and OLAP

Ans)OLTP-Online Transaction processing –Production and Transaction data .ex:BANK

OLAP-Online Analytical Processing

OLTP	OLAP
Highly Normalized database	De-Normalized database
Redundancy issues	No Redundancy Issues
Volumes of data is less comparing to OLAP	Tons of data
Source for the OLAP Systems	Source for Reporting and Analysis

7. Is OLTP and OLAP are normalized? Justify the need for normalization in both the case.

Ans)OLTP is Highly Normalized.OLAP is De-Normalized.

The need is as the data is huge for analysis , the read operation becomes easy only when the joins are less.

To remove redundancy and update anamolies.

First, in order to answer this you have to understand what normalization is. Normalization is the process of removing duplicate information and dependent information from a database. 

Lets say for example you have two tables, one called 'employees' and another called 'addresses'. In the employees table you include a field called 'city' and you also include this field in the addresses table. The reason you might do something like this is that you want to be able to get a basic idea of where your employee lives without having to look up the address so you include it in both locations.

Why is this bad? well it should be obvious but here it is. Its bad because it allows for inaccuracies. What if the employee moves and changes his address and you forget to change the city listed in the employees table (or your system crashes while you trying to do it)

the proper way to do this would be to not have the 'city' field in the employee table and instead have a reference to the address table. When querying this info you need to join the two tables and this will ensure that you always have the correct info.

8. what is View and materialized view? Explain the need for both

Ans)View is permission to view the table at a particular level.

Materialized view is refreshed aggregated data .The dba would set the time limit on these tables so that it refreshes for that particular interval and the aggregation is done on its base table there by giving aggregated results.

9. UNIX: Grep, SED

Ans)

Grep iss a search command.Used to find the files having some words or strings in it.

For Ex: grep abc /

Would list all the file names that have the word in them (searches only for files under root directory).

SED ‘S/ABC/DEF/g’ abc.txt

Substitiutes the word ABC by DEF in the file abc.txt

10. what is ETL and what you do in your testing.(Explain the process how we are doing in moving the data from source to target)

Ans)Already answered.

11. Difference between Oracle and Teradata

Ans)Parallel processing is followed in Teradata.

Round 2:

=======

   1.       How you will validate the data from source to target.

Ans)Already told in other interview questions.

   2.       Write testing lifecycle

Ans)Requirement Gathering

Analysis and Design

Preparing Test scenario and Test cases

Execution

Defects tracking and management.

   3.       what is order by clause

Ans)Order by clause is used to arrange one or more columns in ascending or descending column.

   4.       what is Test plan and write its components

Ans)What to test,How to test , who will test

Time taken to validate the business

Resource In the project and test criterion and risk and contingency planning  and management

5.  write a query to get the students who belongs to dept ‘Electronics’ and total bill for the year 2012.

Stud
Stud name	Dept id		Dept id
			Dept id	Dept name
Hostel
Stud name	Year	Bill
A	Jan 2012	3000
B	Jan 2012	4000
A	Feb 2012	3000
Ans) SELECT STUDENT_NAME,SUM(BILL) FROM STUD WHERE DEPT.DEPT_NAME=’ELECTRONICS’ AND STUD.STUD_NAME=HOSTEL.STUD_NAME GROUP BY STUDENT_ NAME

   6.       what is another name of QC 11

Ans)APPLICATION LIFE CYCLE MANAGEMENT.

   7.       what are the different tabs in QC 11

 Ans)TEST PLAN , TEST LAB , RESOURCES ,DEFECTS,REPORTS

   8.       What are different severities and explain each

Ans)CRITICAL HIGH LOW MEDIUM

   9.       UNIX: How to search a file.

Ans)find filename path

Find –n *abc.txt \dir\path

   10.      is it possible to combine multiple operations together in one executiton. If Yes how to do?

Ans)YES

11. how to send the result of multiple opeartions to 1 file.

Ans)file 1 file 2 >>abc.txt

12. write syntax for a PLSQL procedure

Ans)Createor replace procedure (proc_name)

As

Begin

Select * from tab_name

End proc

13. How to call or run this procedure.

Ans)Exec proc_name

14. what are different schemas.

Ans)STAR AND SNOWFLAKE SCHEMA