Given the following relational schemas (SQL) 1. List the name of every teacher (

Question

Given the following relational schemas (SQL)

1. List the name of every teacher (distinct names) who teaches in RoomN '34' in Winter2011.

2. List CourseN, CourseName, and TeacherName of every course meets on Monday PM.

3. List the name of every teacher who taught at least one course in RroomN '723'.

4. List the CourseN, Quarter, RoomN and DayTime of every course taught by 'Karen Reed' in the Spring 2005.

5. List the CourseN and TeacherName of every course taken by the student 'Ron Smith' or by the student 'David Weidman.'

6. List the CourseN and Quarter for every course taught by 'Karen Reed' and met or meets in RoomN '713'.

7. List the name of every teacher who has taught the same course at least two times.

8. List the name of every teacher (distinct names) who has taught at least two different courses in the same or different quarters.

9. List the CourseN, CourseName, and Quarter which meets or met at least two times a week.

10. List the CourseN and CourseName of every course with number of units > 4

11. List every course number and student's name who has taken the course at least twice.

12. Use '*' to list the courseN, CourseName, Nunit, Quarter, TeacherName of every course sorted by CourseN ascending, CourseName descending.

13. List the CourseN and Quarter of every course taught by two different instructors in the same quarter ordered by the CourseN in descending order.

Given the following relation schemes: Teacher-CourseN. Quarter,TeacherNamel Course-CourseNCourseName, Nunit) LocationNTime= [CourseN,Ouarter. DayTime, RoomNI/ Examples of Day Time: M2:00AM, W4:50PM, and T8:00PM. Note that DayTime is represented as a string. Student-studentName, CourseN. Quarter]

Explanation / Answer

1. List the name of every teacher (distinct names) who teaches in RoomN '34' in Winter2011.

select unique TeacherName from Teacher T left join LocationNTime L
on T.CourseN=L.CouseN where L.RoomN=34 and T.Quarter='Winter2011';

2)List CourseN, CourseName, and TeacherName of every course meets on Monday PM.

select C.CourseN,C.CourseName,T.TeacherName from Course C left join LocationNTime L
on L.CourseN=C.CourseN left join Teacher T on L.CourseN=T.CourseN
where L.DayTime='Monday' and L.DayTime>12;

3)List the name of every teacher who taught at least one course in RroomN '723'.

select T.TeacherName from Teacher T left join LocationNTime L
on.T.CourseN=L.CourseN where L.RoomN=723;

4)List the CourseN, Quarter, RoomN and DayTime of every course taught by 'Karen Reed' in the Spring 2005.

select T.CourseN,T.Quarter,L.RoomN,L.DayTime from Teacher left join LocationNTime L
on T.CourseN=L.CourseN where T.TeacherName='Karen Reed' and T.Quarter='Spring2005';

5. List the CourseN and TeacherName of every course taken by the student 'Ron Smith' or by the student 'David Weidman.'

select C.CourseN,T.TeacherName from Teacher T left join Course C
on T.CourseN=C.CourseN left join Student S on C.CourseN=S.CourseN
where S.studentName='Ron Smith' or S.studentName='David Weidman';

6. List the CourseN and Quarter for every course taught by 'Karen Reed' and met or meets in RoomN '713'.

select C.CourseN,T.Quarter from Course C left join Teacher T
on T.CourseN=C.CourseN left join LocationNTime L on L.CourseN=T.CourseN
where T.TeacherName='Karen Reed' or L.RoomN=713;

7. List the name of every teacher who has taught the same course at least two times.
  
select TeacherName, count(CourseN) as Same-Course-Count from Teacher group by TeacherName having count(CourseN)>=2;

8. List the name of every teacher (distinct names) who has taught at least two different courses in the same or different quarters.

select distinct TeacherName,count(T.CourseN) from Teacher T left join Course C
on T.CourseN=C.CourseN where group by TeacherName having count(T.CourseN)>=2;

9. List the CourseN, CourseName, and Quarter which meets or met at least two times a week.

select C.CourseN,C.CourseName, L.Quarter from Course C left join LocationNTime L
on C.CourseN=L.CourseN where Count(C.Course)>=2;

10. List the CourseN and CourseName of every course with number of units > 4
select CourseN,CourseName from Course where Nunit>4;

11. List every course number and student's name who has taken the course at least twice.
select C.CourseN,S.studentName from Course C left join Student S
on C.CourseN=S.CourseN groub by S.studentName having count(C.CouseN)>=2

12. Use '*' to list the courseN, CourseName, Nunit, Quarter, TeacherName of every course sorted by CourseN ascending, CourseName descending.
select C.*,T.* from Course C left join Teacher T on C.CourseN=T.CourseN order by CourseN asc, order by CourseName desc;

13. List the CourseN and Quarter of every course taught by two different instructors in the same quarter ordered by the CourseN in descending order.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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