Shulou(Shulou.com)06/01 Report--

Student form student (id,name)

Teacher's watch teacher (id,name)

Course schedule lesson (id,name)

Teacher and Curriculum Association Table (id,teacher_id,lesson_id)

Student and course Association Table (id,student_id,lesson_id)

Inquire about Mr. Wang's course.

SELECT t.name AS 'teacher', l.name AS 'course' FROM teacher t

LEFT JOIN teacher_lesson teal ON t.id = teal.teacher_id

LEFT JOIN lesson l ON l.id = teal.lesson_id WHERE t.name = 'Mr. Wang'

Inquire about the students who are in Mr. Wang or Mr. Zhang's class.

SELECT s.name AS 'student', l.name AS 'course', t.name AS 'teacher' FROM student s

LEFT JOIN student_lesson stul ON s.id = stul.student_id

LEFT JOIN lesson l ON l.id = stul.lesson_id

LEFT JOIN teacher_lesson teal ON teal.lesson_id = l.id

LEFT JOIN teacher t ON t.id = teal.teacher_id WHERE t.name = 'Mr. Wang' OR t.name = 'Mr. Zhang'

Inquire about the students who are taught by both Mr. Wang and Mr. Zhang.

SELECT * FROM student s WHERE EXISTS (

SELECT 1 FROM teacher_lesson tl JOIN teacher t ON tl.teacher_id = t.id

JOIN student_lesson sl ON sl.lesson_id = tl.lesson_id WHERE sl.student_id = s.id AND t.name = 'Miss Wang'

) AND EXISTS (

SELECT 1 FROM teacher_lesson tl JOIN teacher t ON tl.teacher_id = t.id

JOIN student_lesson sl ON sl.lesson_id = tl.lesson_id WHERE sl.student_id = s.id AND t.name = 'Mr. Zhang'

);

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.