Common Interview questions SQL

  What are implementation inheritance and interface inheritance?

Implementation inheritance is achieved when a class is derived from another class in such a way that it inherits all its members. Interface inheritance is when a class inherits only the signatures of the functions from another class.


Is is possible to force garbage collector to run?

Yes, we can force garbage collector to run using System.GC.Collect().

How does object pooling and connection pooling differ?

In Object pooling, you can control the number of connections. In connection pooling, you can control the maximum number reached. When using connection pooling, if there is nothing in the pool, a connection is created since the creation is on the same thread. In object pooling, the pool decides the creation of an object depending on whether the maximum is reached which in case if it is, the next available object is returned. However, this could increase the time complexity if the object is heavy. 


Explain the purpose of CultureInfo class. What namespace contains it?

Answer:    System.Globalization namespace contains CultureInfo class. This class provides information about a specific culture, i.e. datetime format, currency, language etc.

What is the use of assert() method?

Answer:   Assert method is in debug compilation. It takes a boolean condition as a parameter. It shows error dialog if the condition is false and if the condition is true, the program proceeds without interruption.

-31. Select only one/top 1 record from "EmployeeDetail" table.

 

SELECT TOP 1 * FROM [EmployeeDetail]

  Select all employee detail with First name "Vikas","Ashish", and "Nikhil".

 

SELECT * FROM [EmployeeDetail] WHERE FirstName IN('Vikas','Ashish','Nikhil')

-33. Select all employee detail with First name not "Vikas","Ashish", and "Nikhil".

 

SELECT * FROM [EmployeeDetail] WHERE FirstName NOT IN('Vikas','Ashish','Nikhil')

Display first name and Gender as M/F.(if male then M, if Female then F)

 

SELECT FirstName, CASE  WHEN Gender = 'Male' THEN 'M'

WHEN Gender = 'Female' THEN 'F'

END AS [Gender]

Get employee details from "EmployeeDetail" table whose Salary greater than 600000

SELECT * FROM [EmployeeDetail] WHERE Salary > 600000

. Get employee details from "EmployeeDetail" table whose Salary less than 700000

 

SELECT * FROM [EmployeeDetail] WHERE Salary < 700000

 

 Get employee details from "EmployeeDetail" table whose Salary between 500000 than 600000

 

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

Select second highest salary from "EmployeeDetail" table.

 

SELECT TOP 1 Salary FROM

(

      SELECT TOP 2 Salary FROM [EmployeeDetail] ORDER BY Salary DESC

) T ORDER BY Salary ASC

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

Write the query to get the department, total no. of departments, total(sum) salary with respect to department from "EmployeeDetail" table.

SELECT Department, COUNT(*) AS [Dept Counts], SUM(Salary) AS [Total Salary] FROM [EmployeeDetail]

GROUP BY Department

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

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

 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

Write down the query to fetch Project name assign to more than one Employee

--ANS: 

Select ProjectName,Count(*) [NoofEmp] from [ProjectDetail] GROUP BY ProjectName HAVING COUNT(*)>1