T-SQL Programing Questions

1)      If we have a table with a column  named EmailID that contains emailIDs like

Ram.sharma@gmail.com

Shayam.singh@gmail.com

How to get the first part of emailed, before @. 

Ans 1:)     

SELECT LEFT(EmailID,CHARINDEX('@',EMAILID)-1) FROM [Employee]

SELECT STUFF(EmailID,CHARINDEX('@',EMAILID),LEN(EMAILID),'') FROM [Employee]

SELECT SUBSTRING(EmailID,1,CHARINDEX('@',EMAILID)-1) FROM [Employee]

2)      If we have a table with column name ID which contains ID like 1,2,3,4,5,6. How we can get this IDs in comma separated strings.

3)      Vice versa for question #2

4)      Can you create a function on which if we pass a date as input parameter, it will give name of the day?

5)      What is the difference between Correlated & Sub-query?

6)      Which aggregate function accepts NULL values?

7)      What is common table expression?

8)      Can you explain any case where you need to use dynamic SQL?

9)      What is RaiseError? Why we use it?

10)   If a user by mistake entered the wrong gender information of Employee like all Male as Female and all female as Male. Write a T-SQL script that updates all Male to Female and all Female to Male.
Ans 10 :

/* Table Schema*/

CREATE TABLE [dbo].[Employee](

      [EmpID] [int] NULL,

      [EmpName] [nvarchar](100) NULL,

      [Gender] [VARCHAR](100) NULL

) ON [PRIMARY]

GO

/* Update Statement with CASE */

UPDATE Employee

SET Gender = CASE

                        WHEN Gender = 'Male' THEN 'Female'

                        WHEN Gender = 'Female' THEN 'male'

                        ELSE

                        Gender

             END

                       

11)   You have an EMPLOYEE table with fields (EmpID, Name, Address) and another table SALARY with fields (EMPID, Salary). Write a query to fetch Employee Name, Address and Salary ONLY for those employees whose name contains the word ‘Raj’.

12)   Name 3 aggregate functions used in SQL Server.

13)   Employee details are stored in an EMPLOYEE Table along with their Manager information. Here is how the table looks like:

EmpID	Employee Name	ManagerID
101	Kiran	102
102	Suresh	103
103	Ramu	NULL
104	Waseem	102
105	Surya	104

Write a T-SQL query to display all the employee names along with their manager name.
Ans 13

/*Script 1*/

SELECT      E1.EmpName AS EmployeeName ,

            E2.EmpName AS Manager

FROM Employee E1, Employee E2

WHERE E1.ManagerID = E2. EmpID

/*Script 2*/

SELECT      E1.EmpName AS EmployeeName ,

            E2.EmpName AS Manager

FROM Employee E1

      INNER JOIN Employee E2

            ON E1.ManagerID = E2. EmpID

14)    Write a Query without using Sub Queries - to list the Departments which doesn't have Employees;

Table Schema is given here'

EMPLOYEE Table Schema: EmployeeID, Name, DepartmentID;

Department Table Schema: DepartmentID, Name;

 
Ans 14:

/* Script-1*/

SELECT D.Name

FROM Department D

      LEFT JOIN Employee E

      ON D.DepartmentID = E.DepartmentID

WHERE E.EmployeeID is NULL

/* Script-2*/

SELECT

      D.Name

FROM Department D

      LEFT JOIN Employee E

            ON D.DepartmentID = E.DepartmentID

WHERE E.Name is NULL

           

/* Script-3*/

SELECT

      D.Name

FROM Department D

      LEFT JOIN Employee E

            ON D.DepartmentID = E.DepartmentID

WHERE E.DepartmentID is NULL

15)   What is ISNULL function do?

16)   Differentiate Clustered index & non clustered index.

17)   What is Error handling & Transactional handling?

18)   How to add Check constraints

19) All the employees in your organization have been given the hike in their salary as follows:

      Salary between 10000 and 20000 got an hike of 4000

      Salary between 20001 and 50000 got an hike of 8000

      Salary between 50001 and 60000 got an hike of 12000

Ans 19 :

/* Table Schema*/

CREATE TABLE [dbo].[Employee](

      [EmpID] [int] NULL,

      [Salary] INT

)

GO

/* Script to update the Salary*/

UPDATE EMPLOYEE

SET Salary = CASE

                  WHEN Salary BETWEEN 10000 AND 20000 THEN Salary + 4000

                  WHEN Salary BETWEEN 20001 AND 50000 THEN Salary + 8000

                  WHEN Salary BETWEEN 50001 AND 60000 THEN salary + 12000

              ELSE Salary

             END 

20) How to Start the Execution of SQL Agent Job using T-SQL ?

Ans 20 : Exec dbo.sp_start_job 'Job_Name'

21) If you try to update the value of a Primary key, what will happens?

Ans 21 : If the new Value for the Primary key doesn't violate the Uniqueness then the Value is updated otherwise Error message will be thrown.'