SQL Server Interview Questions and Answers, Cheat Sheet of Law

Download SQL Server Interview Questions and Answers and more Cheat Sheet Law in PDF only on Docsity!

800+ SQL SERVER

INTERVIEW

QUESTION

ANSWERS PDF

www.interviewquestionspdf.com

SQL SERVER INTERVIEW QUESTION

ANSWERS PDF (MORE THAN 800 + QUESTION FREE PDF

DOWNLOAD)

BY Vikas Ahlawat (www.interviewquestionspdf.com) For latest interview questions must visit www.interviewquestionspdf.com SQL SERVER INTERVIEW QUESTIONS ANSWERS

• Basic SQL Interview Q.(for 0-1 year exp) Sql Server Basic Interview Query Set- Description Link
• String Related Basic Queries(for 0-1 year exp) Sql Server Basic Interview Query Set-
• Date Time related Queries(for 0-1 year exp) Sql Server Date-Time Interview Query SET-
• Salary Related Queries (for 0-2 year exp) Sql Server Salary Interview Query SET-
• Group By Related Queries(for 0-2 year exp) Sql Server Group By Interview Query SET-
• Join Related Queries(for 0-2 year exp) Sql Server Join Interview Query SET-
• Tricky Join Queries(for 0-2 year exp) Sql Server Tricky Join Interview Query SET-
• DDL Queries(for 0-2 year exp) Sql Server DDL Interview Query SET-
• Very Interesting set(for 2-5+ year exp) Small but tricky Interview Query SET-
• Very Much Tricky Q.(for 2-5+ year exp) Very much Tricky(not 4 freshers)Query SET-
• Complex Query(for 2-5+ year exp) Sql Server Complex Interview Query SET-
• Data type Interview Q.(for 2-5+ year exp) Sql Server Datatype Interview Questions
• TCS Tricky Interview Q.(for 2-5+ year exp) TCS Sql Server Tricky Interview Queries
• HCL SQL Interview Q.(for 3-5+ year exp) HCL Sql Server Interview Queries
• View Interview Questions(for 2-5+ year exp) Sql Server View Interview Questions
• Index Interview Questions(for 2-5+ year exp) Sql Server Index Interview Questions
• Stored Proc. Interview Q.(for 2-5+ year exp) Sql Server SP Interview Questions
• Temp Table Interview Q.(for 2-5+ year exp) Sql Server Temp Table Interview Questions
• Sql S. 2016 Interview Q (for 2-5+ year exp) Sql Server 2016 Interview Questions
• Constraints Interview Q. (for 2-5+ year exp) Sql Server Constraints Interview Questions
• Storage Related Interview Q. (for 2-5+ year exp) Sql Server Storage/Size Interview Questions
• Basic Sql Server Interview Q.(for 2-5+ year exp) Sql Server Very Basic Interview Questions
• Sql Server 2017 Interview Q.(for 0-5+ year exp) Sql Server 2017 Interview Questions
• 300 SQL Interview Question(for 0-5+ year exp) Sql Server 300 Random Interview Questions

3. Write a query to get FirstName in upper case as "First Name". ANS: MS SQL Server: SELECT UPPER(FirstName) AS [First Name] FROM EmployeeDetail Oracle: SELECT UPPER(FirstName) AS [First Name] FROM EmployeeDetail MySQL: SELECT UPPER(FirstName) AS [First Name] FROM EmployeeDetail 4. Write a query to get FirstName in lower case as "First Name". ANS: MS SQL Server: SELECT LOWER(FirstName) AS [First Name] FROM EmployeeDetail Oracle: SELECT LOWER(FirstName) AS [First Name] FROM EmployeeDetail MySQL: SELECT LOWER(FirstName) AS [First Name] FROM EmployeeDetail 5. Write a query for combine FirstName and LastName and display it as "Name" (also include white space between first name & last name) ANS: MS SQL Server: SELECT FirstName +' '+ LastName AS [Name] FROM EmployeeDetail Oracle: SELECT FirstName ||' '|| LastName AS [Name] FROM EmployeeDetail MySQL: SELECT CONCAT(FirstName ,' ', LastName) AS [Name] FROM EmployeeDetail

6. Select employee detail whose name is "Vikas" ANS: MS SQL Server: SELECT * FROM EmployeeDetail WHERE FirstName = 'Vikas' Oracle: SELECT * FROM EmployeeDetail WHERE FirstName = 'Vikas' MySQL: SELECT * FROM EmployeeDetail WHERE FirstName = 'Vikas' 7. Get all employee detail from EmployeeDetail table whose "FirstName" start with latter 'a'. ANS: MS SQL Server: SELECT * FROM EmployeeDetail WHERE FirstName like 'a%' Oracle: SELECT * FROM EmployeeDetail WHERE FirstName like 'a%' MySQL: SELECT * FROM EmployeeDetail WHERE FirstName like 'a%' 8. Get all employee details from EmployeeDetail table whose "FirstName" contains 'k' ANS:

Set-2: Sql Server Basic Interview Query Related Tables:- Questions Answers 11). Get all employee detail from EmployeeDetail table whose "FirstName" not start with any single character between 'a-p' Ans: SELECT * FROM [EmployeeDetail] WHERE FirstName like '[^a-p]%' 12). Get all employee detail from EmployeeDetail table whose "Gender" end with 'le' and contain 4 letters. The Underscore(_) Wildcard Character represents any single character. Ans: SELECT * FROM [EmployeeDetail] WHERE Gender like 'le' --there are two "" 13). Get all employee detail from EmployeeDetail table whose "FirstName" start with 'A' and contain 5 letters. Ans: SELECT * FROM [EmployeeDetail] WHERE FirstName like 'A____' --there are four "" 14). Get all employee detail from EmployeeDetail table whose "FirstName" containing '%'. ex:-"Vik%as". Ans: SELECT * FROM [EmployeeDetail] WHERE FirstName like '%[%]%'

--According to our table it would return 0 rows, because no name containg '%' 15). Get all unique "Department" from EmployeeDetail table. Ans: SELECT DISTINCT(Department) FROM [EmployeeDetail] 16). Get the highest "Salary" from EmployeeDetail table. Ans: SELECT MAX(Salary) FROM [EmployeeDetail] 17). Get the lowest "Salary" from EmployeeDetail table. Ans: SELECT MIN(Salary) FROM [EmployeeDetail] SQL SERVER DATE RELATED INTERVIEW QUERY 18). Show "JoiningDate" in "dd mmm yyyy" format, ex- "15 Feb 2013" Ans: SELECT CONVERT(VARCHAR(20),JoiningDate,106) FROM [EmployeeDetail] 19). Show "JoiningDate" in "yyyy/mm/dd" format, ex- "2013/02/15" Ans: SELECT CONVERT(VARCHAR(20),JoiningDate,111) FROM [EmployeeDetail] 20). Show only time part of the "JoiningDate". Ans: SELECT CONVERT(VARCHAR(20),JoiningDate,108) FROM [EmployeeDetail]

Set-3: Sql Server Date-Time Interview Query ( Date Time related Queries ) Related Table:- SQL DATETIME RELATED QUERIES 21). Get only Year part of "JoiningDate". Ans: SELECT DATEPART(YEAR, JoiningDate) FROM [EmployeeDetail] 22). Get only Month part of "JoiningDate". Ans: SELECT DATEPART(MONTH,JoiningDate) FROM [EmployeeDetail] 23). Get system date. Ans: SELECT GETDATE() 24). Get UTC date. Ans: SELECT GETUTCDATE() 25). Get the first name, current date, joiningdate and diff between current date and joining date in months. Ans: SELECT FirstName, GETDATE() [Current Date], JoiningDate, DATEDIFF(MM,JoiningDate,GETDATE()) AS [Total Months] FROM [EmployeeDetail] 26). Get the first name, current date, joiningdate and diff between current date and

joining date in days. Ans: SELECT FirstName, GETDATE() [Current Date], JoiningDate, DATEDIFF(DD,JoiningDate,GETDATE()) AS [Total Months] FROM [EmployeeDetail] 27). Get all employee details from EmployeeDetail table whose joining year is 2013. Ans: SELECT * FROM [EmployeeDetail] WHERE DATEPART(YYYY,JoiningDate) = '2013' 28). Get all employee details from EmployeeDetail table whose joining month is Jan(1). Ans: SELECT * FROM [EmployeeDetail] WHERE DATEPART(MM,JoiningDate) = '1' 29). Get all employee details from EmployeeDetail table whose joining date between "2013- 01 - 01" and "2013- 12 - 01". Ans: SELECT * FROM [EmployeeDetail] WHERE JoiningDate BETWEEN '2013- 01 - 01' AND '2013- 12 - 01' 30). Get how many employee exist in "EmployeeDetail" table. Ans: SELECT COUNT(*) FROM [EmployeeDetail]

left side Ans: SELECT LTRIM(FirstName) AS [FirstName] FROM [EmployeeDetail]

36. Display first name and Gender as M/F.(if male then M, if Female then F) Ans: SELECT FirstName, CASE WHEN Gender = 'Male' THEN 'M' WHEN Gender = 'Female' THEN 'F' END AS [Gender] FROM [EmployeeDetail] 37. Select first name from "EmployeeDetail" table prifixed with "Hello " Ans: SELECT 'Hello ' + FirstName FROM [EmployeeDetail] 38. Get employee details from "EmployeeDetail" table whose Salary greater than 600000 Ans: SELECT * FROM [EmployeeDetail] WHERE Salary > 600000 39. Get employee details from "EmployeeDetail" table whose Salary less than 700000 Ans: SELECT * FROM [EmployeeDetail] WHERE Salary < 700000 40. Get employee details from "EmployeeDetail" table whose Salary between 500000

than 600000 Ans: SELECT * FROM [EmployeeDetail] WHERE Salary BETWEEN 500000 AND 600000

41. Select second highest salary from "EmployeeDetail" table. Ans: SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM [EmployeeDetail] ORDER BY Salary DESC) T ORDER BY Salar yASC

44. Write the query to get the department and department wise total(sum) salary, display it in descending order according to salary. Ans: SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY SUM(Salary) DESC 45. Write the query to get the department, total no. of departments, total(sum) salary with respect to department from "EmployeeDetail" table. Ans: SELECT Department, COUNT(*) AS [Dept Counts], SUM(Salary) AS [Total Salary] FROM[EmployeeDetail] GROUP BY Department 46. Get department wise average salary from "EmployeeDetail" table order by salary ascending Ans: SELECT Department, AVG(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY AVG(Salary) ASC 47 . Get department wise maximum salary from "EmployeeDetail" table order by salary ascending Ans: SELECT Department, MAX(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY MAX(Salary) ASC 48. Get department wise minimum salary from "EmployeeDetail" table order by

salary ascending Ans: SELECT Department, MIN(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY MIN(Salary) ASC -- USE OF HAVING

49. Write down the query to fetch Project name assign to more than one Employee Ans: Select ProjectName,Count() [NoofEmp] from [ProjectDetail] GROUP BY ProjectNa meHAVING COUNT()>

52. Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for all employee even they have not assigned project. Ans: SELECT FirstName,ProjectName FROM [EmployeeDetail] A LEFT OUTER JOIN[Projec tDetail] B ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName 53(35.1) Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for all employee if project is not assigned then display "-No Project Assigned". Ans: SELECT FirstName, ISNULL(ProjectName,'-No Project Assigned') FROM[EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName 54. Get all project name even they have not matching any employeeid, in left table,

order by firstname from "EmployeeDetail" and "ProjectDetail". Ans: SELECT FirstName,ProjectName FROM [EmployeeDetail] A RIGHT OUTER JOIN[Proj ectDetail] B ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName

55. Get complete record (employeename, project name) from both tables ([EmployeeDetail],[ProjectDetail]), if no match found in any table then show NULL. Ans: SELECT FirstName,ProjectName FROM [EmployeeDetail] A FULL OUTER JOIN[Projec tDetail] B ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName 56. Write a query to find out the employeename who has not assigned any project, and display "-No Project Assigned"( tables :- [EmployeeDetail],[ProjectDetail]). Ans: SELECT FirstName, ISNULL(ProjectName,'-No Project Assigned') AS [ProjectName]FROM [EmployeeDetail] A LEFT OUTER JOIN [ProjectDetail] B ON A.EmployeeID =B.EmployeeDetailID WHERE ProjectName IS NULL 57. Write a query to find out the project name which is not assigned to any employee( tables :- [EmployeeDetail],[ProjectDetail]). Ans: SELECT ProjectName FROM [EmployeeDetail] A RIGHT OUTER JOIN [ProjectDetail] B ONA.EmployeeID = B.EmployeeDetailID