Bissoy.com
Doctors Medicines MCQs Q&A

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

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 Correct Answer 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

Related Questions

Consider a database that has the relation schemas EMP(EmpId, EmpName, DeptId), and DEPT(DeptName, DeptId) Note that the deptId can be permitted to be NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus. (I) { t |∃u ∈ EMP(tEmpName] = u[EmpName] ∧ ∀ v∈ DEPT(t[DeptId] ≠ v[DeptId]))} (II) {t |∃ u ∈ EMP(t[EmpName] = u[EmpName] ∧ ∃ v ∈ DEPT(t[DeptId] ≠ v[DeptId]))} (III) {t | ∃ u∈ EMP(t[EmpName] = u[EmpName] ∧ ∃ v ∈ DEPT(t[DeptId] = v[DeptId]))} Which of the above queries are safe?
Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation. Employee (empId, empName, empAge) Dependent(depId, eId, depName, depAge) Consider the following relational algebra query: ΠempId(employee)-ΠempId (employee⋈(empId = eID)∧(empAge ≤ depAge)dependent) The above query evaluates to the set of empIds of employees whose age is greater than that of
Consider the following relational schema: employee(empId,empName,empDept) customer(custId,custName,salesRepId,rating) salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return? SELECT empName FROM employee E WHERE NOT EXISTS (SELECT custId FROM customer C WHERE C.salesRepId = E.empId AND C.rating <> ’GOOD’);
What is the total number of employees in the company? Which of the following option is sufficient alone to find the answer? A) In the company, employees like either tea or coffee or cold drink. Out of the total employees, 25% like tea and 40% like coffee. Out of remaining employees, 20% like cold drink and remaining employees don’t like any of these drinks. B) Out of total employees 18% working in the marketing departments, 35% working in IT departments. From the remaining employees 25% working in HR departments and 45% working in content departments and remaining 141 are working in other departments. C) Employees working in the company is form state X, Y and Z. 20% of employees working in the company is from state X and from remaining 60% employees is from state Z and the remaining employees is from the state Y. D) 60% of the employees in the company are married and remaining employees are unmarried. The ratio of male and female unmarried employees is 4 : 5 and ratio of married male and female employees is 3 : 4.
Study the given pie chart and answer the question given below. Percentage breakup of employees working in different departments of a company Department the ratio of men and women male female Information technology (IT) 11 12 Account 5 4 Marketing 8 9 Production 4 3 Human Resource (HR) 9 5 Total number of employees = 1800 If 10% of the men working in the Accounts department are transferred to the HR department, and one-fourth of the female employees working in the Production department is transferred to the Marketing department, then the number of male employees working in the HR department is what percent less than the number of female employees working in the Marketing department? (correct to one decimal place)
The break-up (degree wise) of the total number of employees of a company working in five offices, A, B, C, D and E, in a city, in 2020 is shown in the following pie graph. Total Number of Employees = 3400 The ratio of the number of male and female employees working in office A is 5 ∶ 6 and 50% of the employees in office E are females. Let a be the ratio of the number of male employees in A to that of the female employees in E and b be the ratio of the number of male employees in E to that of the female employees in A. What is the ratio a ∶ b?
Study the given pie chart and answer the question that follows: The pie chart shows the break-up of the total number of employees of a company in 2019, in five departments A, B, C, D, and E. Total number of employees=4500 If the ratio of male employees to female employees in department A is 3 : 4, then the number of employees in department C is what percentage more than the number of female employees in department A (nearest to an integer)?
The given question is followed by two statements numbered I and II. The question may or may not be answered with the help of these statements. You have to decide if these statements are sufficient to answer the question. The four employees of a company have monthly salaries that total Rs. 30,000. What is the salary of the highest paid of these employees ? Statements: I. The highest paid employee has a monthly salary that is twice as great as that of each of the other three employees. Il. The highest paid employee has a monthly salary that is exactly Rs. 6000 greater than that of each of the other 3 employees.
Given below are two quantities named A and B. Based on the given information, you have to determine the relation between the two quantities. You should use the given data and your knowledge of Mathematics to choose between the possible answers. What is the average salary of 14 employees? I. Average salary of 5 ladies’ employees is Rs. 5000. II. Average salary of 5 male employees is Rs. 8000. III. Average salary of 4 peons (excluding male and lady employee) is Rs. 2000
Given the following schema: employees(emp-id, first-name, last-name, hire-date, dept-id, salary) departments(dept-id, dept-name, manager-id, location-id) You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query: SQL>SELECT last-name, hire-date FROM employees WHERE (dept-id, hire-date) IN (SELECT dept-id, MAX(hire-date) FROM employees JOIN departments USING(dept-id) WHERE location-id = 1700 GROUP BY dept-id); What is the outcome?