#练习：
#有三个表students,courses,informations记录学生，课程及对应的信息。
#1.查询课程名称为“Java”的学员学号和姓名
#2.查询课程编号为‘88’的学员姓名和所属单位
#3.查询不选修课程编号为‘115’的学员姓名和所属单位
#4.查询选修全部课程的学员姓名和所属单位
#5.查询选修了课程的学员人数
#6.查询选修了超过3门课程的学员学号和所属单位
#1.查询课程名称为“Java”的学员学号和姓名
#方式一：
SELECt stu.student_id,stu.student_name FROM students stu
LEFT JOIN informations inf ON stu.student_id = inf.student_id
LEFT JOIN courses cou ON cou.course_id = inf.course_id WHERe cou.course_name = "Java";
#方式二：
SELECt student_id,student_name FROM students
WHERe student_id IN (
SELECt student_id FROM informations
WHERe course_id = (
SELECt course_id FROM courses
WHERe course_name = 'Java'
)
);
#2.查询课程编号为‘88’的学员姓名和所属单位
#方式一：
SELECt stu.student_name,stu.student_company FROM students stu
LEFT JOIN informations inf ON stu.student_id = inf.student_id
LEFT JOIN courses cou ON cou.course_id = inf.course_id WHERe cou.course_id = 88;
#方式二：
SELECt student_name,student_company FROM students
WHERe student_id IN(
SELECt student_id FROM informations
WHERe course_id = 88
);
#3.查询不选修课程编号为‘115’的学员姓名和所属单位
SELECt student_name,student_company FROM students
WHERe student_id NOT IN(
SELECt student_id FROM informations
WHERe course_id = 115
);
#4.查询选修全部课程的学员姓名和所属单位
SELECt student_name,student_company FROM students
WHERe student_id IN (
SELECt student_id FROM informations
GROUP BY student_id
HAVINg COUNT(*) = (
SELECt COUNT(course_id)
FROM courses
)
)
#5.查询选修了课程的学员人数
SELECt COUNT(student_id) FROM (
SELECt student_id,COUNT(*) FROM informations
GROUP BY student_id
)tb_stucourse
#6.查询选修了超过3门课程的学员学号和所属单位
SELECt student_name,student_company FROM students
WHERe student_id IN (
SELECt student_id FROM informations
GROUP BY student_id
HAVINg COUNT(*) > 3
)
