SQL Interview Questions

1) My table is Emp
Eno ename loc
1 Raj Delhi
2 Gaur Rajasthan

I want to display like this

Eno ename loc
1 Raj Rajasthan
2 Gaur Delhi


Ans.) select eno,ename,
case when loc=’Rajasthan’ then ‘delhi’
when loc = 'Delhi' then 'Rajasthan' else loc end as loc from emp 

2) A,B two tables.
In A I have 10 columns and
in B I have 0 columns
So, whenever I write a Query likes
Select *
from A,B
What will be the output of the above query?

Ans.)We cannot create table with zero columns so query will not give any results.

3) I have a table 'Student'
stdid maths phy che Eng
1 25 52 32 12
5 25 42 23 44

Now, I want to highest marks in each student
and the output should be like this:

stdid highestmarks
1 52
5 44





Ans.)Select stdid, greatest(maths,phy,che,eng) from student; 
 
4) How will find the same columns names from n number of tables.
Say for eg. I have 50 tables, few have same column names. So, I want to
display how many tables have same column names.


Ans.) 4.Select Column_Name, count(0) From User_Tab_Columns Group By Column_Name Having Count(0)>1

for getting the table name as well:

Select Column_Name, Table_Name From User_Tab_Columns Where Column_Name In (
Select Column_Name From User_Tab_Columns Group By Column_Name Having Count(0)>1)
order by Column_Name, Table_Name
 
 5) I want to display 3rd & 8th row from my table which has n number of rows?


Ans.) Example: Id, name, sal

Select id,name,sal from (select id,name,sal, row_number() over (order by id) as chk from tbl) where chk in (3,8) 

6) How Groupby, Having, where & Order by are used. Put then in correct sequence.


Ans.)Where,Group by,having & Order by 

7) How to display all the rows of a table except the last row.

Ans)except last row
Select * from tablename where rownum<=&n
minus
select * from tablename where rownum<=&n-1 

8)How to display all the rows of a table except the first row.

Ans.)except first row
select * from tablename where rownum<=&n
minus
select * from tablename where rownum<=1 

9)How to find avg salaries in table with out using AVG function?

Ans.) select sum(sal)/count(sal) "Avg Salary" from emp 

10) Find the records whose salary is more than average salary in each group?

Ans.)Must use Correlated subquery and below is the snippet

select T1.Emp_id, T1.SAL
from TestSallary T1
where T1.SAL > (SELECT AVG(T2.sal)
from TestSallary T2
where T1.Emp_ID = T2.Emp_ID) 
 

11)how to print the number of posive signs and negetive signs and number of zeros in a particular 

column?

Ans.)Must use derived table (first derive the data into the table and the apply your conditions later on) snippet below

select COUNT(*),Salary
from (SELECT EMP_ID,
(Case when SAL = 0 then 'Zero'
when sal > 0 then 'Positive'
else 'Negative'
End) as Salary
from TestSallary) as DERIVEDSAL
group by Salary 

12)How to find the latest(most recent) update records in a table?

Ans.)this can be done by simple subquery : assuming its phone records

select * from PhoneRecords
where Record_id in (select max(Record_id) from PhoneRecords
group by PhoneNumber) 

13)Write a query which display total salary department wise with the employee name?

Ans.)Select Ename, DEPT_NO, SUM(SAL) OVER (PARTITION BY DEPT_NO) FROM EMPLOYEE

 

14)Comparing & validating Source to Target where source DB is different than the Target DB.
Say Suppose :
1) Source is MYSQL & Target is ORACLE
2) Source is flat file or .CSV file & Target is MYSQL or ORACLE

What will be the approach to test/validate the data between S & T ?

Ans.) 1. If the source is a flat file or a CSV then first we need to import data in DB (source)
2. Then we need to create view using Source that would return us data into the form of tables used in Target DB
(For this sake, we can create Templates for creating views and these templates should be the replica of the Target DB tables)
3. Once those views (using Source DB) are created and data is visible
4. We need to write a procedure to compare Source DB (views) to the Target DB 

(OR) 

1)If source is mysql and target is other rdbms then we follow some validation/comparison methods while in sql queries

A)Count comparison:We simply count the records in source and target database using count function-say suppose we are extracting 50 records from the source then we will count total no of records in target database/table before and after running etl job.
After running etl job count should be increased by 50 in target database.
B)Checksum comparison:Generaly we do have summry/aggregate data/column in target database-we will compare summary data of target database against the source database, for exp salary of an employee for a year should be matched against aggreated sum of monthly salary for that employee in source database.
C)Domain comprasion:Duplicate records may come in target database in this case no of records can be matched in both the database-we have to use distinct keywork before the coulmn while counting the records
2)In case of flat file its quite simple we do have trailer(no of records) record in a flat file,i.e we already have no of counts of records of souce database ,anyone of method mentioned above can be used