如何查询所有同学的学号、姓名、选课数、总成绩
具体数据如下：
--首先创建表
create table student --学生表
(
sno int primary key identity(1,1),
sname varchar(20) not null,
sage int,
ssex varchar(2)
)
create table teacher --教师表
(
tno int primary key identity(1,1) not null,
Tname varchar(20) not null
)
create table course --课程表
(
cno int primary key identity(1,1),
cname varchar(20) unique not null,
tno int foreign key references teacher(tno)
)
create table sc --成绩表
(
Sno int foreign key references student(sno),
Cno int foreign key references course(cno),
score float
)
--往学生表中插入记录
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,'男'
--往老师表中插入记录
insert into teacher values('李浩')
insert into teacher values('叶平')
insert into teacher values('孙晓平')
insert into teacher values('刘立海')
insert into teacher values('高桥梁')
insert into teacher values('杨雪丽')
--往课程表中插入记录
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);
--往成绩表中插入数据
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)