Not sure im doing sql queries correctly

Question

department(dept_name, building, budget)

course(course_id, title, dept_name, credits)

instructor(ID, name, dept_name, salary)

section(course_id, sec_id, semester, year, building, room_number, time_slot_id)

teaches(ID, course_id, sec_id, semester, year)

student(ID, name, dept_name, tot_cred)

takes(ID, course_id, sec_id, semester, year, grade)

1. Find the course title, semester and year of each course that was taken by more than 50 students

   select course.title , takes.semester , takes.year
   from course
   natural join takes
   where course.course_id = takes.course_id
   having count(distinct ID) > 50
   

2. Find the title of each course that had more than one section

   select title 
   from course
   natural join section 
   where course.course_id = section.course_id
   having count(distinct sec_id) > 1
   

3. Find the IDs of all instructors who taught more than 5 courses in the Comp. Sci. department

   select ID
   from instructor
   natural join course
   where course.dept_name = instructor. dept_name
   having count(credits)>5
   

Also should this be credits or course_id

4. Find all instructors who didn’t teach any module offered by the Biology department

This one I don’t even no where to start

Answer 1

All your queries are missing GROUP BY clauses.

And if you use NATURAL JOIN, you don't need a WHERE clause to relate the tables -- NATURAL JOIN does that automatically.

select course.title , takes.semester , takes.year
from course
natural join takes
GROUP BY course.title, takes.semester, takes.year    
having count(distinct ID) > 50

select title 
from course
natural join section 
GROUP BY title
having count(distinct sec_id) > 1

select ID
from instructor
natural join course
GROUP BY ID
having count(credits)>5

Your last query has several other problems. You're not linking the instructor to the course using the teaches table, and you're not checking whether the course is in the Comp. Sci. department.

SELECT i.id
FROM instructor AS i
JOIN teaches AS t ON i.id = t.sec_id
JOIN course AS c ON t.course_id = c.id
WHERE c.dept_name = "Comp. Sci."
HAVING COUNT(*) > 5