1)The schema of the database is provided below ( keys are in bold ) student ( si

Question

1)The schema of the database is provided below (keys are in bold)

student (sid, sname, sex, age, year, gpa)
dept (dname, numphds)
prof (pname, dname)
course (dname, cno, cname)
major (dname, sid)
section (dname, cno, sectno, pname)
enroll (sid, dname, cno, sectno, grade)

a) Print the name(s) of student(s) with the lowest gpa.

b) For each Computer Sciences class, print the cno, sectno, and the average gpa of the students enrolled in the class.

c) Print the course names, course numbers and section numbers of all classes with less than six students enrolled in them.

Explanation / Answer

a)

the query for this will be:

select sname from student where gpa = (select MIN(gpa) from student)

Here we need to use the nested queries. First we will find the min gpa in the table and then we will use it to find the sname.

b)

query will be

select cno,sectno,AVG(gpa) from enroll where dname = Computer Science

Here we need to query the table columns when department name is computer science

c)

the query will be

SELECT co.cname, co.cno, en.sectno, COUNT(en.sid) as studentcount
FROM course co
LEFT JOIN enroll en on en.cno = co.cno
GROUP BY co.cname, co.cno, en.sectno
HAVING COUNT(en.sid) < 6

here we need to merge two tables for the query to work and we need to count the studentid(sid) for checking if the number of students are greater than 6 or not. course and enroll table needs to be joined.

co is used for course table

en for enroll table.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.