挑了几道比较难的复合条件及连接查询题，记录一下
⑴ 查询选修了课程代码是045239且成绩80分(含80分)以上的学生的学号、姓名、成绩。
SELECT student.sno, student.sname, student.sex, course.course_name, select_cource.score FROM student, select_cource, course WHERE select_cource.course_no = 045239 AND course.course_no = select_cource.course_no AND select_cource.score > 80 AND select_cource.sno = student.sno
⑷ 查询选修了课程代码是045239且成绩在80分(含80分)以上的学生的学号、姓名、性别、课程名、年龄、成绩。
SELECT student.sno, student.sname, student.sex, course.course_name, CEIL( DATEDIFF( NOW(), student.birthday )/ 365 ) age, select_cource.score FROM student, select_cource, course WHERE select_cource.course_no = 045239 AND course.course_no = select_cource.course_no AND select_cource.score >= 80 AND select_cource.sno = student.sno
⑸ 查询选修了高等数学所有同学的学号、姓名、性别、院系名称、专业、出生日期和年级，并在查询结果中将列名显示为：学号、姓名、性别、系名、专业、出生日期、年级。
SELECT student.sno, student.sname, student.sex, student.district, major.major_name, student.class, student.birthday, student.school_year FROM student, major, course, select_cource WHERE student.major_no = major.major_no AND course.course_no = select_cource.course_no AND student.sno = select_cource.sno AND course.course_name LIKE '高等数学%'
⑺ 查询至少选修了两门以上课程的学生的学号、姓名和所在班级。
SELECT student.sno, student.sname, student.class FROM student WHERE student.sno IN ( SELECT sno FROM select_cource GROUP BY select_cource.course_no HAVING SUM( select_cource.sno )> 2)
⑻ 查询所有课程都及格的学生的学号，姓名，最高成绩，最低成绩，平均成绩。
SELECT student.sno, student.sname, MAX( select_cource.score ) max, MIN( select_cource.score ) min, avg( select_cource.score ) average FROM student, select_cource WHERE student.sno = select_cource.sno AND select_cource.score >= 60
⑴ 定义一个查询学生的学号、姓名、系名、专业、学年、学期、最高成绩、最低成绩、平均成绩的视图student_score。
CREATE VIEW student_score AS SELECT student.sno, student.sname, major.department, major.major_name, course.school_year, course.semester, MAX( select_cource.score ) max, MIN( select_cource.score ) min, AVG( select_cource.score ) averge FROM student, major, select_cource, course WHERE student.major_no = major.major_no AND student.sno = select_cource.sno AND course.course_no = select_cource.course_no GROUP BY student.sno, major.department, major.major_name, student.school_year, course.semester
定义一个查询选修课程的课程号、课程名称、学年、学期、学分、最高成绩、最低成绩、平均成绩的视图course_score。
