Frequently asked Scripts during Interviews

1. Select no of Months between two dates:

SELECT

    DATEDIFF(MONTH, '4/1/2011', '01/23/2013') +

    CASE

        WHEN DAY('4/1/2011') < DAY('01/23/2013')

        THEN 1

        ELSE 0

    END

Output:

----------------

22

2. What will be the output of following script

CREATE TABLE dbo.t1

(

 id INT NOT NULL IDENTITY (1, 1),

 name VARCHAR(100)

);

INSERT  INTO dbo.t1

        (name)

VALUES  ('sonu');

DBCC CHECKIDENT ('dbo.t1', RESEED, 24);

INSERT  INTO dbo.t1

        (name)

VALUES  ('queryingsql');

SELECT  id

FROM    dbo.t1;

Output:

---------------------------

1

25

3. Create a query that display the Emp name and indicate the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand rupees. Sort the data in descending order of salary. As for example if Emp name sonu have salary 11000. Then output should be Sonu***********.

select FirstName+SUBSTRING('****************************

********************************************************

********************************************************

********************************************************

*************************************************',1,ROUND(salary/1000,0))

    as Employee_and_salary ,salary from Employee order by salary desc;

Output:

---------------------------------------------------------------

Employee_and_salary    salary

sonu***********                         11000

mohit**********                         10000

mohit**********                         10000

monu*********                            9000

More script coming soon...........