how to find complex SQL query

Question

I have a table like this:

Table: Employee

   EmpId:  INTEGER NOT NULL
   DeptId: INTEGER NOT NULL
   Name:   Char(40) NOT NULL
   Age:    INTEGER NOT NULL
   Salary: INTEGER NOT NULL

Table: Department

   DeptId: INTEGER NOT NULL
   Name:   Char(40) NOT NULL
   Rank:   INTEGER NULL

The query that I need to find is:-

a) to Rank all the department by decreasing order of average age of all the employees in that department and then update the rank column accordingly. Thus the department with the highest average employee age should be ranked 1, the department with the second highest average employee age should be ranked 2, and so on.

In case more than one department have the same average employee age, then they should be ranked by DeptId which is unique, The rank of deprtment without any employee should not be updated.

b) To list the names of the department along with the total budgets in the department. Total budget is simply defined as sum of the salary of the employees in that department.In case the department does not have any employees then their sum should be 0.

c) SQL query to output th names of all the departments such that the average salary of the department is greater than 10,00,000

SQL server or mysql? You could improve this question by including sample data and expected output. AND you should show what you have tried otherwise cynics might suspect you want them to do your job for you. — P.Salmon
See meta.stackoverflow.com/questions/333952/…, and appease those cynics — Strawberry
Did you look into the SQL commands AVG (to get the average age and salary), RANK, DENSE_RANK or ROW_NUMBER for the ranking function? To me it looks very much like homework... — Lmu92
That is not more complex than making spaghetti. Please do not call simple joins complex - wait until your sql statements span 2-3 pages before you call them complex. — TomTom

TK Rohit TK Rohit · Accepted Answer · 2018-07-21T11:52:30

I have created temporary tables and data for running the queries

CREATE TABLE #Employee(
EmpId  INTEGER NOT NULL,
DeptId INTEGER NOT NULL,
Name   Char(40) NOT NULL,
Age    INTEGER NOT NULL,
Salary INTEGER NOT NULL
);

CREATE TABLE #Department(
DeptId INTEGER NOT NULL,
Name   Char(40) NOT NULL,
Rank   INTEGER NULL
);

INSERT INTO #Employee VALUES(1,1,'A',34,9000000),
                            (2,2,'B',25,450000),
                            (3,1,'C',45,600000),
                            (4,1,'D',23,340000),
                            (5,2,'E',21,580000),
                            (6,3,'F',26,7500000),
                            (7,1,'G',27,650000),
                            (8,2,'H',28,410000),
                            (9,1,'I',29,120000),
                            (10,2,'J',54,150000),
                            (11,3,'I',65,1500000);

INSERT INTO #Department (DeptId,Name) VALUES(1,'Dept 1'),
                                            (2,'Dept 2'),
                                            (3,'Dept 3');

And below are the queries that will suffice your requirements

a)

   UPDATE #Department SET RANK=a.Rank
   FROM (SELECT DeptId,AVG(Age) AS 'AvgAge',ROW_NUMBER() OVER (ORDER BY AVG(AGE)DESC,DeptId ASC) AS 'Rank' 
         FROM #Employee GROUP BY DeptId)a 
   WHERE #Department.DeptId=a.DeptId

b)

   SELECT d.Name,SUM(e.Salary) AS 'Total Budget' 
   FROM #Employee e JOIN #Department d 
   ON e.DeptId=d.DeptId 
   GROUP BY d.Name

c)

   SELECT d.Name,AVG(e.Salary) AS 'Avg Sal > 1000000' 
   FROM #Employee e JOIN #Department d 
   ON e.DeptId=d.DeptId
   GROUP BY d.Name HAVING AVG(e.Salary)>1000000

how to find complex SQL query

1 Answers