1.3k views

Consider the set of relations

• EMP (Employee-no. Dept-no, Employee-name, Salary)
• DEPT (Dept-no. Dept-name, Location)

Write an SQL query to:

a)Find all employees names who work in departments located at ‘Calcutta’ and whose salary is greater than Rs.50,000.

b)Calculate, for each department number, the number of employees with a salary greater than Rs. 1,00,000.

edited | 1.3k views
0

@Arjun @jothee @srestha In GO-PDF (2018 earlier version) part (b) of the question isn't there..??

Is it corrected now..???

(a)

select Employee-name
from EMP, DEPT
where Salary>50000 and EMP.Dept-no=DEPT.Dept-no and Location="Calcutta"

(b)

select Dept-no, count(*)
from EMP where salary > 100000
group by Dept-no
selected by
0
select Dept-no, count(*)
from EMP where salary > 100000
group by Dept-no

instead of where clause, I think,  will come having some(salary)>100000

because above will give the count value same for each department.

0
I don't think so. Because "Having" clause applies only on grouped tuples whereas "Select" works on individual tuples. Here, we want to group by only those tuples whose salary is greater than 1,00,000.
0
For (b), a department with no employee having salary > 100000 should also be a part of result.

Example, suppose Dept-no 123 does not have a single employee with salary > 100000 then result should also  contain the tuple (123,0).

The query for (b) will only display the (Dept-no, count) tuples in result where count > 0.
0

@Pratik Gawali we cannot expect any records to be outputted when using a GROUP BY clause, when no records exist in your source.The reason is that when you have no records, the GROUP BY clause have nothing to group by, and then is not able to give you any output.

0

@meghna I do agree with you that GROUP BY will not be able to output a record for a dept-no containing no record with salary > 100000 and this is what concerns me as the question is Calculate, for each department number, the number of employees with a salary greater than Rs. 1,00,000.

If the result of the query does not give info about certain departments then how has the query calculated for each department number?

0

@Pratik Gawali I think in sql with a SELECT-FROM-WHERE block, if I say "retrieve the result for each <some attribute>" it means to retrieve the result for those tuples only which satisfy certain conditions present in WHERE clause or other constructs like HAVING(in conjunction to group by) , unlike the mathematical logic.

I found some examples from Navathe, it may help to understand that "each" doesn't only correspond to "each tuple" but "each tuple which satisfies the condition"

a.
SELECT EMPLOYEE-NAME FROM EMP WHERE SALARY > 50000
AND DEPT-NO IN (SELECT DEPT-NO FROM DEPT WHERE LOCATION='Calcutta');

b.
SELECT COUNT(EMPLOYEE-NO),DEPT-NO FROM EMP
GROUP BY (DEPT-NO) HAVING SALARY > 100000;

0
does second query works?
0

I think it wont,

condition in having clause is used to select a group not tuple so "having sal > 1 lac" might not work as we are grouping by dep.no and using "having" clause for "salary".

1
2