SQL Server Interview Questions PDF

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.interviewq...

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 Description Link Basic SQL Interview Q.(for 0-1 year exp) Sql Server Basic Interview Query Set-1 String Related Basic Queries(for 0-1 year exp) Sql Server Basic Interview Query Set-2 Date Time related Queries(for 0-1 year exp) Sql Server Date-Time Interview Query SET-3 Salary Related Queries (for 0-2 year exp) Sql Server Salary Interview Query SET-4 Group By Related Queries(for 0-2 year exp) Sql Server Group By Interview Query SET-5 Join Related Queries(for 0-2 year exp) Sql Server Join Interview Query SET-6 Tricky Join Queries(for 0-2 year exp) Sql Server Tricky Join Interview Query SET-7 DDL Queries(for 0-2 year exp) Sql Server DDL Interview Query SET-8 Very Interesting set(for 2-5+ year exp) Small but tricky Interview Query SET-9 Very Much Tricky Q.(for 2-5+ year exp) Very much Tricky(not 4 freshers)Query SET-10 Complex Query(for 2-5+ year exp) Sql Server Complex Interview Query SET-11 Data type Interview Q.(for 2-5+ year exp) Sql Server Datatype Interview Questions 12 TCS Tricky Interview Q.(for 2-5+ year exp) TCS Sql Server Tricky Interview Queries 13 HCL SQL Interview Q.(for 3-5+ year exp) HCL Sql Server Interview Queries 14 View Interview Questions(for 2-5+ year exp) Sql Server View Interview Questions 15 Index Interview Questions(for 2-5+ year exp) Sql Server Index Interview Questions 16 Stored Proc. Interview Q.(for 2-5+ year exp) Sql Server SP Interview Questions 17 Temp Table Interview Q.(for 2-5+ year exp) Sql Server Temp Table Interview Questions 18 Sql S. 2016 Interview Q (for 2-5+ year exp) Sql Server 2016 Interview Questions 19 Constraints Interview Q. (for 2-5+ year exp) Sql Server Constraints Interview Questions 20 Storage Related Interview Q. (for 2-5+ year exp) Sql Server Storage/Size Interview Questions 21 Basic Sql Server Interview Q.(for 2-5+ year exp) Sql Server Very Basic Interview Questions 22 Sql Server 2017 Interview Q.(for 0-5+ year exp) Sql Server 2017 Interview Questions 23 300 SQL Interview Question(for 0-5+ year exp) Sql Server 300 Random Interview Questions 24 Here our step by step SQL Server Interview Questions/ TSQL Queries asked during interview. Set-1: Sql Server Basic Interview Query Tables:- 1. Write a query to get all employee detail from "EmployeeDetail" table ANS: MS SQL Server: SELECT * FROM EmployeeDetail Oracle: SELECT * FROM EmployeeDetail MySQL: SELECT * FROM EmployeeDetail 2. Write a query to get only "FirstName" column from "EmployeeDetail" table ANS: MS SQL Server: SELECT FirstName FROM EmployeeDetail Oracle: SELECT FirstName FROM EmployeeDetail MySQL: SELECT FirstName FROM EmployeeDetail 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: MS SQL Server: SELECT * FROM EmployeeDetail WHERE FirstName like '%k%' Oracle: SELECT * FROM EmployeeDetail WHERE FirstName like '%k%' MySQL: SELECT * FROM EmployeeDetail WHERE FirstName like '%k%' 9. Get all employee details from EmployeeDetail table whose "FirstName" end with 'h' ANS: MS SQL Server: SELECT * FROM EmployeeDetail WHERE FirstName like '%h' Oracle: SELECT * FROM EmployeeDetail WHERE FirstName like '%h' MySQL: SELECT * FROM EmployeeDetail WHERE FirstName like '%h' 10. Get all employee detail from EmployeeDetail table whose "FirstName" start with any single character between 'a-p' ANS: MS SQL Server: SELECT * FROM EmployeeDetail WHERE FirstName like '[a-p]%' Oracle: SELECT * FROM EmployeeDetail WHERE FirstName like '[a-p]%' MySQL: SELECT * FROM EmployeeDetail WHERE FirstName like '[a-p]%' 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] Set-4: Sql Server Salary Interview Query (Salary Related Queries) Related Tables:- 31. Select only one/top 1 record from "EmployeeDetail" table. Ans: SELECT TOP 1 * FROM [EmployeeDetail] 32. Select all employee detail with First name "Vikas","Ashish", and "Nikhil". Ans: SELECT * FROM [EmployeeDetail] WHERE FirstName IN('Vikas','Ashish','Nikhil') 33. Select all employee detail with First name not in "Vikas","Ashish", and "Nikhil". Ans: SELECT * FROM [EmployeeDetail] WHERE FirstName NOT IN('Vikas','Ashish','Nikhil') 34. Select first name from "EmployeeDetail" table after removing white spaces from right side Ans: SELECT RTRIM(FirstName) AS [FirstName] FROM [EmployeeDetail] 35. Select first name from "EmployeeDetail" table after removing white spaces from 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 Set-5: Sql Server Group By Interview Query (Group By Related Queries) Related Table: QUESTIONS ANSWERS 42. Write the query to get the department and department wise total(sum) salary from "EmployeeDetail" table. Ans: SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department 43. Write the query to get the department and department wise total(sum) salary, display it in ascending order according to salary. Ans: SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY SUM(Salary) ASC 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(*)>1 Set-6: Sql Server Join Interview Query (Join Related Queries) Related Tables: SQL JOINS RELATED INTERVIEW QUERIES 51. Get employee name, project name order by firstname from "EmployeeDetail" and "ProjectDetail" for those employee which have assigned project already. Ans: SELECT FirstName,ProjectName FROM [EmployeeDetail] A INNER JOIN [ProjectDeta il]B ON A.EmployeeID = B.EmployeeDetailID ORDER BY FirstName 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 WHERE FirstName IS NULL 58. Write down the query to fetch EmployeeName & Project who has assign more than one project. Ans: Select EmployeeID, FirstName, ProjectName from [EmployeeDetail] E INNER JOIN[P rojectDetail] P ON E.EmployeeID = P.EmployeeDetailID WHERE EmployeeID IN (SELECT EmployeeDetailID FROM [ProjectDetail] GROUP BYEmpl oyeeDetailID HAVING COUNT(*) >1 ) 59. Write down the query to fetch ProjectName on which more than one employee are working along with EmployeeName. Ans: Select P.ProjectName, E.FName from ProjectDetails P INNER JOIN EmployeeDetails E on p.EmployeId = E.Id where P.ProjectName in(select ProjectName from ProjectDetailsgr oup by ProjectName having COUNT(1)>1) Set-7: Sql Server Tricky Join Interview Query (Tricky Join Queries) COMPLEX SQL JOINS INTERVIEW QUERIES SET- 7 --60. What would be the output of the following query(INNER JOIN) SELECT T1.ID, T2.ID FROM TBL_1 T1 INNER JOIN TBL_2 T2 ON T1.ID = T2.ID --ANS: --61. What would the output of the following query(LEFT OUTER JOIN) SELECT T1.ID, T2.ID FROM TBL_1 T1 LEFT OUTER JOIN TBL_2 T2 ON T1.ID = T2.ID --ANS: Output would be same as 60th Question --62. What will be the output of the following query(LEFT OUTER JOIN) SELECT T1.ID, T2.ID FROM TBL_1 T1 LEFT OUTER JOIN TBL_2 T2 ON T1.ID = T2.ID --ANS: Output would be same as 60th Question --63. What would the output of the following query(RIGHT OUTER JOIN) SELECT T1.ID, T2.ID FROM TBL_1 T1 RIGHT OUTER JOIN TBL_2 T2 ON T1.ID = T2.ID --ANS: Output would be same as 60th Question --64. What would be the output of the following query(FULL OUTER JOIN) SELECT T1.ID, T2.ID FROM TBL_1 T1 FULL OUTER JOIN TBL_2 T2 ON T1.ID = T2.ID --ANS: Output would be same as 60th Question --65. What would be the output of the following query(CROSS JOIN) SELECT T1.ID, T2.ID FROM TBL_1 T1 CROSS JOIN TBL_2 T2 --ANS: Output would be same as 60th Question --66. What would be the output of the following query.(Related Tables : Table_1,Table_2) SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B ON A.ID = B.ID --ANS: --67. What would be the output of the following query.(Related Tables : Table_1,Table_2) SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B ON A.ID = B.ID AND A.[Name] = B.[Name] --ANS: --68. What would be the output of the following query.(Related Tables : Table_1,Table_2) --(INNER JOIN WITH AND) SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B ON A.ID = B.ID AND A.[Name] = B.[Name] --ANS: --69. What would be the output of the following query.(Related Tables : Table_1,Table_2) --(INNER JOIN WITH OR) SELECT A.[ID], A.[Name],B.[ID], B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B ON A.ID = B.ID OR A.[Name] = B.[Name] --ANS: --70. What would be the output of the following query.(Related Tables : Table_1,Table_2) --(INNER JOIN WITH NOT EQUAL !=) SELECT A.[ID], A.[Name],B.[ID], B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B ON A.ID != B.ID --ANS: --71. Click on the Page no 2 below for continue reading ( for 71st and more such Query) --71. What would be the output of the following query.(Related Tables : Table_1,Table_2) --(INNER JOIN WITH NOT) SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B ON NOT(A.ID = B.ID) --ANS: --72. What would be the output of the following query.(Related Tables : Table_1,Table_2) --(INNER JOIN WITH IN) SELECT A.[ID], A.[Name],B.[ID], B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B ON A.ID IN(1) --ANS: --73. What would be the output of the following query.(Related Tables : Table_1,Table_2) --(INNER JOIN WITH NOT) SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A INNER JOIN [Table_2] B ON NOT(A.ID = B.ID) --ANS: --74. What would be the output of the following query.(Related Tables : Table_1,Table_2) --(LEFT OUTER JOIN) SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A LEFT OUTER JOIN [Table_2] B ON A.ID = B.ID --ANS: --75. Write down the query to fatch record from Table_1 which not exist in Table_2(based on ID column) --ANS: SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A LEFT OUTER JOIN [Table_2] B ON A.ID = B.ID WHERE B.[ID] IS NULL --76. What would be the output of the following query.(Related Tables : Table_1,Table_2) --(LEFT OUTER JOIN WITH !=) SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A LEFT OUTER JOIN [Table_2] B ON A.ID != B.ID --ANS: --77. Write down the query to fatch record from Table_2 which not exist in Table_1(based on ID column) --ANS: SELECT A.[ID] ,A.[Name],B.[ID] ,B.[Name] FROM [Table_1] A RIGHT OUTER JOIN [Table_2] B ON A.ID = B.ID WHERE A.[ID] IS NULL Set-8: Sql Server DDL Interview Query (DDL Queries) --78. Write down the query to create employee table with Identity column([EmployeeID]) --ANS: CREATE TABLE EmployeeDetail( [EmployeeID] INT IDENTITY(1,1) NOT NULL, [FirstName] VARCHAR(50) NULL, [LastName] NVARCHAR(50) NULL, [Salary] DECIMAL(10, 2) NULL, [JoiningDate] DATETIME NULL,[Depar tment] NVARCHAR(20) NULL, [Gender] VARCHAR(10) NULL) --79. Write down the query to create employee table with Identity column([EmployeeID]) --ANS: CREATE TABLE EmployeeDetail( [EmployeeID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [FirstName]NV ARCHAR(50) NULL, [LastName] NVARCHAR(50) NULL, [Salary] DECIMAL(10, 2) NULL, [JoiningDate] DATETIME NULL,[Depar tment] NVARCHAR(20) NULL, [Gender] VARCHAR(10) NULL) --80. Write down the query to create employee table with primary key (EmployeeID) --ANS: CREATE TABLE EmployeeDetail( [EmployeeID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [FirstName] NVARCHAR(50) NULL,[LastName] NVARCHAR(50) NULL, [Salary] DECIMAL(10, 2) NULL,[Join ingDate] DATETIME NULL, [Department] NVARCHAR(20) NULL, [Gender] VARCHAR(10) NULL) --81. How to set Primary key using Alter command --ANS: ALTER TABLE EmployeeDetail ADD PRIMARY KEY (P_EmployeeID) --82. How to set primary key and foreignkey relationship using query(set EmployeeID column of ProjectDetail table as a foreignkey) --ANS: ALTER TABLE ProjectDetail ADD CONSTRAINT fk_EmployeeDetailID_Eid FOREIGN KEY(EmployeeDetailID)REFERENCESEmployeeDetail (EmployeeID) Set-9: Small but tricky Interview Query (Very Interesting set) ANSWERS : 110) D 109) A 108) A 107) A 106) A 105) C 104) D 103) B 102) B 101) C 100) C 99) A 98) D 97) A 96) C 95) C 94) C 93) D 92) B 91) A 90) A 89) C 88) D 87) A 86) D 85) C 84) D 83) B 83). SELECT 15 --output of this query would be. A). Throw error B). 15 C). 0 D). 1 84).SELECT $ --output of this query would be. A). Throw error B). $ C). 1 D). 0.00 85). SELECT COUNT(*) --output of this query would be. A). Throw error B). 0 C). 1 D). * 86). SELECT COUNT('7') --output of this query would be. A). Throw error B). 7 C). 0 D). 1 87). SELECT 'VIKAS' + 1 --output of this query would be. A). Throw error B). 'VIKAS' C). VIKAS D). VIKAS1 88).SELECT 'VIKAS' + '1' --output of this query would be. A). Throw error B). 'VIKAS' C). VIKAS D). VIKAS1 89).SELECT (SELECT 'VIKAS') --output of this query would be. A). Throw error B). 'VIKAS' C). VIKAS D). VIKAS1 90).SELECT SELECT 'VIKAS' --output of this query would be. A). Throw error B). 'VIKAS' C). VIKAS D). VIKAS1 91). SELECT * FROM 'Country' --output of this query would be. A). Throw error B). Select all data from country table C). Country D). Throw error 92). SELECT * FROM Country , EmployeeDetail --output of this query would be. A). Throw error B). Output will be cross join of both tables C). Output will be inner join D). Output will be only Country table data 93). SELECT COUNT(*) + COUNT(*) --output of this query would be. A). Throw error B). 0 C). 1 D). 2 94). SELECT 'VIKAS' FROM Country --output of this query would be. A). Throw error B). Display one time "VIKAS" C). Display "VIKAS" as many rows in Country table D). Will select country table data 95).SELECT SUM(1+2*3) --output of this query would be. A). Throw error B). 9 C). 7 D). 6 96). SELECT MAX(1+2*3) --output of this query would be. A). Throw error B). 3 C). 7 D). 6 97).SELECT MAX(1,3,4) --output of this query would be. A).Throw error B). 1 C). 3 D). 4 98).SELECT MAX('VIKAS') --output of this query would be. A).Throw error B). 1 C). 2 D). VIKAS 99).Select Count(SELECT CountryID FROM Country) --output of this query would be. A).Throw error B). Will display count of country table C). 0 D). 1 100). SELECT 1 + '1' --output of this query would be. A). Throw error B). 1 C). 2 D). 11 101). SELECT '1' + 1 --output of this query would be. A). Throw error B). 1 C). 2 D). 11 102). SELECT NULL + 5 --output of this query would be. A). Throw error B). NULL C). 5 D). 0 103). SELECT NULL + '1' --output of this query would be. A). Throw error B). NULL C). 1 D). 0 104). SELECT 1 WHERE NULL = NULL --output of this query would be. A). Throw error B). NULL C). 1 D). NOTHING WILL RETURN BY This (0 rows will be returned by this) because the condition is false 105). SELECT SUM(1) --output of this query would be. A). Throw error B). NULL C). 1 D). 0 106). SELECT SUM('1') --output of this query would be. A). Throw error B). NULL C). 1 D). 0 107). SELECT SUM(NULL) --output of this query would be. A). Throw error B). NULL C). 1 D). 0 108). SELECT 6/0 --output of this query would be. A). Throw error(Divide by zero error encountered.) B). NULL C). 1 D). 0 109). SELECT 0/0 --output of this query would be. A). Throw error(Divide by zero error encountered.) B). NULL C). 1 D). 0 110). SELECT 0/9 --output of this query would be. A). Throw error(Divide by zero error encountered.) B). NULL C). 1 D). 0 Set-10: Very much Tricky(not 4 freshers)Query (Very Much Tricky Query) Related Tables : --100. Write down the query to print first letter of a Name in Upper Case and all other letter in Lower Case.(EmployDetail table) ANS: SELECT UPPER(SUBSTRING(FirstName,1,1))+LOWER(SUBSTRING(FirstName,2,LEN(FirstName)- 1)) AS [FirstName] Output:- --101. Write down the query to display all employee name in one cell seprated by ',' ex:-"Vikas, nikita, Ashish, Nikhil , anish"(EmployDetail table) ANS: Solution 1: SELECT STUFF(( SELECT ', ' + E.FirstName FROM [EmployeeDetail] AS E FOR XML PATH('')), 1, 2, '') AS [All Emp Name] Output:- Solution 2: --102. Write down the query to get ProjectName and respective EmployeeName(firstname) which are working on the project, --if more then one employee working on same project, then it should be in same cell seprated by comma --for example :- Task Tracker : Vikas, Ashish ANS: SELECT ProjectName, STUFF((SELECT ', ' + FirstName FROM EmployeeDetail E1 INNER JOIN [ProjectDetail] P1 ON E1.EmployeeID = P1.EmployeeDetailID WHERE P1.ProjectName = P2.ProjectName FOR XML PATH('')),1,2,'' ) AS [Employee Name] FROM EmployeeDetail E2 INNER JOIN [ProjectDetail] P2 ON E2.EmployeeID = P2.EmployeeDetailID GROUP BY ProjectName Output:- AND THE VERY VERY COMPLEX QUERY HERE --103: You have a table(FuelDetail) with ID, Fuel, And Date columns. --Fuel column is contain fuel quantity at a particular time when car start traveling. So we need to find out that when the driver fill Petrol in his/her car. --By FuelDetail Table image on the top of this post, you can understand the query. --Car start driving at 10 Am on 25th April with petrol(10 liter) --at 11 AM Petrol was 9 liters --at 12 AM petrol was 8 liters --at 2 PM (14) petrol was 12 liters... --This means that he/she fill the petrol at 25th April 2014 at 2PM --Next time he fill petrol at 7PM 25th April 2014 --and Next time he fill petrol at 11PM 25th April 2014 ANS: Solution 1: SELECT c1.fuel AS [Fuel quantity Now],c1.[Date],c.fuel AS [Fuel quantity Before],c.[Date] FROM FuelDetail c JOIN FuelDetail c1 ON c1.[Date] =(SELECT MIN([Date]) FROM FuelDetail WHERE [Date]>c.[Date] ) WHERE c1.fuel>c.fuel Solution 2:(by Eduardo Ramires) see in comment section Select FD.ID, FD.Fuel, FD.Date,FD1.Fuel [Fuel Quantity Before],FD1.Date from FuelDetail FD inner join FuelDetail FD1 on FD1.ID = (FD.ID-1) and FD1.Fuel < FD.Fuel Output will be: Set-11: Sql Server Complex Interview Query (Tricky queries) HARD/TRICKY/COMPLEX SQL JOIN QUERIES EXAMPLES Answers: 104) C, 105) C, 106) B, 107) E, 108) D 104). What would be the out-put of the following Sql query? SELECT A.A FROM (SELECT 1 A, 2 B) A JOIN (SELECT 1 A,1 B)B ON A.A = B.B Options: 105). What would be the out-put of the following Sql query? SELECT B.A FROM (SELECT 1 A) A JOIN (SELECT 1 A, 2 B)B ON A.A = B.A Options: 106). What would be the out-put of the following Sql query? SELECT B.A FROM (SELECT 1 A) A JOIN (SELECT 1 A, 2 B)B ON A.A = B.B Options: 107). What would be the out-put of the following Sql query? SELECT * FROM (SELECT 1 A UNION ALL SELECT 2 B) A JOIN (SELECT 1 A,2 B UNION ALL SELECT 1 A, 1 B)B ON A.A = B.B Options: 108). What would be the out-put of the following Sql query? SELECT * FROM (SELECT 1 A UNION ALL SELECT 2 B) A JOIN (SELECT 1 A,2 B)B ON A.A = B.B Options: Now let’s start Mix Interview questions (Theoretical + Queries) Set-12: Sql Server Datatype Interview Questions So let's first go through SQL Server Data types DATATYPE DESCRIPTION bigint Integer data from -2^63 to 2^63-1 binary Fixed-length binary data with a maximum length of 8,000 bytes bit Integer data with either a 1 or 0 value (often for a true or false reading) char Fixed-length non-unicode character data with a maximum length of 8,000 characters cursor A reference to a cursor datetime Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds (but use datetime2 instead) decimal Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 (same as 'nume (decimal(9,2)) = max value 9999999.99) float Floating precision number data from -1.79E + 308 through 1.79E + 308 image Variable-length binary data with a maximum length of 2^31 - 1 bytes int Integer data from -2^31 through 2^31 - 1 (-2 billion to 2 billion approx) money Monetary data values from -2^63 through 2^63 - 1 nchar Fixed-length Unicode data with a maximum length of 4,000 characters ntext Variable-length Unicode data with a maximum length of 2^30 - 1 characters (Deprecated don't use!) numeric Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 (same as 'decim nvarchar Variable-length Unicode data with a maximum length of 4,000 characters real Floating precision number data from -3.40E + 38 through 3.40E + 38 smalldatetime Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute smallint Integer data from -2^15 through 2^15 - 1 (-32000 to 32000 approx) smallmoney Monetary data values from -214,748.3648 to +214,748.3647 sql_variant A data type that stores values of various data types, except text, ntext, timestamp, and sql_variant table A special data type used to store a result set for later processing text Variable-length data with a maximum length of 2^31 - 1 characters (Deprecated - don't u timestamp A database-wide unique number that gets updated every time a row gets updated tinyint Integer data from 0 to 255 uniqueidentifier A globally unique identifier varbinary Variable-length binary data with a maximum length of 8,000 bytes varchar Variable-length non-unicode data with a maximum of 8,000 characters date holds date time holds time datetime2 Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of about 100 nanoseconds, plus more compactly stored datetimeoffset takes international time into account in reading xml for storing or even parsing raw xml data SQL SERVER DATATYPES INTERVIEW QUESTIONS What do you understand by Data-types in sql server? Ans: SQL Server data types defines the characteristic of the data that is stored in a column. Each column, variable and expression has related data type in SQL. How you should choose data type for particular column when you create a table? Ans: The data type should be chosen based on the information you wish to store. for example you would not use an integer data type for storing employee name. What is the very useful datatype introduced in SQL Server 2016? Ans: JSON datatype What are the two types of character data SQL Server supports? Ans: Regular and Unicode What are the Regular character data types? Ans: Char and VarChar What are the Unicode character data types? Ans: NChar and NVarChar How are literal strings expressed with Regular character column? Ans: Single quote 'text'. How are literal strings expressed with Unicode character column? Ans: Must Start with N'text'. What can you define with variable length character data types? Ans: VARCHAR(MAX) How large is VARCHAR(MAX)? Ans: 8000 Bytes in line. Name any five date and time data types? Ans: 1.) DATETIME 2.) SMALLDATETIME 3.) DATE 4.) TIME 5.) DATETIME2 6.) DATETIMEOFFSET What does the PARSE function do? Ans: Parse a value as a requested type and indicate a culture. Syntax? PARSE('date' AS datatype USING culture) What happens when you only want to work with time in a DATETIME data type? Ans: SQL Server stores the date as Jan 1 1900. What do you understand by Timestamp, Difference between Datetime and Timestamp datatypes? Ans: Datetime: Datetime is a datatype. Timestamp: Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes. In fact, in sql server 2008 this column type was renamed (i.e. timestamp is deprecated) to rowversion. It basically means that every time a row is changed, this value is increased. This is done with a database counter, i.e. two different rows that where updated in the same transaction have the same row version. Set-13: TCS Sql Server Tricky Interview Queries 1). How to select random record form a table? Ans: Select top 1 * from order by newId() 2). Suppose that you have table Employee with a column EName which contain Records Employee name(EName) as A,B,A,A,B,D,C,M,A, Write a query which will change/Swap the EName A to B and B to A. Ans: UPDATE Employee set EName = (CASE WHEN EName='A' THEN 'B' WHEN EName='B' THEN 'A' ELSE EName END) 3). Write a query to create a clone of existing table without using Create Command. Ans: SELECT * INTO FROM WHERE 1=2 SELECT TOP 0 * INTO FROM 4). Table Tbl1 has 100 rows, Table Tbl2 has 0 rows so number of rows returned by the below query? SELECT Tbl1.* from Tbl1, Tbl2; Ans : No row will be retun by this query 5). Write a query to print 1 to 100 in sql server without using loops? Ans: Use Recursive common table expression: ;WITH CTE AS ( SELECT 1 [Sequence] UNION ALL SELECT [Sequence] + 1 FROM CTE WHERE [Sequence]