SQL Server tricky Interview Questions?

1.Problem:- 
Note:- bellow script related to MS SQL server

I have three tables data bellow. 

create table plch_by_car (Name varchar(20))

Go

create table plch_by_Bus (Name varchar(20))

Go

create table plch_on_foot (Name varchar(20))

Go

insert into plch_by_car values('Abraham chatterley')

insert into plch_by_car values('Benjamin delaney')

insert into plch_by_car values('charles esterey')

Go

insert into plch_by_Bus values('Abraham chatterley')

insert into plch_by_Bus values('Benjamin delaney')

insert into plch_by_Bus values('David fautle')

Go

insert into plch_on_foot values('Abraham chatterley')

insert into plch_on_foot values('charles Esterey')

insert into plch_on_foot values('Evender Gilderoy')

Problem statement :-

use set Operators to sallow this problem (Union, Union all, intersect ,Except). 

comment-some people use more than one Type of transport to going to work, so they may be represented in more than one of the table.

1.I need a list of all the people who use both CAR and BUS to go to work, Plus the people who use their feet. Any given person should only appear once on the list

which of the choices contain a query producing the list of names as Specified returning this desired output;

Names

-------------------

Abraham chatterley

Benjamin delaney

charles esterey

Evender Gilderoy

Please try to sallow  this problem with out looking the answer  
find the answer click on bellow link
Answer for above problem.


2.Problem:- 
    Table data

create table PLCH_GAS_PRICE (Logged_date Date unique, logged_price float not null)

GO

insert into PLCH_GAS_PRICE values('2014-09-01',7.50)

insert into PLCH_GAS_PRICE values('2014-09-02',7.61)

insert into PLCH_GAS_PRICE values('2014-09-03',7.72)

insert into PLCH_GAS_PRICE values('2014-09-04',7.89)

insert into PLCH_GAS_PRICE values('2014-09-05',7.89)

insert into PLCH_GAS_PRICE values('2014-09-06',7.83)

insert into PLCH_GAS_PRICE values('2014-09-07',7.55)

insert into PLCH_GAS_PRICE values('2014-09-08',7.55)

insert into PLCH_GAS_PRICE values('2014-09-09',7.72)

insert into PLCH_GAS_PRICE values('2014-09-10',7.89)

insert into PLCH_GAS_PRICE values('2014-09-11',7.61)

insert into PLCH_GAS_PRICE values('2014-09-12',7.61)

insert into PLCH_GAS_PRICE values('2014-09-13',7.61)

insert into PLCH_GAS_PRICE values('2014-09-14',7.72)

comment -We wish a report that shows the prices per day along with the difference compared to yesterday and  compared to Day before yesterday. for that purpose we have this incomplete query:

select Logg_date,

              logged_price

              ##Replace##

               

from PLCH_GAS_PRICE

order by Logg_date

Which off the choices can replace ##Replace## to make the Query return this desired output.

Please try to sallow  this problem with out looking the answer  

find the answer click on bellow link
Answer for above problem.

3.Problem:- 

               use bellow table data for this problem

CREATE TABLE qz_dept(dept_id int ,

                     dept_name varchar(20))

Go

CREATE TABLE qz_emp(emp_id int ,

                    emp_name varchar(20),

                    dept_id int)

Go

 insert bellow data into tables.
qz_dept table data

dept_id     dept_name
---------   ----------------
100         Development

200         Operations

qz_emp table data

emp_id      emp_name             dept_id
----------- -------------------- -----------
110         Jane Johnson         100
120         Susan Smith          100
130         Charlene Cox         100
210         Tom Tennyson         200
220         Dave Dempsey         200

Comment-

Please try to sallow  this problem with out looking the answer  

find the answer click on bellow link

Answer for above problem.