中关村面试题(数据库部分)

中关村IT企业Java笔试题1.已知表T1中有2行数据，T2中有3行数据，执行SQL语句 D“select a.* from T1 a,T2 b”后，返回的行数为A 2B 3C 5D 62.已知表T1和T2的字段定义完全相同，T1 ，T2中各有5条不同的数据，其中T1有3条数据存在域表T2中，语句”select * from T1 union select * from T2”，返回的行数为:A 8B 10C 3D 123.已知表T1含有字段ID,CourseID和Score，且数据为 BID CourseID Score3 1 902 1 852 2 903 2 80则语句”select id,sum(score) from T1 group by ID”执行结果为A ID sum(score)3 1702 175B ID sum(score)2 1753 170C ID sum(score)2 1703 175D ID sum(score)3 1752 1704.电话号码表t_phonebook中含有100万条数据，其中号码字段phoneno上创建了唯一索引，且电话号码全部由数字组成，要统计号码头为321的电话号码的数量，下面写法执行速度最慢的是 CA select count(*) from t_phonebook where phoneno>=‟321‟ and phoneno<‟321A‟B select count(*) from t_phonebook where phoneno like …321%‟C select count(*) from t_phonebook where substr(phone,1,3)=‟321‟5.已知表tbl中字段land_ID建有索引，字段cust_id建有唯一索引，下列语句查询逻辑相同，其中执行效率最优的是 BA select * from tbl where land_id>750 or (cust_id=180 or cust_id=560)B select * from tbl where (cust_i=180 or cust_id=560) or land_id>750C select * from tbl where land_id>750 union select * from tbl where cust_id=180 union select * from tbl where cust_id = 560D select * from tbl where land_id>750 union (select * from tbl where cust_id=180 union all select * from tbl where cust_id=560)6.员工表staff表结构如下,staff和skill字段建有唯一约束 Dstaff varchar2(10),skill varchar2(10)哪条语句可以查询同时拥有技能A和技能B的员工A select staff from staffskill where skill=…A‟ or skill=…B‟B select staff from staffskill where skill=‟A‟ and skill=‟B‟C select staff from staffskill where skill=‟A‟ or skill=‟B‟ group by staffD select staff from staf，fskill where skill=‟A‟ or skill=‟B‟ group by staff having count(*)>17.员工表staff结构如下 Cstaffno varchar2(10)email varchar2(50)哪一个SQL语句查询出没有Email地址的员工号A select staffno from staff where Email = nullB select staffno from staff where Email<>nullC select staffno from staff where Email is nullD select staffno from staff where Email is not null8.存在两个结构相同的数据库表T1(col1,col2,col3)、T2(col1,col2,col3)，写出一SQL语句将所有T1数据导入到T2表 DA select col1,col2,col3 from T1 into T2(col1,col2,col3)B insert T1 (col1,col,col3) into T2(col1,col2,col3)C insert into T2 (col1,col2,col3) as select col1,col2,col3 from T1D insert into T2(col1,col2,col3) select col1,col2,col3 from T1;9.一个表的字段为varchar2，如果建表时没有指定长度，则默认长度为 FA 1B 25C 38D 255E 4000F 建表时varchar2类型字段必须指定长度10.用truncate和delete语句删除表中数据的区别 AA truncate 命令不记录日志B truncate 命令记录日志C delete命令不记录日志11.如下查询语句select id_number ,100/quantity from inventory 如果quantity为空,则查询语句的第二个字段将返回 BA a spaceB a null valueC a value of 0D a value of 100E the keywork null12.如下语句：select i.id_number, m.id_number from inventory i, manufacturer m wherei.manufacturer_id = m.id_number order by inventory.description执行是错误的，请问以下措施哪个能够改正这个错误? AA 在order by字句中使用表的别名B where 字句中去掉表的别名C where字句中用表名代替表的别名D order by字句中去掉表名，只要字段名称即可13. A 表字段a类型int中有100条记录，值分别为1至100，如下语句select a from A wherea between 1 and 50 or (a in (25,70,95) and a between 25 and 75)则如下哪个值在这个sql语句返回的结果集中 AA 30B 51C 75D 9514.变量v_time=‟23-MAY-00‟, 如下那条语句返回值为‟01-JAN-00‟? BA select round(v_time,‟DAY‟) from dualB select round(v_time,‟YEAR‟) from dualC select round(v_time,‟MONTH‟) from dualD A select round(to_char(v_time,‟yyyy‟)) from dual15.关于索引（index）哪些说法是错误的？ AA 创建索引能提高数据插入的性能B 索引应该根据具体检索需求来创建，在选择性好的列上创建索引C 索引并非越多越好16.指出下面sql语句错误之处 Dselect id_number “Part Number”, sum(price) “price”from inventory where price >50 group by “Part Number” order by 2;A order by 2B from inventoryC where price>50D group by “Part Number”17.如下语句 Aif v_num>5 thenv_example := 1;elsif v_num>10 thenv_example :=2;elsif v_num<20 thenv_example :=3;elsif v_num<39 thenv_example :=4;else v_example :=5;如果v_num=37，则v_example 的值为A 1B 2C 3D 4E 518.如下语句： Bbeginfor i in 1..8 loopif i=2 thennull;elseif i=5 thenrollback;elseif i=8 thencommit;elseinsert into texample values(i);end if;end if;end if;end loop;commit;end;执行该语句后将有几条记录插入到表texample表中bA 1B 2C 3D 419.设计一表结构，某字段要求存放员工编号，为固定10位长字符，采用哪种类型字段: AA charB longC varcharD varray。