The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+14 votes
936 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:

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

asked in Databases by Veteran (59.5k points)
edited by | 936 views

3 Answers

+18 votes
Best answer

(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
answered by Active (3.3k points)
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.

+4 votes

1)  SELECT  e.Employee-name
     FROM  EMP e , DEPT  d
      WHERE e.Dept-no=d.Dept-no
               AND

                      e.Salary>50,000

                    AND
               d.location='Calcutta';
or,
 SELECT e.Employee-name
 FROM
        EMP e
 WHERE
       e.Salary>50,000 AND
       e.Dept_no = (SELECT d.Dept_no

                            FROM DEPT d

                              WHERE location='Calcutta' );

2)   SELECT Dept-no, count(Employee_no)
      FROM EMP WHERE salary > 100000
       GROUP BY Dept-no;

answered by Active (3k points)
edited by
+3 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;
answered by Loyal (7.9k points)


Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

37,941 questions
45,453 answers
131,195 comments
48,211 users