SQL interview questions and answers - scenarios based

Q. How will you go about identifying duplicate records in a table
A. The following SQL query will do the trick

SELECT code, user_name, COUNT(user_name) AS NumOccurrences
  FROM tbl_user
GROUP BY code, user_name
HAVING ( COUNT(user_name) > 1 )

Q. How would you go about deleting the duplicate records?
A. You could do it in a number of steps as shown below.

• Create a temporary table.
• Insert the unique records into the temporary table.
• Delete the records from the original table.
• Insert the saved single records from the temporary table back to the original table.

Q. How will you go about searching for table and column names that you don't know where they really are? For example, search for a column name to find out in which tables they do exist.

A. You need to query the database system tables. For example, in Sybase, you can query it as shown below.

select a.name, b.name
from sysobjects a, syscolumns b
where a.id = b.id
and b.name like '%split_income%'

Q. How will you go about writing an SQL query for the following scenario?

Valuation table with the following columns portfolioid, accountid, balance, inactiveflag, valuationdttm, and typecd. The portfolio table has columns portfolioid, and portfoliocd.  The account table has columns accountid and accountcd.

Write an SQL query to extract out the accountcd and the corresponding balance for a given portfoliocd and valuationdttm. Please note that there will be multiple balance records for each account, and your query must only extract out a single balance record per account based on the rule 'extract the record with minimum value for typecd'.

A. As you can see in the sample answer below, inner joins are used to join with the relevant tables. A sub query is used  to calculate the min(typecd) to extract the record with minimum value for typecd.

select  acc.accountcd, val.balance
from valuation val
        inner join portfolio pf on pf.portfolioid = val.portfolioid
        inner join account acc on acc.accountid = val.accountid
where pf.portfoliocd = 'AR30'
and   val.valuationdttm = '28 Dec 2012'
and   val.inactiveflag = 'N'
and   acc.inactiveflag = 'N'
and   val.typecd = (select min(val2.typecd) from valuation val2 where val2.valuationdttm = val.valuationdttm and val2.inactiveflag = 'N' and val2.accountid = val.accountid group by accountid)
order by acc.accountcd

Q. If you need to map actual values retrieved from the database to some other value and then sort by these translated values as well, how will you go about accomplishing this in your SQL code?

For example, StatusCd is the column in the Portfolio table,  and it can have the values of New, and Processed. But the SQL query should return a status of 'Excluded' if the ExcludedFlag column is set yes, and 'Sent' if the SentDateTime is not null. iIf none of the above conditions are met, then return the StatusCd as in the database. The sorting needs to be carried out in the order of 'New', 'Processed', 'Sent', and then 'Excluded'.


A. This can be achieved with a switch/case statement. The syntax of switch/case statement can vary among databases. Here is a sample SQL based on Sybase database server.

SELECT PortfolioCd, SentDateTime, ExcludedFlag, StatusCd as ActualStatusCd, 
 case when p.ExcludedFlag = 'Y' then 'Excluded'
 
                    else case when p.SentDateTime is null then p.StatusCd 
                                 else 'Sent'
                   end
            
end as EvaluatedStatusCd
       
 FROM Portfolio p WHERE valuationdttm > '09 Jan 2013'  and InActiveFlag = 'N'
 
 ORDER BY case when p.ExcludedFlag = 'Y' then '4' 
                    else case  when p.SentDateTime is not null then '3'
                           else case when p.StatusCd = 'New' then '1'
                                        when p.StatusCd = 'Processed' then '2'
                                 end
                          end
              end,  

             PortfolioCd   
    

Q. How would you retrieve a date time column converted to string and formatted as dd/mm/yy hh:mm:ss
A. You can use specif functions provided by your database server. These functions are specific to the database server you are using, hence your code cannot be ported to other database servers. Here is an example in Sybase.

SELECT PortfolioCd, 
      convert(char(11), p.SentDateTime, 103) + convert(char(12), p.SentDateTime, 108) as SentDateTime
FROM Portfolio p 
WHERE valuationdttm > '09 Jan 2013'  and InActiveFlag = 'N'

In the above example, the convert function is used to convert the date time field to char. The 103 in Sybase means dd/mm/yy format and and 108 to convert to the time format hh:mm:ss.


Q. How will you go about tuning your SQL and stored procedures?
A. You can use tools like DB Artisan, TOAD, etc to analyze the query plan. The code (in Sybase) below gives you the elapsed time.

Q. How will you go about tuning your SQL and stored procedures?
A. You can use tools like DB Artisan, TOAD, etc to analyze the query plan. The code below gives you the elapsed time.

DECLARE @start datetime, @stop datetime
SET @start = GETDATE()

exec MY_PROC 'AC345', '02 Jan 2013', null, 'N'

SET @stop = GETDATE()
select datediff(ms, @start, @stop)

Proper indexing is key to get good performancee out of your SQL queries.

Q. What are all the different types of indexes?
A. There are three types of indexes

Unique Index: does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.

Clustered Index: reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.

NonClustered Index: does not alter the physical order of the table and maintains logical order of data. Each table can have 999 non-clustered indexes.


You may also like:

• SQL Interview Questions and Answers
•  SQL Interview Questions and Answers on deleting records
•  SQL subqueries interview questions and answers
•  SQL Interview Questions and Answers: storing a tree structure in a database table
•  Excel spreadsheet to generate SQL
•  SQL Tutorial with HSQLDB