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 ascendingAns: 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)
good practice
ReplyDeleteThanks a lot however for me it's very useful
ReplyDeleteThanks a lot however for me it's very useful
ReplyDeleteGreat work!!!!
ReplyDeleteGud for practice
ReplyDeleteYes sir
DeleteGud for practice
ReplyDeleteVery Nice
ReplyDeletevery helpful
ReplyDeleteSeems 47 and 48 needs a small correction - Shouldn't the column names be Maximum Salary, Minimum Salary instead of Average Salary..??
ReplyDeleteColumn alias name
DeleteColumn alias name
DeleteYou are right
Deleteaweome...........
ReplyDeleteaweome.....
DeleteExcellent work ....
ReplyDeletehi 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...
ReplyDeleteHope you got the JOB
DeleteAwesome
ReplyDeleteIt was very Helpful! thanks!
ReplyDeleteVery helpful..Thanks alot ..:-)
ReplyDeletethere is a slary column in salary table and location column is in bank table and empid column in emp table then how to write query in sql resulting the data of total salary of an employee based on branch wise
ReplyDeletethank you
ReplyDeleteits very usefull to me
thank you its very useful
ReplyDeleteGood explanation
ReplyDeleteThank you..Really helpful.
ReplyDeletethank so much
ReplyDelete