1. Suppose we have EmpTbl as below
8. Select Salary whose name starts with 'R'.
Ans. Select Name, Salary from EmpTbl where Name like 'R%'
You will get the output like below
You will get the output like below.
12. How to find the duplicate records?
Ans. select EmpId,Name,Department,Salary,BirthDate ,Count(*) as Cnt
from EmpTbl Group by EmpId,Name,Department,Salary,BirthDate having Count(*)>1
13. Select employee names whose age is greater than 30.
14. Select the names of the employees whose Birthdate date is between 01/01/1988 to 01/01/1999.
Ans. SELECT DISTINCT Name FROM EmpTbl
WHERE BirthDate BETWEEN '01/01/1988' AND '01/01/1999'
15. What will be the output of the query
Select Null/0
Ans. NULL
16. What will be the output of the query
Select SUM(NULL)
Ans. Error
17.What will be the output of the query
Select 4 from EmpTbl
Ans.
18. What will be the output of the query
Select (1/2)*10
Ans. 0
19. How to fetch the first 10 characters of the string?
A. SELECT SUBSTRING(Name,1,10) AS EmployeeName FROM EmpTbl
20. How to add column LastName in EmpTbl?
Ans. ALTER TABLE EmpTbl ADD LastName nvarchar(max)
21. How to get the list of all the tables of a database?
Ans. SELECT * from sys.Tables
And Data like
Now we have queries like
1. Select maximum salary from EmpTbl
Ans. Select max(Salary) as Salary from EmpTbl
You will get the output like below
2. Select Salary and Name in Descending Order
Ans. 1st Way :
Select max(salary) as Salary, Name from EmpTbl Group by Name order by Salary desc
2nd Way :
Select Salary, Name from EmpTbl Order by Salary desc
You will get the output like below
3. Select employee name with highest salary Department wise.
Ans. Select Salary, Name, Department from EmpTbl where Salary in (select max(Salary) from EmpTbl Group by Department)
You will get the output like below
4. Select the second highest Salary from the table.
Ans. 1st Way :
Select Top(1) * from (select top(2) Salary from EmpTbl order by Salary desc) As T Order by Salary asc
2nd Way :
Select Max(Salary) from EmpTbl where Salary <(Select Max(Salary) from EmpTbl)
You will get the output like below
5. Select Sum of Salary Department wise.
Ans. Select Sum(Salary) as Salary, Department from EmpTbl Group by Department
You will get the output like below
6. Select Department whose Sum of Salary is greater than 25000.
Ans. Select Sum(Salary) as Salary, Department from EmpTbl Group by Department having Sum(Salary)>25000
You will get the output like below
7. Select the name of the employees whose Salary is between 10000 to 50000.
Ans. Select Name, Salary from EmpTbl where Salary between 10000 and 50000
You will get the output like below.
Ans. Select Name, Salary from EmpTbl where Name like 'R%'
You will get the output like below
9. Select the name of all employees as comma separated.
Ans. SELECT Name + ', ' From EmpTbl For XML PATH('')You will get the output like below.
10. How to swap the department Asp.net and PHP?
Ans UPDATE EmpTbl
SET Department = CASE
WHEN Department = 'Asp.net' THEN 'PHP'
ELSE Case When Department= 'PHP' then 'Asp.net' End
END Where Department='Asp.net' or Department='PHP'
11. Get the name of Employees whose salary is the same.
Ans. select e1.Salary,e1.Name from EmpTbl as e1,EmpTbl as e2
Where e1.Salary=e2.Salary and e1.EmpId!=e2.EmpId
12. How to find the duplicate records?
Ans. select EmpId,Name,Department,Salary,BirthDate ,Count(*) as Cnt
from EmpTbl Group by EmpId,Name,Department,Salary,BirthDate having Count(*)>1
Ans. select Name from EmpTbl
where datediff(year,BirthDate, getdate()) >30
14. Select the names of the employees whose Birthdate date is between 01/01/1988 to 01/01/1999.
Ans. SELECT DISTINCT Name FROM EmpTbl
WHERE BirthDate BETWEEN '01/01/1988' AND '01/01/1999'
15. What will be the output of the query
Select Null/0
Ans. NULL
16. What will be the output of the query
Select SUM(NULL)
Ans. Error
17.What will be the output of the query
Select 4 from EmpTbl
Ans.
18. What will be the output of the query
Select (1/2)*10
Ans. 0
19. How to fetch the first 10 characters of the string?
A. SELECT SUBSTRING(Name,1,10) AS EmployeeName FROM EmpTbl
20. How to add column LastName in EmpTbl?
Ans. ALTER TABLE EmpTbl ADD LastName nvarchar(max)
21. How to get the list of all the tables of a database?
Ans. SELECT * from sys.Tables
