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
