SQL Server 练习题 3

**SQL高级查询的练习题

Student(Sno,Sname,Sage,Ssex) 学生表
Course(Cno,Cname,Tno) 课程表
SC(Sno,Cno,score) 成绩表
Teacher(Tno,Tname) 教师表**

if exists(select * from sysobjects where name='student')
drop table student;
if exists(select * from sysobjects where name='course')
drop table course;
if exists(select * from sysobjects where name='sc')
drop table sc;
if exists(select * from sysobjects where name='teacher')
drop table teacher;

create table student --学生表
(
	Sno int primary key identity(1,1),--学生学号
	Sname varchar(20),--学生姓名
	Sage int,--学生年龄
	Ssex char(2) check(Ssex = '男' or Ssex = '女') --学生性别
)
--往学生表中插入记录
insert into student
select '张三',20,'男' union
select '李四',21,'女' union
select '王五',22,'男' union
select '赵六',23,'女' union
select '孙七',24,'男' union
select '钱八',21,'女' union
select '杨九',22,'男' union
select '刘十',23,'女' union
select '小二',24,'男'

create table Teacher --教师表
( 
	Tno int primary key identity(1,1),--教师教号 
	Tname varchar(10)
)
--往老师表中插入记录
insert into teacher values('李浩');
insert into teacher values('叶平');
insert into teacher values('孙晓平');
insert into teacher values('刘立海');
insert into teacher values('高桥梁');
insert into teacher values('杨雪丽');

create table Course --课程表 
(
   	Cno int primary key identity(1,1),--课程id
	Cname varchar(20),--课程名称 
	Tno int foreign key references Teacher(tno) -- 外键教师表	
) 
--往课程表中插入记录
select * from teacher;
insert into course values('SQL SERVER 数据库',1);
insert into course values('ORACLE',2);
insert into course values('C语言',3);
insert into course values('C++',4);
insert into course values('数据结构',5);
insert into course values('软件工程',6);

create table SC --成绩表
(
	Sno int foreign key references student(Sno),
	Cno int foreign key references Course(Cno), 
	score float
) 
--往成绩表中插入数据
select * from student;
select * from course;
select * from sc where 1=0;
insert into sc values(1,1,80.0);
insert into sc values(1,2,38.5);
insert into sc values(1,3,58.0);
insert into sc values(1,4,78.5);
insert into sc values(1,5,85.0);
insert into sc values(1,6,89.0);
insert into sc values(2,1,50.0);
insert into sc values(2,2,92.0);
insert into sc values(2,3,81.5);
insert into sc values(2,4,81.5);
insert into sc values(2,5,73.0);
insert into sc values(2,6,89.5);
insert into sc values(3,1,45.0);
insert into sc values(3,2,67.5);
insert into sc values(3,3,89.5);
insert into sc values(3,4,69.5);
insert into sc values(3,5,75.0);
insert into sc values(3,6,87.5);
insert into sc values(4,1,78.0);
insert into sc values(4,2,97.5);
insert into sc values(4,3,76.5);
insert into sc values(4,4,78.5);
insert into sc values(4,5,85.0);
insert into sc values(4,6,94.5);
insert into sc values(5,1,78.0);
insert into sc values(5,2,89.5);
insert into sc values(5,3,86.5);
insert into sc values(5,4,88.5);
insert into sc values(5,5,90.0);
insert into sc values(5,6,87.5);
insert into sc values(6,1,98.0);
insert into sc values(6,2,69.5);
insert into sc values(6,3,81.5);
insert into sc values(6,4,80.5);
insert into sc values(6,5,92.0);
insert into sc values(6,6,83.5);

表创建完成完成12道查询练习

1、查询“1”课程比“2”课程成绩高的所有学生的学号；

select * from sc sc1,sc sc2
where sc1.Sno=sc2.Sno 
and sc1.Cno=1 
and sc2.Cno=2 
and sc1.score>sc2.score

2、查询平均成绩大于60分的同学的学号和平均成绩；

select Sno,AVG(score)
from sc
group by sno
having AVG(score)>60

3、查询所有同学的学号、姓名、选课数、总成绩；

select s.sname,t.课程数,t.总成绩
from student s,
(select Sno,COUNT(cno) 课程数 ,SUM(score) 总成绩
from sc
group by Sno) t
where s.sno=t.Sno

4、查询姓“李”的老师的个数；

select COUNT(*) from Teacher where Tname like '李%'

5、查询没学过“叶平”老师课的同学的学号、姓名；

select Sno,Sname
from student
where Sno not in (select sno
from sc
where cno  in (select cno
from Course
where Tno=(select tno
from Teacher 
where Tname='叶平')))

6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

select *
from sc sc1,sc sc2
where sc1.Sno=sc2.Sno and sc1.Cno=1 
and sc2.Cno=2