数据库查询
必做任务
对表中添加数据，该表格式延续homework1
use h1_19377215
sp_help student_19377215
delete from Htable1_19377215
/*很烦的是，一开始插入数据的外键匹配错误了，导致我要删表重新搞*/
insert into Htable1_19377215(detnumber,dettext)values
(2010,'CS'),
(2008,'MIS'),
(2001,'IS')
select * from Htable1_19377215
/*先插入这个表的数据貌似不行，因为有外键约束，先对外键的表做一下数据插入试试。因此插入的数据应当是外键表中存在的*/
insert into student_19377215(sno,sname,sdept,gender) values('19377210','七七','IS','女'),
('19377211','赵信','IS','男'),
('19377212','纳尼','MIS','女'),
('19377213','无敌','CS','男'),
('19377214','罗辑','IS','女'),
('19377215','大锤','IS','男'),
('19377216','木木','CS','女'),
('19377217','jet','IS','男'),
('19377218','妙语和','IS','女'),
('19377219','咕咕','IS','男'),
('19377220','只因','CS','男')
insert into student_19377215(sno,sname,sdept,gender) values('20377210','七八','IS','女'),
('20377211','赵析','IS','男'),
('20377212','纳你','MIS','女'),
('20377213','武帝','CS','男'),
('20377214','罗技','IS','女'),
('20377215','大吹','IS','男'),
('20377216','沐沐','CS','女'),
('20377217','嘉然','IS','男'),
('20377218','米奇','IS','女'),
('20377219','喵喵','IS','男'),
('20377220','呜呜','CS','男')
select * from student_19377215
/*sc表有外键约束，先把course表给设置一下*/
select *from course_19377215
/*长度为10的字符串长度居然被截断了，看样子sql里一个中文=2char*/
/*更改表的属性*/
alter table course_19377215 alter column cname varchar(50)
alter table course_19377215 alter column ccredit int
delete from course_19377215
insert into course_19377215(cno,cname,ccredit)values
('1901','现代程序设计',2),
('1902','数据库',2),
('1903','计算机网络基础',2),
('1904','计量经济学',3),
('1905','数据结构',2),
('1906','计算机组成原理',2)
insert into course_19377215(cno,cname,ccredit)values
('2001','现代程序设计',2),
('2002','数据库',2),
('2003','计算机网络基础',2),
('2004','计量经济学',3),
('2005','数据结构',2),
('2006','计算机组成原理',2),
('2007','拉丁舞',1)
insert into course_19377215(cno,cname,ccredit)values
('1907','拉丁舞',1)
/*发现对于选修，必修有要求，需要生成一个新的字段，并且插入新的数据*/
alter table course_19377215 add ctype varchar(10) check(ctype='必修' or ctype='选修')
/*如果要在已有数据中的字段中插入数据，貌似只能用update,insert只能一次插入一行*/
update course_19377215 set ctype=case when cno='1901' then '必修'
when cno='1902' then '必修'
when cno='1903' then '必修'
when cno='1904' then '必修'
when cno='1905' then '选修'
when cno='1906' then '选修'
when cno='1907' then '选修'
when cno='2001' then '必修'
when cno='2002' then '必修'
when cno='2003' then '必修'
when cno='2004' then '必修'
when cno='2005' then '选修'
when cno='2006' then '选修'
when cno='2007' then '选修'
else ctype end;
select * from course_19377215
insert into sc_19377215 values
('19377210','1901',95),
('19377210','1902',93),
('19377210','1903',92),
('19377210','1904',98),
('19377210','1905',98),
('19377210','1906',93),
('19377211','1901',85),
('19377211','1902',73),
('19377211','1903',92),
('19377211','1904',76),
('19377211','1905',66),
('19377212','1901',88),
('19377212','1902',98),
('19377212','1903',91),
('19377212','1904',56),
('19377212','1906',88),
('19377213','1901',90),
('19377213','1902',93),
('19377213','1903',95),
('19377213','1904',92),
('19377213','1905',91),
('19377214','1901',82),
('19377214','1902',88),
('19377214','1903',86),
('19377214','1904',80),
('19377214','1906',99),
('19377215','1901',85),
('19377215','1902',73),
('19377215','1903',96),
('19377215','1904',90),
('19377215','1905',89),
('19377216','1901',58),
('19377216','1902',73),
('19377216','1903',91),
('19377216','1904',80),
('19377216','1906',66),
('19377217','1901',90),
('19377217','1902',92),
('19377217','1903',92),
('19377217','1904',91),
('19377217','1905',88),
('19377218','1901',88),
('19377218','1902',90),
('19377218','1903',94),
('19377218','1904',94),
('19377218','1905',89),
('19377219','1901',98),
('19377219','1902',99),
('19377219','1903',100),
('19377219','1904',97),
('19377219','1906',98),
('19377220','1901',98),
('19377220','1902',68),
('19377220','1903',65),
('19377220','1904',76),
('19377220','1905',89)
insert into sc_19377215 values
('20377210','2001',95),
('20377210','2002',93),
('20377210','2003',92),
('20377210','2004',98),
('20377210','2005',98),
('20377210','2006',93),
('20377211','2001',85),
('20377211','2002',73),
('20377211','2003',92),
('20377211','2004',76),
('20377211','2005',66),
('20377212','2001',88),
('20377212','2002',98),
('20377212','2003',91),
('20377212','2004',56),
('20377212','2006',88),
('20377213','2001',90),
('20377213','2002',93),
('20377213','2003',95),
('20377213','2004',92),
('20377213','2005',91),
('20377214','2001',82),
('20377214','2002',88),
('20377214','2003',86),
('20377214','2004',80),
('20377214','2006',99),
('20377215','2001',85),
('20377215','2002',73),
('20377215','2003',96),
('20377215','2004',90),
('20377215','2005',89),
('20377216','2001',58),
('20377216','2002',73),
('20377216','2003',91),
('20377216','2004',80),
('20377216','2006',66),
('20377217','2001',90),
('20377217','2002',92),
('20377217','2003',92),
('20377217','2004',91),
('20377217','2005',88),
('20377218','2001',88),
('20377218','2002',90),
('20377218','2003',94),
('20377218','2004',94),
('20377218','2005',89),
('20377219','2001',98),
('20377219','2002',99),
('20377219','2003',100),
('20377219','2004',97),
('20377219','2006',98),
('20377220','2001',98),
('20377220','2002',68),
('20377220','2003',65),
('20377220','2004',76),
('20377220','2005',89)
select * from sc_19377215
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7oRLopzk-1666790099219)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017211838008.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-05qC9VBR-1666790099220)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017211820718.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ieJVjPTz-1666790099221)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017211759053.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rv0HJJ9s-1666790099222)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017211737697.png)]
统计每门课报的人数和平均成绩
考虑到有的课没人选的情况，采用full join
/*左连接表格，使得课程代码表名称跟cno一一对应上*/
/*select后要么跟聚合函数，要么在group by中*/
select count(sc_19377215.sno) as '选课人数',course_19377215.cname,AVG(sc_19377215.grade) as '平均成绩',course_19377215.cno as '选课编号'
from sc_19377215 full join course_19377215 on sc_19377215.cno=course_19377215.cno
group by sc_19377215.cno,course_19377215.cname,course_19377215.cno
select count(sc_19377215.sno) from sc_19377215 ,course_19377215 where sc_19377215.cno=course_19377215.cno group by sc_19377215.cno
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8a29lR8P-1666790099223)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017211621286.png)]
列出有不及格选课记录的学生姓名、学号、专业
select distinct sname,sname,sdept from student_19377215,sc_19377215 where student_19377215.sno=sc_19377215.sno and grade<60
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GVXxfYPS-1666790099223)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017212557383.png)]
将2019年入学，必修课成绩依然有不及格状态的同学，将其学籍状态标记为暂缓毕业
先加入学籍状态
alter table student_19377215 add 学籍状态 varchar(20)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4wvXuoDz-1666790099224)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017213002983.png)]
update student_19377215 set 学籍状态='暂缓毕业'
where student_19377215.sno in(select student_19377215.sno from sc_19377215 left join student_19377215 on sc_19377215.sno=student_19377215.sno
where grade<60 and sc_19377215.cno in (select cno from course_19377215 where course_19377215.ctype='必修') and student_19377215.sno like'19%')
select * from student_19377215 where 学籍状态='暂缓毕业'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wWyBr5fD-1666790099225)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017214925745.png)]
定义视图 提供每门课的学生平均分
create view S_G(cno,cname,grade_ave) as
select course_19377215.cno,cname,avg(grade) as '平均成绩'
from course_19377215,sc_19377215
where course_19377215.cno=sc_19377215.cno
group by cname,sc_19377215.cno,course_19377215.cno
select * from S_G
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C9l7Q2T9-1666790099226)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017220633686.png)]
删除从来没有人选修的课程信息
delete from course_19377215 where course_19377215.cno not in(select cno from sc_19377215)
select * from course_19377215
拉丁舞从来没人选，于是被删掉了
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Y1w23qIv-1666790099227)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017220949975.png)]
选做任务
加几个字段（必做部分已经完成了类似操作）
alter table course_19377215 add ctime int,c_teacher varchar(10)
alter table student_19377215 add GPA float
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wyqlIv8x-1666790099228)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017221418043.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5CjxvVwQ-1666790099229)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017221522609.png)]
将gpa加入到学生表中
select sc_19377215.sno,sname,convert(decimal(10,2),(sum(ccredit*grade/25.0)/sum(ccredit))) as 'GPA' from sc_19377215,student_19377215,course_19377215 where sc_19377215.sno=student_19377215.sno
and sc_19377215.cno=course_19377215.cno
group by sc_19377215.sno,sname
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RA1ej0w4-1666790099229)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017223707085.png)]
输出前五个gpa的学生
只能创建新表将结果传入
create table stu_gpa
(sno char(10) not null primary key,
sname varchar(10) not null,
sdept char(10) not null,
gpa float,
)
insert into stu_gpa(sno,sname,sdept,gpa)
select sc_19377215.sno,sname,sdept,convert(decimal(10,2),(sum(ccredit*grade/25.0)/sum(ccredit))) from sc_19377215,student_19377215,course_19377215 where sc_19377215.sno=student_19377215.sno
and sc_19377215.cno=course_19377215.cno
group by sc_19377215.sno,sname,sdept
select top 5 sno,sname,sdept,gpa from stu_gpa
where sdept='IS'
order by gpa desc --降序排序
t,convert(decimal(10,2),(sum(ccredit*grade/25.0)/sum(ccredit))) from sc_19377215,student_19377215,course_19377215 where sc_19377215.sno=student_19377215.sno
and sc_19377215.cno=course_19377215.cno
group by sc_19377215.sno,sname,sdept
select top 5 sno,sname,sdept,gpa from stu_gpa
where sdept=‘IS’
order by gpa desc --降序排序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ff9KTlEG-1666790099230)(C:\Users\kerrla\AppData\Roaming\Typora\typora-user-images\image-20221017231541068.png)]
扫一扫
2322
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
