Display ODD /EVEN number records only?
(using MOD & rowNUM)
SELECT *
FROM
(SELECT ROWNUM RN, EMPLOYEE_Id, SALARY FROM EMPLOYEES)
WHERE mod (RN, 2) =1; (for odd)
WHERE mod (RN, 2) =0; (for even)
Display first three highest salaries?
SELECT SALARY FROM
(SELECT DISTINCT SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC)
WHERE ROWNUM <= 3
HOW TO GET COL-LEVEL/ROW LEVEL DUPLICATES?
SELECT DEPARTMENT_ID, COUNT(*)
FROM DEPARTMENTS
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) =>1;
ROWLEVEL DUPLICATES WE HAVE MENTION ALL COLUMN NAMES
FIND THE Nth HIGHEST NUMBER FROM A TABLE?
select * from
(
select First_name, salary, dense_rank()
over(order by salary desc)r from Employees)
where r=6
SELECT ENAME, SALARY FROM
(
SELECT DISTINCT SALARY, ENAME ,
ROW_NUMBER() OVER ( ORDER BY SALARY DESC) AS ROWNUMBER
FROM EMPLOYEES
ORDER BY SALARY DESC
)
WHERE ROWNUMBER = NTH
HOW TO GET ONLY INTERGER VALUES/CHARACTER VALUES FROM A DATA?
How To Split/Separate Numbers And Alphabets From Alpha Numeric String In SQL?
SELECT * FROM MIXEDVALUES WHERE REGEXP_LIKE (value, '^[a-zA-Z]')
where regexp_like( Colname, '^[[:digit:]]*$');
How to find Nth highest salary from a table?
What is Normalization? Forms.?
Analytical functions? Do we use clauses in analytical functions?
How to get unique values from both tables?
select [Link], [Link]
from
(select distinct code from UNI1) t1
full outer join
(select distinct code from UNI2) t2
on [Link] = [Link]
NVL and coalesce functions?
BI tools?
Project Overview
Data Reconciliation?
How to compare two tables?
Data Types?
How to validate data Parent and child table?
Source contains 1234 but target table should contact 00001234?
How to convert multiple row values into single row using comma?
Select Listagg(Fisrt_name,',')
from employees
where Job_id='IT_Prog';
Count of negative and positive salaries?
select salary,
case when salary>=0 then salary end as Pos_salary,
case when salary<0 then salary end as Neg_salary
from emp;
Smoke testing vs regression testing
Test strategy document
How validate data in source (oracle) and target (Flat file)
Scd types
Test case status (Deferred status)
Pesticide Paradox
Normalization
BCNF(boyce-Codd normal form)
Bug vs Defect
Test scenarios and case
Joins
Scd types
How to create views?
Rank vs Dense Rank
Power Center/Powe Mart
String functions
Common table Expressions,
View, schema objects
Difference between stored procedures and functions
Temporary table, purpose
Cube and OLAP cube
Index, clustered index
Find 2nd highest salary using RANK
Incremental load or delta /full load or initial load? How to validate?
Documents in Testing life cycle?
Data mart vs data warehouse
Data Mining
Rejected files
Informatica Logs
Duplicate values
Data completeness check
Data Validation procedure
How to validate Transformation?
Auto_Increment in Oracle SQL
Need to create Sequence
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
Delete Duplicate records from a table and select unique records
with CTR (firstname,
lastname,
country,
duplicates)
AS
(
select firstname,lastname,country,
row_number() over(partition by firstname,lastname,country order by id) as duplicates
from employee
)
delete from CTR
WHERE DUPLICATES > 1 ;
OR
DELETE FROM EMPLOYEE
WHERE ROWID NOT IN
(
SELECT MAX(ROWID) FROM EMPLOYEE
GROUP BY FIRSTNAME,LASTNAME,COUNTRY);
OR
CREATE TABLE TEMPS AS ( SELECT DISTINCT * FROM EMPLOYEE);
DROP Table which contains duplicate and rename new table
• Difference between truncate and Delete and Drop
• Deffered status and Blocked status
HOW TO GET ONLY INT & CHARACTER VALUES INTO TWO DIFFERENT COL FROM A SINGLE COLUMN?
Print number and character in two different columns from a single column in oracle
ename
123
456
789
ABC
XYZ
OUTPUT
Num Chars
123 ABC
456 XYZ
SELECT regexp_substr(NAME,'[0-9]+') AS num1,
regexp_substr(substr(NAME,1,1),'^[a-zA-Z]') AS char1
FROM mixed_data;
HOW TO EXTRACT A SUBSTRING FROM A STRING INTO A NEW COLUMN?
SELECT NAME,
substr(NAME, instr(NAME,' '), LENGTH(NAME)-instr(NAME,' ')+1) AS surname
FROM ages;
Displays Fname and Lname in separate columns
select name,
SUBSTR(name,0, INSTR(name,' ')-1) as FNAME,
SUBSTR(name,INSTR(name,' ')+1) as LNAME
from AGES;
HOW TO COMBINE TWO COLUMNS DATA INTO SINGLE A COLUMN?
Concate Function.
Write a query if length of ename is >=5 then display ename
if length of ename is <5 then 3 letters should display from ename?
SELECT FIRST_NAME,
CASE WHEN LENGTH(FIRST_NAME)>=5 THEN FIRST_NAME END AS FULLNAME,
CASE WHEN LENGTH(FIRST_NAME)<5 THEN SUBSTR(FIRST_NAME, 1, 3 ) END AS SNAME
FROM EMPLOYEES;
A SURROGATE KEY IS A MADE-UP VALUE WITH THE SOLE PURPOSE OF UNIQUELY IDENTIFYING A ROW.
USUALLY, THIS IS REPRESENTED BY AN AUTO INCREMENTING ID.
CREATE TABLE Example
(
SurrogateKey INT IDENTITY(1,1) -- A surrogate key that increments automatically
)
STORY POINTS, RETROSPECTIVE.
DEAD LOCK in SQL
CORRELATED SUBQUERIES
SQL Correlated Subqueries are used to select data from a table referenced in the outer query. The
subquery is known as a correlated because the subquery is related to the outer query. In this type of
queries, a table alias (also called a correlation name) must be used to specify which table reference is to
be used.
Uncorrelated subquery executes the subquery first and provides the value to the outer query, whereas
correlated subquery references a column in the outer query and executes the subquery once for each
row in the outer query..
Ex :
SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_code
FROM orders a
WHERE a.agent_code=(
SELECT b.agent_code
FROM agents b WHERE b.agent_name='Alex');
ex :
The following query finds employees whose salary is greater than the average salary of all employees:
SELECT
employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
salary > (SELECT
AVG(salary)
FROM
employees);
A correlated subquery is one way of reading every row in a table and comparing values in each row
against related data. It is used whenever a subquery must return a different result or set of results for
each candidate row considered by the main query. In other words, you can use a correlated subquery to
answer a multipart question whose answer depends on the value in each row processed by the parent
statement.
Nested Subqueries Versus Correlated Subqueries :
With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to
be used by the main query. A correlated subquery, however, executes once for each candidate row
considered by the outer query. In other words, the inner query is driven by the outer query.
NOTE : You can also use the ANY and ALL operator in a correlated subquery.
EXAMPLE of Correlated Subqueries : Find all the employees who earn more than the average salary in
their department.
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE department_id =
outer.department_id);
Other use of correlation are in UPDATE and DELETE
CORRELATED UPDATE :
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE [Link] =
[Link]);
Use a correlated subquery to update rows in one table based on rows from another table.
CORRELATED DELETE :
DELETE FROM table1 alias1
WHERE column1 operator
(SELECT expression
FROM table2 alias2
WHERE [Link] = [Link]);
Use a correlated subquery to delete rows in one table based on the rows from another table.
Using the EXISTS Operator :
The EXISTS operator tests for existence of rows in the results set of the subquery. If a subquery row value
is found the condition is flagged TRUE and the search does not continue in the inner query, and if it is not
found then the condition is flagged FALSE and the search continues in the inner query.
EXAMPLE of using EXIST operator :
Find employees who have at least one person reporting to them.
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT ’X’
FROM employees
WHERE manager_id =
outer.employee_id);
PERFORMANCE IMPROVEMENT OF A SQL QUERY, TUNING
WRITE A QUERY TO GET CUSTOMER Transaction DETAILS CARD AND NETBANCKING IN LAST ONE
HOUR?
select * from
(
select cust_name,Transac_time,
case when type_Transc='Card' then cust_name end as Card_cust,
case when type_Transc='Netbanking' then cust_name end as NetBank_cust
from Dtime)
WHERE Transac_time <= CURRENT_TIMESTAMP-1/24;
SELECT 8 CHARACTER FROM ADDRESS COLUMN AND CONVERT TO DATE?
select TO_date(sdate,'YYYY/MM/DD')
from
(
select street_address,
substr(street_address,1,8) as Sdate
from locations);
SELECT EMP WHO IS SERVING FROM LAST 8 YEARS and READY TO LEAVE ON NOv 2022?
select * from [Link]
where Hire_date <= sysdate- interval '8' year and LWD=31/11/2022;
select * from
(
select First_name,job_id,hire_date,Round((Sysdate-hire_date)/365,0)exp
from employees)
where exp >=20;
or
select First_name,job_id,hire_date,months_between(sysdate,hire_date) exp
from employees;
STORED PROCUERES?
CLUSTERED AND NON CLUSTERED INDEX?
What are Validations you perform as part of ETL Testing?
SCD types
Which Table will update on day to day basis first Dimension or Fact Table?
Schema types and explain
Delete duplicate records from a table.
Select Highest salary emp from each department using Rank functions?
How to validate Data Types in ETL?
DESCRIBE TABLE_Name
or
ALL_TAB_COLUMNS
SQL query to Identify duplicate records in a table without functions?
Write a query to identify teachers who teaches only maths subject?
How to validate newly added column data in Target Table?
How do you compare data between source and Target when we have millions of records?
Source is Flat File and Target is Oracle, how do you validate data?
Factless Fact Table?
Referencial Integrity?
SNOW FLAKE
what are the Dimensional Tables used?
Views and Materialzed views
How do you validate SCD2 type data?
Current data check
Duplicate Check
Component Test case - DataGaps
Data validations in ETL?
How to Run Jobs in Informatica?
How to Validate Failed Jobs in Informatica?
Primary Key, Foreign Key, Surrogate Key?
Messaging Queue, Readiss queue?
Elastic Search ETL?
Cross Join?
Apply all JOINS on BELOW Tables and results
EMP ID SAL EMP ID SAL
1 7000 1 1000
1 1000 4 3000
2 2000 5 300
3 300
Write a Query to get SUM of sal after removing Zeros from salary?
EMP_ID SAL
1 7000
1 1000
4 200
5 6000
2 2000
3 300
1 1000
SELECT SUM(sal) AS SUM
FROM
(
SELECT salary, RTRIM(salary,0) AS sal
FROM rtrim_test);
Write a query to Display salary after Decimal point only?
EMI_ID SALARY
1 3131.363
2 250.1659
3 950.659
4 9230.59
SELECT SUBSTR(SALARY,0, INSTR(SALARY,'.')-1) AS DEC
FROM
DECIMAL_TES;
Write a query to Display only first two digits of the salary from the above quesry?
SELECT SUBSTR(DEC,0,2)
FROM
(
SELECT SUBSTR(SALARY,0, INSTR(SALARY,'.')-1) AS DEC FROM
DECIMAL_TES);
Write a Query to Diplay records which have valid Phone Number and lenght of the phone number
should be equal to 10?
Phone_Type Phone_Number
Hander 1234560982
Landline 1234560ABC
Mobile 9989652365
Tele 998965236523
Twele 1569khj556
SELECT PHONE_NUM FROM PHONENUM_VALI
WHERE REGEXP_LIKE(PHONE_NUM, '^[[:digit:]]*$') AND LENGTH(PHONE_NUM)=10;
SELECT PHONE_NUM FROM PHONENUM_VALI
WHERE REGEXP_LIKE(PHONE_NUM,'^[0-9]*$') AND LENGTH(PHONE_NUM)=10;
Write a Query to Display output in the below format from the table?
EMPID BASIC [Link] ADDITIONAL
1 3000 250 0
2 5000 350 0
3 5000 300 30
OUTPUT :
EMPID SAL
1 0
1 250
1 3000
2 0
2 350
2 5000
3 30
3 300
3 5000
SELECT EMP_ID,BASIC AS SAL FROM EMP_SALARY_DETAILS
UNION
SELECT EMP_ID,HR_ALL FROM EMP_SALARY_DETAILS
UNION
SELECT EMP_ID,ADDITIONAL FROM EMP_SALARY_DETAILS;
Source Has 5 records and Targer contains 10 Records, do we have identify duplicate records In Data
Validation Stage in ETL?
Dimension Table vs FAct Table
EMP ID Information and Salary Table
Can we perform AVG, SUM aggregations using Rank Functions in SQL?
Together we can work