--查询所有同学的成绩信息,要求输出学生姓名和成绩
use students_new
go
select s_name,score from t_score,t_student
(文件名) (文件)
where t_student.s_number=t_score.s_number
(文件名.文件)
(两张表的连接条件)
--查询所有课程的成绩信息,要求输出课程名称和成绩
select c_name,score from t_course,t_score
where t_course.c_number=t_score.c_number
--查询“高等数学”课程的平均分
select avg(score) as 平均分 from t_course,t_score
where c_name='高等数学' and t_course.c_number=t_score.c_number
--查询所有成绩信息,要求输出学生姓名、课程名称成绩
select s_name,c_name,score from t_course,t_score,t_student
where t_course.c_number=t_score.c_number and t_score.s_number=t_student.s_number
--取别名法
select s.s_number,c.c_name,s.score from t_course as c,t_score as s,t_student as ts
where c.c_number=s.c_number and s.s_number=ts.s_number
--查询"张小航"同学"高等数学"课程的成绩
select s.score from t_score as s,t_student as stu,t_course as c where s_name='张小航'and c_name='高等数学'
and c.c_number=s.c_number and s.s_number=stu.s_number
--查询每位同学的最高分,要求输出学生姓名和最高分
select MAX(score),s_name from t_score as s,t_student as stu
where s.s_number=stu.s_number group by s_name
--查询选修了"高等数学"课程的学生姓名和性别
select s_name,sex from t_score as s,t_student as stu,t_course as c where c_name='高等数学'
and c.c_number=s.c_number and s.s_number=stu.s_number
--查询"高等数学"课程不及格的学生信息,并按成绩升序排列输出
select score,s_number,s.sex,s.birthday,s.polity from t_score as s,t_course as c,s_student as stu
where c_name='高等数学'and score<='60' and c.c_number=s.c_number and s.s_number=stu.s_number order by score