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: Find all employees names who work in departments located at ‘Calcutta’ and whose salary is greater than Rs.50,000. Calculate, for each department number, the number of employees with a salary greater than Rs. 1,00,000. Databases gate1999 databases sql easy descriptive + – Kathleen asked Sep 23, 2014 edited Oct 10, 2018 by Krithiga2101 Kathleen 12.2k views answer comment Share Follow See 1 comment See all 1 1 comment reply SomeEarth commented Jan 14, 2019 reply Follow Share @Arjun @jothee @srestha In GO-PDF (2018 earlier version) part (b) of the question isn't there..?? Is it corrected now..??? 0 votes 0 votes Please log in or register to add a comment.
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 Aravind answered Sep 25, 2014 selected Jul 20, 2015 by Arjun Aravind comment Share Follow See all 10 Comments See all 10 10 Comments reply Show 7 previous comments no.18 commented Mar 2, 2021 reply Follow Share can we write it as select Dept-no, count(*) From Emp Group By Dept-no Having Count (Salary >100000) 1 votes 1 votes Abhrajyoti00 commented Sep 27, 2022 reply Follow Share @ijnuhb It will give output for each count multiplied with number of rows in Dept. It will be wrong. 0 votes 0 votes shashankrustagi commented Oct 13, 2022 reply Follow Share thanks 0 votes 0 votes Please log in or register to add a comment.
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; Tuhin Dutta answered Aug 9, 2017 Tuhin Dutta comment Share Follow See all 6 Comments See all 6 6 Comments reply Show 3 previous comments Amcodes commented Nov 2, 2020 reply Follow Share No, HAVING , is Executed first . 0 votes 0 votes Pranavpurkar commented Aug 8, 2022 reply Follow Share i am getting something like this! SELECT COUNT(EMPLOYEE-NO),DEPT-NO FROM EMP e,DEPT d WHERE e.dept-no=d.dept-no HAVING SALARY > 100000 GROUP BY (DEPT-NO); pls check it once! 0 votes 0 votes gaurav_kumar commented Nov 13, 2023 reply Follow Share Query 2 is wrong. Once you use Group By (some un-aggrerated attributes set (say S)), then Having clause can only have un-aggrerated attributes set(X which is subset of S), and any aggregated attributes are allowed. Here Salary is un-aggrerated attribute which is not used in Group By clause, so you can't use it. You can write the query by using the "where" clause for removal of unsatisfied tuples before logically grouping them. 0 votes 0 votes Please log in or register to add a comment.
0 votes 0 votes Select EMP.Employee-name FROM EMP natural join DEPT WHERE DEPT.Location= 'Calcutta' and Salery > 50,000. Oaesp answered Nov 11, 2021 Oaesp comment Share Follow See all 0 reply Please log in or register to add a comment.
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. 24aaaa23 answered Oct 27, 2022 24aaaa23 comment Share Follow See all 0 reply Please log in or register to add a comment.