数据库面试题.doc

1.1. 数据库1.1.1. 数据库员工表的定义如下：Last_name VARCHAR2(25)First_name VARCHAR2(25)Salary NUMBER(7,2)现查询显示未达到平均工资的员工姓名.请考察下面的SQL语句:SELECT last_name, first_name FROM employee WHERE salary avg(salary);对这条SQL做以下哪项修改能够达到目的?A. 改变WHERE子句B. 把聚合函数移到SELECT列表中,并增加GROUP子句C. 通过子查询得出平均工资水平并在父查询的WHERE子句中比较D. 把聚合函数移到SELECT列表中,并增加GROUP BY子句和HAVING子句注:SELECT last_name, first_name FROM employee where sal600;1.1.18. 数据库有两张表一个学生表（id，name，sex），一个学生成绩表（id,chineses，English，math），要求查询学生基本信息以及各科成绩和总成绩，总成绩要求在200到300之间，学生姓名降序。 Student Id name sex grade Id chineses english mathSelect from student s join grade g on(s.id=g.id) 1.1.19. 现有关系数据库表如下:学生表(学号 char(6), 姓名,性别,身份证号)课程表(课号 char(6), 名称)成绩表(id,学号,课号,分数)用sql实现下面2题：1.检索姓马的女同学情况(姓名, 身份证号)2.检索有一门或一门以上课程成绩大于等于90的所有学生信息(学号,姓名)Student course ScoreSelect * from stu where exists (select 1 from score where sid=stu.id and score=90);1.1.20. 有三张表,学生表 Student,课程 Coruse,学生课程表 SC,学生可以选修多门课程,一门课程可以被多个学生选修,通过 SC 表关联。1）写出建表语句；2）写出 SQL 语句,查询选修了所有选修课程的学生；3）写出 SQL 语句,查询选修了至少 2 门以上的课程的学生。1.1.21. 表class和student结构如下，请完成后续SQL语句表class属性类型(长度)默认值约束含义CLASSNO数值 (2)无主键班级编号CNAME变长字符 (10)无非空班级名称表student属性类型(长度)默认值约束含义STUNO数值 (8)无主键学号SNAME变长字符 (12)无非空姓名SEX字符 (2)男无性别BIRTHDAY字符(8)无无生日EMAIL变长字符 (20)无唯一电子邮件SCORE数值 (5, 2)无检查成绩CLASSNO数值 (2)无外键，关联到表CLASS的CLASSNO主键班级编号数据STUNOSNAMESEXBIRTHDAYEMAILSCORECLASSNO21tom男1979020389.50156jerry默认值空空空2a. 修改表student的数据，将所有一班的学生成绩加10分。b. 删除表student的数据，将所有3班出生日期晚于1981年5月12日的记录删除。c. 按班级升序排序，成绩降序排序，查询student表的所有记录。d. 查询student表中所有三班成绩为空的学生记录。e. 表student与class联合查询，要求查询所有学生的学号，姓名，成绩，班级名称。f. 按班级编号分组统计每个班的人数、最高分、最低分、平均分，按平均分降序排序。g. 查询一班学生记录中所有成绩高于本班学生平均分的记录。h. 查询所有学生记录中成绩前十名的学生的学号、姓名、成绩、班级编号。1.1.22. 有两张表student和score Student:学号,姓名,性别,年龄Score:学号,语文,数学,英语1.查询张三的学号,姓名,性别, 语文,数学,英语2.查询语文比数学好的同学3.查出姓名相同的学生学号1.1.23. 对一个用户登录模块，要求每个用户只允许次登陆错误，超过则将锁定此帐户。1.1.24. 数据脚本create table test1( pici VARCHAR2(30), busicode VARCHAR2(50), amt NUMBER, flag VARCHAR2(1);- Add comments to the columns comment on column test1.flag is 1表示成功 2表示失败;insert into test1 values(20130201,0201111,10,1);insert into test1 values(20130201,0201112,5,2);insert into test1 values(20130201,0201113,10,2);insert into test1 values(20130201,0201114,5,1);insert into test1 values(20130202,0202111,10,1);insert into test1 values(20130202,0202112,20,1);insert into test1 values(20130202,0202113,20,1);insert into test1 values(20130202,0202114,20,1);insert into test1 values(20130203,0203111,10,2);insert into test1 values(20130203,0203111,10,2);insert into test1 values(20130203,0203111,10,2);实现要求：标识位flag 1表示扣款成功2表示扣款失败 一句sql 查询出每天扣款成功笔数，成功金额，失败笔数，失败金额1.1.25. 数据库 （1）创建sms表的语句（2）写出users与sms左关联的查询语句1.1.26.书表(books)book_id,book_name,creatdate,Lastmodifydate,decription001,三个人的世界,2005-02-02,2005-07-07,NULL作者表(authors)A_id,A_name01,王纷02,李尚03,泰和部门表(depts)d_id,d_name001,编辑一部002,编辑二部003,编辑三部书和作者关联表(bookmap)book_id,A_id001,01001,02001,03部门和作者关联表(depmap)d_id,a_id001,01002,02003,03找出每个部门的所写的总书量,比如,一本书有3个人写,如果三个人在不同的部门,则每个部门的总数量就是1.最后结果如下:部门,书量编辑一部,1编辑二部,1编辑三部,11.1.27.两个表情况表名：wu_plan IDplan modelcorp_code plannum prixis100001 exx22nokia 2000 2001200002 lc001sony 3000 0表名：wu_bomID plan pact amount1 00001 aa1 3002 00001 aa2 2003 00002 bb1 5004 00002 bb2 8005 00002 bb3 400查询这两个表中plan唯一，每一个plan中，amount最少的，plannum大于prixis的记录。结果是:ID plan modelcorp_codeplannumprixispact amount100001exx22nokia 2000 0 aa2 200200002lc001sony 3000 0 bb3 4001.1.28. 数据库方面： create table tbl_threat( pk_threat_id int unsigned not null auto_increment, dt_log_time datetime, /发生时间 i_severity int, /严重程度 i_device_id int, /设备id str_tr_type varchar(64), /告警类型 str_tr_name varchar(256), /类型名称 i_work_id int unsigned, /工单id dt_complete_time datetime, /完成时间 i_status tinyint default 0 comment 0-新分派,1-重新激活 2-完成,/告警状态 primary key (pk_threat_id);create table tbl_work( pk_work_id int not null auto_increment, str_title varchar(256), /工单名称 i_owner_org_id int unsigned, /负责人组织id i_owner_id int, /负责人id dt_dispatch_time datetime, /派单时间 dt_finish_time datetime, /完成时间 i_is_history tinyint default 0, /是否为历史工单 dt_expect_time datetime, /期望完成时间 i_in_time tinyint(1) default 0, /及时性 i_priority tinyint(1), /优先级 primary key (pk_work_id);ps: threat 告警 work 工单 i_severity 严重程度 0代表一般 1代表低危 2 代表中危 3 代表高危1.请查出当前日期前三天发生的告警的类型名称、告警类型、发生时间、严重程度及派单时间用日期按照降序排列。2.请查出当前日期前三天发生的告警的数量最多的告警类型及数量，数量按降序排列的前5个。1.1.29. 设有图书管理数据库：图书(总编号C(6),分类号C(8),书名C(16),作者C(6),出版单位C(20),单价N(6,2)1.检索书价在15元至25元(含15元和25元)之间的图书的书名、作者、书价和分类号，结果按分类号升序排序。2.为图书表建立一个视图. 1.1.30. 写一个oracle 函数，输入参数（字符串 str ,整型 len ,字符 c）返回字符串 rstr 要求：如果字符串str的长度小于 len, 则返回的字符串rstr为在字符串str前填充字符c 达到长度为len的字符串。 如果字符串str的长度大于等于len ,则返回的字符串rstr 为str。 参考答案(oracle)：1.1.31. 表结构 （1）.表名： g_cardapply字段 （字段名/类型/长度）:g_applyno varchar 8; /申请单号 (关键字)g_applydate bigint 8; /申请日期g_state varchar 2; /申请状态（2）.表名： g_cardapplydetail字段(字段名/类型/长度):g_applyno varchar 8; / 申请单号g_name varchar 30; /申请人姓名g_idcard varchar 18; / 申请人身份证号g_state varchar 2; / 申请状态其中，两个表的关联字段为申请单号题目：（1）.查询身份证号码为440401430103082 的申请日期（2）.查询同一个身份证号码有两条以上记录的身份证号码及记录个数（3）.将身份证号码为440401430103082的记录在两个表中的申请状态均改为07（4）.删除g_cardapplydetail表中的所有李姓记录1.1.32. 参见如下表结构回答问题（共15分）出版社: 出版社代码 char(2), 出版社名称 varchar2(32)图书: 图书编号 char(8),图书名称 varchar2(128),出版社代码 char(2),作者代号 char(4),图书简介 varchar2(128)作者: 作者代号 char(4),作者名称 varchar2(10),性别 char(1),年龄 number(3),文学方向 varchar2(64)获奖名单: 获奖日期 date,获奖人员 char(4)1）.编写SQL语句，找出“作者”库中没有出现在“获奖名单”库中所有作者信息的SQL语句，要求使用not in、not exists以及外关联三种方法，并说明哪种方法最优。（6分）2）.“获奖名单”表，写出SQL语句，查询出在上一个月获奖的人员。(4分) 1.1.33. 某公司信息管理系统的需求分析和部分关系模式的结果描述如下：（共15分） （1）.公司有多个部门，每个部门有一名负责人、一间办公室、一部电话、多名职员，每个职员最多属于一个部门，负责人也是公司一名职员。（2）.数据库的部分关系模式设计如下：职员（职员号，职工姓名，月工资，部门号，办公室，电话）部门（部门号，部门名，负责人代码，任职时间）请回答下述问题。1).根据上述说明，请分别给出”职员”和”部门”关系模式的主键及外键。2).请编写SQL语句，针对人数大于等于10的部门创建视图D_View（Dept,D_num, D_Avgpay），其中，Dept为部门号，D_num为部门人数，D_Avgpay为平均工资。3).目前的“职员”关系模式存在什么问题？在不增加新关系模式的前提下，请给出修改后的“职员”和“部门”关系模式。.建一个员工信息表employee, 表中 id （员工代码)、sex (员工性别)、name(姓 名）、departmentid (部门代码) 、address (地址)，birthdate (生日)、postcode (邮编) 、salary薪水)、workdate (入职日期) 、remark （备注信息），其中 postcode 、 remark 可以为空， 薪水需为numher 类型.，生日、入职日期为 date 类型。以员工代码作为主键。2.插入两条记录，id分别为0023, 1023，其余信息自己编造。3.查询员工总数 、薪水总额。4.查询出各部门的最小年龄、最大年龄。5.统计每个部门的男女人数，按照部门代码排序。6.创建入职日期( workdate )索引employee_idx 。7.修改id为 0023 的员工的入职日期为2007-12-31 。8.删除id为1023的员工信息。9使前面所做的修改,删除生效假定数据库设置不是自动生效)10.假定有一表结构和employee完全一样的表employee_bak ，把employee表的数据完全导入employee_bak表。11.假设还有一表duty,其记录为员工的级别 level，也是以员id为主键，根据表employee 、duty 查询出级别在10级以上的所有员工详细信息。12不区分部门查询出入职日期最早的10位员工信息。13.删除索引 employee_idx 。1.1.35. 有一表格（T_user）有如下数据： Id nameagegender-1 张三20 男2 李四22 男3 张三20 男4 王五21 男5 王五20 男请用SQL查询出姓名相同而且年龄也相同的人员姓名。1.1.36. 下面是某班级进行期末考试的相关数据表 学生信息表(T_Student)学生ID学生姓名1小明2小刚3小红课程信息表(T_Course)课程编号课程名称A语文B数学C英语考试成绩表(T_Score)学生ID课程编号分数1A821C952A542C621.按照区间对学生成绩进行优良评级，=85对应“优”；=75 并且=60并且75对应“及格”，= 20050101AND 入住日期 5（4）4.为加快 SQL 语句的执行效率，可在相应的表上创建索引。根据问题 3 中的 SQL 语句， 除主键和外键外，还需要在哪个表的哪些属性上创建索引，应该创建什么类型的索引，请说明原因。1.1.39. 假如有一个制造商生产若干种产品，并由不同的销售负责销售这些产品，产品和销售商之间是多对多的关系，数据库结构如下：Table1 : 该制造商制造的产品表字段名称 字段类型 字段长度 字段含义ProductID 文本 6 产品编号 （Primary Key)