公司SQL考核

题目及答案 (1)数据库脚本（建表及插入语句） (8)题目及答案答案已经一个个测了，没有问题，但可能由于上传问题单引号的位置出现问题（必须是英文状态下的单引号）1.查询没学过“谌燕”老师课的同学，显示（学号、姓名）select out_stu.student_no, out_stu.student_namefrom hand_student out_stuwhere out_stu.student_no not in(select distinct hs.student_nofrom hand_course c,hand_teacher t,hand_student hs,hand_student_core hscwhere c.teacher_no = t.teacher_no andhs.student_no = hsc.student_no andhsc.course_no = c.course_no and t.teacher_name = '谌燕');SELECT hs.student_no, hs.student_nameFROM hand_student hsWHERE NOT EXISTS(SELECT1FROM hand_course hc, hand_teacher ht,hand_student_core hscWHERE hc.teacher_no = ht.teacher_no ANDhc.course_no = hsc.course_no ANDht.teacher_name = '谌燕'ANDhsc.student_no = hs.student_no);2.查询没有学全所有课的同学，显示（学号、姓名）select hs.student_no, hs.student_namefrom hand_student hs, hand_student_core hscwhere hs.student_no = hsc.student_no(+)group by hs.student_no, hs.student_namehaving count(hsc.course_no) < (select count(*)from hand_course c);3.查询 c001 课程比 c002 课程成绩高的所有学生，显示：学号，姓名select stu.student_no, stu.student_namefrom hand_student stu,(select t1.student_nofrom (select *from hand_student_core scwhere sc.course_no ='c001') t1,(select *from hand_student_core scwhere sc.course_no = 'c002') t2where t1.student_no = t2.student_no and t1.core > t2.core) ttwhere stu.student_no = tt.student_no;4.按各科平均成绩和及格率的百分数，按及格率高到低的顺序排序，显示（课程号、平均分、及格率）select hsc.course_no,avg(hsc.core), sum(casewhen hsc.core > 60then1elseend)/count(1)*100 ||'%'pass_ratefrom hand_student_core hscgroup by hsc.course_noorder by to_number(substr(pass_rate, 1, length(pass_rate) - 1)) desc;5.1992年之后出生的学生名单找出年龄最大和最小的同学，显示（学号、姓名、年龄）SELECT hs.student_no, hs.student_name, hs.student_age FROM hand_student hs,(SELECT MAX(hs.student_age) max_age,MIN(hs.student_age) min_ageFROM hand_student hsWHERE to_number(to_char(SYSDATE, 'yyyy')) -hs.student_age > 1992) hhWHERE hs.student_age = hh.max_age OR hs.student_age = hh.min_age;6.列出矩阵类型各分数段人数，横轴为分数段[100-85]、[85-70]、[70-60]、[<60]，纵轴为课程号、课程名称（提示使用case when 句式）SELECT hsc.course_no,hc.course_name, SUM(CASEWHEN hsc.core BETWEEN85AND100THEN1ELSEEND) AS "100 - 85", SUM(CASEWHEN hsc.core BETWEEN70AND85THEN1ELSEEND) AS "85 - 70", SUM(CASEWHEN hsc.core BETWEEN60AND70THEN1ELSEEND) AS "70 - 60", SUM(CASEWHEN hsc.core < 60then1ELSEEND) AS "< 60"FROM hand_student_core hsc, hand_course hcWHERE hsc.course_no = hc.course_noGROUP BY hsc.course_no, hc.course_name;7.查询各科成绩前三名的记录:(不考虑成绩并列情况)，显示（学号、课程号、分数）SELECT student_no, course_no, coreFROM (SELECT hsc.student_no,hsc.course_no,hsc.core,DENSE_RANK() OVER(PARTITION BY hsc.course_no ORDER BY hsc.core DESC) ranksFROM hand_student_core hsc)WHERE ranks < 4;8.查询选修“谌燕”老师所授课程的学生中每科成绩最高的学生，显示（学号、姓名、课程名称、成绩）SELECT hs.student_no, hs.student_name, hc.course_name, hsc.coreFROM hand_student hs,hand_student_core hsc,hand_course hc,hand_teacher htWHERE hs.student_no = hsc.student_no AND hsc.course_no = hc.course_no ANDhc.teacher_no = ht.teacher_no AND ht.teacher_name ='谌燕'ANDhsc.core = (SELECT MAX(sc.core)FROM hand_student_core scWHERE sc.course_no = hc.course_no);9.查询两门以上不及格课程的同学及平均成绩，显示（学号、姓名、平均成绩（保留两位小数））SELECT hsc.student_no, hs.student_name, ROUND(AVG(hsc.core), 2) avg_coreFROM hand_student_core hsc, hand_student hsWHERE EXISTS(SELECT sc.student_noFROM hand_student_core scWHERE sc.core < 60AND sc.student_no = hsc.student_noGROUP BY sc.student_noHAVING COUNT(sc.student_no) > 1) AND hsc.student_no = hs.student_noGROUP BY hsc.student_no, hs.student_name;10。

查询姓氏数量最多的学生名单，显示（学号、姓名、人数）SELECT hs.student_no, hs.student_name, tFROM (SELECT SUBSTR(hs.student_name, 1, 1) surname,COUNT(1) cnt,dense_rank() OVER(ORDER BY COUNT(1) DESC) ranks FROM hand_student hsGROUP BY SUBSTR(hs.student_name, 1, 1)) ht,hand_student hsWHERE SUBSTR(hs.student_name, 1, 1) = ht.surname ANDht.ranks = 1;11.查询课程名称为“J2SE”的学生成绩信息，90以上为“优秀”、80-90为“良好”、60-80为“及格”、60分以下为“不及格”，显示（学号、姓名、课程名称、成绩、等级）SELECT hsc.student_no,hs.student_name,hc.course_name,hsc.core, CASEWHEN hsc.core >= 90THEN'优秀'WHEN hsc.core < 90AND hsc.core >= 80THEN'良好'WHEN hsc.core < 80AND hsc.core >= 60THEN'及格'WHEN hsc.core < 60THEN'不及格'END core_levelFROM hand_student_core hsc, hand_course hc, hand_student hs WHERE hsc.course_no = hc.course_no AND hsc.student_no = hs.student_no ANDhc.course_name ='J2SE';12.这是一个树结构，查询教师“胡明星”的所有主管及姓名:（无主管的教师也需要显示），显示（教师编号、教师名称、主管编号、主管名称）select t.teacher_no,t.teacher_name,t.manager_no,t2.teacher_name manager_namefrom hand_teacher t, hand_teacher t2where t.manager_no = t2.teacher_no;13.查询分数高于课程“J2SE”中所有学生成绩的学生课程信息，显示（学号，姓名，课程名称、分数）SELECT hsc.student_no, hs.student_name, hc.course_name, hsc.coreFROM hand_student_core hsc, hand_course hc, hand_student hs WHERE hsc.course_no = hc.course_no AND hsc.student_no = hs.student_no ANDhsc.core > ALL(SELECT hsc.coreFROM hand_student_core hsc, hand_course hcWHERE hsc.course_no = hc.course_no AND hc.course_name ='J2SE') ANDhc.course_name != 'J2SE';14.分别根据教师、课程、教师和课程三个条件统计选课的学生数量：（使用rollup),显示（教师名称、课程名称、选课数量）SELECT ht.teacher_name, hc.course_name, COUNT(1) nums FROM hand_student_core hsc, hand_teacher ht, hand_course hc WHERE hsc.course_no = hc.course_no AND hc.teacher_no = ht.teacher_noGROUP BY ROLLUP(ht.teacher_name, hc.course_name);15.查询所有课程成绩前三名的按照升序排在最开头，其余数据排序保持默认，显示（学号、成绩）SELECT hs.student_no, hs.coreFROM (SELECT rownum rn,hsc.student_no,hsc.core,row_number() OVER(ORDER BY hsc.core DESC) ranks FROM hand_student_core hsc) hsORDER BY CASEWHEN ranks <= 3THEN-ranksELSEnullEND,rn;数据库脚本（建表及插入语句）直接复制即可用/*Create table 学生信息 */create table HAND_STUDENT(STUDENT_NO VARCHAR2(10) not null,STUDENT_NAME VARCHAR2(20),STUDENT_AGE NUMBER(2),STUDENT_GENDER VARCHAR2(5));/*Add comments to the table 给表名添加注释*/comment on table HAND_STUDENT is‘学生信息表’;/*Add comments to the columns 给字段添加注释*/comment on column HAND_STUDENT.STUDENT_NOis‘学号’;comment on column HAND_STUDENT.STUDENT_NAMEis‘姓名’;comment on column HAND_STUDENT.STUDENT_AGEis‘年龄’;comment on column HAND_STUDENT.STUDENT_GENDERis‘性别’;/*Create/Recreate primary, unique and foreign key constraints 添加外键约束 */alter table HAND_STUDENT add primary key (STUDENT_NO);/*Create table 教师信息表 */create table HAND_TEACHER(TEACHER_NO VARCHAR2(10) not null,TEACHER_NAME VARCHAR2(20),MANAGER_NO VARCHAR2(10));/*Add comments to the table 添加注释 */comment on table HAND_TEACHERis‘教师信息表’;–Add comments to the columnscomment on column HAND_TEACHER.TEACHER_NOis‘教师编号’;comment on column HAND_TEACHER.TEACHER_NAMEis‘教师名称’;comment on column HAND_TEACHER.MANAGER_NOis‘上级编号’;/*Create/Recreate primary, unique and foreign key constraints 添加约束*/alter table HAND_TEACHER add primary key (TEACHER_NO);/*Create table 课程信息表 */create table HAND_COURSE(COURSE_NO VARCHAR2(10) not null,COURSE_NAME VARCHAR2(20),TEACHER_NO VARCHAR2(20) not null);/*Add comments to the table */comment on table HAND_COURSEis‘课程信息表’;–Add comments to the columnscomment on column HAND_COURSE.COURSE_NOis‘课程号’;comment on column HAND_COURSE.COURSE_NAMEis‘课程名称’;comment on column HAND_COURSE.TEACHER_NOis‘教师编号’;/*Create/Recreate primary, unique and foreign key constraints */alter table HAND_COURSE add constraint PK_COURSE primary key (COURSE_NO, TEACHER_NO);/*Create table 成绩信息表 */create table HAND_STUDENT_CORE(STUDENT_NO VARCHAR2(10) not null,COURSE_NO VARCHAR2(10) not null,CORE NUMBER(4,2));/*Add comments to the table */comment on table HAND_STUDENT_COREis‘学生成绩表’;–Add comments to the columnscomment on column HAND_STUDENT_CORE.STUDENT_NOis‘学号’;comment on column HAND_STUDENT_CORE.COURSE_NOis‘课程号’;comment on column HAND_STUDENT_CORE.COREis‘分数’;/*Create/Recreate primary, unique and foreign key constraints */alter table HAND_STUDENT_CORE add constraint PK_SC primary key (STUDENT_NO, COURSE_NO);/*初始化学生表的数据**/insert into HAND_STUDENT values(‘s001’,’张三’,23,’男’);insert into HAND_STUDENT values(‘s002’,’李四’,23,’男’); insert into HAND_STUDENT values(‘s003’,’吴鹏’,25,’男’); insert into HAND_STUDENT values(‘s004’,’琴沁’,20,’女’); insert into HAND_STUDENT values(‘s005’,’王丽’,20,’女’); insert into HAND_STUDENT values(‘s006’,’李波’,21,’男’); insert into HAND_STUDENT values(‘s007’,’刘玉’,21,’男’); insert into HAND_STUDENT values(‘s008’,’萧蓉’,21,’女’); insert into HAND_STUDENT values(‘s009’,’陈萧晓’,23,’女’); insert into HAND_STUDENT values(‘s010’,’陈美’,22,’女’); commit;/********初始化教师表*************/insert into HAND_TEACHER values(‘t001’, ‘刘阳’,”);insert into HAND_TEACHER values(‘t002’, ‘谌燕’,’t001’); insert into HAND_TEACHER values(‘t003’, ‘胡明星’,’t002’); commit;/*****初始化课程表******************/insert into HAND_COURSE values(‘c001’,’J2SE’,’t002’); insert into HAND_COURSE values(‘c002’,’Java Web’,’t002’); insert into HAND_COURSE values(‘c003’,’SSH’,’t001’); insert into HAND_COURSE values(‘c004’,’Oracle’,’t001’); insert into HAND_COURSE values(‘c005’,’SQL SERVER 2005’,’t003’);insert into HAND_COURSE values(‘c006’,’C#’,’t003’);insert into HAND_COURSE values(‘c007’,’JavaScript’,’t002’); insert into HAND_COURSE values(‘c008’,’DIV+CSS’,’t001’); insert into HAND_COURSE values(‘c009’,’PHP’,’t003’); insert into HAND_COURSE values(‘c010’,’EJB3.0’,’t002’); commit;/*****初始化成绩表*************/insert into HAND_STUDENT_CORE values(‘s001’,’c001’,58.9); insert into HAND_STUDENT_CORE values(‘s002’,’c001’,80.9); insert into HAND_STUDENT_CORE values(‘s003’,’c001’,81.9); insert into HAND_STUDENT_CORE values(‘s004’,’c001’,60.9); insert into HAND_STUDENT_CORE values(‘s001’,’c002’,82.9); insert into HAND_STUDENT_CORE values(‘s002’,’c002’,72.9); insert into HAND_STUDENT_CORE values(‘s003’,’c002’,81.9); insert into HAND_STUDENT_CORE values(‘s001’,’c003’,’59’); commit;。