I have listed down some tricky and complicated questions asked during the written test ( for the position of Sr.SQL Developer with SSIS Developer in MNC Company)
1. List out all the department which are not assign to any location and any employee master(using joins and where clause)
SELECT D.* FROM Departments as D LEFT JOIN Employee as E on E.DeptId =D.Id LEFT JOIN Locations as L on L.DeptId =D.Id WHERE (E.DeptId is null Or L.DeptId is null)
2. Department wise Employee list
SELECT D.Id,DeptName, Stuff((SELECT ','+ Name FROM Employee AS E WHERE D.Id=E.DeptId FOR XML PATH('')),1,1,' ') AS Employees FROM Departments AS D
3. Create Procedure “GETEMPLOYEEBYDEPID” with @Deptid input parameter and return following o/p while pass the Department id.
1. When we pass @Deptid=0, it will return all employees
2. When we pass @Deptid=3, it will return all employees which are assign to depatid=3
CREATE PROCEDURE GETEMPLOYEEBYDEPID @DEPTID TINYINT=0 AS BEGIN SELECT E.Id,E.Name FROM Employee AS E WHERE (@DEPTID=0 OR E.DeptId=@DEPTID) END
4. How to reverse any string in SQL? For Example: When we pass “INDIA” in a variable, it will return “AIDNI”
DECLARE @str VARCHAR(15)= 'INDIA' select REVERSE (@str)
5. How many ways to deploy SSIS package?
- SQL Server
- File System
- SSIS Package Store
- DTUTIL Command line tool
6. How to avoid Early Arriving Facts and late arriving dimensions in SQL Server Integration Services SSIS?
7. Provide list of synchronous and asynchronous components in ssis.
Audit, Cache Transform, Character Map, Conditional Split, Copy Column, Data Conversion, Derived Column, Lookup, Multicast, Percent Sampling, Row Count, DQS Cleansing, Export Column, Import Column etc.
Data Mining Query, Merge, Merge Join, Pivot, Term Lookup, Unpivot, Union All, Aggregate, Fuzzy Grouping, Fuzzy Lookup, Row Sampling, Sort etc.
8. Types of Cache Mode in Lookup Component in SSIS
a. Full Cache
b. Partial Cache
c. No Cache
9. What is use of SSIS Logging?
10. How to debug SSIS package in production environment?