SQL Assignment The schema of these relations is: Courses (course, credits, days,
ID: 3586435 • Letter: S
Question
SQL Assignment
The schema of these relations is: Courses (course, credits, days, time, room, title, prof, dept) Profs (pname, given, office, ext, email) Students (name, given, linux, major, email) Enroll (linux, course, grade) Coreq (lect, lab) Dayx (days, dayo, day, jour) -- ex: MWF 1 Mon | lundi Write the necessary SQL queries for the following task 1. List all course codes and titles and count of enrolled students, for courses that have at least 2 students enrolled (like last one, but requires a join). Write the necessary SQL queries for the following tasks Queries that could be done using a subquery (but not necessarily a grouping): 1. List all courses taught in the rooms used by prof Jensen (code and title). 2. List names of all students enrolled in courses taught by the prof who teaches CSC111Explanation / Answer
1. select c.course,c.title,count(s.name) from Course c join Enroll e join Students s on s.linux=e.linux and e.course=c.course having count(e.linux)>=2;
The below are the subqueries which satisfies the given requests:-
1. select course,title from Courses where room in (select room from Courses where prof='Jensen');
2. select name from students where linux in (select s.linux from students s,Enroll e,Courses c,Profs p where s.linux=e.linux and e.course=c.course and c.prof=p.pname and c.coursename = 'CSC111');
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.