Some T SQL Interview questions and Answers



1. How to use sql query to copy only structure?
Ans: select * into table2 from table1 where 1 = 2


2. How do we handle Error?
Ans: I think we can use @@Error. Right after the query condition is executed we can check for @@Error <> 0, if it is not returning zero mean some error occured. Raiserror is another command for raising error We can also use Try catch block

3. What is PatIndex?
Ans: Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found
Syntax - PATINDEX ( '%pattern%' , expression )
Eg: USE AdventureWorks;
USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO

4. How to query a string contains %?
Ans: SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']'

5. How to get values from a table with comma seperated?
Ans: declare @vName nvarchar(100)
set @vName = ''
select @vName = @vName + ','+ [Name] from HumanResources.Shift
select @vName

6. How to update 'Yes' to 'No' and viceversa in a query?
Ans: Update tablename set ColumnName1 = (case ColumnName1 when 'Yes'
then 'No'else 'Yes' end)

7. Consider you have a table with columns ID(primary key), Country and State.
Now if you have some rows with combination of country and state repeating,
ie, two rows with combination India, Kerala. Write a query for deleting
duplicate records?

Ans: With T1 as
(Select *,Row_Number() over (partition by Country, State order by ID)
as 'RowNo' From TableName)
Delete from T1 where RowNo > 1;

8. How to create temporary table? How do we apply noncluster index? What is nolock? When and where is nolock applied normally?

Ans. Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply "nolock". nolock is normally applied while querying on production servers. This would make the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The risk will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.

1.
CREATE TABLE #tmpTable
(
OfficeName varchar(50)
, officeid int
, CustID int
, AgentID int
, mlsid varchar(4)
, RequestMoreDetails int null
, Emails int null
)
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID)

2.
select
OfficeName
, officeid
, o.CustID
, AgentID -
, o.mlsid
, PrintBrochure_Views = null
, RequestMoreDetails = null
, Emails = null
into #ForOffices from #Offices o
LEFT JOIN dbo.planparts WITH (NOLOCK)
ON bppa.officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID)

9. Another simple example for using temporary table.
    Also how to split a single column in to multiple columns based on column value.
    Here date_format function format date to yyyymm format.
    period_diff function returns number of months between two date parameters passed. Also parameter expect value in either yymm or yyyymm format.
IN MYSQL

CREATE TEMPORARY TABLE monthlyoffer( MemberName VARCHAR(50) ,January INT DEFAULT 0 ,February INT DEFAULT 0 ,March INT DEFAULT 0 ,April INT DEFAULT 0 ,May INT DEFAULT 0 ,June INT DEFAULT 0,July INT DEFAULT 0 ,August INT DEFAULT 0 ,September INT DEFAULT 0 ,October INT DEFAULT 0 ,November INT DEFAULT 0 ,December INT DEFAULT 0 ,PaidDate date ,CurrentYearPaid INT ,TotalPaid INT ,Balance INT);

Insert into monthlyoffer (MemberName ,January,February,March,April,May,June,July,August,September,October,November,December,PaidDate,CurrentYearPaid ,TotalPaid,Balance)
Select
  mem.first_name,
  sum(case when  monthname(paid_date)='January' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='February' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='March' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='April' and Year(mo.paid_date) = 2012  then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='May' and Year(mo.paid_date) = 2012  then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='June' and Year(mo.paid_date) = 2012  then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='July' and Year(mo.paid_date) = 2012  then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='August' and Year(mo.paid_date) = 2012  then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='September' and Year(mo.paid_date) = 2012  then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='October' and Year(mo.paid_date) = 2012  then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='November' and Year(mo.paid_date) = 2012  then mo.amount_paid else 0 end),
  sum(case when  monthname(paid_date)='December' and Year(mo.paid_date) = 2012  then mo.amount_paid else 0 end),
  max(paid_date),
  sum(case when Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),
  sum(mo.amount_paid),(mem.offer_amount*(1+PERIOD_DIFF(DATE_FORMAT(Now(), '%Y%m'),DATE_FORMAT(mem.offer_start_date, '%Y%m'))))-sum(case when mo.amount_paid is null then 0 else mo.amount_paid end)
from members mem
  left join  monthly_offered_members_list mo on mo.member_id =  mem.member_id and mo.approved = 1
where mem.offer_amount is not null and member_status = 'Active' and Year(mem.offer_start_date) <= 2012
group by mem.first_name;

Select * from monthlyoffer;


21 comments:

  1. Really useful! Thanks for posting them!

    ReplyDelete
  2. Very useful, thank you!!!

    ReplyDelete
  3. AnonymousJune 08, 2010

    good. add some more.

    ReplyDelete
  4. superrrrr..
    i dint understnad a word

    ReplyDelete
  5. Damnn usefull...specially the first onee hilariousss

    ReplyDelete
  6. AnonymousJuly 23, 2010

    good one buddy...keep doing the same :)

    ReplyDelete
  7. AnonymousJuly 25, 2010

    deleting dup rows is good..this query is possible after sql 2000..good job..

    ReplyDelete
  8. Hi

    I like this post:

    You create good material for community.

    Please keep posting.

    Let me introduce other material that may be good for net community.

    Source: Production interview questions

    Best rgs
    Peter

    ReplyDelete
  9. Hi

    Tks very much for post:

    I like it and hope that you continue posting.

    Let me show other source that may be good for community.

    Source: Production interview questions

    Best rgs
    David

    ReplyDelete
  10. Great man..really helpful

    ReplyDelete
  11. uday2995@gmail.comMay 06, 2011

    chala bagundii

    ReplyDelete
  12. hAI THIS IS KAMAL

    PLS SEND ME ANY ORACLE PL/SQL MATERIAL FOR PREPARING INTERVIEW...
    MY ID-yes.kamalakannan@gmail.com

    ReplyDelete
  13. AnonymousJuly 05, 2012

    thaku boss

    ReplyDelete
  14. Wonder full information, Thanks a lot for your helping hand

    ReplyDelete
  15. Thanks... this was pretty useful.... u can also refer the below post for more question & answer..

    http://knowitbasic.blogspot.com/2014/02/t-sql-ssis-ssrs-interview-questions-and.html

    ReplyDelete
  16. Very useful information thank you for sharing. PLSQL Online Training

    ReplyDelete
  17. The extended languages of the SQL is the plSQL and the TlSQL.



    oracle sql interview questions with answers

    ReplyDelete