Oracle测试题

Oracle基础测试
第一题. 60分
1.建立如下4张表：5分*4=20分
学生表student（sid,sname)，
教师表teacher(tid,tname)，
课程表course(cid,cname，ctype)，
选课表choose_course(ccid,sid,tid,cid)
2. 分别在4张表中插入如下数据：5分*4=20分
学生表(1,'小明');
学生表(2,'小花');
教师表(1,'陈红');
教师表(2,'陈白');
课程表(1,'语文','文科');
课程表(2,'数学','理科');
--小明选了陈红老师的语文
--小明选了陈红老师的数学
--小花选了陈红老师的数学
--小明选了陈白老师的语文
--小花选了陈红老师的语文
3. 查出如下数据5分*4=20分
a.查找陈红老师教的学生是那些？
SELECT SNAME FROM(
SELECT * FROM CHOOSE_COURSE_STU8899ED JOIN STUDENT_STU8899ED
ON CHOOSE_COURSE_STU8899ED.SID=STUDENT_STU8899ED.SID
)E
WHERE E.TID=1
b.找学生小明所有的文科老师？
SELECT TNAME FROM(
SELECT * FROM(
SELECT * FROM(SELECT * FROM CHOOSE_COURSE_STU8899ED CC JOIN STUDENT_STU8899ED S
ON CC.SID=S.SID )T1 JOIN TEACHER_STU8899ED T
ON T.TID=T1.TID)T2 JOIN COURSE_STU8899ED CS
ON T2.CID=CS.CID
)WHERE SNAME='小明' and CTYPE='文科'
c.找出没有选修陈红老师的学生
SELECT * FROM TEACHER_STU8899ED
WHERE TID NOT IN(
SELECT TEACHER_STU8899ED.TID FROM TEACHER_STU8899ED T,STUDENT_STU8899ED S,CHOOSE_COURSE_STU8899ED CC
WHERE T.TID=CC.TID AND S.SID=CC.SID AND TNAME='陈红' )
d.教的学生最少的老师？
SELECT TNAME FROM TEACHER_STU8899ED
WHERE TID=(
SELECT TID FROM
(SELECT TID,COUNT(*) C FROM CHOOSE_COURSE_STU8899ED E
WHERE TID IN (SELECT TID FROM CHOOSE_COURSE_STU8899ED M WHERE E.TID=M.TID)
GROUP BY TID)
WHERE ROWNUM=1
)
第二题. 10分
表一(AAA)
商品名称mc 商品总量sl
A 100
B 120
表二(BBB)
商品名称mc 出库数量sl
A 10
A 20
B 10
B 20
B 30
用一条SQL语句算出商品A,B目前还剩多少？
SELECT AAA.MC,(SL-E.SS) LEFT FROM AAA
JOIN(
SELECT MC,SUM(SL) SS FROM BBB
GROUP BY MC
)E
ON AAA.MC=E.MC
第三题. 10分
用一条SQL 语句查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三语文81
张三数学75
李四语文76
李四数学90
王五语文81
王五数学100
王五英语90
SELECT DISTINCT NAME FROM SSSSSS E
WHERE (
SELECT COUNT(*) FROM SSSSSS M
WHERE FENSHU>80 AND =
)=3
第四题. 10分
一个表中的同一个ID可能有1条记录，也可能有多个记录，把所有重复的ID 查出来，并显示每个重复的ID有多少条记录数。

例如：ID 001有1条，ID 002有5条，ID 006有6条
则结果显示：ID 数量
002 5
006 6
SELECT TNAME FROM
WHERE TID IN(
SELECT TID FROM
(SELECT TID,COUNT(*) C FROM CHOOSE_COURSE_STU8899ED E WHERE TID IN (SELECT TID FROM CHOOSE_COURSE_STU8899ED M
WHERE E.TID=M.TID)
GROUP BY TID)
WHERE ROWNUM=1
)
第五题. 10分
表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句? 胜负
2005-05-09 2 2
2005-05-10 1 2
SELECT E1.*,C2 FROM
(SELECT DA,COUNT(*) C1 FROM S1 E WHERE DA IN (SELECT DA FROM S1 M WHERE E.DA=M.DA AND E.SU='F') GROUP BY DA)E1
JOIN
(SELECT DA,COUNT(*) C2 FROM S1 E WHERE DA IN (SELECT DA FROM S1 M WHERE E.DA=M.DA AND E.SU='v') GROUP BY DA)E2
ON E1.DA=E2.DA。