CODEWITHSUNDEEP
mysqlsql-serverdatabase
Abhishek
Abhishek

Reputation: 680

how to find complex SQL query

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

Upvotes: 0

Views: 259

Answers (1)

TK Rohit
TK Rohit

Reputation: 28

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

Upvotes: 0

Related Questions