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
318 views
in Programming by (101k points)
closed by

The relation scheme given below is used to store information about the employees of a company, where empId is the key and deptId indicates the department to which the employee is assigned. Each employee is assigned to exactly one department.

emp(empId, name, gender, salary, deptId)

Consider the following SQL query:

select deptId, count(⋆)

from emp

where gender = "female" and salary > (select avg(salary) from emp)

group by deptId;

The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of


1. female employees in the department.
2. employees in the department.
3. employees in the company.
4. female employees in the company.

1 Answer

0 votes
by (102k points)
selected by
 
Best answer
Correct Answer - Option 3 : employees in the company.

Answer: Option 3

Explanation:

 (select avg(salary) from emp)

This part of the Query will get evaluated first and gives " Average Salary of all employees in the company."

select deptId, count(⋆)

from emp

where gender = "female" and salary > (select avg(salary) from emp)

group by deptId;

this will count for each department no of employees who are female and whose salary is greater than the average salary.

Lets take an example 

Empid

Name

Gender

Salary

deptid

1

A

Male

2000

CSE

2

B

Female

3000

CSE

3

C

Male

4000

CSE

4

D

Female

5000

CSE

5

E

Male

3000

ME

6

F

Female

4000

ME

7

G

Male

4000

ME

8

H

Female

2000

ME

9

I

Male

1000

CE

10

J

Female

2000

CE

11

K

Male

4000

CE

12

L

Female

5000

CE

13

M

Male

3000

EC

14

N

Female

4000

EC

15

O

Female

5000

EC

 

(select avg(salary) from emp) this will return 3400 as the average salary.

and final Query result will be

Deptid

count

CSE

1

ME

1

CE

1

EC

2

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

...