Basic SQL questions

Write the following queries in SQL, using the university schema

Please, Take the sample data from attached link for university schema. Using the university schema, write the following queries.

• Find the names of courses in Computer science department which have 3 credits

select title from course where dept_name = 'Comp. Sci.' and credits=3;        

• For the student with ID 24746(or any other value), show all course_id and title of all courses registered for by the student.

select course_id, title from course as c left outer join
student as s on c.dept_name = s.dept_name where s.id = '24746';        

• Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names)

Natural join: A natural join in SQL combines rows from two or more tables based on the common column(s) between them.

select distinct(name) from student where 
student.id = (select id from student natural join takes where dept_name = 'Comp. Sci.');        

• For the student with ID 24746(or any other value), show all course_id and title of all courses registered for by the student.

select t.course_id, c.title from takes as t left outer join course as c on t.course_id = c.course_id where id = '24746';        

or

select course_id, title from takes natural join course where ID='24746';        

• As above, but show the total number of credits for such courses (taken by that student). Don't display the tot_creds value from the student table, you should use SQL aggregation on courses taken by the student.

select sum(credits) from takes natural join course where id='24746';        

• As above, but display the total credits for each of the students, along with the ID of the student; don't bother about the name of the student. (Don't bother about students who have not registered for any course, they can be omitted)

select id,sum(credits) from takes natural join course group by id;        

• Display the IDs of all instructors who have never taught a course Oracle uses the keyword minus in place of except; (2) interpret "taught" as "taught or is scheduled to teach") As above, but display the names of the instructors also, not just the IDs.

select name,id from instructor where id not in (select id from teaches);        

Reference : https://db-book.com/