Bakhtovar Yusufi Bakhtovar Yusufi - 1 month ago 10
SQL Question

How to Select data from multiple tables with several conditions?


There are tables FACULTIES (F_NUMB, F_NAME, F_TELEF) and STUDENTS (S_NUM, SURNAME, SPECIALITY, GROUP, FACULTY_NUMB).
Please write SQL query to achieve faculties names in which the count of students are greater than in faculty “Computer science”.


This is what I'm doing:

select faculties.f_name, count(STUDENTS.S_NUM) from STUDENTS, FACULTIES
where students.fac_num=FACULTIES.F_NUMB group by faculties.f_name
having count(STUDENTS.S_NUM) > (select STUDENTS.S_NUM from STUDENTS, FACULTIES
where faculties.f_name='Computer Science' and students.fac_num=faculties.f_numb);


But still it is not working. Please help me..

Answer

Try this:

SELECT f.f_name
FROM faculties f
WHERE (SELECT COUNT(1) -- Here we get the student count for the faculty
  FROM students s 
  WHERE f.f_numb = s.faculty_numb) > 
  (SELECT COUNT(1)  -- Here we get the CS faculty student count
  FROM students s JOIN faculties f ON (f.f_numb = s.faculty_numb) 
  WHERE  f.f_name = 'Computer science');

For each row you check how many students the faculty has and compare it against the number of students the "Computer science" faculty has.