Question

SQL FORMAT (11-13 are completed and correct)

-- 11. what are the names of all students who have taken some course? Don't show duplicates.
select distinct(name) from student where tot_cred > 0;

-- 12. what are the names of departments that offer 4-credit courses? Don't list duplicates.
select distinct(dept_name) from course where credits=4;

-- 13. What are the names and IDs of all students who have received an A in a computer science class?
select distinct(name), id from student natural join takes natural join cours$

-- 14. How many B grades have been given to physics majors?

-- 15. What is the average total credits of students who have taken CS-319?

-- 16. What is the average total credits of students who have taken CS-101?

-- 17. What are the course IDs of courses taught by instructor Katz?

-- 18. What are the course IDs of all courses offered by instructor Crick's department?

-- 19. What is the course_id, semester, and year of sections of computer science courses?
-- Don't show duplicates.

-- 20. What are the names of students who have taken a class taught by instructor Srinivasan?

Answer 1

NOTE : SINCE THERE IS NO SCHEMA OF TABLE PROVIDED THEREFOR THE COLUNM NAMES HAVE BEEN TAKEN TENTATIVELY AND QUERIES MAYNOT EXECUTE DIRECTLY. YOU NEED TO CHNAGE THE COLUMN NAMES AS PER YOUR TABLE SCHEMA.

14. How many B grades have been given to physics majors?

QUERRY : SELECT count(*) from student WHERE dept_name='Physics Major' AND grade='B';

Explanation : In the above query the count function of SQL has been used to count the number of students that are in the department of physics major with a grade B.

15. What is the average total credits of students who have taken CS-319?

QUERRY : SELECT avg(credit) from student where course='CS-319';

Explanation : In the above query the AVG function of SQL has been used to directly calculate the average credit of the student who has taken the course CS-319.

16. What is the average total credits of students who have taken CS-101?

QUERRY : SELECT avg(credit) from student where course='CS-101';

Explanation : In the above query the AVG function of SQL has been used to directly calculate the average credit of the student who has taken the course CS-101.

17. What are the course IDs of courses taught by instructor Katz?

QUERRY : SELECT course_ID from student where instructor='Katz';

Explanation : In the above query the course_ID has been chosen for the subjects that has Katz as their instructor.

18. What are the course IDs of all courses offered by instructor Crick's?

QUERRY : SELECT course_ID from student where instructor='Crick';

Explanation :In the above query the course_ID has been chosen for the subjects that has Crick as their instructor.

19. What is the course_id, semester, and year of sections of computer science courses?

QUERRY : SELECT DISTINCT course_id, semester, year from student WHERE course="Computer Science';

Explanation : Since there must not be any repetition of values the DISTINCT keyword has been used to select only un-repeated values from from the table students that have course computer science.

20. What are the names of students who have taken a class taught by instructor Srinivasan?

QUERRY : SELECT name_student from student where instructor='Srinivasan';

Explanation : The above query gives the name of all the students that have Srinivasan as their instructor.