数据库SQL进阶练习1（基于Oracle,个别语法与mysql有差别）

数据库SQL进阶练习1（基于Oracle,个别语法与mysql有差别）

1、建立学员表stuInfo和学员成绩表stuMarks
学员表包括：学员姓名stuName，学号stuNo,性别stuSex，年龄stuAge，地址stuAddress
成绩表包括：成绩号examNo，学号stuNo,笔试成绩writtenExam，机试成绩labExam

2、向两个表中分别录入信息
学员表: 张秋丽 s25301 男 18 北京海淀
李斯文 s25303 女 22 河南洛阳
李文东 s25302 男 31
刘小林 s25304 男 23 新疆
成绩表：E0001 s25301 80 58
E0002 s25302 50
E0003 s25303 92 97
E0004 s25304 98 85

/=插入数据练习===/
–向学员信息表stuInfo插入数据–
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)VALUES(‘张秋丽’,‘s25301’,‘男’,18,‘北京海淀’)
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES(‘李斯文’,‘s25303’,‘女’,22,‘河南洛阳’)
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES(‘李文才’,‘s25302’,‘男’,31)
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES(‘欧阳俊雄’,‘s25304’,‘男’,28,‘新疆威武哈’)

–向学员成绩表stuMarks插入数据–
INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES(‘E0001’,‘s25301’,80,58)
INSERT INTO stuMarks(ExamNo,stuNo,writtenExam) VALUES(‘E0002’,‘s25302’,50)
INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES(‘E0003’,‘s25303’,92,97)
INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES(‘E0004’,‘s25304’,98,85)

3、完成下列操作
–1.查询两表的数据–
select * from STUINFO t;
select* from STUMARKS;
–2.查询男学员名单–
select * from stuinfo where stusex=‘男’;
–3.查询笔试成绩优秀的学员情况（成绩在75~100之间)–
select * from stumarks where writtenexam between 75 and 100;
–4.显示学员信息，地址没有填写的显示“地址不详”–
select stuname,stuno,stusex,stuage,nvl(stuaddress,‘地址不详’)as “stuAddress” from stuinfo;
–5.统计笔试考试平均分和机试考试平均分-- ？空值问题
select avg(writtenexam)as “avgWrittenExam”,avg(nvl(labexam,0))as “avgLabExam” from stumarks;
–6.统计参加本次考试的学员人数
select count(stuName)as"学员人数" from stuinfo;
–7.查询没有通过考试的人数（笔试或机试小于60分）-- ？空值问题
select count(stuno)as"不及格人数" from stumarks where writtenexam<60 or nvl(labexam,0)<60;
–8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分–
select stuno,writtenexam,nvl(labexam,0), (writtenexam+nvl(labexam,0))/2 as “平均分” from stumarks;
–9.排名次（按平均分从高到低排序），显示名次、学号、平均分-- ?名次
select row_number()over(order by (writtenexam+nvl(labexam,0))/2 desc) as"名次", stuno,
(writtenexam+nvl(labexam,0))/2 as “平均分” from stumarks order by nvl(labexam,0)/2 DESC;
–10.机试成绩都提5分–
select stuno,writtenexam,nvl(labexam,0)+5 as"labExam" from stumarks;
–11.机试成绩100分封顶（加分后超过100分的，按100分计算）–
select stuno,writtenexam,(case when nvl(labexam,0)+5>100 then 100 else nvl(labexam,0)+5 end)as “labExam”
from stumarks;
/*数据是char类型时
select stuno,writtenexam,
(case when nvl(labexam,0)+5>100 then ‘100+’ else cast(nvl(labexam,0)+5 as varchar(6))end)as “labExam”
from stumarks;
*/
–12.笔试不及格的提到及格，60-90分的加5分，90分以上加3分（但不能超过100）–
select stuno,(case when writtenexam<60 then 60 when writtenexam>60 and writtenexam<90 then writtenexam+5 when writtenexam+3>100 then 100 else writtenexam+3 end)as “labExam”
from stumarks;