数据库开发面试题答案.txt
免费下载
create table classinfo(classid number,classname varchar2(20),manager
varchar2(20) )
insert into classinfo (classid,classname,manager) values(1,'苹果班','李老师')
insert into classinfo (classid,classname,manager) values(2,'橘子班','王老师')
insert into classinfo (classid,classname,manager) values (15,'蘑菇班','韩老师')
create table studentinfo (studentid character,classid integer,studentname
varchar2(10),birthdate date,idcard varchar2(20))
insert into studentinfo (studentid,classid,studentname,birthdate,idcard) values
(1,1,'林黛玉',to_date('1989-06-11 ','yyyy-mm-dd hh24:mi:ss'),'3701')
insert into studentinfo (studentid,classid,studentname,birthdate,idcard) values
(2,15,'史湘云',to_date('1987-08-23 ','yyyy-mm-dd hh24:mi:ss'),'3704')
insert into studentinfo (studentid,classid,studentname,birthdate,idcard) values
(3,15,'云写意',to_date('1988-10-26 ','yyyy-mm-dd hh24:mi:ss'),'3703')
insert into studentinfo (studentid,classid,studentname,birthdate,idcard) values
(4,15,'夏沫',to_date('1988-08-11 ','yyyy-mm-dd hh24:mi:ss'),'3707')
insert into studentinfo (studentid,classid,studentname,birthdate,idcard) values
(5,1,'骆绝尘',to_date('1988-12-13 ','yyyy-mm-dd hh24:mi:ss'),'3708')
create table curriculainfo(curriculaid number,curriculaname varchar2(50))
insert into curriculainfo (curriculaid,curriculaname) values (1001,'美术')
insert into curriculainfo (curriculaid,curriculaname) values (1002,'音乐')
create table scoreinfo (studentid character,curriculaid number,score integer)
insert into scoreinfo (studentid ,curriculaid ,score) values(1,1001,80)
insert into scoreinfo (studentid ,curriculaid ,score) values(1,1002,80)
insert into scoreinfo (studentid ,curriculaid ,score) values(2,1001,93)
insert into scoreinfo (studentid ,curriculaid ,score) values(2,1002,70)
insert into scoreinfo (studentid ,curriculaid ,score) values(3,1001,92)
insert into scoreinfo (studentid ,curriculaid ,score) values(3,1002,88)
insert into scoreinfo (studentid ,curriculaid ,score) values(4,1001,90)
insert into scoreinfo (studentid ,curriculaid ,score) values(4,1002,87)
insert into scoreinfo (studentid ,curriculaid ,score) values(5,1001,90)
insert into scoreinfo (studentid ,curriculaid ,score) values(5,1002,83)
第一题
select classinfo.classname,classinfo.manager,studentinfo.studentname,
studentinfo.birthdate,
studentinfo.idcard from studentinfo inner join classinfo
on studentinfo.classid=15 and studentinfo.classid=classinfo.classid order by
studentinfo.birthdate desc;
第二题
select classname as 班级名称,count(studentid) as 班级人数,manager as 班级负责人
from classinfo c left join studentinfo s on c.classid=s.classid group by
classname,manager order by count(studentid) desc;
第三题
select classname as 班级名称,curriculaname as 课程名称,count(studentid) as 学生个数
from scoreinfo sc join curriculainfo cu
on sc.curriculaid=cu.curriculaid join (select st.studentid,cl.classname from
studentinfo st join classinfo cl on
st.classid=cl.classid) ne on sc.studentid=ne.studentid where
sc.curriculaid=1001 group by classname,curriculaname;
第四题
select
studentinfo.studentid,studentinfo.classid,studentinfo.studentname,studentinfo.bi
rthdate,studentinfo.idcard,scoreinfo.studentid,scoreinfo.curriculaid,scoreinfo.s
core
from studentinfo inner join scoreinfo on studentinfo.studentid=1 and
scoreinfo.studentid=studentinfo.studentid
select *
from studentinfo inner join scoreinfo on studentinfo.studentid=1 and
scoreinfo.studentid=studentinfo.studentid
第五题
update scoreinfo set score=100 where curriculaid=1001 and studentid in (select
studentid from studentinfo where classid=1)
of 2
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
1
2
Vastbase数据库管理v2.2build15.pdf
3
清华大学第八弹：文科生零基础AI编程：快速提升想象力和实操能力.pdf
4
利用Docker来运行MySQLv2.pdf
5
（浙江大学）DeepSeek：智能时代的全面到来和人机协作的新常态.pdf
6
达梦数据库 DM8数据守护集群安装部署_手动切换.pdf
7
达梦数据库DM8读写分离集群安装部署_手动切换.pdf
8
控制器-replicaset.docx
9
Packt Publishing PostgreSQL Server Programming 2nd (2020).pdf
10
redis安装和配置方法.docx
相关文档
评论