Tuesday, 15 July 2014

GROUP BY : SQL INTERVIEW QUERIES QUESTIONS AND ANSWERS WITH EXAMPLE FOR EXPERIENCED SET-5

SQL GROUP BY & HAVING INTERVIEW QUERIES
This is 5th post related to SQL interview queries with examples. In this post we will discuss most important SQL server queries, which is related to "Group by" keyword. Group by related queries is most frequently asked in all interview. This post contains questions for both fresher and experienced developers.
About GROUP BY:-
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

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 ProjectName HAVING COUNT(*)>1


Click here for next set(MORE THAN 100 QUERIES)
Visit Our New Site for HR Interview Questions Answers http://www.interviewboat.com/Company/GetCompany
Sponsored Ads

15 comments:

  1. Thanks a lot however for me it's very useful

    ReplyDelete
  2. Thanks a lot however for me it's very useful

    ReplyDelete
  3. very helpful

    ReplyDelete
  4. Seems 47 and 48 needs a small correction - Shouldn't the column names be Maximum Salary, Minimum Salary instead of Average Salary..??

    ReplyDelete
  5. hi friends i have interview at 7th on Group By, Joins , Stored Procedures, Triggers, Views these topics so plz could you share the interview questions. i already search some site but i want more because i need this job...

    ReplyDelete