1)
If we have a table with a
column named EmailID that contains
emailIDs like
If we have a table with a
column named EmailID that contains
emailIDs like
Shayam.singh@gmail.com
How to get the first part
of emailed, before @.
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.
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
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?
parameter, it will give name of the day?
5)
What is the difference between
Correlated & Sub-query?
What is the difference between
Correlated & Sub-query?
6)
Which aggregate function
accepts NULL values?
Which aggregate function
accepts NULL values?
7)
What is common table
expression?
What is common table
expression?
8)
Can you explain any case where
you need to use dynamic SQL?
Can you explain any case where
you need to use dynamic SQL?
9)
What is RaiseError? Why we use
it?
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 :
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’.
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.
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:
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
all the employee names along with their manager name.
Ans 13
/*Script 1*/
SELECT E1.EmpName
AS EmployeeName ,
AS EmployeeName ,
E2.EmpName AS Manager
FROM Employee
E1, Employee E2
E1, Employee E2
WHERE E1.ManagerID = E2. EmpID
/*Script 2*/
SELECT E1.EmpName
AS EmployeeName ,
AS EmployeeName ,
E2.EmpName AS Manager
FROM Employee
E1
E1
INNER
JOIN Employee E2
JOIN Employee E2
ON
E1.ManagerID =
E2. EmpID
E1.ManagerID =
E2. EmpID
14)
Write a Query without using Sub Queries – to
list the Departments which doesn’t have Employees;
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;
Table Schema: EmployeeID, Name,
DepartmentID;
Department
Table Schema: DepartmentID, Name;
Table Schema: DepartmentID, Name;
Ans 14:
/* Script-1*/
SELECT D.Name
FROM Department
D
D
LEFT
JOIN Employee E
JOIN Employee E
ON D.DepartmentID = E.DepartmentID
WHERE E.EmployeeID is NULL
/* Script-2*/
SELECT
D.Name
FROM Department
D
D
LEFT
JOIN Employee E
JOIN Employee E
ON
D.DepartmentID =
E.DepartmentID
D.DepartmentID =
E.DepartmentID
WHERE E.Name is NULL
/* Script-3*/
SELECT
D.Name
FROM Department
D
D
LEFT
JOIN Employee E
JOIN Employee E
ON
D.DepartmentID =
E.DepartmentID
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:
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
10000 AND 20000 THEN
Salary + 4000
WHEN Salary BETWEEN
20001 AND 50000 THEN
Salary + 8000
20001 AND 50000 THEN
Salary + 8000
WHEN Salary BETWEEN
50001 AND 60000 THEN
salary + 12000
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.’
doesn‘t violate the Uniqueness
then the Value is updated otherwise Error message will be thrown.’