Wednesday, 18 May 2016

Oracle interview Questions


1) HOW TO FIND DUPLICATE RECORDS?

 SELECT * FROM EMP WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM EMP GROUP BY JOB);

2) HOW TO DISPLAY ODD NUMBER ROWS?

 SELECT RN,SAL FROM (SELECT ROWNUM RN,SAL FROM EMP) WHERE MOD(RN,2)=1;

3) HOW TO DISPLAY EVEN NUMBER ROWS?

  SELECT RN,SAL FROM (SELECT ROWNUM RN,SAL FROM EMP) WHERE MOD(RN,2)=0;

4) HOW TO REMOVE DUPLICATE RECORDS?

 DELETE FROM EMP WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP GROUP BY JOB);

5) HOW TO DISPLAY FIRST 4 RECORDS?

 SELECT ROWNUM,ENAME,SAL FROM EMP WHERE ROWNUM<5;

6) HOW TO DISPLAY TOP 5 SALARIES?

 SELECT ROWNUM,EMPNO,ENAME,SAL FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=5;

7) HOW TO DISPLAY THE WITHOUT DUPLICATE ROWS?

 SELECT * FROM EMP WHERE ROWED IN (SELECT MAX(ROWID) FROM EMP GROUP BY JOB);

8) FIND OUT NTH HIGHEST SALARIES FROM EMP TABLE?

 SELECT DISTINCT(A.SAL)FROM EMP A

WHERE &N=(SELECT COUNT(DISTINCT(B.SAL)FROM EMP B

WHERE A.SAL=B.SAL);

9) HOW TO DISPLAY RECORDS FROM 5 TH TO 9 TH RECORDS ONLY?

 SELECT * FROM EMP WHERE ROWNUM<=10;

 MINUS

 SELECT * FROM EMP WHERE ROWNUM<=5;

OR(DYNAMICALLY)

 SELECT RN,SAL FROM(SELECT ROWNUM RN,SAL FROM EMP) WHERE RN BETWEEN &N1 AND &N2;

10) HOW TO DISPLAY THE 2 ND HIGHEST SALARY?

 SELECT EMPNO,ENAME,SAL,R FROM(SELECT EMPNO,ENAME,SAL,DENSE_RANK() OVER(ORDER BY SAL DESC) R FROM EMP)WHERE R=2;

11) HOW TO REMOVE SPACES IN BETWEEN STRING?

 SELECT REPLACE(‘BALAJIXXXBALU’,’X’,’ ‘)FROM DUAL;

12) HOW TO DISPLAY THE HOW MANY DUPLICATE NAMES IN A TABLE?

 SELECT JOB FROM(SELECT JOB,COUNT(JOB)FROM EMP GROUP BY JOB HAVING COUNT(JOB) >1);

13) HOW TO DISPLAY THE HALF OF THE ROWS?

 SELECT SAL FROM (SELECT ROWNUM RN,SAL FROM EMP)WHERE RN 6;

14) DISPLAY DEPT NUMBERS AND TOTAL NUMBER OF EMPLOYEES WORKING IN EACH DEPARTMENT?

 SELECT DEPTNO,COUNT(DEPT)FROM EMP GROUP BY DEPTNO;

15) DISPLAY THE DEPARTMENT NUMBERS AND TOTAL SALARY FOR EACH DEPT?

 SELECT DEPTNO,SUM(SAL)FROM EMP GROUP BY DEPTNO;

16) DISPLAY THE DEPT NUMBERS WITH MORETHEN THREE EMPLOYEES IN EACH DEPT?

 SELECT DEPTNO,COUNT(DEPTNO)FROM EMP GROUP BY DEPTNO HAVING COUNT(*) >3;

17) HOW TO DISPLAY SINGLE ‘S’ LETTER STRING?

 SELECT * FROM EMP WHERE INSTR(JOB,’S’,1,1) 0 AND INSTR(JOB,’S’,1,2)=0;

18) HOW TO DISPLAY LAST 4 RECORDS?

 SELECT * FROM EMP WHERE ROWNUM<=(SELECT COUNT(*) FROM EMP)

MINUS

SELECT * FROM EMP WHERE ROWNUM<=(SELECT COUNT(*)-4 FROM EMP);

19) HOW TO USE ALL FUNCTIONS?

 SELECT SUM(SAL),DEPTNO FROM EMP

WHERE SAL =500

GROUP BY DEPTNO

HAVING DEPTNO=10

ORDER BY DEPTNO DESC;

20) HOW WILL U IDENTIFY THE DUPLICATES IN PARTICULAR COLUMN?

 SELECT JOB,COUNT(*)FROM EMP GROUP BY JOB HAVING COUNT(*)> 1;

21) WRITE A QUERY TO LIST OUT THE EMPLOYEES WITH THERE RESPECT TO MGR LEVELS?

SELECT LPAD(‘*’,LEVEL2),EMPNO,ENAME,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH EMPNO=7839;

22) WRITE A QUERY TO GENERATE SEQUENCE NUMBERS FROM 1 TO THE SPECIFIED NUMBER N?

SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=&N;



23) https://www.youtube.com/watch?v=oPBWC4_Zmj0index=2list=PL9DF6E4B45C36D411
OUTPUT: HOW TO EXTRACT ONLY YOUTUBE FROM ABOVE LINK?
Ans) select substr('https://www.youtube.com/watch?v=oPBWC4_Zmj0index=2list=PL9DF6E4B45C36D411',
instr('https://www.youtube.com/watch?v=oPBWC4_Zmj0index=2list=PL9DF6E4B45C36D411','.',1,1)+1,
instr('https://www.youtube.com/watch?v=oPBWC4_Zmj0index=2list=PL9DF6E4B45C36D411','.',1,2) -
instr('https://www.youtube.com/watch?v=oPBWC4_Zmj0index=2list=PL9DF6E4B45C36D411','.',1,1)-1)from dual;

24) BALAJI
OUTPUT: HOW TO COUNT HOW MANY TIMES 'A' IS REPEATED?
Ans) SELECT LENGTH('BALAJI')-LENGTH(REPLACE('BALAJI','A','')) from dual;

25) IN THE COLUMN ACCOUNT TYPE, “SAVINGS ACCOUNT” AND “CURRENT ACCOUNT” ARE TWO DIFFERENT Values Present. THEY GET CONVERTED TO “SB” AND “CA” IN TARGET.
WRITE QUERY TO VALIDATE THIS WITH BOTH SOURCE AND TARGET TABLE IN SAME QUERY
Ans)
SELECT T1.ACCOUNT_NO,T1.ACCOUNT_TYPE
FROM EMP T1
INNER JOIN (SELECT ACCOUNT_NO,
CASE ACCOUNT_TYPE
WHEN 'SAVINGS ACCOUNT' THEN 'SB'
WHEN 'CURRENT ACCOUNT' THEN 'CA'
ELSE ACCOUNT_TYPE
END NEW_ACCOUNT_TYPE
FROM SOURCE) S1
ON T1.ACCOUNT_TYPE=S1.NEW_ACCOUNT_TYPE
AND T1.ACCOUNT_NO= S1.ACCOUNT_NO


26) SOURCE HAS 10 RECORDS AND TARGET AND 8 RECORDS ONLY. HOW WILL YOU IDENTIFY THE MISSING RECORDS FROM THE OUTPUT?
Ans)
SELECT S1.PK_COL,T1.PK_COL FROM SOURCE S1
LEFT OUTER JOIN TARGET T1
ON S1.PK_COL= T1.PK_COL
ORDER BY S1.PK_COL

or
SELECT PK_COL FROM SOURCE
WHERE PK_COL NOT IN (SELECT PK_COL FROM TARGET)


27) DATA FROM TABLE1 WITH 3 COLUMNS IS TRANSFERRED TO TABLE2 WITH 3 COLUMNS WITH NO TRANSFORMATIONS.  WRITE QUERY/QUERIES TO VALIDATE THAT ALL THE RECORDS WERE TRANSFERRED CORRECTLY?
Ans)
SELECT COL_1,COL_2,COL_3 FROM TABLE1
MINUS
SELECT COL_1,COL_2,COL_3 FROM TABLE2


28) DURING AN ETL TRANSFORMATION FROM SOURCE TO TARGET WRITE QUERY TO SHOW ALL THE RECORDS FROM SOURCE WHICH WERE NOT TRANSFERRED TO THE TARGET WITH USING “IN”?
Ans)

SELECT PK_COL FROM SOURCE

WHERE PK_COL NOT IN (SELECT PK_COL FROM TARGET)

29) Scenario Based questions.

Set operators

Table X


Name Value
A 1
B 2
C 3
E 6
G 7


Table Y

Name  Value
B 3
C 4
D 5
E 6
F 7

MINUS

SELECT * FROM X
MINUS
SELECT * FROM Y;

O/P:-


UNION, UNION ALL AND INTERSECT

SELECT * FROM X
UNION
SELECT * FROM Y;

SELECT * FROM X
UNION ALL
SELECT * FROM Y;

SELECT * FROM X
INTERSECT
SELECT * FROM Y;










   

             


                                   

Equi JOINS

SELECT X.NAME,X.VALUE,Y.NAME,Y.VALUE FROM X X
JOIN Y Y
ON X.VALUE=Y.VALUE;











Left outer join:

SELECT X.NAME,X.VALUE,Y.NAME,Y.VALUE FROM X X
LEFT OUTER JOIN Y Y
ON X.VALUE=Y.VALUE;












Right outer join:

SELECT X.NAME,X.VALUE,Y.NAME,Y.VALUE FROM X X
RIGHT OUTER JOIN Y Y
ON X.VALUE=Y.VALUE;













Full ourter join:

SELECT X.NAME,X.VALUE,Y.NAME,Y.VALUE FROM X X
FULL OUTER JOIN Y Y
ON X.VALUE=Y.VALUE;

















Example 2:
























Monday, 18 November 2013

Unix Commands

Man ual command.
man man This is help command, and will explains you about online manual pages you can also use man in conjunction with any command to learn more about that command for example.
  • man ls will explain about the ls command and how you can use it.
  • man -k pattern command will search for the pattern in given command.
Banner command.
banner prints characters in a sort of ascii art poster, for example to print wait in big letters. I will type
banner wait at unix command line or in my script. This is how it will look.
  
 #    #    ##       #     #####
 #    #   #  #      #       #
 #    #  #    #     #       #
 # ## #  ######     #       #
 ##  ##  #    #     #       #
 #    #  #    #     #       #
 
Cal command
cal command will print the calander on current month by default. If you want to print calander of august of 1965. That's eightht month of 1965.
cal 8 1965 will print following results.
    August 1965
 S  M Tu  W Th  F  S
 1  2  3  4  5  6  7
 8  9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
 
Clear command
clear command clears the screen and puts cursor at beginning of first line.
Calendar command
calendar command reads your calendar file and displays only lines with current day.
For example in your calendar file if you have this
12/20   Test new software.
1/15    Test newly developed 3270 product.
1/20    Install memory on HP 9000 machine.
On dec 20th the first line will be displayed. you can use this command with your crontab file or in your login files.
Nohup command.
nohup command if added in front of any command will continue running the command or process even if you shut down your terminal or close your session to machine. For exmaple, if I want to run a job that takes lot of time and must be run from terminal and is called update_entries_tonight .
nohup update_entries_tonight will run the job even if terminal is shut down in middle of this job.
Tty command
Tty command will display your terminal. Syntax is
tty options
Options
  • -l will print the synchronous line number.
  • -s will return only the codes: 0 (a terminal), 1 (not a terminal), 2 (invalid options) (good for scripts)

File Management commands.


Pwd command.
pwd command will print your home directory on screen, pwd means print working directory.
 /u0/ssb/sandeep
is output for the command when I use pwd in /u0/ssb/sandeep directory.
Ls command
ls command is most widely used command and it displays the contents of directory.
options
  • ls will list all the files in your home directory, this command has many options.
  • ls -l will list all the file names, permissions, group, etc in long format.
  • ls -a will list all the files including hidden files that start with . .
  • ls -lt will list all files names based on the time of creation, newer files bring first.
  • ls -Fxwill list files and directory names will be followed by slash.
  • ls -Rwill lists all the files and files in the all the directories, recursively.
  • ls -R | more will list all the files and files in all the directories, one page at a time.
Mkdir command.
mkdir balaji will create new directory, i.e. here balaji directory is created.
Cd command.
cd balaji will change directory from current directory to balaji directory.
Use pwd to check your current directory and ls to see if balaji directory is there or not.
You can then use cd balaji to change the directory to this new directory.

Cat command
cat cal.txt cat command displays the contents of a file here cal.txt on screen (or standard out).

Head command.
head filename by default will display the first 10 lines of a file.
If you want first 50 lines you can use head -50 filename or for 37 lines head -37 filename and so forth.
Tail command.
tail filename by default will display the last 10 lines of a file.
If you want last 50 lines then you can use tail -50 filename.
More command. more command will display a page at a time and then wait for input which is spacebar. For example if you have a file which is 500 lines and you want to read it all. So you can use
more filename
Wc command
wc command counts the characters, words or lines in a file depending upon the option.
Options
  • wc -l filename will print total number of lines in a file.
  • wc -w filename will print total number of words in a file.
  • wc -c filename will print total number of characters in a file.
File command.
File command displays about the contents of a given file, whether it is a text (Ascii) or binary file. To use it type
file filename. For example I have cal.txt which has ascii characters about calander of current month and I have resume1.doc file which is a binariy file in microsoft word. I will get
file resume.doc
resume1.doc:    data   
file cal.txt
cal.txt:        ascii text

Cp command.
cp command copies a file. If I want to copy a file named oldfile in a current directory to a file named newfile in a current directory.
cp oldfile newfile
If I want to copy oldfile to other directory for example /tmp then
cp oldfile /tmp/newfile. Useful options available with cp are -p and -r . -p options preserves the modification time and permissions, -r recursively copy a directory and its files, duplicating the tree structure.

Rcp command.
rcp command will copy files between two unix systems and works just like cp command (-p and -i options too).
For example you are on a unix system that is called Cheetah and want to copy a file which is in current directory to a system that is called lion in /usr/john/ directory then you can use rcp command
rcp filename lion:/usr/john
You will also need permissions between the two machines. For more infor type man rcp at command line.

Mv command.
mv command is used to move a file from one directory to another directory or to rename a file.
Some examples:
  • mv oldfile newfile will rename oldfile to newfile.
  • mv -i oldfile newfile for confirmation prompt.
  • mv -f oldfile newfile will force the rename even if target file exists.
  • mv * /usr/bajwa/ will move all the files in current directory to /usr/bajwa directory.

Ln command.
Instead of copying you can also make links to existing files using ln command.
If you want to create a link to a file called coolfile in /usr/local/bin directory then you can enter this command.
ln mycoolfile /usr/local/bin/coolfile
Some examples:
  • ln -s fileone filetwo will create a symbolic link and can exist across machines.
  • ln -n option will not overwrite existing files.
  • ln -f will force the link to occur.

Rm command.
To delete files use rm command.
Options:
  • rm oldfile will delete file named oldfile.
  • rm -f option will remove write-protected files without prompting.
  • rm -r option will delete the entire directory as well as all the subdirectories, very dangerous command.

Rmdir command.
rmdir command will remove directory or directories if a directory is empty.
Options:
  • rm -r directory_name will remove all files even if directory is not empty.
  • rmdir sandeep is how you use it to remove sandeep directory.
  • rmdir -p will remove directories and any parent directories that are empty.
  • rmdir -s will suppress standard error messages caused by -p.

Comparison and Searching


Diff command.
diff command will compare the two files and print out the differences between.
Here I have two ascii text files. fileone and file two.
Contents of fileone are
This is first file 
this is second line
this is third line
this is different    as;lkdjf 
this is not different

filetwo contains
This is first file
this is second line
this is third line
this is different    xxxxxxxas;lkdjf
this is not different

diff fileone filetwo will give following output
 4c4
< this is different    as;lkdjf
---
> this is different    xxxxxxxas;lkdjf

Cmp command.
cmp command compares the two files. For exmaple I have two different files fileone and filetwo.
cmp fileone filetwo will give me
fileone filetwo differ: char 80, line 4 

if I run cmp command on similar files nothing is returned.
-s command can be used to return exit codes. i.e. return 0 if files are identical, 1 if files are different, 2 if files are inaccessible.
This following command prints a message 'no changes' if files are same
cmp -s fileone file1 && echo 'no changes' .
no changes

Dircmp Command.
dircmp command compares two directories. If i have two directories in my home directory named
dirone and dirtwo and each has 5-10 files in it. Then
dircmp dirone dirtwo will return this
 Dec  9 16:06 1997  dirone only and dirtwo only Page 1
 
 
./cal.txt                                   ./fourth.txt
./dohazaar.txt                              ./rmt.txt
./four.txt                                  ./te.txt
./junk.txt                                  ./third.txt
./test.txt 

Grep Command
grep command is the most useful search command. You can use it to find processes running on system, to find a pattern in a file, etc. It can be used to search one or more files to match an expression.
It can also be used in conjunction with other commands as in this following example, output of ps command is passed to grep command, here it means search all processes in system and find the pattern sleep.
ps -ef | grep sleep will display all the sleep processes running in the system as follows.
     ops 12964 25853  0 16:12:24 ttyAE/AAES  0:00 sleep 60
     dxi 12974 15640  0 16:12:25 ttyAH/AAHP  0:00 sleep 60
     ops 12941 25688  0 16:12:21 ttyAE/AAEt  0:00 sleep 60
     ops 12847 25812  0 16:11:59 ttyAH/AAH6  0:00 sleep 60
     ops 12894 25834  0 16:12:12 ttyAE/AAEX  0:00 sleep 60
     dxi 13067 27253  2 16:12:48 ttyAE/ABEY  0:00 sleep 1
     ops 13046 25761  0 16:12:44 ttyAE/AAE0  0:00 sleep 60
     dxi 12956 13078  0 16:12:23 ttyAG/AAG+  0:00 sleep 60
     ops 12965 25737  0 16:12:24 ttyAE/AAEp  0:00 sleep 60
     ops 12989 25778  0 16:12:28 ttyAH/AAHv  0:00 sleep 60
     ssb 13069 26758  2 16:12:49 ttyAH/AAHs  0:00 grep sleep
     pjk 27049  3353  0 15:20:23 ?           0:00 sleep 3600
Options:
  • -b option will precede each line with its block number.
  • -c option will only print the count of matched lines.
  • -i ignores uppercase and lowercase distinctions.
  • -l lists filenames but not matched lines.
other associated commands with grep are egrep and fgrep. egrep typically runs faster. for more information type man egrep or man fgrep in your system.

Find command.
Find command is a extremely useful command. you can search for any file anywhere using this command provided that file and directory you are searching has read write attributes set to you ,your, group or all. Find descends directory tree beginning at each pathname and finds the files that meet the specified conditions. Here are some examples.

Some Examples:
find $HOME -print will lists all files in your home directory.
find /work -name chapter1 -print will list all files named chapter1 in /work directory.
find / -type d -name 'man*' -print will list all manpage directories.
find / -size 0 -ok rm {} \; will remove all empty files on system.
conditions of find
  • -atime +n |-n| n will find files that were last accessed more than n or less than -n days or n days.
  • -ctime +n or -n will find that were changed +n -n or n days ago.
  • -depth descend the directory structure, working on actual files first and then directories. You can use it with cpio command.
  • -exec commad {} \; run the Unix command on each file matched by find. Very useful condition.
  • -print print or list to standard output (screen).
  • -name pattern find the pattern.
  • -perm nnnfind files whole permission flags match octal number nnn.
  • -size n find files that contain n blocks.
  • -type c Find file whole type is c. C could be b or block, c Character special file, d directory, p fifo or named pipe, l symbolic link, or f plain file.
Text processing


Cut command.
cut command selects a list of columns or fields from one or more files.
Option -c is for columns and -f for fields. It is entered as
cut options [files]
for example if a file named testfile contains
this is firstline
this is secondline
this is thirdline
Examples:
cut -c1,4 testfile will print this to standard output (screen)
ts
ts
ts
It is printing columns 1 and 4 of this file which contains t and s (part of this).
Options:
  • -c list cut the column positions identified in list.
  • -f list will cut the fields identified in list.
  • -s could be used with -f to suppress lines without delimiters.

Paste Command.
paste command merge the lines of one or more files into vertical columns separated by a tab.
for example if a file named testfile contains
this is firstline
and a file named testfile2 contains
this is testfile2
then running this command
paste testfile testfile2 > outputfile
will put this into outputfile
this is firstline       this is testfile2 
it contains contents of both files in columns.
who | paste - - will list users in two columns.
Options:
  • -d'char' separate columns with char instead of a tab.
  • -s merge subsequent lines from one file.

Sort command.
sort command sort the lines of a file or files, in alphabetical order. for example if you have a file named testfile with these contents
zzz
aaa
1234
yuer
wer
qww
wwe
Then running
sort testfile
will give us output of
1234
aaa
qww
wer
wwe
yuer
zzz
Options:
  • -b ignores leading spaces and tabs.
  • -c checks whether files are already sorted.
  • -d ignores punctuation.
  • -i ignores non-printing characters.
  • -n sorts in arithmetic order.
  • -ofile put output in a file.
  • +m[-m] skips n fields before sorting, and sort upto field position m.
  • -r reverse the order of sort.
  • -u identical lines in input file apear only one time in output.

Uniq command.
uniq command removes duplicate adjacent lines from sorted file while sending one copy of each second file.
Examples

sort names | uniq -d will show which lines appear more than once in names file.
Options:
  • -c print each line once, counting instances of each.
  • -d print duplicate lines once, but no unique lines.
  • -u print only unique lines.

Awk and Nawk command.
awk is more like a scripting language builtin on all unix systems. Although mostly used for text processing, etc.
Here are some examples which are connected with other commands.
Examples:
df -t | awk 'BEGIN {tot=0} $2 == "total" {tot=tot+$1} END {print (tot*512)/1000000}' Will give total space in your system in megabytes.
Here the output of command df -t is being passed into awk which is counting the field 1 after pattern "total" appears. Same way if you change $1 to $4 it will accumulate and display the addition of field 4
which is used space.
for more information about awk and nawk command in your system enter man awk or man nawk.

Sed command.
sed command launches a stream line editor which you can use at command line.
you can enter your sed commands in a file and then using -f option edit your text file. It works as
sed [options] files
options:
  • -e 'instruction' Apply the editing instruction to the files.
  • -f script Apply the set of instructions from the editing script.
  • -n suppress default output.

for more information about sed, enter man sed at command line in your system.

Vi editor.
vi command launches a vi sual editor. To edit a file type
vi filename
vi editor is a default editor of all Unix systems. It has several modes. In order to write characters you will need to hit i to be in insert mode and then start typing. Make sure that your terminal has correct settings, vt100 emulation works good if you are logged in using pc.
Once you are done typing then to be in command mode where you can write/search/ you need to hit :w filename to write
and in case you are done writing and want to exit
:w! will write and exit.
options:
  • i for insert mode.
    • I inserts text at the curson
    • A appends text at the end of the line.
    • a appends text after cursor.
    • O open a new line of text above the curson.
    • o open a new line of text below the curson.
  • : for command mode.
    • <escape> to invoke command mode from insert mode.
    • :!sh to run unix commands.
    • x to delete a single character.
    • dd to delete an entire line
    • ndd to delete n number of lines.
    • d$ to delete from cursor to end of line.
    • yy to copy a line to buffer.
    • P to paste text from buffer.
    • nyy copy n number of lines to buffer.
    • :%s/stringA/stringb /g to replace stringA with stringB in whole file.
    • G to go to last line in file.
    • 1G to go to the first line in file.
    • w to move forward to next word.
    • b to move backwards to next word.
    • $ to move to the end of line.
    • J join a line with the one below it.
  • /string to search string in file.
  • n to search for next occurence of string.

Report Testing

Report testing basic scenarios:

1.Verify data shown on the dashboard/reports are as expected.
2.users can see reports according to their user profile(check authentication and authorization)
3.verification of appropriate report layout to ensure it's in synch with the original document
4.verification on the accuracy and completeness of the reports
5.verify drill through reports,cross tab reports,parent/child reports etc to see if they are all working as expected
6.'Analysis functions' and 'Data Analysis' should be working
7.Previewing and/or exporting of reports to different formats such as spreadsheet,PDF,html,e-mail etc to ensure two things:
a.report layout in the new format is not distorted
b.data shown are accurate and consistent
8.if a report shows graph as well as related data in tabular format,then both should reflect consistent data.
9.Create SQL?MDX queries to fetch the data in tabular format,then both should reflect consistent data
10.verify the reports to ensure the data shown on the reports are consistent with what are beeing  supplied by upstream systems(could be oltp database, datamart, dwh,cube,etc..) data from the upstream could be extracted using the sql/mdx as specified in point 9.
11.verify calculations or business rules(if any) that applies to the underlying data(eg. check business formula that applies to a metric/KPI etc)
12.verify report level rules/validations/errors/warnings. for example-error message for wrong input, optional/mandatory prompts, 'No Data' warnings, field/prompts validation etc.
13.verify usability of reports(e.g. general appearance(color,look and feel), navigation etc)
14.verify performances of report.

In General Test scenarios for reports
Headers and Footers:

1)Is there a report title?
2)Is the report title centered? Is it underlined?
3)Was the correct font used for the Report Title?
4)Is the last report refresh datetime displayed in the right corner of page header?
5)Is the report data refresh date displayed in the right corner of the page header?
6)Was the correct  font (for ex:Arial Narrow 9) used in the page header?
7)Was the correct(for ex:Arial Narrow 7) font used in the page footer?
8)Does the footer include the page number and # of pages?
9)Does the footer contain text (for ex:" Confidential")?
10)Does the footer contain "XYZ Company”?

11)Does the “XYZ Company” text appear in blue color?

Report Body/Formatting:

1)Was Arial Narrow 9 font used for column/data headers?
2)Was Arial Narrow 9 font used for data fields?
3)Were commas used for numbers (ex 2,000)?
4)For percentages, was the % sign included in either the table header or in each corresponding data cell, as well as any subtotals and grand totals?
5)For percentages and currency, was the decimal limit set to two?
6)For currency, was the correct currency sign included in each cell, subtotal, and grand total?
7)For percentages and currency, were parenthesis used for negatives?
8)Was the format "dd mon yyyy" (ex 29 Jul 2009) used for dates?
9)Was 24 hour military time used?
10)Were column headers centered?
11)Were row labels left justified?
12)Was text data left justified?
13)Was numeric data right justified?
14)Was calculated data right justified?
15)Were domestic phone numbers formatted like 999-999-9999?


Readability & Accuracy:

1)Are prompt names meaningful?
2)Is the overall report readable (e.g. does shading print appropriately)?
3)Does the report print out in the required format(s)?
4)Has the report been checked for spelling and grammatical errors?

5)Does the report name conform to Centre Of Excellence(COE) report naming standards?

Query Design:

1)Is the data drawn from the appropriate source (e.g., does the data come from the folders that provide accurate results and optimum performance)?
2)Are all of the queries named?
3)Are the query results correct? Have they been checked against an appropriate source?
4)Is the performance acceptable?

Dashboard Testing scenarios:

1)Verify data shown on the  dashboard/reports  are as expected
2)Users can see reports according to their user profile (check authentication and authorization)
3)Verification of appropriate report layout to ensure it’s in synch with the original requirement. 
4)Verification on the accuracy and completeness of the reports 
5)Verify drill through reports, cross tab reports, parent / child reports etc to see if they are all working as expected 
6)'Analysis Functions' and 'Data Analysis' should be  working
7)Previewing and/or exporting of reports to different formats such as spreadsheet, PDF, html, e-mail etc to ensure two things: 
8)report layout in the new format is not distorted 
9)data shown are accurate and consistent 
10)If a report shows graph as well as related data in tabular format, then both should reflect consistent data 
11)Create SQL/MDX queries to fetch the data from the source to compare and validate with the target (in our case ‘reports’).
12)Verify reports to ensure data shown on the reports are consistent with what are being supplied by upstream systems (could be OLTP database, datamart, datawarehouse, cube etc.). Data from the upstream systems could be extracted using the SQL/MDX as specified in point 9.
13)Verify calculations or business rules (if any) that applies to the underlying data (e.g. check business formula that applies to a metric/KPI etc)
14)Verify report level rules/validations/errors/warnings. For example - error message for wrong input, Optional/Mandatory prompts, ‘No data’ warnings, Field/Prompts validation etc. 
15)Verify usability of reports (e.g. General appearance(Color, look and feel), Navigation etc)
16)Verify performances of report 
17)Reference data check, validate the report data as per records in reference table.


ETL Process


Why do organizations need Data Warehouse?

Organizations with organized IT practices are looking forward to create a next level of technology transformation. They are now trying to make themselves much more operational with easy-to-interoperate data. Having said that data is most important part of any organization, it may be everyday data or historical data. Data is backbone of any report and reports are the baseline on which all the vital management decisions are taken.
Most of the companies are taking a step forward for constructing their data warehouse to store and monitor real time data as well as historical data. Crafting an efficient data warehouse is not an easy job. Many organizations have distributed departments with different applications running on distributed technology. ETL tool is employed in order to make a flawless integration between different data sources from different departments. ETL tool will work as an integrator, extracting data from different sources; transforming it in preferred format based on the business transformation rules and loading it in cohesive DB known are Data Warehouse.

Well planned, well defined and effective testing scope guarantees smooth conversion of the project to the production. A business gains the real buoyancy once the ETL processes are verified and validated by independent group of experts to make sure that data warehouse is concrete and robust.Well planned, well defined and effective testing scope guarantees smooth conversion of the project to the production. A business gains the real buoyancy once the ETL processes are verified and validated by independent group of experts to make sure that data warehouse is concrete and robust.


ETL or Data warehouse testing is categorized into four different 

engagements irrespective of technology or ETL tools used:

• New Data Warehouse Testing – New DW is built and verified from scratch. Data input is taken from customer requirements and different data sources and new data warehouse is build and verified with the help of ETL tools.

• Migration Testing – In this type of project customer will have an existing DW and ETL performing the job but they are looking to bag new tool in order to improve efficiency.

• Change Request – In this type of project new data is added from different sources to an existing DW. Also, there might be a condition where customer needs to change their existing business rule or they might integrate the new rule.

• Report Testing – Report are the end result of any Data Warehouse and the basic propose for which DW is build. Report must be tested by validating layout, data in the report and calculation.

ETL Testing Techniques:

1) Verify that data is transformed correctly according to various business requirements and rules.

2) Make sure that all projected data is loaded into the data warehouse without any data loss and truncation.

3) Make sure that ETL application appropriately rejects, replaces with default values and reports invalid data.

4) Make sure that data is loaded in data warehouse within prescribed and expected time frames to confirm improved performance and scalability.Apart from these 4 main ETL testing methods other testing methods like integration testing and user acceptance testing is also carried out to make sure everything is smooth and reliable.

ETL Testing Process:

Similar to any other testing that lies under Independent Verification and Validation, ETL also go through the same phase.

• Business and requirement understanding

• Validating

• Test Estimation

• Test planning based on the inputs from test estimation and business requirement

• Designing test cases and test scenarios from all the available inputs

• Once all the test cases are ready and are approved, testing team proceed to perform 

pre-execution check and test data preparation for testing

• Lastly execution is performed till exit criteria are met

• Upon successful completion summary report is prepared and closure process is done.
It is necessary to define test strategy which should be mutually accepted by stakeholders before starting actual testing. A well defined test strategy will make sure that correct approach has been followed meeting the testing aspiration. ETL testing might require writing SQL statements extensively by testing team or may be tailoring the SQL provided by development team. In any case testing team must be aware of the results they are trying to get using those SQL statements.

Data Warehouse Testing scenarios

ETL testing:

- Verify that data transformation from source to destination works as expected

- Verify that expected data is added in target system

- Verify that all DB fields and field data is loaded without any truncation

- Verify data checksum for record count match

- Verify that for rejected data proper error logs are generated with all details

- Verify NULL value fields

- Verify that duplicate data is not loaded

- Verify data integrity

ETL Testing Challenges:

ETL testing is quite different from conventional testing. There are many challenges we faced while performing data warehouse testing. Here is the list of few ETL testing challenges I experienced on my project:

- Incompatible and duplicate data.

- Loss of data during ETL process.

- Unavailability of inclusive test bed.

- Testers have no privileges to execute ETL jobs by their own.

- Volume and complexity of data is very huge.

- Fault in business process and procedures.

- Trouble acquiring and building test data.

- Missing business flow information.

Data is important for businesses to make the critical business decisions. ETL testing plays a significant role validating and ensuring that the business information is exact, consistent and reliable. Also, it minimizes hazard of data loss in production.

Sunday, 17 November 2013

ETL Testing concepts

What is ETL? ?what are the scenarios we can check in ETL process???

Ans)The process of extracting the data from different source(operational databases) systems, integrating the data and transforming the data into a homogeneous format and loading into the target warehouse database is called as ETL(Extraction, Transformation and Loading) and some times also called as Data Acquisition Process.

Extraction
1) Data is able to extract the required fields
2)The execution logic for each source system is working
3)Extraction scripts are generated security access to the source systems.
4)source to extraction destinations working in terms of completeness and accuracy.
5)Extraction is getting completed within the expected result.

Transformation:
1)Transformation scripts are transforming the data as per the expected logic.
2)The onetime transformation for historical snap-shots are working.
3)Detailed and aggregated data sets are created and are matching.

Loading:
1)data sets in staging to loading destination is working
2)Any transformations during loading process is working
3)One time historical snap-shots are working
4)Both incremental and total refresh are working
5)Loading is happening with the expected result.

ETL Testing Common scenario's:

1)Verify all the DB Objects are in place as defined in mapping document. ( Source, Staging /ODS, DWH)
2)Verify all the ETL objects are deployed to QA Enviorment and are valid.
3)Verify table structures of DB are same as in the document.
4)All the flat files that are required for QA to run test are in place and is in required format.
5)Verify that data consolidation of duplicate, truncations or merged data was properly handled.
6)Verify that data field types,formats, field prescision and field length are as specified (specially if it supports south asian languages)
7)Verify that defaults values, Nulls records, Error records, Invalid data row and status type row are properly handeled.
9)Verify that expected ranges of field contents are specified where known.
10)Verify data completeness in target table.
11)Verify data integrity.
12)Verify to ensure that no null rows present in EDW tables
13)Compare records counts between source and target..check for any rejected records.
14)Verify Data from Source and Target .it should be matched
15)Verify all possible outcomes of the transformation rules, default values, straight moves and as specified in the Business Specification document
16)Verify Incorrect sequence is the referential integrity failures, incorrect end-dating (if applicable) etc, reject, records etc.
17)Verify if ETL jobs/processes fail because of number of reasons (say for ex: database related failures, connectivity failures etc). Then all data should be rollback up to last successfully batch run.
18)Verify if ETL  fail during data validation then all error should be handle through error handing process .
19)Check unique values has to load in to the target. No duplicate records should be existing.
20)Verify  if the ETL process is completing within the load window.
21)Verify with invalid inputs and out of boundary scenarios and to check the behavior of the application.
22)If there are unix/shell/perl script in workflow verify the result of the scripts as well.
23)Reference data check, validate the report data as per records in reference table.
24)Verify CDC.





UNIX Real Time Scenarios

1.How to find out row count in a file?
A. wc -l filename

2. How to display  first 5 lines?
A. head -5 filename

3.How to display last 5 rows?
A. tail -5 filename

4.How to copy files from once server to another sever?
scp filename username@servername:path
ex: scp abcd.txt computer@124.127.55: ETL\source\files

5.How to delete files
A. rm filename

6.How to diplay file?
A. cat filename

7.How to display zip file?
A. Zcat filename

8.How to unzip file?
A. gunzip filename

9.How to rename file?
A. mv oldfilename newfilename
Ex: mv abc.txt abcd.txt

10.How to display first 5 lines in zipfile?
A. zcat filename | head -5

11. How to display last 5 lines in zipfile?
A. zcat filename | tail -5

12. How to get the process id?
A. ps -ef | grep job(process id)

13.How to Kill the job?
A. kill -9 jobname

14. How to display list of fles(list revers order date)?
A. ls -lrt





Oracle

Oracle :- Oak Ridge Analytical Computer Logical Engine

Statements of Oracle:-
DDL – Data definition language
1)      Create – Used to make new database object
2)      Alter- To modify the structure of the table
3)      Add – Used to add new column
4)      Modify – Used to modify structure of existing columns
5)      Drop – Used to remove columns

DML – Data manipulation language
1)      Insert – New statement to be inserted
2)      Update – Used to modify the existing records
3)      Delete – Used to remove the records from the table

DCL – Data control language
1)      Grant – Used to give permissions
2)      Revoke – Used to cancel permissions for (insert, update, delete and select)

TCL – Transaction control language
1)      Commit – Used to save changes made by DML statements
2)      Roll back – It is used to cancel transactions which are not saved
3)      Save Point – It is used to mark the transaction here

DRL – Data retrieval language
1)      Select – Used to retrive data from tables it is for read only purpose

Set Operators:-

Union – Union operator returns all rows selected by either query. Use the union operator to return all rows from multiple tables and eliminate any duplicate rows.
Null values are not ignored during duplicate checking.

Union all – Combines the result of two select statements into one result set including the duplicates.

Minus – Minus operator returns rows from the first query that are not present in the second query.

Mathematical Functions:-

ABS – This function returns the absolute value of a number.
Syntax – ABS(number)
Ex- select ABS(-32) from dual; o/p – 32
ABS(-32.4)
o/p: 32.4

ABS(-32.45)
o/p: 32.4

ABS(32.67)
o/p: 32.67

ABS(32.67 * -1)
o/p: 32.67

CEIL - This function returns the smallest integer value that is greater than or equal to a number.

Syntax- CEIL( number )
Ex - CEIL(32.65)
o/p: 33

CEIL(32.1)
o/p: 33

CEIL(32)
o/p: 32

CEIL(-32.65)
o/p: -32

CEIL(-32)
o/p: -32

EXTRACT - This function extracts a value from a date or interval value.
You can only extract YEAR, MONTH, and DAY from a DATE.
You can only extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with a time zone datatype.
Syntax:

EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )

Ex -
EXTRACT(YEAR FROM DATE '2003-08-22')
o/p: 2003

EXTRACT(MONTH FROM DATE '2003-08-22')
o/p: 8

EXTRACT(DAY FROM DATE '2003-08-22')
o/p: 22

FLOOR - This function returns the largest integer value that is equal to or less than a number.
Syntax- FLOOR( number )
Ex -
FLOOR(5.9)
o/p: 5

FLOOR(34.29)
o/p: 34

FLOOR(-5.9)
o/p: -6

ROUND - This function returns a number rounded to a certain number of decimal places.
Syntax (WITH NUMBERS): ROUND( number, [ decimal_places ] )
Ex-
ROUND(125.315)
o/p: 125

ROUND(125.315, 0)
o/p: 125

ROUND(125.315, 1)
o/p: 125.3

ROUND(125.315, 2)
o/p: 125.32

ROUND(125.315, 3)
o/p: 125.315

ROUND(-125.315, 2)
o/p: -125.32

TRUNC - This function returns a number truncated to a certain number of decimal places.
Syntax (WITH NUMBERS) : TRUNC( number, [ decimal_places ] )
Ex-
TRUNC(125.815)
o/p: 125

TRUNC(125.815, 0)
o/p: 125

TRUNC(125.815, 1)
o/p: 125.8

TRUNC(125.815, 2)
o/p: 125.81

TRUNC(125.815, 3)
o/p: 125.815

TRUNC(-125.815, 2)
o/p: -125.81

TRUNC(125.815, -1)
o/p: 120

TRUNC(125.815, -2)
o/p: 100

TRUNC(125.815, -3)
o/p: 0

TO_CHAR – This function converts a number or date to a string.

Syntax : TO_CHAR( value, [ format_mask ], [ nls_language ] )
Ex-
TO_CHAR(1210.73, '9999.9')
o/p: ' 1210.7'

TO_CHAR(-1210.73, '9999.9')
o/p: '-1210.7'

TO_CHAR(1210.73, '9,999.99')
o/p: ' 1,210.73'

TO_CHAR(1210.73, '$9,999.00')
o/p: ' $1,210.73'

TO_CHAR(21, '000099')
o/p: ' 000021'

TO_CHAR(sysdate, 'yyyy/mm/dd')
o/p: '2003/07/09'

TO_CHAR(sysdate, 'Month DD, YYYY')
o/p: 'July 09, 2003'

TO_CHAR(sysdate, 'FMMonth DD, YYYY')
o/p: 'July 9, 2003'

TO_CHAR(sysdate, 'MON DDth, YYYY')
o/p: 'JUL 09TH, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')
o/p: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')
o/p: 'Jul 9th, 2003'

TO_CHAR(sysdate, 'FMMonth DD, YYYY')
o/p: 'July 9, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')
o/p: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')
o/p: 'Jul 9th, 2003'

TO_DATE - This function converts a string to a date.

Syntax : TO_DATE( string1, [ format_mask ], [ nls_language ] )
Ex-
TO_DATE('2003/07/09', 'yyyy/mm/dd')
o/p: date value of July 9, 2003

TO_DATE('070903', 'MMDDYY')
o/p: date value of July 9, 2003

TO_DATE('20020315', 'yyyymmdd')
o/p: date value of Mar 15, 2002

TO_NUMBER - This function converts a string to a number.

Syntax : TO_NUMBER( string1, [ format_mask ], [ nls_language ] )
Ex-
TO_NUMBER('1210.73', '9999.99')
o/p: 1210.73

TO_NUMBER('546', '999')
o/p: 546

TO_NUMBER('23', '99')
o/p: 23

TO_NUMBER('1210.73')
o/p: 1210.73

TO_TIMESTAMP - This function converts a string to a timestamp.

Syntax : TO_TIMESTAMP( string1, [ format_mask ] [ 'nlsparam' ] )
Ex:

TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

TO_TIMESTAMP('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')
would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

TO_TIMESTAMP_TZ - This function converts a string to a timestamp with time zone.

Syntax:TO_TIMESTAMP_TZ( string1 , [ format_mask ] [ 'nlsparam' ] )
Ex-
TO_TIMESTAMP_TZ('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM')
would return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.

TO_TIMESTAMP_TZ('2003/DEC/13 10:13:18 -8:00', 'YYYY/MON/DD HH:MI:SS TZH:TZM')
would also return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.