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:
Labels: SQL
