Translate the following from english to SQL (Using mysql) 1. What are the names

Question

Translate the following from english to SQL (Using mysql)

1. What are the names and total number of sections each instructor has taught? (show 0 for instructors who have not taught any sections)

2. What are the names of all students that have passed (a C or better) the course Intro to Computer Science?

UNIVERSITY schema:

Classroom(Building, Room_Number, Capacity);
Department(Dept_Name, Building, Budget);
Course(Course_Id, Title, Dept_Name, Credits);
Instructor(Id, Name, Dept_Name, Salary);
Section(Course_Id, Sec_Id, Semester, Year, Building, Room_Number, Time_Slot_Id);
Teaches(Id, Course_Id, Sec_Id, Semester, Year);
Student(Id, Name, Dept_Name, Tot_Cred);
Takes(Id, Course_Id, Sec_Id, Semester, Year, Grade);
Advisor(S_Id, I_Id);
Time_Slot(Time_Slot_Id, Day, Start_Time, End_Time);
Prereq(Course_Id, Prereq_Id);

Explanation / Answer

1. SQL>

SELECT I.Name, COUNT(T.Sec_Id)
FROM Instructor I, Department D, Course C, Teaches T, Section S
WHERE I.Dept_Name = D.Dept_Name AND D.Dept_Name = C.Dept_Name AND C.Course_Id = T.Course_Id AND T.Sec_Id = S.Sec_Id;

2. SQL>

SELECT I.Name, COUNT(T.Sec_Id)
FROM Instructor I, Department D, Course C, Teaches T, Section S
WHERE I.Dept_Name = D.Dept_Name AND D.Dept_Name = C.Dept_Name AND C.Course_Id = T.Course_Id AND T.Sec_Id = S.Sec_Id;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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