Oracle的关于建表，约束，查询等的练习(二)
得出
SELECT i.itemname,s.name,g.mark
FROM item i,grade g,sporter s
WHERE i.location='二操场'
AND i.itemid=g.itemid
AND s.sporterid=g.sporterid
AND g.mark IN(
SELECT MAX(mark) FROM (
SELECT i.itemname itemname,s.name name,g.mark mark
FROM item i,grade g,sporter s
WHERE i.location='二操场'
AND i.itemid=g.itemid
AND s.sporterid=g.sporterid
)GROUP BY itemname
)
;
3.找出参加了张三所参加过的项目的其他同学的姓名
SELECT DISTINCT s.name FROM sporter s,grade g WHERE s.sporterid=g.sporterid AND s.name<>'张三' AND g.itemid IN ( SELECT g.itemid FROM sporter s,grade g WHERE s.sporterid=g.sporterid AND s.name='张三' ) ;
4.经查张三因为使用了违禁药品，其成绩都记为0分，做出修改
UPDATE grade SET mark=0 WHERE sporterid=( SELECT sporterid FROM sporter WHERE name='张三' );
5.取消山粗女子跳高比赛项目
DELETE FROM item WHERE itemname='女子跳高';