head

Monday, February 18, 2013

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 2nd 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

No comments:

Post a Comment