edited by
12,248 views
22 votes
22 votes

Consider the set of relations

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

Write an SQL query to:

  1. Find all employees names who work in departments located at ‘Calcutta’ and whose salary is greater than Rs.50,000.
  2. Calculate, for each department number, the number of employees with a salary greater than Rs. 1,00,000.
edited by

4 Answers

Best answer
32 votes
32 votes

(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
9 votes
9 votes
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 votes
0 votes
Select EMP.Employee-name  

FROM EMP natural join DEPT

WHERE DEPT.Location= 'Calcutta' and Salery > 50,000.
0 votes
0 votes
for  option a

select Employee-name

from EMP

where  salary >’50,000’ and

                         Dept-no in (select  Dept-no

                          from DEP

                           where location =’Calcutta’

                         );

 

for b

select Dept -no,count(Employee-no)

from EMP

where salary>’1,00,000’

group by Dept-no.

Related questions

3 votes
3 votes
3 answers
1
go_editor asked Feb 8, 2018
1,793 views
Consider the set of relationsEMP (Employee-no. Dept-no, Employee-name, Salary)DEPT (Dept-no. Dept-name, Location)Write an SQL query to:Calculate, for each department numb...
39 votes
39 votes
2 answers
2
Kathleen asked Sep 23, 2014
19,635 views
Which of the following is/are correct?An SQL query automatically eliminates duplicatesAn SQL query will not work if there are no indexes on the relationsSQL permits attri...
25 votes
25 votes
1 answer
3
Kathleen asked Sep 23, 2014
4,009 views
Let $R = (A, B, C, D, E, F)$ be a relation scheme with the following dependencies $C \rightarrow F, E \rightarrow A, EC \rightarrow D, A \rightarrow B $. Which one of the...
38 votes
38 votes
1 answer
4
Kathleen asked Sep 23, 2014
16,788 views
Consider the join of a relation $R$ with a relation $S$. If $R$ has $m$ tuples and $S$ has $n$ tuples then the maximum and minimum sizes of the join respectively are$m+n$...