一、建立数据库,插入数据,建立学生表、课程表和选课表
create table 学生表
(
学号 varchar(10) primary key,
姓名 nvarchar(5),
年龄 smallint,
性别 nvarchar(5),
专业 nvarchar(10)
)
insert into 学生表 values('202415121','韩梅梅','20','女','电子商务');
insert into 学生表 values('202415122','李磊','19','男','电子商务');
insert into 学生表 values('202415123','林涛','18','男','软件工程');
insert into 学生表 values('202415124','吉姆','21','男','电子电工');
insert into 学生表 values('202415125','杰瑞','22','女','电子商务');
insert into 学生表 values('202415126','汤姆','23','男','电子商务');
create table 课程表
(
课程号 varchar(10) primary key,
课程名称 nvarchar(15),
学分 smallint
)
insert into 课程表 values('1001','数据库',4);
insert into 课程表 values('1002','数学',2);
insert into 课程表 values('1003','信息技术',3);
insert into 课程表 values('1004','操作系统',4);
create table 选课表
(
学号 varchar(10),
课程号 varchar(10),
分数 smallint,
primary key(学号,课程号)
);
alter table 选课表 add constraint fk_选课表_学号 foreign key (学号) references 学生表(学号);
alter table 选课表 add constraint fk_选课表_课程号 foreign key (课程号) references 课程表(课程号);
insert into 选课表 values ('202415121','1001',92);
insert into 选课表 values ('202415121','1002',85);
insert into 选课表 values ('202415122','1003',88);
insert into 选课表 values ('202415123','1002',null);
insert into 选课表 values ('202415123','1003',80);
insert into 选课表 values ('202415124','1004',70);
insert into 选课表 values ('202415124','1001',null);
二、练习查询
1.统计每个专业的学生人数
2.统计每门课程的选课人数和考试的最高分
首先在选课表中查找,按课程号统计选课人数,效果如下:
统计所有人数和最高分(用max指令执行查询最高分)
3.查询选修了“1002”课程号的学生的姓名和专业
因为我们建立的学生表与课程表没有相关约束,无法建立联系,但我们可以通用链接选课表来联系学生表和课程表,所以利用 “inner join" 链接选课表,效果如下:
也可以直接罗列所有,效果如下:
4.查询85分以上的学生的姓名,课程号和成绩
5.查询平均成绩大于80分的同学的学号和平均成绩
平均成绩利用 avg() 指令,此外,在聚合函数中条件不能用where 会报错,利用 having+条件
即可
6.查询被学生选修的课程的课程号和课程名称
查询被选修的课程号:
利用distinct(课程号)将重复的课程号和整为一个
7.查询课程名称为"数据库" ,并且分数低于95分的学生姓名,分数
8.查询每门课程的平均成绩,按照平均成绩升序排列
接下来进行排序