SQL Interview Quiz

I see posts and tweets all the time complaining about awful whiteboard interview sessions but what is the best method to determine one’s skill set in SQL.  Attached is a copy of an interview quiz I created years ago to help gauge if folks were familiar with SQL and could handle simple joins and queries.

Context: This was given to folks that had SQL on their resume and were applying for entry/mid level positions in Dev, QA, or Analyst positions.

We would leave interviewee in the room alone for 30 minutes, they were instructed to come out when they finished if done earlier.

Scoring: Didn’t really care to much if syntax was perfect, because they are coding with paper and pencil for goodness sake.  I could usually tell when folks struggled based on how much time they took and how many eraser marks were all over the pages.  If they used their phone, good for them, its not like developers don’t use google themselves 🙂

Obviously this is not perfect, but what do you think about my above process?  What could I do better?  What other things are people doing?

This came about because we had multiple applicants with SQL on their resume that didn’t really know the language and didn’t have much experience even though they claimed they did.

 

 

 

 

 

Searching SSRS Meta Data

Often times I am tasked with analyzing impacts when we have DML or Logic changes to tables in our EDW.  With RedGate Search the Database side is covered and fairly straight forward but when it comes to investigating SSRS reports that may use a table or column the task becomes much more difficult.

I knew the query data used in the reports must be stored somewhere, the trick was finding out where and how to parse it.  Luckily for me the another SQL user had found the solution so I don’t want to take credit.  I just felt I needed to share because this made my analysis so much easier and pain free.

Derived from https://www.simple-talk.com/sql/reporting-services/administrating-sql-server-reporting-services—planning,-documenting-and-troubleshooting/

/**********************************************************************************

SSRS ReportServer Query to Return Reports which use Search Context in Query

**********************************************************************************/

;WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’,
‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’ AS REP
)
SELECT c.Path ,
c.Name ,
DataSetXML.value(‘@Name’, ‘varchar(MAX)’) DataSourceName ,
DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) CommandText
FROM ( SELECT ItemID ,
CAST(CAST(Content AS VARBINARY(MAX)) AS XML) ReportXML
FROM [ReportServer].[dbo].[Catalog]
WHERE TYPE = 2
) ReportXML
CROSS APPLY ReportXML.nodes(‘//REP:DataSet’) DataSetXML ( DataSetXML )
INNER JOIN [dbo].[Catalog] c ON ReportXML.ItemID = c.ItemID
— Search by part of the query text
WHERE ( DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) )

LIKE ‘%<insert column or table name here%’—- ENTER SEARCH TEXT HERE