Question

Consider the following relational schema, where the primary keys are Bold,
Students(sid, sname, address, dept_id)
Courses(cid, cname, dept_id)
Departments(dept_id, dname, college)
Grades(sid, cid, year, semester, grade)
Write the following queries in SQL. Only use the tables that are absolutely needed for the corresponding query.
1. Find the sids and snames of students who took "Database" course (i.e., cname = ‘Database’) in Fall 2019.
2. Find the average grade of all the students who took "CS327" (i.e., cid=‘CS327’) in Fall 2019. You can assume grade is a numerical attribute.
3. Find the number of distinct courses offered by each department in Fall 2019.
4. Find the sids of students who have taken at least 10 different courses offered by "Computer Science" department (i.e., dname = ‘Computer Science’).

Answer 1

Answer:

The queries for each question are explained.

1. Find the sids and snames of students who took "Database" course (i.e., cname = ‘Database’) in Fall 2019.

 The columns sid and sname are taken from students table.

The course name belong to the courses table.

The semester and year belong to the grades table.

To get the final query, all these tables need to be joined in the FROM clause using JOIN ON keywords.

select s.sid, sname

from students s join grades g on g.sid = s.sid

join courses c on g.cid = c.cid

where cname like 'database%'

and semester like 'Fall'

and year=2019

2. Find the average grade of all the students who took "CS327" (i.e., cid=‘CS327’) in Fall 2019. You can assume grade is a numerical attribute.

The grade column is taken from grades table.

The course id belongs to the grades table.

The semester and year also belong to the grades table.

The final query is formed using the grades table only.

 select avg(grade)

from grades

where cid like 'CS327'

and semester like 'Fall'

and year=2019

 3. Find the number of distinct courses offered by each department in Fall 2019.

 The department id belongs to the courses table.

The course id belongs to the grades table.

The semester and year also belong to the grades table.

To get the final query, the courses and the grades tables are joined in the FROM clause using JOIN ON keywords.

select dept_id, count(distinct g.cid)

from courses c join grades g on c.cid = g.cid

where semester like 'Fall'

and year=2019

group by dept_id