SQL Kit

Every interviewer has some favorite questions that he likes to ask in almost each and every interview. Here are some of mine, and a bit of advice for those who think these are easy, I would hold off on the answers till next week for you to try and take a guess at the answers (Without executing the commands on SQL Server, of course)

1. The Setup :

Employee

• EmployeeId int
• EmployeeName nvarchar(50)
• Sex ( constraint M / F) char(1)
• DepartmentId int

 

The requirement is to produce the following output in an optimized way

DepartmentId                          CountFemale                      CountMale                  EmployeeCount

2. The Setup :

USE PlayGround
GO
BEGIN TRAN
    Create Table Employee
    (
        EmployeeId int
        ,EmployeeName nvarchar(100)
    )
    exec xp_cmdshell ‘bcp Playground.dbo.Employee in c:\Employee.txt -E -c’
    TRUNCATE TABLE Employee
ROLLBACK TRAN
GO
SELECT * FROM Employee
GO

What is the expected result of the above script ?

3. A variation on the above :

USE PlayGround
GO
    Create Table Employee
    (
        EmployeeId int
        ,EmployeeName nvarchar(100)
    )
    Insert Into Employee Select 1, ‘TestEmployee’
GO

BEGIN TRAN
    TRUNCATE TABLE Employee
ROLLBACK TRAN
GO
SELECT * FROM Employee

4. Is it possible to insert into two tables having same structure using a single insert statement ?