Use app×
Join Bloom Tuition
One on One Online Tuition
JEE MAIN 2025 Foundation Course
NEET 2025 Foundation Course
CLASS 12 FOUNDATION COURSE
CLASS 10 FOUNDATION COURSE
CLASS 9 FOUNDATION COURSE
CLASS 8 FOUNDATION COURSE
0 votes
1.2k views
in Programming by (106k points)
closed by

Consider the following schema:

Emp (Empcode, Name, Sex, Salary, Deptt)

A simple SQL query is executed as follows :

SELECT Deptt FROM Emp

WHERE sex = ‘M’

GROUP by Dept

Having avg (Salary) > { select avg (Salary) from Emp}

The output will be


1. Average salary of male employee is the average salary of the organization
2. Average salary of male employee is less than the average salary of the organization 
3. Average salary of male employee is equal to the average salary of the organization
4. Average salary of male employees is more than the average salary of the organization  

1 Answer

0 votes
by (103k points)
selected by
 
Best answer
Correct Answer - Option 4 : Average salary of male employees is more than the average salary of the organization  

The correct answer is "option 4".

QUERY

SELECT Deptt FROM Emp           // return department from Emp table

WHERE sex = ‘M’                          // of all male employees

GROUP by Dept                           // group by department name

Having avg (Salary) > { select avg (Salary) from Emp}   // all male employees whose salary > average salary of the organization

CALCULATION:

Consider the following table:

Department

Salary

Sex

A

2000

M

B

2100

M

C

1000

F

C

4000

M

B

1200

F

C

2400

M

A

2300

M

B

2600

M

B

1800

F

A

2500

M

-

        21900 (total)

-

The average salary of all the Employees are: \(\frac{21900}{10}\) = 2190

The SQL query -

SELECT Deptt FROM Emp           

WHERE sex = ‘M’                          

GROUP by Dept                           

will produce the table :

Department

Salary

Sex

A

2000

M

A

2300

M

A

2500

M

B

2100

M

B

2600

M

C

4000

M

C

2400

M

Now this query line will give table :

Having avg (Salary) > { select avg (Salary) from Emp} 

Department

Total Salary

AVG Salary

A

2000 + 2300 + 2500 = 6800

\(\frac{6800}{3}\) = 2266

B

2100 + 2600 = 4700

\(\frac{4700}{2}\) = 2350

C

4000 + 2400 = 6400

\(\frac{6400}{2}\)= 3200

Final output of the query will be :

Department

A

B

C

Hence, this query will return the department name in which the salary of male employees is greater than the average salary of all the employees in the organization.

Welcome to Sarthaks eConnect: A unique platform where students can interact with teachers/experts/students to get solutions to their queries. Students (upto class 10+2) preparing for All Government Exams, CBSE Board Exam, ICSE Board Exam, State Board Exam, JEE (Mains+Advance) and NEET can ask questions from any subject and get quick answers by subject teachers/ experts/mentors/students.

Categories

...