上课记录

create database sc
go

use sc
go

create table student
(
 sno char(9) primary key,
 sname char(20) unique,
 ssex char(2),
 sage smallint,
 sdept char(20)
);
create table course
(
 CNO CHAR(4) primary key,
 cname char(40),
 cpno char(4),
 ccredit smallint,
 foreign key(cpno) references course(cno)
);
create table sc
(
 sno char(9),
 cno char(4),
 grade smallint,
 primary key(sno,cno),
 foreign key (sno) references student(sno),
 foreign key (cno) references course(cno)
 
);
insert into student
values ('200215121','李勇','男',20,'cs')
insert into student
values ('200215122','刘晨','女',19,'cs')
insert into student
values ('200215123','王敏','女',18,'ma')
insert into student
values ('200215125','张立','男',19,'is')
insert into course
values('2','数学',null,2)
insert into course
values('6','数据处理',null,2)
insert into course
values('4','操作系统','6',3)
insert into course
values('7','PASCAL语言','6',4)
insert into course
values('5','数据结构','7',4)
insert into course
values('1','数据库','5',4)
insert into course
values('3','信息系统','1',4)
insert into sc
values('200215121','1',92)
insert into sc
values ('200215121','2',85)
insert into sc
values ('200215121','3',88)
insert into sc
values ('200215122','2',90)
insert into sc
values ('200215122','3',80)
select * from student
select * from course
select * from sc
select *
from student
order by sage desc
select *
from sc
order by cno/*默认是升序(asc)，可省略*/,grade desc
select sname
/*查询学生中男生人数*/
select count(sno)
from student
where ssex='男'
/*查询学生中各年龄段人数*/
select sage,count(sno)
from student
group by sage
/*查询3号课程的成绩总分*/
select sum(grade)
from sc
where cno='3'
/*查询学生的年龄和*/
select sum(sage)
from student
/*查询学生200215122所选修的课程的平均分*/
select avg(grade)
from sc
where sno='200215122'
/*查询3号课程的平均分*/
select avg(grade)
from sc
where cno='3'
/*求3号课程的最高分*/
select max(grade)
from sc
where cno='3'
/*查询3号课程的平均分和最高分*/
select avg(grade) 平均分, max(grade) 最高分
from sc
where cno='3'

 

注意
1.
select count(sno+cno)
from sc
2.
sun(),avg()这两个是对数值型的属性进行统计操作
3.
max(),min()选择最大的或者最小的（数值型的或者字符型的都可以）。
 

三.分组 group by 字句的应用
/*1.查询课程号及相应的选修人数*/
select cno,count(*)
from sc
group by cno

/*1.查询男生和女生人数*/
select ssex,count(*)
from student
group by ssex