Answers for Sql interview questions ?

Problem 1
find the problem statement click on the bellow link
          Problem Statement

Answer

select * from plch_by_car

intersect

select * from plch_by_car

union

select * from plch_on_foot

Problem 2


find the problem statement click on the bellow link

          Problem Statement

in this problem we are using LAG function to sallow this problem. for Lag function followed by Over and order by clause  is required 

Answer

select Logg_date, logged_price ,

round(isnull(logged_price -lag( logged_price)over (order by Logg_date),' '),2) as yesterday_diff,

round(isnull(logged_price -lag( logged_price,2)over (order by Logg_date),' '),2) as Day_Before_yesterday_diff 

from PLCH_GAS_PRICE

order by Logg_date

if we are not using round function then data comes like this 

Problem 3

find the problem statement click on the bellow link

          Problem Statement

this problem we can sallow using Sub-query or CTE with the help of ROW_NUMBER() function. here i am using CTE to sallow this problem.

with Deptcte as (SELECT dept_id,emp_name,

ROW_NUMBER()OVER (PARTITION BY DEPT_ID ORDER BY DEPT_ID) AS DEPT FROM qz_emp)

select

       case

              when C.DEPT= 1 THEN D.dept_name ELSE ' ' END DEPT, C.emp_name FROM Deptcte as C

              INNER JOIN qz_dept as D

              ON C.dept_id =D.dept_id

Query Explanation