數(shù)據(jù)庫上機考試題-含答案.doc

游戲開發(fā)商 Game_Company結(jié)構(gòu)：idChar(5) not nullpknamevarChar2(100) not nullcountryvarChar2(100) 數(shù)據(jù)：idnamecountry00001世嘉日本00002盛大中國00003任天堂美國游戲 Game結(jié)構(gòu)：idChar(4) not nullpkcompanyIdChar(5) not nullFk :Game_Company的idnamevarChar2(100) not nulltypevarChar2(50)數(shù)據(jù)：idcompanyIdnametype900100001惡狼傳說格斗900200001櫻花大戰(zhàn)90%益智900300002GT賽車競技900400003口袋妖怪益智900500003俄羅斯方塊益智900600003馬力歐益智900700003口袋妖怪競技游戲者 Player結(jié)構(gòu)：useridChar(3) not nullpkusernamevarChar2(50) not nullgameidChar(4) not nullFk :Game_Company的id數(shù)據(jù)：useridusernamegameid001張三9003002李四9002003陳奇9001004王照9005005鄭明9006006胡倩9006007李夢9007008馬林90061. 創(chuàng)建game 表的結(jié)構(gòu)；表名定義為表名+自己的姓名；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表的數(shù)據(jù)；【9008， 00003， 超級瑪麗， 動作】Insert into game values (9008, 00003, 超級瑪麗, 動作);4. 更新game表數(shù)據(jù)，把9008游戲名更新為 超級瑪麗升級版；Update game set name=超級瑪麗升級版 where id=9008;5. 統(tǒng)計各個游戲類別的游戲數(shù)量；Select name, count(*) from game group by name;6. 查詢每個游戲商家生產(chǎn)的游戲個數(shù)，以及廠商名，國家；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;數(shù)據(jù)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,櫻花大戰(zhàn)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