5/17/2017
Dat Data Ware WareHo Hou use/ se/ETL TES TESTING TING:: SQL Intervie rview w Question ions and Answe swers Part1 rt1 1
More
Next Blog»
Create Blog
Sign In
Data WareHouse/ETL TESTING Sunday, 26 August 2012
Followers
SQL Interview Questions and Answers Part1
Followers (23) Next
Q) i have emp table in which columns are empid,empname,sa empid,empname,sall now i want to
increase the sal of the particular emp whose sal is <10000 <10000 with wi th 2000 2 000RS RS & sal s al >10000 & <20000 with 4000RS and whose sal is >20000 with 5000RS now write a single update query. UPDATE Emp SET sal = sal + CASE WHEN sal>=1000 AND sal<2000 THEN 200 WHEN sal>=2000 AND sal<3000 THEN 300 END;
Follow
Blog Archive ▼ 2012 (47)
1. The following query retrieves "2" highest paid employees FROM each Department :
▼ August (43)
What is Data Warehouse? DWH Architecture
SELECT deptno, empno, sal FROM emp e
Operational Data Store(ODS)
WHERE
what is the diff difference erence be tween database vs datawar...
2 > ( SELECT COUNT(e1.sal) FROM emp e1 WHERE e.deptno = e1.deptno AND e.sal < e1.sal )
Types of Fact Tables in Data Warehouse
ORDER BY 1,3 DESC;
Types of Schemas
2. Query that will display the total no. of employees, and of that total the number who were hired in 1980, 1981, 1982, and 1983.
Testing Strategies for Data Warehouse Applications...
Give appropriate column headings.
SQL Interview Questions
I am looking at the following output. We need to stick to this format.
ETL Testing challenges
1980
1981
1982
1983
Difference between Database and Data Warehouse Tes...
-----------
------------
------------
-------------
---------
ETL Testing Process:
-14
1
Total
10
2
1
ETL Testing Techniques: ETL or Data warehouse testing
SELECT COUNT (*), COUNT(DECODE(TO_CHAR (hiredate, 'YYYY'),'1980', empno)) "1980", http://dwhtest.blogspot.i n/2012/08/sql - i nter vi ew - questi ons- and- answer s.htm l
Update Strategy Update Transformation 1/9
5/17/2017
Data WareHouse/ETL TESTING: SQL Interview Questions and Answers Part1
COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1981', empno)) "1981", COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1982', empno)) "1982", COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1983', empno)) "1983" FROM emp;
What is difference between Joiner and Lookup Trans... SQL Query Overwrite in Source Qulifier What is Difference be tween Router and Filter Trans... Sequence Generator Transformation
3. Query for listing Deptno, ename, sal, SUM(sal in that dept) :
Expression Transformation
SELECT a.deptno, ename, sal, (SELECT SUM(sal) FROM emp b
Rank Tr ansformation
WHERE a.deptno = b.deptno) FROM emp a
Router Transformation
ORDER BY a.deptno;
Transformation Tips Filter Transformation
OUTPUT :
Aggregator T ransformation
======= DEPTNO =========
ENAME =======
SAL ====
SUM (SAL) =========
Joiner Tr ansformation Union Transformation
10 30
KING BLAKE
5000 2850
11725 10900
10
CLARK
2450
11725
10
JONES
2975
11725
Types of Testing
30 30
MARTIN ALLEN
1250 1600
10900 10900
Test Case
30
TURNER
1500
10900
30
JAMES
950
10900
Defect Tracking Format
30 20
WARD SMITH
2750 8000
10900 33000
V model
20 20
SCOTT MILLER
3000 20000
33000 33000
4. Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job for all departments, giving each column an appropriate heading. The output is as follows - we need to stick to this format : Job 20
Dept 10 Dept 30
----------
Dept Total
---------------
ANALYST
-------------
--------------------
SQL Interview Questions and Answers Part1 SQL Interview Questions and Answers part2 SQL Interview Questions and Answers Part3 ETL Testing life cycle SQL Interview Questions and Answers Partt4
4150
SQL Interview Questions and Answers Part6
2850
8275
SQL Interview Questions and Answers Part7
PRESIDENT
SQL Interview Questions and Answers Part8
5000 5000 SALESMAN 5600
Software Requirements Specification(SRS)
950 2450
2975
Test Design Plan
SQL Interview Questions and Answers Part5
1300
1900 MANAGER
Bug/Defect Life Cycle
6000
6000 CLERK
What are t he active and passive transformations?
► September (4)
5600
About Me http://dwhtest.blogspot.in/2012/08/sql-interview-questions-and-answers.html
2/9
5/17/2017
Data WareHouse/ETL TESTING: SQL Interview Questions and Answers Part1
SELECT job "Job", SUM (DECODE (deptno, 10, sal)) "Dept 10", SUM (DECODE (deptno, 20, sal)) "Dept 20", SUM (DECODE (deptno, 30, sal)) "Dept 30", SUM (sal) "Total"
leela battu Follow
2
View my complete p rofile
FROM emp GROUP BY job ; 5. 4th Top Salary of all the employees: SELECT DEPTNO, ENAME, SAL FROM EMP A WHERE 3 = (SELECT COUNT(B.SAL) FROM EMP B WHERE A.SAL < B.SAL) ORDER BY SAL DESC; 6. Retrieving the 5th row FROM a table: SELECT DEPTNO, ENAME, SAL FROM EMP WHERE ROWID = (SELECT ROWID FROM EMP
WHERE ROWNUM <=
5
MINUS SELECT ROWID FROM EMP WHERE ROWNUM < 5)
7. Tree Query : Name
Null?
Type
------------------------------------------------------------------SUB
NOT NULL
VARCHAR2(4)
SUPER
VARCHAR2(4)
PRICE
NUMBER(6,2)
SELECT sub, super FROM parts CONNECT BY PRIOR sub = super START WITH sub = 'p1'; 8. Eliminate duplicates rows in a table : DELETE FROM table_name A WHERE ROWID > ( SELECT min(ROWID) FROM table_name B WHERE A.col = B.col); 9. Displaying EVERY 4th row in a table : (If a table has 14 rows, 4,8,12 rows will be selected) SELECT * FROM emp WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4) FROM emp); 10. Top N rows FROM a table : (Displays top 9 salaried people) SELECT ename, deptno, sal FROM (SELECT * FROM emp ORDER BY sal DESC) http://dwhtest.blogspot.in/2012/08/sql-interview-questions-and-answers.html
3/9
5/17/2017
Data WareHouse/ETL TESTING: SQL Interview Questions and Answers Part1
WHERE ROWNUM < 10; 11. How does one count/sum RANGES of data values in a column? A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z).
SELECT f2, COUNT(DECODE(greatest(f1,59), least(f1,100), 1, 0)) "Range
60-100", COUNT(DECODE(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59", COUNT(DECODE(greatest(f1,29), least(f1, 0), 1, 0)) "Range 00-29" FROM
my_table
GROUP BY f2; 12. For equal size ranges it migth be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...). SELECT ename "Name", sal "Salary", DECODE( TRUNC(sal/1000, 0), 0, 0.0, 1, 0.1, 2, 0.2, 3, 0.3) "Tax rate" FROM
emp;
13. How does one count different data values in a column? COL NAME DATATYPE ---------------------------------------DNO
NUMBER
SEX
CHAR
SELECT dno, SUM(DECODE(sex,'M',1,0)) MALE, SUM(DECODE(sex,'F',1,0)) FEMALE, COUNT(DECODE(sex,'M',1,'F',1)) TOTAL FROM
t1
GROUP BY dno; 14. Query to get the product of all the values of a column : SELECT EXP(SUM(LN(col1))) FROM srinu; 15. Query to display only the duplicate records in a table: SELECT num FROM satyam GROUP BY num HAVING COUNT(*) > 1; 16. Query for getting the following output as many number of rows in the table : * http://dwhtest.blogspot.in/2012/08/sql-interview-questions-and-answers.html
4/9
5/17/2017
Data WareHouse/ETL TESTING: SQL Interview Questions and Answers Part1
** *** **** ***** SELECT RPAD(DECODE(temp,temp,'*'),ROWNUM,'*') FROM srinu1; 17. Function for getting the Balance Value : FUNCTION F_BALANCE_VALUE (p_business_group_id number, p_payroll_action_id number, p_balance_name varchar2, p_dimension_name varchar2) RETURN NUMBER IS l_bal number; l_defined_bal_id number; l_assignment_action_id number; BEGIN SELECT assignment_action_id INTO l_assignment_action_id
FROM
pay_assignment_actions
WHERE assignment_id = :p_assignment_id AND payroll_action_id = p_payroll_action_id;
SELECT defined_balance_id
INTO l_defined_bal_id
FROM pay_balance_types pbt, pay_defined_balances pdb, pay_balance_dimensions pbd
WHERE pbt.business_group_id = p_business_group_id AND UPPER(pbt.balance_name) = UPPER(p_balance_name) AND pbt.business_group_id = pdb.business_group_id AND pbt.balance_type_id = pdb.balance_type_id AND UPPER(pbd.dimension_name) =
UPPER(p_dimension_name) AND pdb.balance_dimension_id = pbd.balance_dimension_id; l_bal := pay_balance_pkg.get_value(l_defined_bal_id,l_assignment_action_id); RETURN (l_bal); exception WHEN no_data_found THEN RETURN 0; END; 18. Function for getting the Element Value : http://dwhtest.blogspot.in/2012/08/sql-interview-questions-and-answers.html
5/9
5/17/2017
Data WareHouse/ETL TESTING: SQL Interview Questions and Answers Part1
FUNCTION f_element_value( p_classification_name in varchar2, p_element_name
in varchar2,
p_business_group_id p_input_value_name
in number, in varchar2,
p_payroll_action_id
in number,
p_assignment_id
in number
) RETURN number IS l_element_value
number(14,2) default 0;
l_input_value_id pay_input_values_f.input_value_id%type; l_element_type_id pay_element_types_f.element_type_id%type; BEGIN SELECT DISTINCT element_type_id INTO l_element_type_id FROM
pay_element_types_f pet, pay_element_classifications pec
WHERE pet.classification_id = pec.classification_id AND
upper(classification_name) =
upper(p_classification_name) AND upper(element_name) = upper(p_element_name) AND pet.business_group_id = p_business_group_id; SELECT input_value_id INTO l_input_value_id FROM pay_input_values_f WHERE upper(name) = upper(p_input_value_name) AND element_type_id = l_element_type_id; SELECT NVL(prrv.result_value,0) INTO l_element_value FROM
pay_run_result_values prrv,
pay_run_results prr, pay_assignment_actions paa WHERE prrv.run_result_id = prr.run_result_id AND
prr.assignment_ACTION_ID = paa.assignment_action_id
AND
paa.assignment_id = p_assignment_id
AND
input_value_id = l_input_value_id
AND
paa.payroll_action_id = p_payroll_action_id;
RETURN (l_element_value); exception WHEN no_data_found THEN RETURN 0; END; 19. SELECT Query for counting No of words : SELECT ename, NVL(LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEF GHIJKLMNOPQRSTUVWXYZ'' ',' word_length
@'),' ',''))+1,1)
FROM emp; http://dwhtest.blogspot.in/2012/08/sql-interview-questions-and-answers.html
6/9
5/17/2017
Data WareHouse/ETL TESTING: SQL Interview Questions and Answers Part1
Explanation : TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVW XYZ'' ','
@') -- This will translate all the characters
FROM A-Z including a single quote to a space. It will also translate a space to a @ . REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOP @'),' ','') -- This will replace every
QRSTUVWXYZ'' ','
space with nothing in the above result. LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJ KLMNOPQRSTUVWXYZ'' ','
@'),' ',''))+1 -- This will give
u the count of @ characters in the above result. 20. Function to check for a leap year : CREATE OR REPLACE FUNCTION is_leap_year (p_date IN DATE) RETURN VARCHAR2 AS v_test DATE; BEGIN v_test := TO_DATE ('29-Feb-' || TO_CHAR (p_date,'YYYY'),'DD-Mon-YYYY'); RETURN 'Y'; EXCEPTION WHEN OTHERS THEN RETURN 'N'; END is_leap_year; SQL> SELECT hiredate, TO_CHAR (hiredate, 'Day') weekday FROM emp WHERE is_leap_year (hiredate) = 'Y';
Posted by leela battu at 10:28:00 pm +1 Recommend this on Google
Labels: SQL
7 comments: Tek Classes 21 September 2015 at 15:48 This was really a good post and also very informative.for more details http://www.tekclasses.com/ Reply
Tek Classes 21 September 2015 at 16:04 Info is very good.. Very informative article post.for more details http://www.tekclasses.com/ http://dwhtest.blogspot.in/2012/08/sql-interview-questions-and-answers.html
7/9
5/17/2017
Data WareHouse/ETL TESTING: SQL Interview Questions and Answers Part1
Reply
Tek Classes 21 September 2015 at 16:28 Your blog is reall y useful. T hanks for sharing this useful blog..Suppose if anyone interested to learn http://www.tekclasses.com/ Reply
Interview questions answers pdf 10 June 2016 at 17:16 Data Warehousing Interview Questions and Answers http://allinterviewquestionsandanswerspdf.blogspot.in/2016/06/top-100data-warehousing-interview.html Reply
Pradeep Reddy 12 September 2016 at 15:35 Very good collection of question and answers thank you for sharing this article. Know more about ETL Testing Training Reply
Pradeep Reddy 12 September 2016 at 15:35 Very good collection of question and answers thank you for sharing this article. Know more about ETL Testing Training Reply
Emergers Technologies 21 April 2017 at 17:18 Just found your post by searching on the Google, I am Impressed and Learned Lot of new thing from your post. I am new to blogging and always try to learn new skill as I believe that blogging is the full time job for learning new things day by day. "Emergers Technologies" Reply
Enter your comment...
Comment as:
Publish
Unknown (Goo
Preview
http://dwhtest.blogspot.in/2012/08/sql-interview-questions-and-answers.html
Sign out
Notify me
8/9
5/17/2017
Data WareHouse/ETL TESTING: SQL Interview Questions and Answers Part1
Newer Post
Home
Older Post
Subscribe to: Post Comments (Atom)
Total Pageviews
59,595 Search This Blog Search
sri. Watermark theme. Powered by Blogger.
http://dwhtest.blogspot.in/2012/08/sql-interview-questions-and-answers.html
9/9