SQL Server Date Formats

http://www.sql-server-helper.com/tips/date-formats.aspx

count distinct rows

with DateRowCount(col1,CountRows)
as
(
select col1,COUNT(col1)
over (partition by col1) from table1 as CountRows
)
select col1, CountRows
from DateRowCount
group by col1, CountRows
order by col1

Update table from another table

UPDATE table1
SET
col1 = B.col1
, col2 = B.col2
, col3 = B.col3
from table2 B
where B.col4 = table1.col4 and B.col5 = table1.col5
and exists (select 1 from table2 where table2.account = table1.account and table2.memo_number = table1.memo_number )

Delete Duplicate rows

WITH CTE (account,memo_number, DuplicateCount)


AS

(

SELECT account,memo_number, valdate,

ROW_NUMBER() OVER(PARTITION BY account,memo_number ORDER BY valdate desc) AS DuplicateCount

FROM inv1_transactions_bkp

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

GO

Interview Questions

Question

Article I.            Previous Projects

What was your last project?

 Explain the business side of the project?

What was your exact role?

What was the size of the database?

Who was your manager?

Who did you worked with?

What the largest table? How many rows?

Give me some examples of the database tables?

Was there any external source for  files?

Article II.          Informatica

Section 2.01                Objects

What is the use of joiner transformation?

How to use sorted port?

What is passive and active transformation?

Why do we override mapping SQL?

How to get a previous row in an informatica mapping?

What is pre session and post session database and OS level options?

How do I

Section 2.02                Example of mapping

Give me an example of most complex mapping you have done? Why was it complex?

Explain to me how a type II dimension can be implemented?

Section 2.03                Exception handling

Section 2.04                Re-run

Section 2.05                Performance tuning

Article III.        Data Modeling

What is a dimension?

What is a fact?

Are dimensions normalized?

What is a slowing changing, type II dimension?

Article IV.       SQL

What is an outer join?

Does oracle support full outer join?

How to find duplicate rows in a table?

There is a table will addresses of employees. There can be more than one address for the same employee. How do I get the latest address?

What is connect by clause?

Article V.         Oracle

Section 5.01                Objects

What is the difference between procedures, packages and functions?

What are inline views?

What are materialized views?

How to incrementally refresh a materialized view?

What are the types of indexes?

What is difference between bitmap and B-tree index?

What are the types of cursors?

Give me an example where ref cursor is used?

Why do we need cursors?

What is Auth id clause?

What is commit point?

What is a exception block?

While updating a table; how do you know, how many rows have been updated?

Section 5.02                Tables

What are PCT free and PCT used?  

What is te difference between time stamp and date?

Section 5.03                SQLLDR

Give me all the options for SQLLDR?

What is training space option?

Can you uses function in SQLLDR?

Can you use functions in SQLLDR?

How do process the bad records?

What is the difference between bad and rejected files?

How do you skip records?

How do yo skip the 1^st few records?

Section 5.04                Performance Tuning

When do you use bit map index?

What is gather statistics vs. DBMS_STATS?

How to use show plan?

A query is running slow; what will you do to make is perform faster?

Article VI.       Shell Script

What is .profile file?

How to remove header row?

How to remove a specific row from file?

How to count rows?

How to sum a specific column in a file?

How to write a shell script to ftp a file?

How to write a shell script to call a oracle procedure?

How  to write a script a to call  SQLLDR?

Article VII.      Testing

How did you do Unit testing?

How dd you compare rows between two different databases?

Article VIII.    Oracle interview

1. INDEX
1. What is an index?
2. What types of indexes exists in oracle?
3. What is a bitmap index?
4. When do you use bitmap index?
5. Partitioned Tables and Indexes in Oracle 9i
6. Hash Partitioned Global Indexes Oracle 10g
7. Partitioned Tables and Indexes in Oracle 10g
8. What are IOT?
9. What are function index ? how do they work?
2. JOINS
1. What king of joins exists?
2. What is an equi join?
3. What is an inner join?
4. What is a outer join? When do you use it? Give an example?
3. PACKAGES
1. What’s the difference between procedures, packages & functions?
2. When do you use packages?
3. What is author id clause?
4.  
4. Oracle database
1. What is a tablespace?
2. What is block?
3. What is transportable tablespaces
4. What is a rollback segment?
5. What are the various memory components in Oracle?
6. Explain the physical architecture of oracle?
5. TABLES
1. What is pct free/ pct used
2. How do u set pct free pct used for a table?
3. What is partitioning?
4. What type of partitioning is present in oracle?
5. What is
6. TUNING
1. Define the how will you tune an sql statement data is performing badly?
2. Step 1
3. Step 2
4. Step 3
5. Step 4
6. What is analyzing table?
7. What is the type of optimizers?
8. How do optimize star schema?
9. How do u use explain plan?
7. SQL
1. How do you find duplicates in a table?
2. How do you filter a data in a group by query?
3.  
8. PLSQL
1. What is cursor? When do you use a cursor? What type of cursors exists? What are the differences and when each of them is used?
2. What are built in packages?
3. How do you write to a file from oracle?
4. What is PLSQL block?
5. How do you do exception handling? What is error propagation?
6. What is %ROWTYPE, %TYPE
7. What is a pragama ?
8. What is ref cursor?
9. What is dynamic PLSQL
9. SECURITY
1. How do you implement row level security in oracle?

10.   

1. What is a control file in SQL Loader?
2. What are synonyms?
3. What is data pump?
4.  

11.  ORACLE other objects

1. What are materialized views?
2. How are materialized views used?
3. What are the materialized views refresh strategy?
4. What are IOT?
5. What are oracle streams?
6.  
11.  

Article IX.       ETL interview

1. What is ETL?
2. How may etl resource have you managed?
3. Discuss UR etl strategy?
4. Discuss all steps, analysis, mapping document, design document , development, testing, migration,  & performance tuning strategies for ETL?
5. How do you distribute work?
6. What is migration worthy ETL?
7. Discuss automation of ETL?
8. What is the biggest load you have managed?
9. How do you manage files?
10. How do you populate a type II dimensions?
11. Explain the most difficult etl process
12. How do you do reconciliation?
13. How do u manage exceptions?
14. What kind of etl exceptions exists? How are they managed?
15. What is error handling?
16. What is data quality?
17. how do assure data quality in etl process?
18.  

Article X.         Data Warehousing interview

1. What is data warehouse?
1. Consolidation- Many systems/Subject areas/Location
2. Integration
3. Historical Store
4. Aggregation
2. What’s the difference between ODS/DWH & data mart?
1.  
3. What is R Kimball strategy vs Bill Inmon strategy?
1.  
4. What strategy do you prefer? Why? When?
1.  

Article XI.       Data Modeling interview

1. What is data model?
1.  
2. What is 1^st normal form..2^nd..3^rd…
1.  
3. What’s the difference between logical & physical model?
4.  
5. What is Star schema, Snowflake schema, and 3nf modeling?
1.  
6. How does many to many relationship implemented in Modeling?    
1.  
7. What notations are used for modeling?
1.  
8. What IS Type I & type II dim?
1.  
9. What is fact?
10. What is a dim?
11. What is a fact less fact?
12. What is a non-aggregate fact?
13. What is snapshot fact?
14. Have you done data modeling?
15. What is the difference between OLTP data model & OLAP data model?

Article XII.      Informatica Interview

Article XIII.    Unix interview

Ksh

Awk

Sed

How do u get success & failure of Script

How can U 2 run process in parallel?

            `

Article XIV.   Reporting & OALP interview

Have you done reporting?

What kind of tools have you used?

What’s the difference between Reporting & olap?

Whats OLAP? ROLAP?MOLAP?HOLAP?

Name some tools on MOLAP & ROLAP?

Article XV.     General interview Questions

Tell me about yourself?

            I have 10 year so  experience in IT

            90% of the time I have worked on Informatica.

            My recent experience has been on Informatica.

 I will start with my recent experience.

            Tell about the current project

             

Talk about UR Role in Previous Project

Ask him if he wants details…

            Project about

Role

How many people managing?

Tech aspects

           

            I will like to come and give a personal interview.

           

Difference between OLTP and OLAP

OLTP

Current data
Short database transactions
Online update/insert/delete
Normalization is promoted
High volume transactions
Transaction recovery is necessary


OLAP
Current and historical data
Long database transactions
Batch update/insert/delete
Denormalization is promoted
Low volume transactions
Transaction recovery is not necessary

Constraint Based Loading

http://www.geekinterview.com/question_details/30013