Given the following tables: students(sid name,age,gpa) . courses(cid,deptid, nam

Question

Given the following tables: students(sid name,age,gpa) . courses(cid,deptid, name) . professors(ssn,name,address,phone,deptid) section.grade enrollment (sid.cid foreign key (sid) references students, foreign key (cid) references courses foreign key (cid,section) references teaches) * teaches(cid section ssn, foreign key (cid) references courses foreign key (ssn) references professors) Domain cid is in '198:11', 640:151','198:112'...^ deptid is in ['cs','math','music'... .grade is in f'A','B', C"....] * section, age, ssn are an integers * address, phone, name are strings . gpa is float Provide SQL instructions for each of the following questions 1. Create the database schema. 2. Find the name of professors that work for the cs department 3. Find those students (sid) enrolled in courses in the cs department 4. List ssn and name of professors that work for the cs department, but are not teaching any cs courses. 5. List the number of courses offered by each department. Just the number of courses (not sections) 6. List of those departments that offer more than 10 courses. 7. Produce a list of the name of those students whose professor's name starts with an M. Your result must have no duplicates

Explanation / Answer

Answers)

Database schema should be created as per the given tables.

Q2) List professor name which work for cs department

Answer)

select name from professors where ssn in (

select ssn from teaches where cid in(

select cid from courses where name ='cs'

)

);

Q3) Find sid of students enrolled in cs department.

Answer)

select sid from enrollment where cid in(

select cid from courses where name ='cs'

);

Q4) List ssn and name of professors who work for cs department, but not teaching cs courses.

Answer)

select ssn, name from professors where ssn not in (

select ssn from teaches where cid in(

select cid from courses where name ='cs'

)

) and deptid in (

select deptid from courses where name='cs'

);

Q5) List number of courses offered by each department.

Answer)

select count(cid) from courses group by deptid;

Q6) List department that offers more than 10 courses.

Answer)

select deptid from courses where GROUP BY deptid having COUNT(cid)>10;

Q7) List of students whose professor name starts with M.

Answer)

select sid, name from students where sid in (

select sid from enrollment where cid in (

select cid from teaches where ssn in(

select ssn from professors where name like 'M%'

)

)

);

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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