Oracle学习——编码练习

有如下表关系：

玩家表Player

    ID          NAME            BIRTHDAY            SCORE           GAMEID

    10011       BOB             1998-01-07          15894.86        001

    10023       TOM             1989-09-08          15733.91        003

    10089       LEO             1997-08-08          15633.25        001

    10073       JAMES           1999-03-10          17888.29        002

    10025       SMITH           1987-01-06          19331.79        003

    …

游戏表GAME

    GAMEID          GAMENAME            COMPANY         COUNTRY

    001             CRAZYBIRD           NEUSOFT         CHINA

    002             SUPERMARIO          RENTIANTANG     JAPAN

    003             RUNNINGMAN          MIRSOFT         AMERICA

    004             DNF                 NEOPLE          KOREA

1.建立如上表格，并插入上述数据

CREATE TABLE Player(

ID NUMBER (5),

NAME VARCHAR 2(20),

BIRTHDAY DATE,

SCORE NUMBER(7,2),

GAMEID CHAR(3)

)

--插入上述数据

INSERT INTO Player VALUES(10011,’BOB’,’07-1月-1998’,15894.86,’001’)

…

--创建GAME表

CREATE TABLE GAME(

GAMEID CHAR(3),

GAMENAME VARCHAR2(20),

COMPANY VARCHAR2(20),

COUNTRY VARCHAR2(20)

)

--插入上述数据

INSERT INTO GAME VALUES(‘001’,’ CRAZYBIRD’,’ NEUSOFT’,’ CHINA’)

…

2.修改GAME表中数据，将编号004的游戏名称改为‘地下城与勇士’

Update GAME

Set gamename = ‘地下城与勇士’

Where gameid = ‘004’

3.删除编号10011的玩家

Delete from Player

Where ID = 10011

4.查询出游戏编号002玩家的平均分数。

SELECT AVG(score)

FROM player

WHERE gameid = '002'

5.查询出玩日本（JAPAN）游戏的玩家人数。

SELECT COUNT(ID)

FROM player

WHERE gameid IN (SELECT gameid FROM game WHERE country = 'JAPAN')

6.查询出所有玩家的姓名，分数，所玩游戏编号，所玩游戏名称，及生产公司。

SELECT NAME,score,g.gameid,gamename,company

FROM player p,game g

WHERE p.gameid = g.gameid;

7.查询出高于自己所玩游戏平均分数的玩家信息。

SELECT p.*

FROM player p,(SELECT gameid,AVG(score) avgsal FROM player GROUP BY gameid) f

WHERE p.gameid = f.gameid

AND score > avgsal

8.查询出每种游戏的玩家人数，游戏名称，要求没有玩家的游戏名称也要显示出来。

方法一

SELECT COUNT(ID),gamename

FROM player p,game g

WHERE p.gameid(+) = g.gameid

GROUP BY gamename

方法二

SELECT COUNT(ID),gamename

FROM game g

LEFT OUTER JOIN player p

ON g.gameid = p.gameid

GROUP BY gamename

9.查询出与JAMES玩家玩相同游戏，分数比他高的玩家信息。

SELECT *

FROM player

WHERE gameid = (SELECT gameid FROM player WHERE NAME = 'JAMES')

AND score > (SELECT score FROM player WHERE NAME = 'JAMES')

10.查询出没有游戏玩家的游戏编号，游戏名称。

SELECT g.gameid,gamename

FROM game g

WHERE gameid NOT IN (SELECT gameid FROM player)

11.查询出出生日期比LEO早，分数比TOM高的玩家姓名，出生日期，游戏编号。

SELECT NAME,birthday,gameid

FROM player

WHERE birthday < (SELECT birthday FROM player WHERE NAME = 'LEO')

AND score > (SELECT score FROM player WHERE NAME = 'TOM')

12.查询出游戏玩家数量大于100的游戏编号，游戏名称。

SELECT g.gameid,gamename

FROM player p,game g

WHERE g.gameid = p.gameid

GROUP BY g.gameid,gamename

HAVING COUNT(ID) > 100

13.查询分数大于DNF玩家平均分数的玩家编号，玩家姓名，分数，及他自己所玩游戏的平均分数。

SELECT ID,NAME,score,avgsal

FROM player p,(SELECT gameid,AVG(score) avgsal FROM player GROUP BY gameid) f

WHERE p.gameid = f.gameid

AND score > (SELECT AVG(score)

            FROM player

            WHERE gameid = (SELECT gameid FROM game WHERE gamename = 'DNF'))