没干过网络推广能干吗,东莞seo优化案例,山西省建设厅网站打不开,珠海商城What’s more
山东大学 2020级数据库系统 实验一 山东大学 2020级数据库系统 实验二 山东大学 2020级数据库系统 实验三 山东大学 2020级数据库系统 实验四 山东大学 2020级数据库系统 实验五 山东大学 2020级数据库系统 实验六 山东大学 2020级数据库系统 实验七 山东大学 20…What’s more
山东大学 2020级数据库系统 实验一 山东大学 2020级数据库系统 实验二 山东大学 2020级数据库系统 实验三 山东大学 2020级数据库系统 实验四 山东大学 2020级数据库系统 实验五 山东大学 2020级数据库系统 实验六 山东大学 2020级数据库系统 实验七 山东大学 2020级数据库系统 实验八、九
写在前面
做数据库实验一定要静得下心来才能发现其中的错误然后进行改正。同时如果发现 SQL 语句总是报错“一定是你错了只是不知道错在哪里”
其次SQL 语句中较为复杂的点博主都进行了注释希望大家一定要看懂思路后自己写一遍而不是盲目的 CtrlCCtrlV切记切记
实验五
实验五主要考察的内容如下 对于聚集函数 sum, max, count 的使用同时有无 group by 的意识
对于分部分查询的熟练程度可能会有其他方法但这部分我分块查询用的比较多~~
对于 union all 的了解及区分 union all 和 union 的区别
5-1 在学生表pub.student中统计名字姓名的第一位是姓氏其余为名字不考虑复姓的使用的频率将统计结果放入test5_01中表结构如下。 First_name varchar(4) frequency numeric(4) 国强 1034 红 1232 卫东 2323 ……………… 思路 使用 substr() 函数取出名字中的姓然后对所有姓进行 count 计数即可
create table test5_01 asselect distinct substr(name, 2, length(name)) first_name, count(*) frequencyfrom pub.studentgroup by substr(name, 2, length(name))5-2 在学生表pub.student中统计名字姓名的第一位是姓氏不作统计名字指姓名的第二个之后的汉字的每个字使用的频率将统计结果放入test5_02中特别提示需要区别union和union all的不同表结构如下。 letter varchar(2) frequency numeric(4) 锋 1034 红 1232 鹏 2323 ……………… 避坑指南 需要先选出姓名中的第二个字和第三个字然后再对他们进行统一的计数 思路 1. 选出名字中的第二个字记为集合 A然后使用 union all 来连接名字中第三个字的集合记为Bunion 和 union all 的区别在于一个去重一个不去重 2. 然后对 A ∪\cup∪ B 进行统一的计数即可
create table test5_02(letter varchar(2),frequency numeric(4))insert into test5_02
select letter, count(*) frequency
from ((select substr(name, 2, 1) letterfrom pub.studentwhere substr(name, 2, 1) is not null)union all(select substr(name, 3, 1) letterfrom pub.studentwhere substr(name, 3, 1) is not null))
group by letter5-3 创建学院班级学分达标情况统计表1test5_03依据pub.student pub.coursepub.student_course统计形成表中各项数据成绩60为及格计入学分总学分10算作达标院系为空值的数据不统计在下表中表结构院系名称dname、班级class、学分达标人数p_count1、学分未达标人数p_count2、总人数p_count。 Dname varchar(30) class varchar(10) P_count1 Int P_count2 int P_count int 计算机学院 2006 计算机学院 2007 软件学院 2006 ……………… 注意此题较难如果你现在静不下来那就下下道题吧如果能够静下来Let’s go 避坑指南 “成绩 60” 是指该学生该门课的成绩的最大值 60可能有考了多次的学生有的学生在 pub.student 中但是他没有选课因此又不在 pub.student_course 中。这部分学生应该算作学分未达标 p_count2。因此这部分这么难算为何不用 p_count - p_count1 呢我也给出计算 p_count2 的代码啦有兴趣可以看看~~有的学院班级的学生全部都学分不达标其实只有一个‘生命科学学院 2008’帮忙就帮到这儿啦具体人数还是自己算一算哈~~当时卡了我好久/(ㄒoㄒ)/~~ 思路分块查询 先从 pub.student 中选出 dname, class, p_count结果记为 t1再从 t2 表中找到 p_count1需要先找到每个学生每门课的最高分在通过最高分判定是否得到相应的学分再用 sum(credit) 来得到学分的和最后进行判定p_count2 使用 p_count - p_count1 即可
create table test5_03(dname varchar(30),class varchar(10),p_count1 int,p_count2 int,p_count int)-----------一张表一张表地看思路更清晰哦一共就 t1t2 两张表--------------
insert into test5_03
select t1.dname, t1.class, t2.p_count1, (t1.p_count - t2.p_count1) p_count2, t1.p_count
from (select distinct dname, class, count(*) p_countfrom pub.studentwhere dname is not nullgroup by dname, class) t1, --t1 表找到 dname, class, p_count(select distinct dname, class, count(*) p_count1from (select sid, dname, class, sum(credit) sum_credit --找到总学分from (select sid, cid, dname, class, max(score) max_score --找到成绩的最大值from pub.student_course natural join pub.studentgroup by sid, cid, dname, class) natural join pub.coursewhere max_score 60 --最大成绩 60 才计入学分and dname is not nullgroup by sid, dname, class)where sum_credit 10 --总学分 10 才选出来group by dname, class) t2, --t2 表找到 p_count1找 dname 和 class 是为了在后面进行连接where t1.dname t2.dname
and t1.class t2.class现在给出计算 p_count2 的代码有兴趣可以看看哈~~
select distinct dname, class, count(*) p_count2
from ((select sid, dname, class, sum(credit) sum_credit --选了课但是没有达标的学生from (select sid, cid, dname, class, max(score) max_scorefrom pub.student_course natural join pub.studentgroup by sid, cid, dname, class) natural join pub.coursewhere max_score 60and dname is not nullgroup by sid, dname, class)
union(select distinct sid, dname, class, 0 as sum_credit --在 pub.student 中但是不在 pub.student_course 中的学生这部分学生也算作不达标哦~~from pub.studentwhere sid not in(select sidfrom pub.student_course)))
where sum_credit 10
group by dname, class5-4 创建学院班级学分达标情况统计表2test5_04依据pub.student pub.coursepub.student_course统计形成表中各项数据成绩60为及格计入学分2008级及之前的班级总学分8算作达标2008级之后的班级学分10算作达标院系为空值的数据不统计在下表中表结构院系名称dname、班级class、学分达标人数p_count1、学分未达标人数p_count2、总人数p_count。 Dname varchar(30) class varchar(10) P_count1 int P_count2 int P_count int 计算机学院 2006 计算机学院 2007 软件学院 2006 ……………… 思路 如果你看懂了 5-3 的思路那么相信这道题对你来说会比较简单。只需要改变一下条件增加一个判定即可
create table test5_04(dname varchar(30),class varchar(10),p_count1 int,p_count2 int,p_count int)-----------一张表一张表地看思路更清晰哦一共就 t1t2 两张表--------------
insert into test5_04
select t1.dname, t1.class, t2.p_count1, (t1.p_count - t2.p_count1) p_count2, t1.p_count
from (select distinct dname, class, count(*) p_countfrom pub.studentwhere dname is not nullgroup by dname, class) t1,(select distinct dname, class, count(*) p_count1from (select sid, dname, class, sum(credit) sum_creditfrom (select sid, cid, dname, class, max(score) max_scorefrom pub.student_course natural join pub.studentgroup by sid, cid, dname, class) natural join pub.coursewhere max_score 60and dname is not nullgroup by sid, dname, class)where sum_credit --Look at here! 增加的条件在这里 —— 就是根据 class 来决定 sum_credit 的判定条件哦casewhen class 2008 then 8when class 2008 then 10endgroup by dname, class) t2where t1.dname t2.dname
and t1.class t2.class手动算 p_count2 也是在相同的地方加上 case 即可。
5-5 注意事项 如果一个学生一门课程有多次成绩仅仅计算最高成绩也就是只用他的最好成绩参加如下统计。 5 查询各院系不包括院系名称为空的的数据结构平均成绩avg_ds_score、操作系统平均成绩avg_os_score平均成绩四舍五入到个位创建表test5_05表结构及格式如下 Dname Avg_ds_score Avg_os_score 马克思主义学院 72 70 软件学院 77 74 艺术学院 77 76 医学院 74 73 思路 主要还是运用了分块查询的思想先从 t1 表中找到唯一的 dname 属性值然后从 t2 表中找到“数据结构”课程成绩的平均值注意其中包含了一个求最大值的过程同样接着从 t2 表中找到“操作系统”课程成绩的平均值也有一个求最大值的过程最后使用 dname 来进行连接即可或者直接 natural join 也行
create table test5_05(dname varchar(20),avg_ds_score int,avg_os_score int);insert into test5_05
select distinct t1.dname, t2.avg_ds_score, t3.avg_os_score
from(select distinct dnamefrom pub.student) t1,(select distinct dname, round(avg_ds_score, 0) avg_ds_scorefrom (select distinct dname, avg(max_ds_score) avg_ds_scorefrom (select distinct sid, dname, max(score) max_ds_scorefrom pub.student natural join pub.student_coursewhere cid (select cid from pub.course where name 数据结构)group by sid, dname)group by dname)) t2,(select distinct dname, round(avg_os_score, 0) avg_os_scorefrom (select distinct dname, avg(max_os_score) avg_os_scorefrom (select distinct sid, dname, max(score) max_os_scorefrom pub.student natural join pub.student_coursewhere cid (select cid from pub.course where name 操作系统)group by sid, dname)group by dname)) t3
where t1.dname t2.dname
and t2.dname t3.dname5-6 查询计算机科学与技术学院的同时选修了数据结构、操作系统两门课的学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score创建表test5_06。 思路 分块查询先找到同时选了这两门课的学生的 sid, name在分别找这两门课的成绩使用存在性检测 not exists … except(minus) … 结构可以找到同时选修了这两门课的学生的 sid, name需要注意的是这两门课的成绩都需要用对应成绩的最大值哦~~
create table test5_06 asselect t1.sid, t1.name, 计算机科学与技术学院 as dname, t2.ds_score, t3.os_scorefrom (select sid, namefrom pub.student Swhere dname 计算机科学与技术学院and not exists( (select cidfrom pub.coursewhere name 数据结构 or name 操作系统)minus(select cidfrom pub.student_course Twhere S.sid T.sid))) t1,(select sid, max(score) ds_scorefrom pub.student_coursewhere cid (select cid from pub.course where name 数据结构)group by sid) t2,(select sid, max(score) os_scorefrom pub.student_coursewhere cid (select cid from pub.course where name 操作系统)group by sid) t3where t1.sid t2.sidand t2.sid t3.sid5-7 查询计算机科学与技术学院的选修了数据结构或者操作系统的学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score创建表test5_07。 思路 先去找到计算机科学与技术学院选修了“数据结构”或者“操作系统”的学生的 sid, name由于二者只修其一的学生我们同样需要将它保留下来因此可以使用 natural left outer join来连接成绩这两门课的成绩同样注意使用最大值即可
create table test5_07 asselect sid, name, dname, ds_score, os_scorefrom (select distinct sid, name, dnamefrom pub.student natural join pub.student_coursewhere dname 计算机科学与技术学院and cid in (select cid from pub.course where name 数据结构 or name 操作系统)) natural left outer join (select distinct sid, max(score) ds_scorefrom pub.student_course natural join pub.coursewhere name 数据结构group by sid)natural left outer join(select distinct sid, max(score) os_scorefrom pub.student_course natural join pub.coursewhere name 操作系统group by sid)5-8 查询计算机科学与技术学院所有学生的学号sid、姓名name、院系名称dname、数据结构成绩ds_score、操作系统成绩os_score创建表test5_08。 思路 直接找到计算机科学与技术学院所有学生的 sid, name接着找到每门课成绩的最大值最后使用 natural left outer join 即可
create table test5_08 asselect sid, name, dname, ds_score, os_scorefrom (select distinct sid, name, dnamefrom pub.studentwhere dname 计算机科学与技术学院) natural left outer join (select distinct sid, max(score) ds_scorefrom pub.student_course natural join pub.coursewhere name 数据结构group by sid)natural left outer join(select distinct sid, max(score) os_scorefrom pub.student_course natural join pub.coursewhere name 操作系统group by sid)再次强调一定是看懂思路之后自己实践哈~~ 有问题还请斧正
