[大数据]sql sever的连接查询操作

一、基本操作语句

1.查询指定学号的学生的所有课程的课程号，成绩

select score.cno,score.sscore 
from score
left join student on student.sno=score.sno
where student.sname='lily';

2.查出所有学生的学号，姓名，选课名称及成绩

select student.sno,student.sname,course.cname,score.sscore
from student,score,course
where student.sno=score.sno and score.cno=course.cno;

3.查出所有有“大学英语”成绩的学生姓名

select student.sname
from student
left join score on student.sno=score.sno
left join course on course.cno=score.cno
where course.cname='大学英语';

4.查询学号为xxx的学生的所有课程的课程号，课程名，学分和成绩

select score.cno,course.cname,course.credit,score.sscore
from score
left join course on score.cno=course.cno
where sno='011';

5.查出所有的“高级语言程序设计”课程的不及格学生的学号

select score.sno
from score
left join course on course.cno=score.cno
where course.cname='高级语言程序设计'
and score.sscore<60;

6.查询所有比“***”同学年龄小的学生信息

select * from student 
where (DateName(year,GetDate())-year(sbirthday))<
(select DateName(year,GetDate())-year(sbirthday)
from student 
where sno='004');

7.统计“**”同学的平均成绩

select avg(sscore) 
from student
left join score on score.sno=student.sno
where sname='huyi';

8.查出所有平均成绩比“**”同学的高的学生信息

select avg(sscore)
from student
left join score on score.sno=student.sno
group by student.sno
having avg(sscore)>
(
select avg(sscore) 
from student
left join score on score.sno=student.sno
where sname='huyi'
);

9.统计“数据库”课程的平均成绩

select avg(sscore)
from score
left join course on course.cno=score.cno
where course.cname='数据库';

10.统计所有“数据库”课程成绩比“数据库”课程的平均成绩高的所有学生名单

select student.sno,student.sname,student.classno,score.sscore
from student
left join score on student.sno=score.sno
left join course on course.cno=score.cno
where course.cname='数据库' 
and score.sscore>
(
	select avg(sscore)
	from score
	left join course on course.cno=score.cno
	where course.cname='数据库'
);

11.完成统计所有“数据库”课程成绩比“数据库”课程的平均成绩低的所有学生名单

select student.sno,student.sname,student.classno,score.sscore
from student
left join score on student.sno=score.sno
left join course on course.cno=score.cno
where course.cname='数据库' 
and score.sscore<=
(
	select avg(sscore)
	from score
	left join course on course.cno=score.cno
	where course.cname='数据库'
);

12.查出所有没有“大学英语”成绩的学生名单

select student.sno,student.sname,student.classno 
from student 
where student.sno
not in 
(select student.sno
from student
left join score on student.sno=score.sno
left join course on course.cno=score.cno
where course.cname='大学英语');