数据库上机考试题-含答案.doc

游戏开发商 Game_Company结构：idChar(5) not nullpknamevarChar2(100) not nullcountryvarChar2(100) 数据：idnamecountry00001世嘉日本00002盛大中国00003任天堂美国游戏 Game结构：idChar(4) not nullpkcompanyIdChar(5) not nullFk :Game_Company的idnamevarChar2(100) not nulltypevarChar2(50)数据：idcompanyIdnametype900100001恶狼传说格斗900200001樱花大战90%益智900300002GT赛车竞技900400003口袋妖怪益智900500003俄罗斯方块益智900600003马力欧益智900700003口袋妖怪竞技游戏者 Player结构：useridChar(3) not nullpkusernamevarChar2(50) not nullgameidChar(4) not nullFk :Game_Company的id数据：useridusernamegameid001张三9003002李四9002003陈奇9001004王照9005005郑明9006006胡倩9006007李梦9007008马林90061. 创建game 表的结构；表名定义为表名+自己的姓名；create table database_java1_test.game_XXX( idChar(4), companyIdChar(5) not null, namevarChar(100) not null, typevarChar(50);2. 向game表中的主外键；alter table database_java1_test.game add primary key (id);alter table database_java1_test.gameadd constraint fk_game_companyforeign key (companyId) references database_java1_test.game_company (id) ;3. 添加game表的数据；【9008， 00003， 超级玛丽， 动作】Insert into game values (9008, 00003, 超级玛丽, 动作);4. 更新game表数据，把9008游戏名更新为 超级玛丽升级版；Update game set name=超级玛丽升级版 where id=9008;5. 统计各个游戏类别的游戏数量；Select name, count(*) from game group by name;6. 查询每个游戏商家生产的游戏个数，以及厂商名，国家；select t0.id, , t0.country, t.totalnum from game_company t0, (select companyid, count(*) totalnum from game group by companyid) twhere t0.id=panyid7. 查询玩家最少的游戏名；select t0.id, , t1.minnum from game t0, (select t.gameid, min(t.totalnum) minnum from (select gameid, count(*) totalnum from player group by gameid) t) t1where t0.id=t1.gameid;8. 查询游戏名带有%的游戏select * from game where name like %;9. 查询没有人玩的游戏 select * from game g where g.id not in (select distinct p.gameid from player p)10. 查询玩家最多的三个游戏名；select t0.id, , t.totalnum from game t0, (select gameid, count(*) totalnum from player group by gameid order by totalnum desc, gameid asc limit 3) twhere t0.id=t.gameid;数据drop table Player;drop table Game;drop table Game_Company;create table Game_Company( id Char(5) not null primary key, namevarChar2(100) not null, countryvarChar2(100);create table Game( idChar(4)primary key, companyIdChar(5) not null, namevarChar2(100) not null, typevarChar2(50);create table Player( useridChar(3) primary key, usernamevarChar2(50) not null, gameidChar(4) not null); insert into game values (9001,00001,恶狼传说,格斗); insert into game values (9002,00001,樱花大战90%,益智); insert into game values (9003,00002,GT赛车,竞技); insert into game values (9004,00003,口袋妖怪,益智); insert into game values (9005,00003,俄罗斯方块,益智); insert into game values (9006,00003,马力欧,益智); insert into game values (9007,00003,口袋妖怪,竞技);insert into Game_Company values(00001,世嘉,日本);insert into Game_Company values(00002,盛大,中国);insert into Game_Company values(00003,任天堂,美国);insert into Player values(001,张三,9003);insert into Player values(002,李四,9002);insert into Player values(003,陈奇,9001);insert into Player values(004,王照,9005);inse