The schema of the database is provided below ( keys are in bold) student ( sid ,
ID: 3768124 • Letter: T
Question
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 (cno, cname, dname)
major (dname, sid)
section (dname, cno, sectno, pname)
enroll( sid, grade, dname, cno, sectno)
a) Print the name(s) and sid(s) of the student(s) enrolled in the most classes.
b) Print the names of departments that have one or more majors who are under 18 years old.
c) Print the names and majors of students who are taking one of the College Geometry courses. (Hint: You'll need to use the "like" predicate and the string matching character in your query.)
Explanation / Answer
b)
SELECT DISTINCT dept.dname
FROM dept
inner join major on major.dname = dept.dname
inner join student on major.sid = student.sid and student.age < 18
group by dept.dname
having count(*)>0;
c)
SELECT dept.dname, dept.numphds
FROM dept
LEFT JOIN course on course.dname = dept.dname
and course.cname LIKE '%College Geometry%'
where course.dname is NULL
you can do the same with not exists clause
SELECT dept.dname, dept.numphds
FROM dept
WHERE NOT EXISTS
( select 1 from course
where course.dname = dept.dname
and course.cname LIKE '%College Geometry%'
)
a)I m not sure with this hope this helps u
select s.id, s.name
from student s
join enroll e on e.sid = s.id
group by s.id, s.name
order by count(*) desc
limit 1