数据库面试题

8：设有关系EMP（ENO，ENAME，SALARY，DNO），其中各属性的含义依次为职工号、姓名、工资和所在部门号，以及关系DEPT（DNO，DNAME，MANAGER），其中各属性含义依次为部门号、部门名称、部门经理的职工号。（回答下列题目）(20分)1.列出各部门中工资不低于600元的职工的平均工资。(10分)2.请用SQL语句将“销售部”的那些工资数额低于600的职工的工资上调10%。(10分)1.SELECTDNO,AVG(SALARY)FROMEMPWHERESALARY＞=600GROUPBYDNO2.UPDATEEMPSETSALARY=SALARY*1.1WHEREENOIN(SELECTENOFROMEMP,DEPTWHEREEMP.DNO=DEPT.DNOANDDNAME='销售部'ANDSALARY＜600;或者UPDATEEMPSETSALERY=SALERY*1.1FROMEMPA,ENOBWHEREA.DNO=B.DNOANDB.DNAME='销售部'ANDB.SALARY＜600;6：设职工---社团数据库有三个基本表：(20分)职工（职工号，姓名，年龄，性别）；社会团体（编号，名称，负责人，活动地点）；参加（职工号，编号，参加日期）；其中：1）职工表的主键为职工号。2）社会团体表的主键为编号；主键为负责人，被参照表为职工表，对应属性为职工号。3）参加表的职工号和编号为主键；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。试用SQL语句表达下列操作：1）定义职工表、社会团体表和参加表，并说明其主键和参照关系。2）建立下列两个视图。社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；参加人情况（职工号，姓名，社团编号，社团名称，参加日期）3）查找参加唱歌队或篮球队的职工号和姓名。4）求参加人数超过100人的社会团体的名称和负责人。答：1）CREATTABLE职工（职工号CHAR（10）NOTNULL，姓名CHAR（8）NOTNULL，年龄SMALLINT，性别CHAR（2），CONSTRAINTC1_PKPRIMARYKEY（职工号）；CREATTABLE社会团体（编号CHAR（8）NOTNULL，名称CHAR（12）NOTNULL，负责人CHAR（8），活动地点VARCHAR（50），CONSTRAINTC2_PKPRIMARYKEY（职工号）CONSTRAINTC2FOREIGNKEY（负责人）REFERENCES职工（职工号））；CREATTABLE参加（职工号CHAR（8），编号CHAR（8），参加日期DATA，CONSTRAINTC3_PKPRIMARYKEY（职工号，编号），CONSTRAINC3FOREIGNKEY(职工号)REFERENCES职工（职工号））；2）CREATVIEW社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）ASSELECT编号，名称，负责人，姓名，性别FROM社会团体，职工WHERE社会团体.负责人＝职工.职工号;CREATVIEW参加人情况（职工号，姓名，社团编号，参加日期）ASSELECT参加.职员号,姓名，社会团体.编号，名称，参加日期FROM职工，社会团体，参加WHERE职工.职工号＝参加.职工号AND参加.编号＝社会团体.编号;3)SELECT职工号，姓名FROM职工，社会团体，参加WHERE职工.职工号＝参加.职工号AND参加.编号＝社会团体.编号AND社会团体.名称IN（'唱歌队'，'篮球队'）；4）SELECT社会团体.名称，职工.姓名FROM职工，社会团体，参加WHERE社会团体.编号＝参加.编号AND社会团体.负责人＝职工.职工号GROUPBY参加.编号HAVINGCOUNT（参加.编号）＞100；8：设工程—零件数据库中有四个基本表：（18分）供应商（供应商代码，姓名，所在城市，联系电话）；工程（工程代码，工程名，负责人，预算）；零件（零件代码，零件名，规格，产地，颜色）；供应零件（供应商代码，工程代码，零件代码，数量）试用SQL语句完成下列操作：1）查找预算在50000~100000元之间的工程的信息，并将结果按预算降序排列。2)找出使用上海产的零件的工程名称。3)将由供应商S5供给工程代码为J4的零件P6改为由S3供应，并将零件P6的产地改为S3供货商的所在城市。4）从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录。1.SELECT*FROM工程WHERE预算BETWEEN50000AND100000(或者WHERE预算=50000AND预算=1000000)ORDERBY预算DESC；2.SELECT工程名FROM供应商，供应零件，工程WHERE供应商.供应商代码＝供应零件.供应商代码AND供应零件.工程代码＝工程.工程代码AND供应商.所在城市＝'上海'；3.UPDATE供应零件SET供应商代码＝'S3'WHERE供应商代码＝'S5'AND工程代码＝'J4'AND零件代码＝'P6';UPDATE零件SET产地=B.所在城市FROM零件A,供应商BWHEREA.零件代码='P6'ANDB.供应商代码='S3';4.DELETEFROM供应零件WHERE供应商代码='S2';DELETEFROM供应商WHERE供应商代码='S2';4.数据库部分1、存储过程与触发器必须讲，经常被面试到?createprocedureinsert_Student(_namevarchar(50),_ageint,out_idint)begininsertintostudentvalue(null,_name,_age);selectmax(stuId)into_idfromstudent;end;callinsert_Student('wfz',23,@id);select@id;mysqlcreatetriggerupdate_StudentBEFOREupdateonstudentFOREACHROW-select*fromstudent;触发器不允许返回结果createtriggerupdate_StudentBEFOREupdateonstudentFOREACHROWinsertintostudentvalue(null,'zxx',28);mysql的触发器目前不能对当前表进行操作createtriggerupdate_StudentBEFOREupdateonstudentFOREACHROWdeletefromarticleswhereid=8;这个例子不是很好，最好是用删除一个用户时，顺带删除该用户的所有帖子这里要注意使用OLD.id其实我认为触发器用处还是很多的，不应该不用，而应该常用。比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高。而UCH没有用触发器，效率和数据处理能力都很低。呵呵，个人意见。1、数据库三范式是什么?第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式）数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。第二范式（2NF）：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。第三范式的要求如下：满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。所以第三范式具有如下特征：1，每一列只有一个值2，每一行都能区分。3，每一个表都不包含其他表已经包含的非主关键字信息。例如，帖子表中只能出现发帖人的id，而不能出现发帖人的id，还同时出现发帖人姓名，否则，只要出现同一发帖人id的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。1、说出一些数据库优化方面的经验?看mysql帮助文档子查询章节的最后部分，例如，根据扫描的原理，下面的子查询语句要比第二条关联查询的效率高：1.selecte.name,e.salarywheree.managerid=(selectidfromemployeewherename='zxx');2.selecte.name,e.salary,m.name,m.salaryfromemployeese,employeesmwheree.managerid=m.idandm.name='zxx';表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等将姓名和密码单独从用户表中独立出来。这可以是非常好的一对一的案例哟！sql语句全部大写，特别是列名和表名都大写。特别是sql命令的缓存功能，更加需要统一大小写，sql语句发给oracle服务器语法检查和编译成为内部指令缓存和执行指令。根据缓存的特点，不要拼凑条件，而是用?和PreparedStatment还有索引对查询性能的改进也是值得关注的。备注：下面是关于性能的讨论举例4航班3个城市m*nselect*fromflight,citywhereflight.startcityid=city.cityidandcity.name='beijing';m+nselect*fromflightwherestartcityid=(selectcityidfromcitywherecityname='beijing');selectflight.id,'beijing',flight.flightTimefromflightwherestartcityid=(selectcityidfromcitywherecityname='beijing')2、union和unionall有什么不同?假设我们有一个表Student，包括以下字段与数据：droptablestudent;createtablestudent(idintprimarykey,namenvarchar2(50)notnull,scorenumbernotnull);insertintostudentvalues(1,'Aaron',78);insertintostudentvalues(2,'Bill',76);insertintostudentvalues(3,'Cindy',89);insertintostudentvalues(4,'Damon',90);insertintostudentvalues(5,'Ella',73);insertintostudentvalues(6,'Frado',61);insertintostudentvalues(7,'Gill',99);insertintostudentvalues(8,'Hellen',56);insertintostudentvalues(9,'Ivan',93);insertintostudentvalues(10,'Jay',90);commit;Union和UnionAll的区别。select*fromstudentwhereid4unionselect*fromstudentwhereid2andid6结果将是1Aaron782Bill763Cindy8